Spajanje dva popisa bez duplikata

Klasična situacija: imate dvije liste koje treba spojiti u jednu. Štoviše, u početnim popisima mogu postojati i jedinstveni elementi i oni koji se podudaraju (i između popisa i iznutra), ali na izlazu morate dobiti popis bez duplikata (ponavljanja):

Tradicionalno pogledajmo nekoliko načina rješavanja tako uobičajenog problema - od primitivnog "na čelu" do složenijeg, ali elegantnog.

Metoda 1: Uklonite duplikate

Problem možete riješiti na najjednostavniji način – ručno kopirajte elemente oba popisa u jedan i zatim primijenite alat na dobiveni skup. Ukloni duplikate s kartice Datum (Podaci — Ukloni duplikate):

Naravno, ova metoda neće raditi ako se podaci u izvornim listama često mijenjaju – morat ćete ponoviti cijeli postupak nakon svake promjene. 

Metoda 1a. stožerna tablica

Ova metoda je, zapravo, logičan nastavak prethodne. Ako popisi nisu jako veliki i maksimalni broj elemenata u njima je unaprijed poznat (na primjer, ne više od 10), tada možete spojiti dvije tablice u jednu izravnim vezama, dodati stupac s onima s desne strane i izradite tablicu sažetka na temelju dobivene tablice:

Kao što znate, stožerna tablica zanemaruje ponavljanja, tako da ćemo na izlazu dobiti kombinirani popis bez duplikata. Pomoćni stupac s 1 potreban je samo zato što Excel može izgraditi sažete tablice koje sadrže najmanje dva stupca.

Kada se izvorni popisi promijene, novi podaci će ići u kombiniranu tablicu putem izravnih veza, ali zaokretna tablica morat će se ažurirati ručno (desni klik – Ažurirajte i spremite). Ako vam ne treba ponovno izračunavanje u hodu, onda je bolje koristiti druge opcije.

Metoda 2: Formula polja

Problem možete riješiti formulama. U tom će se slučaju ponovno izračunavanje i ažuriranje rezultata dogoditi automatski i trenutno, odmah nakon promjena u izvornim popisima. Radi praktičnosti i sažetosti, dajmo našim popisima imena. Popis 1 и Popis 2koristeći Upravitelj imena kartica formula (Formule — Upravitelj naziva — Stvori):

Nakon imenovanja, formula koja nam treba izgledat će ovako:

Na prvi pogled izgleda jezivo, ali zapravo sve nije tako strašno. Dopustite mi da proširim ovu formulu u nekoliko redaka pomoću kombinacije tipki Alt+Enter i uvlake s razmacima, kao što smo učinili, na primjer ovdje:

Logika je ovdje sljedeća:

• Formula INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) odabire sve jedinstvene elemente s prve liste. Čim ih ponestane, počinje davati grešku #N/A:

  

• Formula INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) izdvaja jedinstvene elemente iz druge liste na isti način.
• Ugniježđene jedna u drugu dvije funkcije IFERROR implementiraju izlaz prvo jedinstvenih s liste-1, a zatim s liste-2 jednu za drugom.

Imajte na umu da je ovo formula polja, tj. nakon upisivanja mora se unijeti u ćeliju koja nije obična ući, ali s tipkovničkim prečacem Ctrl+smjena+ući a zatim kopirajte (povucite) prema dolje do podređenih ćelija s marginom.

U engleskoj verziji programa Excel ova formula izgleda ovako:

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), “”) 

Loša strana ovog pristupa je da formule polja značajno usporavaju rad s datotekom ako izvorne tablice imaju velik (nekoliko stotina ili više) broj elemenata. 

Metoda 3. Power Query

Ako vaši izvorni popisi imaju veliki broj elemenata, na primjer, nekoliko stotina ili tisuća, tada je umjesto spore formule niza bolje koristiti bitno drugačiji pristup, naime alate za dodatke Power Query. Ovaj je dodatak ugrađen u Excel 2016 prema zadanim postavkama. Ako imate Excel 2010 ili 2013, možete ga preuzeti i instalirati zasebno (besplatno).

Algoritam postupaka je sljedeći:

1. Otvorite zasebnu karticu instaliranog dodatka Upit snage (ako imate Excel 2010-2013) ili jednostavno idite na karticu Datum (ako imate Excel 2016).
2. Odaberite prvi popis i pritisnite gumb Iz tablice/raspona (Iz raspona/tablice). Na pitanje o stvaranju "pametne tablice" s našeg popisa, slažemo se:

   

3. Otvara se prozor uređivača upita u kojem možete vidjeti učitane podatke i naziv upita Tablica 1 (možete ga promijeniti u svoj ako želite).
4. Dvaput kliknite na zaglavlje tablice (word Popis 1) i preimenujte ga u bilo koji drugi (npr ljudi). Što točno imenovati nije važno, ali izmišljeno ime mora se zapamtiti, jer. morat će se ponovno koristiti kasnije prilikom uvoza druge tablice. Spajanje dviju tablica u budućnosti će funkcionirati samo ako se naslovi njihovih stupaca podudaraju.
5. Proširite padajući popis u gornjem lijevom kutu zatvorite i preuzmite I odaberite Zatvorite i učitajte u… (Zatvori&Učitaj u...):

   

6. U sljedećem dijaloškom okviru (možda će izgledati malo drugačije – nemojte se uznemiriti), odaberite Samo stvorite vezu (Samo stvoriti vezu):

   

7. Cijeli postupak (točke 2-6) ponavljamo za drugu listu. Kada mijenjate naziv naslova stupca, važno je koristiti isti naziv (Osobe) kao u prethodnom upitu.
8. U Excel prozoru na kartici Datum ili na kartici Upit snage Odaberite Dohvati podatke – Kombiniraj zahtjeve – Dodaj (Dohvati podatke — Spoji upite — Dodaj):

   

9. U dijaloškom okviru koji se pojavi odaberite naše zahtjeve s padajućih popisa:

   

10. Kao rezultat toga, dobit ćemo novi upit, gdje će dvije liste biti povezane jedna ispod druge. Ostaje ukloniti duplikate pomoću gumba Izbriši retke – Ukloni duplikate (Brisanje redaka — brisanje duplikata):

    

11. Gotov upit se može preimenovati na desnoj strani ploče s opcijama, dajući mu zdravo ime (ovo će zapravo biti naziv tablice rezultata) i sve se može prenijeti na list s naredbom zatvorite i preuzmite (Zatvori&Učitaj):

    

Ubuduće, kod bilo kakvih izmjena ili dopuna izvornih popisa, za ažuriranje tablice rezultata bit će dovoljan samo desni klik.

• Kako prikupiti više tablica iz različitih datoteka pomoću Power Queryja
• Izdvajanje jedinstvenih stavki s popisa
• Kako usporediti dva popisa međusobno za podudaranja i razlike