Controleer formules en foutmeldingen in Excel
Kom je geen wijs meer uit de ‘spaghetti’ van formules of loop je tegen foutmeldingen aan in Excel? Los het op met deze tips.
“Kun je mij uitleggen hoe deze formule werkt?”, is een van de vragen die ik dan krijg, en kijkend in hun werkblad zie ik in de formulebalk een onbegrijpelijke geneste constructie die mij doet denken aan een ‘spaghetti-de-la-casa’. Bij AVK kun je terecht voor een excel training waarmee jij het maximale uit Excel leert halen.
Of: “In deze cel staat een foutmelding, kun je de fout weghalen?” Natuurlijk kan ik dat, de ‘Delete’-toets zit er niet voor niets… Maar men is dan uit op het opheffen van de oorzaak van de fout.
Formules controleren op fouten is niet altijd eenvoudig. Soms lukt me dat ter plekke, maar als de oplossing te diep onder de oppervlakte ligt is meer tijd nodig, meer dan soms beschikbaar is. En wanneer ik dan terug verwijs naar degene die het werkblad met al die ingewikkelde formules oorspronkelijk heeft samengesteld (er is toch iemand die de spaghetti heeft gemaakt), komt de aap uit de mouw: “Dat zouden we wel willen, maar die is net vorige maand vertrokken en werkt nu ergens anders…….” Mmmm…
Laat ik in dit blog eens vertellen over methoden die je in Excel kunt gebruiken om je formules in je werkbladen te controleren. Om de oorzaken van fouten op te sporen of om te doorgronden hoe ‘die afschuwelijke ingewikkelde formule’ werkt zoals die werkt. Gewoon omdat deze methoden bij zoveel gebruikers bij lange na niet bekend genoeg zijn.
Automatische foutaanduiding in Excel
Een resultaat in de cel zoals bijvoorbeeld ‘#N/B’, ‘#WAARDE!’ of ‘#DEEL/0’, kan duiden op een fout in de formule. Standaard is de optie ingeschakeld dat Excel de aanwezige formules automatisch controleert op onregelmatigheden. Met een klein groen driehoekje in de linkerbovenhoek van een cel geeft het programma aan dat er met die cel iets bijzonders aan de hand is. Mogelijk een fout in de formule.
   
Als je dat wilt onderzoeken selecteer je die cel en klik je vervolgens op het kleine gele ruitvormige icoontje dat ernaast verschijnt. Het keuzemenu geeft je verschillende opties om de fout te onderzoeken.
Opmerking: Heb je wel formulefouten maar zie je de groene driehoekige aanduidingen daarover in de cellen niet verschijnen? Dan is bij jou de automatische foutcontrole niet ingeschakeld. Ga naar Bestand – Opties en controleer de instellingen in de sectie ‘Regels voor foutcontrole’ in de categorie ‘Formules”.
Controle met het Lint
Excel biedt ruime mogelijkheden om jouw formules te controleren. Er is zelfs een aparte groep opdrachtknoppen voor gemaakt. Kijk maar eens in het Lint, op de tab ‘Formules’.
Formules weergeven
Een manier om de berekeningen te controleren is om de formules zichtbaar te maken. Deze weergave kan met name van pas komen wanneer je op jouw werkblad zeer veel formules hebt staan die je wil controleren. Klik op de knop ‘Formules weergeven‘ om op je werkblad niet de uitkomsten, maar juist de formules weer te geven. Als je genoeg hebt gezien zet je met een klik op dezelfde knop de weergave weer uit. Overigens gebruik ik deze weergave ook wel eens als ik een keer niet de uitkomsten maar de formules zelf wil afdrukken.
Broncellen en Doelcellen aanwijzen
Formules via cel-verwijzingen maken gebruik van – soms vele – andere cellen. Maak de samenhang tussen die cellen zichtbaar. Dat kan je enorm helpen bij het opsporen van eventuele fouten.
Selecteer de cel die een formule bevat en klik dan op de knop ‘Broncellen aanwijzen‘. Er verschijnen blauw gekleurde pijl-aanwijzingen die de cellen aanduiden die door de formule worden aangesproken. Klik nogmaals om dieper te graven.
Klik op de knop ‘Pijlen verwijderen’ om de gekleurde pijl-aanduidingen weer van het werkblad te verwijderen.
De andere kant op – van bron naar doel – kan ook. Selecteer een cel, en klik vervolgens op de knop ‘Doelcellen aanwijzen‘. Met gekleurde pijlen wordt aangewezen welke cellen gebruik maken van de geselecteerde waarde. Klik ook hierbij nogmaals om verder te kijken.
Via het dialoogvenster ‘Foutcontrole’ kun je alle op jouw werkblad aangetroffen fouten een voor een onderzoeken en corrigeren. En je krijgt Help-informatie over de optredende fouten.
Gebruik ‘Formule evalueren’ om van een formule het rekenproces stap-voor-stap te volgen tot aan de uiteindelijke uitkomst. Op deze manier kun je proberen te ontdekken in welke tussenstap een ‘fout’ optreedt.
‘Venster controle‘ is met name handig als de formule gebruik maakt van cellen van meerdere werkbladen. Nadat je van die cellen controle hebt toegevoegd, kun je de voortgang van al deze cellen blijven volgen in dit venster.
Geneste functies in de formulebalk
Over spaghetti gesproken… in de formulebalk zie je een combinatie van meerdere functies. Zelfs in dit soort ‘geneste’ constructies kun je nog wegwijs worden als je van de functies de opbouwvensters raadpleegt.
Selecteer de cel waarin de formule staat die je wil onderzoeken.
Klik direct voor de formulebalk op het ‘fx’-icoontje om het opbouwvenster te openen. Van geneste functies zie je de opbouwvensters achtereenvolgens verschijnen wanneer je de functienaam in de formulebalk met de muis aanklikt. In het venster staat uitleg over het gebruik ervan en van de argumenten. Gebruik eventueel de blauw onderstreepte koppeling in dit venster om hulp te vragen over deze functie.
Kies het juiste recept
Excel geeft ruim baan aan professionals en hobbyisten om een formule te voorzien van de meest fantastische constructies. Met veel vernuft leidt een formule tot een oplossing. Maar dat maakt het voor anderen nog niet altijd even gemakkelijk te doorgronden. Er zijn net zoveel manieren om formules te bouwen als er wegen zijn die naar Rome leiden. Soms is het bij het samenstellen van een formule ook simpelweg een kwestie van smaak. En de lekkerste spaghetti eet je nu eenmaal thuis…
Bovenstaande tip is een van de honderden Tips & Tricks afkomstig van onze helpdesk en studyportal  www.officecontent.nl.
Hier kun je terecht voor al je vragen over MS Office, iPad, Skype en SharePoint. Voor meer informatie over OfficeContent en de prijzen van een abonnement kun je een mail sturen naar Info@avk.nl
Volg ons