Různé metody pro párování dat v aplikaci Excel
Existuje několik způsobů, jak porovnat data v aplikaci Excel, pokud chceme porovnat data ve stejném sloupci, řekněme, že chceme zkontrolovat duplicitu, můžeme použít podmíněné formátování z domovské karty nebo jinak, pokud chceme porovnat data ve dvou nebo více různých sloupců můžeme použít podmíněné funkce jako if if.
- Metoda č. 1 - Použití funkce Vlookup
- Metoda č. 2 - Použití funkce Index + Match
- Metoda č. 3 - Vytvořte si vlastní vyhledávací hodnotu
Nyní pojďme podrobně probrat každou z metod
# 1 - Porovnání dat pomocí funkce VLOOKUP
VLOOKUP se nepoužívá pouze k získání požadovaných informací z datové tabulky; spíše jej lze použít také jako nástroj pro usmíření. Pokud jde o odsouhlasení nebo shodu dat, vede vzorec VLOOKUP vzorec.
Podívejte se například na následující tabulku.
Máme zde dvě datové tabulky, první je Data 1 a druhá Data 2.
Nyní musíme sladit, zda se data ve dvou tabulkách shodují nebo ne. Úplně první způsob, jak porovnat data, je funkce SUMA v aplikaci Excel se dvěma tabulkami, kde získáte celkový prodej.
Data 1 - tabulka
Data 2 - tabulka
Použil jsem funkci SUM pro sloupec Prodejní částka tabulky. Na začátku samotného kroku jsme dostali rozdíl v hodnotách. Tabulka Data 1 s celkovým prodejem 2,16 214 a tabulka Data 2 s celkovým prodejem 2,10 214 .
Nyní to musíme podrobně prozkoumat. Pojďme tedy použít funkci VLOOKUP pro každé datum.
Vyberte pole tabulky jako rozsah dat 1 .
Potřebujeme data z druhého sloupce a rozsah vyhledávání je FALSE, tj. Přesná shoda.
Výstup je uveden níže:
V další buňce odečtěte původní hodnotu s dorazovou hodnotou.
Po odečtení dostaneme výsledek jako nulu.
Nyní zkopírujte a vložte vzorec do všech buněk, abyste získali hodnoty odchylky.
V buňce G6 a G12 jsme dostali rozdíly.
V Data 1 máme pro datum 04-Mar-2019 12104 a v Data 2 máme pro stejné datum 15104, takže je rozdíl 3000.
Podobně pro datum 18. března 2019 v Data 1 máme 19351 a v Data 2 máme 10351, takže rozdíl je 9000.
# 2 - Porovnání dat pomocí funkce INDEX + MATCH
Pro stejná data můžeme použít funkci INDEX + MATCH. Můžeme to použít jako alternativu k funkci VLOOKUP.
Funkce INDEX se používá k získání hodnoty z vybraného sloupce na základě zadaného čísla řádku. Abychom poskytli číslo řádku, musíme použít funkci MATCH na základě hodnoty LOOKUP.
Otevřete funkci INDEX v buňce F3.
Vyberte pole jako rozsah sloupce výsledku, tj. B2 až B14.
Chcete-li získat číslo řádku, otevřete funkci MATCH nyní jako další argument.
Vyberte vyhledávací hodnotu jako buňku D3.
Dále vyberte vyhledávací pole jako sloupec Datum prodeje v Data 1.
V typu shody vyberte „0 - Přesná shoda“.
Uzavřete dvě závorky a stisknutím klávesy Enter získáte výsledek.
To také dává stejný výsledek jako pouze VLOOKUP. Protože jsme použili stejná data, dostali jsme čísla taková, jaká jsou
# 3 - Vytvořte si vlastní vyhledávací hodnotu
Nyní jsme viděli, jak spojit data pomocí funkcí aplikace Excel. Nyní uvidíme různé scénáře v reálném čase. V tomto příkladu se podívejte na níže uvedená data.
Ve výše uvedených datech máme Zone-Wise a Date-wise údaje o prodeji, jak je uvedeno výše. Musíme znovu provést proces párování dat. Použijme funkci VLOOKUP podle předchozího příkladu.
Máme mnoho variant. Prozkoumejme každý případ od případu.
V buňce I5 jsme dostali rozptyl 8300. Podívejme se na hlavní tabulku.
I když je hlavní hodnota tabulky 12104, dostali jsme z funkce VLOOKUP hodnotu 20404. Důvodem je VLOOKUP, který může vrátit hodnotu první nalezené vyhledávací hodnoty.
V tomto případě je naší vyhledávací hodnotou datum, tj. 20. března 2019. Ve výše uvedené buňce pro severní zónu pro stejné datum máme hodnotu 20404, takže VLOOKUP vrátil tuto hodnotu také pro východní zónu.
Abychom tento problém překonali, musíme vytvořit jedinečné vyhledávací hodnoty. Zkombinujte zónu, datum a částku prodeje v datech 1 i datech 2.
Data 1 - tabulka
Data 2 - tabulka
Nyní jsme pro každou zónu vytvořili jedinečnou hodnotu s kombinovanou hodnotou zóny, data prodeje a částky prodeje.
Pomocí těchto jedinečných hodnot použijeme funkci VLOOKUP.
Aplikujte vzorec na všechny buňky; dostaneme rozptyl nuly ve všech buňkách.
Takto můžeme pomocí funkcí aplikace Excel porovnat data a najít odchylky. Než použijeme vzorec, musíme se podívat na duplikáty ve vyhledávací hodnotě pro přesné odsouhlasení. Výše uvedený příklad je nejlepší ilustrací duplicitních hodnot ve vyhledávací hodnotě. V takových scénářích musíme vytvořit vlastní jedinečné hodnoty vyhledávání a dospět k výsledku.
