Tijdens het werken met Excel-formules kunt u soms de volgende waarschuwing zien.
Deze prompt vertelt u dat er een kringverwijzing in uw werkblad staat en dit kan leiden tot een onjuiste berekening door de formules. Het vraagt u ook om dit kringverwijzingsprobleem aan te pakken en te sorteren.
In deze zelfstudie behandel ik alles wat u moet weten over kringverwijzing, en ook: hoe kringverwijzingen in Excel te vinden en te verwijderen.
Dus laten we beginnen!
Wat is circulaire verwijzing in Excel?
In eenvoudige bewoordingen, een kringverwijzing vindt plaats wanneer u een formule in een cel krijgt - die op zichzelf de cel (waarin deze is ingevoerd) voor de berekening gebruikt.
Laat me dit proberen uit te leggen aan de hand van een eenvoudig voorbeeld.
Stel je hebt de dataset in cel A1:A5 en je gebruikt de onderstaande formule in cel A6:
=SOM(A1:A6)
Dit geeft u een kringverwijzingswaarschuwing.
Dit komt omdat u de waarden in cel A1:A6 wilt optellen, en het resultaat zou in cel A6 moeten staan.
Dit creëert een lus omdat Excel gewoon de nieuwe waarde in cel A6 blijft toevoegen, die blijft veranderen (vandaar een cirkelvormige referentielus).
Hoe circulaire verwijzingen in Excel te vinden?
Hoewel de waarschuwingsprompt voor circulaire verwijzing vriendelijk genoeg is om u te vertellen dat deze in uw werkblad bestaat, vertelt het u niet waar het gebeurt en welke celverwijzingen het veroorzaken.
Dus als u kringverwijzingen in het werkblad probeert te vinden en te verwerken, moet u een manier weten om deze op de een of andere manier te vinden.
Hieronder vindt u de stappen om een kringverwijzing in Excel te vinden:
- Activeer het werkblad met de kringverwijzing
- Klik op het tabblad Formules
- Klik in de groep Formule bewerken op het vervolgkeuzepictogram Foutcontrole (klein naar beneden wijzend pijltje aan de rechterkant)
- Beweeg de cursor over de optie Circulaire verwijzingen. Het toont u de cel met een kringverwijzing in het werkblad
- Klik op het celadres (dat wordt weergegeven) en u gaat naar die cel in het werkblad.
Nadat u het probleem hebt opgelost, kunt u opnieuw dezelfde stappen hierboven volgen en worden meer celverwijzingen weergegeven met de kringverwijzing. Als er geen is, ziet u geen celverwijzing,
Een andere snelle en gemakkelijke manier om de kringverwijzing te vinden, is door naar de statusbalk te kijken. Aan de linkerkant ervan toont het u de tekst Circular Reference samen met het celadres.
Er zijn een paar dingen die u moet weten als u met kringverwijzingen werkt:
- Als de iteratieve berekening is ingeschakeld (later in deze zelfstudie behandeld), zal de statusbalk het adres van de circulaire referentiecel niet tonen
- Als de kringverwijzing niet in het actieve blad staat (maar in andere bladen in dezelfde werkmap), wordt alleen de kringverwijzing weergegeven en niet het celadres
- Als u eenmaal een waarschuwingsprompt voor kringverwijzingen krijgt en deze negeert, wordt de prompt de volgende keer niet meer weergegeven.
- Als u een werkmap opent met de kringverwijzing, wordt de prompt weergegeven zodra de werkmap wordt geopend.
Hoe een circulaire verwijzing in Excel te verwijderen?
Als je eenmaal hebt vastgesteld dat er kringverwijzingen in je blad staan, is het tijd om deze te verwijderen (tenzij je wilt dat ze daar met een reden staan).
Helaas is het niet zo eenvoudig als het indrukken van de delete-toets. Aangezien deze afhankelijk zijn van formules en elke formule anders is, moet u dit van geval tot geval analyseren.
In het geval dat de celverwijzing per ongeluk het probleem veroorzaakt, kunt u dit eenvoudig corrigeren door de verwijzing aan te passen.
Maar soms is het niet zo eenvoudig.
Cirkelverwijzing kan ook worden veroorzaakt op basis van meerdere cellen die op veel niveaus in elkaar overgaan.
Laat me je een voorbeeld laten zien.
Hieronder staat een kringverwijzing in cel C6, maar het is niet alleen een eenvoudig geval van zelfverwijzing. Het is multi-level waarbij de cellen die het in de berekeningen gebruikt ook naar elkaar verwijzen.
- De formules in cel A6 zijn =SUM(A1:A5)+C6
- De formule is cel C1 is =A6*0.1
- De formule in cel C6 is =A6+C1
In het bovenstaande voorbeeld is het resultaat in cel C6 afhankelijk van de waarden in cel A6 en C1, die op hun beurt afhankelijk zijn van cel C6 (waardoor de kringverwijzingsfout wordt veroorzaakt)
En nogmaals, ik heb een heel eenvoudig voorbeeld gekozen, alleen voor demo-doeleinden. In werkelijkheid kunnen deze vrij moeilijk te achterhalen zijn en misschien ver weg in hetzelfde werkblad of zelfs verspreid over meerdere werkbladen.
In zo'n geval is er één manier om de cellen te identificeren die kringverwijzing veroorzaken en deze vervolgens te behandelen.
Het is door de optie Trace Precedents te gebruiken.
Hieronder vindt u de stappen om traceerprecedenten te gebruiken om cellen te vinden die worden ingevoerd in de cel met de kringverwijzing:
- Selecteer de cel met de kringverwijzing
- Klik op het tabblad Formules
- Klik op Trace Precedenten
De bovenstaande stappen laten u blauwe pijlen zien die u vertellen welke cellen in de formule in de geselecteerde cel worden ingevoerd. Op deze manier kunt u de formules en de cellen inspecteren en de kringverwijzing verwijderen.
Als u met complexe financiële modellen werkt, is het mogelijk dat deze precedenten ook meerdere niveaus diep gaan.
Dit werkt goed als u alle formules hebt die naar cellen in hetzelfde werkblad verwijzen. Als het in meerdere werkbladen staat, is deze methode niet effectief.
Iteratieve berekeningen in Excel in-/uitschakelen?
Wanneer u een kringverwijzing in een cel hebt, krijgt u eerst de waarschuwingsprompt zoals hieronder weergegeven, en als u dit dialoogvenster sluit, krijgt u 0 als resultaat in de cel.
Dit komt omdat wanneer er een kringverwijzing is, het een eindeloze lus is en Excel er niet in wil verstrikt raken. Dus het geeft een 0 terug.
Maar in sommige gevallen wilt u misschien dat de kringverwijzing actief is en een aantal iteraties uitvoert. In zo'n geval, in plaats van een oneindige lus, en kunt u beslissen hoe vaak de lus moet worden uitgevoerd.
Dit heet iteratieve berekening in Excel.
Hieronder vindt u de stappen om iteratieve berekeningen in Excel in te schakelen en te configureren:
- Klik op het tabblad Bestand
- Klik op Opties. Dit opent het dialoogvenster Excel-opties
- Selecteer Formule in het linkerdeelvenster
- Vink in de sectie Berekeningsopties het vakje 'Iteratieve berekening inschakelen' aan. Hier kunt u de maximale iteraties en maximale wijzigingswaarde opgeven
Dat is het! De bovenstaande stappen zouden iteratieve berekeningen in Excel mogelijk maken.
Laat me ook snel de twee opties in de iteratieve berekening uitleggen:
- Maximale iteraties: Dit is het maximale aantal keren dat u Excel wilt laten berekenen voordat u het eindresultaat krijgt. Dus als u dit opgeeft als 100, voert Excel de lus 100 keer uit voordat u het uiteindelijke resultaat krijgt.
- Maximale verandering: Dit is de maximale verandering die, indien niet bereikt tussen iteraties, de berekening zou worden gestopt. Standaard is de waarde .001. Hoe lager deze waarde, hoe nauwkeuriger het resultaat.
Onthoud dat hoe vaker de iteraties worden uitgevoerd, hoe meer tijd en middelen Excel nodig heeft om dit te doen. Als u de maximale iteraties hoog houdt, kan dit ertoe leiden dat uw Excel vertraagt of crasht.
Opmerking: wanneer iteratieve berekeningen zijn ingeschakeld, toont Excel u de waarschuwingsprompt voor kringverwijzing niet en wordt deze nu ook weergegeven in de statusbalk.Bewust circulaire verwijzingen gebruiken
In de meeste gevallen zou de aanwezigheid van een kringverwijzing in uw werkblad een fout zijn. En dit is de reden waarom Excel u een prompt toont die zegt: "Probeer deze verwijzingen te verwijderen of te wijzigen of de formules naar verschillende cellen te verplaatsen."
Maar er kunnen enkele specifieke gevallen zijn waarin u een kringverwijzing nodig hebt om het gewenste resultaat te krijgen.
Een dergelijk specifiek geval heb ik al geschreven over het krijgen van de tijdstempel in een cel in een cel in Excel.
Stel dat u bijvoorbeeld een formule wilt maken zodat elke invoer in een cel in kolom A wordt gemaakt, de tijdstempel verschijnt in kolom B (zoals hieronder weergegeven):
Hoewel u eenvoudig een tijdstempel kunt invoegen met behulp van de onderstaande formule:
=ALS(A2"",ALS(B2"",B2,NU()),"")
Het probleem met de bovenstaande formule is dat alle tijdstempels worden bijgewerkt zodra er een wijziging in het werkblad wordt aangebracht of als het werkblad wordt heropend (omdat de NOW-formule vluchtig is)
Om dit probleem te omzeilen, kunt u een kringverwijzingsmethode gebruiken. Gebruik dezelfde formule, maar schakel iteratieve berekening in.
Er zijn ook enkele andere gevallen waarin de mogelijkheid om kringverwijzing te gebruiken gewenst is (u kunt hier een voorbeeld vinden).
Opmerking: hoewel er enkele gevallen kunnen zijn waarin u kringverwijzing kunt gebruiken, vind ik het het beste om het niet te gebruiken. Circulaire verwijzingen kunnen ook een negatieve invloed hebben op de prestaties van uw werkmap en kunnen deze vertragen. In zeldzame gevallen waar je het nodig hebt, gebruik ik altijd liever VBA-codes om het werk gedaan te krijgen.Ik hoop dat je deze tutorial nuttig vond!
Andere Excel-tutorials die u wellicht nuttig vindt:
- #REF! Fout in Excel; Hoe de referentiefout te herstellen!
- Excel VBA-foutafhandeling
- Gebruik IFERROR met VERT.ZOEKEN om #N/A-fouten te verwijderen
- Hoe te verwijzen naar een ander blad of werkmap in Excel (met voorbeelden)
- Absolute, relatieve en gemengde celverwijzingen in Excel
