Bijna 10 jaar geleden ben ik gestart als zelfstandige op het gebied van kleinschalige automatisering. Met veel plezier heb ik diverse kleine en grote projecten gedaan. Ook het geven van Excel-cursussen heeft veel voldoening en positieve energie opgeleverd. Op mijn website ginfo.nl zijn nu ongeveer 140 artikelen verschenen onder de kop Tips&Trucs. Dit vooral om mijn opgebouwde ervaring en kennis te delen.
Toen ik enkele jaren geleden besloot om mijn werkzame leven te beëindigen en meer tijd te pakken voor andere zaken ben ik wel doorgegaan met het schrijven van artikelen.
Maar na deze 10 jaar moet ik toch constateren dat de inspiratie op begint te raken. Het wordt steeds moeilijker om een nieuw onderdeel van Excel te vinden waarover een artikel geschreven kan worden. Daartegenover staat dat Microsoft (gelukkig) niet stil zit en Excel regelmatig van nieuwe, geweldige uitbreidingen heeft en zal blijven voorzien. Om het gebruik daarvan goed te kunnen belichten moet je dat zelf in de praktijk veel hebben toegepast. Aan deze ervaring ontbreekt het mij en daarom heb ik deze opties ook niet besproken; de enige uitzondering is Power Query.
Dit alles heeft erin geresulteerd dat ik besloten heb om te stoppen met het publiceren van nieuwe artikelen. Mijn website zal ik wel nog een poosje in de lucht houden, zodat iedereen oude (en misschien verouderde) artikelen kan nalezen en de voorbeelden in de downloads kan bestuderen. Dit zolang ik de doorlopende kosten van de website nog aanvaardbaar vind ;-).
NB hebben de artikelen geholpen bij uw werk, bij uw hobby of vond u ze interessant om te lezen en wilt u helpen om de website in de lucht te houden? Iedere bijdrage op NL10ASNB0707879337 t.n.v. G. Verbruggen is welkom (svp onder vermelding van Bijdrage G-Info).
Andere sites
In mijn laatste tip wil ik enkele links opnemen naar andere sites:
Naam
Omschrijving
Link
Chandoo
De Engelstalige website van mijn ‘goeroe’. Zijn doel: At Chandoo.org, my goal is simple. I want you to become awesome in Excel & Power BI.
Een Engelstalige website van Randy Austin. Allerlei toepassingen waarbij VBA een grote rol speelt. Gebruik de button Send me workbooks om iedere week een complete applicatie (!) te ontvangen met Youtube-uitleg.
Alle (on-line) bedrijven willen tegenwoordig van hun klanten graag een review ontvangen. Dit is niet alleen interessant voor de marketingafdeling maar ook voor nieuwe kopers. Ikzelf kijk tenminste ook altijd gauw door de lijst reviews om te zien wat anderen van het product vinden.
Meestal kun je een product waarderen met 1-5 sterren. De betrouwbaarheid van de gemiddelde score (zoals hierboven de 4,7) wordt natuurlijk steeds groter met het aantal reviews dat er is uitgebracht. Maar de kwaliteit van de score wordt ook bepaald door de spreiding van de uitgebrachte scores. Vandaar dat je meestal nog kunt doorklikken:
In dit artikel gaan we wat mogelijkheden binnen Excel langs waarmee we bij een review-overzicht snel en interactief op de details kunnen inzoomen (het idee en de cijfers zijn ontleend aan een artikel van Chandoo).
Basis-gegevens
In het Voorbeeldbestand in het tabblad Reviews ziet u een bestand met voor enkele top-deals van Bol.com een reviewer-code met de daarbij behorende FICTIEVE score.
Door middel van een draaitabel kunnen we snel een overzicht met gemiddeldes genereren:
Maar om makkelijk een overzicht van de onderliggende details te kunnen tonen gaan we een andere methode hanteren.
Samenvattend overzicht
In kolom C hebben we de namen van de aanbiedingen geplaatst, waar we de gemiddelde Rating van willen weten (zie het tabblad Ovz1 van het Voorbeeldbestand).
NB wil je snel alle items hebben, die in het basis-bestand voorkomen, dan liggen er 3 methodes voor de hand:
maak een draaitabel zoals hierboven en kopieer de namen naar de gewenste plaats
gebruik de optie Geavanceerd in het blok Sorteren en filteren van de menutab Gegevens: en vul bij Kopiëren naar de gewenste plaats in
kopieer de kolom met de aanbiedingen en kies dan de optie Dubbele waarden verwijderen in het blok Hulpmiddelen voor gegevens van de menutab Gegevens.
In cel D3 staat de formule =GEMIDDELDE.ALS(tblReviews[Aanbieding];C3;tblReviews[Rating]) Ofwel bepaal het Gemiddelde van alle Ratings als de naam in C3 gelijk is aan een naam in de kolom Aanbieding van de Excel-tabel tblReviews.
In kolom E wordt het gemiddelde grafisch weergegeven. Daarbij worden 3 symbolen uit het font Wingdings2 gebruikt: (zie het tabblad Berekeningen).
De cellen met de symbolen hebben respectievelijk de namen crkVol, crkHalf en crkLeeg gekregen.
Cel E3 bevat de formule: =HERHALING(crkVol;GEHEEL(D3+0,25)) &HERHALING(crkHalf;ALS(EN(REST(D3;1)>0,25;REST(D3;1)<0,75);1;0)) &HERHALING(crkLeeg;GEHEEL(5-(D3-0,25)))
Eerst wordt het aantal volle cirkels bepaald door het gehele deel van de waarde in D3 te nemen (door daar 0,25 bij op te tellen wordt een waarde van bijvoorbeeld 1,8 ook voorzien van 2 volle cirkels); de functie Herhaling genereert een tekst met het gewenste aantal volle cirkels.
Als de Rest van deling van D3 door 1 groter is dan 0,25 EN kleiner dan 0,75 dan zal de tweede Herhaling-functie een halfvolle cirkel opleveren, anders niets.
Als laatste wordt het aantal toe te voegen lege cirkels gegeneerd.
Door middel van het &-teken worden de drie teksten aan elkaar gekoppeld.
LET OP op deze manier wordt een tekst gemaakt die uit symbolen uit het font Wingdings2 bestaat. Om deze tekst in Excel zichtbaar te maken moeten de betreffende cellen in kolom E dan ook het lettertype Wingdings2 hebben.
Zoals hiervoor gezegd hebben we details over het aantal reviews en de spreiding in de scores nodig om de gemiddeldes op waarde te schatten.
Detail overzicht
In het tabblad Berekeningen van het Voorbeeldbestand vindt u een overzicht van de scores van een bepaalde aanbieding. Afhankelijk van de waarde in cel C2 (met de naam aanbNr) wordt in cel D2 (met de naam aanbNm) de naam van de betreffende aanbieding opgehaald door middel van de functie Index (lstAanbiedingen is de lijst producten in kolom C van het tabblad Ovz1).
Cellen B1:B9 bevatten de getallen 1 t/m 5; alleen hebben die een Aangepaste notatie gekregen: “Score “0 Selecteer daartoe de cellen, druk op Ctrl-1 (Celeigenschappen) en kies het tabblad Getal:
In cel C5 staat de formule: =AANTALLEN.ALS(tblReviews[Aanbieding];aanbNm;tblReviews[Rating];B5) Ofwel bepaal het aantal als de waarde in de kolom Aanbieding van de Excel-tabel tblReviews gelijk is aan aanbNm en de waarde in Rating gelijk is aan de waarde in B5 (het woord Score daar is opmaak!). Cel C5 heeft ook weer een Aangepaste notatie(0) gekregen.
In cel C10 wordt de som bepaald van de cellen daarboven met als Aangepaste notatie0 “reviews”. Dit levert het totaal aantal reviews voor de betreffende aanbieding. Dit totaal wordt in de cellen D5:D9 overgenomen. Dit hebben we nodig voor het maken van de bijbehorende grafiek.
Detail grafiek
Van het detail-overzicht kunnen we nu makkelijk een grafiek maken:
selecteer de cellen B4:D9 in het tabblad Berekeningen
kies in de menutab Invoegen in het blok Grafieken de optie Kolom- of staafdiagram
kies het eerste staafdiagram (Gegroepeerde staaf)
We hebben op deze manier een grafische weergave van de aantallen per score vergeleken met het totaal aantal reviews.
Deze grafiek is gekopieerd naar het tabblad Ovz1; zoals te zien is doet wat opmaak wonderen:
verwijder de horizontale as, de gridlijnen en de legenda
klik op Grafiektitel, plaats de cursor in de Formulebalk, tik in = en klik met de muis op cel C10 van het tabblad Berekeningen
klik ergens in de grafiek met de rechter muisknop en kies Gegevens selecteren. Verplaats Aantal naar beneden
klik rechts op één van de staven en kies de optie Gegevensreeks opmaken. Zet de Overlapping van reeks op 100%.
klik rechts op één van de scores en kies As opmaken. Zet het vinkje aan bij Categorieën in omgekeerde volgorde.
klik rechts op één van de grote staven en kies de optie Gegevenslabels toevoegen. Klik rechts op één van de labels en kies de optie Gegevenslabels opmaken. Kies de optie Waarde uit cellen en selecteer de cellen C5:C9. Vink de optie Waarde uit.
pas de overige opmaak naar wens aan.
Interactief details tonen (1)
Nu moeten we nog een methode vinden om vanuit het review-overzicht de detail-grafiek te tonen. Op het tabblad Ovz1 van het Voorbeeldbestand hebben we gebruik gemaakt van Keuzerondjes:
kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen en kies daar de optie Keuzerondje (Formulierbesturingselement). NB zie je geen menutab Ontwikkelaars? Klik ergens in hetmenugedeelte met de rechter muisknop en kies de optie Het lint aanpassen. Zorg dan dat in het rechterblok de optie Ontwikkelaars is aangevinkt.
klik in de cel waar je het eerste rondje wilt hebben. Verschuif het rondje eventueel nog zodat hij precies op de gewenste plaats staat.
pas de standaard-tekst Keuzerondje 1 aan; in dit geval wissen we de tekst helemaal.
klik rechts op het rondje en kies de optie Besturingselement opmaken. Zorg dat bij Koppeling met cel een verwijzing naar de cel Berekeningen!$C$2 komt.
klik rechts op het rondje en kies Kopiëren
toets 8 keer Ctrl-V (plakken) in
plaats het onderste rondje op de juiste plaats
selecteer alle rondjes door er op te klikken met Ctrl ingedrukt
kies in de menutab Hulpmiddelen voor tekenen/Opmaak in het blok Rangschikken de optie Uitlijnen en kies daarna Links uitlijnen; daarna nogmaals Uitlijnen met de optie Verticaal verdelen.
Wanneer je nu een rondje aanklikt zal het overeenkomende nummer in cel C2 van het tabblad Berekeningen worden geplaatst. Het detail-overzicht en de bijbehorende grafiek(en) passen zich dan automatisch aan.
NB aan de cellen C3:E11 is een voorwaardelijke opmaak gekoppeld die er voor zorgt dat de achtergrond van de gekozen aanbieding wordt gekleurd:
Interactief details tonen (2)
Een andere methode is in het tabblad Ovz2 van het Voorbeeldbestand geïmplementeerd.
Als je daar op één van de cellen C3:E11 klikt dan start een VBA-routine die er voor zorgt dat cel C2 (met de naam aanbNr) van het tabblad Berekeningen wordt aangepast.
klik met de rechter muisknop op de naam van één van de tabbladen en kies de optie Programmacode weergeven
klik in het Project-overzicht aan de linkerkant van het scherm dubbel op het blad Ovz2
plaats in het codescherm de volgende programma-regels: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim topRij As Integer If Not (Application.Intersect(Target, Range(“lstOvz2”).Cells) Is Nothing) Then topRij = Range(“lstOvz2”).Cells(1, 1).Row [aanbNr] = Target.Row() – topRij + 1 End If End Sub Kortweg: als in dit werkblad de selectie wordt veranderd (door bijvoorbeeld het klikken met de muis of het gebruik van de cursortoetsen) dan zal de VBA-routine kijken of er een overlap is tussen de cel die is geselecteerd (de Target) en de cellen van lstOvz2 (dus C3:E11). Is dat zo dan wordt het rijnummer van de eerste cel van deze lijst in de variabele topRij geplaatst. Door deze topRij af te trekken van het rijnummer van de Target-cel weten we welke aanbieding aangeklikt is (nog wel even corrigeren met +1).
LET OP: na het downloaden de extensie wijzigen in xlsb
De oudere jongeren onder ons (of de jongere ouderen?) weten het nog wel: in 1972 (50 jaar geleden) verscheen het Rapport van de Club van Rome met als ondertitel De grenzen aan de groei.
Een pocketboekje dat een intensieve discussie op gang heeft gebracht: voor sommige mensen was het een eye-opener (we kunnen niet blijven doorgaan met het ongelimiteerd opsouperen van onze hulpbronnen, we moeten ‘de groei’ temperen), anderen wezen er op dat je met het onderliggende model alles kunt bewijzen (een Eindhovense professor formuleerde dat als ‘Met dit model kun je ook je eigen handtekening maken; een kwestie van de parameters aanpassen aan je wensen‘).
In het vorige artikel van G-Info is een voorbeeld uit het rapport langs gekomen om te laten zien hoe je Vergelijkingen/formules in Excel kunt schrijven. Een losse opmerking daarbij (‘Zou het model in Excel nagebouwd kunnen worden?“) is het begin van een zoektocht geworden. Gelukkig kwam daarbij al snel een vereenvoudigd model naar boven. Het begin van een uitdaging: kan dit model in Excel op een zodanige manier geïmplementeerd worden, dat in ieder geval de resultaten van de Club van Rome gereproduceerd worden?
In dit artikel eerst een korte achtergrond van het Rapport van de Club van Rome, een uitleg van het vereenvoudigde model en daarna wat vingeroefeningen om te laten zien hoe we denken dat de implementatie er uit kan gaan zien.
Rapport van de Club van Rome
Wikipedia: “De grenzen aan de groei is een rapport van de Club van Rome uit 1972 waarin de uitputtingsproblematiek centraal staat. Het rapport werd uitgewerkt door een team van het Massachusetts Institute of Technology (MIT) onder leiding van Dennis Meadows en Donella Meadows. Het rapport heeft grote invloed gehad op het milieubewustzijn.
Aan de basis van de studie ligt het gebruik van een systeemdynamisch model met computersimulatie van interacties tussen bevolking, industriële groei, voedselproductie en limieten in de ecosystemen van de aarde: het World3-model, mede ontwikkeld door Jay Forrester. Van deze variabelen werd de ontwikkeling van 1900 tot 1970 vastgesteld. Vervolgens werden de trends voortgezet, waarbij verschillende aannames werden gedaan. Ervan uitgaande dat geen belangrijke veranderingen plaats zouden vinden in de fysieke, economische en sociale relaties (het referentie scenario) waren de uitkomsten schokkend. De natuurlijke hulpbronnen zouden gaandeweg uitgeput raken en de industriële groei remmen. De bevolkingsomvang en vervuiling zouden nog enige tijd toenemen, maar de verslechtering van de voedselvoorziening en de gezondheidszorg leidden in eerste instantie tot stilstand en later tot terugloop in de bevolkingsgroei.“
Het rapport was niet zozeer bedoeld om kwantitatieve voorspellingen over de toekomst te doen (de leden van de Club en MIT’ers beseften terdege dat het model daarvoor veel te globaal en simpel was). Het diende als input voor de discussie over de groei van de wereldbevolking, ons consumptiepatroon en het gebruik van de natuurlijke grondstoffen en het effect van milieuvervuiling.
Het model is doorgerekend met diverse scenario’s. Hiernaast staat een grafische weergave van het resultaat van het standaard BAU-scenario (Bussiness As Usual). Daarin volgen alle variabelen van 1900 tot 1970 de historische waarden. De rest is door het model berekend op basis van de aanname dat “er geen belangrijke veranderingen plaatsvinden in de fysieke, economische of sociale relaties.“
De grafieken werden weergegeven door letters, waarna de belangrijkste variabelen met de hand werden ingetekend; B stelt het geboortecijfer voor, D het sterftecijfer en S de diensten per hoofd. Uit het rapport: “Elk van de variabelen is uitgezet op een verschillende schaalverdeling. We hebben met opzet de verticale schaalverdelingen weggelaten en de horizontale tijdas geen indeling gegeven, omdat we de nadruk willen leggen op de algemene gedragspatronen, niet op de numerieke waarden die slechts onnauwkeurig bekend zijn. Maar de schalen zijn in alle scenario’s gelijk, zodat de grafieken gemakkelijk vergeleken kunnen worden.“
Iedereen die geïnteresseerd is in de resultaten van de scenario’s moeten we doorverwijzen naar diverse publicaties. De Nederlandstalige versie van het rapport is nog te koop, de Engelstalige versie is in PDF-versie te downloaden.
Het idee om eens te kijken of we met Excel het BAU-scenario zouden kunnen reproduceren, leek bij bestudering van deze info niet haalbaar. Totdat ….
Een vereenvoudigd model
Bij de zoektocht op internet kwam ik een studie tegen waarin de resultaten van het oorspronkelijke model vergeleken werden met recente data. Ook is daar een vereenvoudigd model te vinden.
Dit model bestaat uit 25 variabelen. Dat moet te doen zijn en ook het aantal verbanden daartussen lijkt behapbaar.
Misschien dat het toch gaat lukken om de resultaten uit 1972 te reproduceren! En dat dan niet alleen: misschien kunnen we de diverse parameters in het model zodanig aanpassen, dat we weten hoe we de wereld de juiste kant op kunnen sturen 😉
Op het tabblad SystemDynamics van het Voorbeeldbestand ziet u een aangepaste vorm van dit vereenvoudigde model:
Bovenstaand model is in Excel gemaakt met 2 soorten vormen: Ovalen en Gekromde pijlen.
Kies in de menutab Invoegen in het blok Illustraties de optie Vormen.
Selecteer de gewenste vorm en ’teken’ met de muis ongeveer op de plaats waar deze moet komen.
Pas in de menutab Hulpmiddelen voor tekenen de Opmaak aan.
De ovalen zijn gemakkelijk groter en kleiner te maken door middel van de rondjes aan de zijkanten. Bovenin zit een greep waarmee de vorm gedraaid kan worden.
De plaats en de vorm van de pijlen kunnen via de 3 bolletjes aangepast worden. Zorg wel dat de uiteinden van de pijl precies op één van de 8 rondjes van een ovaal terecht komen (het resultaat is dan een dicht zwart bolletje). Wanneer je achteraf een vorm verschuift zal de pijl meebewegen.
Je kunt een tekst in een ovaal plaatsen door daarin te dubbel-klikken en dan de tekst te tikken. Om de consistentie te bewaken hebben we alle codes en namen in een apart tabblad Beschr vastgelegd. Door nu eenmaal in/op een ovaal te klikken kan in de formulebalk een verwijzing naar een cel in dit tabblad gemaakt worden. De cellen in kolom D worden gebruikt in het model; afhankelijk van de grootte van de tekst plaatsen we tussen de Code en de Naam een spatie of een harde return (druk tussen de aanhalingstekens op Alt-Enter). Alle cellen in kolom D hebben een overeenkomende naam gekregen (bijvoorbeeld cel D4 heeft de naam KF1_; de underscore is nodig omdat Excel anders denkt dat we een verwijzing naar de cel in kolom KF en rij 1 bedoelen).
NB heb je een paar vormen (inclusief opmaak) die voldoen, dan kun je die natuurlijk ook kopiëren. De tekst (en misschien de grootte) aanpassen en je bent klaar.
Variabelen
We onderkennen in het model 3 soorten variabelen: de Inputs, de Kritische Factoren en de Tussen-variabelen. De Input-variabelen worden niet beïnvloed door de omgeving, maar kunnen wel in de loop van de jaren variëren. De KF’s zijn die 5 variabelen die in alle grafieken van het rapport terugkomen. Alle overige hebben de naam Tussen-variabelen gekregen.
Verbanden tussen variabelen
De pijlen in het model geven het verband tussen de diverse variabelen aan. De richting en kleur laten de soort beïnvloeding zien.
1. Lineair of absoluut verband
Tussen sommige variabelen zit een lineair/absoluut verband: variabelen worden bij elkaar opgeteld of op elkaar gedeeld om de waarde van een andere variabele te berekenen.
Bijvoorbeeld, voor de bevolkingsomvang gebruiken we de volgende formule: KF1t=KF1t-1 + T1t -T2t De Bevolkingsgrootte KF1 in jaar t is de grootte in jaar t-1plus de Geboortes T1 in jaar tminus de Sterftes T2 in jaar t. In het model gaat er een groene pijl van T1 naar KF1, een rode van T2 naar KF1.
Een ander absoluut verband zien we bij KF4: KF4t=T8t/KF1t-1. Het Voedsel per hoofd KF4 in jaar t is gelijk aan de Hoeveelheid voedsel T8 in jaar t gedeeld door de Bevolkingsgrootte KF1 in jaar t-1. Hoe groter T8 hoe groter KF4 (dus een groene pijl in het model), hoe groter KF1 hoe kleiner KF4 (een rode pijl).
NB we kiezen er voor om te delen door de bevolkingsgrootte in het jaar t-1, omdat we anders het risico lopen op een kringverwijzing: KF4 heeft invloed op de sterftekans T4 en die bepaalt weer de bevolkingsgrootte.
2. Relatief verband
Maar de meeste pijlen in het model vertegenwoordigen een ingewikkelder verband tussen de variabelen. We kunnen bijvoorbeeld niet zeggen dat we de hoeveelheid gezondheidszorg ergens van af trekken om tot een sterftekans te komen.
Maar het is wel aannemelijk dat als de gezondheidszorg van jaar op jaar toeneemt dat dan de sterftekans afneemt (los van andere variabelen). In formulevorm: ofwel
NB1 Hoe sterk de invloed van de gezondheidszorg op de sterftekans is, wordt door α bepaald.
NB2 hadden we te maken met een positieve invloed (een groene pijl) dan hadden we de teller en noemer bij T5 omgewisseld.
LET OP We zullen hierna zien dat deze vorm meestal nog te eenvoudig is om het verband tussen variabelen goed te modelleren.
Zoals het er nu uitziet kunnen we het model op basis van deze 2 soorten verbanden gaan beschrijven. In een volgend artikel zal kolom F in het tabblad Beschr van het Voorbeeldbestand gevuld worden met alle gebruikte rekenregels.
Exponentiële groei
Eén van de belangrijkste oorzaken voor de schokkende resultaten van de MIT-studie is gelegen in het feit dat in onze wereld (in ieder geval in het gehanteerde wereld-model) diverse variabelen de neiging hebben tot een exponentiële groei. De belangrijkste daarvan is de bevolking. Hoe dat komt zullen we hierna bekijken.
Waarschijnlijk de bekendste vorm van exponentiële groei heeft te maken met onze financiën.
Stel we beginnen met € 100; wanneer we jaarlijks 5% rente krijgen (dat was ooit!) dan hoeven we niet 20 jaar te wachten tot het bedrag verdubbeld is, maar slechts 14 jaar. Dit door het effect van rente op rente. Wacht je dan nog eens 14 jaar dan heb je al 4 keer zoveel.
Op het tabblad ExpGroei van het Voorbeeldbestand kun je met het percentage ‘spelen’ om te zien wat het effect daarvan is. De bijbehorende grafiek past zich automatisch aan.
In 1970 was de groeivoet van de wereldbevolking 2,1%. Dit zou een verdubbeling betekenen na 33 jaar. In de grafiek op het tabblad ExpGroei staan de werkelijke groei en de groei met 2,1% vanaf 1970 naast elkaar. Daar valt uit af te leiden dat de groeivoet is gedaald in de loop van de tijd. De consequentie daarvan is dat de verdubbeling niet heeft plaats gevonden in 2003 maar ‘pas’ in 2013.
Bebouwbare grond
Eén van de vele consequenties van de exponentiële groei van de bevolking zien we terug bij de verwachting van de beschikbaarheid van voldoende landbouwgrond.
Volgens de Club van Rome was er in 1970 3,2 miljard ha grond beschikbaar voor landbouw. De verwachting was ook dat dat in de toekomst niet significant zou toenemen; dat zou economisch niet rendabel zijn. Op dat moment was er wereldwijd per persoon 0,4 ha nodig om voldoende voedsel te kunnen verbouwen (ter illustratie: in de US werd er toen 0,9 ha pp gebruikt). In het rapport is er ook rekening mee gehouden dat er per persoon 0,08 ha van de beschikbare bouwgrond nodig was voor bewoning en andere infrastructuur.
In het tabblad Bebouwbaar van het Voorbeeldbestand is in de grafiek te zien dat er lang (ruim) voldoende grond was om voedsel te verbouwen. Maar door de exponentiële groei van de bevolking stijgt de benodigde hoeveelheid grond na 1970 snel, terwijl de beschikbare hoeveelheid vanaf dat moment versneld gaat afnemen. De 2 lijnen snijden elkaar ongeveer in het jaar 2000.
Gelukkig is die ‘voorspelling’ niet bewaarheid. Waarschijnlijk door een efficiënter gebruik van de grond en de lagere groei van de bevolking. In het tabblad Bebouwbaar kun je de productiviteit aanpassen. Hiernaast staat de grafiek bij een productiviteitsfactor van 2; ofwel er is maar 0,2 ha pp nodig. De 2 lijnen snijden elkaar nu pas in het jaar 2025.
Het mag duidelijk zijn dat een verdere verhoging van de productiviteit en/of verlaging van de groeivoet van de bevolking slechts uitstel betekent tot er niet voldoende landbouwgrond meer is. Gemiddeld over de wereld gaat het nu nog goed, maar mensen in Afrika kijken daar waarschijnlijk al anders tegen aan. Ook de oorlog in Oekraïne laat ons zien, dat een (relatief kleine) verstoring van de normale wereldorde een groot effect op onze voedselvoorziening tot gevolg heeft.
Bevolkingsgroei
In het model, dat we hier hanteren, wordt de grootte van de bevolking alleen bepaald door het aantal geboortes (T1) en doden (T2) per jaar. Zoals hiernaast te zien is bepaalt de grootte van de bevolking (KF1) echter ook weer de aantallen van T1 en T2. Dergelijke terugkoppelingen zien we meer terug in het model en deze zorgen vaak voor het exponentiele karakter van groei.
In het tabblad TerugKoppeling van het Voorbeeldbestand is dit te zien aan de hand van fictieve cijfers.
Begin 2011 kende de wereld ongeveer 7 miljard inwoners. Het aantal geboortes per 1000 personen per jaar (Geboortecijfer T3) was toen ongeveer 19,1, terwijl het aantal doden circa 8,1 per 1000 was (Sterftekans T4); de groeivoet van de bevolking per jaar was dus ongeveer 11 per 1000 ofwel 1,1%. Onder de aanname dat T3 en T4 niet veranderen kunnen we gemakkelijk het verloop van de bevolking over de jaren berekenen.
Het verloop is voor 40 jaren berekend en ook in een grafiek uitgezet. We hebben Excel 2 trendlijnen laten bepalen: een lineaire en een exponentiële. De lineaire voldoet met een R2 van 0,9968 (zie het artikel Trend-analyse) prima op het getekende stuk, maar wel is te zien dat als we deze trend zouden gebruiken om te voorspellen dat we al snel uit de pas zouden lopen. De andere trendlijn heeft een R2 van 1 en sluit dus exact aan bij de brongegevens (de trendlijn valt samen met de grafiek zelf). De bevolkingsgroei is dus exponentieel. Op het tabblad ExpGroei kunnen we zien dat een groeivoet van 1,1% betekent dat de bevolking iedere 60 jaar zal verdubbelen.
Vingeroefening 1
We gaan als eerste eens kijken hoe we de bepaling van het Geboortecijfer T3 kunnen modelleren (zie het tabblad GebCijfer in het Voorbeeldbestand). Zoals te zien is in het schema wordt T3 beïnvloed door de variabelen KF2 (Industriële productie per hoofd) en T6 (opleiding, gezinsplanning). Bij allebei staat een rode pijl. Dat betekent dat een grotere waarde voor KF2 en/of T6 er voor zorgt dat T3 kleiner wordt (in het Club-rapport wordt de achtergrond hiervan kort uitgelegd).
We hebben hier te maken met een relatief verband; hierboven staat de daarbij behorende formule. Maar daar zitten wel wat haken en ogen aan:
de formule bevat één α, waarmee we de gevoeligheid van T3 voor veranderingen in KF2 en T6 kunnen regelen. Maar de gevoeligheid per variabele kan verschillend zijn; dat zouden we zichtbaar willen hebben.
als het gemiddeld opleidingsniveau dit jaar is gestegen ten opzichte van vorig jaar, zal dat niet direct al dit jaar een verandering in T3 geven; daar zit natuurlijk een vertraging in.
een eenmalige grote wijziging in bijvoorbeeld KF2 hoeft niet ook een dergelijk effect te hebben op T3. Het is beter als we een langere periode dan 1 jaar hanteren om de gemiddelde relatieve wijziging te bepalen.
Wanneer we met deze 3 punten rekening houden wordt de formule ‘iets’ ingewikkelder:
iedere variabele heeft zijn eigen gevoeligheidsfactor GF. Omdat er in het model straks veel van dit soort factoren zijn, geven we iedere factor een aanduiding mee op welk verband deze betrekking heeft. Bijvoorbeeld GFKF2,T3 is de gevoeligheid van het verband tussen KF2 en T3. Om decimalen bij de invoer te vermijden schalen we de GF’s tussen 0 en 100. NB als het resultaat van een verband heel sterk wordt beïnvloed door de bron-variabele kan de GF ook groter dan 100 zijn.
de vertraging in het effect wordt door de V-variabelen in de formule bepaald
en de periode door de P’s. NB we bepalen op bovenstaande manier de gemiddelde wijziging tussen het begin en einde van de periode. Voorlopig lijkt dit een goede oplossing, maar misschien blijkt het straks nodig om een gemiddelde over alle wijzigingen in de periode te nemen. Of als het verloop in de periode sterk exponentieel is een nog wat ingewikkelder methode.
Om wat te kunnen experimenteren staat op het tabblad GebCijfer een tabel met fictieve cijfers over de jaren 1950-1975. De cijfers over 1950 zijn ‘hard’. Met de formule =G4*(1+(ASELECTTUSSEN(0;3)/100-1%)) in cel G5 zorgen we dat KF21951met een waarde tussen -1% en +2% wijzigt ten opzichte van 1950. Op dezelfde manier worden alle cellen in de kolommen G en H met willekeurige waarden gevuld. De waardes voor T3 zijn voor de jaren tot en met 1964 ‘hard’ ingevuld. De niet-harde cellen worden telkens opnieuw berekend wanneer op F9 wordt gedrukt.
In cel I19 staat het eerste resultaat van de berekening volgens bovenstaande systematiek:
de inhoud van cel I18 is de T3 van het vorige jaar
C41 is de waarde van GFKF2,T3
C39 is de VKF2,T3
en C40 is de PKF2,T3
De functie Verschuiving selecteert op basis van (in dit geval) 3 parameters een cel:
de eerste parameter is de start-positie van de selectie; hier de cel in de kolom met de naam T6, die in dezelfde regel staat als de formule ([T6] is de hele tabel-kolom, [@T6] alleen de cel in dezelfde regel).
de tweede is het aantal rijen naar beneden of naar boven voor de daadwerkelijke selectie
en de derde geeft aan of de selectie naar links of rechts ten opzichte van de start-positie moet plaats vinden
Op het tabblad GebCijfer staat naast de tabel met random-waarden ook een tabel waarin alle waarden in de kolommen KF2 en T6 vast zijn. Op deze manier kun je beter zien wat de consequenties van aanpassingen van de GF-, V– en P-parameters zijn voor het resultaat. De grafieken laten het effect goed zien. Het beoordelen van het resultaat voor wijzigingen in alleen KF2 of T6 kan eenvoudig door de andere GF op 0 in te stellen.
Vingeroefening 2
Het tabblad SterfteKans van het Voorbeeldbestand bevat een ander gedeelte van het vereenvoudigde model. We zien hier dat de Sterftekans T4 door 3 variabelen wordt beïnvloed: T5 Gezondheidszorg, KF4 Voedsel per hoofd en KF3 Vervuiling.
We zien 2 rode en 1 groene pijl: als T5 en/of KF4 stijgen zal de sterftekans dalen, maar wanneer de vervuiling toeneemt, neemt ook de T4 toe.
De verbanden tussen de variabelen T5-T4 en KF3-T4 kunnen we modelleren als in de vorige vingeroefening. De relatie tussen KF4 en T4 is ingewikkelder. Wanneer de hoeveelheid voedsel per hoofd blijft stijgen zal dat geen verdere daling van de sterftekans met zich meebrengen (misschien zelfs integendeel).
In het Club-rapport is wel een verband gevonden tussen het voedingsniveau (uitgedrukt in groente calorie-equivalenten) en de gemiddelde verwachte levensduur (situatie 1953). Een trendanalyse laat zien, dat dit verband zich goed laat benaderen door een 4e graadsfunctie (tenminste op het relevante stuk met een voedingsniveau tussen 3 en 12). Deze functie zullen we hierna SK_4 noemen.
De formule (zie het tabblad StefteKans) wordt er niet simpeler op! In het tweede blok gebruiken we dus niet de verhouding tussen twee waardes van KF4, maar de verhouding tussen twee uitkomsten van SK_4(KF4*). NB we moeten straks bij de implementatie van het model de waardes van KF4 schalen naar een waarde tussen 3 en 12.
In een tabel op het tabblad SterfteKans hebben we de gegevens uit het Club-rapport overgenomen. In kolom D staat de berekening volgens de 4e graadsfunctie; de verschillen zijn marginaal. Onder de tabel zijn de 5 benodigde parameters voor de functie opgenomen; de cellen hebben overeenstemmende namen gekregen.
Deze namen gebruiken we in de eigen functie SK_4; deze functie zullen we straks in de modelberekeningen gebruiken in plaats van formules met cel-verwijzingen. NB1 voor uitleg over eigen functies, zie het betreffende artikel. NB2 de functie bevat ook een underscore, omdat Excel anders denkt dat het een verwijzing is naar de cel in kolom SK en rij 4.
Ook nu hebben we een overzicht gemaakt met fictieve gegevens. Door met de diverse parameters te spelen krijg je gevoel voor de samenhang tussen de diverse variabelen. De bijbehorende grafiek ondersteunt daarbij.
Het volgende artikel van G-Info zal gewijd zijn aan de implementatie van het vereenvoudigde model. Daar liggen wel wat uitdagingen; technisch maar zeker ook bij het vullen van de diverse parameters. Er zullen heel wat aannames gedaan moeten worden. En of de resultaten van het model dan lijken op de uitkomsten van de Club van Rome? We zullen het zien.
LET OP: na het downloaden de extensievan het bestand wijzigen in xlsb
De Excel-functie Indirect heb ik de afgelopen tijd weer in menig artikel en project gebruikt.
In de praktijk blijkt het lang niet voor iedereen (direct) helder wat de functie doet en waar die dus voor ingezet kan worden.
Aanleiding voor een artikel; inclusief wat VBA-programmering om het voorbeeld uit het vorige artikel gebruikersvriendelijker te maken.
In 2015 heb ik al eens over deze functie geschreven. Deze keer kiezen we een andere invalshoek voor de uitleg en komen andere facetten van de functie aan bod. Zoals het spreekwoord luidt: dubbel genaaid houdt beter!
Indirect (versie 1)
Op het tabblad Cel van het Voorbeeldbestand is cel B2 gevuld met een tekst die aangeeft welke dag het vandaag is: =”Vandaag is het “&TEKST(VANDAAG();”d-mm-jjjj”)
Cel B4 bevat de tekst B2 en in cel D4 staat de verwijzing naar cel B4: =B4
Het resultaat in cel D4 is uiteraard de inhoud van cel B4, dus de tekst B2.
In cel D5 staat de formule =INDIRECT(B4)
Het resultaat is geen directe verwijzing naar cel B4, maar naar de inhoud van cel B4, dus naar cel B2. Een Indirecte verwijzing dus, vandaar de naam van de functie.
Simpel toch!
Nog een (iets ‘intelligenter’) voorbeeld: in kolom B staan 5 namen. In cel E11 staat een indirecte verwijzing naar cel B12, waardoor de derde naam als resultaat wordt weergegeven. De tussenstap (cel E10) is enigszins dynamisch gemaakt: =”B”&D11+9 Dus aan de tekst B wordt het resultaat van de optelling van de inhoud van cel D11 en het getal 9, gekoppeld.
Wijzigen we nu cel D11 dan zal ook het resultaat in cel E11 veranderen. Maar echt fool-proof is deze constructie niet: voeg maar eens een regel in boven regel 10.
In cel F10 is het statische getal 9 vervangen door het dynamische RIJ(B10)-1.
NB voeg je vóór kolom B een kolom in, dan zal deze formule ook niet meer goed werken. Wil je dit voorkomen dan moet de tekst B in de formule ook dynamisch gemaakt worden.
Indirect (versie 2)
De tussenstap in het vorige voorbeeld (in cel E10 wordt een verwijzing naar een andere cel geconstrueerd) kunnen we overslaan: de parameter die aan de functie Indirect wordt meegegeven kan ook een tekst zijn.
In het tabblad Tekst van het Voorbeeldbestand bevat cel D6 de formule =INDIRECT(“B4”). Het resultaat is de inhoud van cel B4. De functie zorgt nu voor een indirecte verwijzing door een tekst om te zetten naar een bereik/range en daar de inhoud van op te halen. De tekst moet het A1-formaat hebben, één of twee letters voor de kolom- en een getal voor de rij-aanduiding.
Deze tekst-versie kent nog een andere variant: cel D7 bevat de formule =INDIRECT(“R4K2”;ONWAAR) en geeft hetzelfde resultaat als de formule in D6. Hierbij wordt het zogenaamde R1K1-formaat voor de tekst gebruikt: eerst een getal voor de rij en dan een getal voor de kolom. Wel moet aan de functie een tweede parameter meegegeven worden met de waarde ONWAAR.
NB de tweede parameter is niet verplicht als je het A1-formaat gebruikt. Maar deze mag wel altijd ingevuld worden; heeft de tekst het A1-formaat dan moet deze tweede parameter de waarde WAAR hebben.
LET OP wanneer deze spreadsheet in een andere taal-versie van Excel wordt ingeladen dan kan het zijn dat de formule niet goed werkt. Om het bijvoorbeeld in de Engelstalige versie te laten werken moet de formule in cel D7 gewijzigd worden in =INDIRECT(“R4C2”;ONWAAR)
In de cellen G3 en G11 worden de twee varianten nog eens in een ander voorbeeld gebruikt:
De kracht van de tekst-versie van de Indirect-functie ligt er uiteraard in, dat de tekst op allerlei manieren geconstrueerd kan worden (zoals in de vorige voorbeelden in eenvoudige vorm wordt getoond).
Cel G12 bevat de formule =INDIRECT(TEKST(G9*10+G10;”R0K0″);ONWAAR). Op deze manier verwijzen we ook naar cel F4. Daarbij is gebruik gemaakt van de functie Tekst waarmee we de weergave van een getal kunnen bepalen. Door de rij-waarde te vermenigvuldigen met 10 en daar de kolomwaarde bij op te tellen krijgen we (in dit geval) de waarde 46. Met de notatie-aanduiding R0K0 geven we aan dat het laatste cijfer van het getal 46 achter de K moet komen en de overige cijfers tussen de R en de K .
Dit gaat echter fout, als we een verwijzing maken naar een kolomwaarde groter dan 9 (probeer maar eens door in cel G10 de waarde 10 in te voeren).
In cel G13 is dit opgelost: =INDIRECT(TEKST(G9*100+G10;”R0K00″);ONWAAR)
Het gebruik van een harde tekst (dus geen samengestelde tekst zoals hiervoor) als parameter kan bijvoorbeeld handig zijn om te testen of een bepaalde cel niet is verplaatst (al dan niet door het invoegen of verwijderen van rijen of kolommen).
In cel G15 wordt getest of cel F17 wel of niet de waarde Maand bevat. Zo niet dan krijg je een waarschuwing.
Uiteraard hebben we dat met voorwaardelijke opmaak nog wat duidelijker gemaakt (groen is de standaard-kleur, rood als de inhoud afwijkt van de waarde “Ok”).
Gegevensvalidatie 1
In het Voorbeeldbestand vindt u in het tabblad GegVal1 een ‘systeem’ waarmee we de maximumsnelheid van een vervoermiddel kunnen ophalen uit een tabel. Daarbij is gebruik gemaakt van een zogenaamde afhankelijke Gegevensvalidatie (zie het vorige artikel). De keuzelijst in cel F3 is afhankelijk van de keuze in cel F2.
In cel F4 wordt de maximale snelheid van het gekozen merk opgehaald met de functie Vert.Zoeken. Een (flexibeler) alternatief staat in cel F5: =INDEX(C9:C15;VERGELIJKEN(F3;B9:B15;0))
Maar dat ophalen kan ook met behulp van de functie Indirect. Zie cel F6: =INDIRECT(“C”&RIJ(B8)+VERGELIJKEN(F3;B9:B15;0))
NB1 door niet hard te verwijzen naar rij 8, maar de functie RIJ(B8) te gebruiken werkt de formule ook nog als er regels worden toegevoegd of verwijderd.
NB2 als je rijen toevoegt of verwijdert (of kolommen) dan krijg je een VBA-foutmelding. Kies de optie Beëindigen. De reden van deze foutmelding zullen we hierna uitleggen (en wat daar aan te doen!)
Zoals in het vorige artikel aangegeven zou het mooi zijn als het systeem automatisch het Merk leeg maakt als je het type Vervoermiddel wijzigt.
Daarom hebben we aan het tabblad GegVal1 een VBA-routine gekoppeld (klik rechts op het betreffende tabje onderaan en kies de optie Programmacode weergeven):
Iedere keer, dat er iets is gewijzigd in dit tabblad, wordt deze routine automatisch aangeroepen (Worksheet_Change). Welke cel of cellen er zijn gewijzigd wordt door Excel meegegeven in de parameter Target. We willen alleen maar iets doen (namelijk het merk leeg maken) als het type vervoermiddel wijzigt (cel F2). Daarom wordt in de eerste regel van de sub-routine gecontroleerd of Target daar gelijk aan is. Als dat niet zo is, gaat de routine verder met de opdracht na de laatste End If (de subroutine beëindigen dus). De variabele VervMiddel bevat de inhoud van cel F2, zoals die was toen de routine vorige keer is aangeroepen. Daarom kijken we of die veranderd is. Is de huidige inhoud niet gelijk aan VervMiddel dan wordt cel F3 leeg gemaakt en maken we de variabele VervMiddel gelijk aan de inhoud van F2.
NB1 ik weet het: de routine is niet helemaal netjes geprogrammeerd. Wanneer je de eerste keer cel F2 wijzigt weet het ‘systeem’ helemaal niet wat er vorige keer in F2 stond. Dus F3 zal dan altijd leeg gemaakt worden.
NB2 de declaratie van de variabele VervMiddel gebeurt buiten de routine. Zouden we dat niet doen dan wordt deze variabele bij iedere Change opnieuw gedeclareerd en is dan dus altijd leeg.
NB3 deze Excel-werkmap bevat VBA. Bij het opslaan van het bestand moet je dan kiezen voor de extensie xlsm of xlsb.
NB4 verschuiven de cellen F2 en F3 dan moet de VBA aangepast worden. In cel I2 is daarom een controle met een waarschuwing ingebouwd (voeg maar eens een kolom in vóór kolom B).
Maar wat gebeurt er als je een rij of kolom toevoegt of verwijdert? Uiteraard wordt de routine Worksheet_Change aangeroepen, maar de parameter Target bevat helemaal niets. VBA wil dit ‘niets’ vergelijken met een Range en dat kan niet; vandaar dat we een foutmelding krijgen. Dat moeten we natuurlijk wel oplossen.
Gegevensvalidatie 2
In het tabblad GegVal2 van het Voorbeeldbestand staat een simpelere versie van het vorige voorbeeld. In de VBA-routine zit een belangrijk verschil:
In de eerste regel wordt via de VBA-functie Intersect gekeken wat de overlap tussen Target en Range(“F2”) is. Als er geen overlap is (ook als Target leeg is) dan wordt de rest van de routine overgeslagen. Nu krijgen we die vervelende foutmelding van VBA niet meer.
Indirect en Namen
Bij de voorbeelden met gegevensvalidatie is al gebruik gemaakt van Excel-namen. Overal waar je verijst naar een cel of naar een bereik van cellen kun je ook namen gebruiken, dus ook bij de functie Indirect.
Hiernaast staat een voorbeeld (zie het tabblad Namen in het Voorbeeldbestand).
De cellen C3:C12 hebben de naam Een en de cellen C13:C22 de naam Twee.
In cel F2 kun je (met behulp van gegevensvalidatie) kiezen uit deze twee namen.
De formule =SOM(INDIRECT(F2)) in cel F3 telt die cellen bij elkaar op, die door de naam in cel F2 worden bepaald.
LET OP Deze formule werkt alleen als de gegevens netjes met blokken bij elkaar staan (dus soort Een bij elkaar en ook soort Twee). De functie Indirect kan alleen een bereik van cellen verwerken als die cellen op elkaar aansluiten.
In de cellen F4 t/m F7 staan formules die ook het juiste resultaat opleveren, wanneer de gegevens niet netjes gesorteerd zijn.
Voorbeeld van het R1K1-formaat
In cel I4 van het tabblad R1K1 van het Voorbeeldbestand staat een formule die gegevens uit een tabel ophaalt, wanneer de gewenste rij en kolom zijn opgegeven.
Daaronder staan twee varianten; bij eentje wordt de Excel-functie Adresgebruikt.
In het blokje daaronder kunt u zelf ‘spelen’ met de mogelijkheden van deze Adres-functie.
Unieke gegevens selecteren
Hiernaast ziet u een overzicht van 3 lijstjes met daarop verschillende soorten fruit (zie het tabblad Uniek in het Voorbeeldbestand).
Als we willen weten welke soorten voorkomen zoeken we dus de unieke elementen van dit overzicht. Dat is een behoorlijke uitdaging. Maar ook hier komt de functie Indirect van pas.
In cel F3 staat de formule: =INDIRECT( TEKST( MIN( ALS( ($B$3:$D$10<>””)*AANTAL.ALS($F$2:F2;$B$3:$D$10)=0); RIJ($3:$10)*100+KOLOM($B:$D);7^8) );”R0K00″); ONWAAR) &”” Wil je ‘zien’ hoe de formule werkt, gebruik dan in de menutab Formules de optie Formule evalueren.
Aangezien het hier een zogenaamde matrix- of array-formule betreft moet deze worden afgesloten door op Ctrl-Shift-Enter te drukken. Kopieer de formule nu net zo lang naar beneden totdat een of meer lege cellen het resultaat is.
Indirect en bereik-verwijzing
Nog een laatste voorbeeld van het gebruik van de functie Indirect; nu om een bereik van cellen aan te geven (zie het tabblad Range van het Voorbeeldbestand).
In cel F3 halen we vanuit een maand-overzicht het totaal tot en met een bepaalde maand op. Deze maand kan in cel F2 ingevoerd worden.
NB1 cel F2 heeft een gegevensvalidatie, zodat er een keuzelijst ontstaat. Ook al lijkt deze toegestane lijst absoluut ($B$3:$B$14), wanneer je een rij toevoegt aan de tabel dan wordt deze lijst ook automatisch uitgebreid.
NB2 in cel F3 wordt voor het vergelijken gemakshalve naar de hele kolom B gekeken; wanneer je een complex en/of groot systeem hebt gebouwd kan dit vertragend werken. Het is beter om alleen maar in de cellen te zoeken die relevant zijn. In cel F4 staat de formule =SOM(INDIRECT(“C3:C”&VERGELIJKEN(F2;Tabel3[Maand];0)+RIJ(Tabel3[[#Kopteksten];[Maand]]))) Een variant daarop in cel F5: =SOM(C3:INDIRECT(“C”&VERGELIJKEN(F2;Tabel3[Maand];0)+RIJ(Tabel3[[#Kopteksten];[Maand]])))
In cel I4 wordt het totaal van een groepje te kiezen maanden bepaald.
De gegevensvalidatie van de twee grens-maanden is anders dan bij het vorige voorbeeld. Hier gebruiken we de functie Indirect om te verwijzen naar de kolom Maand van de Excel-tabel met de naam Tabel3. Zonder Indirect is dit niet mogelijk.
LET OP gebruik aanhalingstekens rond de kolom-aanduiding.
In een vorig artikel (Excel en kaarten) hebben we laten zien hoe je gegevens op een landkaart kunt plotten; beter gezegd: op ieder willekeurig plaatje.
De daarbij gehanteerde methode maakt het mogelijk om op detailniveau gegevens op een kaart te plaatsen. Microsoft heeft vanaf versie 2016 een ingebouwde grafiek-optie waarmee gegevens op land, provincie en gemeente-niveau als een landkaart kunnen worden weergegeven. Hoe dat in zijn werk gaat (en welke problemen je daarbij moet zien te tackelen) komt in dit artikel aan bod.
Basisgegevens
Bij het CBS zijn heel veel gegevens te vinden, zoals bijvoorbeeld een overzicht van alle gemeentes per provincie (zie het tabblad Gemeenten_alfabetisch_2020 van het Voorbeeldbestand waar de stand van 1 jan 2020 is opgenomen).
We zijn eigenlijk alleen maar geïnteresseerd in de provincie- en gemeentenamen. Met Power Query is dat zo omgezet. Het resultaat staat in de tabel tblGem op het tabblad Data van het Voorbeeldbestand.
Willen we weten hoeveel gemeentes Nederland telde op 1 jan 2020 dan tellen we het aantal niet-lege cellen in de kolom Gemeentenaam met behulp van de functie AANTALARG.
LET OP de functie AANTAL telt alleen gevulde cellen mee, als daar getallen in staan.
In cel C3 wordt op een zelfde manier het aantal cellen in de kolom Provincienaam geteld. Dat levert hetzelfde resultaat op! Hoe bepalen we nu hoeveel provincies er in Nederland zijn (we weten natuurlijk allemaal wel dat het antwoord 12 moet zijn)? De tabel tblGem heeft daartoe een hulpkolom gekregen waarin het volgnummer van de gemeente binnen de provincie wordt geturfd. In cel D8 is daartoe de formule =AANTAL.ALS($B$8:B8;[@Provincienaam]) ingevoerd.
NB de formule wordt in de Excel-tabel automatisch in de hele kolom doorgevoerd. In D9 staat dezelfde formule, maar de laatste B8 is dan B9.
In cel C4 bepalen we het aantal provincies door te tellen hoe vaak het eerste volgnummer voorkomt: =AANTAL.ALS(tblGem[GemPerProv];1)
Voor de liefhebbers staat in cel C5 een formule die het aantal provincies bepaalt zonder gebruik te maken van een hulpkolom: =SOM(ALS( INTERVAL( VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0); VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0) )>0;1))
In een van de volgende artikelen zal ik de functie INTERVAL eens onder de loep nemen.
Kaartgrafiek 1
Als eerste gaan we een kaartgrafiek maken, waarin per provincie is aangegeven hoeveel gemeentes het bevat. Daartoe maken we op basis van de gegevens uit de Excel-tabel tblGem een draaitabel (zie het tabblad ProvGem van het Voorbeeldbestand).
We maken nu de grafiek als volgt:
plaats de cursor op een van de provincienamen
kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
op dit moment kent Excel daarbinnen maar één mogelijkheid, de zogenaamde Choropletenkaart
(“Thematische kaart waarop gemiddelde intensiteiten, dichtheden of relatieve waarden van verschijnselen binnen van te voren begrensde gebieden (meestal administratieve eenheden) zijn weergegeven door middel van vlaksymbolen die verschillen in grijswaarde.“)
En dan het resultaat:
Helaas dus, we zullen de gegevens van de draaitabel eerst moeten kopiëren. Dat hebben we in het tabblad ProvGem gedaan naar de kolommen F en G. De werkwijze wordt dan:
selecteer alle cellen met gegevens, inclusief een kopregel. In het voorbeeld is dat het bereik F3:G15
kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
klik op Choropletenkaart
Na de eerste euforie zien we ineens een ‘grijze’ vlek rechtsboven. Wanneer je met de muis over de kaart beweegt zie je per provincie het bijbehorende aantal, maar bij Groningen krijgen we dit:
Excel heeft zelf geen info over landen, gemeentes en dergelijke vastliggen. Om de kaart te kunnen maken worden gegevens van Bing en TomTom opgehaald wanneer je een grafiek probeert te maken. In de praktijk blijkt dit ophalen lang niet altijd vlekkeloos te gaan. In dit geval weet Excel (of Bing?) niet of we de gemeente of de stad Groningen bedoelen en laat deze gegevens dan weg. Maar waarom gaat het bij Utrecht dan wel goed??? Wijzig je cel F8 in: Provincie Groningen dan worden alle provincies ingetekend.
NB bij het experimenteren met de Kaartgrafiek blijkt dat deze ingebouwde optie nog niet geheel stabiel en betrouwbaar is. In het begin worden plaatsnamen of provincies of andere geografische indelingen soms niet direct herkend. Het blijkt te helpen om te blijven proberen met andere en meer namen tot het systeem deze herkend. Daarna wil het vaak ook met namen lukken, die eerst niet geplot werden.
Welke gegevens er gebruikt kunnen worden is ook niet helemaal duidelijk; het is mij gelukt om de regio’s van Frankrijk ‘in te kleuren’ maar niet de departementen (zie het tabblad Frankrijk van het Voorbeeldbestand). En er gebeuren nog meer ‘vreemde’ dingen: bijvoorbeeld de regio in het noord-oosten heb ik Alsace genoemd, maar Excel/Bing vertaalt dit naar Grand Est.
Een goede methode om Excel (of Bing) te helpen bij het analyseren van de gegevens is om bij de categorie ook het hogere niveau op te nemen. In dit voorbeeld is het hogere niveau van de provincies het land. Klik rechts op een van de provincies in de kaartgrafiek en kies Gegevens selecteren:
Kies bij de aslabels Bewerken en zorg dat ook de kolom met landnamen wordt meegenomen. Ook op deze manier wordt de provincie Groningen herkend:
De standaard-grafiek is op de volgende manier aangepast:
de grafiektitel is aangepast door er in te klikken en wijzigingen aan te brengen. LET OP een dynamische grafiektitel (een verwijzing naar een cel in de Excel-sheet) is hierbij niet mogelijk. Uiteraard kun je nog wel een Tekstvak toevoegen, waarin je een dynamische verwijzing maakt (zie het tabblad GemPerProv van het Voorbeeldbestand)
door rechts te klikken op één van de provincies kun je de Gegevensreeks opmaken
de kaartprojectie laten we voor wat het is; voor echte geografen is dit waarschijnlijk wel interessant
normaal blijft het Kaartgebied ook op Automatisch staan. Soms is één van de overige instellingen wel handig: Experimenteren dus.
bij Kaartlabels heb je 3 mogelijkheden: Geen (dit is de standaardwaarde), Alleen best passend en Alles weergeven. Bij de 2e optie wordt er alleen een label geplaatst wanneer er voldoende ruimte is; bij de 3e manier zul je af en toe een label afgekapt zien (dmv …..)
bij Reekskleur heb ik gekozen voor 3 kleuren; de precieze kleuren en de grenswaarden kunnen daaronder gewijzigd worden
als laatste: klik rechts op één van de provincies en kies Gegevenslabels toevoegen. Door Waarden aan te vinken wordt de Kaartgrafiek aangevuld met de exacte waardes.
Kaartgrafiek 2
Een overzicht van de provincies van Nederland is leuk, maar we willen ook graag per provincie inzoomen.
Het eerste wat we doen is (natuurlijk) een draaitabel maken waarmee we per provincie een overzicht van de gemeentes krijgen (zie het tabblad GemPerProv van het Voorbeeldbestand).
Zoals hiervoor al aangegeven moeten we nog een tussenstap maken voordat we naar de Kaartgrafiek kunnen: daarom hebben we een paar hulpkolommen (F en G) ingevoegd met daarin een formule =ALS(OF(B5=””;B5=”Eindtotaal”);””;B5)
NB omdat een Kaartgrafiek ook niet kan omgaan met dynamische bereiken (waarbij we gebruik maken van de functie Verschuiving) is er in de hulpkolommen ruimte gemaakt voor 100 gemeentes.
In de hulpkolom E is een verwijzing gemaakt naar de gekozen provincie.
Op basis van deze hulpkolommen (met 100 regels) is de kaartgrafiek gemaakt. Kies je in cel C2 een andere provincie dan zal de kaart automatisch aangepast worden.
NB1 wel zal rechtsboven in de grafiek altijd een waarschuwingsteken staan Klik je daar op, dan zul je zien dat maar een (klein) gedeelte van de 100 regels in de grafiek kunnen worden weergegevens; maar dat is ook logisch.
NB2 helaas, niet voor alle provincies gaat het (direct) goed. Kies je bijvoorbeeld Drenthe dan zie je alleen de provincie en niet de gemeentes. Dit is weer op te lossen door bij de aslabels de kolom met provincie NIET mee te nemen. Bij Groningen wordt er helemaal niets getoond! In Noord-Brabant wordt de gemeente Best niet ingevuld, in Limburg hebben we een probleem met de naam Bergen (L).
In de draaitabel wordt in de 2e kolom het aantal gemeentes per gemeente geteld; dat levert natuurlijk altijd als resultaat een 1 op. Ook in de hulpkolom G komt dus een 1. Maar de gebruikte formule daar is iets aangepast: =ALS(OF(B5=””;B5=”Eindtotaal”);””;C5+ALS(RijCur=RIJ();10;0)) Bij de waarde uit kolom C wordt 10 opgeteld als de waarde in de cel met de naam RijCur (dat is hier cel F2) gelijk is aan de Rij waarin de formule staat. De waarde in cel RijCur wordt aangepast door een kleine VBA-routine die aan dit werkblad is gekoppeld: Zo gauw de selectie in dit tabblad wordt gewijzigd (door het verplaatsen van de cursor of het klikken met de muis) wordt de waarde van de geselecteerde rij in de cel RijCur geplaatst. Op dat moment komt in kolom G in die rij een hogere waarde te staan, die er automatisch voor zorgt dat deze gemeente in de grafiek een andere kleur krijgt. Door met de muis op één van de gemeentenamen te klikken kunnen we snel onze topografische kennis opvijzelen!
Kaartgrafiek 3
Een kaartgrafiek kan ook gebruikt worden om een indeling te laten zien. Als de waardes voor de ‘y-as’ geen getallen zijn maar teksten kiest Excel automatisch dit type grafiek (zie het tabblad Utrecht van het Voorbeeldbestand).
De keuze welk type moet worden gegenereerd zit wat verstopt:
klik rechts op een van de gekleurde vlakken en kies Gegevens selecteren
kies bij de Legendagegevens de optie Bewerken
via de radio-buttons kun je dan een van de twee grafiektypen kiezen
Kaartgrafiek 4
Als voorbeeld staan in het tabblad Corona van het Voorbeeldbestand de aantallen Corona-besmettingen van 17 nov voor de verschillende gemeentes in Zuid-Limburg. Niet alleen de aantallen sec, maar ook het aantal besmettingen per 100.000 inwoners. Dit om de gemeentes met elkaar te kunnen vergelijken.
NB de brongegevens van de eerste grafiek worden gevormd door alle gegevens van de kolommen B, C en D. Een kaartgrafiek kan maar één legendawaarde weergeven, dat zal hier dus kolom C zijn. Wanneer je nu deze kolom verbergt (klik op het -teken boven de D) worden de gegevens van de volgende kolom gebruikt. Kolom C is in een groep geplaatst: selecteer de hele kolom door op de letter C te klikken en kies dan Groeperen in de menutab Invoegen.
3D-kaartgrafiek
Dit is een combinatie van een 3D– en een Kaartgrafiek. Microsoft heeft dit niet onder de kaartgrafieken gerubriceerd, althans het zit helemaal ergens anders in de menu-structuur.
plaats de cursor ergens in het bereik met gegevens, die geplot moeten worden. In het voorbeeld is dat in de Excel-tabel tblCorona op het tabblad Corona van het Voorbeeldbestand.
kies dan in de menutab Invoegen de optie 3D–kaart
vul de diverse items als volgt in:
‘speel’ wat met de zoom- en navigeer-knoppen:
Het onderdeel Rondleidingen kent nog veel meer mogelijkheden, maar het voert voor dit artikel te ver om daar op in te gaan.
en vul bij Kopiëren naar de gewenste plaats in
(zie het tabblad Berekeningen).
ofwel 
Klik je daar op, dan zul je zien dat maar een (klein) gedeelte van de 100 regels in de grafiek kunnen worden weergegevens; maar dat is ook logisch.
