Ahoj Paulo, umíš porovnat seznamy v Excelu?
nebo
Jaký je nejlepší způsob porovnání dvou sad dat v Excelu?
Velmi často se v Excelu objevuje požadavek porovnat dva seznamy nebo dvě datové sady a najít chybějící nebo shodné položky. Protože se jedná o Excel, existuje vždy více než jeden způsob, jak věci dělat, včetně porovnávání dat. Od vzorců a podmíněného formátování až po Power Query. V tomto článku se podíváme na několik způsobů porovnání dvou seznamů v aplikaci Excel a podíváme se také na porovnání celých řádků datové sady.
Možná váš preferovaný způsob není uveden níže. Pokud ne, proč nenapíšete komentář níže a nepodělíte se s námi o to, jak nejraději porovnáváte dva seznamy nebo soubory dat v aplikaci Excel. Těším se na vaše komentáře.
Přejeme si porovnat seznam 1 se seznamem 2.
Přejeme si porovnat seznam 1 se seznamem 2.
Obsah
Rychlé podmíněné formátování pro porovnání dvou sloupců dat
Odstranění podmíněného formátování
Porovnání dat v Excelu pomocí funkce MATCH
Porovnání 2 seznamů v Excelu 365 pomocí MATCH nebo XMATCH jako. Funkce dynamického pole
MATCH a dynamická pole pro porovnání 2 seznamů
XMATCH Excel 365 pro porovnání dvou seznamů
Tabulky – porovnání seznamů v Excelu, kde se mohou měnit velikosti rozsahů
Zvýraznění rozdílů v seznamech pomocí vlastního podmíněného formátování
Kopírování vzorce pro Vlastní podmíněné formátování
Další vzorce používané pro porovnání dvou seznamů v Excelu
VLOOKUP pro porovnání dvou seznamů v Excelu
XLOOKUP pro porovnání dvou seznamů v Excelu
COUNTIF pro porovnání dvou seznamů v Excelu
Jak porovnat 2 datové sady v Excelu
Porovnání seznamů nebo souborů dat pomocí Power Query
Rychlé podmíněné formátování pro porovnání dvou sloupců dat
Podmíněné formátování vám umožní zvýraznit buňky nebo rozsah na základě předem definovaných kritérií. Nejrychlejším a nejjednodušším způsobem, jak tyto dva sloupce rychle vizuálně porovnat, je použít předdefinované pravidlo zvýraznění duplicitní hodnoty.
Začněte výběrem dvou sloupců dat.
Na kartě Domů vyberte rozbalovací nabídku Podmíněné formátování. Poté vyberte možnost Pravidla zvýraznění buněk. Dále vyberte Duplicitní hodnoty.
Otevře se okno nastavení Duplicitních hodnot, kde můžete definovat formátování a vybrat mezi Duplicitními nebo Jedinečnými hodnotami.
Zvolením Duplicitní budou všechny opakující se položky nastaveny na zvolené formátování. Nyní můžete rychle zobrazit položky v Seznamu 1, které jsou v Seznamu 2, protože se jedná o formátované položky. Můžete také rychle vidět položky v seznamu 2, které nejsou v seznamu 1, protože tyto položky nemají použité formátování.
Můžete však také formátovat jedinečné položky. Toho lze dosáhnout výběrem možnosti Jedinečné v poli nastavení Duplicitní hodnoty.
V tomto případě jsme použili dva různé podmíněné formáty. Červený označující duplicity a zelený označující unikátní položky.
Všimněte si, že jsem nevzal jen buňky s daty, ale uchopil jsem všechny sloupce A až C. Sloupec B nemá žádná data, takže nemůže ovlivnit výsledky. Tyto buňky však obsahují použité podmíněné formátování, takže by bylo lepším postupem vybrat pouze ty buňky, které potřebujete.
Vymazání podmíněného formátování
Chcete-li vymazat veškeré podmíněné formátování, nejprve vyberte buňku, resp. rozsah. Poté vyberte rozbalovací nabídku podmíněného formátování na pásu Domů. Poté vyberte možnost Vymazat pravidla. Nakonec vyberte možnost Vymazat pravidla z vybraných buněk.
Pokud máte použito více podmíněných formátování najednou a chcete odstranit pouze jedno z nich, vyberte možnost Správa pravidel z rozbalovacího seznamu podmíněného formátování. Vyberte pravidlo, které chcete odstranit, a poté vyberte možnost Odstranit pravidlo.
Stisknutím tlačítka OK se pravidlo odstraní ze Správce pravidel a buňky již nebudou obsahovat formátování.
Tak to je nejzákladnější způsob, jak můžete v Excelu porovnat dva seznamy. Je rychlý, jednoduchý a účinný. Můžete také použít podmíněné formátování založené na vzorcích, kterým se budeme věnovat později v tomto článku.
Srovnávání dat v Excelu pomocí funkce MATCH
Existuje mnoho vyhledávacích vzorců, které můžete použít k porovnání dvou rozsahů nebo seznamů v Excelu. Jako první se podíváme na funkci MATCH.
Funkce MATCH vrací relativní pozici v seznamu. Číslo na základě jeho pozice, pokud bylo nalezeno, v poli vyhledávání.
Syntaxe funkce MATCH je
=MATCH (hodnota vyhledávání, pole vyhledávání, typ shody)
Kde hodnota vyhledávání je hodnota, pro kterou chcete najít shodu. Lookup array je seznam, ve kterém hledáte shodu. A typ shody umožňuje vybrat mezi přesnou nebo přibližnou shodou.
Chceme napsat vzorec pro hledání shody, abychom zjistili, zda se položky v seznamu 2 nacházejí v seznamu 1.
V buňce E3 můžeme zadat vzorec
=MATCH(C2, $A$2:$A$21,0)
Při vyplnění tohoto vzorce dolů se pro hodnoty, kde Excel najde shodu, vrátí pozice této shody. Tam, kde shoda není, bude vrácenou hodnotou #N/A.
Velmi často se stává, že relativní pozice nebo #N/A pro nás nemá žádnou hodnotu a potřebujeme tyto hodnoty převést na true nebo false. K tomu můžeme náš vzorec Match snadno rozšířit pomocí logické funkce. Protože funkce Match vrací číslo, můžeme použít funkci ISNUMBER
=ISNUMBER(MATCH(C2, $A$2:$A$21,0))
Porovnání 2 seznamů v Excelu 365 pomocí funkce MATCH nebo XMATCH jako dynamického pole
Pokud používáte Excel 365, máte další alternativy při použití funkce MATCH pro porovnání seznamů nebo dat. Protože Excel 365 myslí v polích, můžeme nyní předat pole jako vyhledávací hodnotu funkce MATCH a naše výsledky se vysypou za nás. Tím odpadá nutnost kopírovat vzorec dolů a s pouze 1 vzorcem bude vaše tabulka méně náchylná k chybám a kompaktnější.
MATCH a dynamická pole pro porovnání 2 seznamů
Pokud ještě neznáte dynamická pole, doporučuji vám přečíst si tento článek: Dynamická pole Excelu – nový způsob modelování tabulek Excelu: abyste lépe porozuměli tomu, jak fungují a jak se přelévají rozsahy.
Jedinou změnou ve vzorci shody je, že místo výběru buňky C2 jako naší vyhledávací hodnoty vybereme rozsah C2:12
=ISNUMBER(MATCH (C2:C12,$A$2:$A$21,0))
Jak vidíte, jeden vzorec rozsype výsledky do sloupce E.
XMATCH Excel 365 pro porovnání dvou seznamů
Excel 365 také zavádí novou funkci XMATCH. Stejně jako funkce MATCH vrací XMATCH relativní pozici v seznamu. Nyní již znáte funkci XLOOKUP, která nahrazuje starou funkci VLOOKUP, víte, že XLOOKUP přichází s dalšími možnostmi. Ta přichází v podobě nových podmínek v syntaxi vzorce, jako je režim vyhledávání a typy shod. XMATCH má oproti svému předchůdci MATCH také tuto dodatečnou sílu.
Syntaxe pro XMATCH je
XMATCH (vyhledávací hodnota, vyhledávací pole, ,)
Kde
Vyhledávací hodnota je hodnota, kterou hledáte na relativní pozici
Vyhledávací pole je řádek nebo sloupec, který obsahuje vyhledávací hodnotu
Režim shody je volitelný. Na rozdíl od staré funkce MATCH je výchozím nastavením přesná shoda. Můžete také zvolit mezi
- Přesná shoda nebo nejbližší nejmenší
- Přesná shoda nebo nejbližší největší
- Shoda se zástupným znakem
Režim vyhledávání je také volitelný. Výchozí (a jediná možnost ve staré funkci MATCH) je hledání shora dolů. Můžete také zvolit vyhledávání od posledního k prvnímu a binární vyhledávání. Pokud pracujete s binárním vyhledáváním. Volba shody se zástupnými znaky nefunguje.
Pomocí funkce XMATCH můžeme k vytvoření vzorce použít buď dynamická pole, nebo odkazy na buňky, stejně jako jsme se na to podívali u funkce MATCH. Pro tento příklad použijeme dynamická pole. Vzorec je velmi podobný tomu, který jsme použili u MATCH; až na to, že nemusíme vybrat 0 pro přesnou shodu, protože v XMATCH je to výchozí nastavení. Trochu to zamícháme, tentokrát se podíváme na hledání položek v seznamu 2, které nejsou v seznamu 1.
V tomto případě můžeme použít vzorec
=NOT(ISNUMBER(XMATCH(C2:C12,A2:A21)))
Kde not změní trues na false a false na trues.
Tabulky – Porovnávání seznamů v Excelu, kde se mohou měnit velikosti rozsahů
V každém ze vzorců, kterými jsme se dosud zabývali, jsme ve funkci Shoda vybrali rozsah buněk, který není dynamický. To znamená, že pokud do některého ze seznamů přidáme nová data, musíme v ručním kroku aktualizovat náš vzorec tak, aby zahrnoval nová data.
Chcete-li převést seznamy na tabulky, vyberte jeden ze seznamů a stiskněte klávesu CTRL. To je klávesová zkratka pro převod na tabulku. Pokud jste v rozsahu buněk vybrali záhlaví, ujistěte se, že jste zaškrtli políčko potvrzující, že vaše tabulka má záhlaví.
Tabulky ze své podstaty používají strukturované pojmenování. Proto se při psaní vzorce a výběru sloupce z tabulky nezobrazí odkazy na buňky, ale název sloupce.
Podíváme-li se na náš předchozí vzorec používající XMATCH k nalezení položek v seznamu 2, které nejsou v seznamu 1, můžeme nyní tuto funkci přepsat pomocí odkazů na naši tabulku
=NOT (ISNUMBER(XMATCH(seznam2,list1)))
Jelikož jsme nyní použili tabulky, pokud přidáme nový řádek do některé z tabulek, zvětší se i náš rozsah rozsypu, aby zahrnoval nová data.
Zvýraznění rozdílů v seznamech pomocí vlastního podmíněného formátování
Předtím jsme se v tomto článku zabývali velmi rychlým způsobem porovnání těchto dvou seznamů pomocí předdefinovaného pravidla pro duplicity. Můžeme však také použít Vlastní podmíněné formátování. Pokud nejste obeznámeni s Vlastním podmíněným formátováním, doporučuji vám podívat se na tento článek: triky dynamického podmíněného formátování aplikace Excel:
Pro zvýraznění rozdílů se zde musíme podívat na dva různé přístupy. Pokud nepoužíváme tabulky a vytvořili jsme vzorec pravda/nepravda pro identifikaci rozdílů, můžeme vzít kopii vzorce a přidat ji do našeho Vlastního formátování. V případě tabulek si však musíme vynutit použití odkazů na buňky.
Zkopírujte vzorec do Vlastního podmíněného formátování
Začněte tím, že pořídíte kopii vzorce. Protože jsme vzorec vyzkoušeli v tabulce, můžeme se přesvědčit, že funguje, než ho použijeme v podmíněném formátování. To je osvědčený postup, protože u relativních a absolutních odkazů na buňky může být velmi často obtížné vzorec správně nastavit.
Vyberte buňky, na které chcete použít vlastní formátování. Poté na pásu Domů vyberte rozbalovací nabídku Podmíněné formátování a zvolte Nové pravidlo
Otevře se okno Nastavení nového pravidla formátování a vyberte možnost Použít vzorec pro určení buněk, které se mají formátovat. Poté vložte vzorec a nastavte typ formátování
Výsledkem bude, že všechny buňky, které jsou v obou seznamech, budou naformátovány na zvolený formát.
Mějte na paměti, že jsme pro použití tohoto podmíněného formátování vybrali rozsah buněk a není dynamické. Pokud bychom použili tabulky, aktualizovalo by se to, aniž bychom museli cokoli měnit!
Další vzorce používané k porovnání dvou seznamů v Excelu
K porovnání dvou seznamů v Excelu můžete použít mnoho vzorců. Již jsme se zabývali vzorci MATCH a XMATCH, ale nyní se podíváme na několik dalších. Spolu s některými dalšími bude opravdu fungovat kterákoli z vyhledávacích funkcí!
VLOOKUP pro porovnání dvou seznamů v Excelu
Pokud funkci VLOOKUP neznáte, můžete si o ní přečíst zde. Zjednodušeně řečeno VLOOKUP vrátí odpovídající hodnotu z buňky, pokud odpovídající hodnota neexistuje, vrátí se chyba #N/A. V našem příkladu pracujeme s textem. Můžeme tedy provést VLOOKUP a otestovat, zda vrátí text. Pokud bychom používali čísla, pak bychom mohli ISTEXT nahradit ISNUMBER.
Mohli bychom použít funkci =ISTEXT(VLOOKUP(C2, $A$2:$A$21,1,FALSE))
Nebo pokud bychom používali dynamická pole v aplikaci Excel 365, mohli bychom použít funkci
=ISTEXT(VLOOKUP (C2:C12,$A$2:$A$21,1,FALSE))
XLOOKUP pro porovnání dvou seznamů v Excelu
XLOOKUP byl zaveden v Excelu 365 a více informací o něm najdete zde. Velmi podobně jako VLOOKUP vrátí XLOOKUP odpovídající hodnotu z buňky a můžete definovat výsledek, pokud hodnotu nenajdete. Při použití dynamických polí by funkce byla
=ISTEXT(XLOOKUP (C2:C12,A2:A21,A2:A21))
COUNTIF pro porovnání dvou seznamů v Excelu
Funkce COUNTIF spočítá, kolikrát se hodnota nebo text nachází v rozsahu. Pokud se hodnota nenajde, vrátí se 0. Můžeme ji zkombinovat s příkazem IF a vrátit nám pravdivé a nepravdivé hodnoty.
=IF(COUNTIF (A2:A21,C2:C12)<>0, „True“, „False“)
Jak porovnat 2 datové sady v Excelu
Porovnání dvou seznamů je poměrně snadné a my jsme se nyní podívali na několik způsobů, jak to provést. Porovnání dvou datových souborů však může být o něco složitější.
Podívejme se na příklad. Máme dvě tabulky dat, z nichž každá obsahuje stejné záhlaví sloupců. Při pohledu na obrázek vidíme, že porovnání těchto dvou tabulek by vyžadovalo pohled na více než jeden sloupec.
Pokud se potřebujete podívat na více než jeden sloupec, řešením by bylo vytvoření složeného sloupce kombinujícího data do jednoho sloupce. Tím vytvoříme jedinečný sloupec pro každý řádek, který pak můžeme použít jako odpovídající sloupec
V tomto příkladu bychom mohli spojit Name a DoB, abychom každé tabulce dali jedinečný identifikátor
Existuje mnoho způsobů, jak spojit obsah buňky, v tomto případě provedeme jednoduché spojování. Protože používáme tabulky, vzorec obuje formát pojmenování tabulky.
= &“-„&
Zopakujte postup u druhé tabulky.
Nyní můžeme použít některý z výše uvedených příkladů pro přiřazení těchto dvou nových sloupců dat. Tam, kde se shodují, víme, že se shodují celé řádky.
Porovnávání seznamů nebo datových sad pomocí Power Query
Seznamy a datové sady můžete porovnávat také pomocí programu Excels Power Query. Připojením k tabulkám a následným sloučením tabulek pomocí různých typů spojení můžeme oba seznamy porovnat.
V tomto videu se dozvíte, jak porovnat nebo sladit dvě různé datové sady pomocí Excels Power query
K tomuto videu a procvičování existuje datová sada, kterou si můžete vzít z tohoto článku.