Iteratieve berekening, vriend of vijand?

Eén van de eindeloze mogelijkheden van Excel is de ‘iteratieve berekening’. Deze weinig bekende en onschuldig ogende optie verandert de manier waarop Excel formuleresultaten berekent fundamenteel. De functie kan nuttig zijn, maar is niet zonder risico. Laten we eens kijken wat het doet, hoe je het gebruikt en waar je rekening mee moet houden voordat het wordt ingeschakeld.

Hoe Excel berekeningen uitvoert

Iedere Excel-gebruiker weet dat formuleresultaten automatisch opnieuw berekend worden wanneer er iets verandert in een van de cellen waar in de formule naar verwezen wordt. Als de cellen waarnaar wordt verwezen ook formules bevatten, dan zal Excel die plichtsgetrouw als eerste berekenen. Op deze manier kan een enkele celbewerking, zelfs in een bescheiden spreadsheetmodel, ervoor zorgen dat een hele reeks formules opnieuw berekend wordt. Erg nuttig!

Maar wat gebeurt er als, in deze reeks formules, een formule wordt aangetroffen die al eerder in dezelfde reeks werd aangedaan? Neem het eenvoudige geval waarin een formule in cel A1 verwijst naar cel B1 en cel B1 een formule bevat die terugverwijst naar cel A1. Wat moet Excel dan doen? Het is net als de oude grap in het woordenboek:

Wat Excel moet doen, hangt sterk af van de bedoeling van de berekening. Vaak glippen kringverwijzingen namelijk per ongeluk een spreadsheet in. In dergelijke gevallen moeten de formules gewoon worden verbeterd. Maar wat te doen als je bewust een circulaire afhankelijkheid wil inbouwen?

Is een kringverwijzing niet altijd een vergissing?

Een circulaire afhankelijkheid lijkt misschien een contra-intuïtief, maar de noodzaak om terug te gaan naar een eerder resultaat komt relatief vaak voor. Het meest genoemde voorbeeld is een berekening van samengestelde rente.

Probeer het uit en Excel zal een foutmelding geven. Het bericht “we hebben één of meerdere circulaire verwijzingen gevonden” is berucht onder Excel-gebruikers, vooral als de kringverwijzing onbedoeld is. Toch is het protest van Excel terecht: als het gewoon doorging met het vinden van het antwoord, dan zou het voor altijd aan het rekenen blijven. Idealiter, als de circulaire afhankelijkheid opzettelijk is, zou je het na een tijdje willen laten stoppen. Maak kennis met de *iteratieve berekening*.

Als de iteratieve berekeningsoptie is ingeschakeld, kun je specificeren hoe vaak (hoeveel iteraties) Excel een formuleketen doorloopt om een ​​resultaat te berekenen, meestal om te convergeren naar een uitkomst van acceptabele precisie.

Iteratieve berekening: een eenvoudige user case

Hier is een interessante user-case: we maken een tabel met twee kolommen waarbij de tweede kolom automatisch een timestamp invoegt wanneer er iets wordt toegevoegd, een soort ‘aanmaakdatum’ voor tabelrijen.

Merk op dat een gewone Excel-formule dit niet kan. Als je iets als IF(ISBLANK(A1), “”, NOW()) zou proberen, dan zou de timestamp altijd worden bijgewerkt, zelfs als er eerder een was gegenereerd. Alle timestamps geven altijd de laatste bewerking weer:

In plaats daarvan moet de formule het volgende doen:

1. Als de formulecel al een timestamp bevat, moet deze blijven staan.
2. Als er nog geen timestamp is, controleer dan of er een moet worden aangemaakt.

Zonder iteratieve berekening zou stap 1 onmogelijk zijn. Dus volg eerst de volgende stappen: Bestand > Opties > Formules > Iteratieve berekening inschakelen en voer vervolgens de volgende formule in kolom B:B in:

=IF(AND(B1<>””, B1<>0), B1, IF(ISBLANK(A1), “”, NOW()))

Vergeet ook niet om de getalnotatie in te stellen op ‘tijd’:

Dat is alles! Voeg maar eens wat gegevens toe aan de kolommen A-C en bekijk de timestamps die worden toegevoegd. Eenmaal gepubliceerd mogen ze niet meer veranderen.

Voorbehoud bij het gebruik van iteratieve berekening

Omdat toevallige kringverwijzingen een veelvoorkomende bron van frustratie zijn in de Excel-wereld, raken sommigen in de verleiding om iteratieve berekening in te schakelen om een ​​spreadsheet werkend te houden zonder de oorzaak van het probleem te hoeven oplossen. Het spreekt voor zich dat dit geen goede gewoonte is.

Zelfs wanneer iteratieve berekening bewust is ingeschakeld, is dit niet zonder risico. Niet iedereen is bekend met de optie, en zelfs degenen die weten dat de optie bestaat, weten misschien niet dat deze is ingeschakeld. De meeste spreadsheets vereisen immers geen iteratieve berekening.

Het risico

Dus wat is het risico? Hoewel je met iteratieve berekening expres circulaire afhankelijkheden kunt instellen, maskeert het ook eventueel onbedoelde. Als je iteratieve berekening moet gebruiken, zorg er dan voor dat het goed gedocumenteerd is en dat het model grondig wordt gecontroleerd op onbedoelde kringverwijzingen.

Tip: Handige tools zoals de PerfectXL Risk Finder kunnen controleren op kringverwijzingen, zelfs als de iteratieve berekening optie is ingeschakeld.