Hoe tabellen samenvoegen in Excel: Power Query vs. Wizard Tabellen samenvoegen

In deze zelfstudie bekijken we hoe u tabellen in Excel kunt samenvoegen op basis van een of meer gemeenschappelijke kolommen met behulp van Power Query en de Wizard Tabellen samenvoegen.

Het combineren van gegevens uit meerdere tabellen is een van de meest ontmoedigende taken in Excel. Als u besluit het handmatig te doen, kunt u uren bezig zijn om er vervolgens achter te komen dat u belangrijke informatie hebt verknoeid. Als u een ervaren Excel pro bent, dan kunt u mogelijk vertrouwen op VLOOKUP en INDEX MATCH formules. Een macro, denkt u, zou het werk in een handomdraai kunnen doen, als u maar wist hoe. Het goede nieuws voor alle Excel-gebruikers – Power Query of Merge Tables Wizard kan uw tijd-saver zijn. De keuze is aan u.

• Hoe tabellen samenvoegen met Power Query
• Tabellen samenvoegen Wizard – snelle manier om tabellen in Excel te combineren
• Andere manieren om gegevens in Excel te combineren

Hoe tabellen samen te voegen met Excel Power Query

In eenvoudige bewoordingen, Power Query (ook bekend als Get & Transform in Excel 2016 en Excel 2019) is een hulpmiddel om gegevens uit meerdere bronnen te combineren, op te schonen en te transformeren in het formaat dat u nodig hebt, zoals een tabel, draaitabel of pivotgrafiek.

Onder andere kan Power Query 2 tabellen samenvoegen tot 1 of gegevens uit meerdere tabellen combineren door gegevens in kolommen te matchen, wat de focus van deze tutorial is.

Om ervoor te zorgen dat de resultaten aan uw verwachtingen voldoen, moet u rekening houden met de volgende dingen:

• Power Query is een ingebouwde functie in Excel 2016 en Excel 2019, maar het kan ook worden gedownload in Excel 2010 en Excel 2013 en worden gebruikt als een invoegtoepassing. In eerdere versies kunnen sommige vensters er anders uitzien dan de afbeeldingen in deze zelfstudiegids die zijn vastgelegd in Excel 2016.
• Om de tabellen correct te combineren, moeten ze ten minste één gemeenschappelijke kolom hebben (ook wel een gemeenschappelijke id- of sleutelkolom of unieke identificatiecode genoemd). Ook moeten de gemeenschappelijke kolommen alleen unieke waarden bevatten, zonder herhalingen.
• De brontabellen kunnen zich op hetzelfde blad of in verschillende werkbladen bevinden.
• In tegenstelling tot formules, trekt Power Query geen gegevens van de ene tabel naar de andere. Er wordt een nieuwe tabel gemaakt waarin gegevens uit de oorspronkelijke tabellen worden gecombineerd.
• De resulterende tabel wordt niet automatisch bijgewerkt. U moet Excel expliciet vertellen dit te doen. Zie hoe u een samengevoegde tabel kunt verversen.

Brongegevens

Als voorbeeld voegen we 3 tabellen samen op basis van de gemeenschappelijke kolommen Order ID en Verkoper. Merk op dat onze tabellen verschillende aantallen rijen hebben, en hoewel tabel 1 duplicaten heeft in de kolom Verkoper, bevat tabel 3 alleen unieke vermeldingen.

Het is onze taak om de gegevens in tabel 1 in kaart te brengen met de relevante records uit de andere twee tabellen, en alle gegevens samen te voegen in een nieuwe tabel, zoals deze:

Voordat u begint met samenvoegen, raad ik u aan om uw tabellen beschrijvende namen te geven, zodat u ze later gemakkelijker kunt herkennen en beheren. Ook al zeggen we “tabellen”, je hoeft niet echt een Excel tabel te maken. Uw “tabellen” kunnen gebruikelijke bereiken of benoemde bereiken zijn, zoals in dit voorbeeld:

• Tabel 1 heet Orders
• Tabel 2 heet Producten
• Tabel 3 heet Commissies

Creëer Power Query verbindingen

Om uw werkmap niet te vervuilen met kopieën van uw oorspronkelijke tabellen, gaan we ze omzetten in verbindingen, de samenvoeging doen binnen de Power Query Editor, en dan alleen de resulterende tabel laden.

Om een tabel als verbinding in Power Query op te slaan, gaat u als volgt te werk:

1. Selecteer uw eerste tabel (Orders) of een willekeurige cel in die tabel.
2. Ga naar het tabblad Gegevens > Haal & Transformatiegroep en klik op Uit tabel/bereik.
   
3. In de Power Query Editor die wordt geopend, klikt u op de vervolgkeuzelijst Sluiten & Laden (niet op de knop zelf!) en selecteer de optie Sluiten en laden naar….
   
4. In het dialoogvenster Gegevens importeren, selecteer de optie Alleen verbinding maken en klik op OK.
   

   This will create a connection with the name of your table/range and display that connection in the Queries & Connections pane that appears on the right-hand side of your workbook.

5. Herhaal de bovenstaande stappen voor alle andere tabellen die u wilt samenvoegen (in ons geval nog twee tabellen, Producten en Commissies).

Wanneer u klaar bent, ziet u alle verbindingen in het deelvenster:

Twee verbindingen samenvoegen in één tabel

Met de verbindingen op hun plaats, laten we eens kijken hoe u twee tabellen kunt samenvoegen in één:

1. Op het tabblad Gegevens, in de groep Gegevens ophalen & Gegevens transformeren, klikt u op de knop Gegevens ophalen, kiest u Query’s combineren in de vervolgkeuzelijst, en klikt u op Samenvoegen:
   
2. In het dialoogvenster Samenvoegen doet u het volgende:
   • Selecteer uw eerste tabel (Orders) in de eerste vervolgkeuzelijst.
   • Selecteer de 2e tabel (Producten) uit de tweede drop-down.
   • In beide previews, klik op de overeenkomende kolom (Order ID) om deze te selecteren. De geselecteerde kolom wordt groen gemarkeerd.
   • In de vervolgkeuzelijst Soort verbinding laat u de standaardoptie staan: Left Outer (alles van de eerste, overeenkomend van de tweede).
   • Klik op OK.

   

Als u de bovenstaande stappen hebt uitgevoerd, ziet u in de Power Query Editor uw eerste tabel (Orders) met aan het einde een extra kolom met dezelfde naam als in uw tweede tabel (Producten). Deze extra kolom heeft nog geen waarden, alleen het woord “Tabel” in alle cellen. Maar voel je niet ontmoedigd, je hebt alles goed gedaan, en we gaan dat zo meteen oplossen!

Selecteer de toe te voegen kolommen van de tweede tabel

Op dit punt heb je een tabel die lijkt op die in de schermafbeelding hieronder. Om het samenvoegproces te voltooien, voert u de volgende stappen uit in de Power Query Editor:

1. Klik in de toegevoegde kolom (Producten) op de tweezijdige pijl in de koptekst.
   
2. In het vak dat zich opent, doe je het volgende:
   • Houd het keuzerondje Uitbreiden geselecteerd.
   • Uselecteer alle kolommen, en selecteer dan alleen de kolom(men) die je wilt kopiëren uit de tweede tabel. In dit voorbeeld selecteren we alleen de kolom Product omdat onze eerste tabel al Verkoper- en Order-ID’s bevat.
   • Vink het vakje Originele kolomnaam als voorvoegsel gebruiken uit (tenzij u wilt dat de kolomnaam wordt voorafgegaan door de tabelnaam waaruit deze kolom wordt overgenomen).
   • Klik op OK.

   

Als resultaat krijgt u een nieuwe tabel die elke record uit uw eerste tabel bevat en de extra kolom(men) uit de tweede tabel:

Als u slechts twee tabellen hoeft samen te voegen, kunt u ervan uitgaan dat het werk bijna klaar is en de resulterende tabel in Excel gaan laden.

Meer tabellen samenvoegen (optioneel)

In het geval dat u drie of meer tabellen wilt samenvoegen, hebt u nog wat meer werk te doen. Ik zal de stappen hier kort schetsen, omdat je dit allemaal al hebt gedaan bij het samenvoegen van de eerste twee tabellen:

1. Bewaar de tabel die je in de vorige stap hebt gekregen (te zien in de schermafbeelding hierboven) als verbinding:
   • In de Power Query Editor, klik je op Sluiten & Laad drop-down pijl en selecteer je Sluiten en Laden To….
   • In het dialoogvenster Gegevens importeren selecteert u Alleen verbinding maken en klikt u op OK.

   Dit voegt nog een verbinding toe, genaamd Samenvoegen1, aan het deelvenster Query’s & Verbindingen. U kunt deze verbinding een andere naam geven als u wilt (klik met de rechtermuisknop en selecteer Hernoemen in het pop-up menu).
   

Combineer Merge1 met uw derde tabel (Commissies) door deze stappen uit te voeren (Data tab > Get Data > Combineer Queries > Voeg samen).

De schermafbeelding hieronder toont mijn instellingen:


2. Als u in het dialoogvenster Samenvoegen op OK klikt, wordt de Power Query Editor geopend, waarin u de kolommen selecteert die uit tabel 3 moeten worden toegevoegd.

In dit voorbeeld voegen we alleen de kolom Commissie toe:

Als resultaat krijgt u een samengevoegde tabel die bestaat uit de eerste tabel, plus de extra kolommen die uit de andere twee tabellen zijn gekopieerd.

De samengevoegde tabel importeren naar Excel

Met de resulterende tabel in de Power Query Editor hoeft u nog maar één ding te doen: deze in uw Excel-werkmap laden. En dat is het gemakkelijkste deel!

1. Klik in de Power Query Editor op de vervolgkeuzelijst Sluiten & Laden en kies Sluiten en laden naar….
2. In het dialoogvenster Gegevens importeren selecteert u Tabel en Nieuwe werkbladopties.
3. Klik op OK.

Een nieuwe tabel waarin de gegevens uit twee of meer bronnen worden gecombineerd, verschijnt in een nieuw werkblad. Gefeliciteerd, het is je gelukt!

Als finishing touch kun je misschien nog de juiste getalnotatie toepassen op sommige kolommen en misschien de standaard tabelstijl wijzigen in je favoriete stijl. Na deze verbeteringen ziet mijn gecombineerde tabel er erg mooi uit:

Hoe tabellen samenvoegen op basis van meerdere kolommen met Power Query

In het vorige voorbeeld voegden we tabellen samen door gegevens in één sleutelkolom te matchen. Maar niets weerhoudt u ervan om twee of meer kolomparen te selecteren. Dat gaat als volgt:

In het dialoogvenster Samenvoegen houdt u de Ctrl-toets ingedrukt en klikt u één voor één op de belangrijkste kolommen om ze te selecteren. Het is belangrijk dat u de kolommen in beide voorvertoningen in dezelfde volgorde aanklikt, zodat de overeenkomende kolommen dezelfde nummers hebben. Bijvoorbeeld, Verkoper is sleutelkolom 1 en Product is sleutelkolom 2. Blanco cellen of rijen die Power Query niet kan matchen, worden null weergegeven:

Vervolg daarna precies dezelfde stappen als hierboven beschreven, en uw tabellen worden samengevoegd door de waarden in alle sleutelkolommen te matchen.

Hoe de samengevoegde tabel bij te werken/te vernieuwen

Het beste aan Power Query is dat het een eenmalige installatie is. Wanneer u enkele wijzigingen aanbrengt in een brontabel, hoeft u het hele proces niet opnieuw te herhalen. Klik gewoon op de knop Vernieuwen in het deelvenster Query’s & Verbindingen, en de samengevoegde tabel wordt in één keer bijgewerkt:

Als het deelvenster uit uw Excel is verdwenen, klikt u op de knop Queries & Verbindingen op het tabblad Gegevens om het terug te krijgen.

Als alternatief kunt u klikken op de knop Alles vernieuwen op het tabblad Gegevens of op de knop Vernieuwen op het tabblad Query (dit tabblad wordt geactiveerd zodra u een cel binnen een samengevoegde tabel selecteert).

Tabellen samenvoegen Wizard – snelle manier om 2 tabellen samen te voegen in Excel

Nu u bekend bent met de ingebouwde tool, laat ik u onze aanpak zien voor het samenvoegen van tabellen in Excel.

In dit voorbeeld voegen we dezelfde tabellen samen die we zojuist met Power Query hebben samengevoegd. Ik heb zojuist nog een paar rijen aan de tweede tabel toegevoegd om u meer mogelijkheden van onze add-in te laten zien:

Met de Wizard Tabellen samenvoegen geïnstalleerd in uw Excel, is dit wat u moet doen:

1. Selecteer de eerste tabel of een cel daarin en klik op de knop Twee tabellen samenvoegen op het tabblad Ablebits Data:
   

Kijk even naar het geselecteerde bereik om te zien of de invoegtoepassing het goed heeft gedaan en klik op Volgende.
Selecteer de tweede tabel en klik op Volgende. Merk op dat onze tweede tabel 26 rijen bevat, vergeleken met slechts 10 rijen in de eerste tabel:


2. Kies één of meer overeenkomende kolommen en klik op Volgende. Omdat we twee tabellen samenvoegen met één gemeenschappelijke kolom, Order ID, selecteren we alleen die kolom:
   
   Let op het vakje Hoofdlettergevoelige overeenstemming bovenin. Selecteer dit als u hoofdletters en kleine letters in de hoofdkolommen als verschillende tekens wilt behandelen. In dit voorbeeld hebben we dat niet nodig, dus laten we het vakje ongeselecteerd.
3. Selecteer de bij te werken kolommen in de eerste tabel. Deze stap is optioneel, en als u geen updates wilt, kunt u op Volgende klikken zonder hier iets te selecteren.

   We selecteren de kolom Verkoper omdat we meer rijen in de tweede tabel hebben en we de nieuwe verkopersnamen in de bestaande kolom Verkoper willen laten verschijnen:
   

4. Kies de kolom of kolommen die moeten worden toegevoegd aan de eerste tabel, Product in ons geval, en klik op Volgende:
   
5. Deze stap is erg belangrijk, omdat het bepaalt hoe uw tabellen worden samengevoegd.
   In dit voorbeeld gaan we voor de standaard opties zoals te zien is in de schermafbeelding hieronder. Maar ik wil je aandacht vestigen op de volgende 2 vakjes die kunnen voorkomen dat je bestaande gegevens worden overschreven in het geval je ervoor hebt gekozen om sommige kolommen te updaten:
   • Alleen lege cellen
   • Alleen als cellen in de opzoektabel gegevens bevatten

   

Maak uw keuzes, klik op Voltooien, geef de wizard een paar seconden de tijd om de gegevens te verwerken en bekijk de resultaten.

Met de standaardopties markeert de wizard de nieuw toegevoegde rijen en voegt de kolom Status toe. Als u dat allemaal niet wilt, verwijdert u de overeenkomstige vakjes in de laatste stap.

Om drie en meer tabellen samen te voegen, herhaalt u eenvoudig de bovenstaande stappen.

In tegenstelling tot Power Query houdt de wizard Tabellen samenvoegen geen verbinding tussen de resulterende tabel en de brontabel. In sommige situaties kan dit een nadeel zijn. Aan de positieve kant, wat u ook doet met de brontabel – bewerken, verplaatsen of zelfs verwijderen – de samengevoegde tabel blijft intact.

Dit voorbeeld heeft slechts één scenario laten zien dat onze wizard aankan, maar er is veel meer mogelijk! Als u benieuwd bent naar andere use cases, bekijk dan deze voorbeelden.

Ook kunt u een proefversie van Ultimate Suite for Excel downloaden, die naast de Merge Tables Wizard nog meer dan 60 andere handige tools bevat. Als de invoegtoepassing u bevalt en u besluit een licentie te nemen, doen wij u graag dit exclusieve aanbod:

In het geval dat u op zoek bent om tabellen samen te voegen op een andere manier, kunt u de volgende bronnen nuttig vinden.

Andere manieren om gegevens te combineren in Excel:

Samenvoeg tabellen door kolomkoppen – voeg twee of meer tabellen samen op basis van kolomnamen. U kunt ervoor kiezen om alle kolommen te combineren of alleen de kolommen die u selecteert.

Meerdere werkbladen samenvoegen in één – kopieer meerdere bladen naar één samenvattend werkblad. Natuurlijk is dit geen handmatig kopiëren/plakken! U geeft alleen aan welke werkbladen moeten worden samengevoegd, en onze Copy Sheets tool doet de rest.

Vergelijk twee Excel-bestanden – hoe u twee tabellen (werkbladen) vergelijkt op verschillen en ze samenvoegt in één werkblad.