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:
- kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
- in het vervolgmenu kiest u binnen het blok Formulierbesturingselementen de optie Schuifbalk
- 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).
- klik met de rechtermuisknop op de nieuwe schuifbalk en kies Besturingselement opmaken
- 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?
- klik rechts op de schuifbalk
- kies de optie Macro toewijzen
- als er nog geen macro aan de schuifbalk gekoppeld is dan kiest u Nieuw, anders Bewerken
- 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).
- keuzerondjes worden op een vergelijkbare manier als schuifbalken aan het tabblad toegevoegd.
- klik rechts op één van de keuzerondjes en kies de optie Besturingselement opmaken
- koppel het besturingselement aan een cel in Excel; in het voorbeeld C4 ofwel KolomNr
- 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
Ongetwijfeld hebt u op Internet, Youtube of op TV (DWDD of bijvoorbeeld bij 
Toen ik bovenstaand voorbeeld zag, vroeg ik me af in hoeverre het mogelijk zou zijn om dit in Excel na te bouwen.
Om verwijzingen in formules overzichtelijker/leesbaarder te maken zijn aan alle parameters namen gegeven. Het snelste gaat dat op de volgende manier:
selecteer de cellen, die een naam moeten krijgen (inclusief de cellen daarboven)
In het tabblad Berek van het 
maak een nieuw tabblad aan (bijvoorbeeld via 
onderaan op het scherm, op het einde van de andere tabbladen)
in 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.
Om gemakkelijk het verloop in de tijd te kunnen volgen is onder aan de grafiek een schuifbalk toegevoegd:
klik rechts op de schuifbalk en vul de diverse opties in:
en druk op Enter
Een voorbeeld:
; dus wel degelijk de inhoud van drie cellen E4, E5 en E6
Nu we met Verschuiving overweg kunnen, kunnen we die voor onze dynamische grafiek gebruiken:
, bij Naam tikken we in Maand en Verwijst naar wordt =verschuiving($A$49;0;0;$D$1;1)
In het 
In het laatste voorbeeld (tabblad DynGraf3) kunnen de grenzen met behulp van zogenaamde Schuifbalken worden ingesteld.
Daarna kan na rechtsklikken op de schuifbalk het besturingselement opgemaakt worden.