Tijdens het werken met Excel-formules kunt u soms de volgende waarschuwingsmelding te zien krijgen.
Deze prompt vertelt u dat er een cirkelvormige verwijzing in uw werkblad staat en dat dit kan leiden tot of een onjuiste berekening door de formules. Ook wordt u gevraagd deze circulaire verwijzing aan te pakken en te sorteren.
In deze Tips & Tricks behandel ik alles wat u moet weten over circulaire verwijzingen en hoe u circulaire verwijzingen in Excel kunt vinden en verwijderen.
Dus laten we beginnen!
Deze Tips & Tricks behandelt:
Wat is circulaire verwijzing in Excel?
In eenvoudige bewoordingen komt een circulaire verwijzing tot stand wanneer u uiteindelijk een formule in een cel hebt – die op zichzelf de cel (waarin deze is ingevoerd) gebruikt voor de berekening.
Laat ik dit aan de hand van een eenvoudig voorbeeld proberen uit te leggen.
Voorstel dat u de dataset in cel A1:A5 hebt en u gebruikt de onderstaande formule in cel A6:
=SUM(A1:A6)
Dit zal u een circulaire verwijzing waarschuwing geven.
Dit komt omdat u de waarden in cel A1:A6 wilt optellen, en het resultaat moet in cel A6 staan.
Dit creëert een lus omdat Excel steeds de nieuwe waarde in cel A6 blijft toevoegen, die steeds verandert (vandaar een lus met cirkelvormige verwijzingen).
Hoe zoek je cirkelvormige verwijzingen in Excel?
Hoewel de waarschuwing voor cirkelvormige verwijzingen zo vriendelijk is u te vertellen dat deze in uw werkblad voorkomen, vertelt deze u niet waar dit gebeurt en welke celverwijzingen dit veroorzaken.
Dus als u probeert cirkelvormige verwijzingen in het werkblad te vinden en af te handelen, moet u een manier kennen om deze op de een of andere manier te vinden.
Hieronder staan de stappen om een cirkelvormige verwijzing in Excel te vinden:
- Activeer het werkblad dat de cirkelvormige verwijzing heeft
- Klik op het tabblad Formules
- Klik in de groep Formulebewerking op het vervolgkeuzepictogram Foutcontrole (kleine naar beneden wijzende pijl rechts)
- Beweeg de cursor over de optie Circulaire verwijzingen. Het zal u de cel tonen die een cirkelvormige verwijzing in het werkblad heeft
- Klik op het celadres (dat wordt getoond) en het zal u naar die cel in het werkblad brengen.
Als u het probleem hebt aangepakt, kunt u opnieuw dezelfde stappen hierboven volgen en het zal meer celverwijzingen tonen die de cirkelvormige verwijzing hebben. Als er geen is, ziet u geen enkele celverwijzing,
Een andere snelle en eenvoudige manier om de cirkelvormige verwijzing te vinden, is door naar de statusbalk te kijken. In het linkergedeelte daarvan ziet u de tekst Cirkelvormige verwijzing, samen met het celadres.
Er zijn een paar dingen die u moet weten als u met cirkelvormige verwijzingen werkt:
- In het geval dat de iteratieve berekening is ingeschakeld (wordt later in deze tutorial behandeld), wordt het celadres van de cirkelvormige verwijzing niet in de statusbalk weergegeven
- In het geval dat de cirkelvormige verwijzing niet in het actieve blad staat (maar in andere bladen in dezelfde werkmap), wordt alleen de cirkelvormige verwijzing weergegeven en niet het celadres
- In het geval dat u eenmaal een waarschuwingsprompt voor cirkelvormige verwijzingen krijgt en u deze uitschakelt, wordt deze prompt de volgende keer niet meer weergegeven.
- Als u een werkmap opent die de cirkelvormige verwijzing bevat, wordt de prompt getoond zodra de werkmap wordt geopend.
Hoe verwijdert u een cirkelvormige verwijzing in Excel?
Als u eenmaal hebt vastgesteld dat er cirkelvormige verwijzingen in uw blad zijn, is het tijd om deze te verwijderen (tenzij u wilt dat ze er voor een reden zijn).
Het is helaas niet zo eenvoudig als het indrukken van de delete-toets. Omdat deze afhankelijk zijn van formules en elke formule anders is, moet je dit per geval analyseren.
In het geval dat de celverwijzing per ongeluk het probleem veroorzaakt, kun je dit eenvoudig corrigeren door de verwijzing aan te passen.
Maar soms is het niet zo eenvoudig.
Circulaire verwijzing kan ook worden veroorzaakt op basis van meerdere cellen die op vele niveaus in elkaar overgaan.
Laat me u een voorbeeld zien.
Hieronder staat een circulaire verwijzing in cel C6, maar het is niet alleen een simpel geval van zelfverwijzing. Het is multi-level waarbij de cellen die het gebruikt in de berekeningen ook naar elkaar verwijzen.
- De formule in cel A6 is =SUM(A1:A5)+C6
- De formule in 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 weer afhankelijk zijn van cel C6 (en zo de fout door cirkelverwijzing veroorzaken)
En nogmaals, ik heb een heel eenvoudig voorbeeld gekozen, alleen maar voor demodoeleinden. In werkelijkheid kunnen deze vrij moeilijk te achterhalen zijn en misschien ver uit elkaar liggen in hetzelfde werkblad of zelfs verspreid over meerdere werkbladen.
In zo’n geval is er één manier om de cellen te identificeren die cirkelvormige verwijzing veroorzaken en deze vervolgens te behandelen.
Het is door gebruik te maken van de optie Trace Precedents.
Hieronder staan de stappen voor het gebruik van trace precedents om cellen te vinden die de cel voeden die de circulaire verwijzing heeft:
- Selecteer de cel die de cirkelvormige verwijzing heeft
- Klik op het tabblad Formules
- Klik op Trace Precedents
De bovenstaande stappen zouden u blauwe pijlen tonen die u vertellen welke cellen in de formule in de geselecteerde cel worden gevoed. Op deze manier kunt u de formules en de cellen inspecteren en de cirkelvormige verwijzing verwijderen.
In het geval dat u met complexe financiële modellen werkt, kan het zijn 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 is, is deze methode niet effectief.
Hoe Iteratieve berekeningen in Excel in-/uitschakelen
Wanneer u een cirkelvormige verwijzing in een cel hebt, krijgt u eerst de waarschuwingsmelding zoals hieronder weergegeven, en als u dit dialoogvenster sluit, geeft het u 0 als resultaat in de cel.
Dit komt omdat wanneer er een cirkelvormige verwijzing is, het een eindeloze lus is en Excel wil daar niet in verstrikt raken. Dus geeft het een 0.
Maar in sommige gevallen wil je dat de cirkelvormige verwijzing actief is en een paar iteraties uitvoert. In een dergelijk geval, in plaats van een oneindige lus, en u kunt beslissen hoe vaak de lus moet worden uitgevoerd.
Dit wordt iteratieve berekening in Excel genoemd.
Hieronder volgen de stappen om iteratieve berekeningen in Excel in te schakelen en te configureren:
- Klik op het tabblad Bestand
- Klik op Opties. Hierdoor wordt het dialoogvenster Excel-opties geopend
- Selecteer Formule in het linkerdeelvenster
- Vink in het gedeelte Berekeningsopties het vakje ‘Iteratieve berekening inschakelen’ aan. Hier kunt u de maximale iteraties en de maximale veranderingswaarde opgeven
Dat is het! De bovenstaande stappen zouden iteratieve berekening in Excel inschakelen.
Laat me ook snel de twee opties in de iteratieve berekening uitleggen:
- Maximale iteraties: Dit is het maximum aantal keren dat u wilt dat Excel een berekening uitvoert voordat u het eindresultaat krijgt. Dus als u dit opgeeft als 100, zal Excel de lus 100 keer uitvoeren voordat u het eindresultaat 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.
Opgemerkt moet worden dat hoe vaker de iteraties worden uitgevoerd, hoe meer tijd en middelen Excel nodig heeft om dit te doen. Als u het maximumaantal iteraties hoog houdt, kan dit ertoe leiden dat Excel trager wordt of vastloopt.
Opzettelijk cirkelvormige verwijzingen gebruiken
In de meeste gevallen zou de aanwezigheid van cirkelvormige verwijzingen in uw werkblad een fout zijn. En dit is waarom Excel u een prompt toont die zegt – “Probeer deze verwijzingen te verwijderen of te wijzigen of verplaats de formules naar andere cellen.”
Maar er kunnen enkele specifieke gevallen zijn waarin u een cirkelvormige verwijzing nodig hebt om het gewenste resultaat te krijgen.
Een dergelijk specifiek geval waarover ik al heb geschreven is het verkrijgen van de tijdstempel in een cel in een cel in Excel.
Voorbeeld, stel dat u een formule wilt maken zodat bij elke invoer in een cel in kolom A, de tijdstempel in kolom B verschijnt (zoals hieronder weergegeven):
Terwijl u eenvoudig een tijdstempel kunt invoegen met behulp van de onderstaande formule:
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
Het probleem met de bovenstaande formule is dat deze alle tijdstempels zou bijwerken zodra er een wijziging in het werkblad wordt aangebracht of als het werkblad opnieuw wordt geopend (aangezien de NOW-formule vluchtig is)
Om dit probleem te omzeilen, kunt u een circulaire referentiemethode gebruiken.
Er zijn ook andere gevallen waarin het gebruik van circulaire verwijzingen gewenst is (een voorbeeld hiervan vindt u hier).
Ik hoop dat je deze tutorial nuttig vond!
Andere Excel tutorials die je misschien nuttig vindt: