Îmbinarea a două liste fără duplicate

O situație clasică: aveți două liste care trebuie îmbinate într-una singură. Mai mult, în listele inițiale pot exista atât elemente unice, cât și cele care se potrivesc (atât între liste, cât și în interior), dar la ieșire trebuie să obțineți o listă fără duplicate (repetări):

Să ne uităm în mod tradițional la mai multe moduri de a rezolva o astfel de problemă comună – de la primitiv „pe frunte” la mai complex, dar elegant.

Metoda 1: Eliminați duplicatele

Puteți rezolva problema în cel mai simplu mod - copiați manual elementele ambelor liste într-una singură și apoi aplicați instrumentul la setul rezultat. Eliminați duplicatele din filă Date (Date — Eliminați duplicatele):

Desigur, această metodă nu va funcționa dacă datele din listele sursă se schimbă adesea – va trebui să repetați din nou întreaga procedură după fiecare modificare. 

Metoda 1a. masă rotativă

Această metodă este, de fapt, o continuare logică a celei anterioare. Dacă listele nu sunt foarte mari și numărul maxim de elemente din ele este cunoscut în avans (de exemplu, nu mai mult de 10), atunci puteți combina două tabele într-unul prin legături directe, adăugați o coloană cu cele din dreapta și construiți un tabel rezumat pe baza tabelului rezultat:

După cum știți, tabelul pivot ignoră repetările, așa că la ieșire vom obține o listă combinată fără duplicate. Coloana auxiliară cu 1 este necesară doar pentru că Excel poate construi tabele rezumative care conțin cel puțin două coloane.

Când listele originale sunt modificate, noile date vor merge în tabelul combinat prin link-uri directe, dar tabelul pivot va trebui actualizat manual (clic dreapta – Actualizați și salvați). Dacă nu aveți nevoie de recalculare din mers, atunci este mai bine să utilizați alte opțiuni.

Metoda 2: Formula matrice

Puteți rezolva problema cu formule. În acest caz, recalcularea și actualizarea rezultatelor vor avea loc automat și instantaneu, imediat după modificări în listele originale. Pentru comoditate și concizie, să dăm nume listelor noastre. Listează 1 и Listează 2folosind Manager nume fila formulă (Formule — Manager de nume — Creare):

După denumire, formula de care avem nevoie va arăta astfel:

La prima vedere, pare înfiorător, dar, de fapt, totul nu este atât de înfricoșător. Permiteți-mi să extind această formulă pe mai multe linii folosind combinația de taste Alt+Enter și indentare cu spații, așa cum am făcut, de exemplu aici:

Logica aici este următoarea:

• Formula INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) selectează toate elementele unice din prima listă. De îndată ce acestea se epuizează, începe să dea o eroare #N/A:

  

• Formula INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) extrage elementele unice din a doua listă în același mod.
• Imbricate una in alta doua functii IFERROR implementeaza iesirea mai intai dintre cele unice din lista-1, iar apoi din lista-2 una dupa alta.

Rețineți că aceasta este o formulă matrice, adică după tastare, trebuie introdusă într-o celulă care nu este obișnuită Intrați, dar cu o comandă rapidă de la tastatură Ctrl+Schimba+Intrați și apoi copiați (trageți) în jos în celulele copil cu o marjă.

În versiunea în limba engleză a Excel, această formulă arată astfel:

=DACA EROARE(DACĂ EROARE(INDEX(Lista1, POTRIVIRE(0, NUMĂRĂDAC($E$1:E1, Listă1), 0)), INDEX(Lista2, POTRIVIRE(0, NUMĂRĂDAC($E$1:E1, Lista2), 0)) ), „”) 

Dezavantajul acestei abordări este că formulele matrice încetinesc considerabil lucrul cu fișierul dacă tabelele sursă au un număr mare (câteva sute sau mai multe) de elemente. 

Metoda 3. Power Query

Dacă listele dvs. sursă au un număr mare de elemente, de exemplu, câteva sute sau mii, atunci în loc de o formulă matrice lentă, este mai bine să utilizați o abordare fundamental diferită, și anume instrumentele de completare Power Query. Acest program de completare este încorporat implicit în Excel 2016. Dacă aveți Excel 2010 sau 2013, îl puteți descărca și instala separat (gratuit).

Algoritmul acțiunilor este următorul:

1. Deschideți o filă separată a suplimentului instalat Interogare de alimentare (dacă aveți Excel 2010-2013) sau accesați fila Date (dacă aveți Excel 2016).
2. Selectați prima listă și apăsați butonul Din masă/gamă (Din interval/Tabel). Când sunt întrebați despre crearea unei „mese inteligente” din lista noastră, suntem de acord:

   

3. Se deschide fereastra editorului de interogări, unde puteți vedea datele încărcate și numele interogării Tabelul 1 (poți să-l schimbi cu al tău dacă vrei).
4. Faceți dublu clic pe antetul tabelului (cuvânt Listează 1) și redenumiți-l în oricare altul (de exemplu oameni). Ce anume să numești nu este important, dar numele inventat trebuie reținut, pentru că. va trebui folosit din nou mai târziu la importarea celui de-al doilea tabel. Îmbinarea a două tabele în viitor va funcționa numai dacă titlurile lor de coloană se potrivesc.
5. Extinde lista drop-down din colțul din stânga sus închideți și descărcați Și alegeți Închideți și încărcați în... (Închide&Încărcare la...):

   

6. În următoarea casetă de dialog (s-ar putea să arate puțin diferit – nu vă alarmați), selectați Doar creați o conexiune (Creați doar conexiune):

   

7. Repetăm ​​toată procedura (punctele 2-6) pentru a doua listă. La redenumirea unui titlu de coloană, este important să folosiți același nume (Persoane) ca în interogarea anterioară.
8. În fereastra Excel din filă Date sau pe filă Interogare de alimentare Alege Obține date – Combină solicitări – Adăugați (Obțineți date — Îmbinați interogări — Adăugați):

   

9. În caseta de dialog care apare, selectați cererile noastre din listele derulante:

   

10. Ca rezultat, vom obține o nouă interogare, în care două liste vor fi conectate una sub cealaltă. Rămâne să eliminați duplicatele cu butonul Ștergeți rândurile – Eliminați duplicatele (Ștergeți rândurile — Ștergeți duplicatele):

    

11. Interogarea terminată poate fi redenumită în partea dreaptă a panoului de opțiuni, dându-i un nume sănătos (acesta va fi, de fapt, numele tabelului de rezultate) și totul poate fi încărcat în foaie cu comanda închideți și descărcați (Închide&Încărcare):

    

În viitor, cu orice modificări sau completări la listele originale, va fi suficient să faceți clic dreapta pentru a actualiza tabelul cu rezultate.

• Cum să colectați mai multe tabele din fișiere diferite folosind Power Query
• Extragerea elementelor unice dintr-o listă
• Cum să compari două liste între ele pentru potriviri și diferențe