Hoe je gegevens uit twee Excel-werkbladen kunt combineren

Op een maandag komen er twee bestanden in je inbox terecht. Het ene bestand bevat de bestellingen van vorige maand, het andere die van deze maand, en iemand van het hogere management wil voor de lunch één rapport zien. Dus open je beide bestanden, begin je met kopiëren en plakken, en halverwege realiseer je je dat de kolommen niet eens op dezelfde manier zijn uitgelijnd. Klinkt dat bekend?

Dit is wat bijna niemand meteen vermeldt. Achter de zin„gegevens uit twee werkbladen in Excel samenvoegen“ gaan twee totaal verschillende taken schuil, en als je de verkeerde kiest, ben je een hele middag kwijt. De ene taak stapelt rijen op elkaar. De andere koppelt kolommen naast elkaar aan de hand van een gedeelde ID. Als je de juiste diagnose stelt, kost het eigenlijke werk maar een paar minuten.

Het punt is: deze gids maakt eerst een onderscheid op basis van het doel en biedt vervolgens voor elk doel het juiste hulpmiddel aan. Je hoeft niet te gissen.

Hoe je gegevens uit twee werkbladen in Excel combineert: de fout die iedereen maakt

De meeste handleidingen kiezen één methode en gaan daar

dan volledig

op in. Ze besteden zes alinea’s aan VLOOKUP, of laten Power Query zien zonder ook maar één keer op zoekfuncties in te gaan. De helft van de lezers komt op de verkeerde pagina terecht en raakt nog meer in de war dan toen ze begonnen.

Het echte probleem is niet de tool. Het probleem is dat mensen handmatig kopiëren en plakken omdat ze zich nooit hebben afgevraagd in welke vorm de gegevens zich bevinden. En bij handmatig plakken ontstaan er fouten die je gemakkelijk over het hoofd ziet – hier een verdwaalde lege rij, daar een kolom die één plaats is verschoven, en plotseling klopt je totaal niet meer met een paar duizend, zonder dat iemand weet waarom.

Voer dus allereerst deze snelle controle uit.

• Twee werkbladen, dezelfde kolommen, verschillende records (bestellingen van januari + bestellingen van februari)? Je moet rijen toevoegen. Dat is een klus voor Power Query.
• Twee werkbladen, dezelfde ID-kolom, verschillende details (order-ID’s in het ene, e-mailadressen van klanten in het andere)? Je moet kolommen aan elkaar koppelen. Dat is een klus voor XLOOKUP of INDEX/MATCH.

Die ene vraag is bepalend voor alles wat daarna volgt. Als je hier de fout in gaat, helpt zelfs de beste kennis van formules je niet meer.

Taak 1: Rijen toevoegen met Power Query

Als je twee Excel-werkbladen wilt samenvoegen door ze onder elkaar te stapelen, is Power Query de meest overzichtelijke manier. Het is ingebouwd in Excel (tabblad ‘Gegevens’, vanaf Excel 2016) en het wordt automatisch vernieuwd, dus als je volgende maand nieuwe gegevens toevoegt, hoef je slechts op één knop te klikken in plaats van het geheel opnieuw op te bouwen.

De documentatie van Microsoft zelf behandelt de basisprincipes van het combineren van gegevens uit meerdere werkbladen, maar de specifieke werkwijze voor het toevoegen van rijen verloopt als volgt.

Maak eerst van elk werkblad een echte tabel. Klik ergens in je gegevens, druk op Ctrl+T en vink het selectievakje voor kopteksten aan. Doe dit voor beide werkbladen. Geef ze onder ‘Tabelontwerp’ een herkenbare naam, zoals tblJan en tblFeb.

Laad ze vervolgens allebei in Power Query. Plaats je cursor in de eerste tabel en ga naar ‘Gegevens’ > Van Tabel/Bereik. De editor wordt geopend. Je hoeft hier niets te wijzigen – sluit het gewoon en laad het alleen als verbinding (Bestand > Sluiten en laden naar > (Alleen verbinding maken). Herhaal dit voor de tweede tabel.

Nu het daadwerkelijke toevoegen. Gegevens >. Gegevens ophalen >. Query’s combineren >. Toevoegen. Kies je twee query’s en klik op OK. Power Query plaatst de rijen van de tweede tabel direct onder die van de eerste, waarbij de kolommen op basis van de koptekstnamen worden gekoppeld.

Laad het resultaat in een nieuw werkblad. Klaar. En het mooie van deze Power Query-aanpak voor het samenvoegen in Excel is de vernieuwingsfunctie. De volgende keer dat je bijgewerkte bestanden ontvangt, plak je de nieuwe rijen in je brontabellen en klik je op Gegevens > Alles vernieuwen. De gecombineerde tabel wordt dan automatisch bijgewerkt.

“Bij het samenvoegen gaat het helemaal niet om formules. Het gaat erom dat je Excel laat weten dat deze twee stapels van hetzelfde soort zijn, waarna het ze elke maand op verzoek voor je samenvoegt.”

Er staat een gedetailleerde uitleg over de versie met meerdere werkbladen in de handleiding van Trump Excel voor het samenvoegen van werkbladen, voor als je meer dan twee bestanden wilt samenvoegen. Dezelfde logica, alleen meer opvragingen in de toevoeglijst.

Opdracht 2: Kolommen koppelen met XLOOKUP of INDEX/MATCH

Deze

opdracht

is van een andere aard. Je twee werkbladen beschrijven dezelfde records, maar bevatten verschillende kolommen met informatie daarover. Werkblad één bevat order-ID’s en bedragen. Werkblad twee bevat diezelfde order-ID’s plus het e-mailadres en de regio van de klant. Je wilt één tabel waarin alle gegevens naast elkaar staan.

Dit is een situatie waarbij je met VLOOKUP tussen werkbladen moet zoeken, maar VLOOKUP is de oude, kwetsbare methode. XLOOKUP heeft deze functie vervangen voor iedereen die Microsoft 365 of Excel 2021 gebruikt, en het is de moeite waard om in plaats daarvan XLOOKUP te gebruiken.

Stel dat je hoofdtabel op Blad1 staat, met bestelnummers in kolom A. De e-mailadressen die je zoekt, staan op Blad2, met de ID’s in kolom A en de e-mailadressen in kolom B. In Blad1, cel D2:

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B, "Not found")

Dat komt neer op: neem het ID in A2, zoek het op in de kolom ‘ID’ van Blad2 en geef het bijbehorende e-mailadres weer. Dat laatste argument, "Not found", wordt weergegeven als er geen overeenkomst is – wat veel beter is dan die lelijke ‘ #N/A ’ van VLOOKUP die je dan in het gezicht staart.

Sleep het naar beneden in de kolom en elke rij neemt de bijbehorende waarde over. Strak.

Zit je vast aan een oudere Excel-versie zonder XLOOKUP? Met INDEX/MATCH kun je hetzelfde bereiken:

=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))

De 0 -functie in MATCH dwingt een exacte overeenkomst af, wat je bij ID’s bijna altijd wilt. Als je dit achterwege laat, voert Excel een benaderende vergelijking uit, waardoor er bij ongesorteerde gegevens ongemerkt verkeerde waarden worden geretourneerd. Een klassieke stille fout.

Goed om te weten: Power Query kan deze koppelingstaak ook uitvoeren, met de functie ‘Merge’ in plaats van ‘Append’. Voor een eenmalig rapport is het sneller om een formule te schrijven. Voor iets dat je maandelijks opnieuw moet opstellen met duizenden rijen, schaalt de ‘Merge’-functie van Power Query beter en zal het je bestand niet tot een slakkengang vertragen, zoals tienduizend XLOOKUP-formules dat wel doen.

Wanneer je

gegevens

daadwerkelijk wilt samenvoegen

Er is nog een derde, minder opvallende optie die mensen vaak over het hoofd zien. Als het je doel is om gegevens op Excel-wijze samen te voegen – dat wil zeggen dat je totalen of gemiddelden over verschillende werkbladen wilt berekenen in plaats van elke ruwe rij afzonderlijk te behouden – dan heeft Excel daar een speciale functie voor: Gegevens > eer.

Richt het op beide reeksen, kies je functie (Som, Gemiddelde, Aantal) en het berekent de totalen op basis van overeenkomende rij- en kolomlabels. In de stap-voor-staphandleiding over ‘Consolideren’ van Spreadsheeto wordt het dialoogvenster uitgebreid behandeld. Dit is de juiste keuze wanneer u de onderliggende details niet nodig hebt, maar alleen de totalen – bijvoorbeeld om regionale verkoopcijfers samen te voegen tot één overzicht.

Gebruik de functie ‘Consolidate’ echter niet als je eigenlijk elke rij intact wilt houden. Deze functie voegt gegevens samen, waardoor je de gedetailleerdheid verliest. Dit is een toevoertaak die voor het verkeerde doel wordt ingezet.

Problemen met de Combine oplossen

Er zijn een aantal veelvoorkomende problemen. Hieronder vind je een overzicht van wat er meestal misgaat en hoe je dit kunt oplossen.

Dubbele kopteksten na het toevoegen van gegevens duiden bijna altijd op kolomnamen die niet overeenkomen tussen je twee brontabellen. Ga terug, hernoem de kolommen zodat ze exact overeenkomen – inclusief spaties en hoofdletters – en vernieuw vervolgens de pagina.

Leveren zoekopdrachten de foutmelding ‘ #N/A ’ op bij rijen waarvan je weet dat ze bestaan? Negen van de tien keer is er sprake van een verschil in gegevenstype. In het ene werkblad wordt de ID als tekst opgeslagen, in het andere als getal. Op het scherm zien ze er identiek uit, maar Excel beschouwt “1024” en 1024 als verschillende waarden. Converteer één van beide, zodat ze met elkaar overeenkomen.

Extra lege rijen die in je samengevoegde tabel terechtkomen, zijn meestal terug te voeren op lege rijen in het oorspronkelijke gegevensbereik die mee zijn genomen. Filter deze er bij de bron uit voordat je de gegevens samenvoegt.

En als je bestand na een grote opzoektaak traag begint te worden, is dat een teken dat je moet overstappen op Power Query. Duizenden vluchtige formules die bij elke bewerking opnieuw worden berekend, kunnen zelfs een behoorlijke computer lamleggen. Ablebits biedt een goed overzicht van de zwaardere samenvoegscenario’s als je met meerdere grote werkboeken werkt.

Veelgestelde vragen

Moet ik Power Query of een formule gebruiken om twee werkbladen samen te voegen?
Dat hangt ervan af of het om een eenmalige of een terugkerende taak gaat. Voor een eenmalig rapport dat je nooit meer zult gebruiken, is een formule sneller te schrijven. Voor alles wat je maandelijks opnieuw opstelt, is Power Query de betere keuze, omdat het met één klik wordt vernieuwd in plaats van dat je elke keer opnieuw formules moet opstellen.

Wat is het verschil tussen ‘appending’ en ‘merging’?
Bij ‘appending’ worden rijen onder elkaar geplaatst wanneer beide werkbladen hetzelfde soort records bevatten. Bij ‘merging’ (of ‘looking up’) worden kolommen naast elkaar gekoppeld aan de hand van een gedeelde ID, wanneer de werkbladen verschillende gegevens over dezelfde records bevatten. Dit zijn twee totaal verschillende bewerkingen.

Is XLOOKUP beter dan VLOOKUP voor het samenvoegen van werkbladen?
Ja, als je die functie hebt. XLOOKUP gaat om met een ingebouwde “niet gevonden”-melding, zoekt naar links of rechts en loopt niet vast wanneer iemand een kolom invoegt. VLOOKUP is prima voor oude bestanden, maar er is geen reden om er nieuw werk mee te beginnen in Excel 2021 of 365.

Waarom geeft mijn opzoekactie fouten weer terwijl de gegevens duidelijk overeenkomen?
Bijna altijd gaat het om een verschil in gegevenstype. In het ene werkblad wordt de sleutel als tekst opgeslagen, in het andere als getal. Ze zien er hetzelfde uit, maar Excel beschouwt ze niet als gelijk. Standaardiseer de opmaak aan beide kanten en de overeenkomsten worden opgelost.

Kan ik werkbladen uit twee verschillende werkboeken samenvoegen?
Ja. Power Query maakt verbinding met externe bestanden via ‘Gegevens’ > ‘Gegevens ophalen’ > ‘Uit bestand’ > ‘Uit werkboek’. De stappen ‘Toevoegen’ en ‘Samenvoegen’ werken precies hetzelfde zodra beide query’s zijn geladen.

Afsluiting

:

Het samenvoegen van twee tabellen is niet moeilijk als je het niet langer als één probleem beschouwt. Stel eerst de volgende vraag: gaat het om dezelfde records die boven elkaar staan, of om dezelfde ID’s die aan elkaar zijn gekoppeld? Gebruik ‘Append’ in Power Query voor het eerste geval, XLOOKUP of INDEX/MATCH voor het tweede geval, en ‘Consolidate’ als je alleen de totalen nodig hebt. Wanneer je gegevens uit twee werkbladen in Excel combineert, analyseer dan eerst de situatie en bouw daarna verder. Die volgorde bespaart je een hele middag.