Hoe maakt u een grafiek op basis van gegevens uit meerdere werkbladen in Excel?

Wanneer u reeksen wilt vergelijken over verschillende werkbladen met dezelfde gestructureerde gegevens – zoals verkoopcijfers per regio in vier opeenvolgende maanden, elk op een apart tabblad – dan biedt een geconsolideerd kolomdiagram met meerdere gegevensreeksen een krachtige visuele vergelijking.

Deze methode vereist handmatige selectie, maar is eenvoudig en heeft geen complexe formules of macro’s nodig. Ze werkt het beste wanneer uw werkbladen een identieke lay-out hebben en u rechtstreeks een grafiek wilt samenstellen op basis van de brondocumenten.

Volg deze stappen om uw grafiek in te stellen:

1. Klik op Invoegen > Grafiek invoegen Kolomdiagram(of)Kolom) > Gegroepeerde kolom. Hiermee wordt een lege grafiek geopend 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 Gegevens selecteren-bron op de knop Toevoegen om een nieuwe gegevensreeks toe te voegen.

4. Voer in het dialoogvenster Reeks bewerken de reeksnaam in en geef de reekswaarden op door naar het betreffende werkblad te navigeren en het gewenste gegevensbereik te selecteren. Controleer of de verwijzingen kloppen—want fouten hierin kunnen ertoe leiden dat de grafiek onjuiste gegevens of fouten zoals #REF! weergeeft. Klik op OK om te bevestigen.

Tip: Om gegevens van een ander werkblad te verwijzen in het vak Reekswaarden, schakelt u over naar het doelblad en selecteert u het gewenste bereik. Excel voegt dan automatisch de Werkbladnaam toe aan de verwijzing.

5. Herhaal stappen 3 en 4 voor elk werkblad dat u in de grafiek wilt opnemen. Nadat u alle reeksen hebt toegevoegd, ziet u ze vermeld onder Bereik van de reeksnaam in het dialoogvenster.

Opmerking: Zorg ervoor dat u op elk werkblad altijd hetzelfde celbereik selecteert; inconsistenties kunnen de grafiek misleidend maken of uitlijningsproblemen veroorzaken.

6. Klik onder Bewerken in het venster Gegevens selecteren-bron op Horizontale aslabels. Selecteer in het dialoogvenster Bereik van de aslabels de relevante labels zodat deze correct worden uitgelijnd met uw gegevens. Klik op OK wanneer u klaar bent.

7. Sluit het dialoogvenster Gegevens selecteren-bron door op OK te klikken. Uw grafiek combineert nu gegevensreeksen uit meerdere werkbladen.

8. (Optioneel) Selecteer de grafiek en ga naar Ontwerp > Grafiekelement toevoegen > Legenda, en kies een optie (zoals)Legenda > Onderaan) om een legenda weer te geven die elke reeks identificeert.

Deze aanpak is ideaal voor statische datasets. Als de lay-outs van uw werkbladen echter afwijken of de Gegevensbereik per blad verschilt, moet u deze verschillen eerst aanpakken. Als uw gegevens regelmatig worden bijgewerkt of geautomatiseerde consolidatie vereisen, kunt u overwegen de onderstaande alternatieve oplossingen te gebruiken voor meer efficiëntie.

Hier is de resulterende Gegroepeerde kolomgrafiek, die gegevensreeksen uit vier verschillende werkbladen visueel met elkaar vergelijkt:

Als u een grafiek wilt samenstellen door individuele datapunten uit verschillende werkbladen te selecteren in plaats van complete reeksen, kunt u eerst de doelcellen verzamelen in een overzichtsblad en deze daarna samen in één grafiek weergeven. Dit komt vaak voor wanneer u één enkele meetwaarde – zoals de ‘Totaal’-waarde – uit meerdere afdelingsbladen wilt vergelijken.

Kutools voor Excel’s Automatisch werkbladverwijzingen verhogen biedt een efficiënte manier om specifieke waarden uit meerdere bladen te extraheren naar één overzichtsblad en is vooral handig voor dynamische gegevens waarvan de brondocumentwaarden in de loop van de tijd kunnen veranderen.

Zo verzamelt u datapunten en maakt u de grafiek:

1. Klik op de knop Nieuw of op de tabbladbalk van het werkblad om een nieuw werkblad voor consolidatie aan te maken.

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

3. Voer in het dialoogvenster Vul werkbladverwijzingen het volgende uit:

• Selecteer Vullen per kolom, dan per rij uit de Vulvolgorde-vervolgkeuzelijst. Hierdoor wordt de Retourneerwaarde in een verticale lijst georganiseerd.
• Vink de werkbladen aan die de cellen bevatten waarnaar u wilt verwijzen, en zorg ervoor dat u alleen de relevante brontabbladen selecteert.
• Klik op Bereik invullen om de waarden op te halen en klik daarna op Sluiten wanneer u klaar bent.
  

Tip: Herhaal deze stap als u verschillende soorten gegevens wilt verzamelen en deze naar wens als afzonderlijke kolommen of rijen in uw overzichtsblad wilt ordenen.

Na deze stappen ziet u de gegevens die u uit elk werkblad hebt geselecteerd netjes georganiseerd in uw nieuwe blad.

4. Markeer de geconsolideerde datapunten en maak daarna op de gebruikelijke manier de grafiek: Invoegen > Grafiek invoegen Kolomdiagram(of)Kolom) > Gegroepeerde kolom.

U hebt nu een gegroepeerde kolomgrafiek gemaakt die de gegevensselectiepunten visueel met elkaar vergelijkt, waarbij elk punt afkomstig is van een ander werkblad.

Tips:

• Deze methode levert de beste resultaten voor dynamisch bijgewerkte grafieken, omdat de koppelingen automatisch vernieuwen zodra de brongegevens wijzigen (mits u directe verwijzingen of formules gebruikt).
• Controleer de brondocumenten op werkbladnamen als u #REF!-fouten tegenkomt, omdat hernoemde of verwijderde werkbladen de verwijzingen ongeldig maken.

Demo: grafiek maken over/uit meerdere werkbladen in Excel

Voor gebruikers die een meer geautomatiseerde en schaalbare oplossing zoeken – vooral bij een groot aantal werkbladen of bij regelmatige updates – kan een VBA-macro efficiënt gegevens uit meerdere bladen verzamelen en direct een grafiek genereren. Met VBA kunt u het consolidatieproces op maat aanpassen, waardoor het geschikt is voor gevorderde gebruikers of iedereen die zijn rapportages regelmatig bijwerkt.

Voordelen: Automatisering, hoge flexibiliteit voor aangepaste behoeften en naadloze integratie met meerdere werkbladen.
Mogelijke nadelen: Vereist toestemming om macro’s uit te voeren, en sommige gebruikers zijn mogelijk niet vertrouwd met de VBA-syntax of probleemoplossing.

U kunt het volgende voorbeeld gebruiken om een specifieke cel (bijvoorbeeld B2) uit alle werkbladen samen te voegen in een nieuw overzichtsblad en vervolgens een Gegroepeerde kolomgrafiek te maken op basis van de gecombineerde gegevens:

1. Klik op Hulpmiddelen voor ontwikkelaars > Visual Basic om het Microsoft Visual Basic for Applications-venster te openen. Klik op Invoegen > Module en plak 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 knop Uitvoeren in de VBA-editor om de code uit te voeren. De macro maakt automatisch een samenvattingstabel (‘SummaryChartData’), verzamelt gegevens (in dit voorbeeld de waarde in cel B2) van alle werkbladen, behalve het samenvattingsblad, en genereert een grafiek op basis van die gegevens.

Opmerking:

• Als u een andere cel uit elk werkblad wilt extraheren, past u de verwijzing ws.Range("B2") dienovereenkomstig aan.
• Om meer kolommen of flexibele bereiken op te nemen, kunt u de codelogica uitbreiden of door kolomindexen itereren.
• Als zich conflicten met de werkbladnaam voordoen, overschrijft of hermaakt de macro automatisch het overzichtsblad, indien nodig.
• Zorg ervoor dat uw Excel-instellingen het uitvoeren van macro’s toestaan, vóórdat u ze uitvoert.

Als u fouten tegenkomt, controleer dan nogmaals de werkbladnaam, de opgegeven celverwijzingen en of geen enkel werkblad beveiligd of verborgen is.

Cellen spiegelen/koppelen tussen werkbladen in Excel

Een benoemd bereik definiëren over meerdere werkbladen in Excel

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

Voorwaardelijke opmaak gebruiken toepassen op meerdere werkbladen/werkmappen