Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Bestand crasht Excel!!

Red mijn werkmap!
De beste tool voor Excel bestanden met problemen.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Kringverwijzingen > Instellen
This page in English

Kringverwijzingen in Excel

Op de juiste manier met kringverwijzingen werken

Hoewel ik geen voorstander ben van modellen met kringverwijzingen kan dat toch de methode zijn waarmee je je rekenvraagstuk het beste oplost. Daarom hier advies over hoe je op een goede manier met kringverwijzingen moet omgaan.

De kring breken en beginwaarden instellen

Om te voorkomen dat er problemen in je model ontstaan is het handig om een snelle manier te hebben om de kringverwijzing te doorbreken. Kringverwijzingen kunnen namelijk risicovol zijn in die zin, dat als er ergens in de kring een foutwaarde ontstaat (bijvoorbeeld #DEEL/0!) alle cellen in de kring deze foutwaarde overnemen. Het kan zijn dat het dan onmogelijk wordt het model uit deze fouttoestand te krijgen.

Wat je hiertegen kunt doen is (ten minste) één cel voorzien van een ALS functie, welke op zijn beurt de waarde uit een andere cel gebruikt om te bepalen of de kring open of gesloten mag zijn. Zodra deze "schakelcel" een (bepaalde) waarde, bevat breekt de ALS functie de kring en neemt deze de waarde over van een cel waarin je een beginwaarde voor de gehele keten hebt ingevuld.

Stel dat je de onderstaande hypothetische kringverwijzing hebt gemaakt:

Een simpele kringverwijzing
Een simpele kringverwijzing

Om dan het gedrag te krijgen dat ik al beschreef, vervang je de formule in cel C3 door:

=ALS($B$1=1;$B$2;D4)

Je werkblad zou er nu zo uit moeten zien:

Een simpele kringverwijzing die gebroken kan worden
Een simpele kringverwijzing die gebroken kan worden

Test nu je breekbare kring door in cel B1 een 1 in te voeren. Cel C3 krijgt daarmee de beginwaarde door uit cel B2. Maak cel B1 weer leeg om de kring te herstellen.

Je kan ook de schakelcel combineren met de beginwaardecel. Pas de formule in C3 aan naar:

=ALS($B$1="";D4;$B$1)

Nu kan je door in cel B1 een waarde in te vullen direct de beginwaarde doorgeven aan het model. Maak cel B1 weer leeg om de iteratie te starten. Nadeel van deze methode is dat je de beginwaarde van de iteratie hiermee telkens weggooit als je de iteratie wilt starten.

Convergeert het model?

Een groot probleem met modellen met kringverwijzingen is dat je goed in de gaten moet houden of het model onder alle omstandigheden stabiele resultaten oplevert. Iteratieve berekeningen kunnen zich in diverse toestanden bevinden:

  1. De berekening convergeert

    Een convergerende berekening bereikt een stabiel eindresultaat waarbij de resultaten van de laatste iteratiestap weinig tot niet afwijken van de voorlaatste. Dit verschil zou, kleiner of gelijk moeten zijn aan het "Maximum verschil" dat is ingesteld in de berekeningsopties.

  2. De berekening divergeert

    Als de berekening divergeert dan loopt de boel uit de hand: de waarden in het model worden groter en groter of negatiever en negatiever, zonder dat ooit een stabiel eindresultaat wordt gevonden. Het "model" dat hierboven wordt getoond is daar een voorbeeld van. Het komt ook voor dat een model afwisselend een (steed groter) positief en negatief resultaat geeft. Een dergelijk model wordt ook als een divergerend model beschouwd.

  3. Een oscillerend model

    Het model schakelt telkens tussen twee waarden.

  4. Het resultaat verandert niet meer maar is fout

    Dit is de lastigste situatie om te detecteren, juist omdat je de indruk zal hebben dat het model een oplossing heeft bereikt. Ik adviseer dan ook om onder ALLE omstandigheden je resultaten goed te controleren; zijn ze wel juist?

Het staat buiten kijf dat je altijd een convergerend model wilt hebben.

Een manier om op convergentie te controleren is door het aantal iteraties op 1 in te stellen. Daardoor kan je door telkens op F9 te drukken 1 iteratie uitvoeren. Kijk goed naar de waarden om te zien welke kant het uitgaat.

Een alternatieve benadering: laat VBA de kringverwijzingen controleren

Een alternatieve manier om kringverwijzingen te besturen is door VBA in te schakelen om de iteratie te regelen.

De manier die ik hier wil voorstellen behelst het breken van de kringverwijzing(en) om vervolgens tussen-cellen te gebruiken waarbij door middel van wat VBA code de waarde van deze tussen-cellen aan de volgende cel van de kring wordt doorgegeven. Door de begin en eindwaarden te laten bijhouden kan je in VBA precies volgen hoe de iteratie verloopt en zo nodig ingrijpen. De Engelse pagina van dit artikel laat zien hoe dat werkt.

 



Vragen, suggesties en opmerkingen

Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: www.eileenslounge.com.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].