Hoe om te gaan met circulaire verwijzingen in Excel

In Excel typt u formules in om resultaten te krijgen en het is vrij gebruikelijk om daarbij naar andere cellen te verwijzen. Maar wat gebeurt er als u naar de cel zelf verwijst, direct of indirect? Dit betekent een oneindige lus van berekeningen! Gelukkig heeft Excel oplossingen om met dit soort situaties om te gaan.

Circulaire verwijzing betekent dat uw formule probeert de oorsprong cel te berekenen. Doorgaans wordt dit als een fout beschouwd. Er zijn echter momenten waarop deze fout juist nuttig kan zijn en u misschien opzettelijk een cirkelvormige verwijzing wilt maken. Laten we dit fenomeen eens nader bekijken aan de hand van enkele voorbeelden. U kunt de voorbeeldwerkmap hier downloaden.

Directe verwijzing

Als u bijvoorbeeld naar cel C3 gaat en vervolgens “=C3” intypt, zou u Excel een beetje in de war brengen, omdat deze cel nu naar zichzelf wijst, waardoor een cirkelvormige verwijzing wordt gecreëerd. Hetzelfde doen binnen een formule heeft ook hetzelfde effect (d.w.z. =IF(C3,…,…) of =C3+10).

Een ander veelvoorkomend voorbeeld is het opnemen van de oorspronkelijke cel bij het selecteren van een bereik uit aangrenzende cellen. Door bijvoorbeeld de cel C4 op te nemen in de somformule =SUM(C2:C4) wordt dit een cirkelvormige verwijzing.

Indirecte verwijzing

Een cel die zich richt op een cel die naar de oorspronkelijke cel wijst, creëert een indirecte verwijzing. Met andere woorden, een cirkelvormige verwijzing kan tot stand worden gebracht door twee cellen die naar elkaar wijzen. Bijvoorbeeld door “=D3” in cel B3 in te voeren, terwijl cel D3 een verwijzing van cel B3 bevat (“=B3” in dit geval).

Waarom is dit een probleem?

Excel waarschuwt de gebruiker wanneer er sprake is van een cirkelvormige verwijzing, omdat een cirkelvormige verwijzing in wezen neerkomt op eindeloze lussen. Excel opdragen dezelfde berekening oneindig vaak te herhalen, heeft niet veel zin. Om te voorkomen dat uw computer bevriest, negeert Excel gewoon circulaire verwijzingen.

Laten we eens kijken naar deze waarschuwingsmelding. U krijgt de volgende melding nadat u een formule met een cirkelvormige verwijzing hebt ingevoerd,

Merk op dat Excel niet dezelfde foutmelding geeft voor extra cirkelvormige formuleverwijzingen. Op de statusbalk van Excel wordt de laatste cel met een cirkelvormige verwijzing weergegeven. Dit betekent dat er meer dan één dergelijke verwijzing op dit tabblad kan staan. Dit is hoe het eruit ziet,

Excel houdt ook een lijst bij van alle cirkelvormige verwijzingen, die kan worden geopend via de optie Foutcontrole op het tabblad Formule,

Hoe er vanaf te komen

Er is helaas geen eenvoudige manier om ongewenste cirkelvormige verwijzingen te verwijderen of te repareren. U zult een van de eerder genoemde manieren moeten gebruiken om ze een voor een op te lossen. Om erachter te komen wat het probleem veroorzaakt, kunt u de optie Formulecontrole gebruiken op het tabblad Formule. Zie onze handleiding om te leren hoe u dit doet, https://www.spreadsheetweb.com/identifying-analyzing-spreadsheets-formula-auditing/

Kunnen ze nuttig zijn?

Ja! Hoewel, alleen onder bepaalde omstandigheden. Het kan zijn dat u met opzet een cirkelvormig formuleblok meer dan een paar keer wilt berekenen. Excel staat deze “iteratieve” berekeningsaanpak met behulp van limieten toe.

Om circulaire verwijzingen te activeren en te gebruiken, moet u ze eerst activeren door de optie Iteratieve berekeningen inschakelen in het menu Bestand aan te vinken.

Ga naar Bestand > Opties > Formules > Berekeningsopties in Excel 2016, Excel 2013 en Excel 2010.

In Excel 2007, moet u naar de knop Office > Excel-opties > Formules > Iteratiegebied

In Excel 2003 en eerder, moet u naarMenu > Extra > Opties > Berekening

Als u iteratieve berekeningen inschakelt, krijgt u twee extra ingangen in hetzelfde menu,

• Maximumiteraties bepaalt hoe vaak Excel de werkmap moet herberekenen
• Maximale verandering bepaalt het maximale verschil tussen waarden van iteratieve formules. Merk op dat een kleiner getal nauwkeurige resultaten betekent.

Iteratieve berekeningen stoppen wanneer aan een van de hierboven gedefinieerde voorwaarden is voldaan.

Gebruiksgevallen

Berekenen van de toekomstige waarde van een investering

Laten we aannemen dat we $10.000 hebben en dit geld willen beleggen op een CD-rekening. De maandelijkse rente op deze rekening is 1,25%. Download hier het voorbeeld werkboek voor deze use case. Om de totale waarde aan het eind van de 21e maand te berekenen, gaan we elke maand rekenen en de rente bij de vorige maand optellen.

Eerst voert u het startgeld in, de rente en de basisfunctie voor de totale waarde,

=waarde * ( 1 + rentepercentage)

Daarna selecteert u de cel met de beginwaarde in geld en voegt u de referentie van de functie voor de totale waarde toe.

Deze stap veroorzaakt een waarschuwing voor circulaire verwijzing als iteratieve berekeningen niet zijn ingeschakeld. Als u dat nog niet hebt gedaan, schakel deze optie dan in en stel Maximale iteraties in op 20 om de rente op de 21e maand te vinden.

Druk op OK.

Automatische tijdstempel

Cirkelvormige verwijzingen kunnen ook worden gebruikt om tijdstempels in cellen toe te voegen. Download hier de voorbeeldwerkmap voor deze use case. Stel dat we tijdstempels willen toevoegen aan de orders in onderstaande tabel,

We kunnen circulaire verwijzingen gebruiken om een tijdstempel toe te voegen wanneer een nieuwe order wordt ingevoerd.

Start met het toevoegen van een nieuwe kolom in de tabel om de tijdstempels in te plaatsen. Typ de formule in, =IF(A2<>””,IF(I2<>””,I2,NU()),””)

Deze formule controleert of het bestelnummer (cel A2) is ingevoerd. Als er een waarde is ingevoerd en de cel met de tijdstempel is leeg, dan retourneert de formule de functie NOW().

Merk op dat het indrukken van de Enter-toets een waarschuwing voor cirkelverwijzingen geeft als iteratieve berekeningen niet zijn ingeschakeld. De getallen Maximum Iteraties of Maximum Verandering betekenen deze keer niet veel omdat we maar één iteratie nodig hebben, dus u kunt deze twee invoerwaarden op hun standaardwaarden laten staan.

Nu wordt elke keer dat we een nieuwe order invoeren en een nieuwe rij maken, automatisch een tijdstempel afgedrukt in de kolom Tijdstempel.