Sloučení dvou seznamů bez duplikátů

Klasická situace: máte dva seznamy, které je třeba sloučit do jednoho. Navíc v počátečních seznamech mohou být jedinečné prvky i odpovídající prvky (jak mezi seznamy, tak uvnitř), ale na výstupu musíte získat seznam bez duplikátů (opakování):

Podívejme se tradičně na několik způsobů, jak takový běžný problém vyřešit – od primitivních „na čelo“ až po složitější, ale elegantní.

Metoda 1: Odstraňte duplikáty

Problém můžete vyřešit nejjednodušším způsobem – ručně zkopírovat prvky obou seznamů do jednoho a poté nástroj aplikovat na výslednou sadu. Odebrat duplikáty ze záložky Data (Data – odstranit duplikáty):

Tato metoda samozřejmě nebude fungovat, pokud se data ve zdrojových seznamech často mění – po každé změně budete muset celý postup opakovat. 

Způsob 1a. kontingenční tabulka

Tato metoda je ve skutečnosti logickým pokračováním předchozí. Pokud seznamy nejsou příliš velké a maximální počet prvků v nich je předem znám (například ne více než 10), pak můžete spojit dvě tabulky do jedné přímými odkazy, přidat sloupec s jedničkami napravo a vytvořte souhrnnou tabulku na základě výsledné tabulky:

Jak víte, kontingenční tabulka ignoruje opakování, takže na výstupu dostaneme kombinovaný seznam bez duplikátů. Pomocný sloupec s 1 je nutný pouze proto, že Excel dokáže vytvořit souhrnné tabulky obsahující alespoň dva sloupce.

Když se původní seznamy změní, nová data přejdou do kombinované tabulky prostřednictvím přímých odkazů, ale kontingenční tabulku bude nutné aktualizovat ručně (kliknutím pravým tlačítkem – Aktualizovat a uložit). Pokud nepotřebujete přepočet za chodu, je lepší použít jiné možnosti.

Metoda 2: Vzorec pole

Problém můžete vyřešit pomocí vzorců. V tomto případě dojde k přepočtu a aktualizaci výsledků automaticky a okamžitě, ihned po změnách v původních seznamech. Pro pohodlí a stručnost uveďme naše seznamy názvy. Seznam 1 и Seznam 2použitím Správce jmen Karta vzorec (Vzorce — Správce jmen — Vytvořit):

Po pojmenování bude vzorec, který potřebujeme, vypadat takto:

Na první pohled to vypadá strašidelně, ale ve skutečnosti není všechno tak děsivé. Dovolte mi rozšířit tento vzorec na několik řádků pomocí kombinace kláves Alt+Enter a odsazení mezerami, jak jsme to udělali například zde:

Logika je zde následující:

• Vzorec INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) vybere všechny jedinečné prvky z prvního seznamu. Jakmile dojdou, začne hlásit chybu #N/A:

  

• Vzorec INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) extrahuje jedinečné prvky z druhého seznamu stejným způsobem.
• Dvě do sebe vnořené funkce IFERROR implementují výstup nejprve z jedinečných ze seznamu-1 a poté ze seznamu-2 jednu po druhé.

Všimněte si, že se jedná o maticový vzorec, tj. po napsání je nutné jej zadat do buňky, která není obyčejná vstoupit, ale pomocí klávesové zkratky Ctrl+směna+vstoupit a pak zkopírujte (přetáhněte) dolů do podřízených buněk s okrajem.

V anglické verzi Excelu vypadá tento vzorec takto:

=IFERROR(IFERROR(INDEX(Seznam1, MATCH(0, COUNTIF($E$1:E1, Seznam1), 0)), INDEX(Seznam2, MATCH(0, COUNTIF($E$1:E1, Seznam2), 0)) ), "") 

Nevýhodou tohoto přístupu je, že maticové vzorce znatelně zpomalují práci se souborem, pokud mají zdrojové tabulky velký (několik set nebo více) počet prvků. 

Metoda 3. Power Query

Pokud vaše zdrojové seznamy obsahují velký počet prvků, například několik stovek nebo tisíců, je lepší namísto pomalého maticového vzorce použít zásadně odlišný přístup, konkrétně nástroje doplňku Power Query. Tento doplněk je ve výchozím nastavení integrován do Excelu 2016. Pokud máte Excel 2010 nebo 2013, můžete si jej stáhnout a nainstalovat samostatně (zdarma).

Algoritmus akcí je následující:

1. Otevřete samostatnou kartu nainstalovaného doplňku Dotaz na napájení (pokud máte Excel 2010-2013) nebo prostě přejděte na kartu Data (pokud máte Excel 2016).
2. Vyberte první seznam a stiskněte tlačítko Z tabulky/rozsahu (Z rozsahu/tabulky). Když jsme se zeptali na vytvoření „inteligentní tabulky“ z našeho seznamu, souhlasíme:

   

3. Otevře se okno editoru dotazů, kde můžete vidět načtená data a název dotazu Tabulka 1 (pokud chcete, můžete jej změnit na svůj vlastní).
4. Dvakrát klikněte na záhlaví tabulky (word Seznam 1) a přejmenujte jej na jakýkoli jiný (např Lidé). Co přesně pojmenovat není důležité, ale vymyšlený název je třeba si zapamatovat, protože. bude muset být znovu použit později při importu druhé tabulky. Sloučení dvou tabulek v budoucnu bude fungovat pouze tehdy, budou-li se jejich záhlaví sloupců shodovat.
5. Rozbalte rozevírací seznam v levém horním rohu zavřít a stáhnout A zvolte Zavřít a načíst… (Zavřít a načíst do…):

   

6. V dalším dialogovém okně (může vypadat trochu jinak – nelekejte se) vyberte Stačí vytvořit spojení (Pouze vytvořit připojení):

   

7. Celý postup (body 2-6) opakujeme u druhého seznamu. Při přejmenování záhlaví sloupce je důležité použít stejný název (Lidé) jako v předchozím dotazu.
8. V okně Excelu na záložce Data nebo na kartě Dotaz na napájení Vybrat Získat data – Kombinovat požadavky – Přidat (Získat data — Sloučit dotazy — Připojit):

   

9. V zobrazeném dialogovém okně vyberte z rozevíracích seznamů naše požadavky:

   

10. Ve výsledku dostaneme nový dotaz, kde se pod sebe propojí dva seznamy. Zbývá odstranit duplikáty tlačítkem Odstranit řádky – Odstranit duplikáty (Odstranit řádky – odstranit duplikáty):

    

11. Hotový dotaz lze přejmenovat na pravé straně panelu možností a dát mu rozumný název (ve skutečnosti to bude název výsledkové tabulky) a vše lze nahrát na list pomocí příkazu zavřít a stáhnout (Zavřít a načíst):

    

V budoucnu bude při jakýchkoli změnách nebo doplnění původních seznamů stačit pouze kliknout pravým tlačítkem a aktualizovat tabulku výsledků.

• Jak shromáždit více tabulek z různých souborů pomocí Power Query
• Extrahování jedinečných položek ze seznamu
• Jak porovnat dva seznamy mezi sebou pro shody a rozdíly