Kahe loendi ühendamine ilma duplikaatideta

Klassikaline olukord: teil on kaks loendit, mis tuleb üheks liita. Veelgi enam, esialgsetes loendites võib olla nii unikaalseid elemente kui ka sobivaid elemente (nii loendite vahel kui ka sees), kuid väljundis peate saama loendi ilma duplikaatideta (kordusteta):

Vaatame traditsiooniliselt mitmeid viise sellise levinud probleemi lahendamiseks – primitiivsest “otsmikul” kuni keerukama, kuid elegantseni.

1. meetod: eemaldage duplikaadid

Probleemi saate lahendada kõige lihtsamal viisil – kopeerige mõlema loendi elemendid käsitsi ühte ja seejärel rakendage tööriist saadud komplektile. Eemalda duplikaadid vahekaardilt kuupäev (Andmed – eemalda duplikaadid):

Muidugi ei tööta see meetod, kui allikaloendis olevad andmed sageli muutuvad – pärast iga muudatust tuleb kogu protseduuri uuesti korrata. 

Meetod 1a. pöördetabel

See meetod on tegelikult eelmise loogiline jätk. Kui loendid ei ole väga suured ja nendes olevate elementide maksimaalne arv on ette teada (näiteks mitte rohkem kui 10), saate kaks tabelit otselinkide abil üheks liita, lisada veerg paremal olevate ühega ja koostage saadud tabeli põhjal kokkuvõtlik tabel:

Nagu teate, ignoreerib pivot-tabel kordusi, nii et väljundis saame kombineeritud loendi ilma duplikaatideta. Abiveerg 1-ga on vajalik ainult seetõttu, et Excel suudab koostada kokkuvõtlikke tabeleid, mis sisaldavad vähemalt kahte veergu.

Algsete loendite muutmisel lähevad uued andmed otselinkide kaudu kombineeritud tabelisse, kuid pivot-tabelit tuleb käsitsi värskendada (paremklõps – Värskenda ja salvesta). Kui te ei vaja käigu pealt ümberarvutamist, siis on parem kasutada muid võimalusi.

2. meetod: massiivi valem

Probleemi saate lahendada valemitega. Sel juhul toimub tulemuste ümberarvutamine ja värskendamine automaatselt ja koheselt, kohe pärast muudatusi esialgsetes loendites. Mugavuse ja lühiduse huvides anname oma loenditele nimed. Nimekiri 1 и Nimekiri 2kasutamine Nimehaldur tab valem (Valemid – nimehaldur – loomine):

Pärast nime andmist näeb meile vajalik valem välja järgmine:

Esmapilgul tundub see jube, kuid tegelikult pole kõik nii hirmutav. Lubage mul seda valemit laiendada mitmele reale, kasutades Alt+Enter klahvikombinatsiooni ja taanet tühikutega, nagu tegime näiteks siin:

Loogika on siin järgmine:

• Valem INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) valib esimesest loendist kõik unikaalsed elemendid. Niipea kui need otsa saavad, hakkab see andma #N/A viga:

  

• Valem INDEX(Loend2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) ekstraheerib unikaalsed elemendid teisest loendist samal viisil.
• Üksteisesse pesatud kaks IFERROR-funktsiooni realiseerivad esmalt loendi-1 unikaalsete väljundi ja seejärel üksteise järel loendist-2.

Pange tähele, et tegemist on massiivivalemiga, st pärast tippimist tuleb see sisestada lahtrisse, mis pole tavaline sisene, kuid kiirklahviga Ctrl+nihe+sisene ja seejärel kopeerige (lohistage) veerisega alamlahtritesse.

Exceli ingliskeelses versioonis näeb see valem välja järgmine:

=IFERROR(IFERROR(INDEKS(loend1, MATCH(0, LOENDIF($E$1:E1, loend1), 0)), INDEX(loend2, MATCH(0, COUNTIF($E$1:E1, loend 2), 0)) ), ") 

Selle lähenemisviisi negatiivne külg on see, et massiivivalemid aeglustavad märkimisväärselt tööd failiga, kui lähtetabelites on suur (mitusada või enam) elemente. 

3. meetod. Power Query

Kui teie allikaloendites on palju elemente, näiteks mitusada või tuhat, siis on aeglase massiivivalemi asemel parem kasutada põhimõtteliselt teistsugust lähenemist, nimelt Power Query lisatööriistu. See lisandmoodul on vaikimisi sisse ehitatud Excel 2016-sse. Kui teil on Excel 2010 või 2013, saate selle eraldi alla laadida ja installida (tasuta).

Toimingute algoritm on järgmine:

1. Avage installitud lisandmooduli eraldi vahekaart Toite päring (kui teil on Excel 2010–2013) või minge lihtsalt vahekaardile kuupäev (kui teil on Excel 2016).
2. Valige esimene loend ja vajutage nuppu Tabelist/vahemikust (vahemikust/tabelist). Kui meilt küsitakse meie loendist nutika tabeli loomise kohta, nõustume:

   

3. Avaneb päringuredaktori aken, kus näete laetud andmeid ja päringu nime Tabel 1 (saate selle soovi korral enda omaks muuta).
4. Topeltklõps tabeli päisel (sõna Nimekiri 1) ja nimetage see ümber mis tahes muuks (näiteks Inimesed). Mida täpselt nimetada, pole oluline, kuid väljamõeldud nimi tuleb meeles pidada, sest. seda tuleb hiljem teise tabeli importimisel uuesti kasutada. Kahe tabeli liitmine tulevikus toimib ainult siis, kui nende veergude pealkirjad ühtivad.
5. Laiendage vasakus ülanurgas olevat rippmenüüd sulgege ja laadige alla Ja vali Sulgege ja laadige sisse… (Sule&laadimine…):

   

6. Valige järgmises dialoogiboksis (see võib veidi teistsugune välja näha – ärge kartke). Looge lihtsalt ühendus (Loo ainult ühendus):

   

7. Kordame kogu protseduuri (punktid 2-6) teise loendi jaoks. Veeru pealkirja ümbernimetamisel on oluline kasutada sama nime (Inimesed), mis eelmises päringus.
8. Vahekaardil Exceli aknas kuupäev või vahekaardil Toite päring Vali Hangi andmed – ühenda taotlused – lisa (Hangi andmed – Ühenda päringud – lisa):

   

9. Ilmuvas dialoogiboksis valige ripploenditest meie taotlused.

   

10. Selle tulemusena saame uue päringu, kus kaks loendit ühendatakse üksteise alla. Jääb üle duplikaadid nupuga eemaldada Kustuta read – eemaldage duplikaadid (Kustuta read – kustuta duplikaadid):

    

11. Valmis päringu saab ümber nimetada valikute paneeli paremas servas, andes sellele mõistliku nime (see on tegelikult tulemuste tabeli nimi) ja kõik saab käsuga lehele üles laadida sulgege ja laadige alla (Sule&laadimine):

    

Edaspidi piisab algsete loendite muudatuste või täienduste korral tulemuste tabeli värskendamiseks paremklõpsamisest.

• Kuidas Power Query abil erinevatest failidest mitu tabelit koguda
• Unikaalsete üksuste loendist eraldamine
• Kuidas võrrelda kahte loendit vastete ja erinevuste osas