Rosling

Ongetwijfeld 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).
Bij 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

Toen 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’.

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

1. selecteer 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.

In 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. maak een nieuw tabblad aan (bijvoorbeeld via  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. in het nieuwe scherm de gegevens voor de x- en y-as en de belgrootte invullen. De reeksnaam laten we leeg; heeft bij een belgrafiek weinig nut.
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
Om 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. klik 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.