Fouten opsporen in formules

In de volgende tabel vindt u een overzicht van enkele veelvoorkomende fouten die een gebruiker tijdens het typen van een formule kan maken. En wordt uitgelegd hoe die fouten kunnen worden gecorrigeerd:

Let op het volgende	Meer informatie
Begin elke functie met een gelijkteken (=)	Als u het gelijkteken weglaat, kan wat u typt worden weergegeven als tekst of als een datum. Als u bijvoorbeeld SOM(A1:A10) typt, wordt in Excel de tekenreeks SOM(A1:A10) weergegeven en wordt de berekening niet uitgevoerd. Als u 11/2 typt, wordt in Excel de datum als 2 nov weergegeven (als de celopmaak Algemeen is, in plaats van de uitkomst 11 door 2 te delen.
Zorg ervoor dat de haakjes openen en sluiten overeenkomen	Controleer of elk haakje openen een bijbehorend haakje sluiten heeft. Wanneer u een functie in een formule gebruikt, is het belangrijk dat elk haakje op de juiste positie staat om de functie correct te laten werken. De formule =ALS(B5<0),'Niet geldig',B5*1,05) werkt niet, omdat er twee haakjes sluiten zijn en slechts één haakje openen, terwijl er van elk een moet zijn. De juiste formule is: =ALS(B5<0,'Niet geldig',B5*1,05).
Gebruik een dubbele punt om een bereik aan te geven	Als u naar een cellenbereik verwijst, moet u een dubbele punt (:) plaatsen als scheidingsteken tussen de verwijzing naar eerste en de verwijzing naar de laatste cel in het bereik. Bijvoorbeeld: =SOM(A1:A5), en niet =SOM(A1 A5), waardoor de fout #LEEG! zou worden geretourneerd.
Typ alle vereiste argumenten	Sommige functies hebben verplichte argumenten. Controleer ook of u niet te veel argumenten hebt opgegeven.
Voer het juiste type argumenten in	Voor sommige functies, zoals SOM, zijn numerieke argumenten vereist. Voor andere functies, zoals REPLACE, is een tekstwaarde vereist voor ten minste één van de argumenten. Als u het verkeerde type gegevens als argument gebruikt, kan Excel onverwachte resultaten retourneren of een fout weergeven.
Nest niet meer dan 64 functies	U mag maximaal 64 niveaus met functies binnen een functie invoeren (nesten).
Plaats namen van andere werkbladen tussen enkele aanhalingstekens	Als een formule verwijzingen naar waarden of cellen op andere werkbladen of in andere werkmappen bevat, en de naam van het andere werkblad of de andere werkmap spaties of niet-alfabetische tekens bevat, moet u deze naam tussen enkele aanhalingstekens (') plaatsen, bijvoorbeeld ='Kwartaalcijfers'!D3, of =‘123’!A1.
Gebruik een uitroepteken (!) na de naam van het werkblad wanneer u er in een formule naar verwijst	Als u bijvoorbeeld de waarde van cel D3 in een werkblad met de naam Kwartaalgegevens in dezelfde werkmap wilt weergeven, gebruikt u de volgende formule: ='Kwartaalgegevens'!D3.
Neem het pad naar externe werkmappen op	Zorg dat alle externe referenties de naam van een werkmap en het pad naar de werkmap bevatten. Een verwijzing naar een werkmap bevat de naam van de werkmap en moet tussen haakjes staan (Werkbladnaam.xlsx). De verwijzing moet ook de naam van het werkblad in de werkmap bevatten. Als de werkmap waar u naar wilt verwijzen, niet openstaat in Excel, kunt u toch een verwijzing in een formule opnemen. Geef het volledige pad naar het bestand op, zoals in het volgende voorbeeld: =RIJEN('C:\Mijn documenten\[K2 activiteiten.xlsx]Verkoop'!A1:A8). Door deze formule wordt het aantal rijen gegeven in het bereik van cel A1 tot en met A8 in de andere werkmap (8). Opmerking: Als het volledige pad spaties bevat, zoals in het voorgaande voorbeeld, moet u het pad tussen enkele aanhalingstekens zetten (aan het begin van het pad en na de naam van de werkmap, vóór het uitroepteken).
Typ getallen zonder opmaak	Gebruik geen opmaak voor getallen die u in formules opgeeft. Als u 1000 euro wilt invoeren als waarde, voert u 1000 in de formule in. Als u een komma als deel van een getal invoert, interpreteert Excel de komma als scheidingsteken. Als u getallen met scheidingstekens voor duizendtallen of valutatekens wilt weergeven, maakt u de cellen op nadat u de waarden hebt ingevoerd. Als u bijvoorbeeld 3100 wilt optellen bij de waarde in cel A3 en u de formule =SOM(3.100;A3) invoert, worden Excel de getallen 3 en 100 opgeteld en wordt dit totaal opgeteld bij de waarde van A3, in plaats van 3100 toe te voegen aan A3, wat =SOM(3100;A3) is. Als u bijvoorbeeld de formule =ABS(-2,134) invoert, wordt er in Excel een foutbericht weergegeven omdat de functie ABS slechts één argument accepteert: =ABS(-2134).

U kunt bepaalde regels instellen om te controleren op fouten in formules. Deze regels leveren geen gegarandeerd foutloos werkblad op, maar ze zorgen er wel voor dat de meest gangbare fouten worden achterhaald. U kunt elk van deze regels afzonderlijk in- of uitschakelen.

Fouten kunnen op twee manieren worden gemarkeerd en gecorrigeerd: één fout tegelijk (zoals bij een spellingcontrole) of direct tijdens het invoeren van gegevens in het werkblad.

U kunt een fout oplossen met behulp van de opties die in Excel worden weergegeven, of u kunt de fout negeren door Fout negeren te selecteren. Als u een fout in een bepaalde cel negeert, wordt de fout in die cel niet weergegeven in verdere foutcontroles. U kunt echter alle eerder genegeerde fouten opnieuw instellen, zodat ze opnieuw worden weergegeven.

1. Voor Excel in Windows gaat u naar Opties voor bestand> >formules of
   voor Excel op Mac selecteert u het Excel-menu > Voorkeuren > Foutcontrole.

2. Schakel onder Foutcontrole het selectievakje Foutcontrole op de achtergrond inschakelen uit. Elke gevonden fout wordt gemarkeerd met een driehoek in de linkerbovenhoek van de cel.

   

3. Als u de kleur wilt wijzigen van de driehoek waarmee wordt aangegeven waar een fout is opgetreden, selecteert u de gewenste kleur in het vak Fouten aangeven met deze kleur.

4. Schakel onder Controleregels van Excel de selectievakjes voor de volgende regels in of uit:

   • Cellen met formules die resulteren in een fout: een formule gebruikt niet de verwachte syntaxis, argumenten of gegevenstypen. Foutwaarden zijn onder andere #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! en #VALUE!. Elk van deze foutwaarden heeft verschillende oorzaken en wordt op verschillende manieren opgelost.

     Opmerking: Als u een foutwaarde rechtstreeks in een cel invoert, wordt deze opgeslagen als die foutwaarde, maar wordt deze niet gemarkeerd als een fout. Als een formule in een andere cel echter naar die cel verwijst, is het resultaat van de formule de foutwaarde uit die cel.

   • Tabellen bevatten inconsistent berekende formules: een berekende kolom kan afzonderlijke formules bevatten die afwijken van de hoofdkolomformule, waardoor een uitzondering ontstaat. Uitzonderingen in berekende kolommen ontstaan in de volgende gevallen:

     • Wanneer u andere gegevens dan een formule in een cel in een berekende kolom typt.

     • Typ een formule in een berekende kolomcel en gebruik ctrl +Z of selecteer Ongedaan maken op de werkbalk Snelle toegang.

     • Wanneer u een nieuwe formule typt in een berekende kolom die al een of meer uitzonderingen bevat.

     • Wanneer u gegevens naar de berekende kolom kopieert die niet overeenkomen met de formule voor de berekende kolom. Als de gekopieerde gegevens een formule bevatten, worden met deze formule de gegevens in de berekende kolom overschreven.

     • Wanneer u een cel in een ander werkbladgebied verwijdert of verplaatst terwijl naar deze cel wordt verwezen vanuit een van de rijen in de berekende kolom.

   • In cellen worden jaartallen weergegeven met twee cijfers: De cel bevat een datum met tekstnotatie die bij gebruik in formules verkeerd kan worden geïnterpreteerd. De datum in de formule =JAAR("1/1/31") zou bijvoorbeeld 1931 of 2031 kunnen zijn. Gebruik deze regel om formules te controleren op datums met tekstnotatie.

   • Getallen zijn opgemaakt als tekst of worden voorafgegaan door een apostrof: De cel bevat getallen die zijn opgeslagen als tekst. Dit gebeurt gewoonlijk wanneer gegevens uit andere bronnen worden geïmporteerd. Getallen die zijn opgeslagen als tekst, kunnen onverwachte sorteerresultaten veroorzaken, dus u kunt ze het beste converteren naar getallen. ‘=SOM(A1:A10) wordt als tekst gezien.

   • Formules zijn inconsistent met andere formules in het gebied: De formule komt niet overeen met het patroon van andere formules in hetzelfde gebied. In veel gevallen verschillen formules die grenzen aan andere formules alleen in de gebruikte verwijzingen. In het volgende voorbeeld met vier aangrenzende formules wordt naast de formule =SOM(A10:C10) in cel D4 een fout weergegeven omdat de aangrenzende formules met één rij worden verhoogd en er één met acht rijen wordt verhoogd, terwijl de formule =SOM(A4:C4) wordt verwacht.

     

     Als de verwijzingen in een formule niet overeenkomen met die in de aangrenzende formules, wordt er een fout weergegeven.

   • Formules slaan cellen in een gebied over: In een formule worden mogelijk niet automatisch verwijzingen opgenomen naar gegevens die u invoegt tussen het oorspronkelijke gegevensbereik en de cel met de formule. Met deze regel wordt de verwijzing in een formule vergeleken met het werkelijke celbereik dat grenst aan de cel met de formule. Als de aangrenzende cellen extra waarden bevatten en niet leeg zijn, wordt naast de formule een fout weergegeven.

     Excel voegt bijvoorbeeld een fout in naast de formule =SOM(D2:D4) wanneer deze regel wordt toegepast, omdat de cellen D5, D6 en D7 grenzen aan de cellen waarnaar wordt verwezen in de formule en de cel met de formule (D8), en deze cellen gegevens bevatten waarnaar in de formule moet worden verwezen.

     

   • Ontgrendelde cellen met formules: de formule is niet vergrendeld voor beveiliging. Standaard zijn alle cellen in een werkblad vergrendeld, zodat ze niet kunnen worden gewijzigd wanneer het werkblad is beveiligd. Zo kunt u onbedoelde fouten voorkomen, zoals het per ongeluk verwijderen of wijzigen van formules. Deze fout geeft aan dat de cel is ingesteld om te worden ontgrendeld, maar dat het blad niet is beveiligd. Controleer of u de cel niet wilt laten vergrendeld.

   • Formules die naar lege cellen verwijzen: de formule bevat een verwijzing naar een lege cel. Dit kan onjuiste resultaten opleveren, zoals in het volgende voorbeeld is weergegeven.

     Stel dat u het gemiddelde wilt berekenen van de getallen in de volgende kolommen met cellen. Als de derde cel leeg is, wordt deze niet opgenomen in de berekening en is het resultaat 22,75. Als de derde cel 0 bevat, is het resultaat 18,2.

     

   • De ingevoerde tabelgegevens zijn ongeldig: er is een validatiefout aanwezig in een tabel. Controleer de validatie-instelling voor de cel via het tabblad Gegevens > groep Hulpmiddelen voor gegevens > Gegevensvalidatie.

1. Selecteer het werkblad dat u op fouten wilt controleren.

2. Als het werkblad handmatig wordt berekend, drukt u nu op F9 om een herberekening uit te voeren.

   Als het dialoogvenster Foutcontrole niet wordt weergegeven, selecteert u Formules > Formulecontrole > Foutcontrole.

3. Als u eerder fouten hebt genegeerd, kunt u als volgt opnieuw controleren op deze fouten: ga naar Bestand > Opties > Formules. Voor Excel op Mac selecteert u het Excel-menu > Voorkeuren > Foutcontrole.

   Selecteer in de sectie Foutcontrolede optie Genegeerde fouten opnieuw instellen > OK.

   

   Opmerking: Als u genegeerde fouten opnieuw instelt, worden alle fouten op alle bladen van de actieve werkmap opnieuw ingesteld.

   Tip: Het scheelt als u het dialoogvenster Foutcontrole onder de formulebalk plaatst.

   

4. Selecteer een van de actieknoppen aan de rechterkant van het dialoogvenster. De beschikbare acties zijn voor elk type fout verschillend.

5. Selecteer Volgende.

1. Selecteer foutcontrole naast de cel en selecteer vervolgens de gewenste optie. De beschikbare opdrachten zijn per type fout verschillend en het eerste item in de lijst bevat de beschrijving van de fout.

   Als u Fout negeren selecteert, wordt de fout gemarkeerd als genegeerd voor elke volgende controle.

   

Als een formule een resultaat niet correct kan evalueren, wordt in Excel een foutwaarde weergegeven, zoals #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! en #VALUE!. Elk fouttype heeft verschillende oorzaken en verschillende oplossingen.

De volgende tabel bevat koppelingen naar artikelen waarin deze fouten uitgebreid worden beschreven en een korte beschrijving, zodat u aan de slag kunt.

Onderwerp	Omschrijving
De fout ##### corrigeren	Deze fout wordt weergegeven als een kolom niet breed genoeg is om alle tekens in een cel weer te geven of als een cel negatieve datum- of tijdwaarden bevat. Een formule waarmee een datum in de toekomst wordt afgetrokken van een datum in het verleden, zoals =06/15/2008-07/01/2008, resulteert bijvoorbeeld in een negatieve datumwaarde. Tip: Pas de cel automatisch aan door tussen de kolomkoppen te dubbelklikken. Als ### wordt weergegeven omdat niet alle tekens kunnen worden weergegeven, wordt dit gecorrigeerd.
De fout #DEEL/0! corrigeren	Deze fout wordt weergegeven wanneer een getal wordt gedeeld door nul (0) of door een cel die geen waarde bevat. Tip: Voeg een foutafhandeling toe zoals in het volgend voorbeeld: =ALS(C2;B2/C2;0)
De fout #N/B corrigeren	Deze fout wordt weergegeven wanneer een waarde niet beschikbaar is voor een functie of formule. Als u een functie zoals VERT.ZOEKEN gebruikt, is er dan een overeenkomst in het bereik met de gezochte gegevens? Meestal niet. Probeer ALS.FOUT te gebruiken om de fout #N/B te onderdrukken. U kunt ook gebruikmaken van: =ALS.FOUT(VERT.ZOEKEN(D2;$D$6:$E$8;2;WAAR);0)
De fout #NAAM? corrigeren	Deze fout wordt weergegeven wanneer tekst in een formule niet wordt herkend. Een bereiknaam of de naam van een functie kan bijvoorbeeld onjuist worden gespeld. Opmerking: Als u een functie gebruikt, zorg er dan voor dat u de functienaam correct spelt. In dit geval is SOM niet correct gespeld. Verwijder de 'e' en excel corrigeert deze.
De fout #LEEG! corrigeren	Deze fout wordt weergegeven wanneer u een snijpunt opgeeft van twee gebieden die elkaar niet overlappen. De snijpuntoperator is een spatie die de verwijzingen in een formule van elkaar scheidt. Opmerking: Zorg dat uw bereiken op de juiste wijze zijn gescheiden, de gebieden C2:C3 en E4:E6 kruisen elkaar niet, dus na invoeren van de formule =SOM(C2:C3 E4:E6) wordt de fout #LEEG! geretourneerd. Als u een komma tussen het C- en E-bereik plaatst, wordt deze gecorrigeerd =SOM(C2:C3;E4:E6)
De fout #GETAL! corrigeren	Deze fout wordt weergegeven wanneer een formule of functie ongeldige numerieke waarden bevat. Gebruikt u een iteratiefunctie, zoals IR of RENTE? Zo ja, dan wordt de fout #GETAL! waarschijnlijk veroorzaakt omdat de functie geen resultaat kan vinden. Raadpleeg het Help-onderwerp voor een oplossing.
De fout #VERW! corrigeren	Deze fout wordt weergegeven wanneer een celverwijzing niet geldig is. U hebt bijvoorbeeld cellen verwijderd waarnaar wordt verwezen door andere formules, of u hebt cellen geplakt die u hebt verplaatst op cellen waarnaar wordt verwezen door andere formules. Hebt u per ongeluk een rij of kolom verwijderd? We hebben kolom B in deze formule, =SOM(A2,B2,C2) verwijderd en kijken wat er is gebeurd. Gebruik Ongedaan maken (Ctrl+Z) om de verwijdering ongedaan te maken en maak de formule opnieuw, of gebruik een doorlopende bereikverwijzing, bijvoorbeeld =SOM(A2:C2). Deze wordt automatisch bijgewerkt als kolom B wordt verwijderd.
De fout #WAARDE! corrigeren	Deze fout kan optreden als de formule verwijst naar cellen die verschillende gegevenstypen bevatten. Gebruikt u wiskundige operatoren (+, -, *, /, ^) voor verschillende gegevenstypen? Gebruik in dat geval een functie. Hier zou =SOM(F2:F5) het probleem hebben opgelost.

Wanneer cellen niet zichtbaar zijn in een werkblad, kunt u deze cellen en hun formules watch op de werkbalk Venster controleren. Met Venster Controle kunt u berekeningen en resultaten van formules in grote werkbladen gemakkelijk controleren, evalueren of bevestigen. Als u Venster Controle gebruikt, hoeft u niet telkens naar de verschillende delen van het werkblad te schuiven.

Deze werkbalk kan net als alle andere werkbalken worden verplaatst of vastgezet. U kunt de werkbalk bijvoorbeeld onder aan het venster vastzetten. In de werkbalk worden de volgende celeigenschappen bijgehouden: 1) Werkmap, 2) Blad, 3) Naam (als de cel een overeenkomend benoemd bereik heeft), 4) Celadres, 5) Waarde en 6) Formule.

Cellen toevoegen aan Venster Controle

1. Selecteer de cellen die u wilt bekijken.

   Als u alle cellen in een werkblad met formules wilt selecteren, gaat u naar Start > Bewerken > selecteert u Zoeken & Selecteren (of u kunt Ctrl+G of Control+G op de Mac gebruiken)> Ga naar Speciale > formules.

   

2. Ga naar Formules > Formulecontrole > selecteer Venster controleren.

3. Selecteer Controle toevoegen.

   

4. Controleer of u alle cellen hebt geselecteerd die u wilt watch en selecteer Toevoegen.

   

5. Als u de breedte van een Venster Controle-kolom wilt wijzigen, versleept u de rand rechts van de kolomkop.

6. Als u een cel wilt weergeven waarnaar door een item op de werkbalk Venster Controle wordt verwezen, dubbelklikt u op dit item.

   Opmerking: Cellen met externe verwijzing naar andere werkmappen worden alleen op de werkbalk Venster Controle weergegeven als de andere werkmappen geopend zijn.

Cellen verwijderen uit Venster Controle

1. Als de werkbalk Venster controleren niet wordt weergegeven, gaat u naar Formules > Formulecontrole > selecteert u Venster controleren.

2. Selecteer de cellen die u wilt verwijderen.

   Als u meerdere cellen wilt selecteren, drukt u op Ctrl en selecteert u de cellen.

3. Selecteer Controle verwijderen.

   

Soms is het moeilijk om duidelijk te zien hoe een geneste formule het uiteindelijke resultaat berekent, omdat er meerdere tussenliggende berekeningen en logische tests plaatsvinden. Als u het dialoogvenster Formule evalueren gebruikt, kunt u de verschillende onderdelen van een geneste formule echter bekijken in de volgorde waarin de formule wordt berekend. De formule =ALS(GEMIDDELDE(D2:D5)>50,SOM(E2:E5),0) is bijvoorbeeld gemakkelijker te begrijpen wanneer u de volgende tussenliggende resultaten ziet:

In het dialoogvenster Formule evalueren	Beschrijving
=ALS(GEMIDDELDE(D2:D5)>50;SOM(E2:E5);0)	De geneste formule wordt eerst weergegeven. De functies GEMIDDELDE en SOM zijn genest in de functie ALS. Het celbereik D2:D5 bevat de waarden 55, 35, 45 en 25, en het resultaat van de functie GEMIDDELDE(D2:D5) is dus 40.
=ALS(40>50;SOM(E2:E5);0)	Het celbereik D2:D5 bevat de waarden 55, 35, 45 en 25, en het resultaat van de functie GEMIDDELDE(D2:D5) is dus 40.
=ALS(Onwaar;SOM(E2:E5);0)	40 is niet groter dan 50, dus de expressie in het eerste argument van de functie ALS (het argument logische_test) is onwaar. De functie ALS retourneert de waarde van het derde argument (het argument waarde-als-onwaar). De functie SOM wordt niet geëvalueerd, omdat deze functie het tweede argument voor de functie ALS is (het argument waarde-als-waar) en alleen wordt geretourneerd als de expressie waar is.

1. Selecteer de cel die u wilt evalueren. U kunt slechts één cel tegelijk evalueren.

2. Ga naar Formules > Formules controleren > Formule evalueren.

3. Selecteer Evalueren om de waarde van de onderstreepte verwijzing te onderzoeken. Het resultaat van de evaluatie wordt cursief weergegeven.

   Als het onderstreepte deel van de formule een verwijzing naar een andere formule is, selecteert u Stap in om de andere formule weer te geven in het vak Evaluatie . Selecteer Stap uit om terug te gaan naar de vorige cel en formule.

   De knop Overstappen is niet beschikbaar voor een verwijzing als de verwijzing een tweede keer in de formule voorkomt of als de formule verwijst naar een cel in een afzonderlijke werkmap.

4. Ga door met het selecteren van Evalueren totdat elk onderdeel van de formule is geëvalueerd.

5. Als u de evaluatie opnieuw wilt zien, selecteert u Opnieuw opstarten.

6. Als u de evaluatie wilt beëindigen, selecteert u Sluiten.