Hoe circulaire verwijzingen in Excel te vinden

Wanneer u met Microsoft Excel werkt , kunt u af en toe een zogenaamde kringverwijzing tegenkomen. Dit is wanneer een formule in een cel naar zichzelf verwijst, waardoor een eindeloze lus in het berekeningsproces ontstaat.

Om circulaire verwijzingen in Excel te vinden, gebruikt u de tool "Foutcontrole" op het tabblad "Formules" om cellen met een cirkelvormige afhankelijkheid te identificeren.

Dit artikel laat zien hoe u kringverwijzingen in Excel kunt vinden en oplossen. Ook leer je hoe je ze kunt voorkomen bij het werken met grote of complexe werkbladen.

Inhoudsopgave

• Wat zijn circulaire verwijzingen
• Het circulaire referentiefoutberichtvenster
• Hoe circulaire verwijzingen in Excel te vinden
• Cirkelreferenties zoeken met de Excel-statusbalk
• Hoe verborgen kringverwijzingen in Excel te vinden
  • Hoe de Trace Dependent Tool te gebruiken
  • Trace Precedents-tool
• 2 manieren om kringverwijzingen in Excel te verwijderen
  • 2. Gebruik alternatieve formules
• 5 tips om circulaire verwijzingen te voorkomen
  • 1. Wees voorzichtig bij het selecteren van bereiken
  • 2. Plan uw formules zorgvuldig
  • 3. Gebruik alternatieve formules
  • 4. Verdeel complexe formules in kleinere delen
  • 5. Los problemen gaandeweg op
• Hoe iteratieve berekening inschakelen en waarom moeite doen?

Wat zijn circulaire verwijzingen

Cirkelverwijzingen komen voor in Excel wanneer een formuleberekening in een cel afhankelijk is van de eigen waarde van de cel. Dit creëert een lus en leidt tot onjuiste of onvoorspelbare resultaten.

Dit is gemakkelijker te begrijpen met een eenvoudig voorbeeld.

Stel dat je twee getallen hebt in cel A1 en B1. U wilt een formule gebruiken in cel A3 die de twee getallen bij elkaar optelt. De formule in cel A3 zou er als volgt uit moeten zien:

=A1 + B1

Als je echter een fout maakt en C1 meeneemt in de berekeningen, heb je nu een kringverwijzing:

=A1 + B1 + C1

Dit kan een voor de hand liggend probleem zijn dat moet worden vermeden wanneer het om twee cellen gaat. Wanneer u echter met grote reeksen cellen werkt, worden fouten waarschijnlijker.

Gelukkig is Excel ontworpen om met dergelijke situaties om te gaan door een waarschuwingsbericht weer te geven wanneer een kringverwijzing wordt gedetecteerd.

In sommige gevallen kunt u echter een kringverwijzing tegenkomen zonder dat u een waarschuwing ontvangt. Daarom is het belangrijk om te weten hoe u deze problemen handmatig kunt vinden en oplossen.

Het circulaire referentiefoutberichtvenster

Excel controleert standaard op kringverwijzingen wanneer u formules in cellen invoert. Wanneer u op Enter drukt in een cel met een probleem, wordt in een pop-upvenster het foutbericht met de kringverwijzing weergegeven.

Dit is de verklarende tekst:

Er zijn een of meer kringverwijzingen waarbij een formule direct of indirect naar zijn eigen cel verwijst. Hierdoor kunnen ze verkeerd rekenen. Probeer deze verwijzingen te verwijderen of te wijzigen of verplaats de formules naar andere cellen.

Wanneer u dit dialoogvenster ziet, klikt u op "OK" en gebruikt u de methoden in dit artikel om het probleem op te lossen.

U hoeft de fout echter niet onmiddellijk op te lossen. U kunt met de spreadsheet blijven werken met de bedoeling het probleem later op te lossen.

In dat geval moet u weten hoe u kringverwijzingen in Excel kunt vinden nadat u het waarschuwingsvenster hebt gesloten.

Hoe circulaire verwijzingen in Excel te vinden

Excel heeft een ingebouwde tool voor foutcontrole die verschillende soorten fouten vindt, waaronder kringverwijzingen. U opent de tool op het tabblad Formules.

Deze afbeelding toont de locatie binnen de Formula Auditing-groep.

Nu u weet hoe u de tool kunt vinden, volgen hier de gedetailleerde stappen om deze te gebruiken:

1. Ga naar het tabblad Formules.

1. Klik op de vervolgkeuzelijst Foutcontrole in de groep Formulecontrole.

2. Selecteer "Circulaire verwijzingen" in de vervolgkeuzelijst.

3. Klik op de koppeling om met uw cursor naar de eerste cel met een probleem te springen.

De onderstaande afbeelding laat zien dat de tool een kringfout heeft gevonden in cel G1. De weergegeven cel is een hyperlink.

Wanneer u op de koppeling klikt, gaat uw cursor naar de cel. Dit is erg handig bij een grote hoeveelheid gegevens.

Zodra je de eerste kringverwijzing hebt opgelost, kun je teruggaan naar de tool Foutcontrole om de volgende op het blad te vinden.

Gebruik de stappen in de latere secties van dit artikel om het probleem op te lossen en gebruik het hulpprogramma om naar het volgende probleem te gaan.

Als er geen circulaire problemen meer zijn, wordt het menu-item "Circulaire verwijzingen" in het vervolgkeuzemenu grijs weergegeven. Je hebt verder geen werk te doen.

Cirkelreferenties zoeken met de Excel-statusbalk

Een andere manier om een ​​kringverwijzing te herkennen, is door naar de statusbalk onder aan het Excel-werkblad te kijken.

Als er een of meer problemen in het werkblad zijn, geeft de statusbalk de locatie van één probleemcelverwijzing weer.

Als er bijvoorbeeld een kringverwijzing is in cel C1 en er zijn geen andere problemen met de spreadsheet, dan zal de statusbalk lezen: "Circulaire verwijzingen: C1".

Op deze afbeelding ziet u waar u het display kunt zien.

Hoe verborgen kringverwijzingen in Excel te vinden

Verborgen kringverwijzingen in Excel zijn kringverwijzingen die niet onmiddellijk worden gedetecteerd door de ingebouwde foutfuncties. Met andere woorden, Excel geeft niet de standaardwaarschuwing weer.

Ze gebeuren wanneer een cel indirect naar een andere cel verwijst, via een reeks tussenliggende cellen, en uiteindelijk terugverwijst naar de oorspronkelijke cel.

Excel heeft twee hulpprogramma's waarmee u relaties kunt opsporen en deze problemen kunt onderzoeken:

1. Trace Dependents-tool

2. Trace Precedents-tool

Hoe de Trace Dependent Tool te gebruiken

Ervaren Excel-gebruikers profiteren van de Trace Dependents-tool om deze problemen te onderzoeken en kringverwijzingen op te lossen. Door een of meer cellen te kiezen en deze tool te gebruiken, toont Excel u de afhankelijke cellen.

De tool is beschikbaar in de sectie Formulecontrole op het tabblad Formules.

Volg deze stappen om de tool te gebruiken:

1. Klik op de cel of cellen waarvoor u personen ten laste wilt traceren.

2. Ga naar het tabblad 'Formules' in het Excel-lint.

3. Klik op de knop 'Trace Dependents' in de groep 'Formula Auditing'.

4. Bekijk de tracerpijl of -pijlen die afhankelijkheden tonen.

Excel toont pijlen van de actieve celwaarde naar cellen die ervan afhankelijk zijn. De blauwe pijl in de onderstaande afbeelding geeft aan dat cel D6 afhankelijk is van cel B2.

Trace Precedents-tool

Precedenten zijn het tegenovergestelde van personen ten laste. Met het hulpmiddel Trace Precedents kunt u een cel met een formule kiezen en alle cellen zien waarvan deze afhankelijk is, dwz de precedenten.

De tool is beschikbaar in de sectie Formulecontrole op het tabblad Formules.

Volg deze stappen om de tool te gebruiken:

1. Klik op de cel met de formule die u wilt onderzoeken.

2. Ga naar het tabblad Formule in het Excel-lint.

3. Klik op de knop 'Precedenten traceren' in de groep 'Formule-auditing'.

4. Bekijk de traceerpijlen die in Excel worden weergegeven.

De blauwe pijlen in de onderstaande afbeelding laten zien dat cel D6 afhankelijk is van het celbereik A2:B5.

Door beide tools te gebruiken, kunt u Excel-kringverwijzingen vinden die niet meteen duidelijk zijn. De volgende stap is om het probleem in de geselecteerde cel op te lossen. U kunt dan verder zoeken naar hetzelfde probleem in andere cellen.

2 manieren om kringverwijzingen in Excel te verwijderen

Er zijn twee hoofdmethoden om kringverwijzingen op te lossen:

• het doorbreken van de kringverwijzing

• alternatieve formules gebruiken

Laten we beide manieren uitleggen.

1. De cirkelreferentie doorbreken

Het verbreken van de kringverwijzing in Excel is een efficiënte manier om het probleem op te lossen. Dit vereist dat u de cellen identificeert die betrokken zijn bij de kringverwijzing en hun formules dienovereenkomstig aanpast.

Volg hiervoor de eerder beschreven stappen om het celadres te vinden.

Vervolgens moet u de formule in de formulebalk bekijken. Zoek naar een verwijzing naar de cel zelf.

In ons eerste voorbeeld van een kringverwijzing bevatte de optelformule in cel C1 een verwijzing naar de cel zelf:

=A1 + B1 + C1

Dit kan worden opgelost door de laatste toevoeging te verwijderen. Zodra dit probleem is opgelost, gaat u verder met het vinden van andere kringverwijzingen en adresseert u deze.

2. Gebruik alternatieve formules

Enkele van de meest voorkomende oorzaken van kringverwijzingen zijn bij het optellen of middelen van cellen. De fout is om de resultaatcel in de formule op te nemen.

In ons vorige voorbeeld gebruikte de formule opteloperatoren om de som van een celbereik te berekenen. De alternatieve methode is om de SUM Excel-functie te gebruiken.

Om het voorbeeld te corrigeren, vervangt u de formule door:

=SOM(A1:B1)

Op dezelfde manier zou je een bereik kunnen middelen met een formule als =(A1+B1)/2. Het is echter minder waarschijnlijk dat de GEMIDDELDE functie tot een fout leidt.

=GEMIDDELDE(A1:B1)

5 tips om circulaire verwijzingen te voorkomen

Om kringverwijzingen in Microsoft Excel te voorkomen, is het essentieel om een ​​duidelijk begrip te hebben van hoe uw formules met elkaar omgaan in uw Excel-blad.

Door de relaties tussen cellen te identificeren en je bewust te zijn van hoe deze verbindingen samenwerken, kun je voorkomen dat je cirkelvormige formules maakt.

Hier zijn onze vijf beste tips om kringverwijzingen te voorkomen.

1. Wees voorzichtig bij het selecteren van bereiken

In mijn ervaring is een van de meest voorkomende oorzaken van kringverwijzingen wanneer gebruikers snel bereiken selecteren om in hun formules op te nemen.

Wanneer u verticaal en/of horizontaal veegt om cellen te groeperen, kunt u eenvoudig nog een cel selecteren die grenst aan het juiste bereik. Als die cel ook de resultatencel is, hebt u per ongeluk een kringverwijzing gemaakt.

Deze fout wordt meestal gemaakt bij het werken met grote gegevenssets, zoals het uitvoeren van chikwadraattests. De oplossing is om voorzichtig te zijn met uw bereikselectie.

2. Plan uw formules zorgvuldig

Denk voordat u formules invoert na over de logica achter uw berekeningen en hoe ze door cellen moeten lopen.

U kunt de berekeningen vooraf in kaart brengen met behulp van diagramtools of een whiteboard.

Dit kan u helpen bij het identificeren van mogelijke lussen die kunnen leiden tot kringverwijzingen.

3. Gebruik alternatieve formules

Bepaalde functies, zoals INDIRECT of OFFSET , kunnen onbedoeld kringverwijzingen creëren als ze niet op de juiste manier worden gebruikt.

Overweeg het gebruik van alternatieve functies die vergelijkbare functionaliteit bieden zonder het risico van kringverwijzingen.

4. Verdeel complexe formules in kleinere delen

In plaats van een enkele complexe formule met meerdere geneste functies te maken, kunt u overwegen uw berekeningen op te splitsen in kleinere, beter beheersbare delen.

Dit maakt uw werkmap niet alleen gemakkelijker te begrijpen en te onderhouden, maar verkleint ook de kans op het introduceren van een kringverwijzing.

5. Los problemen gaandeweg op

Bij het werken met grote of gecompliceerde werkmappen is het ook essentieel om te controleren op kringverwijzingen. In plaats van problemen te negeren totdat een grote set gegevens en formules is verzameld, is het beter om elke fout op te lossen wanneer deze zich voordoet.

U kunt ervoor kiezen om een ​​waarschuwingsbericht voor kringverwijzingen korte tijd te negeren. U moet echter de functie Foutcontrole gebruiken om de bestaande kringverwijzingen snel in te halen.

Dit maakt het gemakkelijker om ze op te lossen en hun effecten op uw werkmap te voorkomen.

Hoe iteratieve berekening inschakelen en waarom moeite doen?

Meestal wilt u kringverwijzingen in uw werkblad vermijden. Er zijn echter enkele scenario's waarin ze nodig zijn.

Sommige technische of wetenschappelijke berekeningen moeten bijvoorbeeld het resultaat van een eerdere berekening gebruiken. Dit staat bekend als een iteratieve oplossing.

Elke lus verfijnt de resultaten van een berekening totdat een specifieke drempel of doel is bereikt.

In deze gevallen is het essentieel om iteratieve berekeningen in de Excel-instellingen in te schakelen om bedoelde kringverwijzingen mogelijk te maken. Hierdoor kan de software de berekeningen een bepaald aantal keren herhalen en zo tot een oplossing komen.

Volg deze stappen om een ​​kringverwijzing te laten werken in uw Excel-bestand:

1. Klik op het tabblad Bestand.

2. Selecteer opties.

3. Kies de categorie Formules.

4. Vink onder de sectie 'Berekeningsopties' het vakje aan voor 'Iteratieve berekening inschakelen'.

5. Wijzig het aantal in het vak voor maximale iteraties of laat de standaardwaarde 100 staan.

6. Gebruik de maximale wijzigingsbox om iteraties te verminderen volgens uw vereisten.