Zlúčenie dvoch zoznamov bez duplikátov

Klasická situácia: máte dva zoznamy, ktoré je potrebné zlúčiť do jedného. Navyše v počiatočných zoznamoch môžu byť jedinečné prvky aj zodpovedajúce prvky (medzi zoznamami aj vo vnútri), ale na výstupe musíte získať zoznam bez duplikátov (opakovaní):

Pozrime sa už tradične na niekoľko spôsobov riešenia takéhoto bežného problému – od primitívnych „na čelo“ až po zložitejšie, no elegantné.

Metóda 1: Odstráňte duplikáty

Problém môžete vyriešiť najjednoduchším spôsobom – ručne skopírujte prvky oboch zoznamov do jedného a následne aplikujte nástroj na výslednú množinu. Odstrániť duplikáty z karty dátum (Údaje – odstrániť duplikáty):

Táto metóda samozrejme nebude fungovať, ak sa údaje v zdrojových zoznamoch často menia – po každej zmene budete musieť celý postup zopakovať. 

Spôsob 1a. kontingenčnej tabuľky

Táto metóda je v skutočnosti logickým pokračovaním predchádzajúcej. Ak zoznamy nie sú príliš veľké a maximálny počet prvkov v nich je známy vopred (napríklad nie viac ako 10), potom môžete spojiť dve tabuľky do jednej pomocou priamych odkazov, pridať stĺpec s jednotkami vpravo a zostavte súhrnnú tabuľku na základe výslednej tabuľky:

Ako viete, kontingenčná tabuľka ignoruje opakovania, takže na výstupe dostaneme kombinovaný zoznam bez duplikátov. Pomocný stĺpec s 1 je potrebný len preto, že Excel dokáže zostaviť súhrnné tabuľky obsahujúce aspoň dva stĺpce.

Keď sa pôvodné zoznamy zmenia, nové údaje prejdú do kombinovanej tabuľky prostredníctvom priamych odkazov, ale kontingenčnú tabuľku bude potrebné aktualizovať manuálne (kliknutie pravým tlačidlom myši – Aktualizovať a uložiť). Ak nepotrebujete prepočet za chodu, potom je lepšie použiť iné možnosti.

Metóda 2: Vzorec poľa

Problém môžete vyriešiť pomocou vzorcov. V tomto prípade dôjde k prepočítaniu a aktualizácii výsledkov automaticky a okamžite, ihneď po zmenách v pôvodných zoznamoch. Pre pohodlie a stručnosť dajme zoznamom názvy. Zoznam 1 и Zoznam 2a využitím Správca mien pútko vzorec (Vzorce — Správca mien — Vytvoriť):

Po pomenovaní bude vzorec, ktorý potrebujeme, vyzerať takto:

Na prvý pohľad to vyzerá strašidelne, ale v skutočnosti nie je všetko také strašidelné. Dovoľte mi rozšíriť tento vzorec na niekoľko riadkov pomocou kombinácie klávesov Alt+Enter a odsadenia medzerami, ako sme to urobili napríklad tu:

Logika je tu nasledovná:

• Vzorec INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) vyberie všetky jedinečné prvky z prvého zoznamu. Hneď ako sa minú, začne vykazovať chybu #N/A:

  

• Vzorec INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) extrahuje jedinečné prvky z druhého zoznamu rovnakým spôsobom.
• Dve funkcie IFERROR vnorené do seba implementujú výstup najprv z jedinečných zo zoznamu-1 a potom zo zoznamu-2 jednu po druhej.

Všimnite si, že ide o maticový vzorec, tj po napísaní ho treba zadať do bunky, ktorá nie je obyčajná vstúpiť, ale pomocou klávesovej skratky ctrl+smena+vstúpiť a potom skopírujte (potiahnite) nadol do podriadených buniek s okrajom.

V anglickej verzii Excelu tento vzorec vyzerá takto:

=IFERROR(IFERROR(INDEX(Zoznam1, ZHODA(0, COUNTIF($E$1:E1, Zoznam1), 0)), INDEX(Zoznam2, ZHODA(0, COUNTIF($E$1:E1, Zoznam2), 0)) ), „“) 

Nevýhodou tohto prístupu je, že vzorce poľa výrazne spomaľujú prácu so súborom, ak majú zdrojové tabuľky veľký (niekoľko stoviek alebo viac) prvkov. 

Metóda 3. Power Query

Ak majú vaše zdrojové zoznamy veľký počet prvkov, napríklad niekoľko stoviek alebo tisícok, potom je namiesto pomalého vzorca poľa lepšie použiť zásadne odlišný prístup, konkrétne nástroje doplnku Power Query. Tento doplnok je predvolene zabudovaný do Excelu 2016. Ak máte Excel 2010 alebo 2013, môžete si ho stiahnuť a nainštalovať samostatne (zadarmo).

Algoritmus akcií je nasledujúci:

1. Otvorte samostatnú kartu nainštalovaného doplnku mocenský dotaz (ak máte Excel 2010-2013) alebo jednoducho prejdite na kartu dátum (ak máte Excel 2016).
2. Vyberte prvý zoznam a stlačte tlačidlo Z tabuľky/rozsahu (Z rozsahu/tabuľky). Na otázku o vytvorení „inteligentnej tabuľky“ z nášho zoznamu súhlasíme:

   

3. Otvorí sa okno editora dotazov, kde môžete vidieť načítané údaje a názov dotazu Tabuľka 1 (ak chcete, môžete ho zmeniť na svoj vlastný).
4. Dvakrát kliknite na hlavičku tabuľky (slov Zoznam 1) a premenujte ho na akékoľvek iné (napr ľudia). Čo presne pomenovať nie je dôležité, ale vymyslený názov si treba zapamätať, pretože. bude sa musieť použiť neskôr pri importe druhej tabuľky. Zlúčenie dvoch tabuliek v budúcnosti bude fungovať len vtedy, ak sa ich hlavičky stĺpcov zhodujú.
5. Rozbaľte rozbaľovací zoznam v ľavom hornom rohu zavrieť a stiahnuť A vyberte Zavrieť a načítať… (Zavrieť a načítať do...):

   

6. V ďalšom dialógovom okne (môže vyzerať trochu inak – nezľaknite sa) vyberte Stačí vytvoriť spojenie (Iba vytvoriť spojenie):

   

7. Celý postup (body 2-6) zopakujeme pre druhý zoznam. Pri premenovávaní nadpisu stĺpca je dôležité použiť rovnaký názov (Ľudia) ako v predchádzajúcom dotaze.
8. V okne programu Excel na karte dátum alebo na karte mocenský dotaz Vybrať Získať údaje – Kombinovať požiadavky – Pridať (Získať údaje – Zlúčiť dopyty – Pripojiť):

   

9. V zobrazenom dialógovom okne vyberte z rozbaľovacích zoznamov naše požiadavky:

   

10. Vo výsledku dostaneme nový dotaz, kde budú pod sebou prepojené dva zoznamy. Zostáva odstrániť duplikáty pomocou tlačidla Odstrániť riadky – Odstrániť duplikáty (Odstrániť riadky – odstrániť duplikáty):

    

11. Hotový dotaz je možné premenovať na pravej strane panela možností a dať mu rozumný názov (v skutočnosti to bude názov výslednej tabuľky) a všetko je možné nahrať na hárok pomocou príkazu zavrieť a stiahnuť (Zavrieť a načítať):

    

V budúcnosti pri akýchkoľvek zmenách alebo doplnkoch pôvodných zoznamov bude stačiť kliknúť pravým tlačidlom myši a aktualizovať tabuľku výsledkov.

• Ako zhromaždiť viacero tabuliek z rôznych súborov pomocou Power Query
• Extrahovanie jedinečných položiek zo zoznamu
• Ako porovnať dva zoznamy medzi sebou pre zhody a rozdiely