Dynamische grafieken

Niet iedereen zal meteen weten, wat hiermee bedoeld wordt.
De term dynamische grafiek wordt in de Excel-omgeving gebruikt, wanneer een grafiek zich direct aanpast aan wijzigingen in de bron-spreadsheet zonder dat de gebruikte bereiken voor de x- en y-waarden hoeven worden aangepast.
Het bekendste voorbeeld: er komen omzet-gegevens van een nieuwe maand beschikbaar; deze worden onderaan een reeks toegevoegd. Dan zou het fijn zijn als de bijbehorende grafiek dit automatisch zou overnemen.

Eenvoudige oplossing

Op allerlei manieren is er een bepaalde vorm van dynamiek te implementeren. DynGraf1

Laten we eerst een eenvoudige implementatie bekijken.
In het Voorbeeldbestand op het tabblad DynGraf1 zijn bedragen (kolom C) voor iedere maand (kolom A) opgenomen; in kolom B hebben de maanden een volgnummer gekregen.
Stel dat we alleen de cijfers tot en met juni willen weergeven:

  1.  cel D1 krijgt dan de waarde 6
  2. in D4 staat de formule: =ALS(B4<=$D$1;A4;””)
    Dus als het volgnummer kleiner of gelijk is aan de waarde in cel D1, dan wordt de waarde uit A4 (de maand) overgenomen en anders komt er niets (er staan alleen maar 2 aanhalingstekens)
  3. idem voor cel E4: =ALS(B4<=$D$1;C4;””)
  4. D4 en E4 kopiëren we naar beneden
  5. dan maken we een grafiek, die voor de x-waarden (horizontale as) “kijkt” naar de cellen D4 t/m D15 en voor de y-waarden naar E4 t/m E15
  6. de titel van de grafiek maken we dynamisch door een verwijzing te maken naar cel B17; dit gaat als volgt:
    * geef de grafiek een willekeurige titel
    * klik in de titel, dan in de formulebalk en voer daar het =-teken in, gevolgd door een verwijzing naar de gewenste cel: DynGraf2 en druk op Enter
  7. in cel B17 zorgen we dat de tekst aangepast wordt voor de maandkeuze:
    =”Bedragen t/m “&VERT.ZOEKEN($D$1;B4:D15;3;ONWAAR)
    Hier worden 2 teksten gekoppeld (zie het artikel Teksten samenvoegen): de “harde” tekst Bedragen t/m en de betreffende maand, die met de functie verticaal zoeken wordt opgehaald (zoek in de eerste kolom van het blok B4:D15 naar de waarde uit cel D1; geef de corresponderende waarde uit de 3e kolom van dat blok terug)

Een groot nadeel van de bovenstaande methode is, dat er voor de ontbrekende maanden een lege ruimte in de grafiek overblijft.
Dat kunnen we voorkomen door de grafiek dynamisch te maken met behulp van de functie Verschuiving.

Verschuiving

Voordat we deze functie voor grafieken gaan gebruiken eerst wat uitleg:
overal waar je in Excel een verwijzing naar een cel of een groep cellen (een cel-bereik of -range) gebruikt kun je ook VERSCHUIVING gebruiken.
De verwijzing zal dan niet naar de opgegeven cel zijn, maar naar 1 of meerdere cellen verschoven. DynGraf3Een voorbeeld:

  1. kies in het Voorbeeldbestand het tabblad DynGraf1
  2.  in cel H4 zetten we de formule =VERSCHUIVING(C4;2;0)
  3. het resultaat is 120; de functie geeft de inhoud van de cel 2 rijen onder en 0 kolommen rechts van C4 (dus C6)
  4. zo geeft de formule =VERSCHUIVING(C4;1;2) de waarde 110 (namelijk de inhoud van cel E5)
  5.  =VERSCHUIVING(E3;3;-2) werkt ook!! Resultaat is 120; de inhoud van cel C6.
  6. =VERSCHUIVING(D3;D1;0) en =VERSCHUIVING(D3;D1;1) leveren de maand en het bedrag afhankelijk van de waarde in cel D1
  7. Maar deze functie kent nog meer parameters (optioneel; zijn dus niet verplicht). We kunnen ook nog aangeven hoeveel rijen en kolommen het bereik vanuit de nieuwe positie moet hebben:
    =VERSCHUIVING(D3;1;1;3;1) geeft als resultaat de inhoud van de cellen E4:E6; namelijk, vanuit cel D3 startend, 1 rij naar beneden en 1 kolom naar rechts (dus E4) nemen we een bereik van 3 rijen hoog en 1 kolom breed.
    Wanneer we deze formule intikken in Excel, levert dit het resultaat 100; Excel laat de waarde van de eerste cel (E4 dus) zien.
    NB wil je weten wat Excel intern als resultaat van de functie vasthoudt:
    * klik in de formulebalk achter de betreffende functie en druk op F9
    * in plaats van de formule komt nu het resultaat: DynGraf4; dus wel degelijk de inhoud van drie cellen E4, E5 en E6
    * druk daarna niet op Enter maar op Esc
  8. maar als het resultaat van de vorige functie de inhoud van 3 cellen is, dan kunnen we natuurlijk ook nog het volgende doen:
    =SOM(VERSCHUIVING(D3;1;1;D1;1))
    Het resultaat is de som van de bedragen van de maanden, waarbij cel D1 bepaalt hoeveel maanden er worden meegenomen.

 Complexere (maar mooiere) oplossing

DynGraf5Nu we met Verschuiving overweg kunnen, kunnen we die voor onze dynamische grafiek gebruiken:

  1. kies opnieuw in het Voorbeeldbestand het tabblad DynGraf1
  2. scrol naar beneden zodat rij 70 in beeld is
  3. hier staat nog een grafiek die afhankelijk is van de waarde in cel D1; maar deze heeft geen “lege” maanden
  4. voordat je een dergelijke grafiek kunt maken, moeten binnen Excel enkele nieuwe namen gedefinieerd worden, die voor de x- en y-waarden worden gebruikt:
    * ga naar de menu-tab Formules
    * kies binnen het blok Gedefinieerde namen de optie Namen beheren
    * kies de optie NieuwDynGraf6, bij Naam tikken we in Maand en Verwijst naar wordt =verschuiving($A$49;0;0;$D$1;1)
    * klik OK; in het overzicht van namen is onze nieuwe naam Maand opgenomen, waarbij Excel aan de cellen A49 en D1 automatisch de naam van het betreffende tabblad en een ! heeft toegevoegd.
    LET OP maak verwijzingen naar cellen ALTIJD absoluut (met de $-tekens) anders zal de naam relatief zijn t.o.v. de cel, die op het moment van aanmaken geselecteerd is
  5. op dezelfde manier maken we een nieuwe naam Bedrag: =verschuiving($B$49;0;0;$D$1;1) of
    =verschuiving($A$49;0;1;$D$1;1)
  6. bij het maken van de grafiek geven we als Aslabelbereik =DynGraf1!Maand op en als Reekswaarden de naam =DynGraf1!Bedrag.
    LET OP begin met het =-teken en laat de hierboven gedefinieerde naam voorafgaan door de naam van het tabblad en een !
  7. Verander de waarde in D1 en bekijk het resultaat

NB wat gebeurt er als je in D1 bijvoorbeeld 15 intikt?

NB2 zet in D1 de formule =AANTAL(B49:B60) en maak cel B60 leeg

Nog complexere (en nog mooiere) oplossingen

DynGraf7In het Voorbeeldbestand heb ik nog 2 alternatieve oplossingen opgenomen.

In het eerste voorbeeld kun je ook de beginmaand opnemen, zodat de grafiek niet altijd met januari hoeft te beginnen, maar je bijvoorbeeld alleen de resultaten van het lopende kwartaal kunt laten zien.

Open het tabblad DynGraf2 en bekijk de opbouw van de sheet en de grafiek.
In dit tabblad worden de namen Maand2, Bedrag2 en Totaal gebruikt. De laatste is toegewezen aan het tekstvak, dat over de grafiek ligt.

DynGraf8In het laatste voorbeeld (tabblad DynGraf3) kunnen de grenzen met behulp van zogenaamde Schuifbalken worden ingesteld.

Om deze in een sheet te plaatsen kies je in de menu-tab Ontwikkelaars in het blok Besturingselementen de optie Invoegen. Klik dan binnen het blok Formulierbesturingselementen de optie Schuifbalk; geef met de cursor in de sheet aan waar de balk moet komen. DynGraf9Daarna kan na rechtsklikken op de schuifbalk het besturingselement opgemaakt worden.

Zoals te zien is kunnen hier de minimale en maximale waarden worden vastgelegd.  Van groot belang is de optie Koppeling met cel.


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

6 gedachten over “Dynamische grafieken

  1. Fijn deze specifieke toelichting. Maar als ik het zelf probeer te doen krijg ik problemen met de syntax van de verschuiving formule lijkt het. Enig idee waarom de uitkomst van deze formule altijd #WAARDE is?
    VERSCHUIVING(A59;0;0;15;1)

    Ik wilde hiermee een bereik definiëren van A59 tot A63.

  2. Super fijne uitleg!

    Maar… nu heb ik mijn data in kolommen staan ipv rijen (A48=Maand, A49=Bedrag). Kan ik de grafiek dan ook dynamisch maken?
    Dus, met ” =verschuiving($B$49;0;0;$D$1;1)” kom ik (als D1=8) 8 rijen lager uit, terwijl ik juist 8 kolommen opzij wil.

    Ik heb te veel data uit reportbuilder om alles om te zetten van rijen naar kolommen, dus ben op zoek naar een oplossing om mijn grafieken toch dynamisch te krijgen. Ik heb het gevoel dat ik iets heel simpels over het hoofd zie.

    1. Dag Sander

      met =verschuiving($B$49;0;0;1;$D$1) zou het moeten lukken: het blok is dan 1 regel hoog en D1 kolommen breed. Misschien wel eerst de grafiek opnieuw maken.
      Lukt het niet? Stuur even een bestandje.

Geef een reactie

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