Excel kennis verspreiden - deel 04: Formules auditen
- 13/01/2014
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Dag allen, Vandaag is er alweer deel 4, en we gaan het hebben over het Controleren en auditen van formules in Excel. Hoe vind je nu dat foutje in die ellenlange formule die je een paar jaar geleden maakte? Wat was de redenering van de formule alweer? Ook voor zij die wat vaker met Excel werken en vlotjes formules schrijven, denk ik dat er in dit artikel nog altijd een aantal nuttige dingen staan. Richt je dan op de duidelijk gemarkeerde zaken en sla de andere zaken over. Excel is een rekentool, dus wat dat betreft is het opstellen van correcte (en onderhoudsvriendelijke) formules van groot belang. Zeker in een context waarin belangrijke beslissingen genomen worden op basis van de uitkomsten van de formules.
Als inleiding begin ik met 3 observaties van op forums. (Sla gerust de inleiding over als dit reeds bekend is.) Veel mensen typen nog steeds formules als: =+A10*B2
Dat plusteken is overbodig en dient tot niets. Het is een overblijfsel van lang geleden, Lotus 1-2-3 denk ik, in ieder geval van vóór mijn tijd :-) Een variant daarop is: =SOM(C5)
Het toevoegen van de SOM functie is nogal nutteloos: =SOM(C5) is vaak gewoon hetzelfde als =C5. Waar het een verschil geeft, is dat SOM() toegepast op een tekst als resultaat 0 geeft. Merk op, bijgevolg is er een verschil tussen SOM() en +
De SOM functie doet extra checks/manipulaties vergeleken met +. Die laatste probeert gewoon op te tellen, en lukt dat niet dan krijg je een #WAARDE-fout. Zie cel C2 hierboven, terwijl cel C1 wel de SOM weergeeft. Overigens is dit vaak een lastige afweging: wil je fouten maskeren om de gebruiker niet af te leiden, of wil je ze daarentegen expliciet laten opvallen met een foutmelding.
Tot slot gebeurt het dat de formules zich niet uitrekenen:
Hoogstwaarschijnlijk staan de Excel berekeningen op Manueel in plaats van Automatisch (zie screenshot rechts). Of werd de cel opgemaakt als "Text" in plaats van als "General", dan zie je de formule staan in plaats van de uitkomst.
Wat ook mogelijk is, is dat je circulaire verwijzingen in het werkblad hebt gezet. Bvb. zie screenshot hierboven. A16 bepaalt A16… Dat lukt uiteraard niet. Dit kan wél als je aanduidt in de Excel Opties dat je met zulke kringverwijzingen wil gaan werken. Zo heb ik eens een bestandje gemaakt voor de aankoop van een onroerend goed, met heel veel afhankelijkheden erin: .
Als er na de bovenstaande aandachtspunten nog steeds formules niet juist zijn, dan zijn er (tenminste) 2 mogelijkheden: (Noot: voor de meer ervaren Excellers is dit niet nieuw, lees dan gerust diagonaal en pik de duidelijk gemarkeerde zaken eruit)
- de syntax is niet juist en Excel laat niet toe om de formule in de cel te zetten
- de syntax is wel correct maar de uitkomst is fout
-
Een foute syntax is in principe wat gemakkelijker op te lossen. Een aantal vaak voorkomende fouten in de praktijk:
- Heb je haakjes in de formule staan, tel dan alle ( haakjes en alle ) haakjes. Zijn dat er evenveel van beide soort?
- De functienaam is gewoon fout: zo is het SOM.ALS en niet SOMALS (terwij het wél SUMIF zonder puntje in een Engelse Excel installatie is). Haal uiteraard ook niet Nederlandse en Engelse functienamen door mekaar.
- Let op het scheidingsteken tussen argumenten. Over het algemeen is het in een NL Excel versie een puntkomma als scheidingsteken, in een ENG versie is dat een komma als scheidingsteken. In een NL Excel versie schrijf je dus: =AANTAL.ALS($A$1:$A$100; B3) en in een ENG Excel versie wordt dat: =COUNTIF($A$1:$A$100, B3)
- Je hebt té veel of té weinig argumenten gegeven: de functie ALS heeft maximaal 3 argumenten, geen 4 of 5. Dit is met andere woorden nonsens in Excel: =ALS(B3="Aexis";100;0;-20;1)
- Je hebt de dubbele quotes rondom tekst niet afgesloten, of niet geopend:
- En zo zijn er nog een paar mogelijke oorzaken op te sommen.
Accepteert Excel de formules maar blijkt dat het resultaat niet juist is, dan moet je verder zoeken naar logische fouten. Wat kan je dan doen?
- Werd de juiste formule wel gebruikt? Een impressie: AANTAL, AANTALARG, AANTAL.ALS, AANTALLEN.ALS, DBAANTAL, … Het zijn er wel een aantal :-) Het zijn formules die je wellicht niet elke dag gebruikt, dus dan kan daar ook wel eens een verkeerde tussen zitten.
- Heb je de juiste argumenten opgegeven in de formules? En ook in de juiste volgorde? Vaak klik ik dan op de Fx in de formulebalk om de argumenten te bekijken. Daar kan je ook de Help functie raadplegen binnen Excel om zeker te zijn van de syntax en de gevraagde argumenten.
- Klik je in de formulebalk zelf dan zal Excel de bereiken inkleuren op het scherm (althans die op het actieve werkblad). Is een bereik te groot of klein en heb je andere cellen nodig in jouw formule, sleep dan de rand van het bereik zodat de formule wel de juiste cellen neemt.
- Indien nodig, vergroot de formulebalk (in de meer recente versies van Excel dan toch) zodat je de hele formule op het scherm krijgt.
- Beperk de bereiken van de formules tot een kleiner aantal cellen, om zo gemakkelijker na te gaan (met minder cellen) wat er in de formule gebeurt, of niet gebeurt.
- De nummer 1 tool bij uitstek is echter de Formula Auditor: Die kan je vinden op het Lint, bij het tabblad "Formules", dan de groep "Formula Auditing", dan "Evaluate Formula". Zie de optie aangeduid in groen:
- Daarnaast heb je nog een visuele manier om formules te auditen: Precedents en Dependents in het Engels. Je vindt beide opties terug in de groep "Formula Auditing" op het Lint. Zie hierboven.
- Precedents gaat over de cel(len) die als input dienen voor de berekening van een bepaalde cel. M.a.w. welke cellen gebruik ik in een formule? Voorbeeld LINKS op onderstaan screenshot.
- Dependents gaat over de cel(len) die een bepaalde cel gebruiken in hun berekening. M.a.w. welke cellen maken gebruik van deze cel? Voorbeeld RECHTS op onderstaan screenshot.
- "Show formulas" (zie screenshot hierboven over de groep in het Lint) doet wat zegt: switchen tussen uitkomsten en formules. Kan ook handig zijn, bvb. als je denkt dat een cel een formule bevat maar uiteindelijk toch een harde waarde blijkt te hebben.
- "Error Checking" kan ook wel eens nuttig zijn, maar gebruik ik zelf zeer weinig. Het laat toe om bvb. aan te duiden dat een getal als tekst gestockeerd wordt in de cel. Of een groot bereik heeft eenzelfde formule en enkele cellen in het bereik hebben toevallig een andere formule. Die cellen kunnen gemarkeerd worden.
- Een andere manier is om een lange formule op te breken in verschillende stukken, zodat het duidelijker wordt. Uiteindelijk zijn er sinds Excel 2007 (en later) maar liefst 16384 kolommen per werkblad, dus op een hulpkolommetje meer of minder moet je niet kijken :-) Werk van binnen naar buiten als je functies in mekaar nest.
- Je kan een Watch Window gebruiken: Je kan cellen opnemen in een scherm zodat je ze kan monitoren in real time, ook als die cellen op een ander werkblad, in een ander bestand, … staan. Hieronder heb ik cel P7 met het getal 1000 en op een Sheet2 staat een formule die dat getal maal 5 doet. Dan kan je in het Watch Window opvolgen wat de impact is op die cel. Uiteraard is het in de praktijk wel iets uitgebreider dan deze simpele voorbeeldjes :-)
- Gebruik functietoets F9 ! Die laat toe om een stuk van een formule te selecteren, F9 te drukken en Excel vervangt dan het geselecteerde door het resultaat van de selectie.
Dit kan van 1 simpele cel gaan tot een ingewikkelde berekening in de formule. Doe je deze bewerking wel vaker,
denk dan maar aan (verborgen) hulpkolommen aangezien dat gemakkelijker is naar formule-onderhoud en debugging.
DAN:
Hierboven selecteer ik ROWS(A2:A17), dat zijn 16 rijen in het bereik, dus na F9 drukken vervangt Excel ROWS(A2:A17) door 16. Dit kan je uiteraard ook met veel grotere delen van de formule doen.
- Je kan ook een Find > Replace bewerking doen op formules. Stel dat je een kolom verkeerd gedaan hebt, dan kan je met een juiste zoekactie ineens een heel aantal cellen corrigeren. Daarnaast kan je met een Find > Replace operatie ook bijvoorbeeld een = teken vervangen door '= (weglatingsteken isgelijkaan-teken). Gevolg is dat de formules zich niet zullen berekenen en je ziet de formules staan als tekst.
- Wie reeds over Excel 2013 beschikt, kan gebruik maken van de nieuwe formule FORMULATEXT (in het Engels), FORMULETEKST in het Nederlands. Daarmee vraag je de formule uit een andere cel op. Is met momenten ook wel handig. Dat is de formule die ik gebruik in het voorbeeldbestand in bijlage. Die functie zal bij jou niet werken zonder Excel 2013 (of later).
- Grijp nog eens terug naar de Camera tool uit de vorige les ! Neem een dynamische afbeelding van een cellenbereik op een ander werkblad, plak dat in het huidige werkblad met de Camera tool, en je kan gemakkelijk volgen wat er gebeurt als je een paar cellen of formules wijzigt.
- Grijp ook nog eens terug naar de les over Special cells : daar was een voorbeeld om alle cellen te zoeken in een bereik die een formule hebben die leidt tot een foutmelding.
Hieronder heb ik in cel F12 speciaal een iets lastigere functie gezet dan normaal om deze tool eens in actie te laten zien :-)
Met deze Auditing tool kan je stap voor stap, van binnen naar buiten, de hele berekening doorlopen. Je ziet welke cellen genomen worden, welke logica gevolgd wordt, en zo kan je vaststellen of dit wel is wat je in gedachten had. Deze methode gebruik ik zelf altijd bij wat niet-alledaagse formules. In het scherm zie je welk stuk van de formule ondertreept is: klik je op Evaluate dan krijg je het resultaat te zien van het onderstreepte deel. Excel onderstreept het volgende deeltje, en ga zo maar door.
Zo, dat was het zo’n beetje. Dit zijn de belangrijkste manieren om fouten uit Excel werkboeken te halen ! :-)
