Hoe maak je een grafiek aan over/vanuit meerdere werkbladen in Excel?

Wanneer je doel is om reeksen te vergelijken over verschillende werkbladen heen met dezelfde gestructureerde gegevens, zoals verkoop per regio in vier verschillende maanden die zich in afzonderlijke tabbladen bevinden, biedt het maken van een samengevoegde kolomgrafiek met meerdere gegevensreeksen een effectieve visuele vergelijking.

Deze methode vereist handmatige selectie, maar is eenvoudig en vereist geen complexe formules of macro's. Het werkt het best wanneer je werkbladen een identieke indeling hebben en je een grafiek direct uit de bronsheets moet samenstellen.

Volg deze stappen om je grafiek in te stellen:

1. Klik op Invoegen > Kolomgrafiek invoegen (of Kolom) > Gegroepeerde kolom. Dit opent een lege grafiek in het werkblad.

2. Klik met de rechtermuisknop op de nieuw ingevoegde lege grafiek en selecteer Gegevens selecteren in het contextmenu.

3. Klik in het dialoogvenster Gegevensbron selecteren op de Toevoegen knop om een nieuwe gegevensreeks toe te voegen.

4. Voer in het dialoogvenster Reeks bewerken de naam van de reeks in en specificeer de reekswaarden door naar het relevante werkblad te navigeren en het vereiste gegevensbereik te selecteren. Controleer dubbel of de verwijzingen nauwkeurig zijn, omdat fouten in verwijzingen kunnen leiden tot incorrect weergegeven gegevens of fouten zoals #VERW. Klik op OK om te bevestigen.

Tip: Om gegevens uit een ander werkblad in het vak reekswaarden te verwijzen, ga je naar het doelwerkblad en selecteer je het gewenste bereik. Excel zal automatisch de naam van het werkblad in de verwijzing opnemen.

5. Herhaal stappen 3 en 4 voor elk werkblad dat je in de grafiek wilt opnemen. Nadat je alle reeksen hebt toegevoegd, zie je ze vermeld onder Legenda-items (Reeksen) in het dialoogvenster.

Opmerking: Zorg ervoor dat je altijd dezelfde celbereikstructuur kiest op elk blad; inconsistenties kunnen de grafiek misleidend maken of problemen veroorzaken bij uitlijning.

6. Om je grafiek fijn te stemmen, klik je op Bewerken onder Horizontale (Categorie) Aslabels in het venster Gegevensbron selecteren. Selecteer in het dialoogvenster Aslabels de relevante labels om correct uitgelijnd te zijn met je gegevens. Klik op OK wanneer je klaar bent.

7. Sluit het dialoogvenster Gegevensbron selecteren door op OK te klikken. Je grafiek combineert nu gegevensreeksen uit meerdere werkbladen.

8. (Optioneel) Om de visuele duidelijkheid te verbeteren, selecteer je de grafiek, ga je naar Ontwerp > Grafiekelement toevoegen > Legenda, en kies je een optie (zoals Legenda > Onderaan) om legendes weer te geven die elke reeks identificeren.

Deze aanpak is ideaal voor statische datasets. Als de indeling van je werkbladen echter afwijkt, of als het gegevensbereik verschilt tussen de bladen, moet je deze verschillen eerst aanpakken. Als je gegevens vaak worden bijgewerkt of automatische consolidatie nodig hebben, overweeg dan de andere onderstaande oplossingen voor meer efficiëntie.

Hier is de resulterende gegroepeerde kolomgrafiek, die visueel gegevensreeksen vergelijkt die afkomstig zijn uit vier verschillende werkbladen:

In gevallen waarin je een grafiek wilt maken door individuele gegevenspunten uit verschillende werkbladen te selecteren, in plaats van hele reeksen, kun je eerst de doelcellen verzamelen in een samenvattingsblad en deze vervolgens samen in een grafiek weergeven. Dit is gebruikelijk wanneer je bijvoorbeeld één metriek, zoals de 'Totaal'-waarde, wilt vergelijken vanuit meerdere afdelingsbladen.

Kutools voor Excel’s Automatisch werkbladverwijzingen verhogen biedt een efficiënte manier om specifieke waarden uit meerdere bladen te extraheren naar een enkel samenvattingsblad, en is vooral handig voor dynamische gegevens waarbij bronwaarden in de loop van de tijd kunnen veranderen.

Hier is hoe je gegevenspunten kunt verzamelen en de grafiek kunt maken:

1. Klik op de werkbladtabbalk op de Nieuw knop of om een nieuw werkblad te maken voor consolidatie.

2. Selecteer in dit nieuwe werkblad de cel waar je gegevens uit andere bladen wilt extraheren. Ga vervolgens naar Kutools > Meer (onder de Formule groep) > Automatisch werkbladverwijzingen verhogen.

3. Doe het volgende in het dialoogvenster Werkbladverwijzingen invullen:

• Selecteer Verticaal vullen cel na cel in de keuzelijst Vulvolgorde. Dit organiseert de geretourneerde waarden in een verticale lijst.
• Controleer de werkbladen die de cellen bevatten die je wilt refereren, en zorg ervoor dat je alleen de relevante bronbladen selecteert.
• Klik op Bereik invullen om de waarden te halen, en daarna Sluiten wanneer je klaar bent.
  

Tip: Je kunt deze stap herhalen als je verschillende soorten gegevens moet verzamelen, en ze indien nodig organiseren als verschillende kolommen of rijen in je samenvattingsblad.

Na deze stappen zie je de gegevens die je uit elk werkblad hebt geselecteerd netjes georganiseerd in je nieuwe blad.

4. Markeer de samengevoegde gegevenspunten en ga vervolgens verder met het maken van de grafiek zoals gebruikelijk: Invoegen > Kolomgrafiek invoegen (of Kolom) > Gegroepeerde kolom.

Nu heb je een gegroepeerde kolomgrafiek gemaakt die visueel geselecteerde gegevenspunten vergelijkt, elk afkomstig van een ander werkblad.

Tips:

• Deze methode werkt het best voor dynamisch bij te werken grafieken, omdat de links automatisch kunnen vernieuwen wanneer de brongegevens veranderen (mits je directe verwijzingen of formules gebruikt).
• Controleer de namen van de brongegevensbladen als je #VERW!-fouten tegenkomt, omdat hernoemde/verwijderde bladen de verwijzingen zullen breken.

Demo: maak een grafiek aan over/vanuit meerdere werkbladen in Excel

Voor gebruikers die op zoek zijn naar een meer geautomatiseerde en schaalbare oplossing – vooral bij het omgaan met een groot aantal werkbladen of bij routine-updates – kan een VBA-macro efficiënt gegevens uit meerdere bladen verzamelen en onmiddellijk een grafiek genereren. VBA stelt je in staat het consolidatieproces aan te passen, waardoor het geschikt is voor gevorderde gebruikers of iedereen die regelmatig rapportages bijwerkt.

Voordelen: Automatisering, hoge flexibiliteit voor aangepaste behoeften, werkt goed met talloze werkbladen.
Potentiële nadelen: Vereist toestemming om macro's uit te voeren, en sommige gebruikers zijn mogelijk niet bekend met VBA-syntaxis of probleemoplossing.

Je kunt het volgende voorbeeld gebruiken om een specifieke cel (bijvoorbeeld B2) uit alle werkbladen te combineren in een nieuw samenvattingsblad, en vervolgens een gegroepeerde kolomgrafiek te maken van de gecombineerde gegevens:

1. Klik Ontwikkelaarsgereedschappen > Visual Basic om het Microsoft Visual Basic for Applications-venster te openen. Klik op Invoegen > Module, en plak vervolgens de onderstaande code in de module:

Sub CombineDataAndChart()
    Dim ws As Worksheet
    Dim summarySheet As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    Dim wsCount As Integer
    Dim i As Integer
    Dim rng As Range
    
    On Error Resume Next
    
    ' Create summary sheet or clear previous one
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If ws.Name = "SummaryChartData" Then
            ws.Delete
            Exit For
        End If
    Next
    Application.DisplayAlerts = True
    
    Set summarySheet = Worksheets.Add
    summarySheet.Name = "SummaryChartData"
    
    destRow = 1
    
    ' Set header
    summarySheet.Cells(destRow, 1).Value = "Sheet"
    summarySheet.Cells(destRow, 2).Value = "Value"
    destRow = destRow + 1
    
    ' Collect data from all sheets (change range as needed)
    For Each ws In Worksheets
        If ws.Name <> "SummaryChartData" Then
            summarySheet.Cells(destRow, 1).Value = ws.Name
            summarySheet.Cells(destRow, 2).Value = ws.Range("B2").Value ' Modify "B2" as needed
            destRow = destRow + 1
        End If
    Next
    
    ' Create chart
    Dim chartObj As ChartObject
    Set chartObj = summarySheet.ChartObjects.Add(Left:=250, Width:=350, Top:=20, Height:=250)
    
    chartObj.Chart.ChartType = xlColumnClustered
    chartObj.Chart.SetSourceData Source:=summarySheet.Range("A1:B" & destRow - 1)
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Combined Data from All Sheets"
    
    xTitleId = "KutoolsforExcel"
End Sub

2. Klik op de Uitvoeren-knop in de VBA-editor om de code uit te voeren. De macro zal automatisch een samenvattingsblad ("SummaryChartData") aanmaken, gegevens verzamelen (in dit voorbeeld de waarde in cel B2) uit alle werkbladen behalve het samenvattingsblad, en een grafiek bouwen op basis van de samengevoegde gegevens.

Opmerking:

• Als je een andere cel uit elk werkblad wilt extraheren, pas dan de ws.Range("B2")-verwijzing dienovereenkomstig aan.
• Om meer kolommen of flexibele bereiken op te nemen, kun je de logica van de code uitbreiden of door kolomindices lussen.
• Als er conflicten met werkbladnamen optreden, zal de macro automatisch het samenvattingsblad overschrijven of opnieuw aanmaken indien nodig.
• Zorg ervoor dat jouw Excel-instellingen het uitvoeren van macro's toestaan voordat je macro's uitvoert.

Als je fouten tegenkomt, controleer dan dubbel de werkbladnamen, de gespecificeerde celverwijzingen en controleer dat geen enkel werkblad beveiligd of verborgen is.

Cellen spiegelen/linken over werkbladen heen in Excel

Een benoemd bereik definiëren over werkbladen heen in Excel

Gegevens kopiëren van/naar meerdere werkbladen in Excel?

Voorwaardelijke opmaak toepassen over werkbladen/werkboeken