Een werkblad met fouten is pas te repareren als de oorzaak van de fout eenmaal bekend is. Daar ligt dan ook de uitdaging, want het is niet altijd duidelijk waar fouten vandaan komen. Dit zijn 5 veelvoorkomende oorzaken van foutmeldingen in Excel. Hoe voorkomt u deze?

1. Rekenen met verkeerde types

Een #WAARDE-fout (#VALUE!) komt erg vaak voor; iedereen die met Excel heeft gewerkt kent hem wel. Het vervelende van zo’n fout is dat het in principe van alles kan betekenen. Het is Excels manier om te zeggen: ‘Er klopt iets niet met de formule en/of de cellen waarnaar wordt verwezen’. Een gebruikelijke oorzaak is dat een veld waar de formule gebruik van maakt niet het juiste type is, vaak ‘tekst’ als Excel ‘valuta’ verwacht.

Wanneer bijvoorbeeld een datumveld eigenlijk een tekstveld is, kan Excel hier niet mee rekenen. Als een datumnotatie is toegewezen, zet Excel dit op de achtergrond om in een serieel getal dat de software begrijpt. Zo is 1 januari 1900 op 1 gezet en zijn alle volgende data oplopende getallen met +1 per dag. De datum 12 maart 2021 is bijvoorbeeld de 44.265ste dag na het begin van deze telling en het corresponderende getal in Excel voor deze datum is dan ook 44.265. De gebruiker merkt dat niet. Die ziet alleen ‘12 maart 2021’. Als dat tekst is, is dat volgens Excel een string tekens. Wanneer getalnotatie ‘datum’ is toegewezen, evalueert de software ’12 maart 2021’ als 44.265 om ermee te rekenen.

Niet alle formules hebben overigens moeite met tekstvelden. Een SOM-functie bijvoorbeeld negeert tekstwaarden, maar optellen of aftrekken levert de bekende fout #WAARDE! op wanneer er geen getalwaarde is gedefinieerd in de celeigenschappen. De oplossing ligt er dan bij deze melding ook vaak in, als de syntaxis van de formule klopt, om dieper te kijken naar de getalnotaties van cellen.

De oplossing ligt in het gebruik van een IS-functie met voorwaardelijke opmaak om getallen of tekst als zodanig visueel weer te geven. Selecteer het bereik en kies in de ribbon Start > Voorwaardelijke Opmaak > Nieuwe regel… (Start > Conditional Formatting > New Rule…) en kies Een formule gebruiken om te bepalen welke cellen worden opgemaakt (Use a formule to determine which cells to format). Kies daar bijvoorbeeld voor ISGETAL (ISNUMBER), of andersom ISTEKST (ISTEXT), door =ISGETAL(X0:X0) te typen (waarbij X0:X0 het bereik is, bijvoorbeeld A1:C20). Klik vervolgens op Opmaak > Opvulling (Format > Fill) om een markeringskleur te kiezen. Klik daarna op OK. De cellen die als getal zijn gedefinieerd worden daarna opgevuld weergegeven, om het euvel met de formule makkelijker op te kunnen sporen.

Lees ook: Excel-tutorial: fouten verbergen of verwijderen

2. Getal wordt tóch als tekst gebruikt

Daaraan verwant komen gebruikers zonder het te weten geregeld het apostrofprobleem tegen. Dit ontstaat als datasets worden gecombineerd en cellen opnieuw worden gedefinieerd van een tekstveld naar eentje met getalnotatie. Een apostrof voor een getal, betekent dat Excel de cel evalueert als tekst, ook al is er een getalnotatie toegewezen. Dat levert vaak onzichtbare, lastig te debuggen fouten op, bijvoorbeeld met logische vergelijkingen die niet per se correct zijn, omdat een van de geëvalueerde velden niet juist wordt geïnterpreteerd.

Om deze tekstvelden naar getalnotatie te brengen, is een handige tip om de velden te vermenigvuldigen met 1. Tekst 43.000 vermenigvuldigd met het cijfer 1 levert getal 43.000 op. Om cellen zo naar getal te forceren, typt u het getal 1 in een cel, kopieert u vervolgens het celbereik dat een getalnotatie volgens Excel moet bevatten en gaat u linksboven in de ribbon naar Plakken > Plakken speciaal (Paste > Paste Special). Selecteer Vermenigvuldigen (Multiply)en klik op OK. Daarna zijn de cellen naar een standaard opmaak gebracht en kunnen ze verder verfijnd worden met het uitvouwmenu onder Start > [celnotatie] (Start > [Number format], of door de kolom te selecteren, op de rechtermuisknop te drukken en Celeigenschappen (Format Cells) te kiezen.

3. Ontbreken laatste parameter bij VERT.ZOEKEN

De functie VERT.ZOEKEN gebruikt als parameters de zoekwaarde, tabelmatrix en kolomindex, maar waar het nogal eens misgaat is de optionele parameter. Deze heeft twee mogelijk inputs: WAAR (1) of ONWAAR (0). Bij WAAR benadert Excel de opgegeven waarde, oftewel de waarde die het dichtst bij de gevraagd opdracht ligt.

Als een gebruiker hier niets invult, gaat Excel uit van deze benadering en wordt er niet gezocht naar een exacte match. Maar in veruit de meeste scenario’s voor controllers is deze niet op zoek naar een benadering. In die gevallen moet daarom altijd een 0 worden ingevoerd als ‘optionele’ parameter, omdat alleen zo de exacte match wordt gevonden. De parameter voor de benadering is overigens verdwenen in opvolger X.ZOEKEN (X.LOOKUP).

Lees ook: 9 spreadsheetfouten die nachtmerries veroorzaken

4. Werken met niet-bijgewerkte gegevens

Een bron van frustratie bij gebruikers die dit voor het eerst tegenkomen is het effect dat de formules niet automatisch worden bijgewerkt aan de hand van de nieuw ingevoerd informatie. Dat levert problemen op als oude gegevens worden gebruikt wanneer men uitgaat van de nieuwe input. Dit is in de meeste gevallen het gevolg van de berekeningsopties die op handmatig zijn gezet in automatisch. Dit is eenvoudig op te lossen door in de ribbon te kiezen voor Formules > Berekeningsopties (Formulas > Calculation Options) bij Berekening (Calculation) en daar het vinkje terug te zetten bij Automatisch (Automatic). Op deze manier worden ze weer automatisch berekend. Handmatig herberekenen van het blad werkt natuurlijk ook nog steeds. Behalve met de knoppen bij Berekening kan dat ook met Shift F9, of Ctrl Alt F9 voor alle bladen in de werkmap.

5. Harde waardes in formules

In de regel is het geen goed idee om harde waardes te gebruiken in formules. Dat zijn gevallen waarin paramaters worden meegenomen, bijvoorbeeld een vast rentepercentage. Voor het hergebruiken van formules is het beter om deze in een apart parameterblad op te nemen.

Bovendien is het lastiger om een probleem te achterhalen wanneer een formule afwijkt van de standaard en vooraf ingevulde waardes gebruikt. Mocht een waarde veranderen, bijvoorbeeld een btw-waarde die wijzigt van 6 naar 9 procent, dan moet dat in de formule worden aangepast en op elke plek waar deze formule wordt gebruikt. Dat kan een herculestaak worden als het een grote spreadsheet betreft of wanneer er formules zijn met geneste functies die deze harde waarde gebruiken. Fouten sluipen er zo heel snel in.

Als het percentage echter een parameter is die verwijst naar een specifieke locatie, hoeft enkel die locatie te worden bijgewerkt om de formule overal in het werkblad het nieuwe percentage te laten gebruiken. De voordelen van het gebruiken van harde waardes, bijvoorbeeld om in een beperkt overzicht waardes automatisch te vullen, wegen over het algemeen niet op tegen de potentiële nadelen. Vermijd daarom harde waardes in formules.

Volg CMWeb op LinkedIn!