---

---

Mensen zijn verschillend en ook hun informatiewensen zijn verschillend.

Sommige mensen willen globale informatie zien, anderen zoveel mogelijk details.
Voor afdelingen die verantwoordelijk zijn voor rapportages blijft dit altijd een lastig fenomeen. Gelukkig bieden digitale dashboards tegenwoordig een uitkomst: gebruikers van zo’n dashboard kunnen vaak zelf de selectie van voor hen interessante onderwerpen en ook het gewenste detailniveau instellen.

In dit artikel een uitwerking hoe je een grafiek in een dashboard ‘dynamisch’ kunt maken (zie ook een eerder artikel over dynamische grafieken).

Voorbeelddata

Ik ben eigenaar van een energiecentrale (in de vorm van zonnepanelen) en uiteraard wil ik wel volgen wat de energieproductie in de loop van de tijd is.

Voor een rapportage is het dag-niveau wat gedetailleerd, daarom maken we een weekrapportage. Maar ook dan zien we door de hoeveelheid cijfers vaak door de bomen het bos niet meer.

In het tabblad Data van het Voorbeeldbestand vindt u onze weekproductie vanaf week 27 in 2011. De gegevens zijn opgeslagen in een Excel-tabel met de naam tblZon. In de derde kolom staat een formule die het jaar en de week combineert.
In cel C3: =[@Jaar]*100+[@Week]
Ofwel neem het getal uit de Jaar-kolom uit dezelfde regel (daar zorgt de @ voor), vermenigvuldig dat met 100 en tel dan de Week uit die regel er bij op.

Om hierna te weten hoeveel weken er gevuld zijn, wordt dit in een afzonderlijke cel bepaald: in cel I2 staat de formule =AANTAL(tblZon[JrWeek]). Deze turft het aantal numerieke velden in de kolom JrWeek van de tabel tblZon.

De cel I2 heeft de naam MaxAant gekregen; daar zullen we hierna regelmatig naar verwijzen.

NB we hadden in C3 ook de formule =[@Jaar]&[@Week] kunnen gebruiken, maar het resultaat is dan een tekst; dat is voor rapportage-doeleinden minder flexibel.

Grafiek-1

Een grafiek (zie het tabblad Data van het Voorbeeldbestand) laat het verloop in de tijd wel goed zien. De pieken en dalen hieronder komen overeen met de zomers en winters van de afgelopen jaren. In 6,5 jaar heeft de betreffende set van 3 panelen bijna 4.000 kWh opgeleverd.

Maar aangezien de mede-eigenaar van deze energie-centrale meer in details is geïnteresseerd zou het mooi zijn als we de resultaten per jaar afzonderlijk zouden kunnen bekijken.

NB1 de resultaten per week staan uitgezet volgens de linker- ofwel primaire as, de cumulatieve opbrengst  volgens de secundaire as.
Klik rechts op een gegevensreeks en kies dan de Reeks opmaken om te wisselen van as.

NB2 zorg bij het gebruik van 2 assen dat de schaalverdelingen logisch met elkaar overeen komen. In dit geval is de maximale weekproductie op 25 gezet, terwijl het maximum van van de rechter-as op 5.000 is vastgezet.
In dit geval is door de kleur van de cijfers op de assen duidelijk gemaakt welke gegevensreeks bij welke as hoort.

Grafiek-2

Om de details beter te kunnen beoordelen heb ik een nieuwe grafiek gemaakt, die slechts een selectie van het totaal laat zien.

Om snel de jaren te kunnen vergelijken kun je hierbij aangeven met welke week je wilt beginnen (zie tabblad Calc1 van het Voorbeeldbestand).

In cel H2 vul je het volgnummer van de gewenste eerste week in; alle overige cellen bevatten formules en mogen dus niet gewijzigd worden.

Op basis van de invoer in cel H2 (deze heeft de naam Start gekregen) wordt een Excel-tabel met de naam tblZonGed gevuld; er is ruimte gecreëerd voor 55 regels.
In de eerste kolom staat een volgnummer voor de te tonen week.
Daarnaast staat in de kolom JrWeek de formule:
=ALS(Start+[@Nr]-1>MaxAant;””;
VERSCHUIVING(tblZon[[#Kopteksten];[JrWeek]];Start+[@Nr]-1;0))

Ofwel: als in een regel de week boven het maximaal aantal uitkomt, dan wordt de cel leeg (de dubbele aanhalingstekens).
Anders wordt via de functie VERSCHUIVING het weeknummer opgehaald uit de tabel tblZon op het tabblad Data; gerekend vanaf de koptekst JrWeek in die tabel zoveel regels naar beneden als door Start (cel H2) plus het volgnummer uit de betreffende regel wordt aangegeven (nog even met 1 corrigeren).

NB zie voor uitleg over de functie Verschuiving onder andere het artikel Dynamische grafieken.

In de kolommen kWh en Cum staat een vergelijkbare formule; alleen wordt de cel niet leeg gemaakt als de week boven het maximum uitkomt, maar gelijk aan de functie NB(). Die laatste zorgt er voor, dat de grafiek in dat geval geen ‘vreemde’ daling vertoont.

In de kolommen H en J worden nog enkele zaken opgehaald en klaar gezet voor de ‘aankleding’ van de grafiek:

• in cel J3: =VERSCHUIVING(tblZonGed[[#Kopteksten];[JrWeek]];Eind-Start+1;0)
  op basis van de Start- en Eind-week wordt de naam van de week opgehaald uit de tabel van het tabblad Calc1
• in cel H4: =”(van week “&TEKST(J2;”0000-00″)&” t/m “&TEKST(J3;”0000-00″)&”)”
  diverse teksten worden aan elkaar gekoppeld door middel van het &-teken; de begin- en eind-week worden daarbij opgemaakt met een opmaakcode, zodat er een streepje tussen het jaar en de week komt
• in cel H5: =”Opbrengst zonnepanelen  set 1″&TEKEN(13)&Tekst
  ook hier worden teksten gekoppeld, in dit geval gescheiden gescheiden door een code 13, die er voor zorgt dat het vervolg op een nieuwe regel komt

Maak een titel aan in de grafiek, klik daarin dubbel en tik dan in de Formulebalk in =Titel

Grafiek-3

In een dashboard is het niet fraai als gebruikers ergens iets moeten intypen. Daarom gaan we er voor zorgen, dat op een meer intuïtieve manier de grafiek kan worden aangepast.

Allereerst maken we weer een set aan met basis-gegevens (zie tabblad Calc2 in het Voorbeeldbestand). We kunnen daar de begin-week opgeven en het aantal te tonen weken.

In plaats van het aanmaken van een hulptabel zoals bij Grafiek-2 leggen we alle voor de grafiek benodigde gegevens vast in zelf-gedefinieerde namen:

1. kies in de menu-tab Formules in het blok Gedefinieerde namen de optie Naam definiëren
2. de eerste naam wordt WkCalc2
3. en de bijbehorende verwijzing:
   =VERSCHUIVING(tblZon[[#Kopteksten];[JrWeek]];Calc2!Start;0;Aantal)
   De laatste parameter (Aantal) zorgt er voor, dat het resultaat niet 1 cel is, maar een heel bereik.
4. op dezelfde manier voegen we nog de naam DataCalc2 toe met de verwijzing
   =VERSCHUIVING(tblZon[[#Kopteksten];[ kWh]];Calc2!Start;0;Aantal)
5. en dan nog de naam CumCalc2 met
   =VERSCHUIVING(tblZon[[#Kopteksten];[ Cum]];Calc2!Start;0;Aantal)
6. bij het aanmaken van de grafiek worden nu als verwijzingen naar de benodigde reeksen deze nieuwe namen gebruikt.
   LET OP: plaats ook een verwijzing naar een tabblad of werkmap voor de naam (inclusief uitroepteken), bijvoorbeeld voor de x-waarden =Calc2!WkCalc2

Door nu onder de grafiek schuifbalken te plaatsen, die gekoppeld zijn aan de Start– en Aantal-cellen van het tabblad Calc2 ontstaat er een handige, interactieve grafiek (zie het tabblad DashBoard van het Voorbeeldbestand).

1. kies in de menu-tab Ontwikkelaars in het blok Besturingselementen de optie Invoegen.
2. klik dan binnen het blok Formulierbesturingselementen op de optie Schuifbalk
3. geef met de cursor in de sheet aan waar de balk moet komen.
4. daarna kan na rechts-klikken op de schuifbalk het besturingselement opgemaakt worden
5. maak een koppeling met de cel die gewijzigd moet worden door de schuifbalk
6. vul een minimum- en een maximumwaarde in
7. vul de 2 wijzigingswaarden in. De eerste is de stap waarmee de waarde in de cel moet wijzigen als op de pijltjes aan de zijkant geklikt wordt; de tweede als in de balk links of rechts van het schuifje wordt geklikt

---