Tagarchief: Verschuiving

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:


Unieke waarden



Er bestaan nogal wat situaties, waarbij je wilt weten of items allemaal verschillend zijn of niet.
En, als ze niet allemaal verschillend zijn, welke unieke exemplaren komen er dan voor?

In Excel kennen we een dergelijk probleem ook: welke unieke waarden komen er in een rij of kolom voor?

Deze keer zullen we enkele methoden de revue laten passeren; van simpele tot complexe, van opmaak tot selectie.

Voorwaardelijke opmaak

In het Voorbeeldbestand heb ik op het tabblad Data1 een tabel opgenomen met in de kolommen oa de afdelingen en het soort bedrag.

Alle cellen in die kolommen hebben een voorwaardelijke opmaak gekregen:

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. klik op de optie Nieuwe regel
  4. in het vervolgscherm kiezen we als Type: Een formule gebruiken
  5. en de formule wordt:
    =AANTAL.ALS(C$3:C3;C3)=1
    ofwel: als in het bereik C$3:C3 de waarde uit cel C3 1 keer voor komt mag de cel een opmaak krijgen.
    Dat is natuurlijk altijd waar!
    Maar wat gebeurt er als we deze voorwaardelijke opmaak ook ‘loslaten’ op een cel lager? Excel interpreteert de formule dan als =AANTAL.ALS(C$3:C4;C4)=1 en kijkt of de waarde uit C4 slechts 1 keer voor komt in het bereik C$3:C4.
    Ook wanneer deze formule in kolom D wordt toegepast is de controle precies wat we zoeken.
    NB om de formule ook in andere kolommen te kunnen gebruiken is alleen de 3 absoluut gemaakt als begin van het bereik (vandaar het $-teken vóór de 3) en is niet het meer voor de hand liggende $C$3 gebruikt.
  6. voeg nog de gewenste opmaak toe (hier is voor een licht-groene opvulling van de cel gekozen)
  7. klik op OK
  8. zorg dat de opmaak op alle relevante cellen van kolom C en D wordt toegepast:
    * kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
    klik op de optie Regels beheren
    * vul bij Van Toepassing op het gewenste bereik in (in dit geval $C$3:$D$32)

NB1 het overzicht is een Excel-tabel. Daarom zal Excel, wanneer er een regel aan wordt toegevoegd, ook de Voorwaardelijke opmaak direct meenemen; het bereik wordt automatisch aangepast.

NB2 welke unieke waarden er in een tabel-kolom zitten ziet u ook direct door op het ‘vinkje’ achter een kolomkop te klikken.

NB3 het vorige ‘recept’ kan met 1 stap ingekort worden: selecteer in de eerste stap niet één cel, maar het totale bereik waar de opmaak voor moet gelden; stap 8 kan dan vervallen.

Via draaitabel

De vorige oplossing was een optische methode om unieke waarden te vinden; bij een grote tabel niet echt handig.

Het maken van een draaitabel is echter een simpele en doeltreffende manier om snel de unieke waarden in een kolom te vinden:

  1. selecteer een cel in de tabel met gegevens, bijvoorbeeld B2
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm is alles al goed ingevuld; de bron voor de draaitabel is de totale Excel-tabel (met de naam tblData1)
  4. klik OK
  5. sleep het veld Afd naar de Rijlabels en daar zijn alle unieke afdelingsnamen!
  6. hetzelfde kunt ook doen voor de Soort, maar u kunt beter de vorige draaitabel uitbreiden: sleep Soort naar Kolomlabels.
    Sleep ook nog Bedrag naar het Waarde-gebied en u hebt meteen een overzicht van de verdeling van de bedragen naar afdeling en soort!

    Zie ook het tabblad Data1 in het Voorbeeldbestand.

Even een uitstapje: wilt u weten wat het totaal van Srt1 is dan kunt u ook de volgende formule gebruiken: =SOM.ALS(tblData1[Soort];”Srt1″;tblData1[Bedrag]) maar beter is het volgende:

  1. plaats in een bepaalde cel de waarde Srt1, bijvoorbeeld in G14 (zie het tabblad Data1 in het Voorbeeldbestand)
  2. in de cel daarnaast gaan we een formule opbouwen:
    tik in =som.als(
  3. wijs met de cursor de bovenkant van de kolom Afd aan (de cursor wordt dan een zwarte pijl naar beneden) en klik.
    De formule wordt dan aangevuld met tblData1[Soort] ofwel alle cellen in de kolom Soort van de Excel-tabel tblData1.
  4. tik in ; (de punt-komma) en klik op cel G14 en plaats weer een ;
  5. als laatste voegen we de kolom Bedrag toe, die onder voorwaarden gesommeerd moet worden; dat gaat op dezelfde manier als in stap 3.
  6. nog een haakje sluiten en Enter

Door in G14 een andere soort in te typen, krijgt u daarvan het totaal.

Maar wat als er aan 2 (of meer voorwaarden moet worden voldaan?

U moet dan de functie SOMMEN.ALS gebruiken.
Deze werkt net iets anders: eerst geeft u het bereik op, waarvan u de som wilt bepalen, dan het eerste criterium-bereik met daarachter het criterium zelf, daarna een nieuw criterium-bereik met criterium etc.

Gegevensvalidatie

Maar in G14 kunnen nu willekeurige teksten worden ingevoerd. Dat is natuurlijk niet de bedoeling; we willen alleen bestaande soorten kunnen opgeven.

Dit gaat eenvoudig met gegevens-validatie:

  1. plaats de cursor in de cel die u wilt valideren (bijvoorbeeld cel G19 zoals in het tabblad Data1)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. kies bij Toestaan: de optie Lijst
  4. en bij Bron: kiest u de kolom Soort uit de Excel-tabel op de manier zoals hierboven aangegeven.
    NB Excel vertaalt de kolom direct naar daadwerkelijke cellen, maar als de tabel groter (of kleiner wordt) verandert dit bereik mee.
  5. klik op OK.

Oeps, het resultaat is niet helemaal wat we hoopten. In de keuzelijst staan alle voorkomens uit de kolom Soort, we willen natuurlijk alleen unieke waarden hebben!

Unieke waarden via filtering

Om een lijstje te maken van unieke waarden (maar u wilt geen draaitabel gebruiken) dan kent Excel nog een andere aanpak:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. in het Uitgebreid filter kiezen we als Actie de optie Kopiëren naar andere locatie
  3. voor het Lijstbereik selecteert u alle gegevens uit de kolom Afd van de tabel, INCLUSIEF de kop
  4. bij Kopiëren naar vult u de cel in, waar de unieke waarden moeten komen (in het voorbeeld heeft deze cel de naam AfdKop gekregen)
  5. vergeet niet het vinkje te plaatsen bij Unieke records!

In het tabblad Data1 van het Voorbeeldbestand ziet u het resultaat van deze actie.

Ook voor de kolom Soort zijn de unieke waarden opgehaald.

NB1 Wat direct opvalt is dat er geen (automatische) sortering plaats vindt. De volgorde is gelijk aan de volgorde in de bron.

NB2 als er regels aan de bron-tabel worden toegevoegd dan moeten de ophaal-acties opnieuw worden uitgevoerd.
Excel ‘onthoudt’ de gegevens van de laatste actie. Dit is ook te zien in het overzicht Namen beheren in de menutab Formules.

LET OP1 de locatie, waar de unieke waarden moeten komen, moet op hetzelfde tabblad staan als waar de brongegevens zich bevinden.

LET OP2 vergeet niet om bij het Uitgebreid filter ook de kop van de kolom mee te nemen, anders krijgt u de eerste waarde dubbel. Dit is nergens in de Microsoft-documentatie terug te vinden!

Gegevensvalidatie

Op basis van deze unieke waarden kunnen nu makkelijk Gegevensvalidaties worden gemaakt.
Maar om het geheel dynamisch te houden (als er nieuwe unieke waarden bijkomen moet dit automatisch worden meegenomen bij de gegevensvalidatie) wordt als bron-lijst niet  een hard bereik genomen (bijvoorbeeld R3:R5).

Nee, we creëren een dynamisch bereik door een naam toe te voegen:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. vul een naam in (bijvoorbeeld AfdUniek)
  3. en plaats de formule
    =VERSCHUIVING(AfdKop;1;0;AANTALARG(Data1!$R:$R)-1)
    in het vak Verwijst naar.

Bij gegevensvalidatie (zie cel G20 in het tabblad Data1 van het Voorbeeldbestand) gebruikt u als Bron de naam AfdUniek (vergeet niet het =-teken daar voor te zetten!).

In de cellen G21 en H21 is op een vergelijkbare manier gegevensvalidatie toegepast, maar daarbij zijn als bron voor de verschuiving 2 simpele draaitabellen gebruikt.

LET OP worden er regels aan de oorspronkelijke tabel toegevoegd, vergeet dan niet de draaitabellen te vernieuwen anders bevatten de gegevensvalidaties niet de meest recente gegevens.


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:

 

 

Sudoku in Excel



Ik kreeg onlangs de volgende vraag: kun je geen artikel schrijven waarin je uitlegt hoe je met behulp van Excel Sudoku-puzzels op kunt lossen.
Mijn antwoord bestond uit 2 woorden ‘Ja’ en ‘maar’.

De Ja, omdat met Excel ‘alles’ mogelijk is en de maar omdat het oplossen van zo’n puzzel meer een programmeer-uitdaging is en niet zozeer iets om de mogelijkheden van Excel voor de dagelijkse praktijk te laten zien.

Maar toen ik er wat langer over nadacht, zag ik wel wat mogelijkheden.
Dus nu toch een Sudoku in Excel, maar niet een machine die de oplossing genereert (wat zou je daar ook mee moeten), wel een systeem dat het zoeken van een oplossing ondersteunt.

Met behulp van Voorwaardelijke opmaak laat Excel zien op welke plaatsen een cijfer geplaatst kan worden. Dubbele invoer in een rij, kolom of blok wordt ook door Voorwaardelijk opmaak gesignaleerd. Bij deze opmaak wordt veelvuldig gebruik gemaakt van de Namen-optie van Excel; niet alleen de standaard-toepassing daarvan maar ook enkele meer ingewikkelde en dus interessante mogelijkheden!

Basis van het G-Info/Sudoku-systeem

Allereerst hebben we natuurlijk een blok van 9 bij 9 nodig, die dan verder is onderverdeeld in kleinere blokken van 3 bij 3 en in rijen en kolommen. Daar gebruiken we een eigenschap van cellen voor; we kunnen de (dikte van de) randen aanpassen (druk op Ctrl-1). Het bereik van de 81 cellen heeft de naam Sudoku gekregen.

Om straks te weten over welke kolom of rij we het hebben, zijn in regel 3 en in kolom B de cijfers 1 t/m 9 ingevuld. De kruising van die twee, dus cel B3, heeft de naam Ref gekregen. Straks moeten we ook weten wat de inhoud van een geselecteerde cel is; die informatie komt in cel O3 met de naam Selectie.
Regel 3 en de kolommen B en O zijn ‘onzichtbaar’ gemaakt . Dit is te zien aan de +-jes; door daar op te klikken, open en sluit je een bepaalde groep cellen (zie het tabblad Sudoku in het Voorbeeldbestand).

Om het geheel wat mooier te maken is het standaard-raster weggehaald (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rasterlijnen weergeven uit).
Alle regels en kolommen, die we niet nodig hebben, zijn verborgen (selecteer ze eerst en dan rechts klikken).
En als laatste zijn de klom- en rijnamen weggelaten (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rij- en kolomkoppen weergeven uit).

Voor een soepele werking van het spel zijn nog een paar knoppen toegevoegd die bepaalde VBA-routines opstarten; hier komen we later op terug.

Controle 1

Het eerste wat we willen weten als we een Sudoku invullen, is waar overal (als voorbeeld) een 1 staat of waar een 2 etc.
Dat kunnen we met Voorwaardelijke opmaak goed zichtbaar maken; de enige voorwaarde is dat we weten welk cijfer we willen laten oplichten. Daarvoor hebben we een klein VBA-programmaatje nodig:

De subroutine Worksheet_SelectionChange is gekoppeld aan het werkblad Sudoku. Als de selectie in dit werkblad wordt gewijzigd, wordt deze routine automatisch door Excel aangeroepen. Op deze manier bevat de cel Selectie (O3) altijd de laatst geselecteerde waarde uit het bereik Sudoku.

NB bekijk de VBA-routine in het Voorbeeldbestand door bijvoorbeeld met de muis rechts te klikken op de tab Sudoku en dan te kiezen Programmacode weergeven. Eventueel nog dubbelklikken op het blad Sudoku in de Projectverkenner.

Nu kunnen we de Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en Fselecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we de 2e optie (Alleen cellen opmaken met) en geven dan aan dat de opmaak moet worden doorgevoerd als de celwaarde gelijk is aan de inhoud van de cel Selectie (denk aan het =-teken vóór Selectie)
  4. nog even de gewenste Opmaak instellen en OK

Controle 2

De volgende controle zorgt er voor dat we weten of er geen dubbele cijfers in de kolommen, rijen of kleine blokken voorkomen.

Voordat we dit makkelijk en flexibel kunnen doen moeten we wat voorwerk verrichten.
Excel moet automatisch beoordelen bij welke rij, kolom en blok een bepaalde cel hoort. Dan heb ik gedaan door enkele nieuwe Namen in te voeren:

  1. plaats de cursor in de eerste cel van de Sudoku
  2. een nieuwe naam creëren: kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  3. in het vervolgscherm kiezen we als Naam een handige omschrijving, in dit geval RijSel en bij Verwijst naar=Sudoku!$B5
    Denk aan het dollar-teken voor de kolom-letter.
    Afhankelijk waar de cursor staat (nu in regel 5) zal deze naam de waarde uit kolom B van die regel ophalen.
  4. op gelijkwaardige manier bestaat er ook de naam KolomSel met als formule =Sudoku!D$3
    Deze naam geeft dus altijd de waarde uit regel 3 van de kolom waar de cursor staat.
  5. om te bepalen in welk blok de cursor staat is wat ingewikkelder:
    =GEHEEL((Sudoku!RijSel-1)/3)*3+GEHEEL((Sudoku!KolomSel-1)/3)+1
    Hier is de naam BlokSel aan gekoppeld.
  6. nu moeten we nog de inhoud van zo’n rij, kolom en blok kunnen ophalen om de inhoud te kunnen testen. Daartoe zijn nog 3 extra namen gedefinieerd:
    RijTest met de formule =VERSCHUIVING(Sudoku!Ref;Sudoku!RijSel+1;2;1;9)
    KolomTest met =VERSCHUIVING(Sudoku!Ref;2;Sudoku!KolomSel+1;9;1) en
    BlokTest met =VERSCHUIVING(Sudoku!Ref;GEHEEL((Sudoku!BlokSel-1)/3)*3+2;REST((Sudoku!BlokSel-1);3)*3+2;3;3)

Nu kunnen we een nieuwe Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =OF(AANTAL.ALS(BlokTest;D5)>1;
    OF(AANTAL.ALS(RijTest;D5)>1;
    AANTAL.ALS(KolomTest;D5)>1))
    Aantal.Als kijkt hoe vaak in een bereik (de eerste parameter) de waarde van de tweede parameter voorkomt.
  4. nog even een Opmaak instellen en OK

NB1 wil je de gebruikte opmaak bekijken kies dan Regels beheren binnen de optie Voorwaardelijke opmaak

NB2 achteraf gezien was het waarschijnlijker makkelijker geweest om voor het testen de functie INDEX te gebruiken in plaats van VERSCHUIVING. Misschien een andere keer.

Controle 3

Als laatste controle voegen we nog wat opmaak toe, die aangeeft op welke plaatsen een bepaald geselecteerd cijfer nog kan worden ingevuld:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =EN(D5<=0;
    EN(Selectie>0;
    EN(AANTAL.ALS(BlokTest;Selectie)=0;
    EN(AANTAL.ALS(RijTest;Selectie)=0;
    AANTAL.ALS(KolomTest;Selectie)=0))))
    Dus als de betreffende cel, waarvan de opmaak moet worden ingesteld, een waarde kleiner of gelijk aan nul heeft (dus er is nog geen cijfer ingevuld) EN via Selectie weten we welk cijfer we onder de loep nemen EN in het betreffende blok komt het cijfer nog niet voor EN ook niet in de rin EN niet in de kolom dan moet de opmaak toegepast worden.

Buttons/knoppen

Onder de Sudoku zijn een paar knoppen opgenomen, die het opzetten van een spel kunnen vergemakkelijken (zie het Voorbeeldbestand):

  1. Wissen: de beveiliging van het tabblad wordt verwijderd, het speelveld leeg gemaakt, lettertypes en eventuele kleuren worden gereset. Ook de Selectie en de Starttijd worden leeg gemaakt.
  2. de volgende stap is dat een nieuw spel moet worden opgezet. Doe dit op het tabblad Makkelijk of Moeilijk door onder een bestaand spel ‘met de hand’ cijfers op de juiste plaats te zetten.
    Kies dan op het tabblad Sudoku de knop Kopieer. Er komt een pop-up waarmee kan worden aangegeven welk spel uit de voorraad moet worden opgezet. Selecteer daartoe het betreffende blok met de muis en klik dan op OK.
  3. Als je klaar bent om te gaan spelen, klik dan op de knop Start. Allereerst worden alle opgezette cijfers geblokkeerd en vet gemaakt, het tabblad beveiligd (zonder wachtwoord) en de Starttijd ingevuld.
  4. Wil je tussentijds wat zetten gaan proberen, bewaar dan de tussenstand via de knop Save. De stand van dat moment wordt gekopieerd naar het tabblad SaveGame.

Handleiding

In het tabblad Handleiding staat de werkwijze van het systeem ook uitgelegd en ik heb enkele tips toegevoegd.

Veel speel-plezier!


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: