Kringverwijzingen in Excel

Inhoud

• Inleiding
• Soorten kringverwijzingen
• Berekeningsinstellingen
• Op de juiste manier met kringverwijzingen werken
• De kringverwijzingen lokaliseren
• Waarom kringverwijzingen soms niet worden gedetecteerd

Inleiding

Waarschijnlijk heb je deze pagina gevonden nadat Excel die vervelende mededeling heeft getoond over kringverwijzingen toen je een bestand opende, of nadat je een formule had ingevoerd. Excel detecteert een kringverwijzing zodra een reeks formules ertoe leidt dat dezelfde cel meer dan eens in serie berekeningen wordt aangedaan. Veel gebruikers vinden de kringverwijzingen melding uiterst verwarrend en hebben geen idee waardoor die verschijnt. In dit artikel tracht ik het mysterie omtrent deze situatie weg te nemen.

Soorten kringverwijzingen

Eigenlijk zijn er maar enkele soorten kringverwijzingen te onderscheiden:

Opzettelijke kringverwijzingen

De belangrijkste overweging die je moet maken is of het model dat je aan het maken bent eigenlijk wel een echte kringverwijzing is. Vaak is er een manier om de berekening zo te herschrijven dat de kringverwijzing verdwijnt. Probeer altijd eerst of dit mogelijk is, voordat je daadwerkelijk in Excel gaat werken.

Sommige gebruikers maken expres (series van) kringverwijzingen om een rekenmodel iteratieve berekeningen uit te laten voeren. Voorbeelden van zulke modellen zijn modellen van chemische processen waarbij een (deel van) een voedingsstroom wordt gevormd door een gerecyclede uitvoerstroom van hetzelfde proces. Destillatieprocessen hebben vaak van dergelijke "loops" en als je zo'n proces wilt modelleren dan kan het gebruik van een kringverwijzing nuttig of zelfs noodzakelijk zijn.

Gebruik kringverwijzingen alleen als je ervan overtuigd bent dat ze noodzakelijk zijn. Moet je ze gebruiken, zorg er dan voor dat je rekenmodel goed is gedocumenteerd, liefst in de spreadsheet zelf. Maak daarbij duidelijk wat je bedoelingen zijn en hoe het model functioneert. Het is daarbij vooral belangrijk dat je duidelijk aangeeft wat de kring in het model precies is.

Er zijn enkele subtypes kringverwijzingen te onderscheiden. Ik geef ze hier alleen weer onder de "Opzettelijke" kringverwijzingen, aannemende dat dat de enige situatie is waarbij je zou willen weten wat die subtypes zijn.

Cellen die naar zichzelf verwijzen

Het eenvoudigste type kringverwijzing is die waarbij een cel naar zichzelf verwijst. Dit wordt soms gebruikt wanneer men de waarde in een cel alleen onder bepaalde omstandigheden wil laten wijzigen. Een voorbeeld:

Stel dat je bij wilt houden wanneer een bepaalde waarde in een cel is ingevoerd, zonder gebruik te maken van VBA macro's. Een manier is om daarbij gebruik te maken van de functie VANDAAG in combinatie met de ALS functie:

Een cel die naar zichzelf verwijst

Zodra er iets in cel A1 wordt ingevuld zal het resultaaat van de test in de ALS functie ONWAAR worden waardoor de formule in cel B1 naar zichzelf gaat verwijzen. Daarmee zorgt de formule ervoor dat de huidige datum alleen in de cel wordt gezet als cel A1 leeg is.

Enkelvoudige kringen

Een ekelvoudige kringverwijzing ontstaat als iedere cel in de kring er slechts één keer in voorkomt. Een voorbeeld van een dergelijke kring staat hieronder:

Een enkelvoudige kring van cellen die naar elkaar verwijzen

Cel C2 gebruikt de waarde uit cel D3, die weer de waarde haalt uit C4, die de waarde haalt uit B3, welke laatste dan weer cel C2 gebruikt, waarmee de cirkel compleet is.

Meervoudige kringen

Om de zaak nog wat lastiger te maken, cellen kunnen deel uitmaken van meerdere kringen. Zie onderstaand voorbeeld:

Meervoudige kringen

In dit voorbeeld zijn er eigenlijk twee kringen. De eerste is A-B-C-D-E-F-A (de groene pijlen). De tweede kring is A-B-C-D-E-G-A, de rode pijlen. Het is erg lastig om dergelijke meervoudige kringen te ontdekken.

Merk op, dat kringverwijzingen meerdere werkbladen kunnen omvatten en zelfs meerdere bestanden (dat laatste moet je proberen tot elke prijs te voorkomen!). Heb je dan toch een kringverwijzing nodig, zorg er dan voor dat de cellen van de kring zo dicht mogelijk bij elkaar staan. Liefst allemaal zichtbaar op hetzelfde scherm. Als je dat doet maak je het jezelf makkelijker om eventuele problemen op te sporen.

Niet opzettelijke kringverwijzingen

Vaker dan opzettelijk worden kringverwijzingen per ongeluk gemaakt. Bijvoorbeeld doordat men een SOM functie maakt waarbij de cel van de SOM functie zelf in het argument wordt meegenomen.

Voorbeeld: In cel A10, schrijf je de functie : =SOM(A1:A10)

Uiteraard is het de bedoeling dat in deze cel de cellen erboven worden opgeteld, dus eigenlijk had het argument van de som functie A1:A9 moeten zijn.

Berekeningsinstellingen

Als je kringverwijzingen wilt gebruiken, dan zijn de berekeningsopties van Excel erg belangrijk. Op deze pagina leer je hier meer over.

Berekeningsopties

Het allereerste dat je moet doen om een werkend model met kringverwijzingen te krijgen is het aanzetten van iteratieve berekeningen.

Iteratie instellingen Excel 2010

Natuurlijk is het aan jou om te bepalen hoeveel iteraties er per berekening moeten worden gedaan voordat Excel met itereren stopt, of welke precisie je model nodig heeft (Excel stopt of als de precisie is bereikt, of als het aantal iteraties is bereikt!). Zodra je het vakje "Iteratieve berekening inschakelen" aanvinkt zal Excel het model herberekenen. Als je het bestand opslaat en vervolgens sluit en weer opent zou je geen waarschuwing meer moeten krijgen omtrent kringverwijzingen.

Als je het model nog aan het controleren bent, zet het maximum aantal iteraties dan op 1, dan kan je de iteraties stap voor stap doorlopen door telkens op de F9 toets te drukken.

Welke Berekeningsopties zijn van toepassing

Vaak krijg is deze vraag: Ik heb bij mijn bestand "Iteratieve berekening inschakelen" aangevinkt, het bestand opgeslagen en toch krijg ik als ik mijn bestand open soms de waarschuwing over kringverwijzingen en als ik dan onder opties kijk staat die optie toch uit. Hoe komt dit? Om dit te begrijpen is het belangrijk om te weten hoe Excel omgaat met de Berekeningsopties.

Een instelling voor de gehele applicatie

Berekeningsopties gelden altijd voor de gehele Excel sessie, dus voor alle op dat moment openstaande werkmappen. Dit betekent als je twee mappen open hebt staan, map A met handmatige berekening en map B met automatische berekening, één van deze twee instellingen geldig is. De huidige instelling zal voor beide mappen gelden. Dit geldt ook voor de iteratie instellingen, die gelden voor alle openstaande bestanden in die Excel sessie.

Zodra je een werkmap opslaat sla je ook de berekeningsopties op met die map.

Wie het eerst komt...

Excel zal de berekeningsopties instellen zodra je de eerste map opent. Dit doet Excel ook als je een bestand opent nadat je de laatste werkmap in een Excel sessie sluit. Dus als je eerst werkmap A opent (die iteratie uit had staan toen die werd opgeslagen) en daarna map B (met iteratie ingeschakeld toen die werd opgeslagen), dan zal Excel de iteratie uit hebben staan. Dit verklaart waarom je bij het openen van het tweede bestand toch die kringverwijzingen waarschuwing krijgt.

Waarschuwing: als je je bestand opslaat, worden de berekeningsopties opgeslagen die nu actueel zijn. Dit betekent dat als je eerder voor de werkmap de iteratie had ingesteld, deze waarden kunnen worden overschreven door de huidige instellingen.

Ervoor zorgen dat je de juiste berekeningsopties hebt

Er zijn verschillende manieren om ervoor te zorgen dat je de juiste berekeningsopties hebt zodat je werkmap zich gedraagt zoals je gewend bent:

Altijd als eerste bestand openen

Een open deur misschien, maar vaak het gemakkelijkst!. Maar natuurlijk kan het zijn dat andere mensen ook van jouw model gebruik maken. Dan is deze methode niet echt betrouwbaar. Je zou dan dus alle gebruikers moeten instrueren om alleen jouw bestand te openen en geen andere bestanden. Bovendien moet jouw bestand EERST geopend worden. Dat gaat gegarandeerd een keer fout. Je loopt dan ook nog eens de kans dat je berekeningsopties worden overschreven met verkeerde waarden, waardoor je model niet goed meer zal functioneren.

Wat VBA gebruiken om de berekeningsopties te beheren

Een betrouwbaardere manier om de berekeningsopties te beheersen is door ze in te stellen zodra je werkmap wordt geopend. Dit betekent het toevoegen van een macro aan je bestand, maar dat is niet moeilijk.

Ik neem even aan dat je bestand met kringverwijzingen al open staat.

Druk op alt+F11 om de VBA editor to openen en kijk aan de linkerkant in het venster. Daar moet de projectverkenner zichtbaar zijn. Dubbelklik op de ThisWorkbook module::

De Project verkenner in de VBA Editor

Plak deze code in het code venster dat vervolgens opent en pas de berekeningsopties aan zodat ze overeenkomen met je huidige instellingen in Bestand, Opties:

Sla nu je bestand op (en als je Excel 2007 of hoger gebruikt, zorg er dan voor dat je het bestandstype aanpast naar eentje die Macro's kan bevatten,anders zal Excel de macro code weer weggooien als je het bestand sluit!)

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

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

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.

De kringverwijzingen lokaliseren

Excel tracht wel kringverwijzingen te tonen. Helaas geeft Excel alleen de allereerste kringverwijzing aan die het in de berekeningsketen tegenkomt. Die informatie is beschikbaar in het lint op de tab Formules, bij de Foutencontrole keuzelijst:

Als je op zoek bent naar een eenvoudige manier om alle kringverwijzingen in een werkmap te vinden, download dan mijn RefTreeAnalyser utility, die alle kringen in een overzicht toont:

Waarom kringverwijzingen soms niet worden gedetecteerd

Foutwaarden als resultaat

Soms kan een cel in de kring een foutwaarde bevatten. Het kan zijn, dat Excel dan ineens niet meer ziet dat er een kringverwijzing is. Ik ben er helaas niet in geslaagd een bestand te maken dat in die toestand verkeerd.

Waarden in cellen bepalen of een kring echt als kring wordt gezien

Als één van de cellen van de kring een functie bevat die bepaalt welke cellen in die functie gebruikt moeten worden (bijvoorbeeld door de ALS of de KIEZEN functie) , dan zal het van die functie afhangen of Excel de kring nog detecteert. Ik heb van deze situatie gebruik gemaakt om schakel cellen in te stellen, waarmee je zo nodig de kring kunt breken en beginwaarden aan je model kunt geven.

Gebruik van argumenten in VBA UDF's

Excel is slim. Erg slim. Stel je hebt een VBA User Defined Function (UDF) geschreven met twee argumenten. Excel is slim genoeg om te detecteren welke van die twee argumenten je echt gebruikt in de berekening. Daarmee zal de struktuur van je UDF en de waarde van de argumenten kunnen bepalen of het model een kringverwijzing heeft ja of nee. Zorgt een bepaalde beslissingsstruktuur in je UDF ervoor dat de waarde van 1 van de argumenten niet gebruikt wordt, dan breekt de kringverwijzing als die waarde toevallig deel uitmaakte van de kring.

#Naam fouten bij ontbrekende UDF's

Als een willekeurige cel in een kring verwijst naar een UDF die niet beschikbaar is, wordt de kring niet gedetecteerd.

Berekeningsstatus

Wanneer een werkmap wordt geopend zullen de berekeningsopties ervan afhangen of er al een werkmap open stond of niet. Als je eerst een map hebt geopend die iteratie uit heeft staan, en dan je map met de kringverwijzing, dan krijg je de waarschuwing over kringverwijzingen te zien. Als de eerste map doe je opende iteratie ingeschakeld heeft, dan krijg je deze waarschuwing bij alle volgende bestanden die je opent niet meer te zien.