Excel leent zich bij uitstek om mooie grafieken te maken zodat informatie gevisualiseerd wordt. Meestal zijn deze grafieken statisch. Dit wil zeggen dat de grafieken zijn gemaakt op basis van een statische set aan gegevens. Maar als je echt de show wilt stelen maak je je grafieken dynamisch. Het volgende voorbeeld laat zien hoe je in enkele stappen een dynamische grafiek maakt.

Stap 1: een statische tabel maken

We gaan we uit van een eenvoudig voorbeeld. De omzet van verkochte fruitsoorten per kwartaal. Hiervoor maken we eerst een statische tabel.

statische data set

 

 

De grafiek die we later gaan maken moet een dynamische grafiek worden. Dit betekent dat de gegevens waar de grafiek naar verwijst steeds moet kunnen veranderen. Hiervoor moeten we ervoor zorgen dat:

  1. Een selectie kan plaats vinden. Van welke fruitsoort moet de grafiek de informatie tonen? Hiervoor maken we een keuzelijst met behulp van gegevensvalidatie
  2. De gegevens behorende bij de geselecteerde fruitsoort moeten vervolgens opgezocht en getoond worden. Hiervoor gebruiken we de functie verticaal zoeken

Stap 2: Een keuzelijst met behulp van gegevensvalidatie

We willen een keuzelijst maken in cel A6. We selecteren daarom eerst cel A6 en kiezen vervolgens in het lint voor Gegegevens – Gegevensvalidatie

gegevensvalidatie

Er verschijnt een dialoogvenster gegevensvalidatie. In andere artikelen zullen we verder in gaan op de mogelijkheden van gegevensvalidatie. Voor nu kiezen we bij Validatiecriterium bij Toestaan voor lijst. Dit betekent dat we in veld A6 een keuzelijst willen zien.

gegevensvalidatie

Vervolgens plaatsen we de cursor in het veld Bron en we selecteren in het Excel sheet de velden A2 tot en met A4. We willen immers dat ons keuzelijstje bestaat uit de 3 fruitsoorten. Kies nu voor OK.

Gegevensvalidatie

Wanneer de voorgaande stappen goed zijn uitgevoerd zul je zien dat er in cel A6 een driehoekje verschijnt waarmee een keuzelijst (in het Engels dropdown menu) getoond wordt.

Dropdown list

Stap 3: Gegevens ophalen bij de selectie

Nu gaan we ervoor zorgen dat in de velden B6 tot en met E6 de omzet getoond wordt welke hoort bij de gekozen fruitsoort. We doen dit met behulp van de Verticaal Zoeken functie. Meer informatie over verticaal zoeken kun je hier vinden.

Zet om te beginnen de cursor in cel B6. In deze cel plaatsen we de volgende formule:

=VERT.ZOEKEN(A6;A2:E4;2;ONWAAR)

Verticaal zoeken

In feite halen we op basis van de waarde van cel A6 de omzet Q1 op die hoort bij die waarde. Om nu ook de waarden voor Q2 t/m Q4 op te halen moeten we de formule op twee punten aanpassen:

  • De formule gedeeltelijk absoluut maken. Immers, we willen altijd naar cel A6 verwijzen en de waarden moeten altijd uit de tabel A2:E4 komen. Lees eventueel eerst het artikel over relatieve en absolute cel verwijzingen.
  • Zorgen dat de formule de omzet gegevens uit de juiste kolom haalt. De formule zoals we die nu gemaakt hebben verwijst naar de tweede kolom om de omzet voor Q1 op te halen. Wanneer we bijvoorbeeld de omzet voor Q4 in cel E5 willen ophalen moet in de formule de 5e kolom opgenomen worden.

Als je de voorgaande stappen goed hebt uitgevoerd kun je in cel A6 de selectie veranderen. Automatisch moet vervolgens de corresponderende omzet in de cellen B6 t/m E6 getoond worden. We hebben in feite van een statisch set aan gegevens een dynamische set gemaakt die afhankelijk is van de selectie in cel A6. Op basis van deze dynamische gegevens kunnen we een dynamische grafiek maken.

Formule aanpassen

Stap 4: Grafiek koppelen aan de dynamische gegevens set

Tenslotte koppelen we een grafiek aan de dynamische gegevens set in cellen A6 t/m E6. Selecteer hiervoor cellen A6 t/m E6.

Selecteer cellen

Kies vervolgens voor Invoegen – Grafieken en kies voor een kolommen diagram

Invoegen grafieken

Het resultaat is een dynamische grafiek die steeds zal veranderen wanneer de selectie in veld A6 wordt aangepast.

Dynamische grafiek

Je eerste dynamische grafiek

Gefeliciteerd. Je hebt je eerste dynamische grafiek gemaakt. Oké, het is een wat eenvoudig voorbeeld maar dit is wel een belangrijke en goede basis. Zo kun je met een beetje oefening een volledig dynamisch dashboard maken met verschillende grafieken en diagrammen die steeds veranderen zodra de selectie wordt aangepast. Zeker weten dat dit een professionele indruk maakt.

Zie tenslotte ook het voorbeeld bestand DYNAMISCHE_GRAFIEK

Veel succes met Excelleren.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

Deze website gebruikt Akismet om spam te verminderen. Bekijk hoe je reactie-gegevens worden verwerkt.