Een dynamisch grafiekbereik maken in Excel

Deze zelfstudie laat zien hoe u een dynamisch grafiekbereik maakt in alle versies van Excel: 2007, 2010, 2013, 2016 en 2022.

Wanneer u een gegevensset uitvouwt of inkrimpt die wordt gebruikt voor het plotten van een grafiek in Excel, moeten standaard ook de onderliggende brongegevens handmatig worden aangepast.

Door dynamische grafiekbereiken te maken, kunt u dit gedoe echter vermijden.

Met dynamische diagrambereiken kunt u de brongegevens automatisch bijwerken telkens wanneer u waarden toevoegt aan of verwijdert uit het gegevensbereik, wat u veel tijd en moeite bespaart.

In deze tutorial leer je alles wat je moet weten om de kracht van Dynamische grafiekbereiken.

Dynamische grafiekbereiken - Inleiding

Overweeg de volgende voorbeeldgegevensset die schommelingen in de winstmarge analyseert:

Er zijn in principe twee manieren om een ​​dynamisch grafiekbereik in te stellen:

1. Het gegevensbereik converteren naar een tabel
2. Dynamische benoemde bereiken gebruiken als brongegevens van het diagram.

Beide methoden hebben hun voor- en nadelen, dus we zullen ze elk in meer detail bespreken om u te helpen bepalen welke het beste voor u werkt.

Zonder verder oponthoud, laten we aan de slag gaan.

De tabelmethode

Laat ik beginnen met u de snelste en gemakkelijkste manier te tonen om de taak te volbrengen. Dus, hier is de oefening: verander het gegevensbereik in een tabel en je bent gemakkelijker dan erwten pellen.

Op die manier wordt alles wat u typt in de cellen aan het einde van die tabel automatisch opgenomen in de brongegevens van het diagram.

Hier leest u hoe u dat in slechts twee eenvoudige stappen kunt realiseren.

Stap 1: Converteer het gegevensbereik naar een tabel.

Transformeer het celbereik met uw grafiekgegevens direct uit de poort in een tabel.

1. Markeer het volledige gegevensbereik (A1:B6).
2. Klik op de Invoegen tabblad.
3. Druk op de "Tafel" knop.

In de Tabel maken dialoogvenster, doet u het volgende:

1. Controleer nogmaals of het gemarkeerde celbereik overeenkomt met de volledige gegevenstabel.
2. Als uw tabel geen koprij bevat, schakelt u het selectievakje "Mijn tabel heeft koppen" doos.
3. Klik "OKE.“

Als resultaat zou je deze tabel moeten krijgen:

Stap 2: Maak een grafiek op basis van de tabel.

De basis is gelegd, wat betekent dat je nu een grafiek kunt opzetten met behulp van de tabel.

1. Markeer de hele tabel (A1:B6).
2. Navigeer naar de Invoegen tabblad.
3. Maak een 2D-diagram. Laten we ter illustratie een eenvoudig kolomdiagram maken (Kolom of staafdiagram invoegen > Geclusterde kolom).

Dat is het! Probeer nieuwe gegevenspunten toe te voegen om de techniek te testen onderaan de tafel om ze automatisch in kaart te brengen op de plot. Hoeveel eenvoudiger kan het worden?

OPMERKING: Met deze aanpak moet de dataset nooit bevatten lege cellen - dat zal de grafiek verpesten.

De dynamische benoemde bereikmethode

Hoewel eenvoudig toe te passen, de eerder aangetoonde, Tabel Methode: heeft een aantal serieuze nadelen. De grafiek raakt bijvoorbeeld in de war wanneer de nieuwe gegevensset kleiner wordt dan de oorspronkelijke gegevenstabel, plus soms wilt u gewoon niet dat het gegevensbereik wordt omgezet in een tabel.

Kiezen voor benoemde bereiken kan van uw kant iets meer tijd en moeite vergen, maar de techniek doet de nadelen van de tabelmethode teniet en maakt het dynamisch bereik bovendien een stuk comfortabeler om mee te werken op de lange termijn.

Stap 1: Maak de dynamische benoemde bereiken.

Stel om te beginnen de benoemde bereiken in die uiteindelijk zullen worden gebruikt als brongegevens voor uw toekomstige diagram.

1. Ga naar de formules tabblad.
2. Klik "Naam Beheerder.”
3. In de Naam Manager dialoogvenster dat verschijnt, selecteert u "Nieuw.”

In de Nieuwe naam dialoogvenster, maak een geheel nieuw benoemd bereik:

1. Typ "Kwartaal" naast de "Naam" veld. Zorg er voor uw gemak voor dat de naam van het dynamische bereik overeenkomt met de corresponderende koprijcel van kolom A (A1).
2. In de "Domein” veld, selecteer het huidige werkblad. In ons geval is dat Blad1.
3. Voer de volgende formule in de "Verwijst naar" veld: =OFFSET(Blad1!$A$2,0,0,COUNTA(Blad1!$A:$A)-1,1)

In gewoon Engels, elke keer dat u een cel in het werkblad wijzigt, retourneert de OFFSET-functie alleen de werkelijke waarden in kolom A, weglaten van de koprijcel (A1), terwijl de COUNTA-functie het aantal waarden in de kolom herberekent telkens wanneer het werkblad wordt bijgewerkt, waardoor al het vuile werk voor u wordt gedaan.

Laten we de formule in meer detail opsplitsen om u te helpen begrijpen hoe het werkt:

OPMERKING: De naam van een benoemd bereik moet beginnen met een letter of onderstrepingsteken en mag geen spaties bevatten.

Stel op dezelfde manier een ander benoemd bereik in op basis van kolom Winstmarge (kolom B) met behulp van deze formule en label deze “Winstmarge”:

1	=OFFSET(Blad1!$B$2,0,0,COUNTA(Blad1!$B:$B)-1,1)

Herhaal hetzelfde proces als uw gegevenstabel meerdere kolommen met werkelijke waarden bevat. In ons geval zou u daarom twee benoemde bereiken klaar moeten hebben voor actie:

Stap 2: Maak een lege grafiek.

We zijn door het lastigste deel gekomen. Nu is het tijd om een ​​leeg diagram in te stellen, zodat u handmatig de dynamische benoemde bereiken erin kunt invoegen.

1. Selecteer een lege cel in het huidige werkblad (Blad1).
2. Ga terug naar de Invoegen tabblad.
3. Stel elke gewenste 2D-kaart in. Voor ons voorbeeld zullen we een kolomdiagram maken (Kolom- of staafdiagram invoegen > Geclusterde kolom).

Stap 3: Voeg het benoemde bereik / bereiken toe met de werkelijke waarden.

Voeg eerst het benoemde bereik in (Winstmarge) gekoppeld aan de werkelijke waarden (kolom B) in de grafiek.

Klik met de rechtermuisknop op de lege grafiek en kies "Selecteer gegevens” uit het contextmenu.

In de Selecteer gegevensbron dialoogvenster, klik op "Toevoegen.”

In de Serie bewerken maakt u een nieuwe gegevensreeks aan:

1. Onder "Serienaam," markeer de corresponderende koprijcel (B1).
2. Onder "Seriewaarden," specificeer het benoemde bereik dat in de grafiek moet worden geplot door het volgende te typen: "=Blad1!Winst_marge.” De verwijzing bestaat uit twee delen: de namen van het huidige werkblad (=Blad1) en het respectievelijke dynamische benoemde bereik (Winstmarge). Het uitroepteken wordt gebruikt om de twee variabelen samen te binden.
3. Selecteer "OKE.”

Eenmaal daar zal Excel automatisch de waarden in kaart brengen:

Stap 4: Voeg het benoemde bereik in met de aslabels.

Vervang ten slotte de standaard categorie-aslabels door het benoemde bereik bestaande uit: kolom A (Kwartaal).

In de Selecteer gegevensbron dialoogvenster, onder "Horizontale (categorie) aslabels," selecteer de "Bewerking" knop.

Voeg vervolgens het genoemde bereik in de grafiek in door de volgende verwijzing in te voeren onder "Aslabelbereik:”

1	=Blad1!Kwartaal

Eindelijk is het kolomdiagram op basis van het dynamische diagrambereik klaar:

Bekijk dit eens: het diagram wordt automatisch bijgewerkt wanneer u gegevens in het dynamische bereik toevoegt of verwijdert.