Twee lijsten samenvoegen zonder duplicaten

Een klassieke situatie: je hebt twee lijsten die moeten worden samengevoegd tot één. Bovendien kunnen er in de initiële lijsten zowel unieke als overeenkomende elementen zijn (zowel tussen de lijsten als binnenin), maar bij de uitvoer moet je een lijst krijgen zonder duplicaten (herhalingen):

Laten we traditioneel verschillende manieren bekijken om zo'n veelvoorkomend probleem op te lossen - van primitief "op het voorhoofd" tot meer complex, maar elegant.

Methode 1: Duplicaten verwijderen

U kunt het probleem op de eenvoudigste manier oplossen - kopieer de elementen van beide lijsten handmatig naar één en pas het hulpmiddel vervolgens toe op de resulterende set. Verwijder duplicaten van het tabblad Data (Gegevens — Duplicaten verwijderen):

Deze methode werkt natuurlijk niet als de gegevens in de bronlijsten vaak veranderen – je zult de hele procedure na elke wijziging opnieuw moeten herhalen. 

Methode 1a. draaitabel

Deze methode is in feite een logisch vervolg op de vorige. Als de lijsten niet erg groot zijn en het maximale aantal elementen erin van tevoren bekend is (bijvoorbeeld niet meer dan 10), dan kun je twee tabellen combineren tot één door directe links, een kolom toevoegen met aan de rechterkant en bouw een samenvattende tabel op basis van de resulterende tabel:

Zoals u weet, negeert de draaitabel herhalingen, dus bij de uitvoer krijgen we een gecombineerde lijst zonder duplicaten. De hulpkolom met 1 is alleen nodig omdat Excel samenvattende tabellen kan maken die ten minste twee kolommen bevatten.

Wanneer de originele lijsten worden gewijzigd, gaan de nieuwe gegevens via directe links naar de gecombineerde tabel, maar de draaitabel moet handmatig worden bijgewerkt (klik met de rechtermuisknop - Bijwerken en opslaan). Als u geen herberekening ter plekke nodig heeft, kunt u beter andere opties gebruiken.

Methode 2: Matrixformule

Je kunt het probleem oplossen met formules. In dit geval zal de herberekening en actualisering van de resultaten automatisch en onmiddellijk gebeuren, onmiddellijk na wijzigingen in de originele lijsten. Laten we voor het gemak en beknoptheid de namen van onze lijsten geven. lijst 1 и lijst 2gebruik Name Manager tab formule (Formules — Naam Manager — Aanmaken):

Na naamgeving ziet de formule die we nodig hebben er als volgt uit:

Op het eerste gezicht ziet het er griezelig uit, maar in feite is alles niet zo eng. Laat me deze formule uitbreiden over meerdere regels met behulp van de Alt+Enter-toetscombinatie en inspringen met spaties, zoals we deden, bijvoorbeeld hier:

De logica hier is de volgende:

• De formule INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) selecteert alle unieke elementen uit de eerste lijst. Zodra ze op zijn, geeft deze een #N/A-fout:

  

• De formule INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) haalt op dezelfde manier de unieke elementen uit de tweede lijst.
• In elkaar genest implementeren twee IFERROR-functies de uitvoer eerst van de unieke van de lijst-1 en vervolgens van de lijst-2 de een na de ander.

Merk op dat dit een matrixformule is, dat wil zeggen dat deze na het typen moet worden ingevoerd in een cel die niet gewoon is Enter, maar met een sneltoets Ctrl+Shift+Enter en kopieer (sleep) vervolgens naar de onderliggende cellen met een marge.

In de Engelse versie van Excel ziet deze formule er als volgt uit:

=IFERROR(IFERROR(INDEX(Lijst1, MATCH(0, AANTAL.ALS($E$1:E1, Lijst1), 0)), INDEX(Lijst2, MATCH(0, AANTAL.ALS($E$1:E1, Lijst2), 0)) ), “”) 

Het nadeel van deze benadering is dat matrixformules het werk met het bestand merkbaar vertragen als de brontabellen een groot (enkele honderden of meer) aantal elementen hebben. 

Methode 3. Power-query

Als uw bronlijsten een groot aantal elementen bevatten, bijvoorbeeld enkele honderden of duizenden, dan is het beter om in plaats van een langzame matrixformule een fundamenteel andere benadering te gebruiken, namelijk de Power Query-invoegtoepassingen. Deze invoegtoepassing is standaard ingebouwd in Excel 2016. Als u Excel 2010 of 2013 heeft, kunt u deze apart (gratis) downloaden en installeren.

Het algoritme van acties is als volgt:

1. Open een apart tabblad van de geïnstalleerde add-on Power Query (als je Excel 2010-2013 hebt) of ga gewoon naar het tabblad Data (als je Excel 2016 hebt).
2. Selecteer de eerste lijst en druk op de knop Van tafel/bereik (Van bereik/tabel). Op de vraag over het maken van een "slimme tafel" uit onze lijst, zijn we het erover eens:

   

3. Het venster van de query-editor wordt geopend, waar u de geladen gegevens en de naam van de query kunt zien Tabel 1 (u kunt het naar uw eigen wens wijzigen).
4. Dubbelklik op de tabelkop (word lijst 1) en hernoem het naar een ander (bijvoorbeeld Mensen). Wat precies te noemen is niet belangrijk, maar de verzonnen naam moet onthouden worden, want. het zal later opnieuw moeten worden gebruikt bij het importeren van de tweede tabel. Het samenvoegen van twee tabellen in de toekomst werkt alleen als hun kolomkoppen overeenkomen.
5. Vouw de vervolgkeuzelijst in de linkerbovenhoek uit sluiten en downloaden En kies Sluiten en laden in… (Sluiten&Laden naar...):

   

6. In het volgende dialoogvenster (het ziet er misschien een beetje anders uit - wees niet ongerust), selecteer Maak gewoon een verbinding (Alleen verbinding maken):

   

7. We herhalen de hele procedure (punten 2-6) voor de tweede lijst. Bij het hernoemen van een kolomkop is het belangrijk om dezelfde naam (Mensen) te gebruiken als in de vorige zoekopdracht.
8. In het Excel-venster op het tabblad Data of op het tabblad Power Query Kies Gegevens ophalen – Aanvragen combineren – Toevoegen (Gegevens ophalen - Query's samenvoegen - Toevoegen):

   

9. Selecteer in het dialoogvenster dat verschijnt onze verzoeken in de vervolgkeuzelijsten:

   

10. Als resultaat krijgen we een nieuwe zoekopdracht, waarbij twee lijsten onder elkaar worden gekoppeld. Het blijft om duplicaten te verwijderen met de knop Rijen verwijderen – Duplicaten verwijderen (Rijen verwijderen - Duplicaten verwijderen):

    

11. De voltooide query kan worden hernoemd aan de rechterkant van het optiepaneel, waardoor het een normale naam krijgt (dit zal in feite de naam zijn van de resultatentabel) en alles kan naar het blad worden geüpload met de opdracht sluiten en downloaden (Sluiten & laden):

    

In de toekomst, met eventuele wijzigingen of toevoegingen aan de originele lijsten, is het voldoende om met de rechtermuisknop te klikken om de resultatentabel bij te werken.

• Meerdere tabellen uit verschillende bestanden verzamelen met Power Query
• Unieke items uit een lijst halen
• Hoe twee lijsten met elkaar te vergelijken voor overeenkomsten en verschillen