Categorie archief: VBA (Visual Basic for Applications)

EK-2016: wie wordt kampioen?

Excel, VBA (Visual Basic for Applications), , , , , , , ,

ek2016Er is al veel over geschreven: Nederland is er niet bij!

Waar niet bij? Ik bedoel het EK-voetbal natuurlijk. Dus dan maar de focus op België? Of toch Duitsland? Of gastland Frankrijk? Tsja, wie zal op 10 juli aangewezen worden als de kampioen van Europa?

Daarom de WK-voorspeller van 2 jaar geleden maar eens opgepoetst; alle landen ingevoerd, schema nagekeken om tot de ontdekking te komen, dat de indeling van de achtste finales wel heel anders gaat dan bij het WK.

In dit artikel bespreek ik dan ook niet meer de werking en de nauwkeurigheid van de voorspeller (kijk daarvoor naar WK- voorspeller deel 1, deel 2 en deel 3), maar alleen naar de indeling van de ploegen na de groepswedstrijden.
ek2016aO ja, België heeft volgens dit Excel-model trouwens ongeveer 10% kans om kampioen te worden!

Kleine lettertjes: als het model klopt tenminste en alle aannames de werkelijkheid goed benaderen.

Indeling achtste finales

Bij dit EK gaan de nummers 1 en 2 automatisch over naar de knock-out-fase. Van de 6  landen, die bij de groepswedstrijden op plaats 3 eindigen, gaan de beste 4 verder.
ek2016bVoor de indeling van deze 4 is een ingewikkeld schema bedacht (zie hiernaast; bron is WIKIPEDIA)).
De indeling is dus afhankelijk van de groepen waaruit de 3e plaatsen komen (aangeduid met de letters A t/m F).

In het Voorbeeldbestand worden daarom de volgende stappen doorlopen om tot de juiste indeling van de achtste finales te komen (zie tabblad Groepsfase):

  1. in de cellen X4 t/m X9 worden de punten van de landen opgehaald die derde zijn geworden in de 6 groepen
  2. met behulp van de functie GROOTSTE (waarbij ook kan worden aangegeven of je echt de grootste wilt hebben of de een na grootste enz) worden de beste 4 resultaten opgehaald in de cellen X12 t/m X15
  3. in de cellen Y12 t/m Y15 worden dan met behulp van de functies INDEX en VERGELIJKEN de corresponderende groepsletters opgehaald
  4. nu de moeilijkste stap: in cel Y16 (met de naam Serie3) moeten de 4 groepsletters gesorteerd worden.
    Om de functie GROOTSTE te kunnen gebruiken moeten we letters eerst vertalen naar getallen met de functie CODE (een A wordt dan 65, een B 66 etc).
    Als we via GROOTSTE de getallen in de juiste volgorde hebben staan, wordt het getal weer terugvertaald naar een letter met de functie TEKEN. De letters worden aan elkaar ‘geplakt’ met behulp van het &-teken.
    We moeten Excel nog wel aangeven, dat in de formule de functie GROOTSTE op een serie cellen moet worden losgelaten; daarom is de invoer van de formule niet met Enter afgesloten maar met Ctrl-Shift-Enter (we hebben dus een zogenaamde CSE-formule ingevoerd; zie ook het artikel SOMPRODUCT: meer dan SOM en PRODUCT).
  5. Het resultaat in cel Y16 (=Serie3) wordt in het tabblad Finales gebruikt om de juiste tegenstander van de poule-winnaars op te halen; bijvoorbeeld in cel B9 moet de tegenstander van de winnaar van groep B komen:
    =VERT.ZOEKEN(Serie3;Series3;3;ONWAAR)
    Series3 is de naam van de cellen met het Wikipedia-overzicht in het tabblad Groepsfase; de tegenstander van de B-winnaar staat in de derde kolom.

Verwerken resultaten

De eerste wedstrijden zijn ondertussen gespeeld. Geef in kolom M van het tabblad Groepsfase de juiste punten aan de betreffende ploeg.

Wanneer je de resultaten op deze manier in het spreadsheet verwerkt, blijken de kansen voor Duitsland en Frankrijk dankzij hun gewonnen wedstrijden al iets te zijn toegenomen.

Draaitabel opmaken

Excel, VBA (Visual Basic for Applications), ,

Kleine ergernissen kunnen soms uitgroeien tot grote.

Iedereen, die regelmatig een draaitabel maakt, kan dat waarschijnlijk beamen: de draaitabel is snel gemaakt, maar dan moeten er nog diverse handelingen uitgevoerd worden om de lay-out zodanig aan te passen, dat de draaitabel er uitziet, zoals je graag wilt.
Veel van die instellingen zitten niet bij elkaar in de menu-structuur, dus is het zoeken, muizen en klikken geblazen.

Eén van die instellingen is bijvoorbeeld de rapportindeling: iemand bij Microsoft heeft bedacht,  dat de standaard-instelling daarvoor Compact moet zijn en deze standaard is niet te wijzigen.
Deze week bleek uit een vraag van een oud-collega, dat ik niet de enige ben die liever met de ‘klassieke’ indeling werkt.

PT1In dit artikel zal ik laten zien, dat het mogelijk is om alle aanpassingen, die je vaak maakt, op één formulier te plaatsen, zodat het lastige zoeken naar de diverse menu-opties achterwege kan blijven.

Hieronder zal ik uitleggen hoe zo’n  formulier wordt opgebouwd; de gegevens, die ik als voorbeeld gebruik, de daarbij behorende draaitabel en een reeds gebouwd (uitgebreider) formulier vind je in het Voorbeeldbestand.

Formulier maken

Een formulier voor Excel wordt gemaakt in de VBA-editor.
PT2In de editor kom je, wanneer je in de menutab Ontwikkelaars in het blok Programmacode de optie Visual Basic kiest.
Maar veel makkelijker is natuurlijk de toetscombinatie Alt-F11.

NB Is de menutab Ontwikkelaars niet zichtbaar, pas dan de Menubalk aan bij Bestand/Opties.

In de VBA-editor maken we nu als volgt een formulier met enkele voorbeeld-opties:

  1. zorg in het Project-overzicht dat een Excel-map is geselecteerd door er op te klikken (heb je geen Excel-map open, ga dan terug naar Excel (Alt-F11), klik Ctrl-N en ga weer naar VBA)
  2. kies in de menu-balk Invoegen en daarbinnen de optie UserForm
  3. PT3er opent zich een leeg raster (het nieuwe formulier) waarop we met behulp van de Werkset allerlei besturingselementen kunnen plaatsen
  4. Links onder het Project-overzicht zie je het Eigenschappen-venster van het actieve element; nu staan daar de eigenschappen van het formulier.
    Voorlopig zijn alleen de eigenschap Name, waarmee we het formulier binnen VBA kunnen benaderen,  en de Caption (de titel bovenaan het formulier) relevant.
    De overige eigenschappen zijn vooral bedoeld om de lay-out van het formulier te beïnvloeden.
    Bij Name voeren we in UF_DT2 en bij Caption de tekst Draaitabel opmaken.
  5. Nu gaan we 2 opties toevoegen waarmee we kunnen bepalen of er wel of niet rij- en kolomtotalen moeten worden weergegeven in de draaitabel:
    * klik in het grijze raster
    * sleep vanuit de Werkset het selectievakje PT4 naar het raster op de plaats, waar de optie moet komen en herhaal dat een 2e keer (de eerste krijgt automatisch de naam Checkbox1 en de tweede Checkbox2)
    * de namen laten we zo, maar de Caption veranderen we via de Eigenschappen: de eerste krijgt wordt Rij-totalen, de tweede Kolom-totalen
  6. Voor de Rapportindeling willen we drie opties aanbieden. In tegenstelling tot de opties van het vorige punt sluiten deze elkaar uit: een draaitabel kan niet tegelijkertijd in de Compact- en in de Klassieke weergave staan, terwijl we natuurlijk wel de rij- en kolom-totalen los van elkaar willen kunnen aan- en uitzetten.
    Hiervoor gebruiken we dan ook geen Selectievakjes maar Keuzerondjes:
    * sleep drie keer een keuzerondje PT6 naar het formulier
    * verander de Caption daarvan in Compact, respectievelijk Overzicht en Classic
    * de namen van de rondjes laten we gehandhaafd (die blijven dus OptionButton 1, 2 en 3)
  7. Test het formulier even:
    * druk op F5
    * klik op de diverse opties: de Selectievakjes kunnen los van elkaar aan- en uitgezet worden, terwijl er bij de Keuzerondjes altijd maar één actief is
    * het formulier doet verder nog niets; klik rechtsboven op het kruisje om te sluiten
  8. Dan gaan we 2 opdrachtknoppen toevoegen:
    * sleep 2 keer een Opdrachtknop vanuit de Werkset naar het raster. Heb je te weinig plaats: maak het raster groter door de randen te verslepen of de reeds geplaatste elementen te verplaatsen.
    * de eerste opdrachtknop geven we de volgende eigenschappen:  de naam wordt ok en ook de caption wijzigen we daarin; de tweede krijgt de naam cancel en als caption Annuleren
  9. Klik met de rechter muisknop op de 2e opdrachtknop en kies de optie Programmacode weergeven. Alles wat  PT5tussen de 2 regels staat (nu nog niets!) zal worden uitgevoerd wanneer we op de Annuleer-knop klikken. Tik in de 2e (lege) regel in: unload me
    Dit is voor VBA het commando om het formulier (me) uit het geheugen te verwijderen en dus te sluiten.
    NB omdat VBA de 2 woorden herkent zullen er (na ‘Enteren’) automatisch hoofdletters tevoorschijn komen.
  10. Test het formulier opnieuw:
    * dubbelklik in het projectoverzicht op UF_DT2
    * druk op F5.
    * wat doet de ok-knop? En de annuleer-knop?

NB De programmacode, die nodig is wanneer er op de OK-knop wordt geklikt, is wel wat uitgebreider. Door in het Voorbeeldbestand in de VBA-editor rechts op de ok-knop te klikken kan de programma-code bekeken worden.

LET OP heb je nog meer keuzerondjes nodig, maar moeten die niet afhankelijk zijn van de keuze van de overige rondjes, zorg dan dat ieder blok bij elkaar horende keuzerondjes in een zogenaamd Groepsvak (ook een element van de Werkset) wordt geplaatst.
In het Voorbeeldbestand zijn op die manier de instellingen voor de Rapport-indeling en voor de Veld-opmaak afzonderlijk in  te stellen.

Formulier activeren

Nu moeten we nog een mogelijkheid maken om het formulier in Excel op te roepen:

  1. PT7we gaan weer naar VBA (Alt-F11)
  2. klik in de projectverkenner op de Excel-map, waarin het formulier UF_DT2 staat
  3. kies in de het VBA-menu Invoegen en daarbinnen de optie Module
  4. tik dan in:
    sub Start()
    UF_DT2.show
    end sub
Het formulier kun je voortaan opstarten door de macro Start aan te roepen: kies in Excel (om vanuit VBA naar Excel te gaan: Alt-F11) in de menutab Ontwikkelaars in het blok Programmacode de optie Macro’s, klik op de macro Start en dan op de knop Uitvoeren.
NB1 het macro-overzicht kun je ook bereiken door op Alt-F8 te drukken.

NB2 de regel UF_DT2.Show is voldoende om het formulier te tonen. In het Voorbeeldbestand is die routine uitgebreid met programmacode, die er voor zorgt dat de keuzerondjes en selectievakjes overeenkomen met de instellingen van de draaitabel op dat moment.

NB3 heb je de macro/routine vaak nodig, dan is het handig om daar een toetscombinatie aan te koppelen:

  1. ga naar de macro’s (Alt-F8)
  2. klik op de macro Start
  3. klik op de knop Opties
  4. tik een letter in (eventueel samen met Shift) waarmee de macro opgestart moet worden (bijvoorbeeld q, want de sneltoets Ctrl-q wordt door Windows niet standaard gebruikt)
  5. klik Ok en dan Annuleren (dus niet Uitvoeren!)
  6. probeer de toetscombinatie uit.

Formulier altijd klaar voor gebruik

In het voorbeeld hiervoor en in het Voorbeeldbestand zijn het formulier en de diverse routines in een Excel-werkmap opgenomen en kunnen dus ook alleen gebruikt worden, wanneer deze werkmap is geopend.

Wil je altijd de beschikking hebben over formulieren of routines dan moeten die worden opgenomen in de persoonlijke, onzichtbare werkmap Personal.xlsb.
Hoe kom je aan die werkmap? Het meest eenvoudige is op de volgende manier:

  1. PT8kies in Excel in de menutab Ontwikkelaars in het blok Programmacode de optie Macro opnemen
  2. Zorg dat in het middenvak de optie Persoonlijke macrowerkmap is geselecteerd
  3. klik OK
  4. klik dan met de muis op een willekeurige cel en stop de opname
  5. ga naar de VBA-editor (Alt-F11) en verwijder in de Module1 van Personal.xlsb de zojuist opgenomen macro.

Formulier en routine kopiëren naar de persoonlijke werkmap:

  1. open het Voorbeeldbestand (als dat nog niet gebeurd is)
  2. ga naar de VBA-editor (Alt-F11)
  3. sleep het formulier UF-DT vanuit PT.xlsm naar Personal.xlsb
  4. idem met de Module1
  5. sluit Excel en bevestig de vraag over het opslaan van de persoonlijke map.

Elf-proef

Excel, VBA (Visual Basic for Applications), , , ,

bsnBSN: iedereen heeft er een, maar weinig mensen zullen dit nummer uit hun hoofd kennen.
Bij het invullen worden dan ook vaak fouten gemaakt. Iedere instantie wil daarom graag geautomatiseerd checken of het ingevoerde getal juist is.

De controle die je op een BSN kunt uitvoeren is een variant van de elf-proef. Vóór het IBAN-tijdperk werd deze gebruikt om bankrekeningnummers te controleren.

Hoewel de toepassing van de elf-proef voor bankrekeningnummers niet meer geldt, komt deze in de praktijk toch nog regelmatig voor. In Excel is die in te bouwen, maar vergt nogal wat hulpkolommen; de elf-proef is dan ook bij uitstek geschikt om via VBA in een eigen formule te verwerken.
Hieronder (na uitleg van de elf-proef) dan ook voorbeelden in Excel en met behulp van VBA (deze functie is dan ook zonder meer bruikbaar in Access).
Daarna behandelen we een alternatieve functie die ook geschikt is voor de controle van een BSN.

Elf-proef

Wikipedia: “De elfproef (11-proef) is een test die in het Nederlandse elektronische betalingsverkeer werd uitgevoerd op negen- en tiencijferige Nederlandse bankrekeningnummers, voor de invoering van het IBAN, om te controleren of het nummer een geldig rekeningnummer kan zijn. Varianten van de elfproef die gebruikmaken van een controlecijfer, worden toegepast bij andere belangrijke nummers, zoals het burgerservicenummer en het betalingskenmerk op een acceptgiro.
…
Het laatste cijfer van het rekeningnummer wordt met 1 vermenigvuldigd, het voorlaatste met 2, het op twee na laatste met 3, enzovoorts. De producten worden bij elkaar opgeteld en vervolgens wordt de som gedeeld door 11. Het resultaat van deze deling moet voor een geldig rekeningnummer een geheel getal zijn.”

Duidelijk toch: ieder cijfer van het rekeningnummer moeten we vermenigvuldigen met zijn gewicht, die producten optellen, de som moet dan deelbaar zijn door 11 oftewel de rest (na deling; bijvoorbeeld bij het delen van 24 door 11 is de rest 2) moet dan nul zijn.

Excel-voorbeeld 1

Ik bedacht me dat ik ooit eens voor een toepassing de functie Elfproef had gemaakt en die later ook bij cursussen gebruikt had. Even zoeken en ja hoor gevonden.

Maar voordat we die gaan gebruiken kijken we eerst in Excel hoe de elfproef werkt: ziet tabblad Vb1 van het Voorbeeldbestand.
Elfproef1Van het banknummer in kolom A wordt telkens, van achter af, een cijfer geïsoleerd (kolommen D, F, H etcetera; bekijk ook de formules in de kolommen E, G enzovoort) en die cijfers worden in kolom C met hun gewicht vermenigvuldigd. In kolom C worden die producten ook nog opgeteld en wordt de Rest bepaald bij deling door 11. Als daar 0 uitkomt dan is het antwoord ok, anders Geen juist nr.

Elfproef2Deze elfproef is bedoeld voor bankrekeningnummers bestaande uit 9 of 10 cijfers. Kolom A bevat dan ook een input-controle, zodat we in de berekeningen niet allemaal controles hoeven in te bouwen.
De input-controle is geïmplementeerd met behulp van Gegevens-validatie: kies de menutab Gegevens en dan in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie. De drie tabbladen zijn als volgt ingevuld:

Elfproef3

Elfproef4Elfproef5

VBA-voorbeeld 1

Om al die hulpkolommen te vermijden is in kolom B gebruik gemaakt van een eigen functie ElfProef1, die dezelfde resultaten oplevert:
Elfproef6In het Voorbeeldbestand is de functie te vinden door de VBA-editor te starten (Alt-F11 of  via het tabblad Ontwikkelaars of door onderaan rechts te klikken op de naam van het tabblad Vb1 en Module1 te kiezen).

Enige uitleg is op zijn plaats:

  1. In Excel wordt de functie aangeroepen met als parameter (de verwijzing tussen de haakjes) een bankrekeningnummer (in het voorbeeld door te verwijzen naar de cellen in kolom A). In de definitie van de Function ElfProef1 staat dan ook dat er een parameter getal wordt doorgegeven van het type Long (een groot geheel getal). Consequentie hiervan is wel, dat als de functie in Excel verwijst naar een tekst (bijvoorbeeld naar cel B1) de functie niets doet en als resultaat #WAARDE! teruggeeft.
    Achter de functie staat As String: het resultaat van de functie is een tekst (string).
  2. ‘groene’ teksten is voor VBA commentaar; hebben verder geen functie. Invoeren door een enkele aanhalingsteken in te tikken.
  3. met Dim geven we aan welke variabelen we in het programma/de functie gaan gebruiken
  4. om hierna makkelijk de cijfers één voor één te kunnen ‘losknippen’ maken we met behulp van de VBA-functie Str van het getal een tekst (string)
  5. door middel van de For-loop wordt de berekening uitgevoerd: de eerste keer wordt i gelijk aan 1, via Next wordt die 2 enzovoort net zolang tot i groter is dan de lengte van de tekst; dan gaat het programma verder met de opdracht na Next
  6.  in de loop wordt de waarde (Val) bepaald van de diverse cijfers en die waarde wordt met i vermenigvuldigd en bij het (reeds bestaande) lngResult opgeteld.
    Omdat het laatste cijfer het gewicht 1 krijgt etc moeten we met het losknippen achteraan beginnen: dus we nemen het gedeelte (Mid) van de tekst dat start op positie lengte + 1  – i; de laatste parameter van de Mid-functie (het getal 1) geeft aan dat we 1 positie uit de tekst knippen.
  7. we gaan er van uit, dat het bankrekeningnummer voldoet, dus geven standaard als output voor de functie ok mee
  8. maar we moeten nog testen wat de deling door 11 oplevert: hier delen we de som door 11, nemen dan het gehele deel daarvan (de functie Int), vermenigvuldigen dan weer met 11 en kijken  of dit weer gelijk is aan de oorspronkelijke som. Zo niet (<> betekent niet gelijk) dan wordt de output van de functie ONGELDIG.

NB de kolommen B en C zijn van een voorwaardelijke opmaak voorzien, zodat snel duidelijk is wanneer een banknummer aan de elfproef voldoet.

Excel-voorbeeld 2

In het Voorbeeldbestand is een tweede voorbeeld opgenomen (zie tabblad Vb2).
Elfproef7We maken van het bankrekeningnummer uit cel A3 (met dezelfde gegevensvalidatie als in voorbeeld 1) eerst een tekst van 10 tekens: we zetten daartoe vóór het rekeningnummer eerst 10 nullen (mbv het &-teken) en nemen dan de 10 rechtse tekens daarvan (cel B3).

NB in cel B4 staat een alternatief met de functie Herhaling, waardoor het duidelijker is dat we 10 keer de nul er voor zetten.

In de kolommen ontleden we de tekst weer in 10 losse gedeelten (van achter naar voren); er wordt een 0 bij opgeteld om Excel automatisch de tekst te laten omzetten naar een getal. In rij 4 wordt de vermenigvuldiging uitgevoerd, waarna in cel D4 de Som daarvan wordt bepaald. In cel D3 bepalen we dan of een deling door 11 als rest 0 oplevert.

In cel D5 wordt de vermenigvuldiging, optelling en check in één formule uitgevoerd:

=ALS(REST(SOMPRODUCT(E2:N2;E3:N3);11)=0;”ok”;”ONGELDIG”)

Voor uitleg over de functie SOMPRODUCT zie mijn vorige artikel.

VBA-voorbeeld 2

In de VBA-editor is ook een functie ElfProef2 opgenomen (alles met isBSN even overslaan).

De verschillen met de vorige functie:

  1. de input-parameter getal is gedeclareerd als String (tekst)
  2. een voordeel daarvan is, dat we de functie exacter kunnen laten aangeven wanneer de invoer niet aan onze eisen voldoet:
    If getal = “” Or IsNull(getal) Or Not IsNumeric(getal) Then
            ElfProef2 = “Geen juiste invoer”
            Exit Function
        End If
    Dus als de invoer leeg is (op 2 manieren) of als het geen numerieke waarde (getal) is dan wordt de output van de functie Geen juiste invoer en wordt deze afgebroken.
    NB deze test kan uitgebreid en nog concreter gemaakt worden afhankelijk van de behoefte.
  3. in de functie vertalen we het getal naar een tekst van 10 tekens (vergelijkbaar met wat we in Excel hebben gedaan)
  4. de constructie lngResult Mod 11 is vergelijkbaar met de functie Rest in Excel

BSN

Zoals hiervoor aangegeven wordt voor de controle van het BSN een vorm van de elfproef wordt gebruikt: het enige verschil met de standaard is, dat het laatste cijfer niet met +1 maar met -1 wordt vermenigvuldigd.

Bij de functie ElfProef2 is daar al rekening mee gehouden:

  1. optioneel (dus niet verplicht) kan aan de functie een tweede parameter isBSN worden meegegeven (Optional isBSN As Boolean = False). Een variabele van het type Boolean kan alleen de waarde WAAR of ONWAAR (True of False) hebben;  als de parameter niet wordt meegegeven dan krijgt deze de waarde ONWAAR/False.
  2. dmv de regel
    If    i = 1   And    isBSN     Then    hulp   =   – hulp
    zorgen we er voor, dat als i gelijk is aan 1 EN isBSN waar is dat dan het tegenovergestelde van hulp wordt opgeteld

Elfproef8In het tabblad BSN van het Voorbeeldbestand zien we dat het standaard-gebruik van de functie ElfProef2 in cel C3 aangeeft dat we te maken zouden hebben met een ongeldig BSN.

Gebruiken we echter de vorm =ElfProef2(B3;WAAR), zoals in cel D3, dan levert dit als resultaat ok op.

Slicers in Excel

Excel, VBA (Visual Basic for Applications), , , ,

SlicerEén van de weinige woorden, die in de Nederlandstalige versie van Excel niet zijn vertaald, is Slicer.
Ik merk in de praktijk, dat het niet voor iedereen meteen duidelijk is, wat er in dit geval mee wordt bedoeld. In ieder geval niet zo’n machine zoals hiernaast afgebeeld!

De functionaliteit Slicer vinden we sinds versie 2010 terug bij het onderdeel Draaitabellen en is bedoeld om sneller, makkelijker en overzichtelijker selecties te maken/filters te definiëren.

In dit artikel zal ik de meest gangbare toepassingen toelichten. Hoewel er een directe koppeling tussen een Slicer en een Draaitabel bestaat, zullen we zien, dat met een klein beetje VBA de inhoud van de slicers ook op andere plaatsen en op andere manieren gebruikt kan worden.

LET OP Slicers worden door Excel voor de Mac niet ondersteund; bijgaand Voorbeeldbestand kunt u op de Mac dan ook beter niet opstarten.

Basisgegevens

Slicer1Om het gebruik van een Slicer toe te lichten hebben we basisgegevens nodig aan de hand waarvan we een draaitabel kunnen maken.
In het Voorbeeldbestand zien we in het tabblad Basis een fictief omzet-overzicht, gesplitst naar Jaar, Maand, Regio en Soort product.
Slicer2De gegevens zijn opgeslagen in de vorm van een tabel met behulp van de optie Invoegen/Tabel. Excel kent daarbij automatisch de naam Tabel1 aan dit gebied toe.
Een groot voordeel hiervan is, dat als deze tabel wordt uitgebreid met nieuwe regels of kolommen, alle draaitabellen, die hierop gebaseerd zijn, automatisch rekening houden met die uitbreiding. Hetzelfde geldt ook, wanneer er gegevens uit de tabel worden verwijderd: bij het vernieuwen van de draaitabel zul je merken, dat de draaitabel zich daar automatisch aan aanpast.

Slicer3NB1 een ander voordeel van een tabel merk je, wanneer je in het bestand naar beneden scrolt: zonder dat er titels geblokkeerd hoeven te worden, blijven de kopjes van de tabel zichtbaar.

NB2 het voorbeeld is gemaakt door voor alle kolommen willekeurige resultaten te genereren met behulp van de functie ASELECTTUSSEN, eventueel aangevuld met de functie KIEZEN (zie de cellen H3 t/m L3)

Draaitabel

In het basisbestand zijn 100.000 regels/records opgenomen. Met behulp van een draaitabel kunnen deze razendsnel gerubriceerd worden: welke jaren komen voor, zijn alle maanden gevuld, welke regio’s worden gebruikt, welke producten hebben een omzet gegenereerd en wat zijn de bijbehorende bedragen?

Hoe maken we een draaitabel?

  1. Slicer4plaats de cursor ergens in de tabel met basisgegevens, bijvoorbeeld door in cel B4 te klikken
  2. kies in het menutabblad Invoegen in het blok Tabellen de optie Draaitabel
  3. u ziet dan een tussenscherm, waarin vrijwel altijd de voorgestelde keuzes zullen voldoen: Excel heeft de tabel waarin de cursor staat als basis geselecteerd en het resultaat zal in een nieuw werkblad komen
  4. klik op OK
  5. Slicer5er opent zich een nieuw werkblad met rechts een overzicht van alle velden/kolommen uit de geselecteerde tabel.
    Voor ons eerste overzicht vinken we het Jaar, de Regio en de Omzet aan.
    Excel zal proberen te bedenken waar we de gegevens willen hebben. In dit geval gaat hij de Som van de jaren bepalen, maar dat is natuurlijk niet de bedoeling: sleep het blokje “Som van Jaar” van -waarden naar de Kolomlabels en Excel gaat de omzet uitsplitsen naar jaar.

Slicer7“Sneller dan het geluid” zien we dat de verdeling van de omzet in dit geval redelijk evenredig over de regio’s en jaren heeft plaats gevonden (niet vreemd natuurlijk omdat we de gegevens aselect hebben gegenereerd!).

Om de omzet beter te kunnen lezen gaan we de opmaak aanpassen: dit doen we NIET door de opmaak van de cellen B2:E9 aan te passen; als er straks een jaar bijkomt moeten we die kolom dan weer opnieuw opmaken.
Nee, klik met de rechter muisknop op één van de cijfers (bijvoorbeeld cel B5), kies Getalnotatie en binnen de categorie Getal geen decimalen en een scheidingsteken voor duizendtallen.

Filters in Draaitabellen

In het Voorbeeldbestand is op het tabblad Draai1 een ander voorbeeld van een draaitabel gegenereerd: in de rijen is de omzet opgesplitst naar de maanden en in de kolommen naar product.
Slicer8Om een filtering naar Jaar en/of Regio mogelijk te maken zijn die 2 velden in het blok Rapportfilter geplaatst. Deze 2 velden zijn daardoor boven de draaitabel terecht gekomen; door middel van de vinkjes in de cellen B1 en B2 kan dan een jaar of regio geselecteerd worden.

Daarnaast zijn nog de volgende aanpassingen doorgevoerd:

  1. Slicer9via het tabblad Ontwerpen in Hulpmiddelen voor Draaitabellen hebben we binnen Rapportindeling de Tabelweergave gekozen.
    Welke optie het meest geschikt is, hangt zeer sterk af van de inhoud en opzet van de draaitabel; experimenteer hiermee!
    NB Hulpmiddelen voor draaitabellen is alleen zichtbaar wanneer de cursor ergens in de draaitabel staat.
  2. naast de Som van Omzet hebben we ook aantallen en gemiddelde omzet toegevoegd. Om dit te bereiken sleept u de Omzet uit de Lijst met draaitabelvelden opnieuw naar het waarden-gebied.
    Excel zal dan opnieuw de som bepalen; om dit te veranderen moet u in de draaitabel rechts klikken op een cel, die u wilt wijzigen; kies de optie Waarden samenvatten per en kies daar Aantal of Gemiddelde

Compacte draaitabellen

Slicer10Eén van de nadelen van een standaard-draaitabel is dat de kolommen nogal breed worden doordat Excel namen genereert als Som van Omzet etc.

Gelukkig is dit snel aan te passen: in het Voorbeeldbestand is in het tabblad Draai2 de kop in cel B6 aangepast door in die cel nieuwe tekst in te tikken (in dit geval Aantal).

LET OP u zult merken, dat cel C6 zich niet laat veranderen in Omzet; Excel weet dan niet meer of u nu het veld uit de basisgegevens bedoelt of de kolom in de draaitabel. In dit geval hebben we dat opgelost door een spatie achter het woord Omzet te plaatsen! Had er natuurlijk ook vóór mogen staan.

Slicers

Om het filteren (zoals in het plaatje hierboven voor Jaar en Regio) makkelijker te maken is in Excel de optie Slicer ontwikkeld.

Hoe maakt u een slicer?

  1. Activeer de draaitabel, waarvoor een slicer gemaakt moet worden. Klik daartoe met de muis op één van de cellen van de draaitabel.
  2. in het tabblad Hulpmiddelen voor draaitabellen, dat dan beschikbaar komt, kiest u Opties
  3. in het blok Sorteren en filteren kiest u de button Slicer invoegen
  4. alle velden uit de brongegegevens komen in aanmerking; in dit voorbeeld vinken we alleen Jaar en Regio aan. Klik op OK.
  5. Standaard worden de slicers verticaal weergegeven: alle opties onder elkaar.
    Omdat nu de slicers geselecteerd zijn, ziet u bovenaan Hulpmiddelen voor slicers. Kies daar eventueel een andere Slicerstijl en pas het aantal gewenste Kolommen aan (het aantal opties naast elkaar)


Het filteren doet u nu door de betreffende button aan te klikken.
Meerdere selecties binnen een categorie nodig? Hou Ctrl ingedrukt en klik op de gewenste buttons.
Alle items nodig? Klik op Slicer12 .

In het tabblad Draai3 van het Voorbeeldbestand ziet u, dat de velden Jaar en Regio in het Rapportfilter niet meer nodig zijn; deze zijn vervangen door de slicers.

LET OP zorg bij het printen van de draaitabel, dat ook de slicers zichtbaar zijn, anders is niet duidelijk welke filtering is toegepast.
In het tabblad Draai3 is een draaigrafiek weergegeven (in dit geval een weinig-zeggende, maar voor het voorbeeld is de inhoud niet relevant); deze is gebaseerd op de onderliggende draaitabel en dus ook op de slicers. Wanneer deze grafiek apart geprint zou worden is geen enkele informatie over een eventuele filtering zichtbaar.

Inhoud slicer

Om te voorkomen, dat bij bovenstaand probleem er iedere keer handmatig een tekst aan het overzicht of de grafiek moet worden toegevoegd, zou een dynamische referentie naar de inhoud van de slicers mooi zijn. Dit zou dan in een tekst kunnen worden opgenomen.

Helaas kent Excel deze mogelijkheid (nog) niet; er is een interne koppeling tussen de inhoud van de slicer en de filtering in de draaitabel, maar die is niet standaard met een functie uit te lezen.

slicer13Op internet heb ik echter een UDF (User Definied Function) gevonden op de site jkp-ads.com waarmee dit wel mogelijk is. Zo’n UDF wordt in een module van VBA vastgelegd (zie hieronder).

In het tabblad Draai4 van het Voorbeeldbestand is deze toegepast om de tekst in cel B7 op te kunnen bouwen:
=”Omzet voor ” & ALS(I2=”Alles”;”alle jaren”;I2) & ” en ” & ALS(J2=”Alles”;”alle regio’s”;J2)

Met behulp van het &-teken worden teksten aan elkaar gekoppeld: allereerst de tekst Omzet voor, dan (afhankelijk van de inhoud van cel I2) de tekst alle jaren of de inhoud van I2 etc.

Maar in I2 en J2 staat toch niets? Op het oog niet nee, maar de tekstkleur van die cellen is op Wit ingesteld!

Laten we I2 eens bekijken:
=GetSelectedSlicerItems(“Slicer_Jaar1”)

Hier wordt de UDF GetSelectedSlicerItems aangeroepen met de parameter Slicer_jaar1. Dit is de naam die we terugvinden als we rechtsklikken op de eerste slicer en dan de Slicerinstellingen bekijken.

slicer14

VBA gebruikt de omschrijving zoals vermeld achter Naam om in formules te gebruiken.

Grafiektitel

Dezelfde tekst die boven de draaitabel staat, willen we ook als grafiektitel:

  1. klik op de grafiek
  2. kies in Hulpmiddelen van Draaigrafieken, die dan zichtbaar wordt, de optie Indeling
  3. kies in het blok Labels, de optie Grafiektitel en kies Boven grafiek
  4. tik dan direct in de formulebalk ( dus achter de FunctieInvoeren) de formule: =Draai4!$B$7 of tik in = en klik met de muis op de betreffende cel
  5. druk de Enter-toets in

VBA

Deze keer geen uitleg van de VBA-routine; ik denk dat hij voor een beginnende VBA’er goed te volgen zal zijn.

Maar hoe voegt u een routine, die u ergens hebt gevonden (op internet, in een andere Excel-toepassing) aan uw eigen werkmap toe?

  1. kopieer de routine, die hebt gevonden (bijvoorbeeld met behulp van Ctrl-C)
  2. ga naar de werkmap waar u de routine wilt hebben
  3. klik Alt-F11, de Visual Basis Editor opent dan
  4. klik in de menu-tabs op Invoegen en dan Module
  5. in de zo nieuw gemaakte module plakt u de routine uit het  de eerste stap (bijvoorbeeld met behulp van Ctrl-V)

De UDF GetSelectedSlicerItems kan de volgende resultaten opleveren:

  1. Alles, als er geen filter is gedefinieerd
  2. Niets, als er door andere filtering voor deze slicer geen mogelijkheden zijn
  3. Geen slicer gevonden, als de tekst die als parameter wordt meegegeven geen bestaande slicer is
  4. het gekozen filter of bij meervoudige filtering de keuzes, gescheiden door een komma

Eerbetoon aan Rosling cs

Excel, VBA (Visual Basic for Applications), , , , , ,

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.