Spajanje dveh seznamov brez dvojnikov

Klasična situacija: imate dva seznama, ki ju je treba združiti v enega. Poleg tega so lahko na začetnih seznamih edinstveni elementi in ujemajoči se (tako med seznami kot znotraj), vendar morate na izhodu dobiti seznam brez dvojnikov (ponovitev):

Oglejmo si tradicionalno več načinov za rešitev tako pogostega problema - od primitivnega "na čelu" do bolj zapletenega, a elegantnega.

1. način: Odstranite dvojnike

Težavo lahko rešite na najpreprostejši način – ročno kopirate elemente obeh seznamov v enega in nato uporabite orodje za nastali niz. Odstrani dvojnike iz zavihka datum (Podatki — Odstrani dvojnike):

Ta metoda seveda ne bo delovala, če se podatki v izvornih seznamih pogosto spreminjajo – po vsaki spremembi boste morali celoten postopek znova ponoviti. 

Metoda 1a. vrteča miza

Ta metoda je pravzaprav logično nadaljevanje prejšnje. Če seznami niso zelo veliki in je največje število elementov v njih znano vnaprej (na primer ne več kot 10), potem lahko dve tabeli združite v eno z neposrednimi povezavami, dodate stolpec z enimi na desni in na podlagi dobljene tabele sestavite zbirno tabelo:

Kot veste, vrtilna tabela ne upošteva ponovitev, zato bomo na izhodu dobili kombiniran seznam brez dvojnikov. Pomožni stolpec z 1 je potreben samo zato, ker lahko Excel sestavi tabele povzetkov, ki vsebujejo vsaj dva stolpca.

Ko se prvotni seznami spremenijo, bodo novi podatki šli v združeno tabelo prek neposrednih povezav, vrtilno tabelo pa bo treba posodobiti ročno (desni klik – Posodobi in shrani). Če ne potrebujete sprotnega preračunavanja, je bolje uporabiti druge možnosti.

2. način: matrična formula

Težavo lahko rešite s formulami. V tem primeru se bo ponoven izračun in posodobitev rezultatov zgodila samodejno in takoj, takoj po spremembi prvotnih seznamov. Zaradi priročnosti in jedrnatosti dajmo našim seznamom imena. Seznam 1 и Seznam 2uporabo Upravitelj imen tab Formula (Formule — Upravitelj imen — Ustvari):

Po imenovanju bo formula, ki jo potrebujemo, videti takole:

Na prvi pogled je videti grozljivo, a v resnici vse ni tako strašljivo. Naj to formulo razširim v več vrstic s kombinacijo tipk Alt+Enter in jo zamaknem s presledki, kot smo to storili na primer tukaj:

Logika tukaj je naslednja:

• Formula INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) izbere vse edinstvene elemente s prvega seznama. Takoj, ko jih zmanjka, začne izdajati napako #N/A:

  

• Formula INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) na enak način izvleče edinstvene elemente z drugega seznama.
• Dve funkciji IFERROR, ugnezdeni druga v drugo, implementirata izhod najprej unikatnih s seznama-1, nato pa enega za drugim s seznama-2.

Upoštevajte, da je to matrična formula, tj. po vnosu jo je treba vnesti v celico, ki ni navadna Vnesite, vendar z bližnjico na tipkovnici Ctrl+Shift+Vnesite in nato kopirajte (povlecite) navzdol do podrejenih celic z robom.

V angleški različici Excela je ta formula videti takole:

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

Slaba stran tega pristopa je, da matrične formule opazno upočasnijo delo z datoteko, če imajo izvorne tabele veliko (nekaj sto ali več) število elementov. 

3. način. Power Query

Če imajo vaši izvorni seznami veliko število elementov, na primer več sto ali tisoč, potem je namesto počasne matrične formule bolje uporabiti bistveno drugačen pristop, in sicer orodja dodatka Power Query. Ta dodatek je privzeto vgrajen v Excel 2016. Če imate Excel 2010 ali 2013, ga lahko prenesete in namestite ločeno (brezplačno).

Algoritem dejanj je naslednji:

1. Odprite ločen zavihek nameščenega dodatka poizvedba o moči (če imate Excel 2010-2013) ali preprosto pojdite na zavihek datum (če imate Excel 2016).
2. Izberite prvi seznam in pritisnite gumb Iz tabele/razpona (Iz obsega/tabele). Na vprašanje o ustvarjanju "pametne tabele" z našega seznama se strinjamo:

   

3. Odpre se okno urejevalnika poizvedb, kjer si lahko ogledate naložene podatke in ime poizvedbe Tabela 1 (če želite, ga lahko spremenite v svojega).
4. Dvakrat kliknite na glavo tabele (word Seznam 1) in ga preimenujte v katero koli drugo (npr ljudje). Kaj natančno poimenovati, ni pomembno, vendar si je treba zapomniti izmišljeno ime, ker. pozneje ga bo treba ponovno uporabiti pri uvozu druge tabele. Združevanje dveh tabel v prihodnosti bo delovalo le, če se njuni naslovi stolpcev ujemata.
5. Razširite spustni seznam v zgornjem levem kotu zaprite in prenesite In izberite Zapri in naloži v… (Zapri&Naloži na ...):

   

6. V naslednjem pogovornem oknu (morda je videti nekoliko drugače – ne skrbite) izberite Samo ustvarite povezavo (Ustvari samo povezavo):

   

7. Celoten postopek (točke 2-6) ponovimo za drugo listo. Ko preimenujete naslov stolpca, je pomembno, da uporabite isto ime (Ljudje) kot v prejšnji poizvedbi.
8. V Excelovem oknu na zavihku datum ali na zavihku poizvedba o moči Izberite Pridobi podatke – Združi zahteve – Dodaj (Pridobi podatke — Združi poizvedbe — Dodaj):

   

9. V pogovornem oknu, ki se prikaže, na spustnih seznamih izberite naše zahteve:

   

10. Posledično bomo dobili novo poizvedbo, kjer bosta dva seznama povezana drug pod drugim. Ostaja še odstranitev dvojnikov z gumbom Izbriši vrstice – Odstrani dvojnike (Brisanje vrstic — brisanje dvojnikov):

    

11. Končano poizvedbo lahko preimenujete na desni strani plošče z možnostmi in ji date zdravo ime (to bo pravzaprav ime tabele z rezultati) in vse lahko naložite na list z ukazom zaprite in prenesite (Zapri&Naloži):

    

V prihodnje bo ob kakršnih koli spremembah ali dopolnitvah prvotnih seznamov za posodobitev tabele rezultatov dovolj le desni klik.

• Kako zbrati več tabel iz različnih datotek s pomočjo Power Query
• Izvleček edinstvenih elementov s seznama
• Kako primerjati dva seznama med seboj za ujemanja in razlike