Circulaire verwijzingen in Excel vinden op 4 manieren

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 deze kunt 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.

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 te vermenigvuldigen 10 van cel B1 en zet het resultaat in de cel B1 zelf.

Nu, in het bovenstaande scenario, als u gewoon op drukt OK, zal Excel de fout sluiten en nul weergeven als het berekende resultaat. Dit is slechts een duidelijk voorbeeld.

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

U vertrouwt de foutieve 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.

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.

• U hebt zojuist een werkmap geopend die voorheen een of meer kringverwijzingen bevatte. Je ziet de waarschuwing.
• Zodra Excel u eenmaal het waarschuwingsbericht met circulaire verwijzing heeft getoond en u hebt geklikt OK, zal het niet meer dezelfde pop-up weergeven in dezelfde sessie, zelfs als u steeds meer formules met circulaire verwijzingen 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.

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:

• 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 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 vanuit cel A1 naar A9 en haal het resultaat op in de 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.

• 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 zou een andere functie gebruiken in een andere cel, zoals cel B1, die al gebruik maakt van cel A1 als een referentie.

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

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

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

• Je zult alleen zien Circulaire verwijzingen als u zich op een werkblad bevindt dat er geen bevat, maar de werkmap heeft een paar van deze circulaire formules.

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 de Formules menu op het Excel-lint.

• In de Formule auditing opdrachtgroep, klikt u op de Foutcontrolekeuzelijst.
• Beweeg de cursor over de Circulaire verwijzingen optie om alle beschikbare instanties in alle werkbladen te zien.
• Selecteer elke instantie en Excel zal die cellen automatisch lokaliseren.

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 de Formules tabblad in het Excel-lintmenu en klik vervolgens op Traceer precedenten.
• Excel markeert de cellen die van invloed zijn op het resultaat van de formule in de huidige cel met pijlen.

• U kunt ook klikken Traceer afhankelijken 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.

• Klik op de Bestand tabblad op het Excel-lint en open vervolgens Excel-opties uit de zijbalk die verschijnt.
• Op de Excel-opties dialoogvenster selecteert u het Formules menu in de zijbalk en verwijder het vinkje uit het selectievakje voor Schakel iteratieve berekening in.

• Klik 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 het Excel-statusbalk om te weten welke cel zich in de kringverwijzing bevindt.

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.

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

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.

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

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.