Wanneer u een grafiek maakt in Excel en de brongegevens veranderen, moet u de gegevensbron van de grafiek bijwerken om ervoor te zorgen dat deze de nieuwe gegevens weerspiegelt.
Als u werkt met grafieken die regelmatig worden bijgewerkt, kunt u beter een dynamisch grafiekbereik maken.
Wat is een dynamisch grafiekbereik?
Een dynamisch diagrambereik is een gegevensbereik dat automatisch wordt bijgewerkt wanneer u de gegevensbron wijzigt.
Dit dynamische bereik wordt vervolgens gebruikt als brongegevens in een diagram. Naarmate de gegevens veranderen, wordt het dynamische bereik onmiddellijk bijgewerkt, wat leidt tot een update in de grafiek.
Hieronder ziet u een voorbeeld van een grafiek die een dynamisch grafiekbereik gebruikt.
Houd er rekening mee dat de grafiek wordt bijgewerkt met de nieuwe gegevenspunten voor mei en juni zodra de gegevens zijn ingevoerd.
Hoe maak je een dynamisch grafiekbereik in Excel?
Er zijn twee manieren om een dynamisch grafiekbereik in Excel te maken:
- Excel-tabel gebruiken
- Formules gebruiken
In de meeste gevallen is het gebruik van Excel-tabel de beste manier om dynamische bereiken in Excel te maken.
Laten we eens kijken hoe elk van deze methoden werkt.
Klik hier om het voorbeeldbestand te downloaden.
Excel-tabel gebruiken
Het gebruik van Excel-tabel is de beste manier om dynamische bereiken te maken, omdat deze automatisch worden bijgewerkt wanneer er een nieuw gegevenspunt aan wordt toegevoegd.
De Excel-tabelfunctie is geïntroduceerd in de Excel 2007-versie van Windows en als u eerdere versies hebt, kunt u deze niet gebruiken (zie het volgende gedeelte over het maken van een dynamisch grafiekbereik met behulp van formules).
Pro-tip: Om een reeks cellen naar een Excel-tabel te converteren, selecteert u de cellen en gebruikt u de sneltoets - Control + T (houd de Control-toets ingedrukt en druk op de T-toets).In het onderstaande voorbeeld kunt u zien dat zodra ik nieuwe gegevens toevoeg, de Excel-tabel wordt uitgebreid om deze gegevens als onderdeel van de tabel op te nemen (merk op dat de rand en opmaak worden uitgebreid om deze in de tabel op te nemen).
Nu moeten we deze Excel-tabel gebruiken bij het maken van de grafieken.
Hier zijn de exacte stappen om een dynamisch lijndiagram te maken met behulp van de Excel-tabel:
- Selecteer de volledige Excel-tabel.
- Ga naar het tabblad Invoegen.
- Selecteer in de Grafiekengroep de grafiek 'Lijn met markeringen'.
Dat is het!
De bovenstaande stappen zouden een lijndiagram invoegen dat automatisch wordt bijgewerkt wanneer u meer gegevens aan de Excel-tabel toevoegt.
Houd er rekening mee dat terwijl het toevoegen van nieuwe gegevens het diagram automatisch bijwerkt, het verwijderen van gegevens de gegevenspunten niet volledig zou verwijderen. Als u bijvoorbeeld 2 gegevenspunten verwijdert, toont de grafiek wat lege ruimte aan de rechterkant. Om dit te corrigeren, sleept u de blauwe markering rechtsonder in de Excel-tabel om de verwijderde gegevenspunten uit de tabel te verwijderen (zoals hieronder weergegeven).
Hoewel ik het voorbeeld van een lijndiagram heb genomen, kunt u met deze techniek ook andere diagramtypen maken, zoals kolom-/staafdiagrammen.
Excel-formules gebruiken
Zoals ik al zei, is het gebruik van een Excel-tabel de beste manier om dynamische grafiekbereiken te maken.
Als u echter om de een of andere reden geen Excel-tabel kunt gebruiken (mogelijk als u Excel 2003 gebruikt), is er een andere (enigszins gecompliceerde) manier om dynamische grafiekbereiken te maken met behulp van Excel-formules en benoemde bereiken.
Stel je hebt de dataset zoals hieronder weergegeven:
Om een dynamisch grafiekbereik van deze gegevens te maken, moeten we:
- Maak twee dynamische benoemde bereiken met behulp van de OFFSET-formule (elk één voor de kolom 'Waarden' en 'Maanden'). Als u een gegevenspunt toevoegt/verwijdert, worden deze benoemde bereiken automatisch bijgewerkt.
- Voeg een diagram in dat de benoemde bereiken als gegevensbron gebruikt.
Laat me nu elke stap in detail uitleggen.
Stap 1 - Dynamische benoemde bereiken maken
Hieronder vindt u de stappen om dynamische benoemde bereiken te maken:
- Ga naar het tabblad 'Formules'.
- Klik op ‘Naammanager’.
- Geef in het dialoogvenster Naambeheer de naam op als GrafiekWaarden en voer de volgende formule in Verwijst naar onderdeel: =OFFSET(Formula!$B$2,,,COUNTIF(Formula!$B$2:$B$100,””))
- Klik OK.
- Klik in het dialoogvenster Naambeheer op Nieuw.
- Geef in het dialoogvenster Naambeheer de naam op als GrafiekMaanden en voer de volgende formule in Verwijst naar onderdeel: =OFFSET(Formula!$A$2,,,COUNTIF(Formula!$A$2:$A$100,””))
- Klik OK.
- Klik op Sluiten.
De bovenstaande stappen hebben twee benoemde bereiken in de werkmap gemaakt: ChartValue en ChartMonth (deze verwijzen respectievelijk naar de waarden en het maandenbereik in de gegevensset).
Als u de waardekolom bijwerkt door nog een gegevenspunt toe te voegen, wordt het bereik met de naam ChartValue nu automatisch bijgewerkt om het extra gegevenspunt erin weer te geven.
De magie wordt hier gedaan door de OFFSET-functie.
In de 'ChartValue' benoemde bereikformule hebben we B2 als referentiepunt opgegeven. OFFSET-formule begint daar en breidt zich uit om alle gevulde cellen in de kolom te dekken.
Dezelfde logica werkt ook in de formule voor het benoemde bereik van ChartMonth.
Stap 2 - Maak een grafiek met deze benoemde bereiken
Nu hoeft u alleen nog een grafiek in te voegen die de benoemde bereiken als gegevensbron gebruikt.
Dit zijn de stappen om een grafiek in te voegen en dynamische grafiekbereiken te gebruiken:
- Ga naar het tabblad Invoegen.
- Klik op 'Lijn- of vlakdiagram invoegen' en voeg het diagram 'Lijn met markeringen' in. Hiermee wordt de grafiek in het werkblad ingevoegd.
- Ga met het diagram geselecteerd naar het tabblad Ontwerpen.
- Klik op Gegevens selecteren.
- Klik in het dialoogvenster ‘Gegevensbron selecteren’ op de knop Toevoegen in ‘Legend Entries (Series)’.
- Voer in het veld Reekswaarde =Formula!ChartValues in (houd er rekening mee dat u de werkbladnaam vóór het benoemde bereik moet opgeven om dit te laten werken).
- Klik OK.
- Klik op de knop Bewerken in de ‘Horizontale (Categorie) Axis Labels’.
- Voer in het dialoogvenster 'Axis Labels' =Formule!ChartMonths . in
- Klik OK.
Dat is het! Uw grafiek gebruikt nu een dynamisch bereik en wordt bijgewerkt wanneer u gegevenspunten in de grafiek toevoegt/verwijdert.
Een paar belangrijke dingen om te weten bij het gebruik van benoemde bereiken met grafieken:
- Er mogen geen lege cellen in de grafiekgegevens staan. Als er een blanco is, verwijst het genoemde bereik niet naar de juiste gegevensset (omdat het totale aantal ertoe zou leiden dat het verwijst naar een kleiner aantal cellen).
- U moet de naamgevingsconventie volgen wanneer u de bladnaam in de diagrambron gebruikt. Als de bladnaam bijvoorbeeld een enkel woord is, zoals Formule, dan kunt u =Formula!ChartValue gebruiken. Maar als er meer dan één woord is, zoals Formula Chart, dan moet u =’Formula Chart’!ChartValue gebruiken.
