Gegevens in meerdere werkbladen samenvoegen

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. Wanneer u gegevens consolideert, stelt u gegevens samen, zodat u zo nodig gemakkelijker kunt bijwerken en aggregeren.

Als u bijvoorbeeld een onkostenwerkblad hebt voor elk van de regionale kantoren, kunt u deze cijfers samenvoegen in een hoofdwerkblad voor het hele bedrijf. Dit hoofdwerkblad kan ook de verkooptotalen en -gemiddelden, huidige voorraadniveaus en meest verkochte producten voor het hele bedrijf bevatten.

Tip: 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. Meer informatie over deze sjablonen vindt u hier: Een sjabloon maken. Dit is ook een ideaal moment om uw sjabloon in te stellen met Excel-tabellen.

Er zijn twee manieren om gegevens te consolideren, per positie of categorie.

Consolidatie op positie: de gegevens in de brongebieden hebben dezelfde volgorde en gebruiken dezelfde labels. Gebruik deze methode om gegevens samen te voegen vanuit een reeks werkbladen, zoals budgetwerkbladen van afdelingen die op basis van dezelfde sjabloon zijn gemaakt.

Samenvoegen op categorie: wanneer de gegevens in de brongebieden niet in dezelfde volgorde zijn gerangschikt, maar wel dezelfde labels hebben. Gebruik deze methode om gegevens samen te voegen vanuit een reeks werkbladen met verschillende indelingen, maar dezelfde gegevenslabels.

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. Overweeg om een draaitabel te maken als u flexibelere consolidatie per categorie nodig hebt.

Opmerking: De voorbeelden in dit artikel zijn gemaakt met Excel 2016. Hoewel uw weergave kan verschillen als u een andere versie van Excel gebruikt, zijn de stappen hetzelfde.

Volg deze stappen om verschillende werkbladen samen te voegen in een hoofdwerkblad:

Als u dat nog niet hebt gedaan, stelt u de gegevens in elk samenstellend werkblad als volgt in:
- Zorg ervoor dat elk gegevensbereik de lijstindeling heeft. Elke kolom moet een label (koptekst) in de eerste rij hebben en vergelijkbare gegevens bevatten. Er mogen geen lege rijen of kolommen in de lijst staan.
- Plaats elk bereik op een afzonderlijk werkblad, maar voer niets in het hoofdwerkblad in waar u de gegevens wilt samenvoegen. Excel doet dit voor u.
- Zorg ervoor dat elk bereik dezelfde indeling heeft.
Klik in het hoofdwerkblad op de cel in de linkerbovenhoek van het gebied waar u de samengevoegde gegevens wilt weergeven.

Opmerking: Als u wilt voorkomen dat bestaande gegevens in het hoofdwerkblad worden overschreven, moet u ervoor zorgen dat u voldoende cellen rechts en onder deze cel laat staan voor de geconsolideerde gegevens.
Klik op Gegevens>Samenvoegen (in de groep Hulpmiddelen voor gegevens ).
Klik in het vak Functie op de samenvattingsfunctie waarmee in Excel de gegevens moeten worden samengevoegd. De standaardfunctie is SOM.

Hier volgt een voorbeeld waarin drie werkbladbereiken zijn gekozen:
Selecteer de gegevens.

Klik vervolgens in het vak Verwijzing op de knop Samenvouwen om het deelvenster te verkleinen en de gegevens in het werkblad te selecteren.

Klik op het werkblad met de gegevens die u wilt samenvoegen, selecteer de gegevens en klik op de knop Dialoogvenster uitvouwen aan de rechterkant om terug te gaan naar het dialoogvenster Samenvoegen.

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. Vervolgens kunt u doorgaan met het selecteren van andere gegevens.

Hier volgt een voorbeeld waarin drie werkbladbereiken zijn geselecteerd:
Klik in het pop-upvenster Samenvoegen op Toevoegen. Herhaal dit om alle bereiken toe te voegen die u samenvoegt.
Automatische en 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 dit selectievakje uitgeschakeld blijft, kunt u de consolidatie handmatig bijwerken.
Notities:
- U kunt geen koppelingen maken als de bron- en doelgebieden zich op hetzelfde werkblad bevinden.
- 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. Kies de oude verwijzing en druk op de toets Delete.
Klik op OK om de consolidatie voor u te genereren. U kunt desgewenst opmaak toepassen. U hoeft maar één keer op te maken, tenzij u de consolidatie opnieuw uitvoert.
- Labels die niet overeenkomen met labels in de andere brongebieden, worden weergegeven in afzonderlijke rijen of kolommen in de samenvoeging.
- Zorg ervoor dat categorieën die u niet wilt samenvoegen unieke labels hebben die slechts in één bronbereik worden weergegeven.

Als de samen te voegen gegevens zich in verschillende cellen op verschillende werkbladen bevinden:

Voer een formule in met celverwijzingen naar de andere werkbladen, één voor elk afzonderlijk werkblad. Als u gegevens uit de werkbladen Omzet (in cel B4), HR (in cel F5) en Marketing (in cel B9) bijvoorbeeld wilt samenvoegen in cel A2 van het hoofdwerkblad, voert u het volgende in:

Excel-formuleverwijzing voor meerdere bladen

Tip: 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. Het kan ook moeilijk zijn om een fout te herkennen na het invoeren van een complexe formule.

Als de samen te voegen gegevens zich in dezelfde cellen op verschillende werkbladen bevinden:

Voer een formule in met een 3D-verwijzing die verwijst naar een bereik met werkbladnamen. Als u bijvoorbeeld gegevens in cellen A2 van Verkoop tot En met Marketing wilt samenvoegen, voert u in cel E5 van het hoofdwerkblad het volgende in:

Excel-formuleverwijzing voor 3D-bladen