Ontdek (verborgen) kringverwijzingen in je spreadsheet

(Verborgen) kringverwijzingen doen zich voor wanneer Excel probeert een resultaat te berekenen van een cel, waar in dezelfde formule al eerder naar verwezen is. Excel waarschuwt ons niet voor verborgen kringverwijzingen (conditional circular references). In deze video laten we je zien hoe PerfectXL kringverwijzingen voor je detecteert.

Introductie

Veel spreadsheetontwikkelaars herkennen dit bericht:

Excel Pop-up Bericht :: Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

Excel detecteert een ‘circular reference’, of kringverwijzing, als een formule dezelfde cel meerdere malen aandoet.

Case

Ik kreeg een keer een spreadsheet van een collega. Alles zag er goed uit, totdat ik een van de inputcijfers veranderde:

Bronwaarde aanpassen :: Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

Toen verscheen dit irritante bericht:

Excel Pop-up Bericht :: Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

Ik belde mijn collega en hij was ervan overtuigd dat ik een van de formules moest hebben veranderd. Dus, hoe is dit gebeurd? Eerst zal ik uitleggen wat een kringverwijzing is, en hoe Excel deze behandelt.

Soorten kringverwijzingen

Simpelweg zijn er maar twee soorten die echt belangrijk zijn:

  • Bewuste cirkelverwijzingen
  • Onbewuste cirkelverwijzingen

Intermezzo: Excel’s kringverwijzing analyse tool

Excel kan zelf kringverwijzingen in je bestand detecteren en geeft dan een melding. Zodra je op ‘OK’ klikt in het waarschuwingsvenster, vind je de eerste kringverwijzing die Excel detecteert in de Excel ribbon:

Excel Ribbon :: Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

Deze detectie is echter niet waterdicht. Als nieuwe waarden de berekening anders laten verlopen, dan kan het heel goed zijn dat Excel de kringverwijzing daarna niet meer vindt. Bijvoorbeeld, als een cel binnen de kringverwijzing een ALS functie bevat, welke andere cellen in haar ‘WAAR’ argument gebruikt, dan in haar ‘ONWAAR’ argument, dan is het afhankelijk van de resultaten van het test argument of de uiteindelijke berekening als kringverwijzing wordt gezien (hier wordt deze methode bewust gebruikt).

De derde en geniepige soort kringverwijzing

Dit betekent dat er eigenlijk een derde soort kringverwijzing is:

  • Verborgen cirkelverwijzingen

Laten we nu naar de 3 verschillende soorten kringverwijzingen kijken.

Bewuste kringverwijzingen

Dit betreft het gebied van ervaren Excel gebruikers, de Excel whizz kid die weet wat hij doet.

Sommige gebruikers creëren bewust (sets met) kringverwijzingen bij de bouw van een calculatiemodel om iteratieve berekeningen mee te doen. Denk bijvoorbeeld aan het model voor een chemische fabriek waar (een deel van) de uiteindelijke productie output een gerecycled restant is van hetzelfde proces. Distilleerderijen hebben vaak een dergelijke stroom en als je het model van zo’n fabriek wilt berekenen, dan kan een kringverwijzing nuttig zijn om het model correct te formuleren.

Alleen als je er 100% van overtuigd bent dat je niet zonder kringverwijzing kunt om je probleem op te lossen, documenteer je model dan uitermate zorgvuldig. Beschrijf helder wat je intenties zijn en hoe het model precies functioneert.

Bepaal van tevoren of het model dat je probeert te bouwen werkelijk van circulaire aard is. Er zijn namelijk vaak mogelijkheden om berekeningen te herschrijven op een manier waarbij er geen kringverwijzingen nodig zijn. Die methode heeft dan t.a.t. de voorkeur.

Onbewuste kringverwijzingen

De meeste kringverwijzingen worden echter onbewust gemaakt, zoals in een SOM formule, waarbij de cel met de formule wordt meegenomen in de berekening.

Voorbeeld: In cel A10, schrijf je de functie =SOM(A1:A10)

De bedoeling van deze functie was natuurlijk om alle cellen boven cel A10 op te tellen, de functie zou er als volgt uit moeten zien =SOM(A1:A9).

Verborgen kringverwijzingen

Stel je voor dat je wilt bijhouden op welke datum iemand een waarde in kolom A invoert. Een vaak gebruikte methode is om een dergelijke formule te gebruiken met een naar zichzelf verwijzende cel:

Een naar zichzelf verwijzende cel Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

Zo lang cel A1 leeg is, is het resultaat van de ALS functie is cel B1 ‘WAAR’ en berekent Excel alleen de VANDAAG functie in cel B1. Dus of er sprake is van een kringverwijzing hangt af van de inputwaarde! In de huidige situatie krijg je dus geen waarschuwing van Excel. Je kunt je voorstellen dat dergelijke verborgen kringverwijzingen het erg moeilijk maken om mogelijke problemen in je spreadsheet te ontdekken.

Kringverwijzingen detecteren

PerfectXL detecteert de 3 soorten kringverwijzingen voordat Excel ze vindt.

Om ze te vinden, draai een PerfectXL analyse op je gehele bestand en klik je op ‘Risk Overview’:

Risico overzicht :: Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

We kijken naar de tab “Structure Issues”.

Structuurproblemen :: Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

Als je erop klikt zie je een lijst van alle structuur gerelateerde problemen in je Excel bestand.

Structuurproblemen :: Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

Er is in ons voorbeeld maar sprake van 1 structuurprobleem: een kringverwijzing. Klik er op in de tabel om meer details over het probleem te ontdekken:

Structuurproblemen - details :: Ontdek (verborgen) kringverwijzingen in je spreadsheet :: PerfectXL

Zoals je kunt zien betreft ons voorbeeld een verborgen kringverwijzing. Op basis van de resultaat van de MATCH functie (VERGELIJKEN), richt de formule in cel C7 zich op cel C8 of op cel C9. Gezien dat C8 de formule =C7 bevat, krijg je de standaard Excel foutmelding alleen te zien als de MATCH functie resulteert in ‘1’. PerfectXL waarschuwt daarentegen altijd voor kringverwijzingen. De tool vindt alle potentiële kringverwijzingen, zelfs die die verborgen zijn.

Tekst & vertelling: Jan Karel Pieterse, Microsoft Excel MVP since 2002, JKP Application Development Services

Meer Excel Add-in Video Tutorials

Verborgen worksheets, rijen en kolommen weergeven en opnieuw verbergen

Verborgen worksheets, rijen & kolommen weergeven

Als je een spreadsheet aan een klant moet presenteren kan het verbergen van bepaalde werkbladen, rijen of kolommen bijdragen aan de leesbaarheid ervan, maar voor de spreadsheetbouwer zijn verborgen elementen vervelend en verwarrend. In deze video laten we je zien hoe je alle verborgen items in je spreadsheet eenvoudig kunt weergeven en opnieuw verbergen met de PerfectXL add-in.

Verborgen elementen weergeven en weer verbergen

Opgenomen macro's detecteren in je spreadsheet

Opgenomen macro’s detecteren in je spreadsheet

VBA is erg krachtig, maar wanneer het niet correct wordt gebruikt kan het ook een groot risico vormen. Vooral ‘recorded macros’ (opgenomen macro’s) zijn onbetrouwbaar en zeer context-afhankelijk. In deze video leggen we je uit hoe je opgenomen macro’s kunt detecteren in je spreadsheet met behulp van de PerfectXL add-in voor Excel.

Opgenomen macro’s detecteren in je spreadsheet