Hoe combineer ik gegevens uit verschillende werkbladen?

Je kunt in Excel tijd besparen door gegevens uit verschillende werkbladen met elkaar te combineren. Dit is vooral handig als je met grote hoeveelheden data werkt die zijn verdeeld over meerdere werkbladen.

Een goed voorbeeld wanneer dit van pas komt is als je bijvoorbeeld een maandelijkse rapportage maakt in Excel, waarbij iedere maand zijn eigen werkblad heeft. Door de gegevens te combineren, kun je eenvoudig een totaaloverzicht maken zonder alle informatie handmatig over te zetten.

In dit artikel leer je een aantal handige functies die Excel biedt waarmee je dit snel en efficiënt kunt doen, zodat je het niet handmatig samen hoeft te voegen.

1. Gegevens combineren met eenvoudige formules en 3D-referenties

Een 3D-referentie verwijst naar dezelfde cel of hetzelfde bereik op meerdere werkbladen. In plaats van een formule te maken voor elk werkblad apart, gebruik je één formule om gegevens uit dezelfde cellocatie op meerdere tabbladen op te halen. Het heet ‘3D’ omdat je niet alleen naar kolommen en rijen kijkt (zoals normaal in een werkblad), maar ook naar meerdere werkbladen, wat een extra dimensie toevoegt.

In de praktijk is dit vooral handig als je bijvoorbeeld per werkblad verschillende maanden, afdelingen of projecten bijhoudt, en je de gegevens wilt optellen of analyseren in een overzicht.

Voorbereiding

Voordat je gegevens uit verschillende werkbladen gaat combineren, is het belangrijk dat je een paar dingen controleert. Dit zorgt ervoor dat alles soepel verloopt en je geen onverwachte fouten krijgt.

1. Consistentie in je gegevens: Controleer of de structuur van de gegevens op elk werkblad hetzelfde is. Dit betekent dat de kolommen op dezelfde plek staan en dezelfde soorten informatie bevatten. Als je bijvoorbeeld uitgaven bijhoudt, moeten de kolommen voor datum, bedrag, en categorie op elk werkblad op dezelfde manier zijn ingedeeld.
2. Werkbladen identificeren: Zorg ervoor dat je weet uit welke werkbladen je gegevens wilt halen. Dit helpt bij het opzetten van je formules. Noteer de namen van de werkbladen, want die heb je straks nodig om gegevens uit de juiste tabbladen te halen.

Voorbeeld

Aan de hand van een eenvoudig voorbeeld laat ik je zien hoe dit werkt.

In dit voorbeeld bestaat een werkmap uit 13 tabbladen:

• 1 jaaroverzicht
• 12 maanden.

Alle tabbladen van de maanden hebben dezelfde structuur, namelijk:

De eerste stap is om de cel te selecteren waarin je het resultaat wilt zien. Ik kies ervoor om dit in het tabblad ‘jaaroverzicht’ te doen in cel B1.

Ik kies ervoor om met de SOM functie de kosten van iedere maand bij elkaar op te tellen. Dit kan met de volgende formule:

=SOM(Januari:December!B1)

Januari:December! verwijst naar alle werkbladen van ‘Januari’ tot ‘December’ en B1 naar de cel op elk werkblad waarvan je de waarde wilt optellen.

Als je handmatig de inhoud van cel B1 van elk werkblad (van januari tot december) zou willen optellen, dan zou je formule er zo uit kunnen zien:

=Januari!B1 + Februari!B1 + Maart!B1 + April!B1 + Mei!B1 + Juni!B1 + Juli!B1 + Augustus!B1 + September!B1 + Oktober!B1 + November!B1 + December!B1

Het resultaat is hetzelfde, maar de formule is zoals je ziet erg lang en overzichtelijk.

De 3D-referentie is niet alleen korter, maar ook veel eenvoudiger te lezen en te onderhouden. Als je bijvoorbeeld een werkblad toevoegt tussen januari en december, wordt deze automatisch meegenomen in de 3D-referentie, terwijl je met de handmatige methode elke keer de formule zou moeten aanpassen. De kans op foutjes wordt hier hiermee ook gelijk een stuk kleiner.

In dit voorbeeld hebben we dus de SOM-functie gebruikt, maar je kunt ook andere functies gebruiken met een 3D-referentie. Hier zijn een paar voorbeelden:

• =GEMIDDELDE(Januari:December!B1) – Berekent het gemiddelde van de waarden in cel B1 van elk werkblad.
• =MAX(Januari:December!B1) – Geeft de hoogste waarde in cel B1 van elk werkblad.
• =MIN(Januari:December!B1) – Geeft de laagste waarde in cel B1 van elk werkblad.

Hopelijk geeft het een goed beeld hoe handig het gebruiken van de 3D-referenties is als je gegevens hebt die in dezelfde cel staan op verschillende werkbladen.

2. Gegevens combineren met de CONSOLIDEREN-functie

De CONSOLIDEREN-functie is ideaal wanneer je gegevens uit meerdere werkbladen of werkmappen wilt samenvoegen tot een overzicht. Dit is vooral handig als je vergelijkbare gegevens hebt, zoals verkoopcijfers per maand of kwartaal, en je die snel wilt samenvoegen tot één totaaloverzicht.

In dit voorbeeld hebben we 4 werkbladen:

• Jaaroverzicht
• Januari
• Februari
• Maart

Het is de bedoeling om in het jaaroverzicht de totalen uit de maanden bij elkaar op te tellen. Dit kan aan de hand van de volgende stappen.

1. Selecteer de cel waar je de gegevens wilt consolideren. In dit voorbeeld doe ik dat in cel B2 in het jaaroverzicht.
2. Ga naar het tabblad ‘Gegevens’ en klik op ‘Consolideren’ in de groep ‘Hulpmiddelen voor gegevens’. (zie rode vierkant in bovenstaande afbeelding).
3. Kies de functie ‘SOM’.
4. Selecteer het bereik voor de consolidatie. Klik op de cellen in de werkbladen Januari, Februari en Maart, en voeg ze toe aan het bereik.
5. Klik op OK.

De CONSOLIDEREN-functie is vooral handig als je dezelfde soorten gegevens hebt verspreid over meerdere werkbladen of werkmappen en je deze snel wilt combineren zonder gebruik te maken van complexe formules.

Dit voorbeeld zou je ook kunnen doen zoals hiervoor met 3D-referenties, omdat er sprake is van een eenvoudige en consistente structuur.

Je vraagt je wellicht af waarom en wanneer je dan zou kiezen voor consolideren?

Consolideren is flexibeler omdat je eenvoudig de celverwijzingen kunt aanpassen zonder de structuur van je gegevens te veranderen. Bij 3D-referenties moet je de exacte cel blijven gebruiken, wat minder ruimte biedt voor aanpassingen.

Als ik bijvoorbeeld in Januari de kosten in een andere cel zet, is dat geen probleem. Dan kies ik bij het consolideren gewoon voor een andere verwijzing:

Als de structuur van je gegevens in alle werkbladen hetzelfde is, dan raad ik aan om 3D-referenties te gebruiken omdat dit sneller en eenvoudiger om toe te passen.

3. Gegevens combineren met de VERT.ZOEKEN-functie

Met de VERT.ZOEKEN functie kun je gegevens uit verschillende werkbladen opzoeken en samenvoegen op basis van een gemeenschappelijk waarde.

Hoe dit werkt laat ik wederom zien aan de hand van een eenvoudig voorbeeld.

In dit voorbeeld zijn er twee werkbladen.

Het eerste werkblad heet ‘Productgegevens’ en heeft een tabel bestaande uit de productcode, meubelsoort en prijs.

Het andere werkblad heet ‘Prijzen’:

De bedoeling is om het werkblad met ‘productgegevens’ de prijzen op te halen uit het werkblad met ‘prijzen’. Dat kan met de volgende formule.

=VERT.ZOEKEN(A2;Prijzen!A:B;2;ONWAAR)

Deze formule bestaat uit meerdere argumenten. Om te begrijpen hoe dit werkt, worden deze hieronder verder toegelicht:

• A2: Dit is de waarde die je wilt opzoeken (in dit voorbeeld dus de productcode P01).
• Prijzen!A:B: Dit is het bereik in het “Prijzen” werkblad waarin je de waarde zoekt (de productcodes staan in kolom A en de prijzen in kolom B).
• 2: Dit geeft aan dat je de waarde uit de tweede kolom van het bereik wilt tonen (de prijs in dit geval).
• ONWAAR: Dit zorgt ervoor dat de functie een exacte match zoekt.

De VERT.ZOEKEN-functie is vooral handig als je gegevens uit verschillende werkbladen wilt combineren op basis van een uniek kenmerk (zoals een productcode in dit voorbeeld, maar het kan ook een klantnummer of postcode zijn). Je kunt hiermee op een efficiënte manier snel gegevens uit een ander werkblad halen zonder handmatig te hoeven zoeken.

4. Samenvatting

Je hebt nu drie manieren geleerd hoe je gegevens kunt combineren uit verschillende werkbladen. Maar wanneer kun je nou het beste welke functie gebruiken? Ik raad het volgende aan:

• 3D-referenties: Als je snel en eenvoudig gegevens wilt combineren die dezelfde structuur hebben over verschillende werkbladen, zoals bij maandelijkse of jaarlijkse rapportages.
• Consolideren: Als je een totaaloverzicht wilt van waarden uit verschillende werkbladen zonder dat je je zorgen hoeft te maken over specifieke details binnen elke tabel.
• VERT.ZOEKEN: Als je individuele gegevens uit verschillende tabellen wilt ophalen en combineren op basis van unieke kenmerken.

Indien je je Excel vaardigheden op een gestructureerde manier verder wilt ontwikkelen, zou je ook een kijkje kunnen nemen naar een van mijn online Excel cursussen.