Rôzne metódy na porovnávanie údajov v programe Excel
Existuje niekoľko spôsobov, ako porovnávať údaje v programe Excel, ak sa chceme zhodovať s údajmi v rovnakom stĺpci, povedzme, že chceme skontrolovať duplicitu, môžeme použiť podmienené formátovanie z karty Domovská stránka alebo v opačnom prípade, ak sa chceme porovnávať s údajmi v dvoch alebo viacerých viac rôznych stĺpcov môžeme použiť podmienené funkcie ako if if.
- Metóda č. 1 - Použitie funkcie Vlookup
- Metóda č. 2 - Použitie funkcie Index + Match
- Metóda č. 3 - Vytvorte si svoju vlastnú vyhľadávaciu hodnotu
Teraz si poďme podrobne rozobrať každú z metód
Túto šablónu Excel Match Data si môžete stiahnuť tu - Match Excel Excel Template# 1 - Priraďte údaje pomocou funkcie VLOOKUP
VLOOKUP sa nepoužíva iba na získanie požadovaných informácií z údajovej tabuľky, ale môže sa použiť aj ako nástroj na zosúladenie. Pokiaľ ide o zosúladenie alebo porovnanie údajov, v tabuľke vedie vzorec VLOOKUP.
Napríklad si pozrite nasledujúcu tabuľku.
Máme tu dve údajové tabuľky, prvá je Data 1 a druhá sú Data 2.
Teraz musíme zosúladiť, či sa údaje v dvoch tabuľkách zhodujú alebo nie. Úplne prvým spôsobom zhody údajov je funkcia SUM v programe Excel v dvoch tabuľkách, ktorá umožňuje získať celkový predaj.
Údaje 1 - tabuľka
Údaje 2 - tabuľka
Funkciu SUM som použil pre stĺpec Predajná suma tabuľky. Na začiatku samotného kroku sme dostali rozdiel v hodnotách. Tabuľka údajov 1 s celkovým predajom 2 16 214 a tabuľka údajov 2 s celkovým predajom 2 10 214 .
Teraz to musíme podrobne preskúmať. Aplikujme teda funkciu VLOOKUP na každý dátum.
Vyberte pole tabuľky ako rozsah údajov 1 .
Potrebujeme údaje z druhého stĺpca a rozsah vyhľadávania je FALSE, tj. Presná zhoda.
Výstup je uvedený nižšie:
V nasledujúcej bunke odpočítajte pôvodnú hodnotu s dorazovou hodnotou.
Po odpočítaní dostaneme výsledok ako nulu.
Teraz skopírujte a prilepte vzorec do všetkých buniek, aby ste získali hodnoty odchýlky.
V bunkách G6 a G12 sme dostali rozdiely.
V dátach 1 máme k dátumu 04.03.2019 12104 a v dátach 2 máme k rovnakému dátumu 15104, takže existuje rozdiel 3000.
Podobne k dátumu 18. marca 2019 v dátach 1 máme 19351 a v dátach 2 máme 10351, takže rozdiel je 9000.
# 2 - Priradenie údajov pomocou funkcie INDEX + MATCH
Pre rovnaké údaje môžeme použiť funkciu INDEX + MATCH. Môžeme to použiť ako alternatívu k funkcii VLOOKUP.
Funkcia INDEX použitá na získanie hodnoty z vybraného stĺpca na základe poskytnutého čísla riadku. Aby sme poskytli číslo riadku, musíme použiť funkciu MATCH na základe hodnoty LOOKUP.
Otvorte funkciu INDEX v bunke F3.
Vyberte pole ako rozsah stĺpca výsledku, tj. B2 až B14.
Aby bolo možné získať číslo riadku, otvorte ako ďalší argument funkciu MATCH.
Vyberte vyhľadávanú hodnotu ako bunku D3.
Ďalej vyberte vyhľadávacie pole ako stĺpec Dátum predaja v Údaje 1.
V type zhody zvoľte „0 - Presná zhoda“.
Zatvorte dve zátvorky a stlačte kláves Enter, aby ste dosiahli výsledok.
To tiež dáva rovnaký výsledok ako iba VLOOKUP. Pretože sme použili rovnaké údaje, dostali sme čísla, aké sú
# 3 - Vytvorte si svoju vlastnú vyhľadávaciu hodnotu
Teraz sme videli, ako spojiť údaje pomocou funkcií programu Excel. Teraz uvidíme odlišný scenár reálneho času. V tomto príklade sa pozrite na nižšie uvedené údaje.
Vo vyššie uvedených údajoch máme údaje o predaji podľa zóny a dátumu, ako je uvedené vyššie. Musíme znova vykonať proces párovania údajov. Aplikujme funkciu VLOOKUP podľa predchádzajúceho príkladu.
Dostali sme veľa variantov. Poďme preskúmať každý prípad od prípadu.
V bunke I5 sme dostali odchýlku 8300. Pozrime sa na hlavnú tabuľku.
Aj keď v hlavnej tabuľke je hodnota 12104, z funkcie VLOOKUP sme dostali hodnotu 20404. Dôvodom je VLOOKUP, ktorý môže vrátiť hodnotu prvej nájdenej vyhľadávacej hodnoty.
V tomto prípade je našou vyhľadávanou hodnotou dátum, tj 20. marca 2019. Vo vyššie uvedenej bunke pre severnú zónu k rovnakému dátumu máme hodnotu 20404, takže VLOOKUP vrátil túto hodnotu aj pre východnú zónu.
Na prekonanie tohto problému je potrebné vytvoriť jedinečné vyhľadávacie hodnoty. Spojte zónu, dátum a objem predaja v dátach 1 aj dátach 2.
Údaje 1 - tabuľka
Údaje 2 - tabuľka
Teraz sme vytvorili jedinečnú hodnotu pre každú zónu s kombinovanou hodnotou zóny, dátumu predaja a sumy predaja.
Pomocou týchto jedinečných hodnôt môžeme použiť funkciu VLOOKUP.
Použite vzorec na všetky bunky, vo všetkých bunkách dostaneme odchýlku nula.
Takto môžeme pomocou funkcií programu Excel porovnávať údaje a nájsť odchýlky. Pred použitím vzorca sa musíme pozrieť na duplikáty vo vyhľadávacej hodnote, aby sme dosiahli presné porovnanie. Vyššie uvedený príklad predstavuje najlepšiu ilustráciu duplicitných hodnôt vo vyhľadávacej hodnote. V takýchto scenároch musíme vytvoriť vlastné jedinečné hodnoty vyhľadávania a dospieť k výsledku.