Jak srovnávat seznamy v Excelu | Duplicity, shodné a jedinečné položky

V dnešním videu si ukážeme, jak v Excelu srovnávat seznamy nebo listy hodnot. Velmi často se vám totiž v Excelu stane, že musíte porovnat dva listy, najít v seznamech duplicity, odstranit duplicity, označit duplicity, určit jedinečné položky z obou listů, najít rozdíly nebo identifikovat shodné položky. Jak tyto úkoly vyřešit si ukážeme v dnešním videu.

Mohlo by vás rovněž zajímat: Jak vyhledávat v Excelu s duplicitami? Jak určit pořadí hodnot v Excelu včetně duplicit?

Excelový soubor ke stažení

Jak srovnávat seznamy v Excelu

Pro dnešní ukázku nám poslouží excelový soubor, kde máme několik seznamů společností, které budeme vzájemně porovnávat.

Jak najít a označit duplicity

Jedním z nejčastějších úkolů v Excelu při srovnávání listů je, najít duplicitní položky, respektive ověřit, zda se v seznamech duplicity vyskytují nebo ne. Máme zde dva seznamy společností. Naším úkolem je ověřit, zda se v druhém seznamu vyskytují položky, které se zároveň vyskytují i v prvním seznamu. Existuje celá řada způsobů, jak můžete tento úkol vyřešit. Ukážeme si několik z nich. 

Funkce COUNTIF pro vyhledání duplicit

Pro identifikaci duplicitních položek je asi nejsnazší použít funkci COUNTIF. A to z toho důvodu, že tuto metodu lze použít ve všech verzích Excelu a není zapotřebí pro potvrzení funkce použít kombinaci kláves CTRL+SHIFT+ENTER.

Vedle prvního seznamu máme vytvořený pomocný sloupec, kde v prvním buňce (B4) začneme s funkcí COUNTIF. Snažíme se zjistit, zda se položky z druhého seznamu (F4:F12) objevují v prvním seznamu (A4:A16). Z toho vyplývá, že ve funkci COUNTIF v prvním parametru oblast označíme celý druhý list ($F$4:$F$12) a jako kritérium označíme první položku v prvním seznamu (A4). Funkci potvrdíme a stáhneme pro všechny řádky dolů.

Výsledkem je série jedniček a nul. Duplicitu poznáme podle toho, že u ní funkce COUNTIF přiřadila jedničku. Společnosti, které nejsou v druhém seznamu jsou označené nulou. Společnosti, které se tedy vyskytují v obou listech jsou Walmart, Facebook, Mastercard a Alibaba.   

Jak označit duplicity v Excelu funkce COUNTIF
Obrázek č.1 Jak vyhledat duplicity pomocí funkce COUNTIF
Funkce SVYHLEDAT pro vyhledání duplicit

K vyhledávání duplicit v seznamech můžete ale využít i některou z vyhledávacích funkcí, jako jsou funkce SVYHLEDAT nebo funkce POZVYHLEDAT. V případě funkce SVYHLEDAT bychom v pomocném sloupci (C4) napsali funkci SVYHLEDAT, kde jako parametr co hledáme je první položka z prvního seznamu (A4). Druhým parametrem je prohledat, což je tabulka, kde společnost hledáme, což je celý druhý seznam ($F$4:$F$12). V jakém sloupci jsou naše odpovědi? Máme pouze jeden sloupec v tabulce, takže naše odpovědi jsou v prvním sloupci a hledáme přesnou shodu, takže nula.

Funkci SVYHLEDAT potvrdíme a stáhneme pro všechny řádky dolů. U shodných opakujících se položek se objevil stejný název a u ostatních se objevila chybová hláška. Abychom se zbavili těchto chybových hlášek, tak funkci SVYHLEDAT můžeme zabalit do funkce IFERROR, kde chybovou hlášku nahradíme prázdnou buňkou. Funkci potvrdíme a stáhneme pro ostatní buňky dolů.

Jak označit duplicity v Excelu funkce SVYHLEDAT
Obrázek č.2 Jak vyhledat duplicity pomocí funkce SVYHLEDAT
Funkce POZVYHLEDAT pro vyhledání duplicit

Velmi podobně funguje i další vyhledávací funkce POZVYHLEDAT, která by místo stejného slova u shodných společností vrátila číslo řádku, na kterém se duplicita v druhém seznamu nachází. Zkusíme to. Do buňky D4 napíšeme funkci POZVYHLEDAT, kde jako co označíme opět první společnost v prvním seznamu (A4). Jako parametr, kde označíme druhý seznam ($F$4:$F$12), kde společnost hledáme a hledáme zase přesnou shodu, takže nulu. Jelikož víme, že u společností, které funkce nenajde se zobrazí chybová hláška, tak funkci POZVYHLEDAT rovnou zabalíme do funkce IFERROR. Funkci potvrdíme a pošleme dolů.

Jak označit duplicity v Excelu funkce POZVYHLEDAT
Obrázek č.3 Jak vyhledat duplicity pomocí funkce POZVYHLEDAT

V našem příkladu máme seznamy se společnostmi krátké, takže jsou výsledky funkce patrné pouhým okem, ale pokud máte seznamy dlouhé, tak můžete pro vyfiltrování duplicit použít filtr nad tabulkou. Automatický filtr aktivujeme klávesovou zkratkou CTRL+SHIFT+L nebo z lišty Domů, kde vybereme filtr. Teď stačí pro zobrazení duplicitních položek vyfiltrovat jedničky, čísla nebo slova. Pro zobrazení jedinečných položek naopak zobrazíme nuly.

Jak označit duplicitní položky

Nejlehčím způsobem, jak označit duplicitní položky v seznamech je použití podmíněného formátování. Excel má dokonce možnost označení duplicitních položek přímo ve výběru pod záložkou podmíněné formátování. Stačí nejprve označit první seznam, podržet klávesu CTRL a označit položky ve druhém seznamu. Na kartě Domů najdeme záložku podmíněné formátování a zde Pravidla zvýraznění buněk a Duplicitní položky. Teď už jen stačí vybrat formát, barvu nebo styl podmíněného formátování. Když máme formát vybraný tak potvrdíme. Barevně se označily položky, které se v seznamech opakují.

Obrázek č.5 Jak označit duplicitní položky pomocí podmíněného formátování

Jak označit jedinečné položky

Pokud bychom naopak chtěli označit jedinečné položky v seznamech, tak opět označíme postupně oba seznamy a na kartě Domů vybereme Podmíněné formátování, zde vybereme Pravidla zvýraznění buněk a zde Další pravidla. Zde vybereme Formátovat pouze jedinečné nebo duplicitní hodnoty a v rozbalovacím seznamu vybereme možnost Jedinečné. Následně opět vybereme formát, který chceme na jedinečné položky uplatnit a když jsme hotoví tak potvrdíme. Teď se barevně označily všechny hodnoty, které jsou jedinečné.

Jak označit jedinečné položky v Excelu
Obrázek č.6 Jak označit jedinečné položky pomocí podmíněného formátování

Jak odstranit duplicity, aby zůstaly jen jedinečné položky

Dalším úkolem je vytvořit z těchto dvou seznamů pouze jeden seznam s jedinečnými hodnotami. To znamená, že ze seznamu potřebujeme odstranit duplicity. Ukážeme si tři způsoby, jak tento úkol vyřešit.

Odstranit duplicity

První řešení je nejjednodušší a v podstatě nevyžaduje použití žádných funkcí. Začneme tím, že si seznamy postupně nakopírujeme pod sebe do pomocného sloupce. Nejprve označíme položky v prvním seznamu, zkopírujeme a vložíme tam, kde chceme mít jedinečný seznam hodnot. Následně označíme položky ve druhém seznamu, zkopírujeme je a vložíme je pod první seznam. Teď stačí všechny položky označit a na kartě Data vybrat Odstranit duplicity. Excel odstraní duplicity a zanechá pouze jedinečný seznam hodnot.

Jak odstranit duplicity 2
Obrázek č.7 Jak odstranit duplicity v Excelu
Rozšířený filtr (Upřesnit) pro odstranění duplicit

K odstranění duplicit můžete použít i Rozšířeného filtru. Opět začneme tím, že si tentokrát někde mimo sloupec do pomocného sloupce nakopírujeme oba seznamy pod sebe (K49:K70). Teď stačí kliknout do sloupce, kde chcete mít jedinečný seznam hodnot (G49), musíte si ale dát pozor, že okolo tohoto sloupce musí být prázdné buňky, jinak rozšířený filtr nemusí fungovat. 

Klikneme na kartu Data a vybereme možnost rozšířený filtr, v angličtině Advanced filter, v české verzi Excelu Upřesnit. Zde označíme sloupec se seznamem, musíme ovšem označit i záhlaví sloupce, jinak rozšířený filtr vrátí o jednu položku navíc (K48:K70). Následně zaškrtneme možnost Bez duplicitních záznamů a jelikož chceme seznam kopírovat jinam, tak vybereme možnost nakopírovat jinam, a jako oblast kopírovat do vybereme záhlaví sloupce (G48), kam chceme jedinečné hodnoty zkopírovat. Potvrdíme a rozšířený filtr vrátil rovněž jedinečné záznamy.   

Jak odstranit duplicity rozšířený filtr
Obrázek č.8 Jak odstranit duplicity pomocí Rozšířeného filtru
Funkce UNIQUE

Uživatelé Office 365 a Excel 2021 mohou použít funkci UNIQUE. Někde vedle tabulky bychom si opět nakopírovaly seznamy pod sebe, což bude náš pomocný seznam (K49:K70). Do sloupce, kde chceme mít jedinečné hodnoty (I49), napíšeme funkci UNIQUE a jako pole označíme tento pomocný seznam (K49:K70). Funkce UNIQUE vrátí pouze jedinečné hodnoty ze seznamu. 

Jak odstranit duplicity funkce UNIQUE
Obrázek č.9 Jak odstranit duplicity pomocí funkce UNIQUE

Je položka v seznamu?

V dalším příkladu máme za úkol ověřit, které společnosti se z prvního seznamu vyskytují rovněž v druhém seznamu společností. Pro vyřešení tohoto úkolu můžeme použít klidně stejné metody, které jsme použili pro odhalení duplicitních položek. Zkusíme to tentokrát vyřešit za pomocí funkce POZVYHLEDAT

Do buňky B76 napíšeme funkci POZVYHLEDAT, a jelikož chceme ověřit, které položky z prvního seznamu se rovněž vyskytují i ve druhém seznamu, tak jako parametr co označíme první položku v prvním seznamu (A76) a jako pole, ve kterém společnost hledáme označíme druhý seznam ($E$76:$E$84). Hledáme přesnou shodu, takže jako parametr shoda vyplníme nulu. 

Funkci potvrdíme a stáhneme pro ostatní buňky dolů. U společností, které se vyskytují i v druhém seznamu se objevilo číslo a u ostatních řádků se objevila chybová hláška. Nejjednodušší je teď zabalit funkci POZVYHLEDAT do funkce JE.ČISLO. Tato funkce totiž číslo převede na pravdu a vše ostatní převede na nepravdu. U společností v prvním seznamu se teď objevila pravdu u těch společností, které se zároveň vyskytují i v druhém seznamu.

Je položka v seznamu
Obrázek č.10 Je položka v seznamu?

Není položka v seznamu?

Co kdybychom ale chtěli naopak ukázat společnosti, které nejsou ve druhém seznamu? Na začátku použijeme tu samou funkci POZVYHLEDAT, kde zase jako parametr co označíme první společnost v prvním seznamu (A76), jako oblast označíme druhý seznam společností ($E$76:$E$84) a hledáme opět přesnou shodu. Opět se objevila čísla a chybové hlášky. Tentokrát ale chceme ukázat položky, které se ve druhém seznamu nevyskytují. Na rozdíl od funkce JE.ČISLO tady použijeme funkce JE.NEDEF, která na rozdíl od funkce JE.ČISLO označí za pravdu chybové hlášky a jako nepravdu označí všechno ostatní. 

Není položka v seznamu
Obrázek č. 11 Není položka v seznamu?

Pro srovnání teď máme vedle sebe rozdíl. První sloupec ukazuje pravdu u položek, které jsou zároveň v prvním i druhém seznamu a druhý sloupec ukazuje pravdu u položek, které se vyskytují v prvním seznamu a nevyskytují se v druhém seznamu.

Pokud by vás to zajímalo obráceně, tedy které položky jsou v seznamu 2 a nejsou v seznamu 1, tak jen vyměníme parametry ve funkci POZVYHLEDAT. U druhého seznamu použijeme funkci POZVYHLEDAT, kde jako parametr co označíme tentokrát první položku v druhém seznamu (E76), parametr pole označíme seznam společností z prvního seznamu ($A$76:$A$88) a opět hledáme přesnou shodu. Hledáme položky, které nejsou v seznamu, takže potřebujeme místo chybových hlášek pravdu, takže použijeme funkci JE.NEDEF.

Není položka v seznamu 2
Obrázek č.12 Není položka v seznamu?

Zvýraznění odlišných položek

Poslední, co si dnes ukážeme je, jak v seznamu zvýraznit odlišné položky. V následujícím příkladu máme dva seznamy o dvou sloupcích. Máme zde společnosti a jejich tržby. Potřebujeme porovnat, zda se oba seznamy shodují, případně zvýraznit rozdíly. Zvolíme vizuální řešení a využijeme k tomu opět podmíněné formátování tentokrát za pomocí vzorce.

Neshody chceme zobrazit v seznamu 2. Označíme tedy celý druhý seznam a na kartě Domů vybereme Podmíněné formátování a zde Nové pravidlo. A jako pravidlo napíšeme, že chceme označit položky, kde se první buňka v seznamu dva nerovná první buňce v seznamu jedna. Jako vždy si u podmíněné formátování musíme dát pozor na fixace buněk. Jelikož potřebujeme, aby se buňky v rámci podmíněného formátování pohybovaly po celé tabulce, tak žádná z těchto buněk nesmí být zafixována. Odfixujeme buňky pomocí klávesy F4 a vybereme formát. Řekněme, že chceme buňky, které jsou rozdílné, obarvit na oranžovo. Potvrdíme. A ve druhém seznamu se barevně odlišily buňky, které jsou odlišné od prvního seznamu.

Jak označit rozdílné položky
Obrázek č.13 Jak zvýraznit v Excelu odlišné položky pomocí podmíněného formátování

MOHLO BY VÁS ZAJÍMAT

Napsat komentář

Vaše e-mailová adresa nebude zveřejněna. Vyžadované informace jsou označeny *