Onnodige berekeningen verwijderen
Hoe weet je of je irrelevante berekeningen zonder consequenties kunt verwijderen uit je model? Je kunt natuurlijk per formule handmatig onderzoeken of er afhankelijkheden bestaan, maar PerfectXL maakt dit proces een stuk eenvoudiger. Ontdek alle afhankelijkheden in één oogopslag: beoordeel of output nog relevant is en of er formules weg kunnen of dat er eerst nog aanpassingen nodig zijn.
- Download het voorbeeldbestand om de stappen te volgen.
- Werk je al met PerfectXL? Ga dan direct naar de oplossing met PerfectXL.
Stappenplan zonder PerfectXL
Wat zijn “onnodige” berekeningen?
Het is misschien een beetje een vage term, maar wij classificeren iedere berekening die niet wordt gebruikt voor inzicht, rapportage of beinvloeding van andere delen van het model als “onnodig.” Bijvoorbeeld een optelling op een verborgen tabblad die niet wordt opgehaald in de samenvatting, omdat de samenvatting een eigen optelling gebruikt. Veel modellen staan vol met dergelijke overbodige berekeningen waar nooit naar gekeken of verwezen wordt en die daarom het bestand alleen maar trager en ingewikkelder maken. De traagheid maakt het model minder praktisch om mee te werken en de complexiteit maakt het moeilijker voor andere gebruikers om het model te begrijpen.
Stap 1: Traceer alle berekeningen
Om je model op te schonen en onnodige berekeningen te verwijderen, moet je eerst de berekeningen in je bestand lokaliseren. Dit kun je doen door de optie “Show Formulas” onder “Formules” in het hoofdmenu van Excel te gebruiken. De formules op je werkblad worden dan zichtbaar.
Stap 2: Controleer of de berekening ergens wordt gebruikt
Vervolgens moet je per berekening bepalen of er ergens in dit model (of in een ander model) wordt verwezen naar de cel waarin de berekening zich bevindt. Gebruik de functie “Trace Dependents” onder “Formulas” in de Excel ribbon om pijlen weer te geven die laten zien welke cellen worden beïnvloed door de waarde van de geselecteerde cel. Je kunt de sneltoets CTRL+] gebruiken om naar de “dependents” van die cel te navigeren.
N.B.: Er is geen overzicht van ongebruikte cellen, en cellen die worden gebruikt datavalidatielijsten, voorwaardelijke opmaakregels of grafieken zullen geen “dependents” tonen.
Stap 3: Bepaal of een berekening ergens anders wordt gebruikt
Om te bepalen of een berekening wordt gebruikt in een ander bestand of in VBA kun je alleen maar hopen dat het model documentatie bevat waarin duidelijk wordt of er verwijzingen vanuit andere bronnen zijn.
Wordt het resultaat van een berekening gebruikt in een rapport, gecommuniceerd met een klant of andere partij, of gebruikt een gebruiker van dit model deze waarde nog op een andere manier? Helaas bestaat er geen technologie om deze vraag voor je te beantwoorden, maar het is wel een belangrijk punt om te controleren om er zeker van te zijn dat het getal niet wordt gebruikt in e-mailcorrespondentie of op een andere manier buiten het model.
Conclusie
Als je uiteindelijk kunt bepalen dat een berekening niet wordt gebruikt binnen of buiten het model, dan moet die cel worden verwijderd. Overbodige zaken in een spreadsheet groeien en verspreiden zich als een virus. Wanorde creeert meer wanorde, en rommelige spreadsheets worden bij elk gebruik rommeliger. Probeer het bestand alleen relevante/noodzakelijke berekeningen te laten bevatten. Je kunt zelfs overwegen om een aparte spreadsheet te gebruiken voor noodzakelijke snelle inzichten, zodat “hulpformules” niet ongelooflijk in de weg gaan zitten!
Stappenplan met PerfectXL
Hoe weet je of een berekening onnodig is?
PerfectXL heeft twee tools die actief helpen bij het detecteren en verwijderen van onnodige berekeningen. PerfectXL Explore bekijkt het model als geheel (worksheets vol onnodige berekeningen kunnen in één keer worden verwijderd) en PerfectXL Highlighter bekijkt het bestand per cel; hoe ze worden gebruikt en hoe ze verbonden zijn met de rest van het bestand.
Stap 1: Scan het hele bestand in één keer
Open PerfectXL Explore en selecteer het bestand dat je wilt scannen. Op deze manier kun je het hele bestand in één keer evalueren en eventuele ongebruikte werkbladen verwijderen (zie “Modellen vereenvoudigen” voor meer informatie).
Stap 2: Bepaal of er ongebruikte werkbladen zijn
Na het analyseren van je bestand zal PerfectXL Explore een visualisatie van je model weergeven. Het toont je alle werkbladen en externe bronnen die in het bestand worden gebruikt. De pijlen geven aan hoe deze werkbladen en bronnen met elkaar verbonden zijn. De visualisatie van ons testbestand laat zien dat alle werkbladen en bronnen verbonden zijn, wat betekent dat ze allemaal op de een of andere manier worden gebruikt.
Stap 3: Controleer individuele formules
Het volgende wat we willen bepalen, is of er individuele formules (of formuleclusters) zijn die niet worden gebruikt. Open het bestand in Excel en selecteer het eerste werkblad dat je wilt scannen op ongebruikte formules. Navigeer naar het PerfectXL-tabblad in het hoofdmenu en selecteer “Input Output Cells” onder “Highlight Cells”.
Stap 4: Bepaal of er sprake is van onnodige berekeningen
Alle “Unused Data” en “Formula Output” die niet direct wordt gebruikt als indicator of onderdeel van een rapport heeft geen impact op de rest van het model (geen afhankelijken). Zo is het direct duidelijk of er onnodige berekeningen op je werkblad staan.
Stap 5: Lijst met gemarkeerde cellen
Je kunt ook de optie “Show list of highlighted cells” selecteren om alle gemarkeerde cellen op een rij te zien (en niet alleen die die je op het scherm passen – erg handig bij grotere bestanden).
Herhaal deze stappen voor alle input- en berekeningsbladen. Wees extra voorzichtig met dashboards en output-bladen, aangezien deze vaak worden gebruikt voor rapporten of het hele doel van het model zijn.
Conclusie
PerfectXL Explore en PerfectXL Highlighter maken het mogelijk om je bestand op te schonen met de zekerheid dat je geen vitale informatie verwijdert. Op deze manier kun je veilig onnodige berekeningen verwijderen en ervoor zorgen dat je model niet overdreven ingewikkeld of langzaam wordt.