Interactieve grafieken maken met dynamische elementen in Excel

Deze zelfstudie laat zien hoe u interactieve grafieken maakt met dynamische elementen in alle versies van Excel: 2007, 2010, 2013, 2016 en 2022.

Een interactieve grafiek is een grafiek waarmee de gebruiker kan bepalen welke gegevensreeksen in de grafiek moeten worden geïllustreerd, waardoor het mogelijk is om gegevens weer te geven of te verbergen met behulp van formulierbesturingselementen. Deze zijn er in vele soorten en vormen, variërend van keuzerondjes tot schuifbalken en vervolgkeuzelijsten.

Door sommige grafiekelementen dynamisch te maken, zoals de titel van de grafiek, is het op zijn beurt mogelijk om het proces van het bijwerken van uw grafieken te automatiseren wanneer u items toevoegt aan of verwijdert uit de onderliggende brongegevens.

Door interactieve grafieken met dynamische elementen te maken, kun je dus twee vliegen in één klap slaan.

Om te beginnen helpen interactieve grafieken u om enorme hoeveelheden gegevens netjes weer te geven met slechts één grafiek:

Bovendien bespaart het omzetten van sommige grafiekelementen van statisch naar dynamisch op de lange termijn talloze uren en voorkomt u dat u ooit vergeet om de grafiek bij te werken wanneer u uw gegevenstabel wijzigt.

Dat gezegd hebbende, deze tutorial is een spoedcursus in geavanceerde technieken voor het maken van grafieken in Excel: je leert hoe je interactieve grafieken kunt maken, zowel met een vervolgkeuzelijst als met een schuifbalk, en je leert hoe je de titel van je grafiek kunt maken en grafiekgegevensbereik dynamisch.

Voorbeeldgegevens

Om u te laten zien wat wat is, hebben we wat onbewerkte gegevens nodig om mee te werken. Bekijk daarom de volgende tabel met de boekverkoopcijfers van een fictieve online boekwinkel:

Een dynamische grafiektitel maken

Laten we eerst het gemakkelijke gedeelte aanpakken: de titel van uw grafiek dynamisch maken.

U kunt de titel dynamisch maken door deze te koppelen aan een specifieke cel in een werkblad, zodat eventuele wijzigingen in de waarde in die cel automatisch worden weergegeven in de grafiektitel.

En het mooie van deze methode is dat er maar een paar eenvoudige stappen nodig zijn om de titel van uw grafiek van statisch naar dynamisch te veranderen:

1. Dubbelklik op de titel van uw grafiek.
2. Klik op de Formule balk en typ "=” erin.
3. Selecteer een cel om deze toe te wijzen als uw nieuwe grafiektitel.

Op het eerste gezicht lijkt het idee vrij primitief. Maar we hebben nauwelijks de oppervlakte bekrast van de dingen die mogelijk zijn gemaakt door deze eenvoudige techniek toe te passen. Bekijk de uitgebreide zelfstudie over dynamische grafiektitels in Excel voor meer informatie.

Een dynamisch grafiekbereik maken

Volgende stap: een dynamisch grafiekbereik instellen. Nogmaals, conventioneel, elke keer dat u items toevoegt aan of verwijdert uit het gegevensbereik dat wordt gebruikt bij het maken van een Excel-diagram, moeten de brongegevens ook handmatig worden aangepast.

Gelukkig kunt u uzelf de moeite besparen om daarmee om te gaan door een dynamisch grafiekbereik te creëren dat de grafiek automatisch bijwerkt telkens wanneer de brongegevens worden gewijzigd.

In wezen zijn er twee manieren om dit aan te pakken: de tabelmethode en de benoemde bereiken-techniek. We gaan hier kort de eenvoudigere tabelmethode bespreken, maar om een ​​volledig beeld te krijgen van hoe u de kracht van dynamische grafiekbereiken in Excel kunt benutten, bekijkt u de tutorial waarin alle ins en outs in meer detail worden behandeld.

Hoe dan ook, terug naar de tafelmethode. Verander eerst het celbereik dat u wilt gebruiken om een ​​diagram op te bouwen in een tabel.

1. Selecteer het volledige gegevensbereik (A2:G6).
2. Ga naar de Invoegen tabblad.
3. Klik op de "Tafel" knop.

Als uw gegevens geen kopteksten hebben, schakelt u het selectievakje "Mijn tabel heeft koppen" doos.

Eenmaal daar wordt het gegevensbereik omgezet in een tabel.

Nu hoeft u alleen nog maar uw grafiek te plotten terwijl u de tabel als brongegevens van de grafiek gebruikt:

1. Markeer de tabel (A2:G6).
2. Navigeer naar de Invoegen tabblad.
3. Maak een 2D-diagram, bijvoorbeeld een geclusterd staafdiagram (Kolom- of staafdiagram invoegen > Geclusterde staaf).

Voila! Je bent klaar. Test het nu uit door nieuwe items in de tabel in te voegen om ze automatisch opnieuw in kaart te brengen in de grafiek.

Een interactieve grafiek maken met een vervolgkeuzelijst

Nu u uw eerste stappen hebt gezet op het gebied van geavanceerde hulpmiddelen voor gegevensvisualisatie in Excel, is het tijd om verder te gaan met het moeilijke deel: interactieve Excel-diagrammen.

Wanneer u veel gegevens in kaart wilt brengen, kunt u door interactieve functies in uw Excel-grafieken te introduceren, of het nu gaat om vervolgkeuzelijsten, selectievakjes of schuifbalken, u helpen om dashboardruimte te behouden en de grafiekplot overzichtelijker te maken, terwijl het voor de gebruiker comfortabeler wordt. om de gegevens te analyseren.

Bekijk als voorbeeld het onderstaande kolomdiagram. In plaats van een rommelige grafiek met de volledige gegevenstabel weer te geven, kan de gebruiker met slechts twee klikken inzoomen op de verkoopcijfers van een bepaald jaar door te kiezen voor deze interactieve grafiek met een vervolgkeuzelijst.

Volg gewoon de gedetailleerde stappen die hieronder worden beschreven om te leren hoe u hetzelfde kunt doen.

Stap 1: Leg de basis.

Voordat we beginnen, ga naar de Ontwikkelaar tabblad dat enkele van de geavanceerde Excel-functies bevat die we verderop gaan gebruiken. Het tabblad is standaard verborgen, maar het kost maar een paar klikken om het toe te voegen aan het lint.

Klik eerst met de rechtermuisknop op een lege ruimte in het lint en selecteer "Pas het lint aan.”

In de Excel-opties dialoogvenster, navigeer naar de Hoofdtabbladen lijst, controleer dan de “Ontwikkelaar” vak, en druk op “OKE.”

De Ontwikkelaar tabblad zou nu moeten verschijnen op het lint. Kies op dit punt een lege cel om de uitvoer van het vervolgkeuzemenu weer te geven (B8). Dit helpt Excel om de juiste gegevens te tonen op basis van welke optie is geselecteerd in de vervolgkeuzelijst.

Stap 2: Voeg de vervolgkeuzelijst toe.

Onze volgende stap is om het vervolgkeuzemenu aan het werkblad toe te voegen:

1. Ga naar de Ontwikkelaar tabblad.
2. Klik "Invoegen.”
3. Onder "Formulierbesturing”, selecteer “Combobox.”

Teken die lege keuzelijst waar je maar wilt. Ter illustratie, hier is hoe een leeg vervolgkeuzemenu eruit ziet:

Stap 3: Koppel de vervolgkeuzelijst aan de werkbladcellen.

Om ervoor te zorgen dat het menu de lijst van de jaren in de gegevenstabel importeert, moet u deze koppelen aan het respectieve gegevensbereik in het werkblad. Om dat te doen, klikt u met de rechtermuisknop op de keuzelijst en kiest u "Formaat controle.”

Doe het volgende in het dialoogvenster:

1. Voor "Invoerbereik”, markeer alle werkelijke waarden in de gegevenstabel ($A$3:$G$6).
2. Voor "Mobiele link”, selecteer de lege cel die is toegewezen voor het opslaan van de uitvoer van het vervolgkeuzemenu ($B$8).
3. Voor "Drop-down lijnen”, voer het aantal datapunten in uw dataset in (in ons geval vier) en klik op “OKE.”

Deze actie zou ertoe moeten leiden dat de namen van de gegevenspunten worden toegevoegd aan de vervolgkeuzelijst terwijl de waarde van de helpercel (B8) kenmerkt het momenteel geselecteerde item uit het menu als optie 1, 2, 3 of 4:

Stap 4: Stel de grafiekgegevenstabel in.

Aangezien de grafiek slechts één gegevensreeks tegelijk moet illustreren, moet u als tijdelijke oplossing een dynamische hulptabel opstellen die de onbewerkte gegevens doorzoekt en alleen de verkoopcijfers voor het uit de lijst gekozen jaar retourneert.

Die tabel zal uiteindelijk worden gebruikt als de brongegevens van de grafiek, waardoor de gebruiker effectief van weergave kan wisselen met het vervolgkeuzemenu.

Kopieer eerst de kop van de tabel met verkoopgegevens en laat een lege rij voor het weergeven van de gefilterde verkoopcijfers.

Maar hoe zorgt u ervoor dat de gegevens correct worden geëxtraheerd? Dat is waar de INDEX-functie in het spel komt. Voer de volgende formule in in A11 en kopieer het naar G11:

1	=INDEX(A3:A6, $B$8)

Kortom, de formule filtert door kolom Jaar (kolom A) en retourneert alleen de waarde in de rij waarvan het nummer overeenkomt met het momenteel geselecteerde item in de vervolgkeuzelijst.

Stap #5: Stel een grafiek in op basis van de dynamische grafiekgegevens.

Je hebt het moeilijkste deel doorstaan, dus je hoeft alleen nog maar een 2D-grafiek te maken op basis van de helpertabel (markeer de hulptabel [A10:G11] > Invoegen > maak een geclusterd kolomdiagram).

Als laatste detail, als u wilt dat het vervolgkeuzemenu bovenaan de grafiek wordt geplaatst, klikt u met de rechtermuisknop op de keuzelijst met invoervak, klikt u op "Volgorde”, en kies “Naar voren brengen.” (U kunt ook met de rechtermuisknop op de grafiekplot klikken, klik op "Volgorde”, en kies “Stuur naar terug.”)

Dat is het! Daar heb je je interactieve kolomdiagram gekoppeld aan het vervolgkeuzemenu.

Hoe maak je een interactieve grafiek met een schuifbalk

Eerlijk gezegd is het proces van het toevoegen van een schuifbalk aan uw Excel-diagrammen vergelijkbaar met de hierboven beschreven stappen.

Maar wat als u de verkoopdynamiek van elke boekencategorie (de kolommen) wilt analyseren, in plaats van de jaarlijkse verkoopcijfers (de rijen) weer te geven? Om dit te illustreren, kijk eens naar het interactieve lijndiagram hieronder om precies dat te doen:

Hoe trek je het aan? Nou, dat is waar de dingen een beetje ingewikkelder worden. Als je je echter aan het stapsgewijze proces hieronder houdt, zou het volgen van de instructies moeten aanvoelen als een wandeling in het park, zelfs als je een complete newbie bent.

Stap 1: Leg de basis.

Voeg, voordat u begint, de . toe Ontwikkelaar tabblad naar het lint, omdat dat de functie bevat waarmee we een schuifbalk kunnen tekenen (klik met de rechtermuisknop op een lege ruimte in het lint > pas het lint aan > Hoofdtabbladen > vink het vakje "Ontwikkelaar" aan > OK).

Om dingen als een uurwerk te laten werken, wijst u een lege cel toe voor het weergeven van de uitvoer van de schuifbalk, zodat Excel het schuifvak correct in de schuifbalk kan plaatsen (B8).

Stap 2: Teken de schuifbalk.

Onze volgende stap is om de schuifbalk in het werkblad in te voegen:

1. Ga naar de Ontwikkelaar tabblad.
2. Druk op de "Invoegen" knop.
3. Onder "Formulierbesturing”, selecteer “Schuifbalk.”

Op dit punt kunt u de schuifbalk tekenen waar u maar wilt. We zullen het later op zijn plaats verplaatsen.

Stap 2: Koppel de schuifbalk aan de werkbladgegevens.

Klik met de rechtermuisknop op de schuifbalk en selecteer "Formaatbeheer.”

In de Formaatbeheer dialoogvenster, wijzigt u de instellingen van de schuifbalk:

• Huidige waarde: Deze waarde definieert de standaardpositie van het schuifvak in de schuifbalk. Stel de waarde in op "1.”
• Minimale waarde: Deze instelling geeft de laagste waarde van de schuifbalk aan. Typ in ons geval "1’ het veld in.
• Maximale waarde: Deze parameter bepaalt de hoogste waarde van de schuifbalk en moet overeenkomen met het aantal gegevenspunten dat moet worden gebruikt voor het plotten van de interactieve grafiek. Aangezien de gegevenstabel uit zes boekcategorieën bestaat, wijzigt u de waarde in "6.”
• Incrementele verandering: Deze waarde bepaalt hoe soepel het schuifvak van positie verandert wanneer u het verplaatst. Stel de waarde altijd in op “1.”
• Pagina wijzigen: Deze waarde bepaalt hoe soepel het schuifvak van positie verandert wanneer u op het gebied tussen het schuifvak en de pijlen klikt. Stel de waarde altijd in op “1.”
• Mobiele link: Deze waarde kenmerkt de huidige positie van de scrollbox. Markeer eenvoudig de cel in het werkblad dat eerder voor dit doel is toegewezen ($B$8).

Stap 3: Maak de grafiekgegevenstabel.

Nadat u het installatieproces hebt voltooid, maakt u een nieuwe hulptabel voor het opslaan van de dynamische kaartgegevens die uit de onbewerkte gegevens zijn geëxtraheerd. De kaartgegevens passen de waarden automatisch aan, afhankelijk van de huidige positie van het schuifvak, waardoor de schuifbalk effectief aan de kaartplot wordt gekoppeld.

Om dat te doen, begin met kopiëren kolom Jaar zoals weergegeven op de onderstaande schermafbeelding (A2:A6).

Eenmaal daar, implementeert u de volgende INDEX-functie die het celbereik met de boekcategorienamen kiest en een overeenkomstige waarde retourneert op basis van de positie van het schuifvak.

Typ de volgende formule in cel B10:

1	=INDEX($B$2:$G$2, $B$8)

Importeer nu de verkoopcijfers die overeenkomen met de huidige boekcategorie door deze formule in te voeren in B11 en kopieer het naar beneden (B11:B14):

1	=INDEX($B$3:$G$6, MATCH(A11,$A$3:$A$6,0), MATCH($B$10, $B$2:$G$2, 0))

De combinatie van INDEX- en MATCH-functies helpt om nauwkeurig de waarde te retourneren van welk rij- en kolomnummer overeenkomt met de opgegeven criteria.

De formule filtert eerst door kolom Jaar ($A$3:$A$6) en kiest de rij die overeenkomt met het corresponderende jaar in de helpertabel (A11). Vervolgens doorzoekt de formule de namen van de boekcategorieën op dezelfde manier ($B$2:$G$2). Als gevolg hiervan retourneert de formule de juiste dynamische waarde die wordt aangepast wanneer u het schuifvak verplaatst.

Stap 4: Maak een grafiek op basis van de helpertabel.

Nadat de grafiekgegevenstabel is samengesteld, kunt u gewoon een eenvoudige grafiek maken om de schuifbalk in actie te zien.

Markeer de hele helpertabel (A10:B14), ga naar de Invoegen tabblad en teken een 2D-diagram, bijvoorbeeld een lijndiagram (Lijn- of vlakdiagram invoegen > Lijn met markeringen).

OPMERKING: Als u wilt dat de schuifbalk bovenaan de grafiek wordt weergegeven, klikt u met de rechtermuisknop op de schuifbalk en kiest u "Volgorde”, en selecteer “Naar voren brengen.” (U kunt ook met de rechtermuisknop op de grafiekplot klikken, klik op "Volgorde”, en kies “Stuur naar terug.”)

En zo doe je het. Je hebt zojuist alles geleerd wat je moet weten over de krachtige geavanceerde technieken die je datavisualisatiespel met grote sprongen zullen verbeteren.