Controleren op kringverwijzingen
Kringverwijzingen in Excel zijn buitengewoon frustrerend, aangezien Excel je wel op de hoogte brengt van hun aanwezigheid, maar niet aangeeft waar ze zich bevinden. Als je met een groot bestand werkt kan het veel tijd kosten om de bron van het probleem te achterhalen. PerfectXL bespaart je deze moeite door het exacte pad van de kringverwijzing te tonen. Zo weet je precies waar de fout zit en hoe je deze kunt oplossen.
- Download het voorbeeldbestand om de stappen te volgen.
- Werk je al met PerfectXL? Ga dan direct naar de oplossing met PerfectXL.
Stappenplan zonder PerfectXL
Die afschuwelijke pop-up…
Als er een kringverwijzing in je bestand staat, dan verschijnt er een melding wanneer je het opent: een alom bekende (super irritante) pop-up voor veel Excel gebruikers:
Stap 1: Traceer de locatie(s)
Via Formulas -> Error Checking -> Circular References kun je de locatie(s) ervan traceren:
Stap 2: Controleer de locaties
Als we de aanwijzingen van de Error Check in ons testbestand volgen dan vinden we in cel C5 op tabblad “Summary” een verwijzing naar cel C6 op tabblad “Expenses.”
En in cel C6 op tabblad “Expenses” wordt terugverwezen naar cel C6 op tabblad “Summary!”
Dus waar komt het getal 45.400 in cel C5 op tabblad “Summary” vandaan en kunnen we erop vertrouwen?
Stap 3: Beoordeel het probleem
In cel B6 op tabblad “Expenses” zien we dat er sprake is van een optelling en de opzet van de tabel vertelt ons ook dat cel C6 logischerwijs een SOM zou moeten bevatten:
Stap 4: Corrigeer de fout
Wanneer we de fout gecorrigeerd hebben zien we dat het resultaat van de SOM inderdaad het getal 45.400 oplevert:
Stap 5: Controleer de correctie
De verwijzing in cel C5 op tabblad “Summary” is nu geen kringverwijzing meer en zodoende betrouwbare data geworden.
Ook de Error Check onder Formulas in de Excel ribbon bevestigt dat er geen kringverwijzingen meer in het bestand staan:
Wat als er verborgen kringverwijzingen in je bestand staan?
Soms is er sprake van een verwijzing die slechts in bepaalde situaties circulair is. In dat geval spreken we van een ‘verborgen kringverwijzing.’
Als eenvoudig voorbeeld voegen we een “Score”-kolom toe aan tabblad “Income” van ons voorbeeldbestand. Steeds wanneer er meer is binnengekomen dan geschat werd dan kennen we een score van 100 toe aan de score-kolom. Deze formule noteren we als volgt:
=IF (D4 > 0; 100; E4)
In feite zeggen we hier dus: zolang het getal in cel D4 groter dan nul is dan publiceren we het getal “100” in cel E4, maar wanneer het getal in cel D4 kleiner of gelijk aan nul is, dan publiceren we de waarde van cel E4 gelijk aan cel E4. De kringverwijzing openbaart zich zo alleen in het tweede geval.
Kijk maar, wanneer we de formule doortrekken naar rij 5 is er niets aan de hand, omdat de waarde in cel D5 ook groter dan nul is.
Maar trekken we de formule vervolgens verder door naar beneden, dan wordt de kringverwijzing ineens geopenbaard, omdat de waarden in cel D6 en D7 kleiner of gelijk aan nul zijn:
Als we nu de Error Check toepassen, dan wordt alleen de kringverwijzing in cel E6 ‘gelezen’, terwijl we weten dat cel E7 er ook een bevat (maar die toont Excel pas wanneer de eerste is opgelost):
Conclusie
In het bovenstaande voorbeeld zie je duidelijk dat een kringverwijzing afhankelijk kan zijn van variabelen. Dat vormt een risico. Bovendien hebben we een eenvoudig voorbeeld gebruikt; in meer omvangrijke spreadsheets, met ingewikkelde formules, kan het een hele zoektocht zijn om alle kringverwijzingen te ontdekken.
Stappenplan met PerfectXL
Stap 1: Controleren op kringverwijzingen via de PerfectXL add-in
Kringverwijzingen kunnen buitengewoon frustrerend zijn, vooral wanneer Excel je op de hoogte brengt van hun aanwezigheid maar niet de exacte locatie aangeeft. Voordat je jezelf gek maakt, probeer PerfectXL. Open het bestand dat je wilt inspecteren in Excel en ga naar PerfectXL in de menubalk. Kies daar, onder “Problemen detecteren,” de optie “Kringverwijzingen”.
Stap 2: PerfectXL Risk Finder analyseert het bestand
PerfectXL Risk Finder analyseert automatisch je bestand en opent een overzicht van gevonden kringverwijzingen (in dit geval slechts één).
Om meer informatie te krijgen over een kringverwijzing (zoals waar hij zich bevindt, hoe hij eruit ziet en hoe hij gerepareerd kan worden) hoef je er alleen maar op te klikken (in dit geval dus op “Samenvatting!C5”).
Stap 3: Inspecteer de details van een kringverwijzing
Op de detailpagina wordt de context van de kringverwijzing helder geschetst door middel van een screenshot van de locatie en een tabel met alle betrokken cellen (de highlights laten zien welke referenties de kringverwijzing veroorzaken). In dit geval gaat het om een eenvoudige kringverwijzing tussen twee cellen die naar elkaar verwijzen, maar PerfectXL is ook in staat om kringverwijzingen met complexe formuleketens te identificeren.
Vanaf dit scherm kun je rechtstreeks naar de locatie in je Excel-bestand navigeren door te klikken op het screenshot, of op een van de celreferenties in de tabel. Laten we het proberen met het screenshot.
Stap 4: Het probleem oplossen
Vanwege de eenvoud van ons voorbeeld, is het eenvoudig om dit probleem te doorgronden. Zoals te zien is in de tabel van PerfectXL Risk Finder (van de vorige afbeelding), betreft het probleem hier een eenvoudige kruisverwijzing. Door een of beide van de betrokken cellen aan te passen, zal het model weer correct functioneren.
Conclusie
PerfectXL Risk Finder helpt je om zowel de zichtbare als de verborgen kringverwijzingen in je bestand te vinden. Door een helder inzicht in de structuur van een kringverwijzing wordt het een stuk eenvoudiger om hem op te lossen. Als je er een gewoonte van maakt om je bestand te controleren op kringverwijzingen voordat je het deelt met collega’s of klanten, garandeer je jezelf van een soepele overdracht zonder onvoorziene problemen in de toekomst.