Tagarchief: Grafiek

Interactieve grafiek



Deze week zag ik in de Volkskrant een mooie grafiek; daarin kon je niet alleen zien, welke 20 personen in 2018 in Nederland het meeste invloed hebben gehad, maar ook hoe ze in voorgaande jaren scoorden.

Door het veel verschillende kleurgebruik wel een beetje een kerstboom; maar dat zal wel beïnvloed zijn door de tijd van het jaar.

NB voor de systematiek en andere achtergronden, zie volkskrant.nl/hoe-de-top-200-van-invloedrijkste-nederlanders-wordt-samengesteld/

Al met al een aanleiding om eens te kijken of de grafiek (makkelijk) na te bouwen is in Excel en of we deze wat interactiever kunnen maken.

Basis-gegevens

Op de site van de Volkskrant zijn de basisgegevens niet terug te vinden; dus dan moeten we ze maar zelf uit de grafiek afleiden (zie het tabblad Data in het Voorbeeldbestand).

Wel ontbreken er dan nogal wat namen (de grijze lijnen in de grafiek); die zijn gecodeerd met Onbekend.  Deze personen komen niet meer in 2018 in de top-20 voor. De 1e onbekende zien we alleen in 2014 en 2015, terwijl de 2e onbekende ook nog in 2016 en 2017 voorkomt.

Alle data zijn opgeslagen in een Excel-tabel met de naam tblData. Bij uitbreiding van de gegevens zal Excel hier automatisch rekening mee houden.

Overzicht

Om de grafiek te kunnen maken genereren we eerst op basis van de bovenstaande gegevens een draaitabel; in de rijen komt het Jaar, in de kolommen de Naam van de personen en in het waardegebied het VolgNr (als we de gegevens goed in de tabel hebben ingevuld, komt iedereen maar 1 keer per jaar voor. Dus het is geen probleem om Som van VolgNr te gebruiken).

Deze draaitabel vormt op zijn beurt weer de basis voor een lijngrafiek (met markeringen).

Er moet nog wel wat aan de grafiek aangepast worden:

  1. allereerst de Legenda verwijderen
  2. rechts klikken op de draaitabel-knoppen (Som van .. en J..) en Verbergen kiezen
  3. rechts klikken op de linkeras en As opmaken kiezen
  4. Minimum vastzetten op 0 en Maximum op 21
  5. Waarde in omgekeerde volgorde aanvinken en OK klikken
  6. de linkeras verwijderen
  7. horizontale rasterlijnen verwijderen
  8. rechtsklikken op een lijn en Gegevensreeks opmaken kiezen
  9. bij Markeringsopties Standaard kiezen, als Type het rondje selecteren en de Grootte op 16 zetten
  10. Markeringsopvulling: een effen kleur kiezen en aanpassen aan de voorbeeldgrafiek (rood voor stijgers, blauw voor dalers etcetera)
  11. de Lijnkleur ook aanpassen via Ononderbroken streep en OK klikken
  12. nog een keer rechtsklikken op de lijn: Gegevenslabels toevoegen
  13. opnieuw rechtsklikken en dan Gegevenslabels opmaken kiezen
  14. de Labelpositie moet worden: Centreren en OK klikken
  15. dan op de rechtse markering van de lijn klikken (alleen dit punt van de grafiek is dan geselecteerd) en daar rechtsklikken. Kies Gegevenslabel opmaken
  16. Bij Label bevat ook de Reeksnaam aanvinken en als positie Rechts kiezen
  17. alle punten vanaf 8 opnieuw uitvoeren voor de overige lijnen; kies telkens de juiste kleur

Het resultaat mag er zijn (zie het tabblad Ovz1 van het Voorbeeldbestand):

NB het aanpassen van de grafiek is een flink karwei. Huiswerk voor een volgend artikel: het automatisch aanpassen van een grafiek met behulp van VBA.

Interactief overzicht

In de vorige grafiek staan zoveel lijntjes dat het soms niet meevalt om “de bomen door het bos te zien”.

Het zou natuurlijk mooi zijn als we een persoon zouden kunnen selecteren en dat dan de daarbij behorende “scores” in de grafiek automatisch benadrukt worden.

In het tabblad Ovz2 van het Voorbeeldbestand is via Gegevensvalidatie in cel S2 de invoer van een persoonsnaam geautomatiseerd (alleen namen uit de kop van de draaitabel van Ovz1 zijn toegestaan).
In cel S3 staat de formule: 
=INDEX(‘Ovz1’!$C$5:$AL$9;
VERGELIJKEN(R3;’Ovz1′!$B$5:$B$9;0);
VERGELIJKEN($S$2;’Ovz1′!$C$4:$AL$4;0))
Ofwel: zoek met behulp van Index in het blok C5:AL9 van het tabblad Ovz1 naar de rij die overeenkomt met de waarde in R3 en de kolom die overeenkomt met de naam in S2.

Omdat niet iedereen in elk jaar voorkomt kan deze formule ook soms de waarde 0 opleveren; deze waardes willen we niet in de grafiek zien, vandaar dat in T3 de volgende formule staat: =ALS(S3=0;NB();S3)

NB de functie NB() levert als resultaat de waarde #N/B. Deze waardes worden in een grafiek genegeerd. Zie ook het artikel grafiek-zonder-nullen.

In het tabblad Ovz2 van het Voorbeeldbestand is een kopie van de grafiek uit Ovz1 geplakt.
Aan deze grafiek is een nieuwe reeks toegevoegd, de cellen T3:T7.
Deze lijn van deze reeks heeft een rode kleur gekregen. Nog wat andere aanpassingen: Vloeiende lijn aanvinken en een pijl als Eindtype bij Lijnstijl.

Om het geheel rustig te laten ogen hebben alle overige lijnen een grijze kleur gekregen.

Kies in cel S2 een andere naam en de grafiek zal zich automatisch aanpassen!

NB een optie met slicers is handiger, maar dit wordt niet door iedere Excel-versie ondersteund.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Grafiek automatisch schalen en labels toevoegen



Herkent u de frustratie? Heb je net een grafiek ‘netjes’ gemaakt door de assen zodanig te schalen dat de gegevens mooi verdeeld zijn over de grafiek, klopt er niets meer van als er een andere of uitgebreidere reeks gegevens worden weergegeven.
Of je hebt ‘met de hand’  alle labels bij de punten aangepast (ja, ik gebruik nog een oude Excel-versie), dan gaan die bij een andere reeks de mist in.

Dus deze keer een anti-frustratie-artikel: 2 VBA-routines, die bovenstaande problemen oplossen.

Test-gegevens

Om een voorbeeld te kunnen maken had ik wat test-gegevens nodig. Meestal maak ik die door wat random-getallen of teksten te genereren.
Maar ik herinnerde me ineens dat ik ooit eens ergens gelezen had dat je ook test-gegevens op een site kon laten maken.
Na wat zoeken kwam ik de website mockaroo.com tegen.

Na een paar pogingen had ik een mooi lijstje met voorbeeld-gegevens (zie het tabblad Basis in het Voorbeeldbestand).

  1. bij Soort heb ik gekozen voor financiële markten (Mockaroo kiest dan willekeurig uit 2 mogelijkheden)
  2. de Datum ligt tussen 1 jan 2018 en 31 dec 2018
  3. bij Omschr heb ik voor willekeurige voornamen gekozen
  4. het Bedrag ligt tussen 1000 en 2000

Om de rest wat overzichtelijk te houden heb ik 20 regels daaruit geselecteerd (zie het tabblad Basis2) en een beetje aangepast. Deze test-gegevens staan klaar in een Excel-tabel met de naam Tabel1.

Grafiek

Om het verloop van de bedragen per Soort handig te kunnen weergeven, heb ik een draaitabel gemaakt op basis van de gegevens in Tabel1 (zie het tabblad Result in het Voorbeeldbestand).

Op basis van deze draaitabel kun je makkelijk een Draaigrafiek maken, maar dan komt Omschr automatisch op de x-as en ik wil deze gebruiken als Labels in de grafiek.

Dus maar een ‘gewone’ grafiek gemaakt met op de x-as de datums en op de y-as de bedragen.

Hiernaast staat de grafiek waarbij er geen Soort is geselecteerd (dus 20 punten). De x-as begint automatisch bij 19 januari omdat dat de kleinste datum is; iets vergelijkbaars geldt voor het einde van de as.
Excel heeft ook de y-as automatisch geschaald.

Assen handmatig aanpassen

Om de assen handmatig te schalen klik je met de rechtermuisknop op een as en pas je het minimum en het maximum aan (het vinkje daarvoor zal dan verdwijnen ten teken dat er geen automatische schaling hoeft plaats te vinden).

Maar iedere keer als er in de draaitabel een andere Soort wordt gekozen moeten er aanpassingen worden doorgevoerd.

Assen automatisch aanpassen

Om deze schaal-aanpassingen met VBA geautomatiseerd te kunnen doorvoeren moeten we ergens vastleggen wat het gewenste minimum en maximum is.

In het Voorbeeldbestand op het tabblad Result staat in cel G2 de formule =MIN(B:B)
Oftewel bepaal de kleinste waarde/datum in kolom B. In G3 wordt met de functie MAX de grootste waarde vastgelegd. Maar het is mooier als de x-as op de eerste van een maand begint: in cel H2 staat daartoe de formule =DATUM(JAAR(G2);MAAND(G2);1). Deze cel heeft de naam x_min gekregen.

Het maximum van de x-as moet de eerste dag van de volgende maand zijn: cel H3 (met de naam x_max) heeft daarom de formule =DATUM(JAAR(G3);MAAND(G3)+1;1)

NB ook als G3 een datum in december bevat, werkt deze formule goed; Excel zal automatisch het jaar ophogen en de maand gelijk maken aan januari.

In kolom J worden de minimum- en maximum-bedragen uit kolom D bepaald. Cel K2 bevat de formule =GEHEEL(J2/100)*100, zodat het minimum voor de y-as het eerste honderdtal is, kleiner dan de waarde in J2.
In cel K3 bepalen we de bovengrens voor de y-as: =(GEHEEL(J3/100)+1)*100

Nu hebben we nog een VBA-routine nodig, die de handelingen voor het aanpassen van de assen van ons overneemt.

In het tabblad Result is een knop met de naam Assen schalen toegevoegd waarmee deze macro/subroutine kan worden uitgevoerd.

Labels toevoegen

In de oudere versies van Excel is het niet mogelijk om automatisch extra informatie (zoals de Omschr uit het voorbeeld) als Labels aan een punt toe te voegen. Handmatig kan dit wel:

  1. klik rechts op de lijn in de grafiek
  2. kies de optie Gegevenslabels toevoegen
  3. klik op één van de labels; als dan alle labels zijn geselecteerd nogmaals op het label klikken
  4. de tekst van dit label kan nu aangepast worden

Maar als er nu in de draaitabel een andere Soort wordt gekozen dan kloppen de handmatig ingevoerde labels niet meer!

Daar komt VBA weer goed van pas:

Het spannende is hier wat er gebeurt met de variabele xVals.

Wanneer je in de grafiek op de lijn klikt verschijnt er in de formulebalk een formule die begint met =REEKS(
Daarna komt de cel, die de naam van de reeks bevat, dan de x-waarden en daarna de y-waarden.

Aangezien VBA Engelstalig is bevat xVals na het uitlezen van de formule van de Collection: =SERIES(Result!$B$4,Result!$B$6:$B$25,Result!$D$6:$D$25,1)

NB1 binnen Excel (met Nederlandse instellingen) worden de parameters gescheiden door een ; (punt-komma), maar binnen VBA is dit een , (komma).

NB2 de 1 op het einde geeft aan, dat deze lijn de eerste reeks is die in de grafiek geplot moet worden. We hebben maar één lijn, dus deze parameter heeft hier geen invloed.

We hebben in het vervolg van de routine alleen de reeks van de x-waarden nodig, de B6:B25.
Daarom nemen we eerst alles wat rechts van de eerste komma staat:
xVals = Mid(xVals, InStr(xVals, “,”) + 1)
en dan alles links van de volgende, dan weer eerste, komma:
xVals = Left(xVals, InStr(xVals, “,”) – 1)

xVals bevat nu  het bereik Result!$B$6:$B$25

LET OP als de naam van het tabblad zelf ook een komma bevat dan wordt de formule wel wat ingewikkelder (zie VBA).

De For-Next-loop gaat dan alle elementen van dit bereik langs en plaatst de waarde rechts van de datum in het betreffende label.

Uiteraard is deze routine ook via een knop uit te voeren op het tabblad Result van het Voorbeeldbestand.

Titel van grafiek

De titel van de grafiek is dynamisch gemaakt:

  1. in cel N2 staat de formule:  =”Bedragen van “&C2
    Oftewel koppel de inhoud van cel C2 aan de tekst Bedragen van.
  2. zorg dat de grafiek een of andere titel heeft
  3. klik ergens in de titel
  4. tik dan in de formulebalk =N2
  5. en druk op Enter

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

 

 

Functies grafisch weergeven



Excel is inzetbaar op veel gebieden; ook binnen het (wiskunde-)onderwijs is het goed bruikbaar.
Ik moest daar deze week aan denken, toen ik bedacht hoe ik 20 jaar geleden met iemand tijdens bijlessen heb zitten worstelen om hem het gevoel bij te brengen hoe 1e- en 2e-graads-functies er in grafiekvorm uitzien.

Eerstegraadsfunctie

Zoals we allemaal (?) weten is de algemene vorm van een eerstegraadsfunctie zoals hiernaast weergegeven.
Door voor a en b waarden in te vullen krijg je een specifieke functie.

In het Voorbeeldbestand in het tabblad 1e graads is de algemene functie ingevoerd door in de menutab Invoegen in het blok Symbolen de optie Vergelijking te kiezen.

De cellen C5 en C6 (naast de teksten a en b) hebben een naam gekregen:

  1. kies de menutab Formules
  2. dan in het blok Gedefinieerde namen de optie Naam definiëren
  3. voer een Naam in, in dit geval _a
  4. kies een Bereik. Standaard is de naam geldig voor de hele Werkmap, maar in dit geval moet het bereik beperkt worden tot het werkblad 1e graads
  5. pas zo nodig de verwijzing nog aan en klik op OK

NB Excel accepteert niet alle letters als Naam; ik heb er in dit geval voor gekozen om alle namen door een _ (underscore) te laten voorafgaan.

In cel B8 wordt de functie tekstueel opgebouwd:
=”ƒ(x) = ” & ALS(_a=0;””;ALS(_a=1;”x”;ALS(_a=-1;”-x”;_a&”x”))) & ALS(_b=0;””;ALS(_b<0;” – “;” + “) & ABS(_b))

De diverse tekstblokken worden m.b.v. het &-teken aan elkaar gekoppeld.

In het eerste blok is de ƒ ingevoerd via Invoegen/Symbool. Kies daar allereerst rechtsonder de optie ASCII (decimaal) en blader dan door de mogelijkheden.
Wiskundige tekens kunnen ook op de volgende site gevonden worden:  Lijst_van_wiskundige_symbolen; gebruik daar kopiëren en plak dan in Excel.

Met de diverse ALS-functies wordt de opmaak geregeld, zodat er bijvoorbeeld geen 0x of 1x in het resultaat komt te staan.

De ABS-functie levert de absolute waarde van een getal; het resultaat is dan altijd groter of gelijk aan 0.

Om de grafiek te kunnen tekenen hebben we een tabel nodig met de bij de functie horende x,y-waarden. In het tabblad 1e graads van het Voorbeeldbestand worden 100 combinaties berekend in de cellen vanaf B32. In cel C32 komt dan de formule =_a*B32+_b

NB Om het effect van veranderingen in a of b goed te kunnen zien zijn de assen ‘vastgezet’: ze lopen allebei van -5 tot +5.

Bij  eerstegraadsfuncties zijn de snijpunten met de assen altijd belangrijk. Die worden in de cellen C10 en C11 berekend met de formules =”(0 , “&_b&”)”, respectievelijk =ALS(_a=0;”geen”;”(“&-_b/_a&” , 0)”)

Omdat het resultaat van de laatste formule vaak geen geheel getal oplevert, is het inzichtelijker om het resultaat zo nodig als een breuk weer te geven (zie cel C15):
=ALS(_a=0;”geen”;”(“&TEKST(-_b/_a;”# ??/??”)&”, 0)”)

M.b.v. de functie TEKST wordt een opmaak meegegeven aan het resultaat -b/a: eventueel een geheel getal (#) en als het nodig is een breuk (met maximaal 2 cijfers in de teller en noemer).

Om alle overbodige spaties te verwijderen, kan de functie SUBSTITUEREN worden gebruikt (zie bijvoorbeeld cel C19 in het tabblad 1e graads).

In de grafiek is ook de functie weergegeven. Dit is als volgt gedaan:

  1. selecteer de cel met de functie en kies kopiëren (Ctrl-C)
  2. klik ergens anders op het werkblad met de rechter muistoets
  3. kies Plakken speciaal …
  4. en in het vervolgscherm de laatste optie van Andere plakopties (Gekoppelde afbeelding)

De inhoud van de oorspronkelijke cel wordt dan dynamisch in een grafisch object weergegeven. Dit object kan via slepen overal in de sheet geplaatst worden, dus ook in de grafiek.

Tweedegraadsfunctie

De opbouw van het tabblad 2e graads van het Voorbeeldbestand is vergelijkbaar met bovenstaande.

Het bepalen van de eventuele snijpunten met de x-as is echter wat ingewikkelder. Weet u nog?

 

Maar dit levert alleen maar een resultaat op als het getal onder de wortel (de discriminant) niet kleiner is dan nul; dus dat wordt eerste even gecontroleerd.

In C14 staat daarom de volgende formule:
=ALS(Oplos?=”geen”;”geen”;”(“&(-_b-WORTEL(Discr))/(2*_a)&” , 0)”)

 

Derdegraadsfunctie

In het tabblad 3e graads van het Voorbeeldbestand wordt de grafiek van een derdegraadsfunctie weergegeven. Het bepalen van de snijpunten met de x-as wordt nu wat lastiger; dat valt buiten de scope van dit artikel.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

 

 

Grafische analyse in Excel



Onlangs kwam ik op de website www.exceluser.com enkele nuttige artikelen tegen: 3 Simple Tricks to Improve Analytical Charting in Excel3 More Simple Tricks to Improve Excel Charts for Business, Introducing the Power of Year-Over-Year Performance Charts in Excel en What Inflation? How Excel Charts Can Help You Avoid ‘Anecdotal Economics’.

In dit artikel zal ik een paar ideeën van Charles W. Kyd de revue laten passeren en aan de hand van voorbeelden de principes daarachter uitleggen.

Wat laat je zien?

Hiernaast ziet u een simpel omzet-overzicht van een bedrijfje (zie het tabblad Vb1 in het Voorbeeldbestand).
In de maandrapportage kun je zo’n overzicht opnemen, maar je kunt er ook voor kiezen om een grafiek te tonen.

Directeur tevreden, iedereen tevreden?
Nee dus, de marketingmanager vindt dat zo niet goed zichtbaar is, dat de marketing-activiteiten hun vruchten afwerpen en wil een andere grafiek.

Dan blijkt dat de productie-manager weer andere wensen heeft (een collega heeft in de maand maart zijn werkzaamheden overgenomen) en vindt dat het beter is om de groei/krimp van de omzet in de loop van de tijd te laten zien.

Waar cijfer-overzichten veelal de interpretatie van de data aan de lezer overlaten, mag het duidelijk zijn dat grafische presentaties het gevaar van misleiding met zich mee brengen.

Onvergelijkbare gegevens vergelijken

Stel we hebben een maandoverzicht waarin 2 soorten bedragen voorkomen, die qua grootte ver bij elkaar uit de buurt liggen (zie het tabblad Vb2 in het Voorbeeldbestand).

Wanneer we deze in een standaard lijn-grafiek weergeven, dan zijn er weinig details per maand uit de grafiek af te leiden.

Eén van de meest gehanteerde oplossingen is om de twee reeksen aan verschillende assen toe te wijzen.
NB het kleurgebruik moet de lezer helpen om te zien welke reeks bij welke as hoort.

Als vooral het verloop van de tijd van belang is en niet de exacte hoogte van de bedragen, dan is een andere oplossing om de reeksen te indexeren.
Dan is er maar één verticale as nodig; dit voorkomt misverstanden.
Het betekent wel dat er aan de basisgegevens kolommen moeten worden toegevoegd met een berekening zoals =[@Bedr1]/$C$6

Verloop AEX

Eén van de artikelen op ExcelUser.com is geschreven naar aanleiding van het dalen van de koersen in Amerika (What Inflation?). Deze daling liep synchroon met de correctie van de bitcoin-koersen. Maar was/is hier sprake van een correctie of begint de inflatie weer toe te nemen? Of nog erger: zitten we weer aan het begin van een recessie?

En hoe zit dit in Nederland? Geeft de AEX ook aanleiding tot dit soort bespiegelingen?
De bedoeling van dit artikel is niet om hier nu een sluitend antwoord op te geven; wel zal ik aan de hand van enkele voorbeelden laten zien hoe grafieken het proces om te komen tot beantwoording kunnen ondersteunen.

In het tabblad AEX van het Voorbeeldbestand staat een overzicht van de AEX-resultaten vanaf januari 2000 (alleen koersen per werkdag zijn beschikbaar). We zullen alleen de kolom Datum en Close (koerswaarde op het moment van sluiten van de beurs) gebruiken.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAEX. Een van de grote voordelen van deze vorm is, dat wanneer er nieuwe cijfers beschikbaar zijn en deze onderaan toegevoegd worden, alle formules en grafieken automatisch aangepast worden. Een update van het analyse-systeem is dus een ‘fluitje van een cent’.

Om makkelijk te kunnen inzoomen op gedeeltes van het verloop van de AEX is deze grafiek dynamisch gemaakt. Dus wat er getoond wordt is afhankelijk van de inhoud van bepaalde cellen, in dit geval F3 en F4 (zie ook het artikel dynamische-grafieken).

In het tabblad AEX_Ovz van het Voorbeeldbestand staan daartoe 2 hulp-tabellen.
In cel C3 (met de naam MinDtm) wordt het minimum van de kolom Datum uit de tabel tblAEX bepaald; zo ook in cel C4 het maximum. Onder andere deze 2 waardes worden gebruikt om de mogelijke invoer in de cellen F3 (met de naam Start) en F4 (met de naam Eind) te begrenzen (met behulp van Gegevens-validatie).
In cel G3 (met de naam StartRij) wordt dan bepaald in welke rij van de AEX-tabel de datum uit F3 staat: =VERGELIJKEN(Start;tblAEX[Datum])

LET OP de derde parameter in de functie VERGELIJKEN is weggelaten; dat betekent dat de functie de grootste datum zoekt die kleiner of gelijk is aan Start. De kolom Datum in de tabel tblAEX moet dan wel oplopend gesorteerd zijn!

Zo wordt ook de rij van de laatste gewenste datum opgezocht in cel G4 (met de naam EindRij).

Namen definieren

In het voorgaande heb ik al regelmatig Namen gebruikt en dan vooral met het doel formules leesbaarder te maken. Ook hebben alle Excel-tabellen een naam gekregen, waardoor in formules direct duidelijk is naar welke tabel verwezen wordt. Het gebruik van tabellen heeft daarnaast als voordeel dat formules en grafieken verwijzen naar kolomnamen i.p.v. naar cel-bereiken (dus =tblVb1[Omzet] in plaats van =C3:C8)

Maar om dynamische grafieken te kunnen creëren hebben we ook iets ingewikkelder constructies als Naam nodig:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren
  2. klik op de button Nieuw…
  3. geef een naam op; als eerste voorbeeld rngDtm
  4. de formule onder Verwijst naar wordt dan
    =VERSCHUIVING(tblAEX[[#Kopteksten];[Datum]];StartRij;0;EindRij-StartRij+1)
  5. klik Sluiten

NB de functie VERSCHUIVING geeft als resultaat de reeks cellen, die ontstaat als we vanaf de kop van de kolom Datum in de tabel tblAEX het aantal StartRij naar beneden gaan en 0 kolommen naar rechts/links, waarbij de lengte van de reeks bepaald wordt door de formule EindRij-StartRij+1.

De formule voor rngClose is dan gelijk aan =VERSCHUIVING(tblAEX[[#Kopteksten];[Close]];StartRij;0;EindRij-StartRij+1)

LET OP Deze rng-namen kunnen nu via Gegevens selecteren aan een grafiek worden gekoppeld; denk er wel aan om niet =rngDtm in te vullen, maar =AEX!rngDtm, dus voor alle namen een naam van een tabblad, aangevuld met een !-teken.

Vul je in F3 de waarde 1-1-2007 in en in cel F4 de waarde 31-12-2013 dan ontstaat automatisch de volgende grafiek:

AEX in perspectief

Eén van de tips van ExcelUser.com is om een grafiek van een (historisch) perspectief te voorzien. In dit geval kan het interessant zijn om te zien wat het verband is tussen het koersverloop en recessies.

Daartoe zijn in het tabblad Recessie van het Voorbeeldbestand de recessies vanaf 2000 vastgelegd met een begin en een einddatum. Dit in de vorm van een Excel-tabel met als naam tblRecessie.

De tabel tblAEX in het tabblad AEX is uitgebreid met een kolom F waarin de formule
=ALS([@Datum]<=INDEX(tblRecessie[Eind]; VERGELIJKEN([@Datum];tblRecessie[Start]));
1;NB())
staat.

Korte toelichting: zoek met de functie VERGELIJKEN de Datum uit dezelfde regel (vandaar de @) op in de Start-kolom van tblRecessie ; als dit niets oplevert dan is het resultaat van de totale formule #N/B.
Dan wordt met behulp van de functie INDEX gekeken of deze datum kleiner of gelijk is aan de overeenkomende regel in de Eind-kolom van tblRecessie.
Als dat zo is, dan wordt het resultaat 1 en anders ook weer #N/B.
Het resultaat #N/B wordt in een grafiek niet weergegeven.

Deze recessie-gegevens nemen we in de grafiek op; niet rechtstreeks maar ook weer via een naam-formule: rngRecessie
=VERSCHUIVING(tblAEX[[#Kopteksten];[Recessie]];StartRij;0;EindRij-StartRij+1)

Deze range krijgt een eigen type grafiek, namelijk een Vlak en we zetten deze uit op de secundaire as. Zorg dat het maximum van deze as altijd 1 is en verwijder alle kenmerken van de as.

Op een vergelijkbare manier zijn ook de minimum- en maximum-koersen in de grafiek opgenomen.

 

Alarmbellen?

Als we inzoomen op het koersverloop van dit jaar blijft de vraag of er alarmbellen moeten gaan rinkelen; bijna 50 punten verschil tussen het minimum en het maximum, is dat een teken dat het fout gaat?

Die vraag is natuurlijk niet zo maar te beantwoorden, maar zoals Charles W. Kyd in één van zijn artikelen aangeeft: probeer een andere invalshoek.

Daarom zijn de AEX-gegevens uitgebreid met een kolom, die aangeeft hoeveel procent de koers op een bepaalde datum is gestegen of gedaald ten opzichte van het jaar daarvoor:

=[@Close]/INDEX([Close];VERGELIJKEN(ZELFDE.DAG([@Datum];-12);[Datum]))-1

Deel de slotkoers van een dag door de overeenkomende dag 12 maanden terug en trek daar 1 vanaf.

Ook nu is de derde parameter van de functie VERGELIJKEN weggelaten; dus soms wordt de slotkoers niet vergeleken met de slotkoers exact 12 maanden terug, maar de dichtstbijliggende datum er voor.

De grafiek hiervan is via de naam rngCloseMutJr weergegeven op het tabblad AEX_Ovz2 van het Voorbeeldbestand.

In de grafiek is te zien, dat fikse negatieve resultaten veelal samen vallen met recessies. Begin 2018 scoort nog steeds flink positief.
Om toch maar een voorspelling te doen: de conclusie lijkt gerechtvaardigd dat de economie positieve perspectieven laat zien.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

 

 

Van globaal naar detail en vv



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

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: