Formelfel i Excel och lösningar

"Fel är en möjlighet att förbättra". I alla uppgifter eller system uppstår fel. Excel är inget undantag. När du försöker göra något med en formel kommer du att stöta på olika typer av excel -fel. Dessa fel kan göra din formel/instrumentpanel/rapporter till absolut slöseri. Och om du inte vet varför en specifik typ av fel har inträffat kan du behöva spendera timmar för att lösa dem.

Under arbetet med excel har jag stött på många excelfel. Med lite kamp och google -sökningar arbetade jag runt dessa fel. I den här artikeln kommer jag att förklara några vanliga och irriterande Excel -fel som uppstår i Excel. Vi kommer att diskutera varför dessa fel uppstår och hur man löser dem.

Vad är Excel -formelfel
Medan en formel som resulterar i ett excel definierade fel (#NA, #VALUE, #NAME etc.) kallas excelformelfel. Dessa fel fångas upp av excel och skrivs ut på arken. Orsakerna till dessa fel kan vara, otillgängliga värden, felaktiga typer av argument, division med 0, etc. De är lätta att fånga och fixa.

Logiska fel fångas inte av Excel och de är svårast att åtgärda. Inkonsekvens i data, fel datainmatning, mänskliga fel etc. är en vanlig orsak till dessa fel. De kan också fixas men de tar tid och ansträngning. Det är bättre att förbereda dina data perfekt innan du utför dem.
Fånga Excel -formelfel:

Det finns några dedikerade funktioner i excel för att fånga och hantera en viss typ av fel (som ISNA -funktion). Men ISERROR -funktionen och IFERROR -funktionen är två funktioner som kan fånga alla typer av Excel -fel (utom logiskt).

Excel #NA -fel
#NA -fel uppstår i excel när ett värde inte hittas. Det betyder helt enkelt INTE TILLGÄNGLIG. #NA -fel uppstår ofta med Excel VLOOKUP -funktionen.

I bilden ovan får vi #NA -fel när vi letar efter "Divya" i A -kolumnen. Detta beror på att "Divya inte finns i listan.

Löser #NA -fel

Om du är säker på att uppslagsvärdet måste finnas i söklistan är det första du bör göra att kontrollera uppslagsvärdet. Kontrollera om uppslagsvärdena stavas korrekt. Om inte, korrigera det.

För det andra kan du göra en delvis matchning med VLOOKUP eller någon uppslagsfunktion. Om du är säker på att vissa delar av texten måste matcha, använd den här.
Om du inte är säker på att värdet finns eller inte kan det användas för att kontrollera om det finns ett värde i listan eller inte. I bilden ovan kan vi säga att Divya inte finns på listan.

Om du vill fånga #NA -felet och skriva ut eller göra något annat istället för att skriva ut #NA -fel kan du använda Excel ISNA -funktionen. ISNA -funktionen returnerar TRUE om en funktion returnerar #NA -fel. Med detta kan vi undvika #NA -fel. ISNA fungerar fantastiskt med VLOOKUP -funktionen. Kolla in det här.

Excel #VALUE -fel
#VÄRDE inträffar när det medföljande argumentet inte är av en typ som stöds. Om du till exempel försöker lägga till två texter med arithmetic plus operator (+) får du ett #VÄRDE -fel. Samma sak kommer att hända om du försöker få ett år med ett ogiltigt datumformat med YEAR -funktionen.

Hur åtgärdar jag ett #VALUE -fel?

Bekräfta först datatypen du syftar på. Om din funktion kräver ett nummer, se till att du hänvisar till ett nummer. Om ett tal formateras som text använder du funktionen VÄRDE för att konvertera dem till ett tal. Om en funktion kräver text (som DATEVALUE -funktionen) och du hänvisar till ett nummer eller datumtyp, konvertera dem sedan till text.

Om du förväntar dig att det kan finnas ett #VALUE -fel och vill fånga dem, kan du använda ISERR, ISERROR eller IFERROR för att fånga och göra något annat.

Excel #REF -fel
Refen i #REF står för referens. Det här felet uppstår när en formel refererar till en plats som inte finns. Detta händer när vi tar bort celler från områden som en formel också hänvisar till.

I nedanstående gif refererar summan formeln till A2 och B2. När jag tar bort A2 blir formeln till ett #REF -fel.

Lös Excel #REF -fel:
Det bästa är att vara försiktig innan du tar bort celler i data. Se till att ingen formel refererar till den cellen.

Om du redan har #REF -fel, spåra sedan bort det från formeln.

Till exempel, när du får ett #REF -fel kommer din formel att se ut så här.

= A2+#REF!

Du kan bara ta bort #REF! Från formeln för att få en felfri formel. Om du vill göra det i bulk använder du funktionen Sök och ersätt. Tryck på CTRL+H för att öppna Sök och ersätt. Skriv #REF i rutan Sök. Lämna ersättningsboxen tom. Tryck på Ersätt alla -knappen.

Om du vill justera referensen till en ny cell gör du det manuellt och ersätter #REF! Med den giltiga referensen.

Excel #NAME -fel
#NAME förekommer i excel när det inte kan identifiera en text i en formel. Till exempel, om du stavar fel en funktions namn, kommer excel att visa #NAME -felet. Om en formel refererar till ett namn som inte finns på arket kommer det att visa #NAME -fel.

I bilden ovan har cellen B2 formel = POWERS (A2,2). POWERS är inte en giltig funktion i excel, därför returnerar den ett #NAME -fel.

I cell B3 har vi = SUMMA (tal). SUMMEN är en giltig funktion för excel men "nummer" med namnet intervall finns inte på arket. Därför returnerar excel #NAME? Fel.

Hur undviker du #NAME -fel i Excel?

För att undvika #NAME -fel i excel stav alltid funktionsnamn korrekt. Du kan använda Excel -förslag för att vara säker på att du använder en giltig funktion. När vi skriver tecken efter likhetstecknet visar Excel funktioner och namngivna intervall på arket, utgående från det/de tecknen. Bläddra ner till funktionsnamn eller intervallnamn i förslagslistan tryck på fliken för att använda den funktionen.

Excel #DIV/0! Fel
Som namnet antyder uppstår detta fel när en formel resulterar i division med noll. Det här felet kan också uppstå när du tar bort något värde från en cell som en divisionsformel är beroende av.

Hur man löser #DIV/0! Fel.

Detta kan enkelt lösas genom att vara försiktig med data och kontrollera om en formel kommer att resultera i #DIV/0! fel. Här är en exempelformel för att göra det.

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

Vi kommer bara att ha DIV/0 -fel om delaren är 0 eller tom. Därför kontrollerar vi divisorn (B2), om det är noll, skriv ut A2 annars delar A2 med B2. Detta fungerar också för tomma celler.

Excel #NUM -fel

Det här felet uppstår när ett nummer inte kan visas på skärmen. Anledningen kan vara att antalet är för litet eller för stort för att visas. En annan anledning kan vara att en beräkning inte kan utföras med ett givet tal.

= SQRT (ABS (A3))

Detta kommer att returnera 4. Om du vill få det negativa värdet använder du det negativa tecknet före funktionen. Sedan kan du naturligtvis använda felhanteringsfunktionen naturligtvis.
Vid lösning av #NUM -fel har vi en dedikerad artikel. Du kan kontrollera det här.

#NULL -fel i Excel
Detta är en sällsynt typ av fel. #NULL -fel orsakat av felaktig cellreferens. Till exempel om du vill referera till ett intervall A2: A5 i SUM -funktionen, men av misstag skriver du A2 A5. Detta genererar #NULL -fel. Som du kan se i bilden nedan returnerar formeln #NULL -fel.
Om du ersätter mellanslag med kolumn (:) kommer #NULL -felet att vara borta och du får summan av A2: A5. Om du ersätter mellanslag med komma (,) får du summan av A2 och A5.

Hur löser man #NULL -fel?

Som vi vet att #NULL -felet orsakas av stavfel. För att undvika det, försök att välja intervall med curser istället för att skriva det manuellt.

Det kommer att ske när du måste skriva intervalladress från tangentbordet. Ta alltid hand om den anslutande symbolkolumnen (:) eller komma (,) för att undvika.

Om du har ett befintligt #NULL -fel kontrollerar du referenserna. Troligen har du missat kolumn (:) eller komma (,) mellan två cellreferenser. Ersätt dem med lämplig symbol och du är klar att gå.
###### Fel i Excel
Ibland tror vi att detta fel orsakas av otillräckligt utrymme för att visa ett värde, men så är inte fallet. I det här fallet kan du bara förlänga cellens bredd för att se värdet i cellen. Jag kommer inte kalla det ett fel.
Faktum är att detta fel orsakades när vi försöker visa ett negativt tidsvärde (det finns inget som heter negativ tid). Om vi till exempel försöker subtrahera 1 från 12:21:00 kommer den att returnera ######. I excel är det första datumet 1/1/1900 00:00. Om du försöker subtrahera tiden som går innan dess, kommer Excel att visa dig ###### fel. Ju mer du utökar bredden desto mer # får du. Jag har utvecklat det i denna artikel.

Hur undviker du ####### Excel -fel?
Tänk på dessa saker innan du gör räkningar i excel med tidsvärde.

Minsta tidsvärde är 1/1/1900 00:00. Du kan inte ha ett giltigt datum före detta i excel
1 är lika med 24 timmar (1 dag) i excel. Medan du subtraherar timmar, minuter och sekunder, konvertera dem till ekvivalenta värden. Till exempel, för att subtrahera 1 timme från 12:21, måste du subtrahera 1/24 från den.

Spåra fel i Excel
Nu vet vi vad vanliga excelformler är och varför de förekommer. Vi diskuterade också vilken möjlig lösning som kan vara för varje typ av excel -fel.
Ibland i excelrapporter får vi fel och vi kunde inte veta varifrån felet faktiskt uppstår. Det blir svårt att lösa den här typen av fel. För att spåra dessa fel ger excel felspårningsfunktion på formelfliken.

Jag har diskuterat felspårning i detalj i detalj.

Lösa logiska fel i formeln

Logiska fel är svåra att hitta och lösa. Ingen massage visas av excel när du har ett logiskt fel i din funktion. Allt ser bra ut. Men bara du vet att det är något fel där. Till exempel, medan du får procentandel av en del av helhet, skulle du dela delen med total (= (del/total)*100). Det bör alltid vara lika med eller mindre än 100%. När du får mer än 100%vet du att något är fel.

Detta var ett enkelt logiskt fel. Men ibland kommer dina data från flera källor, i så fall blir det svårt att lösa logiska problem. Ett sätt är att utvärdera din formel.

På formelfliken finns alternativet utvärdera formel tillgängligt. Välj din formel och klicka på den. Varje steg i din beräkning kommer att visas för dig som leder till ditt slutliga resultat. Här kan du kontrollera var problemet har uppstått eller var beräkningen har gått fel.

Du kan också spåra beroende och prejudikat för att se vilka referenser din formel beror på och vilka formler som beror på en pertikulär cell.

Så ja killar, det här var några vanliga feltyper som varje Excel -användare står inför. Vi lärde oss varför varje typ av fel uppstår och hur kan vi undvika dem. Vi lärde oss också om dedikerad felhantering Excel -funktion. I den här artikeln har vi länkar till relaterade sidor som diskuterar problemet i detalj. Du kan kolla in dem. Om du har någon specifik typ av fel som irriterar dig, nämna det i kommentarfältet nedan. Du får lösningen på ett snyggt sätt.