10 veel voorkomende fouten in Excel

1. Werken met auto-herstel: voorkomen is beter dan genezen
Het is u ongetwijfeld ook wel eens overkomen: de stroom valt uit of u verwijdert of wijzigt per ongeluk het bestand waar u mee bezig bent. Als u verzuimd hebt om tussentijds het bestand op te slaan, betekent dit veel hersteltijd.

Oplossing:
De oplossing voor dit soort probleem heet “auto-herstel”. De auto-herstel functie is als volgt te bereiken: Klik op Opties in het menu Extra. (in Excel 2007: Klik op de Microsoft Office-knop en klik vervolgens op Opties voor Excel). Schakel het selectievakje “AutoHerstel-info opslaan elke” in op het tabblad Opslaan. Typ of selecteer in het vak minuten het interval waarmee u bestanden wilt opslaan. Hoe vaker de bestanden worden opgeslagen, des te meer informatie kan worden hersteld bij een stroomstoring of een vergelijkbaar probleem. Wijzig eventueel de (standaard)locatie waar u het herstel bestand opgeslagen wilt hebben op uw schijf.

Opgemerkt dient te worden dat auto-herstel op zich geen alternatief vormt voor het
regelmatig opslaan van uw bestanden. Dit moet u vanzelfsprekend blijven doen, vooral als u
gewend bent om met meerdere (oplopende) versies van een bestand te werken.
Met auto-herstel ondervangt u enkel het verlies van de laatste versie waaraan u bezig was.
Tot slot: indien u in geval van nood gebruik dient te maken van het herstelbestand en dit
bestand opslaat dan wordt, indien u geen nieuwe bestandsnaam opgeeft, het oorspronkelijke
bestand overschreven!

2. Rekenkundige bewerkingen op cellen die tekst bevatten, leiden tot de foutmelding #WAARDE!
Indien u een rekenkundige bewerking uitvoert op meerdere cellen en gebruik maakt van rekenkundige operatoren zoals optellen (+), aftrekken (-), vermenigvuldigen (*) en delen (/) kan de foutmelding #WAARDE! verschijnen als er zich tekstvelden tussen bevinden.

Oplossing:
U kunt deze foutmelding voorkomen door zoveel mogelijk te werken met de gelijkwaardige werkbladfuncties: SOM (optellen); PRODUCT (vermenigvuldigen); QUOTIENT (delen) Als u bijvoorbeeld in de reeks A1:A3 begrotingsbedragen hebt staan en cel A4 hebt voorzien van de aanduiding “PM” leidt de optelreeks A1+A2+A3+A4 in cel A5 tot de foutmelding #WAARDE!. Vervangt u de optelreeks in cel A5 door de SOM functie dan wordt de “PM” aanduiding genegeerd en verschijnt de optelling wel in cel A5.

3. Fouten in gegevenslijsten met repeterende data: controle op gegevensintegriteit en juistheid
Stelt u zich eens een werkblad voor dat wordt gebruikt om betalingen aan leveranciers bij te houden. Elke keer dat u een betaling verricht, wordt de naam van de betreffende leverancier getypt, samen met een aantal andere gegevens. De lijst wordt zo in de loop van de tijd gevuld met vele honderden records. U wilt natuurlijk wel dat de naam van de leverancier steeds eenduidig wordt opgenomen. Dus “Jansen & Jansen” (de enig juiste benaming) moet dan niet zijn opgenomen als “Jansen en Jansen”, “Janssen & Jansen” of “Jansen en Janssen”. Laat u dit wel toe dan verschijnen deze variaties bij selecties als aparte entiteit en dat is vanzelfsprekend niet erg wenselijk voor het juiste inzicht per leverancier (bijvoorbeeld bij gebruikmaking van subtotalen). Zeker bij langere lijsten is het echter bijna onvermijdelijk dat er toch typefouten insluipen.

Oplossing:
Om er op een snelle manier achter te komen of er typefouten binnen een lange lijst zijn gemaakt kunt u het best de Autofilter functie gebruiken. Deze functie is te bereiken via het menu Data   Filter   Autofilter. Als u vervolgens klikt op de pijl Autofilter dan toont de lijst alle unieke namen in de betreffende kolom. Eventueel fout gepelde namen springen meteen in het oog. U kunt naar analogie van het voorbeeld filteren op de foutieve naam “Jansen en Jansen” en de naam vervolgens wijzigen in “Jansen & Jansen”.

Extra TIP:
u kunt met deze controle op foutief gespelde namen overigens ook meteen controleren of er zich (onbedoeld) lege velden in een lange reeks bevinden. Is dit het geval dan kan via het filter gekozen worden voor “lege cellen” en kunt u de lege cellen alsnog van inhoud voorzien.

4. Gegevens ongesplitst in één cel opnemen: het probleem van het splitsen achteraf
De stelregel is natuurlijk: celgegevens vooraf zoveel mogelijk enkelvoudig opnemen. U herkent waarschijnlijk het voorbeeld wel waarbij zowel voor- als achternaam gecombineerd in één cel zijn opgenomen. U loopt dan tegen problemen aan als u wilt sorteren of selecteren op achternaam.

Oplossing:
Wilt u gecombineerde celgegevens naar een enkelvoudige vorm terugbrengen dan kunt u dat bereiken met behulp van complexe samengestelde tekstfuncties. In de meeste gevallen gaat het echter zeker zo goed met de menukeuze “tekst naar kolommen” (te bereiken via de menukeuze Data   “tekst naar kolommen”). Deze Excel voorziening werkt zelfs zo goed (in tegenstelling tot eerdergenoemde complexe samengestelde tekstfuncties) dat hij tegelijkertijd als “veelvraat” alle mogelijke interpunctiemogelijkheden aftast. Als voorbeeld hebben we in de cellen A1:A4 verschillende varianten opgenomen van de wijze waarop voor- en achternaam gecombineerd kunnen zijn.

We laten hier “tekst naar kolommen” op los via de volgende tussenstappen:

Na tot slot op de knop “voltooien” te drukken, verschijnt het gewenste resultaat in gescheiden
kolommen.

5. Werken met een niet dynamisch (optel)bereik: foutieve waarden als resultaat
U herkent uit de praktijk ongetwijfeld het volgende: in de reeks A1:A10 hebt u getallen opgenomen en in cel A11 staat de optelling “=SOM(A1:A10)”. Nu wilt u een getal toevoegen aan deze optelling. Wanneer nu boven A11 een rij wordt ingevoegd, moet de formule worden veranderd in “=SOM(A1:A11)”. Dit is natuurlijk nogal omslachtig en blijft foutgevoelig.

Oplossing:
Een oplossing is het gebruikmaken van een relatieve verwijzing. In het voorbeeld kiest u cel A2 en vervolgens voor de menukeuzen “Invoegen”  “Naam” en vult in “Celhierboven” (maar het mag natuurlijk ook een andere herkenbare naam zijn). In het vak “verwijst naar” vult u vervolgens A1 in. Tenslotte vervangt u de aangegeven optelformule door “= SOM(A1:Celhierboven)”. U kunt nu zonder problemen extra rijen plus getallen invoegen. De formule kijkt nu in alle gevallen één cel naar boven en neemt deze cel mee in de optelling. Overigens doet bovengenoemd probleem zich niet voor als u er voor kiest om met lijsten te werken (mogelijk vanaf Excel 2003 en te bereiken via Data  Lijst   Lijst maken.). Binnen lijsten worden toegevoegde rijen automatisch meegenomen in de optelling.

6. Onvolledige bron leidt tot onjuist of onvolledig overzicht binnen een draaitabel
Een draaitabel is een prachtig instrument om naar allerlei inzichten te sorteren en te selecteren. De sorteringen en selecties moeten dan natuurlijk wel in de pas blijven lopen met het onderliggende bronbestand. Maar al te vaak wordt vergeten om de interface met de bronselectie te verversen (via het rode uitroeptekentje ! in de draaitabelwerbalk). Wat echter minder bekend is, is het feit dat een draaitabel niet automatisch synchroon blijft lopen met een bron waar rijen (records) of kolommen (velden) aan worden toegevoegd. In dat geval volstaat het niet om op uitroeptekentje te klikken. Laten we als voorbeeld naar het onderstaande bronbestand kijken (versie Excel 2003)

Nu voegen we als voorbeeld voor de maand maart een viertal records toe voor de vier regio’s.

Als we nu, als proef, op het uitroepteken drukken van de werkbalk verandert er niets in de
draaitabel.

Oplossing:
We moeten terug naar de bron via de Wizard draaitabel, waarvan we voor het gemak enkel
het laatste dialoogvenster tonen

We voegen via de knop “< Vorige” de nieuwe verwijzing ($A$1:$C$13) toe.
Als we nu op het uitroepteken drukken verschijnt tot slot wel de compleet bijgewerkte
draaitabel:

Tip voor gevorderden:
Nog fraaier is het om met een flexibel naambereik te werken (via Invoegen_ Naam
definiëren en vervolgens werken met de functie verschuiving in het verwijsbereik)

7. Het niet op “ONWAAR” zetten leidt tot fouten bij verticaal of horizontaal zoeken
De werkbladfuncties VERT.ZOEKEN en HORIZ.ZOEKEN bevatten het argument “benaderen” waarmee ook in niet-gesorteerde tabellen een exacte overeenkomst kan worden gezocht. Als u een exacte overeenkomst wilt zoeken, dient u het argument benaderen op “ONWAAR” in te stellen. Doet u dit niet en laat u het argument weg dan krijgt u foutieve resultaten. Stel als voorbeeld dat we uitgaan van het volgende adressenbestand:

Nu willen we via verticaal zoeken een directe link leggen tussen personeelsnummers en bijbehorende adressen. Dit doen we voor een reeks bestaande (1, 4, 5, 6, 8 en 10) en niet bestaande (2, 3, 7 en 9) personeelsnummers. Als we het functieargument “ONWAAR” niet invullen krijgen we via verticaal zoeken het volgende onjuiste beeld:

We zien dat op basis van benadering dat ook de niet bestaande personeelsnummers – ten onrechte – een adres toegewezen krijgen.

Oplossing:
Door het argument benadering op “ONWAAR” in te stellen Bijvoorbeeld J3 = VERT.ZOEKEN(I3;$A$1:$D$7;3;ONWAAR) ontstaat wel een juist resultaat:

8. Niet installeren van Analysis Toolpak leidt tot foutmelding #NAAM# bij groot aantal Excel functies
Ongetwijfeld weet u dat Excel over nogal wat ingebouwde functies beschikt. Zo bestaat bijvoorbeeld de handige functie Netto.Werkdagen (Begindatum, Einddatum, Vakantiedagen) die het aantal werkdagen tussen twee data bepaalt. Als u van deze of andere handige functies gebruik maakt en u krijgt de foutmelding #NAAM dan kunt u ervan uitgaan dat u Analysis Toolpak niet hebt geïnstalleerd. Analysis Toolpak is een Excel invoegtoepassing, die zorgt voor de installatie van extra functies.

Oplossing:
U dient de Analysis Toolpak invoegtoepassing te laden. Dat kan via menu Extra   Invoegtoepassingen en dan het juiste vinkje zetten.

Let op:
Als na installatie de gewraakte foutmelding #NAAM# toch blijft verschijnen werkt u waarschijnlijk met een Engelstalige Excel versie en probeert u de Nederlandstalige functiebenaming te gebruiken (of vice versa !). Vanaf Excel 2007 hebt u het Toolpak probleem niet meer: Analysis toolpak functies zijn inmiddels omgezet naar gewone Excel functies.

9. Afrondingsverschillen: vele kleine verschillen maken het geheel niet kloppend
Daar loopt u als financieel vakman met uw timmermansoog ook vaak tegenaan: totaaltellingen in Excel kloppen net niet helemaal. Een heel simpel voorbeeldje:

U telt in A4 driemaal de breuk 10/3 (cellen A:A3) op en u krijgt als uitkomst 10,00.

Oplossing:
Afrondingsverschillen in optellingen verdwijnen als u gebruik maakt van de juiste
afrondingsfunctie. In het voorbeeld rondt u de cellen B1:B3 als volgt af:

B1=AFRONDEN.BENEDEN(A1;0,01);
B2=AFRONDEN.BENEDEN(A2;0,01);
B3=AFRONDEN.BENEDEN(A3;0,01).

In cel B4 krijgt u dan de juiste optelling van de corresponderende reeks

De waarden van de cellen A1 tot en met A3 worden in het voorbeeld afgerond met een
significantie (= nauwkeurigheid) van 0,01. Zo kun je naar behoefte op gelijke wijze naar
boven afronden m.b.v. de functie AFRONDEN.BOVEN().

10. Ongewenste en onbedoelde koppelingen verwijderen
Voor veel Excel gebruikers vormt onderstaand scherm bij het openen van bestanden een ware plaaggeest.

Door op “Bijwerken” te klikken geef je aan dat je de koppeling wilt bijwerken. Bij “Niet bijwerken” blijven de actuele waarden staan. Het probleem hierbij is dat koppelingen vaak onbedoeld, per ongeluk ontstaan. Dit gebeurt bijvoorbeeld als je een werkblad rechttoe rechtaan uit een bestand kopieert, waarbij in dat werkblad formules staan die hun waarden weer halen uit een ander werkblad. Excel blijft dan domweg verwijzen naar het “originele” werkblad in het originele bestand.

Oplossing:
Als je zit met koppelingen naar een werkblad in ander bestand en je wilt deze omzetten naar het bestand waarin de formules staan, dan kan je als volgt te werk gaan (Excel 2003): Open het koppelingen venster (Bewerken, koppelingen); Selecteer de koppeling(en) die je wilt veranderen en klik op bron wijzigen;

Wil je echter voorgoed van een per ongeluk meegekopieerde koppeling af dan klik je op “Koppeling verbreken”. Je krijgt dan – ter instemming – eerst nog het volgende dialoogvenster:

De formules worden tot slot naar bestaande waarden gekopieerd en u krijgt de volgende keer bij het openen het gewraakte berichtvenster niet langer meer te zien!

Lees ook: 10 veel voorkomende fouten in Excel – Deel 2
https://financieel-management.nl/artikel/10-veel-voorkomende-fouten-in-excel-deel-2
__________________________________________________________________________________

Bekijk ook de inspirerende video's:
Excel Video #1: Winstmaximalisatie met de oplosser
Excel Video #2: What-if analyses voor een annuiteitsberekening
Excel Video #3: Frequentieverdeling maken met de invoegtoepassing histogram