Maandelijks archief: november 2015

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.

Analoge klok in Excel

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

astronomische klok praagIedereen die wel eens in Praag is geweest, heeft waarschijnlijk wel een paar keer stil gestaan bij het astronomische uurwerk in het centrum.
Zeker wanneer op het hele uur de 12 apostelen zich vertonen, is het een drukte van jewelste op het Oudestadsplein.

Daar stond ik dus ook, toen we een paar weken geleden een bezoek brachten aan deze prachtige stad.

Het bloed kruipt waar het niet gaan kan: ik dacht meteen, dat moet in Excel na te bouwen zijn!
Maar om alle facetten van de klok te implementeren valt onder de grotere uitdagingen; laten we maar eerst beginnen om een ‘gewone’, analoge klok in Excel op te zetten.

Basis

Uiteraard hebben we de huidige tijd nodig, waar we de klok op gaan baseren: in Excel gebruiken we daar de formule =NU() voor; in het Voorbeeldbestand staat die in cel C2.
Klok1LET OP dit is één van de weinige formules, die geen paramaters kent, maar om voor Excel duidelijk te maken dat het een formule betreft zijn wel de 2 haakjes nodig.

LET OP 2 tijdens het schrijven van het artikel veranderde de inhoud van cel C2 constant. In de volgende voorbeelden komen de getallen dan ook niet meer overeen met de tijd hierboven.

Uit de tijd leiden we het uur, de minuten en de secondes af met de formule =UUR(C2), =MINUUT(C2) respectievelijk =SECONDE(C2).
Omdat we een analoge klok gaan maken, die maar 12 uren kent, staat in cel E3 de formule =REST(E2;12). Hiermee berekenen we de restwaarde als we de uren van cel E2 delen door 12. Uiteraard had dit ook met de formule = ALS(E2>12;E2-12;E2) gekund, maar de eerste formule is korter (en interessanter!?).

Wijzers

Maar hoe gaan we nu die wijzers tekenen in Excel?
Daartoe moeten we ons realiseren, dat de tijd (beter gezegd het aantal seconden na 12 uur) de hoek van de wijzers bepaalt.

We gaan eerst de kleine wijzer tekenen:

  1. Klok3het aantal seconden na 12 uur berekenen we in cel C9: =(E3*60+H3)*60+K3
  2. om hierna de hoek te kunnen bepalen, moeten we weten hoeveel seconden er maximaal in 24 uur zitten; zie cel C10
  3. door de inhoud van cel C9 te delen door die van cel C10, weten we welk gedeelte van de klok door de kleine wijzer is afgelegd (cel C11)
  4. de zo berekende fractie moeten we nog in een hoek vertalen: in C12 komt de formule =C11*360, omdat een hele cirkel 360 graden is.

Nu we de hoek weten, moeten we dit gaan omzetten naar een punt op de klok (cirkel). Gelukkig hebben we vroeger allemaal goed opgelet bij wiskunde en weten natuurlijk(?) nog dat de x-coördinaat van zo’n punt wordt berekend door de cosinus van de hoek te nemen en de y-coördinaat door de sinus.
Maar we zijn nog niet klaar: in de wiskunde (en in Excel) wordt een hoek bepaald ‘rekenend tegen de klok in’; voor ons rekenwerk gebruiken we dan ook de cosinus en sinus van -C12.
Omdat Excel als basis voor de hoek de positieve x-as neemt (een hoek van 0 graden komt dus overeen met een horizontale lijn naar rechts) en we 0 uur netjes bovenaan willen laten beginnen tellen we bij onze berekende hoek nog 90 graden op.
Daarmee zou de x-coördinaat van de kleine wijzer dus worden =COS(-C12+90). Helaas Excel kent ons graden-systeem niet, maar werkt met radialen (jaja, de middelbare schooltijd is toch niet voor niets geweest!). Aangezien 360 graden overeen komt met 2π radialen, delen we onze berekende graden door 360 en vermenigvuldigen met 2π; de x-coördinaat wordt nu =COS((-C12+90)*2*PI()/360).
LET OP ook de functie PI kent geen parameters, maar de 2 haakjes zijn wel nodig.

Hetzelfde verhaal geldt ook voor de y-coördinaat: =SIN((-C12+90)*2*PI()/360).
Maar het is een kleine wijzer: dus we vermenigvuldigen de coördinaten nog met bijvoorbeeld 0,6 (staat in cel C13 en is dus makkelijk aanpasbaar).
Door nu een lijn te tekenen (en daar wat opmaak aan mee te geven) van de oorsprong van het assenstelsel (met de coördinaten 0,0) naar de zo berekende coördinaten hebben we onze wijzer. Gebruik daarvoor geen lijngrafiek maar een spreidingsgrafiek.

NB Bij een lijngrafiek komen de elementen op de x-as allemaal op dezelfde afstand van elkaar; bij een spreidingsgrafiek geef je ook de exacte positie op de x-as mee, zodat je precies kunt aangeven waar het resultaat terecht zal komen. Een spreidingsgrafiek wordt bijvoorbeeld gebruikt als op de x-as datums worden uitgezet, waarbij niet iedere datum in de bron-gegevens voorkomt. Bij een lijngrafiek worden de datums ‘gewoon’ naast elkaar geplaatst; bij een spreidingsgrafiek heeft iedere datum zijn eigen positie op de x-as en zie je onmiddellijk waar er datums ontbreken.

Klok4Het tekenen van de grote en de seconde-wijzer gaat op dezelfde manier. Bedenk daarbij dat het maximale aantal seconden voor de grote wijzer maar 3600 is (60 minuten) en voor de secondewijzer slechts 60.

Klok5Om onze klok nog verder aan te kleden, zetten we ook de uren er op: op dezelfde manier als voor de wijzers bepalen we de hoeken en daarmee de coördinaten. De lijnen die daardoor ontstaan maken we ‘onzichtbaar’ (bij Lijnkleur de optie Geen lijn aanvinken); wel geven we de lijnen een label mee, et voilà.

Nu nog even op F9 drukken (Herberekenen) en de tijd in cel C2 en dus op de klok wordt geactualiseerd.

Automatische klok

Om iedere keer op F9 te drukken wanneer we de tijd willen weten, is natuurlijk niet te bedoeling.
Daarom nog een stukje VBA er achteraan (opgeslagen in zogenaamde macro’s, vandaar dat het Voorbeeldbestand de extensie xlsm heeft):

  1. druk op Alt-F11 (openen van de Visual Basic-editor)
  2. kies Invoegen en dan Module
  3. in de module voegen we een globale variabele toe (die is dus voor het gehele project geldig), waarmee we onthouden of de klok moet lopen of niet:
    Global KlokAan As Boolean
  4. dan een kleine VBA-routine:
    Sub KlokActief()
        If KlokAan Then
            Application.OnTime Now + TimeValue(“00:00:01”), “KlokActief”
            Application.Calculate
        End If
    End Sub
    Wanneer deze routine aangeroepen wordt, wordt allereerst gecontroleerd of de variabele KlokAan Waar is (True). Zo ja dan wordt er voor gezorgd dat 1 seconde later dezelfde routine opnieuw wordt opgestart; daarna wordt de herberekening uitgevoerd.
  5. Om de klok te kunnen starten (en ook weer uit te zetten) voegen we nog een routine toe:
    Sub KlokAanUit()
        If KlokAan Then
            KlokAan = False
        Else
            KlokAan = True
            KlokActief
        End If
    End Sub
    Als de klok aan staat, wordt die uitgezet en anders gaat de klok aan en wordt de routine KlokActief aangeroepen (die er zelf voor zorgt, dat hij iedere seconde wordt uitgevoerd, zolang KlokAan Waar is).
  6. nog een button toevoegen, die verwijst naar de macro KlokAanUit en we zijn klaar:
    ga in Excel naar de menu-optie Ontwikkelaars (eventueel eerst nog activeren via Bestand/Opties/Lint aanpassen en daar rechts bij Hoofdtabbladen een vinkje aan zetten bij Ontwikkelaars) en kies in het blok Besturingselementen de optie Invoegen en kies het eerste symbool, de Knop.