Kringverwijzingen in Excel
Inhoud
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:
Option Explicit
Private Sub Workbook_Open()
With Application
.Calculation = xlCalculationAutomatic
.Iteration = True
.MaxIterations = 100
.MaxChange = 0.001
End With
End Sub
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.
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:
- 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.
- 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.
- Een oscillerend model
Het model schakelt telkens tussen twee waarden.
- 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.
