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 poďme podrobne diskutovať o každej z metód
# 1 - Priraďte údaje pomocou funkcie VLOOKUP
VLOOKUP sa nepoužíva iba na získanie požadovaných informácií z údajovej tabuľky; skôr sa dá použiť aj ako zmierovací nástroj. 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 priradenia údajov je funkcia SUM v programe Excel, ktorá slúži na získanie dvoch tabuliek s cieľom dosiahnuť 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 od dorazovej hodnoty.
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 Data 1 máme 19351 a v Data 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 sa používa 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.
Ak chcete získať číslo riadku, otvorte teraz funkciu MATCH ako ďalší argument.
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čením klávesu Enter získate 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 rôzne scenáre v reálnom čase. V tomto príklade si pozrite nasledujúce ú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. Preskúmajme každý prípad od prípadu.
V bunke I5 sme dostali odchýlku 8300. Pozrime sa na hlavnú tabuľku.
Aj keď je hlavná hodnota tabuľky 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ávacou 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 použijeme funkciu VLOOKUP.
Aplikujte vzorec na všetky bunky; dostaneme rozptyl nuly vo všetkých bunkách.
Takto pomocou funkcií programu Excel môžeme spojiť údaje a nájsť odchýlky. Pred použitím vzorca musíme pre presnú kontrolu skontrolovať duplikáty vo vyhľadávacej hodnote. Vyššie uvedený príklad najlepšie ilustruje duplicitné hodnoty vo vyhľadávacej hodnote. V takýchto scenároch musíme vytvoriť svoje vlastné jedinečné hodnoty vyhľadávania a dospieť k výsledku.
