Tagarchief: Schuifbalk

Scrollen in dashboard


LET OP: na het downloaden de extensie wijzigen in xlsb


Excel wordt steeds vaker gebruikt om het management via dashboards van actuele informatie te voorzien.
Maar ook in dit soort rapportages is de ruimte beperkt. Daarnaast is niet iedere manager geïnteresseerd in dezelfde informatie.

Dit soort problemen is handig op te lossen door gebruikers in de digitale rapportages door de overzichten te laten scrollen.

In dit artikel komen enkele methoden aan bod hoe dit te realiseren. En als we toch bezig zijn: met een beetje VBA kunnen we de overzichten ook makkelijk sorteren.

Basis-materiaal

We hebben een overzicht van afdelingen (het tabblad Data in het Voorbeeldbestand bevat 30 regels) met per afdeling het toegekende kosten-budget (altijd 100.000), de werkelijk gemaakte kosten, het aantal uitgebrachte offertes en het gerealiseerde aantal contracten. Daarnaast bevat het overzicht nog enkele KPI’s: kosten/budget, kosten/offertes en kosten/contracten.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAfd. In cel L2 (met de naam AantAfd) wordt het aantal afdelingen in de tabel geteld:
=AANTALARG(tblAfd[Afdeling])
Dit aantal kunnen we later goed gebruiken om bepaalde zaken te automatiseren.

NB de nummering van de afdelingen is zodanig dat er altijd 2 cijfers gebruikt worden; dit om een juiste sortering te krijgen. Anders krijg je Afd1, Afd10, Afd11, … , Afd2, Afd20 etc.

Overzicht 1

Wanneer nou blijkt, dat we in een bepaalde rapportage slechts ruimte voor 10 regels hebben; welke afdelingen (van de 30) moeten we dan laten zien? Dat hangt natuurlijk af van de wensen van de ontvangers en die kunnen wel eens tegenstrijdig zijn.
Daarom gaan we nu het overzicht met 10 regels zo aanpassen, dat hierbij gekozen kan worden welke regels zichtbaar zijn.

In het tabblad SelIndex van het Voorbeeldbestand geven we in cel C2 (met de naam Start) aan, welke regel van de 30 als bovenste moet worden weergegeven; de volgende 9 worden ook getoond.

In cel F3 staat de formule:
=INDEX(tblAfd[Afdeling];RIJ()-RIJ(AfdKop)+Start-1)
Ofwel: zoek met de functie INDEX in de kolom Afdeling van de tabel tblAfd die rij op die overeenkomt met de RIJ() van (in dit geval) cel F3, minus het rij-nummer van de kop van het overzicht plus de waarde van de cel Start.

NB1 cel F2 heeft de naam AfdKop gekregen.

NB2 voor het bepalen van de juiste regel hadden we natuurlijk ook een hulpkolom kunnen gebruiken met daarin de waardes 1 t/m 10. De gebruikte berekening maakt het mogelijk om de hulpkolom weg te laten en is flexibel genoeg om het overzicht eventueel later nog te verplaatsen. Ook kunnen we deze formule zonder verdere aanpassingen naar beneden kopiëren.

NB3 de formule kan ook naar rechts gekopieerd worden; Excel wijzigt de kolom-verwijzing Afdeling automatisch naar Budget etc.

NB4 om de juiste regel te selecteren moeten we de berekening nog corrigeren met -1.

Wanneer nu de waarde in de cel Start wordt gewijzigd zal het overzicht zich automatisch aanpassen.

Dat aanpassen kan natuurlijk nog veel mooier en makkelijker met behulp van een schuifbalk:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. in het vervolgmenu kiest u binnen het blok Formulierbesturingselementen de optie Schuifbalk
  3. de cursor wordt dan een kruisje; teken, met de linkerknop ingedrukt, de gewenste vorm van de schuifbalk op de gewenste plaats (dit kan allemaal later nog aangepast worden).
  4. klik met de rechtermuisknop op de nieuwe schuifbalk en kies Besturingselement opmaken
  5. zorg dat een koppeling met cel C2 wordt gemaakt (u kunt hier ook de naam Start gebruiken), de minimumwaarde wordt 1 en het maximum 21.

LET OP kies NIET een ActiveX-besturingselement; deze leveren in de praktijk nogal eens crashes van Excel op.

NB als je de schuifbalk wilt verplaatsen of de grootte corrigeren, klik dan eerst rechts op de schuifbalk.

Overzicht 2

Een andere methode om zo’n overzicht met de juiste gegevens te vullen is door gebruik te maken van de functie VERSCHUIVING.

In het tabblad SelVersch1 van het Voorbeeldbestand ziet u in cel F3 de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];
RIJ()-RIJ(AfdKop)+Start-1;
KOLOM()-KOLOM(AfdKop))
ofwel: haal de waarde op uit de cel, die gevonden wordt door vanuit de Afdelings-koptekst van de tabel tblAfd een aantal rijen naar beneden te gaan en een aantal kolommen naar rechts.

NB1 de constructie na het eerste (-haakje hoeft u niet zelf in te tikken; klik gewoon op de betreffende cel en Excel vult de formule vanzelf aan.

NB2 de ingegeven minimum- en maximumwaardes voor de schuifbalk zorgen er voor dat het overzicht geen blanco regels zal bevatten. Helaas is dat niet het geval als in de cel Start een te grote of te kleine waarde wordt ingevoerd.

Overzicht 3

De functie VERSCHUIVING kent nog meer parameters/argumenten. Daar hebben we in het overzicht op het tabblad SelVersch2 van het Voorbeeldbestand gebruik van gemaakt. Cel F3 bevat de formule:
=VERSCHUIVING(tblAfd[[#Kopteksten];[Afdeling]];Start;0;10;8)
Ofwel: selecteer een bereik van cellen, die, gerekend vanaf de koptekst Afdeling, een aantal rijen gelijk aan Start lager begint. Het begin is 0 kolommen verschoven. Het resulterende bereik moet 10 regels hoog en 8 kolommen breed zijn.

LET OP deze formule levert een blok van 80 cellen op. Daarom moet de formule op een speciale manier worden ingevoerd:

  • selecteer eerst met de muis alle cellen waar het overzicht moet komen (in het voorbeeld de cellen F3 tot en met M12)
  • voer dan bovenstaande formule in
  • druk in plaats van op Enter tegelijkertijd op Ctrl-Shift-Enter (de zogenaamde CSE-methode).
  • Excel plaats dan accolades rond de formule

We willen er ook voor zorgen, dat er geen lege regels komen.
De cel Start heeft daartoe een gegevens-validatie gekregen: bij Toestaan is de optie Aangepast ingevuld en bij Formule:
=EN(Start>0;Start<=AantAfd-9)
Dus de waarde in de cel Start moet aan 2 voorwaarden voldoen: groter dan nul EN kleiner of gelijk aan het aantal regels in de bron (minus 9).

LET OP vergeet het eerste =-teken niet

Maximum in schuifbalk

Maar wat als er nu een afdeling bij komt? De tabel tblAfd wordt automatisch aangepast, de teller van het aantal afdelingen zal ook direct opgehoogd worden, de gegevensvalidatie uit het vorige overzicht zal daarom ook goed werken.
Maar … de schuifbalken hebben nog steeds een maximum van 21.

Alleen met behulp van een (kleine) VBA-routine kunnen we het maximum van de schuifbalk automatisch laten meelopen met het aantal regels in de bron-gegevens.

In het overzicht van tabblad SelVersch3 van het Voorbeeldbestand is deze routine geïmplementeerd. Probeer maar eens uit: voeg een regel in het tabblad Data toe en beweeg de schuifbalk op en neer.

Hoe kun je de eigenschappen van de schuifbalk door VBA laten aanpassen?

  1. klik rechts op de schuifbalk
  2. kies de optie Macro toewijzen
  3. als er nog geen macro aan de schuifbalk gekoppeld is dan kiest u Nieuw, anders Bewerken
  4. vul onderstaande programmacode in en sluit de Visual Basic-editor

NB1 afhankelijk van de Excel-versie kan de omschrijving van de subroutine iets anders zijn, bijvoorbeeld Sub Schuifbalk1_BijWijzigen. Laat de naam staan zoals die door Excel is gegenereerd.

NB2 afhankelijk van de situatie kan er ook sprake zijn van Schuifbalk2 etc.

NB3 VBA is altijd Engelstalig. Dus binnen de subroutine is sprake van een ‘shape’ met de naam Scroll Bar 1.

NB4 met de constructie Range(“AantAfd”).Value wordt de waarde uit de cel met de naam AantAfd opgehaald.

‘Automatisch’ sorteren

Zoals al eerder aangegeven zal niet ieder ontvanger van de rapportage de focus op dezelfde KPI leggen. We maken de rapportage zodanig dat de gebruiker zelf kan aangeven welke sortering de gegevens moeten hebben.

Klik op één van de keuzerondjes en de gegevens worden op de betreffende kolom gesorteerd (zie het tabblad Sortering in het Voorbeeldbestand).

  1. keuzerondjes worden op een vergelijkbare manier als schuifbalken aan het tabblad toegevoegd.
  2. klik rechts op één van de keuzerondjes en kies de optie Besturingselement opmaken
  3. koppel het besturingselement aan een cel in Excel; in het voorbeeld C4 ofwel KolomNr
  4. klik rechts op het eerste keuzerondje en kies de optie Macro toewijzen en zorg dat de volgende routine gekoppeld wordt:

NB in dit geval heeft het eerste rondje al het volgnummer 2.

De macro-toewijzing moet voor ieder keuzerondje apart worden uitgevoerd.

Bovenstaand subroutine roept een andere routine aan, Sortering. Deze ziet er als volgt uit (de basis is gemaakt door een macro op te nemen terwijl de sortering handmatig wordt uitgevoerd):


  • als eerste wordt de variabele Kolom gevuld: op basis van de waarde in cel KolomNr wordt één van de kolomkoppen gekozen
  • met het commando With wordt er voor gezorgd dat alle volgende opdrachten betrekking hebben op een Sort van de tabel (ListObject) tblAfd
  • vorige sorteringen verwijderen
  • nieuwe sortering toevoegen
  • het te sorteren bereik heeft een Header/kop
  • de sortering is niet gevoelig voor hoofd- en kleine letters
  • de sortering-orientatie is verticaal (inhoud van een kolom is bepalend)
  • de PinYin-regel mag ook weggelaten worden; alleen interessant bij Chinese tekens
  • en als laatste wordt de sortering toegepast

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


Eerbetoon aan Rosling cs

Rosling

RoslingOngetwijfeld hebt u op Internet, Youtube of op TV (DWDD of bijvoorbeeld bij Zondag met Lubach) wel eens een presentatie van Hans Rosling gezien.
Iedere keer is het weer interessant en enerverend (eigenlijk ook wel spannend) om te zien hoe hij (openbare, vrij beschikbare) gegevens weet om te zetten in informatie.
Wikipedia: Rosling stichtte de Gapminder Foundation samen met zijn zoon Ola Rosling en zijn schoondochter Anna Rosling Rönnlund. Gapminder ontwikkelde de Trendalyzer-software, die internationale statistieken omzet in bewegende, interactieve en onderhoudende grafieken.
Het doel is de promotie van een wereldvisie gebaseerd op feiten, door verhoogd gebruik en begrip van gratis toegankelijke openbare statistieken. Zijn lezingen aan de hand van Gapminder-visualisaties vielen in de prijzen doordat ze grappig en toch doodernstig zijn. De interactieve animaties zijn vrij beschikbaar op de website van de stichting (zie gapminder.org).

roslingBij veel van de presentaties gebruikt Rosling de Trendalyzer-software, waarmee het mogelijk is om diverse items in samenhang te tonen.
Zoals hiernaast bijvoorbeeld: op de (niet zichtbare) assen is het Inkomen per inwoner tegen de Levensverwachting uitgezet.  Per land wordt dit door een bolletje weergegeven, waarbij de grootte van het bolletje wordt bepaald door het aantal inwoners van dat land. Doordat de grafiek ook nog eens een reis door de tijd kan maken, waardoor we historische ontwikkelingen kunnen zien, hebben we dus te maken met een informatie-overzicht met maar liefst 5 dimensies!

Bellendiagram

Rosling2Toen ik bovenstaand voorbeeld zag, vroeg ik me af in hoeverre het mogelijk zou zijn om dit in Excel na te bouwen.
Zelf gebruik ik in rapportages zogenaamde bellendiagrammen (in het Engels bubble chart)  wel eens, maar ze zijn niet echt gangbaar.
Daarom leek het me wel de moeite waard  om dit idee uit te werken; ook als een soort eerbetoon aan het idealistisch te noemen werk van Rosling cs.

Basis-gegevens

Voordat we een grafiek kunnen gaan maken, moeten we natuurlijk de beschikking hebben over relevante basisgegevens.
Omdat Rosling alleen gebruik maakt van openbare bronnen is dit verzamelen niet zo moeilijk; op de site gapminder.org vinden we de nodige gegevens zelf of verwijzingen naar de bronnen.
In het Voorbeeldbestand zijn die opgenomen in de tabbladen Landen (overzicht van bijna alle landen ter wereld), Inkomen (het jaarinkomen per inwoner van de diverse landen, van 1800 tot 2015), Bevolking (het aantal inwoners per land, van 1800 tot 2015) en LevVerwachting (de levensverwachting bij geboorte per land, van 1800 tot 2015).

Inkomen
Bij sommige landen ontbraken (gedeeltes van) inkomen-gegevens. Om te zorgen dat het tekenen van de grafiek hierna niet spaak zal lopen, heb ik die gegevens aangevuld met de waarde 1 (één).

Bevolking
Het aantal inwoners per land gaat in de grafiek de belgrootte  bepalen. Vandaar dat ook hier de ontbrekende gegevens aangevuld zijn met de waarde 1.

Parameters

Alle gegevens, die nodig zijn voor de besturing van ons Excel-systeem, zijn vastgelegd op het tabblad Parameters:

  • alle landen, die we in de grafiek willen opnemen,
  • het beginjaar en
  • eindjaar (in dit systeem 1800, respectievelijk 2015),
  • het jaar, waarvoor we de grafiek willen zien,
  • een indicator voor de snelheid van de verandering van de grafiek (overgang naar een volgend jaar) en
  • het opschrift van een button, die we maken om de grafiek ‘af te kunnen spelen’.

Rosling3Om verwijzingen in formules overzichtelijker/leesbaarder te maken zijn aan alle parameters namen gegeven. Het snelste gaat dat op de volgende manier:

  1. Rosling4selecteer de cellen, die een naam moeten krijgen (inclusief de cellen daarboven)
  2. kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie
  3. zorg dat in het vervolgscherm (in dit geval) alleen het vinkje bij Bovenste rij aan staat en klik OK

NB Klik op één van de parameters (bijvoorbeeld 2015) en zie dat in het Naamvak linksboven niet meer de rij en kolom wordt weergegeven, maar de naam, die we aan de cel hebben gegeven.

Op dezelfde manier heeft de reeks landen ook een naam gekregen. Klik op het pijltje naast het Naamvak en kies Landen om dit te verifiëren.

Berekeningen

Voordat we de grafiek kunnen maken, zullen we alle benodigde gegevens bij elkaar moeten verzamelen in een vorm, die handig is om als bron voor de grafiek te fungeren.

Rosling5In het tabblad Berek van het Voorbeeldbestand staat in cel B3 de formule =Landen (een verwijzing naar de landen op het tabblad Parameters). Deze formule is zo vaak naar beneden gekopieerd als noodzakelijk is om alle landen te zien.

LET OP een dergelijke verwijzing naar een zelf-gedefinieerde naam voor een bereik haalt gegevens op uit de corresponderende regel, dus in dit geval regel 3. Wil je dat niet: selecteer dan eerst alle cellen, die gevuld moeten worden; tik in =Landen en druk dan op Ctrl-Shift-Enter. Op deze manier wordt een zogenaamde matrixformule ingevoerd; Excel zet automatisch accolades rond de formule.

Per land laten we Excel de corresponderende gegevens opzoeken:

  1. in kolom C de regio:
    =VERT.ZOEKEN(B3;LandenBron;2;ONWAAR)
    de inhoud van cel B3 wordt in het bereik LandenBron opgezocht. Als het land gevonden wordt, dan levert de functie het corresponderende resultaat uit de 2e kolom van het blok. Met ONWAAR geven we aan, dat we alleen tevreden zijn als de inhoud van B3 ook echt gevonden is (dus niet Benaderen).
  2. het inkomen per land komt in kolom D:
    =INDEX(Inkomen;VERGELIJKEN($B3;InkLand;0);VERGELIJKEN(KeuzeJaar;InkJaar;0))
    Hoewel de inkomengegevens ook met VERT.ZOEKEN zouden kunnen worden gevonden, heb ik voor de functie Index gekozen (vind ik persoonlijk beter leesbaar en is meer universeel toepasbaar).
    Met behulp van Index zoeken we in het blok Inkomen de gewenste regel en kolom op en krijgen direct het resultaat.
    Maar op welke regel staat het betreffende land? Met
    VERGELIJKEN($B3;InkLand;0)
    is dat zo geregeld: InkLand is de naam van de reeks landen op het tabblad Inkomen. De functie Vergelijken geeft de positie van B3 in deze reeks.
    Op een vergelijkbare manier wordt de juiste kolom opgezocht (het gewenste jaar staat in de parameter Keuzejaar).
    LET OP de 3e parameter van de functie Vergelijken moet 0 (nul) zijn: we zoeken ook weer hier een exacte waarde, geen benadering.
  3. de Levensverwachting en de Bevolkingsgrootte worden ook mbv de functie Index gevuld.

Grafiek

Eindelijk zijn we zover; we gaan de verzamelde informatie grafisch weergeven.

Stap voor stap (op het tabblad Graf van het Voorbeeldbestand staat het uiteindelijke resultaat):

  1. Rosling7maak een nieuw tabblad aan (bijvoorbeeld via Rosling6 onderaan op het scherm, op het einde van de andere tabbladen)
  2. kies in de menutab Invoegen in het blok Grafieken de optie Overige grafieken en kies de eerste optie bij Bel
  3. in het (lege) grafiekgebied rechts klikken en de optie Gegevens selecteren … kiezen en dan Toevoegen
  4. Rosling8in het nieuwe scherm de gegevens voor de x- en y-as en de belgrootte invullen. De reeksnaam laten we leeg; heeft bij een belgrafiek weinig nut.
  5. Klik twee keer op OK en de grafiek is klaar!
  6. nog wat verfraaiingen: de rasterlijnen en legenda weglaten, titels bij de assen etc.
  7. Excel past standaard de assen automatisch aan op basis van de gegevens die gepresenteerd worden. Dat willen we niet, want als het KeuzeJaar wordt veranderd, gaat de grafiek ‘springen’.
    Klik rechts op één van de cijfers van de y-as en zorg dat de Levensverwachting loopt van 10 tot 100 jaar.
    Ook de x-as passen we aan: het Inkomen laten we lopen van 200 naar 100.000. Maar we zijn nog niet klaar: in het gebied met lage inkomens zitten heel veel bellen heel dicht bij elkaar, de hoge inkomens zijn uitschieters. Door deze as logaritmisch weer te geven, worden de lage inkomens duidelijker onderscheiden, terwijl de hogere inkomens ‘in elkaar schuiven’.
    LET OP Een dergelijke logaritmische indeling is moeilijk leesbaar, dus alleen gebruiken als de exacte getallen niet wezenlijk zijn.
  8. alle bellen krijgen dezelfde kleur. Handmatig is dit aan te passen, maar bij deze hoeveelheid niet echt praktisch. Met een VBA-routine zou dit (op basis van de regio) wel mogelijk zijn.
    In dit geval heb ik de bel van Nederland opgezocht (aan de hand van de onderliggende cijfers) en die gekleurd en een label meegegeven.
  9. Het jaar achter de grafiek?
    Maak een tekstvak aan (via de menutab Invoegen) en tik direct in de formulebalk in =Keuzejaar.  Nog wat lay-outen: lettertype en -grootte etcetera en schuif het tekstvak achter de grafiek (in het Voorbeeldbestand zijn de randen bewust zichtbaar gehouden).

Schuifbalk
Rosling9Om gemakkelijk het verloop in de tijd te kunnen volgen is onder aan de grafiek een schuifbalk toegevoegd:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen en kies de schuifbalk (rechts naast Aa onder Formulierbestruringselementen)
  2. ‘teken’ met de muis ergens op het grafiektabblad de plaats waar de schuifbalk moet komen
  3. Rosling10klik rechts op de schuifbalk en vul de diverse opties in:
    Huidige waarde: laten we beginnen met 1800
    Minimumwaarde: in dit geval 1800
    Maximumwaarde: 2015 dus
    De stappen daaronder worden 1 en 10 (1 jaar verder wanneer op het pijltje wordt geklikt, 10 jaar wanneer er in het lege gebied van de schuifbalk wordt geklikt)
    Koppeling met cel: hier vullen we KeuzeJaar in; een verwijzing naar het tabblad Parameters dus.
  4. klik OK

Mbv deze schuifbalk kunnen we nu makkelijk onze grafiek laten veranderen: de reis in de tijd kan beginnen!

Reis in de tijd
Het is natuurlijk nog mooier als we de veranderingen in de tijd automatisch kunnen laten zien.
Achter de Play-button op het tabblad Graf van het Voorbeeldbestand is een kleine VBA-routine opgenomen, die dit voor zijn rekening neemt.
Klik op Alt-F11 om de routine te bekijken. Hebt u vragen hierover? Schroom niet om contact op te nemen met G-Info.
Om de snelheid aan te passen (van 1 naar 5 sec als pauze) is nog een schuifbalk toegevoegd.


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

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: