Formulefouten in Excel en oplossingen

"Fouten zijn een kans om te verbeteren". In elke taak of systeem komen fouten voor. Excel is geen uitzondering. Terwijl u probeert iets met een formule te doen, zult u verschillende soorten Excel-fouten tegenkomen. Deze fouten kunnen uw formule/dashboard/rapporten tot absolute verspilling maken. En als u niet weet waarom een bepaald type fout is opgetreden, moet u mogelijk uren besteden om ze op te lossen.

Tijdens het werken aan Excel ben ik veel Excel-fouten tegengekomen. Met wat moeite en Google-zoekopdrachten heb ik die fouten omzeild. In dit artikel zal ik enkele veelvoorkomende en vervelende Excel-fouten uitleggen die in Excel voorkomen. We zullen bespreken waarom deze fouten optreden en hoe ze kunnen worden opgelost.

Wat zijn Excel-formulefouten?
Bij het toepassen van een formule die resulteert in een Excel-gedefinieerde fouten (#NA, #VALUE, #NAME etc.) worden Excel-formulefouten genoemd. Deze fouten worden opgevangen door Excel en afgedrukt op de bladen. Redenen voor deze fouten kunnen zijn, niet-beschikbare waarden, onjuiste soorten argumenten, deling door 0, enz. Ze zijn gemakkelijk op te sporen en op te lossen.

Logische fouten worden niet opgevangen door Excel en zijn het moeilijkst op te lossen. Inconsistentie in gegevens, verkeerde gegevensinvoer, menselijke fouten enz. zijn een veelvoorkomende reden voor deze fouten. Ze kunnen ook worden gerepareerd, maar ze kosten tijd en moeite. Het is beter om uw gegevens perfect voor te bereiden voordat u er bewerkingen op uitvoert.
Fouten in Excel-formules opvangen:

Er zijn enkele speciale functies in Excel om een specifiek type fouten op te vangen en af te handelen (zoals de ISNA-functie). Maar de ISERROR-functie en de IFERROR-functie zijn twee functies die elke soort Excel-fout kunnen opvangen (behalve logisch).

Excel #NA Fout
#NA-fout treedt op in Excel wanneer een waarde niet wordt gevonden. Het betekent simpelweg NIET BESCHIKBAAR. #NA-fout wordt vaak aangetroffen met de Excel VERT.ZOEKEN-functie.

In bovenstaande afbeelding krijgen we de #NA-fout wanneer we zoeken naar "Divya" in de A-kolom. Dit komt omdat "Divya niet in de lijst staat.

#NA-fout oplossen

Als u zeker weet dat de opzoekwaarde in de opzoeklijst moet voorkomen, moet u eerst de opzoekwaarde controleren. Controleer of de opzoekwaarden correct zijn gespeld. Zo niet, corrigeer het dan.

Ten tweede kunt u een gedeeltelijke overeenkomst maken met VERT.ZOEKEN of een willekeurige opzoekfunctie. Als u zeker weet dat sommige delen van de tekst moeten overeenkomen, gebruik deze dan.
Als u niet zeker weet of die waarde wel of niet bestaat, kan deze worden gebruikt om te controleren of een waarde al dan niet in de lijst voorkomt. In de afbeelding hierboven kunnen we stellen dat Divya niet op de lijst staat.

Als u de #NA-fout wilt opvangen en afdrukken of iets anders wilt doen in plaats van de #NA-fout af te drukken, kunt u de Excel ISNA-functie gebruiken. ISNA-functie retourneert TRUE als een functie #NA-fout retourneert. Hiermee kunnen we de #NA-fout voorkomen. ISNA werkt verbazingwekkend met de VERT.ZOEKEN-functie. Bekijk het hier.

Excel #VALUE Fout
De #WAARDE treedt op wanneer het opgegeven argument niet van een ondersteund type is. Als u bijvoorbeeld twee teksten probeert toe te voegen met de rekenkundige plus-operator (+), krijgt u de fout #VALUE. Hetzelfde zal gebeuren als u een jaar met een ongeldige datumnotatie probeert te krijgen met behulp van de JAAR-functie.

Hoe repareer je een #VALUE-fout?

Bevestig eerst het gegevenstype waarnaar u verwijst. Als uw functie een nummer vereist, zorg er dan voor dat u naar een nummer verwijst. Als een getal is opgemaakt als tekst, gebruik dan de functie WAARDE om ze om te zetten in een getal. Als een functie tekst vereist (zoals de functie DATUMWAARDE) en u verwijst naar een getal of datumtype, converteer ze dan naar tekst.

Als u verwacht dat er een #VALUE-fout kan zijn en deze wilt opvangen, dan kunt u ISERR, ISERROR of IFERROR gebruiken om iets anders op te vangen en te doen.

Excel #REF Fout
De 'ref' in #REF staat voor referentie. Deze fout treedt op wanneer een formule verwijst naar een locatie die niet bestaat. Dit gebeurt wanneer we cellen verwijderen uit bereiken waarnaar ook een formule verwijst.

In het onderstaande gif verwijst de somformule naar A2 en B2. Wanneer ik A2 verwijder, verandert de formule in een #REF-fout.

Los Excel #REF-fout op:
Het beste is om voorzichtig te zijn voordat u cellen in gegevens verwijdert. Zorg ervoor dat geen enkele formule naar die cel verwijst.

Als u al een #REF-fout heeft, traceert u deze en verwijdert u deze uit de formule.

Als u bijvoorbeeld een #REF-fout krijgt, ziet uw formule er als volgt uit.

=A2+#VERWIJZING!

U kunt de #REF! Van de formule om een foutloze formule te krijgen. Als u het in bulk wilt doen, gebruikt u de functie Zoeken en vervangen. Druk op CTRL+H om zoeken en vervangen te openen. Schrijf in het zoekvak #REF. Laat het vervangvak leeg. Druk op de knop Alles vervangen.

Als u de verwijzing naar een nieuwe cel wilt aanpassen, doe dit dan handmatig en vervang de #REF! Met die geldige referentie.

Excel #NAME Fout
De #NAME komt voor in Excel wanneer het een tekst in een formule niet kan identificeren. Als u bijvoorbeeld de naam van een functie verkeerd spelt, geeft Excel de fout #NAME weer. Als een formule verwijst naar een naam die niet op het blad voorkomt, wordt de fout #NAME weergegeven.

In de bovenstaande afbeelding heeft cel B2 de formule =POWERS(A2,2). POWERS is geen geldige functie in Excel, daarom retourneert het een #NAME-fout.

In cel B3 hebben we =SOM(getallen). De SOM is een geldige functie van Excel, maar het bereik met de naam "nummers" bestaat niet op het blad. Daarom retourneert Excel #NAME? Fout.

Hoe de #NAME-fout in Excel te voorkomen?

Om de #NAME-fout in Excel te voorkomen, moet u functienamen altijd correct spellen. U kunt Excel-suggesties gebruiken om er zeker van te zijn dat u een geldige functie gebruikt. Telkens wanneer we tekens typen na het gelijkteken, toont Excel functies en benoemde bereiken op het blad, beginnend bij dat teken/de tekens. Scroll naar beneden naar functienaam of bereiknaam in de suggestielijst druk op tab om die functie te gebruiken.

Excel #DIV/0! Fout
Zoals de naam al doet vermoeden, treedt deze fout op wanneer een formule resulteert in deling door nul. Deze fout kan ook optreden wanneer u een waarde verwijdert uit een cel waarvan een delingsformule afhankelijk is.

Hoe #DIV/0 op te lossen! Fout.

Dit kan eenvoudig worden opgelost door voorzichtig te zijn met gegevens en een vinkje te zetten of een formule #DIV/0 zal opleveren! fout. Hier is een voorbeeldformule om dit te doen.

=ALS(B2=0, A2, A2/B2)

We hebben alleen een DIV/0-fout als de deler 0 of leeg is. Daarom controleren we de deler (B2), als het een nul is, print dan A2 anders deel A2 door B2. Dit werkt ook voor lege cellen.

Excel #NUM Fout

Deze fout treedt op wanneer een nummer niet op het scherm kan worden weergegeven. De reden kan zijn dat het nummer te klein of te groot is om weergegeven te worden. Een andere reden kan zijn dat een berekening niet kan worden uitgevoerd met een gegeven nummer.

=SQRT(ABS(A3))

Dit levert 4 op. Als je de negatieve waarde wilt krijgen, gebruik dan het minteken voor de functie. Dan kunt u natuurlijk de foutafhandelingsfunctie gebruiken.
Over het oplossen van #NUM-fout hebben we een speciaal artikel. U kunt het hier controleren.

#NULL Fout in Excel
Dit is een zeldzaam type fout. #NULL-fout veroorzaakt door onjuiste celverwijzing. Als u bijvoorbeeld een referentie van een bereik A2:A5 in de SOM-functie wilt geven, maar u typt per ongeluk A2 A5. Dit zal de fout #NULL genereren. Zoals u in onderstaande afbeelding kunt zien, retourneert de formule de fout #NULL.
Als u de spatie vervangt door kolom (:), dan is de #NULL-fout verdwenen en krijgt u de som van de A2:A5. Als u de spatie vervangt door een komma (,) , krijgt u de som van A2 en A5.

Hoe de #NULL-fout op te lossen?

Zoals we weten, wordt de fout #NULL veroorzaakt door een typefout. Om dit te vermijden, probeert u bereiken te selecteren met behulp van de cursor in plaats van deze handmatig te typen.

Er zullen momenten zijn dat u het bereikadres vanaf het toetsenbord moet typen. Let altijd op de verbindingssymboolkolom (:) of komma (,) om te vermijden.

Als u een bestaande #NULL-fout heeft, controleer dan de verwijzingen. Hoogstwaarschijnlijk heb je kolom (:) of komma (,) tussen twee celverwijzingen gemist. Vervang ze door het juiste symbool en je bent klaar om te gaan.
###### Fout in Excel
Soms denken we dat deze fout wordt veroorzaakt door onvoldoende ruimte om een waarde weer te geven, maar dit is niet het geval. In dit geval kunt u gewoon de breedte van de cel vergroten om de waarde in de cel te bekijken. Ik zal het geen fout noemen.
In feite werd deze fout veroorzaakt wanneer we een negatieve tijdwaarde proberen weer te geven (er bestaat niet zoiets als een negatieve tijd). Als we bijvoorbeeld 1 proberen af te trekken van 12:21:00 uur, wordt ###### geretourneerd. In Excel is de eerste datum 1/1/1900 00:00. Als u de tijd probeert af te trekken die daaraan voorafgaat, zal Excel u een ###### fout laten zien. Hoe meer je de breedte uitbreidt, hoe meer # je krijgt. Ik heb het in dit artikel uitgewerkt.

Hoe ###### Excel-fout te voorkomen?
Houd rekening met deze dingen voordat u rekenkundige berekeningen in Excel uitvoert met tijdswaarde.

De minimale tijdwaarde is 1/1/1900 00:00. U kunt hiervoor geen geldige datum hebben in Excel
1 is gelijk aan 24 uur (1 dag) in Excel. Terwijl u uren, minuten en seconden aftrekt, zet u ze om in equivalente waarden. Als u bijvoorbeeld 1 uur van 12:21 uur wilt aftrekken, moet u er 1/24 van aftrekken.

Fouten opsporen in Excel
Nu weten we wat veelvoorkomende Excel-formules zijn en waarom ze voorkomen. We hebben ook besproken wat een mogelijke oplossing kan zijn voor elk type Excel-fouten.
Soms krijgen we in Excel-rapporten fouten en kunnen we niet weten waar de fout daadwerkelijk optreedt. Het wordt moeilijk om dit soort fouten op te lossen. Om deze fouten op te sporen, biedt Excel functionaliteit voor het opsporen van fouten op het formuletabblad.

Ik heb het opsporen van fouten in Excel in detail besproken.

Logische fouten in formule oplossen

Logische fouten zijn moeilijk te vinden en op te lossen. Geen massage getoond door excel wanneer je een logische fout in je functie hebt. Alles ziet er goed uit. Maar alleen jij weet dat daar iets mis is. Als u bijvoorbeeld een percentage van een deel van een geheel krijgt, deelt u een deel door totaal (=(deel/totaal)*100). Het moet altijd gelijk zijn aan of kleiner zijn dan 100%. Als je meer dan 100% krijgt, weet je dat er iets mis is.

Dit was een simpele logische fout. Maar soms komen je gegevens uit meerdere bronnen, in dat geval wordt het moeilijk om logische problemen op te lossen. Een manier is om uw formule te evalueren.

Op het formuletabblad is de optie formule evalueren beschikbaar. Selecteer je formule en klik erop. Elke stap van uw berekening wordt aan u getoond die leidt tot uw eindresultaat. Hier kunt u controleren waar het probleem is opgetreden of waar de berekening is misgegaan.

U kunt ook afhankelijkheden en precedenten traceren om te zien van welke verwijzingen uw formule afhankelijk is en welke formules afhankelijk zijn van een perticulaire cel.

Dus ja jongens, dit waren enkele veelvoorkomende fouttypen waarmee elke Excel-gebruiker wordt geconfronteerd. We hebben geleerd waarom elk type fout optreedt en hoe we ze kunnen vermijden. We hebben ook geleerd over de speciale Excel-functie voor foutafhandeling. In dit artikel hebben we links naar gerelateerde pagina's die het probleem in detail bespreken. Je kunt ze bekijken. Als u een specifiek type fout heeft dat u irriteert, vermeld dit dan in de opmerkingen hieronder. U krijgt de oplossing netjes.