Een circulaire verwijzing in Excel vinden en oplossen
Een circulaire verwijzing ontstaat wanneer een formule naar zijn eigen cel verwijst, rechtstreeks of via andere cellen. Excel kan dit niet berekenen, dus geeft het een waarschuwing en toont het meestal 0. Om het op te lossen open je Formules > Foutcontrole > Circulaire verwijzingen, ga je naar de cel die daar wordt getoond en pas je de formule zo aan dat hij niet meer van zichzelf afhangt.
Deze gids laat zien hoe je de lus vindt, welke vijf situaties de meeste lussen veroorzaken en hoe je elk ervan oplost, en het zeldzame geval waarin een lus bewust is gemaakt. Oefen de onderliggende vaardigheden in de lessen die aan het eind zijn gelinkt.
1. Wat een circulaire verwijzing is
Een formule zoals =A1+B1 die je in A1 typt, vraagt Excel om een waarde die eerst A1 nodig heeft, en A1 heeft de formule nodig. Er is geen beginpunt, dus Excel stopt. Een lus kan ook indirect zijn: A1 gebruikt B1, B1 gebruikt C1 en C1 gebruikt A1. Geen enkele formule lijkt fout, maar samen draaien ze in een cirkel.
Wanneer je zo’n formule invoert, toont Excel een waarschuwing. Nadat je die sluit, toont de cel meestal 0, en de statusbalk zegt Circulaire verwijzingen, gevolgd door een celadres.
2. De cel vinden
Ga naar Formules > Foutcontrole, wijs Circulaire verwijzingen aan en kies een cel uit de lijst. Excel springt naar die cel. Klik op Formules > Formules weergeven, of druk op Ctrl+` (de toets boven Tab), om elke formule als tekst te zien. Dat maakt een verwijzing naar zichzelf makkelijker te herkennen. Gebruik Formules > Precedenten opsporen om pijlen te tekenen naar de cellen die de formule leest: een pijl die terugkomt bij dezelfde cel is de lus.
De statusbalk noemt maar één cel, en die kan midden in de lus liggen. Los die cel op en controleer daarna de lijst opnieuw, want er kunnen meerdere lussen in een werkmap zitten.
3. Een totaal oplossen dat zichzelf bevat
De meest voorkomende oorzaak: een totaal dat binnen het bereik staat dat het optelt, zoals =SOM(B2:B10) getypt in B10. De formule telt de cel op waarin hij zelf staat. Verander het bereik zodat het boven het totaal stopt, =SOM(B2:B9), of verplaats het totaal buiten de gegevens. Een rij invoegen binnen een bereik kan het bereik ook uitbreiden tot een totaalrij, dus controleer het bereik na het invoegen.
4. Een percentage of prijs oplossen die zichzelf voedt
Twee cellen die elk van de ander afhangen, veroorzaken een indirecte lus. Bijvoorbeeld een prijs met btw in C2 die een korting in D2 gebruikt, terwijl D2 wordt berekend als percentage van C2. Bepaal welke waarde de invoer is en welke het resultaat, en bereken het resultaat alleen uit de invoer. Bereken hier de korting uit de prijs zonder btw, en daarna de eindprijs uit beide.
Schrijf eerst de volgorde van de berekening op papier op: eerst de invoer, dan elk resultaat om de beurt. Als de volgorde een resultaat nodig heeft dat nog niet bestaat, heeft het model een echt circulair logica-probleem en moet je het herstructureren, niet alleen een formule aanpassen.
5. Een lopend totaal oplossen dat naar zichzelf verwijst
Een lopend totaal in C2 dat als =C2+B2 is geschreven, verwijst naar zichzelf. De juiste vorm telt het vorige totaal op: in C3 =C2+B3, en in C2 gewoon =B2. Kopieer naar beneden. Zet een dollarteken voor de eerste cel van een bereik wanneer je vanaf boven optelt: =SOM($B$2:B3). Zo kun je de formule kopiëren zonder dat het beginpunt verschuift.
6. Een formule oplossen die is gekopieerd of verplaatst
Soms wordt een goede formule circulair nadat je hem hebt gekopieerd, omdat een relatieve verwijzing nu naar de eigen cel wijst. Controleer de formule op de nieuwe plek en gebruik een dollarteken op het deel dat niet mag verschuiven. Een opzoektabel die is geplakt over de cellen die hij leest, is een andere bron. Let ook op een verwijzing in een ander werkblad of een andere werkmap die terugwijst naar het oorspronkelijke werkblad.
7. Wanneer een lus opzettelijk is
Sommige modellen hebben een lus nodig, zoals rente berekend over een gemiddeld saldo dat de rente zelf bevat. Ga in dat geval naar Bestand > Opties > Formules en vink Iteratieve berekening inschakelen aan. Stel het maximale aantal iteraties in (100 is de standaard) en de maximale wijziging (0.001). Excel berekent de lus dan opnieuw tot de wijziging kleiner is dan die grens.
Zet het alleen aan wanneer je het echt nodig hebt. Met iteratie aan waarschuwt Excel niet meer voor per ongeluk gemaakte lussen, dus een typfout kan lang verborgen blijven. Zet naast het model een notitie dat iteratie nodig is.
Veelgemaakte fouten
Iteratieve berekening aanzetten om de waarschuwing te laten verdwijnen. De waarschuwing is weg, maar de formule hangt nog steeds van zichzelf af en geeft een verkeerd getal.
Oplossing: Zoek de cel, corrigeer de formule en laat iteratie uit, tenzij het model het nodig heeft.
De 0-uitkomst negeren. Een circulaire formule toont meestal 0, en dat ziet eruit als een echte waarde.
Oplossing: Zie een verrassende 0 als een teken om in de statusbalk te kijken naar Circulaire verwijzingen.
Alleen de cel repareren die Excel noemt. De statusbalk noemt één cel, maar de lus kan ook door andere cellen lopen.
Oplossing: Open na elke correctie Foutcontrole opnieuw, tot de vermelding Circulaire verwijzingen verdwenen is.
Veelgestelde vragen
Waarom toont Excel 0 bij een circulaire verwijzing?
Excel kan de berekening niet afmaken, dus behoudt het de laatste waarde die het had, en dat is 0 voor een nieuwe formule. De cel is niet correct, ook al toont hij een getal.
Waarom krijg ik geen waarschuwing voor een circulaire verwijzing wanneer ik het bestand open?
De werkmap heeft mogelijk iteratieve berekening aan staan, of de waarschuwing is uitgeschakeld in de Excel-opties. Controleer Bestand > Opties > Formules.
Kan {fn:IF} een circulaire verwijzing stoppen?
Niet op zichzelf. Excel ziet de verwijzing in de formule nog steeds, ook als ALS (Engels: IF) hem niet zou gebruiken. Verwijder de verwijzing, of gebruik een volgorde van berekening die hem niet nodig heeft.
Verwante functies
- SUMSUM telt getallen, cellen en hele bereiken op in één formule.
- IFIF controleert een voorwaarde en geeft één waarde terug als die waar is en een andere als die onwaar is.
- IFERRORIFERROR geeft een waarde terug die jij kiest wanneer een formule een fout oplevert, zoals #N/A of #DIV/0!, en anders het gewone resultaat.
Meer over circulaire verwijzingen
De gids laat zien hoe je een lus vindt en de gewone oorzaken oplost. Dit deel voegt toe hoe je de pijlen leest, hoe een lus verborgen kan zitten over meerdere werkbladen, en hoe je een model bouwt dat iteratie nodig heeft, op een veilige manier.
De spoorpijlen lezen
Selecteer de cel die Excel noemt en kies Formules > Precedenten opsporen. Blauwe pijlen wijzen naar elke cel die de formule leest. Klik er nogmaals op om één niveau verder terug te gaan. In een lus leiden de pijlen je in een rondje en komen ze terug bij de cel waar je begon. Afhankelijken opsporen werkt de andere kant op en toont welke cellen de geselecteerde cel gebruiken. Dat helpt wanneer je niet weet welke formule je eerst moet aanpassen.
Verwijder de pijlen met Pijlen verwijderen wanneer je klaar bent, want ze blijven op het werkblad staan tot je ze wist.
Lussen die over werkbladen verborgen zitten
Een lus hoeft niet op één werkblad te blijven. Een overzichtsblad kan een detailblad lezen dat, in een hoekje dat je zelden opent, het overzicht weer terug leest. Foutcontrole toont de cel, en de naam van het werkblad staat in de formulebalk. Wanneer de spoorpijlen een ander werkblad bereiken, worden ze een gestreepte lijn met een klein werkbladpictogram. Dubbelklik op de gestreepte pijl om naar de andere cel te springen.
=SOM(Detail!C2:C20)
=Summary!B2*0,1Iteratie veilig gebruiken
Als het model echt een lus nodig heeft, houd hem dan klein. Zet de formule met de lus in één duidelijk benoemde cel en geef hem een verstandige startwaarde, zodat de eerste ronde geen fout geeft. Omhul de formule met ALS.FOUT (Engels: IFERROR), want een fout binnen een lus blijft rondgaan en verdwijnt nooit. Vergelijk het resultaat één keer met een berekening met de hand, om zeker te weten dat de lus op het juiste getal uitkomt.
Oefen de verwijzingen die de meeste lussen voorkomen in [de les over celverwijzingen](/learn/cell-references), en de foutentools in [de les over fouten](/learn/errors-iferror).
Gewoonten die lussen voorkomen
Houd invoer, berekeningen en resultaten in aparte gebieden, en laat informatie in één richting stromen, van invoer naar resultaten. Houd totalen buiten de bereiken die ze optellen. Wanneer je rijen invoegt, controleer dan de formules die over het invoegpunt heen optellen. De meeste lussen ontstaan doordat een totaal is verplaatst of een bereik is gegroeid, en een duidelijke indeling maakt beide makkelijk zichtbaar.