Diverse scenario’s schetsen met een dynamische grafiek

Naast het weergeven van cijfers in tabellen wordt het presenteren in de vorm van interactieve grafieken steeds belangrijker. Tijdens vergaderingen is het dan mogelijk om diverse scenario’s en aanzichten te kiezen om beter inzicht in de cijfers te verkrijgen. In dit artikel laat ik je een zeer dynamische grafiek zien waarmee het mogelijk is in- en uit te zoomen.

Het voorbeeld ziet er als volgt uit:

De gebruiker kan via een data validatielijst 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 G). Kolommen B en C zijn hulpkolommen. De formules in kolom B zorgen ervoor dat de weergave van 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”)

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

Beide formules zijn door gekopieerd naar de laatste cel beneden.

Analyseer, presenteer en rapporteer efficiënter en effectiever met Excel

Werk je regelmatig met Excel? Ben je daarbij veel energie kwijt aan het opstellen van analyses en presentaties? Ontdek in de cursus Excel voor financieel professionals vele mogelijkheden die jou direct voordeel opleveren. Win tijd, verbeter je analyses en presenteer effectiever.

Meer informatie | Meld je direct aan

Maken van de grafiek

Je 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. Je 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 je maakt de volgende namen aan:

De volgende stap is het vervangen van de statische referenties in de grafiek door de dynamische bereiknamen. Als je de lijn in de grafiek aanklikt dan zie je 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)

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 formule ziet er als volgt uit:

=REEKS(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 àxlsx!X_Values
• Data!$C$2:$C$14 àxlsx!Y_Values

Let op: je dient de bereiknamen te laten voorafgaan door de naam van het werkboek.

Besturingselementen

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

Via het lint kies je: Ontwikkelaars à Besturingselementen à Invoegen à Formulierbesturingselementen.

De instelling van de het kringveld is als volgt:

De instelling van de horizontale schuifbalk is als volgt:

Conclusie

Met een minimum aan unieke formules en twee besturingselementen kun je 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.

Analyseer, presenteer en rapporteer efficiënter en effectiever met Excel

Werk je regelmatig met Excel? Ben je daarbij veel energie kwijt aan het opstellen van analyses en presentaties? Ontdek in de cursus Excel voor financieel professionals vele mogelijkheden die jou direct voordeel opleveren. Win tijd, verbeter je analyses en presenteer effectiever.

Meer informatie | Meld je direct aan