Tagarchief: Trendlijn

Grenzen aan de groei – 1

Excel, VBA (Visual Basic for Applications), , , ,
Voorbeeldbestand: ClubRome_1.xlsbDownloaden

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.

  1. Kies in de menutab Invoegen in het blok Illustraties de optie Vormen.
  2. Selecteer de gewenste vorm en ’teken’ met de muis ongeveer op de plaats waar deze moet komen.
  3. Pas in de menutab Hulpmiddelen voor tekenen de Opmaak aan.
  1. 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.
  1. 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.
  1. 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-1 plus de Geboortes T1 in jaar t minus 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:

  1. 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.
  2. 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.
  3. 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:

  1. 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.
  2. de vertraging in het effect wordt door de V-variabelen in de formule bepaald
  3. 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 KF21951 met 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:

=I18*
(1+($C$41/100)*
(VERSCHUIVING([@KF2];-$C$39-$C$40;0)/VERSCHUIVING([@KF2];-$C$39;0)-1)/$C$40)*
(1+($C$44/100)*(VERSCHUIVING([@T6];-$C$42-$C$43;0)/VERSCHUIVING([@T6];-$C$42;0)-1)/$C$43)

  • 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:

  1. 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).
  2. de tweede is het aantal rijen naar beneden of naar boven voor de daadwerkelijke selectie
  3. 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.

Voorspellen

Excel, , ,
Voorbeeldbestand: Voorspellen.xlsxDownload

Voorspellen. Wat zou het mooi zijn als we dat zouden kunnen! Tenminste, als die voorspellingen ook nog eens betrouwbaar zouden zijn.

In het dagelijks leven vliegen ons de voorspellingen om de oren: of het nu gaat over het weer, de voetbaluitslagen, de ontwikkelingen rond Corona.

Ook na het analyseren van cijfers komen er vaak vervolgvragen: hoe zullen de verkoopcijfers er op het einde van het jaar uitzien, hoe ziet de gemiddelde temperatuur er over 10 jaar uit etcetera.

We hebben al eerder laten zien dat je in Excel snel en eenvoudig trend-analyses kunt maken: Trend-analyse en Klimaat-streepjescode. Maar daarbij ging het altijd om lineaire trends.

In dit artikel zullen we laten zien, dat je met Excel ook niet-lineaire trends kunt bepalen.

Lineaire trend

Voordat we niet-lineaire trends gaan bekijken toch nog even naar de simpele voorspelling (lineair).

We beginnen met een simpel voorbeeld: we hebben een overzicht van de eerste 6 maanden van het jaar met de bijbehorende bedragen (zie het tabblad Lin van het Voorbeeldbestand).

Als we zouden moeten ‘gokken’ hoe de ontwikkeling de maanden daarna zal zijn, komen we daar wel uit: iedere maand komt er 100 bij.

LET OP uiteraard alleen onder de aanname dat de ontwikkeling in het verleden zich voortzet in de toekomst!

In de grafiek van deze gegevens hebben we Excel ook een trendlijn laten tekenen, die we laten doorlopen tot het eind van het jaar (zie voor de werkwijze het artikel Trend-analyse).

Ook Excel komt tot de conclusie dat in maand 12 het bedrag waarschijnlijk gelijk is aan 1200.

LET OP2 we hebben hier (uiteraard) een lineaire trendlijn gekozen.
Dit kun je wijzigen in het scherm Opties voor trendlijn (klik rechts op een trendlijn en kies de optie Trendlijn opmaken).

Kies je in dit geval bijvoorbeeld de optie Exponentieel dan zal de verwachting voor de rest van het jaar er heel anders uitzien!

Het is dus altijd zaak om bij voorspellingen aan te geven welke methode is gebruikt.

Maar we hoeven niet per se een trendlijn te laten tekenen; we kunnen de ‘voorspelling’ van de toekomst ook met behulp van Excel-formules bepalen.

In cel C23 van het tabblad Lin in het Voorbeeldbestand staat de formule:
=VOORSPELLEN.LINEAR(B23;$C$17:$C$22;$B$17:$B$22)

Ofwel: voorspel, op basis van een lineaire trend, vanuit de waarden in de cellen C17:C22, die horen bij de maanden in de cellen B17:B22, wat het bedrag zal zijn in de maand in cel B23.

Wanneer we deze formule naar beneden kopiëren, ontstaat een grafiek vergelijkbaar met het vorige voorbeeld, inclusief trendlijn.

NB bij het vertalen vanuit de Engelstalige versie heeft Microsoft in de functienaam een i over het hoofd gezien!

Als we op dezelfde manier als hiervoor grafieken maken op basis van minder ‘gladde’ gegevens, dan wordt duidelijk dat de methodieken van een lineaire trendlijn en de functie Voorspellen.Linear gelijk zijn (zie het tabblad Lin van het Voorbeeld-bestand).

Functie Voorspellen

Voordat we dieper ingaan op het gebruik van de Voorspellen-functies, moeten we eerst wat haken en ogen bespreken.

In de vorige voorbeelden werden de maanden met getallen aangegeven. Wanneer we de (duidelijkere) notatie met afkortingen gebruiken, werkt de Voorspellen-functie niet meer!

Zie het tabblad Voorspelling van het Voorbeeldbestand.

Zoals uit het scherm Functieargumenten blijkt (gebruik de naast de formulebalk) verwacht Excel numerieke waarden (getallen dus) voor de onafhankelijke x-waarden; ook voor de daarvan afhankelijke y-waarden trouwens.

NB ook hier zien we dat de vertalers wat steken hebben laten vallen; tenminste in mijn Excel-versie (2019).

Gelukkig kunnen we aan het #WAARDE!-probleem wel wat doen (zie het tabblad Voorspelling van het Voorbeeldbestand):

Aangezien een datum voor Excel ‘gewoon’ een getal is, is dit probleem te tackelen door in plaats van een maandaanduiding de eerste dag van de maand in te voeren. In het tweede blok hebben we de cellen van de Maand-kolom een zodanige notatie gegeven, dat alleen de maandaanduiding overblijft (gebruik daarvoor de Cel-eigenschappen, Ctrl-1).

NB1 je zou in de maandaanduiding overal ook bijvoorbeeld de 2e dag van de maand kunnen nemen.

NB2 Wat opvalt aan de nieuwe cijfers is dat de functie Voorspellen wat meer ‘intelligentie’ heeft meegekregen. Doordat tussen de diverse begindatums van de maanden niet iedere keer even veel dagen zitten, is de maandelijkse toename in de voorspelling niet precies 100.

Wat kun je als waarden voor de onafhankelijke reeks (de x-waarden) gebruiken?

  1. Zoals we hebben gezien kan de reeks uit aansluitende getallen bestaan (1 ,2 ,3 … of 26, 27, 28 …. etc).
    Ook opeenvolgende datums zijn intern voor Excel opeenvolgende getallen.
    Maar ook 10, 20, 30 … werkt; als er maar een gelijke stap tussen de getallen zit; dus ook 4-1-2021, 11-1-2021, 18-1-2021 … (een serie maandagen op een rij).
  2. Ook hebben we gezien dat Excel het snapt als de reeks bestaat uit de eerste dag van opvolgende maanden (mag ook de 2e of 3e zijn etc.). Het moeten dus logische intervallen zijn.

NB3 de functie Voorspellen werkt ook als de brongegevens niet in de juiste volgorde staan (zie het tabblad Voorspelling). Maar waarom je dat zou doen?

NB4 ook als er één of meerdere waarden ontbreken dan levert de functie nog steeds (de juiste) resultaten.

LET OP heb je jaarcijfers (zoals hiernaast de gemiddelde jaartemperaturen in De Bilt) dan maakt het voor de voorspelling wel uit of je in de onafhankelijke kolom een jaaraanduiding gebruikt of een consistente datum in het betreffende jaar.

In dit geval zijn de verschillen minimaal; pas significant in de 5e decimaal.

Ontbrekende gegevens

Zoals we hiervoor hebben gezien werken de Voorspellen-functies ook als er ‘gaten’ zitten in de tijdlijn.

Laten we nog eens een voorbeeld nemen (zie het tabblad Ontbrekend in het Voorbeeldbestand). De gegevens van maand 3 ontbreken, maar de Voorspellen.Linear-functie (vanaf maand 7) herkent toch het juiste lineaire verloop. Dit in tegenstelling tot de trendlijn in de grafiek; die loopt te steil!

Dit probleem wordt veroorzaakt omdat de x-as in bovenstaande grafiek niet juist het tijdsverloop weergeeft.

Kiezen we als grafiektype het Spreidingsdiagram (soms x-y-grafiek genoemd), dan zien we dat ook de trendlijn het verwachte verloop vertoont.

Ook op een andere manier kunnen we zien dat Excel rekening houdt met ‘gaten’ in de gegevens:

  1. we starten met de basis-gegevens in het tabblad Ontbrekend van het Voorbeeldbestand.
  2. dus selecteer de cellen B2:C7
  3. kies in de menutab Gegevens in het blok Voorspelling de optie Voorspellingsblad
  4. we willen ook de voorspelling van maand 12 weten: maak Einde van prognose gelijk aan 12 en druk op de button Maken.

Excel maakt een nieuw tabblad aan met daarin een gegevens-tabel en een grafiek. In de gegevenstabel zien we dat ook maand 3 een bedrag heeft gekregen! En we hebben een aparte kolom voor de voorspellingen (om de 2 stukken in de grafiek netjes te laten aansluiten is de eerste ‘voorspelling’ gelijk aan het bedrag in die maand).

NB Excel gebruikt in dit geval voor de voorspelling niet Voorspellen.Linear maar een meer algemeen bruikbare methode: de AAA-versie van het algoritme ETS (Exponential Triple Smoothing). In cel I9 staat dan ook de formule =VOORSPELLEN.ETS(G9;$H$3:$H$8;$G$3:$G$8;1;1).
Verderop in dit artikel zoomen we verder in op deze functie.

ETS versus lineair

Om het verschil tussen deze 2 methodes (en het gevaar van de lineaire benadering) te beoordelen, staat in het tabblad Basis van het Voorbeeldbestand een maandoverzicht met daarbij behorende aantallen.
Maken we van de eerste 6 maanden een grafiek met daarin een lineaire trendlijn dan verwachten we op het einde van 2019 een aantal van ongeveer 5.000.

Maar uit het jaaroverzicht blijkt dat die voorspelling er behoorlijk naast zit.

Wanneer we alle bekende gegevens meenemen (tot en met okt 2021) en we laten Excel een lineaire trendlijn bepalen dan halen we misschien in maart 2022 de 5.000.

Maar wat is van zo’n voorspelling nu de betrouwbaarheid? Daar kan ETS een uitkomst bieden:

  1. selecteer op het tabblad Basis de cellen B2:C8
  2. kies in de menutab Gegevens in het blok Voorspelling de optie Voorspellingsblad
  3. open op het tussenscherm onderaan Opties
  4. pas het Einde van prognose aan

Op dit tussenscherm zie je al een grafische voorstelling van de voorspelling. Niet alleen het verwachte verdere verloop van de aantallen, maar ook een inschatting van de betrouwbaarheid van deze voorspelling.

Op basis van de gegevens van de eerste 6 maanden van 2019 verwacht ETS dat 95% van de toekomstige aantallen binnen de 2 dunne oranje lijnen zullen liggen (zie het tabblad GegHalfJaar van het Voorbeeldbestand).

Doen we hetzelfde met de gegevens van heel 2019 dan krijgen we het volgende resultaat (zie het tabblad GegJaar van het Voorbeeldbestand):

De volgende opmerkingen horen bij deze twee voorbeelden:

  1. de betrouwbaarheid van de voorspelling zie je duidelijk beter worden: het verschil tussen de 2 dunne lijnen op het einde van de tweede grafiek is ongeveer 1270, terwijl dat in het voorbeeld hiervoor meer dan het dubbele is.
  2. in allebei de gevallen kiest de ETS-methode een lineaire benadering voor de voorspellingen
  3. bij de gegevens van een half jaar worden de voorspellingen in de loop van de tijd steeds ‘slechter’; de twee dunne lijnen lopen steeds verder uit elkaar.
    De gegevens van een heel jaar zijn van dien aard dat de betrouwbaarheidsgrenzen in de maanden daarna overal ongeveer even ver van de voorspelling af blijven liggen.

Wat als we de gegevens van 2 jaar gebruiken voor de voorspellingen?

Op het tabblad Geg2Jaar van het Voorbeeldbestand is te zien, dat de ETS-methode nog steeds uitgaat van een lineair verloop. De fluctuaties in de loop van de tijd zorgen er wel voor dat de betrouwbaarheidsgrenzen weer uit elkaar gaan lopen.

Maar zou er ook een seizoenspatroon in kunnen zitten? Een piek(je) in het midden van het jaar? Dat kunnen we gelukkig in Excel ook uitproberen: in de Opties zetten we het aantal bij Seizoensgebonden op 12 (zie het tabblad Geg2jaarb).

Het lineaire verloop in de voorspelling is daarmee (uiteraard) verdwenen en het betrouwbaarheidsinterval is beduidend smaller geworden! Een indicatie dat de kwaliteit van de voorspelling vooruit is gegaan.

NB meestal zal de ETS-methode pas na 2 seizoen-cycli niet meer de lineaire benadering toepassen op de voorspelling.

Seizoen = 11
Seizoen = 12

Bovenstaande grafieken zijn gemaakt op basis van gegevens van 2½ jaar. De linkse ontstaat wanneer Excel automatisch de berekeningen uitvoert (zie het tabblad Geg2,5Jaar van het Voorbeeldbestand). Blijkbaar ontstaat de beste fit wanneer een seizoenspatroon van 11 maanden wordt gekozen.
De rechtse grafiek ontstaat wanneer we uitgaan van een seizoenspatroon van 1 jaar (zie tabblad Geg2,5Jaarb). Dat lijkt in ieder geval reëler, maar duidelijk is te zien dat de betrouwbaarheid van de voorspellingen dan verslechteren.

Op het laatste tabblad (Geg2,5Jaarc) zien we de voorspellingen, op basis van gegevens van 2½ jaar, aangevuld met de resultaten van de meest recente maanden.

Het lijkt er op, dat de voorspelling op basis van een 12-maanden seizoen zo gek nog niet is.

LET OP door bovenstaande voorbeelden mag het duidelijk zijn dat het ‘gevaarlijk’ is om te vertrouwen op één manier van voorspellen. En logisch: hoe meer historische gegevens hoe beter de voorspelling zal zijn. Tenminste als de gegevens in een stabiele omgeving zijn verzameld (we zullen deze voorspellingsmethode dus maar niet loslaten op de Corona-cijfers).

NB1 Op het Opties-blad kun je ook aangeven hoe groot het betrouwbaarheidsinterval moet zijn:

NB2 de ETS-methode kent diverse betrouwbaarheidsstatistieken:
Het resultaat daarvan zie je in de diverse tabbladen:

Kijk voor de betekenis van deze parameters bijvoorbeeld op
exceljet.net/excel-functions/excel-forecast.ets.stat-function of
www.brightworkresearch.com/alpha-beta-and-gamma-in-forecasting/.

Klimaat-streepjescode

Excel, ,
Voorbeeldbestand: GemTemp.xlsxDownload

Het KNMI publiceerde vorige week een klimaat-streepjescode. Zonder veel extra informatie is direct te zien wat de hoofdconclusie is!

Behalve dat de achterliggende oorzaak niet blij-makend is, is dit wel een mooie aanleiding om te kijken of we in Excel een dergelijk overzicht kunnen maken.

Brongegevens

Het KNMI stelt van een 10-tal weerstations de historische gemiddelde temperaturen ter beschikking; gemiddeld per jaar en per maand.
In het tabblad Data van het Voorbeeldbestand staat een link naar één van de bestanden.

Met behulp van Power Query (zie dit artikel) zijn deze bestanden binnengehaald en in één totaal-tabel, tblBronData, geplaatst (zie het tabblad Brondata van het Voorbeeldbestand).

Jaar-overzicht

Het tabblad OvzJr van het Voorbeeldbestand bevat een draaitabel gemaakt op basis van deze brongegevens. In het filterblok hebben we alleen voor jaarcijfers gekozen.
Nu is snel te zien dat de meeste weerstations pas cijfers vanaf 2006 hebben. Ook zien we dat Nederland nog een behoorlijke variatie in de gemiddelde temperatuur kent (gemiddeld over alle jaren een verschil van 1,5 graad tussen Eelde en Vlissingen).
Enkele stations hebben geen cijfers voor de jaren 1944 en 1945.

NB1 de Waardeveldinstellingen van de gemiddelde temperatuur is gewijzigd van de standaardinstelling Som naar Gemiddeld, zodat in de eindkolom een gemiddelde over alle stations ontstaat.

NB2 de tekst Gemiddelde van GemTemp linksboven in de tabel is gewijzigd; wel staat er een spatie achter GemTemp, anders geeft Excel een foutmelding.

Maand-overzicht

In het tabblad OvzMnd van het Voorbeeldbestand is een andere draaitabel op basis van de brongegevens gemaakt. Per weerstation zijn daar de maandgemiddelden per jaar terug te vinden.

NB bij Maand is het item Year uitgevinkt.

Maandgrafiek

Meestal geeft een grafiek een beter inzicht in het verloop in de tijd dan een tabel met getallen.
In het tabblad GrafMnd van het Voorbeeldbestand ziet u het resultaat:

Door de grote hoeveelheid gegevens valt hier weinig te concluderen. Wel hebben we een (lineaire) trendlijn toegevoegd (rechts-klikken op de grafiek) en dan zien we dat er een stijgende lijn is. Voor iedere stap op de x-as (dus iedere maand) gaat de temperatuur gemiddeld 0,0013 graad omhoog; dat is per jaar dus ruim 0,015 graden en per 100 jaar 1,5 graad, ruwweg de spreiding van het gemiddelde over Nederland.

NB de is een maat voor de betrouwbaarheid van de trendlijn; hoe dichter bij 1 hoe beter. Zie het artikel Trend-analyse.

Jaargrafiek

Dan maar de maandgegevens weglaten en ons focussen op het jaargemiddelde.
Dat zegt nog steeds niet veel (zie het tabblad GrafJr van het Voorbeeldbestand).

Daarom de y-as wat aanpassen zodat de variaties per jaar beter zichtbaar worden:

Duidelijk is te zien dat het jaargemiddelde een stijgende lijn vertoont, ondanks de diverse uitschieters naar beneden.
De stijging van de trendlijn is bijna 0,017 graad per jaar; de betrouwbaarheid is natuurlijk vele malen groter omdat we alleen naar het jaar-gemiddelde kijken.

NB dit artikel gaat niet over trendanalyse; misschien is een lineaire trend hier niet de meest relevante.

Klimaat-streepjescode

Deze manier om klimaatdata weer te geven is bedacht door klimaatwetenschapper Ed Hawkins (onder de naam ‘warming stripes’); dit om de langjarige trend van temperaturen onder de aandacht te brengen.

Maar hoe maken we dit in Excel? Het lijkt op een grafiek maar is het niet; we gaan Voorwaardelijke opmaak gebruiken (zie het tabblad WarmStr in het Voorbeeldbestand):

  1. we maken een draaitabel net als in het tabblad GrafJr, maar we zorgen er voor dat de jaren naast elkaar komen (dus plaatsen Jaar in de Kolommen).
    NB door een kopie te maken van de draaitabel lopen de filteringen/selecties, die we later toepassen, synchroon. De draaitabellen laten allemaal dezelfde gegevens zien maar op een andere manier.
  2. in rij 4 staat om de 10 cellen een verwijzing naar rij 9 (de jaren)
  3. zorg dat in het Filterblok alle stations zijn gekozen en selecteer dan alle gevulde cellen in rij 10.
  4. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  5. kies bij Kleurenschalen de zesde optie (rood-wit-blauw)
  6. maak de kolommen smaller en rij 10 hoger
  7. zorg dat rijen en kolommen, die het beeld ‘vertroebelen’, niet zichtbaar zijn. Selecteer de betreffende rijen (of kolommen) en kies in de menutab Gegevens in het blok Overzicht de optie Groeperen.

NB1 gebruik Groeperen en NIET Verbergen. Een groot nadeel van de 2e methode is namelijk dat anderen (en jijzelf!) niet in de gaten hebben dat er iets niet zichtbaar is.

NB2 wil je de kleuren aanpassen? Ga naar Voorwaardelijke opmaak/Regels beheren.

Maak een kopie van de draaitabel, plaats die er onder en sorteer die op oplopende waarde van de gemiddelde temperatuur en u hebt een mooie temperatuurlegenda. Deze verandert automatisch mee bij verandering van weerstation-keuze.

Analyse

Maar we kunnen de trend binnen de basisgegevens nog verder (en preciezer) analyseren (zie het tabblad Analyse van het Voorbeeldbestand).
De richting van de trendlijn kunnen we ook bepalen zonder de trendlijn te tekenen:
=RICHTING(C6:C124;B6:B124)

De waarde voor b in de formule voor de rechte lijn bepalen we op een vergelijkbare manier met de functie Snijpunt.

NB Snijpunt bepaalt de waarde voor b in het jaar 0 (nul). In cel G4 staat een gecorrigeerde formule, zodat b de waarde geeft voor het eerste jaar uit de reeks (in het voorbeeld 1901): =SNIJPUNT(C6:C124;(B6:B124)-B6+1)

De wordt bepaald met de functie R.KWADRAAT.

Om de richting van de trend (en de bijbehorende ) op bepaalde tijdsintervallen te kunnen bepalen heb ik nog een apart blokje berekeningen toegevoegd:

Via een begin- en eindjaar wordt bepaald welke rijen uit de kolommen B en C we in de berekening mee willen nemen (in het voorbeeld hier de rijen 10 t/m 20). Bij het berekenen van de richting nemen we het betreffende blok mee door middel van de functie Verschuiving:

Bekijken we de gemiddelde resultaten voor heel Nederland tot en met 1980 , dan zien we dat de gemiddelde jaarlijkse temperatuurstijging op 0,0056 uitkomt met een lage betrouwbaarheid.

Terwijl de gemiddelde stijging in de laatste 40 jaar bijna een factor 10 groter is. Daarbij is de betrouwbaarheid van die trend ook een factor 10 hoger.

Trend-analyse

Excel, , , , , , , , , , ,

Ik was deze week nog eens naar Google-analytics aan het kijken hoe het met de bezoekersaantallen van G-Info gaat.

Er zit nog steeds een stijgende lijn in; altijd leuk!

Wel wat vreemde uitschieters:

  • eind april/begin mei 2015 heeft Google problemen gehad en is er niets geturfd
  • juli en aug van ieder jaar vertoont een dip; waarschijnlijk hebben mensen dan wat anders te doen
  • ook bij de dips van eind december kan ik me wat voorstellen
  • bij nadere bestudering blijken alle punten aan de onderkant op zaterdag en zondag te vallen
  • en de pieken naar boven vallen samen met de momenten dat ik een Nieuwsbrief uitstuur met de aankondiging dat er een nieuw artikel op de site is verschenen.

Maar laten we eens kijken hoe we de trend kunnen analyseren.

Trendlijn-1

Op basis van de gegevens van Google heb ik bovenstaande grafiek gemaakt (je kunt de data uit Analytics eenvoudig exporteren naar Excel):

  1. in het Voorbeeldbestand staan op het tabblad Gegevensset de aantallen bezoekers per dag (beter gezegd de aantallen sessies).
    Daar heb ik ‘natuurlijk’ direct een Excel-tabel van gemaakt (zie het artikel over de 10 voordelen van een tabel en Tabellen deel 2). De naam daarvan is Tabel1.
  2. selecteer de gegevens uit de 2 kolommen door de bovenkant van de kolommen aan te wijzen (de cursor is dan een zwart-pijltje naar beneden; de linker-muis-toets ingedrukt houden).
  3. kies in de menubalk de optie Invoegen/Grafieken/Lijn
  4. het resultaat vindt u terug in het tabblad Graf

In de voorbeeldgrafiek heb ik Excel ook een trendlijn laten toevoegen:

  1. klik ergens met de rechtermuisknop op de grafiek en kies de optie Trendlijn toevoegen
  2. laten we het simpel houden en voor een rechte lijn (de optie Lineair) kiezen
  3. vink ook de 2 onderste opties aan: Vergelijking en R-kwadraat

Excel heeft een mooie (stijgende) lijn getekend; de wiskundige formule die daar bij hoort is
y = 0,0956x – 3998,4

Het getal voor de x is de richtingscoëfficiënt (RC): hoeveel verandert de lijn als x (in dit geval de dag) met 1 toeneemt (dus ruwweg iedere 10 dagen komt er 1 bezoeker meer).
Het andere getal (-3998,4; het snijpunt met de y-as) geeft het aantal bezoekers weer als x gelijk is aan 0 (nul).

NB1 Omdat op de x-as datums staan en voor Excel een datum niets anders is dan het aantal dagen na 1-1-1900, zou het aantal bezoekers op die dag-nul dus flink negatief zijn geweest. De lijn naar het verleden doortrekken is dus onzinnig.

NB2 voor de liefhebbers: Excel gebruikt voor het bepalen van de lijn de zogenaamde kleinste-kwadratenmethode.

NB3 R² (R-kwadraat) is de zogenaamde determinatiecoëfficiënt. Deze geeft aan welk gedeelte van de variatie in de ene variabele door de andere wordt verklaard.
Ofwel: hoe ´betrouwbaar´ is de trendlijn. Is de R² kleiner dan 0,5 dan is het verband zwak tot matig, ligt die tussen 0,5 en 0,75 dan is het verband sterk en anders zeer sterk.

Hoeveel bezoekers mag G-Info op basis van deze trendlijn over een jaar verwachten? Als iedere 10 dagen er 1 extra bezoek bij komt dan zouden er over 365 dagen ongeveer 36 meer moeten zijn dan nu; dus ipv gemiddeld 95 zouden er dat ongeveer 130 zijn.
Dit kunnen we ook makkelijk grafisch laten zien:

  1. klik met de rechtermuisknop op de trendlijn in de grafiek
  2. kies Trendlijn opmaken
  3. en bij Voorspelling/Vooruit vullen we 365 in

Richting, Snijpunt, R.kwadraat en Lijnsch

Om in Excel met de trend te kunnen rekenen moeten we de richtingscoëfficiënt en het snijpunt met de y-as exact weten; we hebben niet genoeg aan een formule in de grafiek.

Uiteraard zijn daar functies voor (zie het tabblad Gegevensset in het Voorbeeldbestand):

  1. in cel H2 willen we de RC plaatsen.
    * tik in =Richting(
    * klik op de bovenrand van Sessies in kolom C (de y-waarden)
    * tik in ;
    * klik op de bovenrand van Dagindex in kolom B (de x-waarden)
    * druk op Enter (Excel zal automatisch de sluithaak van de formule toevoegen)
  2. in H3 is op dezelfde manier de functie SNIJPUNT ingevoerd
  3. en in H4 de functie R.KWADRAAT (denk aan de punt na de R)

Voor de liefhebbers: Excel kent nog een andere functie; deze levert meer statistische resultaten op, LIJNSCH.

In het kort (zie het tabblad Gegevensset in het Voorbeeldbestand):

  1. selecteer 6 lege cellen, 2 kolommen en 3 regels  (in het voorbeeld G12:H14)
  2. in de eerste cel komt de volgende formule:
    =LIJNSCH(Tabel1[Sessies];Tabel1[Dagindex];WAAR;WAAR)
    De y- en x-waarden zijn de bekende kolommen uit de tabel met Google-data.
  3. druk nu niet op Enter, maar Ctrl-Shift-Enter (de zogenaamde CSE-invoer)

De betekenis van de 6 cellen heb ik in de kolommen er naast aangegeven; de cellen G12:H14 hebben corresponderende namen gekregen, zodat formules die er naar verwijzen beter leesbaar zijn.

NB De 2 ‘boven’ de R krijg je als volgt: selecteer in de formulebalk de 2, klik Ctrl-1 (de sneltoets voor Celeigenschappen) en kies de optie Superscript. De m, b en y achter SD hebben de eigenschap Subscript gekregen.

LET OP Lijnsch is een zogenaamde array- of matrix-functie. Het resultaat bestaat niet uit 1 waarde maar uit meerdere, vandaar dat die met CSE wordt ingevoerd. Alle cellen bevatten dezelfde functie, omgeven door accolades.

NB Lijnsch levert meer dan 6 resultaten terug; probeer zelf maar uit door in de eerste stap meer kolommen en regels te selecteren.

De resultaten van bovenstaande functies kunnen we nu gebruiken om voorspellingen voor de toekomst te genereren (zie het tabblad Gegevensset in het Voorbeeldbestand).

Trendlijn-2

Omdat de bezoekersaantallen in de weekenden heel anders zijn dan op werkdagen, heb ik voor een nadere analyse op het tabblad Gegevensset in het Voorbeeldbestand nog een kolom toegevoegd:
=KIEZEN(WEEKDAG([@Dagindex];2);”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)

Ofwel: bepaal de Weekdag van de corresponderende datum in kolom B (Dagindex; we willen dat de week op maandag begint, vandaar de parameter 2). Aangezien deze functie volgnummers oplevert (een maandag is 1, dinsdag 2 etc), heb ik de nummers nog vertaald in teksten mbv de functie Kiezen.

Op basis van deze tabel heb ik een draaitabel gemaakt (zie het tabblad Draai in het Voorbeeldbestand).
Door het veld Weekdag in het Rapportfilter te plaatsen kunnen we snel een overzicht per afzonderlijke dag maken.
Om ook analyses per jaar, kwartaal of maand te kunnen maken heb ik de datums in het veld Dagindex gegroepeerd (zie Groeperen in een draaitabel).

Nog een paar stappen en we kunnen met de resultaten gaan ‘spelen’:

  1. selecteer een cel in de draaitabel
  2. in de menutab Hulpmiddelen voor draaitabellen kiezen we het tabblad Opties
  3. klik dan op de optie Draaigrafiek in het blok Extra
  4. kies een Lijngrafiek en klik OK
  5. voeg een Trendlijn toe, inclusief Vergelijking en R-kwadraat

Na wat lay-outen en het toevoegen van 2 slicers (zie Slicers in Excel) krijgen we een resultaat zoals weergegeven in het tabblad DrGraf in het Voorbeeldbestand.

LET OP de b in de vergelijking van de trendlijn gebruikt voor x=0 niet meer de datum 1-1-1900, maar de eerste datum in de betreffende draaitabel.

Wanneer we in de Slicer Weekdag alleen de maandag kiezen (klikken op de button ma) dan zien we dat de R² al veel beter wordt (0,6381).

LET OP de m in de vergelijking van de trendlijn geeft nu niet de verandering per dag aan, maar de verandering naar de volgende maandag, dus na 1 week.
Wil je de trendlijn 1 jaar vooruit laten ‘kijken’, tik bij Voorspelling/Vooruit dan ook geen 365, maar 52 in (dus over 1 jaar 160 bezoekers op maandag?).

Wil je het resultaat over alle werkdagen zien:

  1. kies in de Slicer Weekdag de button ma
  2. houd Shift ingedrukt en klik op vr
  3. laat Shift los
  4. de draaitabel, draaigrafiek en trendlijn passen zich automatisch aan

Analyse van de jaren laat zien (gebruik de betreffende Slicer), dat de trendlijn voor 2015 ´betrouwbaarder´ is dan die van 2016. De resultaten van vorig jaar worden zwaar beïnvloed door de dips in vakantie-periodes.