Hoe circulaire verwijzingen in Excel te vinden om foutieve gegevens te voorkomen

Heb je de cirkelreferentiefout op een Excel-blad gezien en vroeg je je af of het werkblad kapot is? Geloof me! U bent niet de eerste die met deze Excel-fout wordt geconfronteerd. Honderden en duizenden Excel-gebruikers ervaren elke dag cirkelreferentiefouten. Maak je geen zorgen! Hier leest u hoe u kringverwijzingen in Excel kunt vinden en oplossen.

Als fervent Excel-gebruiker typt u zelden dezelfde gegevens in veel cellen. In plaats daarvan importeert u een gegevensset uit de bron en gebruikt u celverwijzingen om formules te maken. Zo creëer je een dynamische spreadsheet.

Wanneer iemand de celwaarden van de celverwijzingen wijzigt, wordt het werkblad automatisch bijgewerkt. Dit wordt automatisering van gegevensanalyse genoemd en gegevensanalisten spelen graag met celverwijzingen.

Het probleem doet zich echter voor wanneer u opzettelijk of per ongeluk het celadres typt waar u momenteel een berekening uitvoert. Of, wanneer u veel formules in het werkblad maakt, verschijnt een cel met een formule indirect in een andere formule.

In dergelijke en vele andere soortgelijke situaties krijg je de kringverwijzingsfout in Excel. Lees verder om erachter te komen wat kringverwijzingen zijn, de redenen waarom ze moeten worden vermeden en vooral hoe u kringverwijzingen in Excel kunt vinden om deze afwijkingen voorgoed te verhelpen.

Wat is een kringverwijzing in Excel?

Excel komt een kringverwijzingsfout tegen wanneer het ziet dat een of meer functies van het werkblad terugverwijzen naar het celadres waar hun functies zijn gemaakt.

Het klinkt misschien verwarrend voor u, dus bedenk hoe ernstig het zou kunnen zijn voor de Excel-app. Kortom, u wilt het celadres nooit in een formule plaatsen waar u het aan het maken bent. Bekijk het volgende voorbeeld voor een visuele verduidelijking:

Je probeert 10 te vermenigvuldigen met cel B1 en het resultaat in cel B1 zelf te zetten.

Als u nu in het bovenstaande scenario op OK drukt , sluit Excel de fout en geeft nul weer als het berekende resultaat. Dit is slechts een duidelijk voorbeeld.

Wat is kringverwijzing in Excel

Stel je nu voor dat er honderden formules zijn in een werkblad voor boekhouding, een werkblad voor financiële planning, een loonlijst voor werknemers, een werkblad voor leveranciersbetalingen, enzovoort.

U vertrouwt op de onjuiste gegevens die door Excel zijn berekend vanwege de cirkelreferentiefout en dat zal op zijn beurt van invloed zijn op uw zakelijke of professionele besluitvorming.

Bovendien kan circulair verwijzen de snelheid van berekeningen binnen de Excel-app ernstig verminderen. In ernstige gevallen kan de fout zelf ook extreem trage prestaties op de pc veroorzaken als deze niet onmiddellijk wordt verholpen.

Als u echter aan geavanceerde Excel-formules werkt waarbij uw gegevensmodel tussenliggende invoer van de kringverwijzing vereist om door te gaan met een lange functie, moet u deze mogelijk gebruiken. In Excel kunt u deze berekeningsmethode zelfs gebruiken wanneer dat nodig is.

In energieproductie-eenheden, zoals windmolens, moet u bijvoorbeeld gerecycleerde outputenergie berekenen op basis van een fractie van de inputenergie. Als alternatief gebruiken ingenieurs in een chemische fabriek de uitgangssteel als input voor dezelfde of een andere ketel of procesvat. Dergelijke technische berekeningen op hoog niveau vereisen inderdaad circulaire referenties voor iteratieve berekeningen.

Wanneer zie je de cirkelreferentiefout in Excel?

Het is duidelijk dat de pop-up met kringverwijzing geen foutmelding is. Het is een manier voor Excel om u te informeren dat het een kringverwijzing is tegengekomen in een of meer formules en dat dit van invloed kan zijn op het berekeningsalgoritme en de prestaties.

Om schade veroorzaakt door kringverwijzingen in Excel-formules te voorkomen, moet u weten wanneer ze verschijnen en wanneer niet. Vind hieronder de exemplaren:

• In een geopend werkblad of werkmap maakt u een formule en voert u voor het eerst de kringverwijzing in.
• U of iemand anders heeft alle kringverwijzingen uit het Excel-bestand verwijderd en opgeslagen. Nu voert u of een andere gebruiker opnieuw een kringverwijzing in hetzelfde bestand in.
• U hebt alle geopende werkmappen gesloten. Open nu een andere werkmap en typ vervolgens een formule met een cirkelvormige verwijzing.

Wanneer zie je de cirkelreferentiefout in Excel

• U hebt zojuist een werkmap geopend die eerder een of meer kringverwijzingen bevatte. Je ziet de waarschuwing.
• Zodra Excel u eenmaal het waarschuwingsbericht voor kringverwijzingen heeft getoond en u op OK hebt geklikt , wordt dezelfde pop-up niet meer weergegeven in dezelfde sessie, zelfs als u steeds meer formules met kringverwijzingen maakt.

U moet dus zorgvuldig zoeken naar de bovenstaande gevallen om cirkelreferentiewaarschuwingen op te vangen en deze op te lossen als u niet op zoek bent naar iteratieve berekeningen.

Soorten kringverwijzingen in Excel

Als u wilt leren hoe u kringverwijzingen in Excel kunt vinden, moet u hun typen begrijpen. Omdat het vinden en verwijderen van kringverwijzingen in Excel geen geautomatiseerde taak is. Hieronder vindt u de meest voorkomende typen van deze fout:

Een voorbeeld van circulair referentiegebruik

• Bedoelde kringverwijzingen treden op wanneer u ze opzettelijk gebruikt voor iteratieve berekeningsdoeleinden. U moet bijvoorbeeld een tijdstempel op een andere cel invoeren wanneer iemand een waarde invoert in een cel waarnaar wordt verwezen met behulp van vluchtige en logische functies zoals IF, OR, SUMIF, enz.

Onbedoelde kringverwijzing in Excel

• Onbedoelde kringverwijzingen wanneer u sneller aan een Excel-formule probeert te werken en per ongeluk naar de formule-bevattende cel verwijst. U moet bijvoorbeeld de SOM- functie toepassen van cel A1 tot A9 en het resultaat ophalen in cel A10 . Maar per ongeluk typt u de formule als volgt in cel A10:

=SOM(A2:A10)

• Verborgen kringverwijzingen zijn verwijzingen die geen waarschuwingsberichten weergeven wanneer u een formule maakt of het werkblad voor de eerste keer opent. Het gebeurt omdat iemand iteratieve berekening op het Excel-bestand heeft toegestaan.

Een voorbeeld van een indirecte kringverwijzing

• Indirecte kringverwijzingen zijn ook populair en komen voor wanneer u een nieuwe formule maakt in een willekeurige cel, bijvoorbeeld cel A1 , en deze nieuwe formule gebruikt een andere functie in een andere cel, zoals cel B1 , die al cel A1 als referentie gebruikt .

Nu u de basisprincipes van kringverwijzingen in Excel hebt begrepen, gaan we kijken hoe u kringverwijzingen in Excel hieronder kunt vinden:

Cirkelreferenties zoeken in Excel: vanuit de statusbalk

Op voorwaarde dat de iteratieve berekeningsfunctie is gedeactiveerd, kunt u de volgende meldingen over kringverwijzingen zien op de Excel-statusbalk:

• Circulaire verwijzingen: C3  (of een andere celverwijzing) wanneer u zich op een werkblad met een circulaire formule in de werkmap bevindt.

Circulaire verwijzingen weergeven in de statusbalk van Excel

• U ziet alleen kringverwijzingen als u zich op een werkblad bevindt dat er geen bevat, maar de werkmap heeft een paar van deze cirkelvormige formules.

Circulaire verwijzingen vinden in Excel: foutcontrole gebruiken

Het is de beste manier om te controleren op kringverwijzingen in een werkmap of werkblad. Dit zijn de stappen die u moet kennen:

• Open een werkblad waarvan u vermoedt dat het circulaire formules bevat.
• Klik op het menu Formules op het Excel-lint.

Gebruik foutcontrole voor het vinden van kringverwijzingen in Excel

• Klik in de opdrachtgroep Formula Auditing op de vervolgkeuzelijst Error Checking  .
• Beweeg de cursor over de optie Cirkelreferenties om alle beschikbare instanties in alle werkbladen te zien.
• Selecteer elke instantie en Excel zal die cellen automatisch lokaliseren.

Circulaire verwijzingen vinden in Excel: van formuletracering

Als u de foutcontrolemethode niet efficiënt genoeg vindt om grote aantallen formules met kringverwijzingen te detecteren, kunt u deze stappen proberen om de formuletraceermethode te gebruiken:

• Selecteer een cel waarnaar wordt verwezen of de formule-bevattende cel.
• Ga naar het tabblad Formules in het Excel-lintmenu en klik vervolgens op Trace Precedents .
• Excel markeert de cellen die van invloed zijn op het resultaat van de formule in de huidige cel met pijlen.

Gebruik formuletracering voor het vinden van kringverwijzingen in Excel

• U kunt ook op Trace Dependents klikken om cellen te ontdekken die worden beïnvloed door de waarde van de geselecteerde celverwijzing.
• Als Excel geen fouten weergeeft nadat u op de bovenstaande opdrachten hebt geklikt, zijn er geen kringverwijzingen in uw werkblad.
• Herhaal de stappen op alle werkbladen om de hele werkmap te controleren.

Deze methode is vooral belangrijk wanneer de functies voor iteratieve berekeningen actief zijn op het Excel-werkblad.

Circulaire verwijzingen zoeken in Excel: Excel-opties gebruiken

• Klik op het tabblad Bestand op het Excel-lint en open vervolgens Excel-opties in de zijbalk die verschijnt.
• Selecteer in het dialoogvenster Excel-opties het menu Formules in de zijbalk en schakel vervolgens het selectievakje Iteratieve berekening inschakelen uit .

Hoe iteratieve berekening in Excel te deactiveren

• Klik op OK om de wijzigingen op te slaan.
• Nu zal de werkmap of het werkblad u automatisch de cirkelverwijzingswaarschuwing laten zien.
• Als het niet meteen wordt weergegeven, slaat u de werkmap op, sluit u deze en opent u deze opnieuw om de waarschuwing te zien.
• Zodra u de waarschuwing ziet, controleert u de Excel-statusbalk om te weten welke cel zich in de kringverwijzing bevindt.

Hoe cirkelverwijzingen in Excel op te lossen

De zekere manier om een ​​of meer kringverwijzingsfouten in Excel te corrigeren, is door elke formule die deze bevat handmatig te bewerken. Op deze manier kunt u zowel onbedoelde als indirecte kringverwijzingen in Excel oplossen.

Hoe cirkelverwijzingen in Excel op te lossen

Een andere gemakkelijke manier is om de formule uit de huidige cel te verwijderen en ergens anders in hetzelfde werkblad te plaatsen.

Circulaire verwijzingen vinden in Google Spreadsheets (bonusmethode)

Net als Excel staat Google Spreadsheets geen kringverwijzingen toe in een of meer formules als u de iteratieve berekeningsfunctie niet hebt geactiveerd. Daarom zal Google Spreadsheets onmiddellijk de #REF! fout op de cel die een circulaire formule bevat.

Google Spreadsheets Hoe circulaire verwijzingen te vinden

Daarom kunt u eenvoudig alle cellen selecteren die de #REF! waarschuwing om kringverwijzingen op Google Spreadsheets te vinden en op te lossen.

Circulaire verwijzingen in Excel: laatste woorden

Dus nu ken je alle geheimen voor het vinden van kringverwijzingen in Excel. Gebruik de methode die u prettig vindt en gooi alle kringverwijzingen weg om ervoor te zorgen dat u niet in de war raakt door onbetrouwbare gegevens uit een Excel-rapport of een wiskundig dashboard.

Vervolgens de  beste Excel-kalendersjablonen voor elk jaar  en de  beste Excel-budgetsjablonen .