Als u de resultaten van afzonderlijke werkbladen wilt samenvatten en rapporteren, kunt u gegevens van elk werkblad samenvoegen in een hoofdwerkblad. De bladen kunnen zich in dezelfde werkmap bevinden als het hoofdwerkblad of in andere werkmappen. Als u bijvoorbeeld een onkostenwerkblad hebt voor elk van de regionale kantoren, kunt u deze cijfers samenvoegen in een hoofdwerkblad voor het hele bedrijf. Als u regelmatig gegevens consolideert, kan het handig zijn om nieuwe werkbladen te maken op basis van een werkbladsjabloon die gebruikmaakt van een consistente indeling. Zie Een sjabloon maken voor meer informatie over sjablonen.
Consolidatie op positie en consolidatie op categorie
Consolidatie op positie: de gegevens in de brongebieden hebben dezelfde volgorde en gebruiken dezelfde labels. Samenvoegen op categorie: wanneer de gegevens in de brongebieden niet in dezelfde volgorde zijn gerangschikt, maar wel dezelfde labels hebben. Het samenvoegen van gegevens op categorie is vergelijkbaar met het maken van een draaitabel. Met een draaitabel kunt u echter de categorieën eenvoudig opnieuw indelen.
De voorbeelden in dit artikel zijn gemaakt met Excel 2016. Zorg ervoor dat elk gegevensbereik de lijstindeling heeft. Elke kolom moet een label (koptekst) in de eerste rij hebben en vergelijkbare gegevens bevatten. Plaats elk bereik op een afzonderlijk werkblad, maar voer niets in het hoofdwerkblad in waar u de gegevens wilt samenvoegen. Klik in het vak Functie op de samenvattingsfunctie die u in Excel wilt gebruiken om de gegevens samen te voegen. Als een werkblad met gegevens die u wilt samenvoegen zich in een andere werkmap bevindt, klikt u op Bladeren om die werkmap te zoeken. Nadat u op OK hebt geklikt, voert Excel het bestandspad in het vak Verwijzing in en voegt een uitroepteken toe aan dat pad. Klik in het pop-upvenster Samenvoegen op Toevoegen.
Automatische versus handmatige updates
Automatische versus handmatige updates: Als u wilt dat Excel uw consolidatietabel automatisch bijwerkt wanneer de brongegevens worden gewijzigd, schakelt u het selectievakje Koppelingen naar brongegevens maken in. Als u de omvang van een bereik wilt wijzigen of een bereik wilt vervangen, klikt u op het bereik in het pop-upvenster Samenvoegen en werkt u het bij met behulp van de bovenstaande stappen. Hiermee wordt een nieuwe bereikreferentie gemaakt, dus u moet de vorige verwijderen voordat u opnieuw consolideert. Klik op OK om de consolidatie voor u te genereren. U kunt desgewenst opmaak toepassen.
Voer een formule in met celverwijzingen naar de andere werkbladen, één voor elk afzonderlijk werkblad. Een celverwijzing invoeren, zoals Verkoop! B4: typ in een formule zonder te typen de formule tot het punt waarop u de verwijzing nodig hebt, klik vervolgens op het werkbladtabblad en klik vervolgens op de cel. Excel vult de naam van het blad en het celadres voor u in. OPMERKING: formules kunnen in dergelijke gevallen foutgevoelig zijn, omdat het heel gemakkelijk is om per ongeluk de verkeerde cel te selecteren.
Voer een formule in met een 3D-verwijzing die verwijst naar een bereik met werkbladnamen. 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.
3D-referenties en alternatieven
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, maar ook naar meerdere werkbladen. In de praktijk is dit vooral handig als je per werkblad verschillende maanden, afdelingen of projecten bijhoudt, en je de gegevens wilt optellen of analyseren in een overzicht.
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. Voor een eenvoudige consolidatie kun je de stappen volgen zoals beschreven in de uitleg hieronder.
Consolideren via tabblad Gegevens
Stap-voor-stap samenvatting:
- Open een nieuw werkblad voor het resultaat en selecteer de doelcel.
- Ga naar Gegevens > Consolideren.
- Kies de gewenste functie, bijvoorbeeld SOM.
- Verwijzing: selecteer het bereik op het eerste bron-werkblad en klik Toevoegen. Herhaal voor elk bron-werkblad.
- Activeer “Bovenste rij” en “Linkerkolom” voor matching op labels en kopteksten. Voor live updates vink “Koppelingen maken met brongegevens” aan.
- Klik OK en controleer het resultaat. Pas zo nodig opmaak toe.
Gegevens combineren met VERT.ZOEKEN
Met de VERT.ZOEKEN-functie kun je gegevens uit verschillende werkbladen op basis van een gemeenschappelijk waarde ophalen en samenvoegen. Voorbeeld: productgegevens en prijzen combineren via een exacte match.
De basisformule: =VERT.ZOEKEN(A2;Prijzen!A:B;2;ONWAAR). De argumenten staan voor respectievelijk de zoekwaarde, het bereik waarin gezocht wordt, de kolom met de gewenste uitkomst en het type overeenkomst (exact).
Samenvatting van de methoden
Je hebt nu drie hoofdmethoden geleerd om gegevens uit meerdere werkbladen te combineren:
- 3D-referenties voor snelle sommen of analyses bij dezelfde structuur over meerdere tabbladen.
- Consolideren voor een totaloverzicht met minimale aanpassingen, met of zonder live koppelingen.
- VERT.ZOEKEN voor koppelen van gegevens op basis van een gemeenschappelijk kenmerk zoals een productcode.
Tip: voor consolidaties die maandelijks terugkeren is Power Query krachtiger - definieer de consolidatie eenmaal, pas automatisch toe op nieuwe data.
Power Query en draaitabellen als geavanceerde opties
Power Query maakt het mogelijk om gegevens uit meerdere tabbladen automatisch te importeren, te combineren en op te schonen. Draaitabellen bieden een alternatief voor consolideren en laten interactief filteren en groeperen toe op de geconsolideerde data. Voor structurele bedrijfsrapportages kan Power Query de professionele keuze zijn.
Praktische voorbeelden en toepassingen
- Maandelijkse of kwartaalrapportages: consolidatie van meerdere maand- of kwartaalbladen naar één jaaroverzicht.
- Multi-vestiging consolidatie: vestigingsresultaten samenvoegen tot een bedrijfstotaal.
- Budget versus werkelijk: samenvoegen van budgetbladen en realisatiecijfers over afdelingen of teams.
Beperkingen en best practices:
- Zorg voor consistente kolomkoppen en labelstructuur over alle bronbladen.
- Wijs van tevoren aan of live koppelingen nodig zijn of niet, om bewerkingen achteraf mogelijk te houden.
Samenvattende vergelijking
- 3D-referenties: snel bij dezelfde structuur, gemakkelijk aan te passen wanneer tabbladen worden toegevoegd.
- Consolideren: handig voor snelle totalen en labels, minder onderhoud bij kleine datasets.
- VERT.ZOEKEN: flexibel voor opzoeken op basis van gemeenschappelijke kenmerken.
- Power Query: schaalbaar en robuust bij grote of regelmatige consolidaties, vooral in Microsoft 365.