Een van de meest voorkomende foutwaarschuwingen die gebruikers in Excel tegenkomen, is de 'Circulaire referentie'. Duizenden gebruikers hebben hetzelfde probleem, en het komt voor wanneer een formule direct of indirect terugverwijst naar zijn eigen cel, waardoor een eindeloze lus van berekeningen ontstaat.
U hebt bijvoorbeeld twee waarden in de cellen B1 en B2. Wanneer de formule =B1+B2 in B2 wordt ingevoerd, ontstaat er een kringverwijzing; de formule in B2 herberekent zichzelf herhaaldelijk omdat elke keer dat deze wordt berekend, de B2-waarde is gewijzigd.
De meeste circulaire verwijzingen zijn onbedoelde fouten; Excel zal u hiervoor waarschuwen. Er zijn echter ook bedoelde kringverwijzingen, die worden gebruikt om iteratieve berekeningen te maken. Onbedoelde kringverwijzingen in uw werkblad kunnen ertoe leiden dat uw formule onjuist wordt berekend.
Daarom leggen we in dit artikel alles uit wat u moet weten over kringverwijzingen en hoe u kringverwijzingen in Excel kunt vinden, herstellen, verwijderen en gebruiken.
Circulaire referentie zoeken en verwerken in Excel
Wanneer we met Excel werken, komen we soms kringverwijzingsfouten tegen die optreden wanneer u een formule invoert die de cel bevat waarin uw formule zich bevindt. Kortom, het gebeurt wanneer uw formule zichzelf probeert te berekenen.
U hebt bijvoorbeeld een kolom met getallen in cel A1:A4 en u gebruikt de SOM-functie (=SUM(A1:A5)) in cel A5. De cel A5 verwijst rechtstreeks naar zijn eigen cel, wat niet klopt. Daarom krijgt u de volgende kringverwijzingswaarschuwing:
Zodra u het bovenstaande waarschuwingsbericht hebt ontvangen, kunt u op de knop 'Help' klikken om meer over de fout te weten te komen, of het venster met het foutbericht sluiten door op de knop 'OK' of 'X' te klikken en '0' als resultaat te krijgen.
Soms kunnen cirkelvormige referentielussen ervoor zorgen dat uw berekening vastloopt of uw werkbladprestaties vertraagt. Circulaire verwijzing kan ook leiden tot een aantal andere problemen, die niet meteen duidelijk zullen zijn. Deze kun je dus het beste vermijden.
Directe en indirecte circulaire verwijzingen
De circulaire verwijzingen kunnen worden onderverdeeld in twee typen: directe circulaire verwijzingen en indirecte circulaire verwijzingen.
Directe referentie:
Een directe kringverwijzing is vrij eenvoudig. Het waarschuwingsbericht voor directe kringverwijzing verschijnt wanneer de formule rechtstreeks naar zijn eigen cel verwijst.
In het onderstaande voorbeeld verwijst de formule in cel A2 rechtstreeks naar zijn eigen cel (A2).
Zodra het waarschuwingsbericht verschijnt, kunt u op 'OK' klikken, maar dit resulteert alleen in '0'.
Indirecte circulaire verwijzing
Een indirecte kringverwijzing in Excel treedt op wanneer een waarde in een formule naar zijn eigen cel verwijst, maar niet rechtstreeks. Met andere woorden, een kringverwijzing kan worden gevormd door twee cellen die naar elkaar verwijzen.
Laten we het uitleggen met dit eenvoudige voorbeeld.
Nu begint de waarde bij A1 die de waarde 20 heeft.
Vervolgens verwijst cel C3 naar cel A1.
Vervolgens verwijst cel A5 naar cel C3.
Vervang nu de waarde 20 in cel A1 door de formule zoals hieronder weergegeven. Elke andere cel is afhankelijk van cel A1. Wanneer u een verwijzing naar een andere vorige formulecel in A1 gebruikt, wordt er een waarschuwing voor een kringverwijzing weergegeven. Omdat de formule in A1 verwijst naar cel A5, die verwijst naar C3, en cel C3 terugverwijst naar A1, vandaar de kringverwijzing.
Wanneer u op 'OK' klikt, resulteert dit in een waarde van 0 in cel A1 en maakt Excel een gekoppelde regel met de Trace Precedents en Trace Dependents zoals hieronder weergegeven. We kunnen deze functie gebruiken om eenvoudig kringverwijzingen te vinden en te herstellen/verwijderen.
Hoe circulaire verwijzingen in Excel in / uit te schakelen?
Iteratieve berekeningen zijn standaard uitgeschakeld (uitgeschakeld) in Excel. Iteratieve berekeningen zijn repetitieve berekeningen totdat aan een specifieke voorwaarde wordt voldaan. Wanneer het is uitgeschakeld, toont Excel een Circular Reference-bericht en retourneert een 0 als resultaat.
Soms zijn echter kringverwijzingen nodig om een lus te berekenen. Om kringverwijzing te gebruiken, moet u iteratieve berekeningen in uw Excel inschakelen en kunt u uw berekeningen uitvoeren. Laten we u nu laten zien hoe u iteratieve berekeningen kunt in- of uitschakelen.
Ga in Excel 2010, Excel 2013, Excel 2016, Excel 2019 en Microsoft 365 naar het tabblad 'Bestand' in de linkerbovenhoek van Excel en klik vervolgens op 'Opties' in het linkerdeelvenster.
Ga in het venster Excel-opties naar het tabblad 'Formule' en vink het selectievakje 'Iteratieve berekening inschakelen' aan onder het gedeelte 'Berekeningsopties'. Klik vervolgens op ‘OK’ om de wijzigingen op te slaan.
Dit maakt iteratieve berekening mogelijk en maakt kringverwijzing mogelijk.
Om dit in vorige versies van Excel te bereiken, volgt u deze stappen:
- Klik in Excel 2007 op de Office-knop > Excel-opties > Formules > Iteratiegebied.
- In Excel 2003 en eerdere versies moet u naar Menu > Extra > Opties > tabblad Berekening gaan.
Maximale iteraties en maximale wijzigingsparameters
Nadat u iteratieve berekeningen hebt ingeschakeld, kunt u de iteratieve berekeningen beheren door twee opties op te geven die beschikbaar zijn onder de sectie Iteratieve berekening inschakelen, zoals weergegeven in de onderstaande schermafbeelding.
- Maximale iteraties – Dit getal geeft aan hoe vaak de formule opnieuw moet worden berekend voordat u het eindresultaat krijgt. De standaardwaarde is 100. Als u deze wijzigt in '50', herhaalt Excel de berekeningen 50 keer voordat u het uiteindelijke resultaat krijgt. Onthoud dat hoe hoger het aantal iteraties, hoe meer middelen en tijd het kost om te berekenen.
- Maximale verandering – Het bepaalt de maximale verandering tussen rekenresultaten. Deze waarde bepaalt de nauwkeurigheid van het resultaat. Hoe kleiner het getal, hoe nauwkeuriger het resultaat zou zijn en hoe langer het duurt om het werkblad te berekenen.
Als de optie voor iteratieve berekeningen is ingeschakeld, krijgt u geen waarschuwing wanneer er een kringverwijzing in uw werkblad staat. Schakel interactieve berekening alleen in als het absoluut noodzakelijk is.
Vind circulaire referentie in Excel
Stel dat je een grote dataset hebt en je krijgt de kringverwijzingswaarschuwing, dan moet je nog steeds uitvinden waar (in welke cel) de fout is opgetreden om deze te herstellen. Volg deze stappen om kringverwijzingen in Excel te vinden:
Hulpprogramma voor foutcontrole gebruiken
Open eerst het werkblad waar de kringverwijzing is gebeurd. Ga naar het tabblad ‘Formule’, klik op de pijl naast de tool ‘Foutcontrole’. Beweeg vervolgens de cursor over de optie 'Cirkelverwijzingen', Excel toont u de lijst met alle cellen die betrokken zijn bij de kringverwijzing, zoals hieronder weergegeven.
Klik op het gewenste celadres in de lijst en u gaat naar dat celadres om het probleem op te lossen.
Statusbalk gebruiken
U kunt de kringverwijzing ook vinden op de statusbalk. Op de statusbalk van Excel wordt het laatste celadres weergegeven met een kringverwijzing, zoals 'Cirkelverwijzingen: B6' (zie onderstaande schermafbeelding).
Er zijn bepaalde dingen die u moet weten bij het omgaan met kringverwijzing:
- De statusbalk toont het celadres van de circulaire referentie niet wanneer de optie Iteratieve berekening is ingeschakeld, dus u moet deze uitschakelen voordat u in de werkmap naar circulaire referenties gaat kijken.
- Als kringverwijzing niet wordt gevonden in het actieve blad, geeft de statusbalk alleen 'Kringverwijzingen' weer zonder celadres.
- U krijgt slechts één keer een kringverwijzingsprompt en nadat u op 'OK' hebt geklikt, wordt de prompt de volgende keer niet meer weergegeven.
- Als uw werkmap kringverwijzingen heeft, wordt de prompt weergegeven telkens wanneer u deze opent totdat u de kringverwijzing oplost of totdat u iteratieve berekening inschakelt.
Een circulaire verwijzing in Excel verwijderen
Circulaire verwijzingen vinden is eenvoudig, maar het oplossen ervan is niet zo eenvoudig. Helaas is er in Excel geen optie waarmee u alle kringverwijzingen in één keer kunt verwijderen.
Om kringverwijzingen te corrigeren, moet je elke kringverwijzing afzonderlijk zoeken en proberen deze aan te passen, de cirkelvormige formule helemaal te verwijderen of door een andere te vervangen.
Soms hoeft u in eenvoudige formules alleen de parameters van de formule opnieuw aan te passen, zodat deze niet naar zichzelf verwijst. Wijzig bijvoorbeeld de formule in B6 in =SUM(B1:B5)*A5 (verander B6 in B5).
Het resultaat van de berekening wordt geretourneerd als '756'.
In gevallen waarin een Excel-kringverwijzing moeilijk te vinden is, kunt u de functies Trace Precedents en Trace Dependents gebruiken om deze terug te traceren naar de bron en deze één voor één op te lossen. De pijl geeft aan welke cellen worden beïnvloed door de actieve cel.
Er zijn twee traceermethoden waarmee u kringverwijzingen kunt verwijderen door de relaties tussen formules en cellen weer te geven.
Om toegang te krijgen tot de traceermethoden, gaat u naar het tabblad 'Formules' en klikt u vervolgens op 'Trace Precedents' of 'Trace Dependents' in de groep Formula Auditing.
Traceer precedenten
Wanneer u deze optie selecteert, worden de cellen gevolgd die van invloed zijn op de waarde van de actieve cel. Het tekent een blauwe lijn die aangeeft welke cellen de huidige cel beïnvloeden. De sneltoets om precedenten te gebruiken is: Alt + T U T.
In het onderstaande voorbeeld toont de blauwe pijl de cellen die de B6-waarde beïnvloeden, zijn B1:B6 en A5. Zoals je hieronder kunt zien, maakt cel B6 ook deel uit van de formule, waardoor het een kringverwijzing wordt en de formule als resultaat '0' retourneert.
Dit kan eenvoudig worden opgelost door B6 te vervangen door B5 in het argument van SUM: =SUM(B1:B5).
Afhankelijke personen traceren
Met de functie traceerafhankelijkheden worden de cellen getraceerd die afhankelijk zijn van de geselecteerde cel. Deze functie tekent een blauwe lijn die aangeeft welke cellen worden beïnvloed door de geselecteerde cel. Dat wil zeggen, het geeft weer welke cellen formules bevatten die verwijzen naar de actieve cel. De sneltoets om afhankelijke personen te gebruiken is: Alt + T U D.
In het volgende voorbeeld wordt cel D3 beïnvloed door B4. Het is afhankelijk van B4 voor zijn waarde om resultaten te produceren. Trace-afhankelijk trekt dus een blauwe lijn van B4 naar D3, wat aangeeft dat D3 afhankelijk is van B4.
Bewust circulaire verwijzingen gebruiken in Excel
Het is niet aan te raden om met opzet circulaire verwijzingen te gebruiken, maar er kunnen zeldzame gevallen zijn waarin u een circulaire verwijzing nodig hebt om de gewenste uitvoer te krijgen.
Laten we dat uitleggen aan de hand van een voorbeeld.
Schakel om te beginnen 'Iteratief rekenen' in uw Excel-werkmap in. Nadat u Iteratieve berekening hebt ingeschakeld, kunt u kringverwijzingen in uw voordeel gaan gebruiken.
Stel dat u een huis koopt en dat u een commissie van 2% over de totale kosten van het huis aan uw makelaar wilt geven. De totale kosten worden berekend in cel B6 en het commissiepercentage (agentkosten) wordt berekend in B4. De commissie wordt berekend op basis van de totale kosten en de totale kosten zijn inclusief de commissie. Omdat de cellen B4 en B6 van elkaar afhankelijk zijn, ontstaat er een kringverwijzing.
Voer de formule in om de totale kosten in cel B6 te berekenen:
=SOM(B1:B4)
Aangezien de totale kosten inclusief de makelaarskosten zijn, hebben we B4 opgenomen in de bovenstaande formule.
Om de makelaarskosten van 2% te berekenen, voert u deze formule in B4 in:
=B6*2%Nu is de formule in cel B4 afhankelijk van de waarde van B6 om 2% van de totale vergoeding te berekenen en de formule in B6 is afhankelijk van B4 om de totale kosten (inclusief makelaarskosten) te berekenen, vandaar de kringverwijzing.
Als de iteratieve berekening is ingeschakeld, geeft Excel geen waarschuwing of een 0 in het resultaat. In plaats daarvan wordt het resultaat van de cellen B6 en B4 berekend zoals hierboven weergegeven.
De optie iteratieve berekeningen is meestal standaard uitgeschakeld. Als u het niet hebt ingeschakeld en wanneer u de formule invoert in B4, wordt er een kringverwijzing gemaakt. Excel geeft de waarschuwing en wanneer u op 'OK' klikt, wordt de tracerpijl weergegeven.
Dat is het. Dit was alles wat u moest weten over kringverwijzingen in Excel.