Wanneer u in Excel een grafiek maakt en de brongegevens veranderen, moet u de gegevensbron van de grafiek bijwerken om ervoor te zorgen dat deze de nieuwe gegevens weergeeft.
Wanneer u werkt met diagrammen die vaak worden bijgewerkt, kunt u beter een dynamisch grafiekbereik maken.
Deze Tips & Tricks behandelt:
Wat is een dynamisch grafiekbereik?
Een dynamisch grafiekbereik is een gegevensbereik dat automatisch wordt bijgewerkt wanneer u de gegevensbron wijzigt.
Dit dynamische bereik wordt dan gebruikt als de brongegevens in een grafiek. Wanneer de gegevens veranderen, wordt het dynamische bereik onmiddellijk bijgewerkt, wat tot een update in de grafiek leidt.
Hieronder ziet u een voorbeeld van een grafiek die gebruikmaakt van een dynamisch grafiekbereik.
Merk op dat de grafiek wordt bijgewerkt met de nieuwe gegevenspunten voor mei en juni zodra de gegevens worden ingevoerd.
Hoe maak je een dynamisch grafiekbereik in Excel?
Er zijn twee manieren om een dynamisch grafiekbereik in Excel te maken:
- Gebruik Excel-tabel
- Gebruik formules
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.
Met behulp van Excel Tabel
Het gebruik van Excel Tabel is de beste manier om dynamische bereiken te maken, omdat het automatisch wordt bijgewerkt wanneer een nieuw gegevenspunt wordt toegevoegd aan het.
Excel Tabel functie werd geïntroduceerd in Excel 2007 versie van Windows en als je versies voorafgaand aan het, zult u niet in staat zijn om het te gebruiken (zie de volgende paragraaf over het maken van dynamische grafiek bereik met behulp van formules).
In het onderstaande voorbeeld kunt u zien dat zodra ik nieuwe gegevens toevoeg, de Excel-tabel zich uitbreidt om deze gegevens op te nemen als onderdeel van de tabel (merk op dat de rand en de opmaak zich uitbreiden om deze in de tabel op te nemen).
Nu moeten we deze Excel-tabel gebruiken tijdens het maken van de grafieken.
Hier volgen de exacte stappen voor het maken van een dynamisch lijndiagram met behulp van de Excel-tabel:
- Selecteer de volledige Excel-tabel.
- Ga naar het tabblad Invoegen.
- In de groep Grafieken selecteert u de grafiek ‘Lijn met markeringen’.
Dat is alles!
De bovenstaande stappen zouden een lijndiagram invoegen dat automatisch wordt bijgewerkt wanneer u meer gegevens aan de Excel-tabel toevoegt.
Merk op dat terwijl het toevoegen van nieuwe gegevens het diagram automatisch bijwerkt, het verwijderen van gegevens de gegevenspunten niet volledig zou verwijderen. Bijvoorbeeld, als u 2 datapunten verwijdert, zal de grafiek wat lege ruimte tonen 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 getoond).
Hoewel ik het voorbeeld van een lijndiagram heb genomen, kunt u met deze techniek ook andere diagramtypen zoals kolom/staafdiagrammen maken.
Excel-formules gebruiken
Zoals gezegd, is het gebruik van Excel-tabel de beste manier om dynamische grafiekbereiken te maken.
Mocht u echter om de een of andere reden geen gebruik kunnen maken van Excel tabel (mogelijk als u Excel 2003 gebruikt), dan is er een andere (enigszins gecompliceerde) manier om dynamische grafiekbereiken te maken met behulp van Excel formules en benoemde bereiken.
Vooropgesteld dat u beschikt over de gegevensreeks zoals hieronder afgebeeld:
Om van deze gegevens een dynamisch grafiekbereik te maken, moeten we:
- twee dynamische, benoemde bereiken maken met behulp van de OFFSET-formule (één voor elk van de kolommen ‘Waarden’ en ‘Maanden’). Het toevoegen/verwijderen van een gegevenspunt zou automatisch deze benoemde bereiken bijwerken.
- Een grafiek invoegen die de benoemde bereiken als gegevensbron gebruikt.
Laat me elke stap nu in detail uitleggen.
Stap 1 – Dynamische benoemde bereiken creëren
Hieronder volgen de stappen om dynamische benoemde bereiken te creëren:
- Ga naar het tabblad ‘Formules’.
- Klik op ‘Naambeheer’.
- In het dialoogvenster Naambeheer specificeert u de naam als Grafiekwaarden en voert u de volgende formule in het gedeelte Verwijst naar: =OFFSET(Formule!$B$2,,,COUNTIF(Formula!$B$2:$B$100,”<>”))
- Klik op OK.
- In het dialoogvenster Name Manager klikt u op New.
- In het dialoogvenster Naammanager specificeert u de naam als GrafiekMaand en voert u in het deel Verwijzingen naar de volgende formule in: =OFFSET(Formule!$A$2,,,COUNTIF(Formule!$A$2:$A$100,”<>”))
- Klik op 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 maandbereik in de gegevensverzameling).
Als u de waardekolom bijwerkt door een gegevenspunt toe te voegen, wordt het genoemde bereik ChartValue nu automatisch bijgewerkt om het extra gegevenspunt weer te geven.
De magie wordt hier verricht door de OFFSET-functie.
In de formule voor het genoemde bereik ‘ChartValue’ hebben we B2 opgegeven als referentiepunt. De OFFSET-formule begint daar en strekt zich uit tot alle gevulde cellen in de kolom.
Dezelfde logica werkt ook in de formule voor het benoemde bereik ChartMonth.
Stap 2 – Een diagram maken met behulp van deze benoemde bereiken
Nu hoeft u alleen nog maar een diagram in te voegen dat de benoemde bereiken als gegevensbron zal gebruiken.
Hier volgen de stappen voor het invoegen van een diagram en het gebruik van dynamische diagrambereiken:
- Ga naar het tabblad Invoegen.
- Klik op ‘Lijn- of Gebiedsgrafiek invoegen’ en voeg de ‘Lijn met markeringen’-grafiek in. Hierdoor wordt het diagram in het werkblad ingevoegd.
- Met het diagram geselecteerd, gaat u naar het tabblad Ontwerp.
- Klik op Gegevens selecteren.
- In het dialoogvenster ‘Gegevensbron selecteren’ klikt u op de knop Toevoegen in ‘Legenda-entries (Reeksen)’.
- In het veld Reekswaarde voert u =Formula!ChartValues (merk op dat u de naam van het werkblad vóór de genoemde reeks moet opgeven om dit te laten werken).
- Klik op OK.
- Klik op de knop Edit in de ‘Horizontal (Category) Axis Labels’.
- In het dialoogvenster ‘Aslabels’ voert u in =Formula!ChartMonths
- Klik op Ok.
Dat is het! Uw grafiek gebruikt nu een dynamisch bereik en wordt bijgewerkt wanneer u gegevenspunten in de grafiek toevoegt of verwijdert.
Een paar belangrijke dingen die u moet weten wanneer u benoemde bereiken met grafieken gebruikt:
- Er mogen geen lege cellen in de grafiekgegevens zijn. Als er lege cellen zijn, zou het benoemde bereik niet naar de juiste gegevensreeks verwijzen (omdat de totaaltelling ertoe zou leiden dat het naar minder cellen zou verwijzen).
- U moet de naamgevingsconventie volgen bij het gebruik van de bladnaam in de grafiekbron. Bijvoorbeeld, als de bladnaam uit één woord bestaat, zoals Formule, dan kunt u =Formula!ChartValue gebruiken. Maar als er meer dan één woord is, zoals Formulegrafiek, dan moet u =’Formulegrafiek’!GrafiekValue.
Je vindt de volgende Excel-tutorials misschien ook leuk:
- Hoe maak ik een thermometertabel in Excel.
- Hoe maak ik een klokgrafiek in Excel?
- Een stapdiagram maken in Excel?
- Een Pareto-diagram maken in Excel?
- Hoe maak ik een histogram in Excel?
- Hoe voeg ik foutenbalken toe in Excel (horizontaal/verticaal/aangepast)