Naast het weergeven van cijfers in tabellen wordt het presenteren in de vorm van interactieve grafieken steeds belangrijker. Het is dan mogelijk om de aandacht te kunnen verleggen naar een bepaalde tijdshorizon. In deze bijdrage laat ik u een zeer dynamische grafiek zien waarmee het mogelijk is om de regio te selecteren alsmede de tijdshorizon door middel van een zoomfactor en een beweegbare tijdas.

Voorbeeld van een dynamische grafiek

Het voorbeeld ziet er als volgt uit:

De gebruiker kan via een datavalidatielijst de regio selecteren. Daarnaast is het mogelijk om via het kringveld de zoomfactor (= tijdshorizon) in te stellen. De  horizontale schuifbalk onder de grafiek maakt het mogelijk om door de tijd heen te lopen. In alle gevallen past de grafiek zich automatisch aan.

Opzet van het model

We starten met een datablad en de volgende indeling:

De tabel bevat per maand alle historische omzetcijfers van de vier filialen East, West, North en South (kolommen D tot en met G). Kolommen B en C zijn hulpkolommen. De formules in kolom B zorgen ervoor dat de datum in de grafiek leesbaar wordt weergegeven. De formules in kolom C zijn gekoppeld aan de cel K1, waar de gebruiker de regio kan kiezen.

We gebruiken de volgende bereiknamen:

De volgende formules zijn in het datablad ingebouwd:

B2=TEKST(A2;”mm”)&TEKEN(13)&TEKST(A2;”jj”)
(B2=TEXT(A2,”mm”)&CHAR(13)&TEXT(A2,”jj”)

C2=INDEX(D:G;RIJ();VERGELIJKEN(Region;$D$1:$G$1;0))
(C2=INDEX(D:G,ROW(),MATCH(Region,$D$1:$G$1,0)))

Beide formules zijn door gekopieerd naar de laatste cel beneden.

Maken van de grafiek

U dient eerst een statische grafiek te maken door het markeren van het gebied B1 tot en met C202 (= laatste cel in de kolom  C). Kies uit het lint Invoegen à Grafieken à 2D lijn. U kunt de grafiek naar eigen believen opmaken.

Om de grafiek dynamisch te maken gaat u de volgende dynamische bereiknamen aanmaken. Via het lint kiest u Formules à Gedefinieerde Namen à Namen beheren en u maakt de volgende namen aan:

De volgende stap is het vervangen van de statische referenties in de grafiek door de dynamische bereiknamen. Als u de lijn in de grafiek aanklikt dan ziet u de bijbehorende formule verschijnen in de formulebalk.

De formule luidt als volgt:

=REEKS(Data!$C$1;Data!$B$2:$B$14;Data!$C$2:$C$14;1)
(=SEQUENCE(Data!$C$1,Data!$B$2:$B$14,Data!$C$2:$C$14,1))

De formule bestaat uit de volgende vier onderdelen:

1. Data!$C$1 > verwijst naar de naam van de regio
2. Data!$B$2:$B$14 > verwijst naar de datums
3. Data!$C$2:$C$14 > verwijst naar de omzetcijfers
4. 1 > verwijst naar de eerste dataset

De verwijzingen naar de datums en omzetcijfers dienen te worden vervangen door de dynamische bereiknamen. De nieuwe uiteindelijke formule ziet er als volgt uit:

=REEKS(Data!$C$1;DynamicChart.xlsx!X_Values;DynamicChart.xlsx!Y_Values;1)
(=SEQUENCE(Data!$C$1,DynamicChart.xlsx!X_Values,DynamicChart.xlsx!Y_Values,1))

De datums en omzetcijfers worden als volgt vervangen:

• Data!$B$2:$B$14 > DynamicChart.xlsx!X_Values
• Data!$C$2:$C$14 > DynamicChart.xlsx!Y_Values

Let op: u dient de bereiknamen te laten voorafgaan door de naam van het werkboek!

Besturingselementen

U plaatst een horizontale schuifbalk onder de grafiek en een kringveld rechts naast de zoomfactor (L2).

Via het lint kiest u Ontwikkelaars > Besturingselementen > Invoegen > Formulierbesturingselementen (Developer > Controls > Insert > Form Controls)

De instelling van de het kringveld is als volgt:

De instelling van de horizontale schuifbalk is als volgt:

Conclusie

Zoals u ziet, kunt u met een minimum aan unieke formules en twee besturingselementen een professionele dynamische grafiek maken die zeer flexibel te bekijken is en daardoor inzichten kan verschaffen die met normale grafieken niet aan de oppervlakte zullen komen.

Auteur: Tony De Jonker

Dit artikel is verschenen in cm: 2022, afl. 8.

Hier leest u meer Excel tips en trucs door Tony De Jonker

Volg CMWeb op LinkedIn!