Spajanje dvije liste bez duplikata

Klasična situacija: imate dvije liste koje je potrebno spojiti u jednu. Štoviše, u početnim listama mogu biti i jedinstveni elementi i oni koji se podudaraju (i između lista i iznutra), ali na izlazu morate dobiti listu bez duplikata (ponavljanja):

Pogledajmo tradicionalno nekoliko načina rješavanja tako uobičajenog problema - od primitivnog "na čelu" do složenijih, ali elegantnijih.

Metoda 1: Uklonite duplikate

Problem možete riješiti na najjednostavniji način – ručno kopirajte elemente obje liste u jednu, a zatim primijenite alat na rezultirajući skup. Ukloni duplikate sa kartice podaci (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. pivot table

Ova metoda je, zapravo, logičan nastavak prethodne. Ako liste nisu jako velike i maksimalan broj elemenata u njima je unaprijed poznat (na primjer, ne više od 10), tada možete spojiti dvije tabele u jednu direktnim vezama, dodati kolonu s onima na desnoj strani i napravite zbirnu tabelu na osnovu rezultirajuće tabele:

Kao što znate, stožerna tabela ignoriše ponavljanja, tako da ćemo na izlazu dobiti kombinovanu listu bez duplikata. Pomoćna kolona sa 1 potrebna je samo zato što Excel može da napravi zbirne tabele koje sadrže najmanje dve kolone.

Kada se originalne liste promijene, novi podaci će ići u kombinovanu tabelu putem direktnih veza, ali će se stožerna tabela morati ručno ažurirati (desni klik – Ažurirajte i sačuvajte). Ako vam nije potrebno ponovno izračunavanje u hodu, onda je bolje koristiti druge opcije.

Metoda 2: Formula niza

Problem možete riješiti formulama. U ovom slučaju, ponovno izračunavanje i ažuriranje rezultata će se dogoditi automatski i trenutno, odmah nakon promjena u originalnim listama. Radi praktičnosti i sažetosti, dajmo našim listama imena. List 1 и List 2korišćenje Ime Manager tabulator formula (Formule — Menadžer imena — Kreirajte):

Nakon imenovanja, formula koja nam je potrebna izgledat će ovako:

Na prvi pogled izgleda jezivo, ali, zapravo, nije sve tako strašno. Dozvolite mi da proširim ovu formulu na nekoliko redaka koristeći kombinaciju tipki Alt+Enter i uvučem razmake, kao što smo to učinili, na primjer ovdje:

Logika je sljedeća:

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

  

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

Imajte na umu da je ovo formula niza, tj. nakon kucanja, mora se unijeti u ćeliju koja nije obična ući, ali sa prečicom na tastaturi Ctrl+smjena+ući a zatim kopirajte (prevucite) dolje u podređene ćelije sa marginom.

U engleskoj verziji Excela ova formula izgleda ovako:

=IFGREŠKA(IFERROR(INDEX(Lista1, PODRŽAVANJE(0, BROJEVI($E$1:E1, Lista1), 0)), INDEX(Lista2, PODRŽAVANJE(0, BROJEVO($E$1:E1, Lista2), 0)) ), “”) 

Nedostatak ovog pristupa je da formule niza primjetno usporavaju rad sa datotekom ako izvorne tabele imaju veliki (nekoliko stotina ili više) broj elemenata. 

Metod 3. Power Query

Ako vaše izvorne liste imaju veliki broj elemenata, na primjer, nekoliko stotina ili tisuća, onda je umjesto spore formule niza bolje koristiti fundamentalno drugačiji pristup, odnosno Power Query dodatke. Ovaj dodatak je podrazumevano ugrađen u Excel 2016. Ako imate Excel 2010 ili 2013, možete ga zasebno preuzeti i instalirati (besplatno).

Algoritam radnji je sljedeći:

1. Otvorite zasebnu karticu instaliranog dodatka power query (ako imate Excel 2010-2013) ili jednostavno idite na karticu podaci (ako imate Excel 2016).
2. Odaberite prvu listu i pritisnite dugme Iz tabele/opseg (Iz dometa/tabele). Na pitanje o kreiranju "pametnog stola" sa naše liste, slažemo se:

   

3. Otvara se prozor uređivača upita u kojem možete vidjeti učitane podatke i naziv upita Tabela 1 (možete ga promijeniti u svoj ako želite).
4. Dvaput kliknite na zaglavlje tabele (word List 1) i preimenujte ga u bilo koji drugi (npr ljudi). Kako tačno imenovati nije važno, ali izmišljeno ime se mora zapamtiti, jer. moraće se ponovo koristiti kasnije prilikom uvoza druge tabele. Spajanje dvije tabele u budućnosti će raditi samo ako se naslovi njihovih kolona podudaraju.
5. Proširite padajuću listu u gornjem lijevom uglu zatvorite i preuzmite i odaberite Zatvorite i učitajte u… (Zatvori i učitaj u…):

   

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

   

7. Ponavljamo cijeli postupak (tačke 2-6) za drugu listu. Prilikom preimenovanja zaglavlja kolone, važno je koristiti isto ime (Ljudi) kao u prethodnom upitu.
8. U prozoru Excel na kartici podaci ili na kartici power query Izabrati Dobijte podatke – Kombinirajte zahtjeve – Dodajte (Dobijte podatke — Spojite upite — Dodajte):

   

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

   

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

    

11. Završeni upit se može preimenovati na desnoj strani panela sa opcijama, dajući mu zdravo ime (to će u stvari biti ime tabele rezultata) i sve se može učitati na list pomoću naredbe zatvorite i preuzmite (Zatvori&Učitaj):

    

U budućnosti, sa bilo kakvim promjenama ili dopunama originalnih lista, biće dovoljno samo kliknuti desnim tasterom miša da ažurirate tabelu rezultata.

• Kako prikupiti više tabela iz različitih datoteka koristeći Power Query
• Izdvajanje jedinstvenih stavki sa liste
• Kako uporediti dvije liste jedna s drugom za podudarnosti i razlike