Categorie archief: Excel

Voorspellen-2

2 december 2021ExcelIndex, Kansverdeling, Kruispunt, Model, VergelijkenGijs Verbruggen

Voorbeeldbestand: Voorspellen2.xlsx Download

Het vorige artikel ging over Voorspellen.
We hebben daar laten zien hoe je op diverse manieren op basis van historische gegevens iets kunt zeggen over de toekomst.
Dat daarbij veel ‘slagen om de arm’ gehouden moeten worden, mag duidelijk zijn: als er ook maar iets in de omgevingsfactoren verandert, kunnen de voorspellingen ver afwijken van de nog te behalen resultaten.

In dit artikel gaan we de zaak omdraaien: op basis van feiten/metingen én scenario’s voor omgevingsfactoren berekenen we de consequenties voor de toekomstige resultaten.

Als voorbeeld voor de werkwijze kijken we of we de waterstanden in de Geul (het gedeelte vanaf de bron tot aan Valkenburg) op deze manier zouden kunnen voorspellen.
Alvast een disclaimer: het is nog een heel eenvoudig model, dus ik blijf nog even, 75 meter hoger, in Heerlen wonen!

In dit voorbeeld wordt gebruik gemaakt van enkele (minder bekende) Excel-technieken: kruispunt van reeksen en het toepassen van een zelf-gedefinieerde kansverdeling.

Probleem-beschrijving

Zoals gezegd gaan we het probleem flink versimpelen zodat we het in een eenvoudig model kunnen gebruiken (zie het tabblad Data van het Voorbeeldbestand):

vanaf de bron slingert de Geul zich over ongeveer 50km naar Valkenburg
dit gedeelte van de rivier wordt in 5 gelijke delen verdeeld
per deel ligt vast hoeveel water er altijd blijft staan (MinStand).
Vanaf de bron is de rivier gemiddeld 1m breed en er staat altijd minimaal 25cm water, dus dat is 2.500m³.
In Valkenburg is de rivier maar 2m breed, maar is altijd minstens 50cm diep.
ook leggen we de hoeveelheid water vast (MaxStand) waarbij het gebied gaat overstromen.
Vanaf de bron neemt deze maximale stand toe; net voor Valkenburg heeft de rivier de ruimte in de breedte, maar in het stadje wordt die over een flink stuk ingeperkt tot 2m.
door regenval komt er water bij. Per deel ramen we de maximale hoeveelheid water dat daar per dag in de rivier terecht komt (MaxBij).
als laatste hebben we nog nodig hoeveel water er dagelijks maximaal afgevoerd kan worden (MaxAf; vaak beperkt door het meanderen, versmallingen etcetera).

NB nog maar een keer: dit zijn heel simpele benaderingen; om het model te verbeteren zouden exactere metingen moeten worden uitgevoerd.

Kruispunt

Voordat we het eerste model gaan bekijken bespreken we eerst een Excel-techniek die in dat model gebruikt zal worden.

In het overzicht op het tabblad Data hebben diverse reeksen een naam gekregen: de gegevens achter MinStand, MaxStand et cetera krijgen de naam uit de eerste kolom.
Voor de kolommen zouden we ook zoiets willen doen, maar de kolomkoppen bestaan uitsluitend uit getallen en Excel-namen moeten beginnen met een letter of speciaal teken. Ook één of twee letters er voor zetten is niet voldoende omdat er dan een verwijzing naar een kolomnaam ontstaat. We hebben er voor gekozen om de gegevens in de kolommen de naam _Km10, _Km20 et cetera te geven.

Om in een tabel gegevens op te zoeken gebruiken we vaak Vert.Zoeken of een combinatie van Index en Vergelijken.

In het Voorbeeldbestand gebruiken we de kruispunt-techniek: in cel J4 van het tabblad Data staat de formule
=MinStand _Km10.

LET OP de 2 namen moeten gescheiden zijn door een spatie.

Het resultaat van de formule is de inhoud van de cel op het kruispunt van de 2 reeksen.

We kunnen de formule dynamisch maken: in plaats van harde namen, willen we verwijzingen naar cellen gebruiken. Helaas: een gewone verwijzing werkt niet, we moeten de functie Indirect gebruiken (zie de formule in cel J8:
=INDIRECT(J6) INDIRECT(“_Km”&J7)). Plaats wel een spatie tussen de 2 Indirect-functies!

Eerste model

Op het tabblad Simpel van het Voorbeeldbestand nemen we allereerst met de kruispunt-techniek de gegevens uit het tabblad Data over. Door een juiste mix van absolute en relatieve verwijzingen kan de formule uit cel C5 overal gekopieerd worden.

Om te voorkomen dat per ongeluk basis-gegevens worden overschreven zijn de model-tabbladen beveiligd (wel zonder wachtwoord).
Wil je gegevens wijzigen, doe dat dan in het tabblad Data.

De opbouw van het model:

we gaan ervanuit dat op dag 1 de rivier overal de minimumstand heeft.
in de kolom daarnaast bepalen we de hoeveelheid water die er bij komt, door een willekeurig getal te kiezen tussen 0 en MaxBij voor dat deel van de rivier.

de hoeveelheid water, dat wordt afgevoerd, wordt bepaald door de formule (in cel E11)
=ALS(C11+D11>C$5;MIN(C$8;C11+D11-C$5);0)
Dus als de beginstand plus het water, dat er bij komt, meer is dan de minimumstand dan wordt er water afgevoerd, anders niets. De afvoer is gelijk aan de beginstand plus de toevoer minus de minimale stand. Maar als daarmee de MaxAf wordt overschreden, dan wordt de MaxAf afgevoerd.
de berekening van de eindstand (in cel G11) is dan makkelijk: =C11+D11-E11
de beginstand van de volgende dag is de eindstand van de dag er voor. De berekening van de overige gegevens van die dag is gelijk aan die van de eerste dag.
bij de volgende delen van de rivier is de berekening precies hetzelfde met één verschil: de hoeveelheid water dat er per dag bij komt is niet alleen de hoeveelheid regen maar ook de hoeveelheid die uit het vorige deel van de rivier komt (kolom Af).

Zo gaat het resultaat er dan uitzien (zie het tabblad Simpel van het Voorbeeldbestand):

Om snel te zien of de eindstanden per dag nog ‘behapbaar’ zijn, zijn er kolommen tussengevoegd met een voorwaardelijke opmaak (stoplichtmodel).

Druk op F9 (herberekenen) en u ziet wat de consequenties volgens dit model zijn voor de waterstromen in de Geul. Vanwege de willekeurige hoeveelheid regen, die gegenereerd wordt, wijzigen iedere keer alle resultaten. Hou F9 vast en u ziet snel waar de grootste problemen worden verwacht.

Aangepast model

Eén van de gehanteerde aannames (los van de onderliggende ‘metingen’) rammelt nogal: als het in één deel hard regent, dan kan het in het volgende deel bijna droog zijn. Dat is natuurlijk op zo’n korte afstand helemaal niet reëel.
Op het tabblad Afh van het Voorbeeldbestand vindt u dan ook een ander model. Daarbij krijgt het eerste deel van de rivier per dag nog steeds een willekeurige hoeveelheid regen, maar de andere delen krijgen op die dag een evenredige hoeveelheid.
In cel I11 staat daartoe de formule: =H$7*D11/C$7
De maximaal verwachte regen in het tweede deel (H7) wordt vermenigvuldigd met de hoeveelheid regen in het eerste deel (D11) gedeeld door de maximaal verwachte regen in het tweede deel (C7) .

Maar als je nu F9 gebruikt, zul je zien dat deze aanpassing voor de resultaten nog niet zoveel uitmaakt.
Ook nu ontstaan in het tweede gedeelte van de rivier al snel overstromingen.

Eigen kansverdeling

Bij de vorige modellen hebben we gebruik gemaakt van een zogenaamde discrete, uniforme verdeling (iedere hoeveelheid regen heeft even veel kans om voor te komen).

Een andere bekende verdeling is de normale distributie.

Maar allebei geven ze niet goed weer hoe de regen zich gedraagt in onze regio.

De kans dat het op een dag niet regent is (gelukkig) groter dan alle andere mogelijkheden. Maar als het regent dan komt er ook echt wel wat naar beneden.

De volgende verdeling geeft dat idee weer:

De kans dat het niet regent is in dit voorbeeld 25%, kans op een heel klein beetje regen is klein, de kans op iets meer regen wordt groter en de kans op heel veel regen is heel klein. Deze verdeling zouden we in ons model willen gebruiken.
Voordat we dat doen, kijken we even gedetailleerder naar deze verdeling.

In het tabblad KansVerdeling van het Voorbeeldbestand is de gewenste verdeling ‘met de hand’ ingevuld. Stap 0 krijgt een kans van 25%, de volgende stap 2,5%, dan 1,5% et cetera tot en met stap 25 die een kans van 0,5% heeft.

De tabel (met de naam Tabel1) heeft ook nog een cumulatieve kolom.
LET OP in deze kolom staan de cumulatieven tot en met de VORIGE stap!

We gaan deze tabel gebruiken om vanuit een percentage in de cumulatieve kolom de daarbij behorende stap op te zoeken. Een combinatie van de functies Index en Vergelijken levert het gewenste resultaat:

NB de 3^eparameter in de functie Vergelijken heeft de waarde 1. De functie zoekt dan naar de grootste waarde die kleiner dan of gelijk is aan de Zoekwaarde (de eerste parameter).

In cel *B34* wordt telkens een willekeurig getal tussen 0 en 1 berekend, in *C34* staat dan de bijbehorende stap. De rode ster in de grafiek geeft de positie aan van de *30%*-kans, de zwarte ster die van de kans uit cel *B34*.
Druk op F9 om in *B34* een andere kans te genereren.

Met deze techniek vertalen we een continue, uniforme distributie naar een discrete, gewenste verdeling. 25% van de willekeurige getallen in cel B34 laten in cel C34 een stap=0 zien, 2,5% genereren een stap=1, 1,5% een stap=2 enzovoort.

Ter controle worden in het tabblad VerdContr telkens 1.000 percentages gegeneerd, die op bovenstaande manier naar een Code worden vertaald. Wanneer deze codes in een draaitabel worden gezet, kunnen we daarop een draaigrafiek baseren. We zien dan onze gewenste verdeling terug.

NB Vernieuw de draaitabel en alles wijzigt. Aangezien Excel automatisch alles opnieuw berekent (en dus ook nieuwe kansen bepaalt) hoeven we niet meer op F9 te drukken om nieuwe codes te krijgen.

Laatste model

In het tabblad Verdeling van het Voorbeeldbestand hebben we bovenstaande verdeling en techniek gebruikt om het model te verbeteren. In de Bij-kolom in het 10-km-deel van de Geul gebruiken we de formule:
=INDEX(Tabel1[Stap];VERGELIJKEN(ASELECT();Tabel1[Cum];1))*C$7/MAX(Tabel1[Stap])
Dus de gevonden stap wordt geschaald naar een waarde tussen 0 en de maximaal verwachte regenval.

NB1 je kunt de gewenste verdeling verfijnen door meer stappen in te voegen; door MAX(Tabel1[Stap]) wordt de gevonden stap op de juiste manier gecorrigeerd.
Zorg wel dat de som van de kansen in de gewenste verdeling precies 100% is.

NB2 aan de Eind-kolommen is nog een voorwaardelijke opmaak toegevoegd waardoor de ‘waterstand’ snel kan worden ingeschat.

Allerlaatste model

In het vorige model kan de hoeveelheid regen van dag op dag flink fluctueren; dat is natuurlijk niet helemaal de realiteit. In het tabblad Verdeling4gelijk van het Voorbeeldbestand hebben we er voor gezorgd dat er telkens 4 dagen op een rij ‘hetzelfde weer’ is, beter gezegd dat het telkens 4 dagen even veel regent.

En zo kunnen we nog wel even doorgaan. Iedere aanpassing aan het model roept weer vragen op en nodigt uit tot nieuwe aanpassingen. Wat te denken van de continue toename van het water in Valkenburg. Hoe zal de situatie daar zijn na 40 dagen???? Ergens zit nog iets geks in het model.

Maar goed, we laten de verdere verfijning over aan Rijkswaterstaat 😉

Voorspellen

25 november 2021ExcelSpreidingsdiagram, Trendlijn, Voorspellen.ETS, Voorspellen.LinearGijs Verbruggen

Voorbeeldbestand: Voorspellen.xlsx Download

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 OP² 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 2^e 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?

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).
Ook hebben we gezien dat Excel het snapt als de reeks bestaat uit de eerste dag van opvolgende maanden (mag ook de 2^e of 3^e 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:

we starten met de basis-gegevens in het tabblad Ontbrekend van het Voorbeeldbestand.
dus selecteer de cellen B2:C7
kies in de menutab Gegevens in het blok Voorspelling de optie Voorspellingsblad
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:

selecteer op het tabblad Basis de cellen B2:C8
kies in de menutab Gegevens in het blok Voorspelling de optie Voorspellingsblad
open op het tussenscherm onderaan Opties
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:

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.
in allebei de gevallen kiest de ETS-methode een lineaire benadering voor de voorspellingen
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.

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/.

Kringverwijzingen en iteraties

10 november 2021ExcelIteratie, Kringverwijzing, voorwaardelijke opmaakGijs Verbruggen

Voorbeeldbestand: GameLife.xlsx Download

Iedereen maakt het wel eens mee dat Excel ineens een melding geeft dat er een Kringverwijzing is gesignaleerd.

Wat betekent dat? Hoe weet je of er Kringverwijzingen zijn? Hoe los je het op? En kunnen we kringverwijzingen zinvol toepassen in de praktijk? Om de laatste vraag alvast te beantwoorden: jazeker, en dat noemen we dan Iteraties.

Kringverwijzing

Soms gebeurt het ineens: ben je ijverig bezig een mooie spreadsheet in elkaar te zetten, krijg je ineens de volgende melding:

Deze melding kun je ook krijgen als je een bestaande spreadsheet opent.

De uitleg in de melding maakt wel duidelijk wat er aan de hand is: een formule in een cel verwijst naar deze cel zelf (direct of indirect). Excel weet dan niet wat ie moet doen: 1 keer berekenen of, als dat resultaat is berekend, nog een keer? En daarna nog een keer? In de standaard-instelling (waarbij Excel alles automatisch herberekent) stopt de berekening en is het resultaat van de formule gelijk aan 0.

Wanneer Excel een kringverwijzing tegenkomt dan wordt dit zichtbaar in de status-balk onder aan het Excel-venster.

LET OP1 ook al staat er in de statusbalk maar één cel, er kunnen meerdere kringverwijzingen in het spreadsheet voorkomen.

LET OP2 als er kringverwijzingen zijn dan moet je normaal gesproken deze direct oplossen; ALLE resultaten van berekeningen zijn daardoor onbetrouwbaar.

Een paar voorbeelden (zie het tabblad Kring van het Voorbeeldbestand):

In cel B2 staat de formule =B2+1.

Er staat nu de waarde 0, dus het resultaat zou dan 1 moeten zijn. Maar als er 1 staat in de cel, dan wordt het resultaat van de formule 2 enzovoort.
Meestal betekent dit dat er naar een verkeerde cel wordt verwezen of dat de formule in de verkeerde cel wordt geplaatst.
De oplossing is simpel: pas de verwijzing in de formule aan of verplaats de formule.

Een vaker voorkomende fout bij het maken van een spreadsheet: in de verwijzing in een Som-formule wordt ook verwezen naar de cel, waarin de formule staat (in het voorbeeld hiernaast D6).
Oplossing: zorg dat de verwijzing in de formule naar de juiste cellen verwijst.

We kennen ook een zogenaamde verborgen kringverwijzing; deze zijn soms moeilijker op te sporen omdat deze niet altijd optreden.
In het voorbeeld hiernaast staat in cel F6 een formule die afhankelijk van de waarde in F2 twee verschillende berekeningen uitvoert.
Als F2 de waarde 1 bevat kan Excel de berekening gewoon uitvoeren en zal dan ook niets melden; bevat die cel echter een andere waarde dan wordt de tweede berekening uitgevoerd en dan ontstaat er wel een kringverwijzing.
Oplossing: zorg dat de verwijzing in de formule naar de juiste cellen verwijst.

Een voorbeeld van een indirecte kringverwijzing: in cel J2 staat een verwijzing naar de cellen H2:H4. Niets geks.
Behalve als we kijken naar de inhoud van cel H4: =SOM(H2:J3), dus hierbij wordt ook impliciet verwezen naar de cel J2.

Dit soort fouten zijn vaak moeilijker te achterhalen. Maar gelukkig helpt Excel ons door een lijntje tussen de 2 cellen te tekenen. Dubbelklik hierop en Excel toont afwisselend de inhoud van de 2 betrokken cellen.

Toepassing Kringverwijzing

Zoals gezegd: meestal duidt het optreden van kringverwijzingen er op, dat er ergens in één of meer formules fouten staan. Maar soms wil je bewust gebruik maken van dit fenomeen.

Eigenlijk overal waar een eindstand van een periode weer gebruikt wordt als input voor de berekeningen in de volgende periode zouden we kringverwijzingen kunnen tegenkomen/gebruiken.

Bijvoorbeeld wanneer we het verloop van een hypotheek in de tijd willen berekenen, zullen we de schuldrest op het einde van de maand gebruiken om de rente en aflossing van de volgende maand te berekenen. Daardoor kennen we weer de schuldrest op het einde van die periode . Deze gebruiken we dan weer als input voor de volgende maand etc.

Een simpeler voorbeeld staat op het tabblad Corona van het Voorbeeldbestand .
De berekening kent twee input-variabelen: de zogenaamde R en het gemiddeld aantal dagen dat het duurt om een besmetting over te dragen.
Bij een R=2 en het aantal dagen=5 ligt, wanneer we op dag 0 met 1 besmetting starten, na 50 dagen het daaruit voorkomende aantal besmettingen al boven de 1.000.

In dit flink versimpelde model bepalen we het aantal besmettingen op het einde van een periode door de stand van de vorige periode te vermenigvuldigen met R.

Als we niet geïnteresseerd zijn in de tussenresultaten zouden we het eindresultaat (in het voorbeeld in cel G6) ook kunnen berekenen door het het vorige resultaat in die cel te vermenigvuldigen met de R-waarde.
Helaas: Excel voert die berekening normaal gesproken niet uit, want er ontstaat daar een kringverwijzing (zie op de statusbalk linksonder). En als die het wel zou kunnen/willen berekenen: wanneer moet die stoppen? Na 10 keer zoals in de berekening hiervoor of na 20 of na ???

NB1 in cel F6 staat de berekening =F6+GemOvDgn, dus ook daar ontstaat een kringverwijzing.

NB2 er ontstaan geen kringverwijzingen wanneer Opnieuw (de waarde in cel G2) gelijk is aan Ja. Dit gebruiken we om ons model op een standaard-waarde in te stellen (zie hierna).

We gaan de Excel-omgeving aanpassen zodat we de berekening in G6 WEL kunnen uitvoeren:

kies in de menutab Bestand de button Opties
klik op Formules en vul de Berekeningsopties in zoals hieronder, dus op handmatig berekenen en Iteratieve berekeningen inschakelen:
klik op OK

Nu kunnen we gaan rekenen:

vul cel G2 met de waarde Ja
we hebben Excel op Handmatig berekenen gezet, dus druk op F9 of klik onderaan in de statusbalk op Berekenen
geef cel G2 de waarde Nee
en kies opnieuw Berekenen:
telkens wanneer we herberekenen, verandert het resultaat:
wil je opnieuw beginnen? Vul cel G2 met de waarde Ja, wijzig eventueel de R en het aantal dagen in kolom C, vul G2 met Nee en kies Herberekenen zoals in stap 2.

NB in de Berekeningsopties hebben we het aantal Iteraties op 1 gezet. Iedere keer wanneer we herberekenen, gaat Excel dus alle cellen één keer langs om formules in die cellen te berekenen. Zet je het aantal Iteraties hoger dan voert Excel de berekeningen dat aantal keren achter elkaar uit.

LET OP vergeet niet de Berekeningsopties weer op Automatisch berekenen te zetten wanneer je met een ander spreadsheet aan de slag gaat!

Game of life

Het vorige voorbeeld laat goed de werking van Kruisverwijzingen/Iteraties zien. Een ander bekend (en zinvoller) voorbeeld is Conway’s Game of Life.

De Britse wiskundige Conway bedacht dit ‘spel’ in 1970 om de groei van een organisme na te bootsen, gebaseerd op het werk van John von Neumann.

In een 2-dimensionaal rooster heeft iedere cel óf de waarde Bewoond óf de waarde Onbewoond. Voor elke levenscyclus is iedere cel afhankelijk van de status van zijn 8 buren:

iedere bewoonde cel met minder dan 2 bewoonde buren sterft (onderbevolking)
iedere bewoonde cel met 2 of 3 bewoonde buren blijft bewoond
iedere bewoonde cel met meer dan 3 bewoonde buren sterft (overbevolking)
iedere onbewoonde cel met precies 3 bewoonde buren wordt bewoond (reproductie)

Hiernaast zie je de ‘vertaling’ van een patroon naar het aantal buren.

Onderstaande uitwerking van de Game of Life in Excel is gebaseerd op een artikel uit 2007: www.microsoft.com/…./iteration-conways-game-of-life/

Om het ‘levenspel’ in Excel te implementeren maken we de volgende afspraken:

voor het Conway-rooster gebruiken we natuurlijk het raster van een tabblad
waar Conway een oneindig groot rooster gebruikt, beperken we ons tot 30 rijen en 40 kolommen
in een ‘bewoonde’ cel plaatsen we een 1, anders een 0
via Voorwaardelijke opmaak vullen we een bewoonde cel met een groene kleur, anders wordt de cel wit

Om iedere levenssstap na te bootsen vertalen we de Conway-regels in een formule:

eerst als een cel bewoond is: als het aantal bewoonde buren 0 of 1 is dan wordt de cel 0, als dat aantal 2 of 3 is dan blijft de cel 1, anders wordt de cel 0. In een Excel-formule: KIEZEN(buren+1);0;0;1;1;0;0;0;0;0)
NB de laagste waarde van de eerste parameter moet 1 zijn, dus we verhogen het aantal buren met 1.
Iets compacter: KIEZEN(MIN(5;buren+1));0;0;1;1;0)
als we te maken hebben met een onbewoonde cel: ALS(buren=3;1;0)

Gecombineerd: ALS(celwaarde=1;KIEZEN(MIN(5;.buren+1);0;0;1;1;0);ALS(buren=3;1;0))

In het tabblad 1.Game van het Voorbeeldbestand staat een implementatie van het spel.

De bewoonde buren van een cel liggen vast in een ander tabblad (2.Buren).

Het aantal bewoonde buren van bijvoorbeeld cel E6 in het tabblad 1.Game liggen vast in cel E6 van het tabblad 2.Buren.

De gecombineerde formule hierboven wordt dan voor cel E6: =ALS(E6=1;KIEZEN(MIN(5;.’2.Buren’!E6+1);0;0;1;1;0);ALS(‘2.Buren’!E6=3;1;0))

Nu zijn we er bijna: we moeten ook nog een start-situatie kunnen vastleggen.

Cel C2 (met de naam Reset) geven we een waarde WAAR als we het rooster in de begin-situatie willen krijgen; we passen daarom de formules in het rooster aan:
=ALS(Reset;Basis!E6;ALS(E6=1;KIEZEN(MIN(5;’2.Buren’!E6+1);0;0;1;1;0);ALS(‘2.Buren’!E6=3;1;0)))
en leggen de gewenste begin-situatie in het tabblad Basis vast.

NB1 om het systeem te resetten kun je ook een vinkje plaatsen in het hokje in kolom E; dit selectievakje is gekoppeld aan cel E2.

NB2 och ja, vergeet niet de Berekeningsopties aan te passen zoals hierboven beschreven en het spreadsheet handmatig te herberekenen door bijvoorbeeld F9 in te drukken.

NB3 cel C3 (met de naam Iteratie) bevat een teller voor het aantal uitgevoerde iteraties vanaf een reset: =ALS(Reset;0;Iteratie+1)

Voorbeeld-run (zorg dat de Berekeningsopties goed staan):

vul de Reset-cel met de waarde WAAR (of vink het selectievakje aan)
herbereken het spreadsheet (F9 of Berekenen in de statusbalk aanklikken)
vul de Reset-cel met de waarde ONWAAR (of vink het selectievakje uit)
herbereken het spreadsheet (F9 of Berekenen in de statusbalk aanklikken)
herhaal stap 4 zo vaak als gewenst (dit is het resultaat na 6 iteraties)

Na 13 iteraties ziet het scherm er zo uit:

maar na 18 iteraties blijft een zogenaamde oscillator over:
<–>

NB1 dit voorbeeld eindigt in een 2-periode oscillator. Veel andere voorbeelden eindigen in een stabiele situatie (start bijvoorbeeld met een rijtje van 12 bewoonde cellen) of een leeg rooster (start met een rijtje van 6). Soms ontdek je een Life-game dat zich maar blijft ontwikkelen.

NB2 op internet zijn veel voorbeelden van dit spel te vinden (zie bijvoorbeeld en.wikipedia.org/wiki/…..Examples_of_patterns en playgameoflife.com).

NB3 Het rooster in het tabblad 1.Game (en alle anderen) is redelijk beperkt (30 x 40) maar voldoet voor de meeste bekende voorbeelden. Een mogelijke verbetering: pas op het tabblad 2.Buren de formules op de grenzen aan; vervang verwijzingen naar cellen buiten het rooster door verwijzingen naar cellen op de grens aan de andere kant van het rooster.

In het Voorbeeldbestand staan nog diverse tabbladen met mogelijke start-posities; de namen daarvan beginnen met Basis. Kopieer het rooster daaruit naar het tabblad Basis en herstart een run zoals hiervoor beschreven.

Wanneer je het tabblad Basis450 gebruikt zul je zien dat het heel lang duurt voordat er een stabiele situatie ontstaat. In dit soort gevallen is het handig om eerst het aantal iteraties per herberekeningsronde te verhogen in de Berekeningsopties:

NB In dit systeem hebben we gebruik gemaakt van de volgende werking van Excel: het herberekenen van cellen begint op het eerste tabblad, daarna het tweede etc. Dus pas als alle berekeningen op het tabblad 1.Game zijn uitgevoerd, worden de cellen van het tabblad 2.Buren berekend.

Niet-aaneengesloten bereiken

2 oktober 2021ExcelBereik, CSE, Indirect, RIJ, SOM.ALS, SOMMEN.ALSGijs Verbruggen

Voorbeeldbestand: Bereiken.xlsx Download

Een niet-aaneengesloten bereik is een selectie van meerdere cellen waarbij ieder gedeelte uit één of meer cellen kan bestaan, maar waarbij die cellen niet allemaal op elkaar aansluiten.

Hieronder gaan we kijken hoe je dit soort bereiken kunt selecteren en wanneer je ze in formules wèl, maar zeker ook wanneer je ze niet kunt gebruiken. En uiteraard zoeken we ook oplossingen voor dat laatste.

Cellen selecteren

Een aaneengesloten gebied van cellen selecteren gaat het makkelijkst met behulp van het toetsenbord: gebruik de cursor-toetsen om de eerste cel te selecteren, hou Shift ingedrukt en gebruik dan weer de cursortoetsen om het hele gebied te selecteren.

Wanneer je een niet-aaneengesloten gebied wilt gebruiken dan zul je de muis ter hand moeten nemen:

selecteer de eerste cel van het bereik door met de linker muisknop te klikken
met de linkermuisknop ingedrukt kun je eventueel nog meer AANSLUITENDE cellen selecteren
hou de Ctrl-toets ingedrukt en selecteer ergens anders één of meer aaneengesloten cellen
herhaal punt 3 zoveel als nodig

Functies en niet-aaneengesloten bereiken

Op het tabblad SubTot van het Voorbeeldbestand vindt u een kwartaaloverzicht van enkele afdelingen.

In kolom G3 staat het jaartotaal van de afdeling Dir:

tik in =som(
klik met de linkermuisknop op cel C3
ga, met de muisknop ingedrukt, naar cel F3
druk op Enter

NB1 Excel vult automatisch het haakje-sluiten aan

NB2 tussen haakjes komt automatisch C3:F3, ofwel alle cellen van C3 tot en met F3

NB3 een snellere methode om van een naast-gelegen bereik de Som te bepalen: plaats de cursor in cel G3 en druk op de toetscombinatie Alt en =

Maar wanneer we alleen de cellen opgeteld willen hebben waarvan de waardes groter dan 10 zijn dan wordt het lastiger:

tik in cel H3 =som(
klik met de linkermuisknop op cel D3
ga, met de muisknop ingedrukt, naar cel E3
druk op Enter

Nu voor cel H4:

we beginnen weer met =som(
klik op cel D4
klik dan, met Ctrl ingedrukt, op cel F4
druk op Enter
het resultaat is: =SOM(D4;F4)

NB tussen haakjes staat nu een ; als scheidingsteken. Voor Excel betekent dit dat aan de functie 2 parameters worden meegegeven. In dit geval bestaat iedere parameter uit 1 cel.

Voor cel H5:

start met =som(
klik op cel C5
klik dan, met Ctrl ingedrukt, op cel E5
verschuif de cursor, nog steeds Ctrl ingedrukt, naar F5
druk op Enter
het resultaat is: =SOM(C5;E5:F5)

NB ook hier worden aan de Som-functie 2 parameters meegegeven; de eerste bestaat uit 1 cel, de tweede uit een bereik van meerdere cellen.

Op een vergelijkbare manier kun je ook het totaal van alle aantallen groter dan 10 bepalen (zie cel C9).

NB1 uiteraard is de berekening in cel C10 een stuk eenvoudiger: =SOM(H3:H7)

NB2 plaats de cursor achter de formule en je hebt een gekleurd controlemiddel om te zien of de juiste cellen zijn geselecteerd:

In cel C12 is te zien, dat ook de functie Gemiddelde met niet-aaneengesloten bereiken overweg kan.

LET OP: voor deze functie kun je NIET de kortere formule =GEMIDDELDE(H3:H7) gebruiken!

Maar …. bovenstaande formules werken alleen maar in een statische situatie. Wanneer de kwartaalcijfers nog kunnen wijzigen hebben we een probleem; dan moeten alle formules gecontroleerd en eventueel aangepast worden.

In het tabblad Aselect van het Voorbeeldbestand ziet u een formule waarbij wijzigingen in de brongegevens geen problemen meer opleveren:
=SOM.ALS(C3:F3;”>10″)

Ofwel: sommeer alle getallen in het bereik C3:F3 die groter dan 10 zijn.

LET OP de voorwaarde waaraan de cellen moeten voldoen, moet tussen aanhalingstekens staan. Wel kan de voorwaarde in een cel worden opgenomen; dan blijven de aanhalingstekens achterwege; zie cel H3: =SOM.ALS(C3:F3;$G$2)

Het bepalen van de Som of Gemiddelde van alle waardes onder een voorwaarde is op deze manier ook een stuk eenvoudiger: =GEMIDDELDE.ALS(C3:F7;”>10″)

Bij voorgaande Als-formules was het bereik waar de voorwaarde op los gelaten moest worden aaneengesloten. Wanneer dat niet het geval is hebben we een probleem!

Probleem

In het tabblad Probleem van het Voorbeeldbestand zien we een projectenoverzicht over de maanden. Per maand is er een Raming en een Actueel resultaat. Delta is het verschil tussen die twee (Actueel – Raming). Wanneer de Actuele stand kleiner is dan de Raming is dat gunstig (dus als Delta < 0 is).

NB De Delta-kolommen hebben een voorwaardelijke opmaak gekregen.

Per project willen we het totaal van de negatieve Delta’s weten.

In cel S4 staat een formule, die dit probleem zou kunnen tackelen. De Raming en Actueel-kolommen zijn immers altijd positief (of 0): =SOM.ALS(C4:R4;”<0″)

Maar als we de Correctie-kolom gaan gebruiken en daar komen negatieve waardes voor, dan werkt de formule niet meer als verwacht/gehoopt.

We zouden dan iets willen gebruiken als =SOM.ALS( (E4;I4;M4;Q4) ;”<0″ )
Dus als eerste parameter geen aaneengesloten bereik, maar losse cellen.

Helaas! Excel raakt hier de weg kwijt. Deze functie is heel strikt: de eerste parameter moet een bereik zijn, de tweede moet de voorwaarde bevatten. En de tweede parameter begint achter de eerste punt-komma.

Gelukkig kunnen we dit probleem wel oplossen met de formule in T4:
=SOMMEN.ALS(C4:R4;C4:R4;”<0″;C$3:R$3;”Delta”)
Neem de Som van alle cellen uit C4:R4 (de eerste parameter), waarbij de cellen in C4:R4 (tweede parameter) kleiner zijn dan 0 en de cellen C3:R3 de waarde Delta bevatten.

LET OP bij de cellen C3:R3 is de rij-aanduiding absoluut gemaakt (zie $-tekens), zodat er bij het naar beneden kopiëren de formule naar de juiste rij blijft verwijzen.

Wat te doen, als de kopregel niet zo’n handige aanduiding van de betreffende kolommen heeft? Dan hebben we nog een ‘leuk’ alternatief (zie cel U4):

LET OP dit is een zogenaamde CSE-, array– of matrix-formule; deze wordt niet afgesloten door op Enter te drukken maar tegelijkertijd Ctrl-Shift-Enter. Excel plaats dan automatisch de accolades rond de formule.

Korte uitleg: door de CSE-methode ‘dwingen’ we Excel om (in dit geval) 4 keer de Als-functie uit te voeren; telkens met een andere kolom door middel van de Kiezen-functie.

Functies en niet-aaneengesloten bereiken (conclusie)

Wanneer kun je niet-aaneengesloten bereiken gebruiken binnen een functie?

Kort door de bocht: alleen bij de simpele rekenkundige functies als Som, Aantal, Gemiddelde etcetera.
Bij het invoeren van dit soort functies zie je dat je meer dan 1 dezelfde soort parameter kunt opgeven.

NB de rechte haken geven aan dat de tweede parameter optioneel (niet verplicht) is. De … geven aan, dat er nog meer parameters opgegeven kunnen worden.

Bij andere functies zie je dat de verschillende parameters ieder een ‘andere’ rol vervullen. Logisch dat je dan niet een willekeurig aantal bereiken als 1 parameter kunt opgeven.

Vreemd eigenlijk: als je naar het scherm met Functieargumenten van Som kijkt (gebruik de button naast de formulebalk), dan zie je als toelichting dat je getallen als parameter kunt opgeven. Nergens een woord over bereiken!

Waarschijnlijk toch de meest gebruikte optie van Excel.

Toetje

Voor de liefhebbers bevat het Voorbeeldbestand nog enkele sheets waarmee de opzet van de ‘Probleem’-sheet op een geheel andere wijze wordt aangepakt; de basisgegevens worden in een database-vorm ingevoerd. Het resultaat-overzicht wordt daardoor een stuk flexibeler.

Functie Indirect

30 juni 2021Excel, VBA (Visual Basic for Applications)Adres, Gegevensvalidatie, Indirect, RIJ, TekstGijs Verbruggen

Voorbeeldbestand: Indirect.xlsx Download

LET OP: na het downloaden de extensie van 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 Adres gebruikt.

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.

G-Info

van gegevens naar informatie