Formelfejl i Excel og løsninger

"Fejl er en mulighed for at forbedre". I enhver opgave eller system opstår der fejl. Excel er ingen undtagelse. Når du prøver at gøre noget med en formel, støder du på forskellige typer excel -fejl. Disse fejl kan gøre din formel/dashboard/rapporter til absolut spild. Og hvis du ikke ved, hvorfor der er opstået en bestemt type fejl, skal du muligvis bruge timer på at løse dem.

Mens jeg arbejdede med excel, har jeg stødt på mange excel -fejl. Med lidt kamp og google -søgninger arbejdede jeg uden om disse fejl. I denne artikel vil jeg forklare nogle almindelige og irriterende Excel -fejl, der opstår i Excel. Vi vil diskutere, hvorfor disse fejl opstår, og hvordan man løser dem.

Hvad er Excel -formelfejl
Mens anvendelse af en formel, der resulterer i en excel -definerede fejl (#NA, #VALUE, #NAME osv.) Kaldes excelformelfejl. Disse fejl fanges af excel og udskrives på arkene. Årsagerne til disse fejl kan være utilgængelige værdier, forkerte typer argumenter, division med 0 osv. De er lette at fange og rette.

Logiske fejl fanges ikke af Excel, og de er sværest at rette. Uoverensstemmelse i data, forkert dataindtastning, menneskelige fejl osv. Er en almindelig årsag til disse fejl. De kan også rettes, men de tager tid og kræfter. Det er bedre at forberede dine data perfekt, før du foretager betjening af dem.
Indhentning af Excel -formelfejl:

Der er nogle dedikerede funktioner i excel til at fange og håndtere en bestemt type fejl (som ISNA -funktion). Men ISERROR -funktionen og IFERROR -funktionen er to funktioner, der kan fange enhver form for Excel -fejl (undtagen logisk).

Excel #NA -fejl
#NA -fejl opstår i excel, når der ikke findes en værdi. Det betyder simpelthen IKKE TILGÆNGELIG. #NA -fejl opstår ofte med Excel VLOOKUP -funktion.

I billedet ovenfor får vi #NA -fejl, når vi leder efter "Divya" i kolonnen A. Det er fordi "Divya ikke er på listen.

Løser #NA -fejl

Hvis du er sikker på, at opslagsværdien skal findes på opslagslisten, er det første, du skal gøre, at kontrollere opslagsværdien. Kontroller, om opslagsværdierne er stavet korrekt. Hvis ikke, så ret det.

For det andet kan du lave en delvis match med VLOOKUP eller enhver opslagsfunktion. Hvis du er sikker på, at nogle dele af teksten skal matche, skal du bruge denne.
Hvis du ikke er sikker på, at værdien eksisterer eller ej, kan den bruges til at kontrollere, om der findes en værdi på listen eller ej. På billedet ovenfor kan vi sige, at Divya ikke er på listen.

Hvis du vil fange #NA -fejlen og udskrive eller gøre noget andet i stedet for at udskrive #NA -fejl, kan du bruge Excel ISNA -funktionen. ISNA -funktion returnerer SAND, hvis en funktion returnerer #NA -fejl. Ved at bruge dette kan vi undgå #NA -fejl. ISNA fungerer fantastisk med VLOOKUP -funktionen. Tjek det her.

Excel #VALUE Fejl
#VÆRDI opstår, når det medfølgende argument ikke er af en understøttet type. Hvis du f.eks. Forsøger at tilføje to tekster ved hjælp af arithmetic plus operator (+), får du en #VÆRDI -fejl. Det samme vil ske, hvis du prøver at få et år med et ugyldigt datoformat ved hjælp af YEAR -funktionen.

Sådan rettes en #VALUE -fejl?

Bekræft først den datatype, du henviser til. Hvis din funktion kræver et tal, skal du sørge for at referere til et tal. Hvis et tal er formateret som tekst, skal du bruge funktionen VÆRDI til at konvertere dem til et tal. Hvis en funktion kræver tekst (som DATEVALUE -funktion), og du henviser til et nummer eller en datatype, skal du konvertere dem til tekst.

Hvis du forventer, at der kan være en #VÆRDI -fejl og vil fange dem, kan du bruge ISERR, ISERROR eller IFERROR til at fange og gøre noget andet.

Excel #REF -fejl
'Ref' i #REF står for reference. Denne fejl opstår, når en formel refererer til et sted, der ikke findes. Dette sker, når vi sletter celler fra områder, som en formel også henviser til.

I nedenstående gif refererer sumformlen til A2 og B2. Når jeg sletter A2, bliver formlen til en #REF -fejl.

Løs Excel #REF -fejl:
Det bedste er at være forsigtig, før du sletter celler i data. Sørg for, at ingen formel refererer til den celle.

Hvis du allerede har #REF -fejl, skal du spore, og derefter slette den fra formlen.

Når du f.eks. Får en #REF -fejl, vil din formel se sådan ud.

= A2+#REF!

Du kan bare fjerne #REF! Fra formlen for at få en fejlfri formel. Hvis du vil gøre det i bulk, skal du bruge Find og erstat -funktionen. Tryk på CTRL+H for at åbne find og erstatte. Skriv #REF i boksen Find. Lad udskiftningsboksen stå tom. Tryk på knappen Erstat alle.

Hvis du vil justere referencen til en ny celle, skal du gøre det manuelt og udskifte #REF! Med den gyldige reference.

Excel #NAME fejl
#NAME forekommer i excel, når den ikke kan identificere en tekst i en formel. Hvis du f.eks. Stave en funktions navn forkert, viser excel fejlen #NAME. Hvis en formel refererer til et navn, der ikke findes på arket, viser det #NAME -fejl.

I billedet ovenfor har cellen B2 formel = POWERS (A2,2). POWERS er ikke en gyldig funktion i excel, derfor returnerer den en #NAME -fejl.

I celle B3 har vi = SUM (tal). SUMMEN er en gyldig excel -funktion, men "tal" navngivet område findes ikke på arket. Derfor returnerer excel #NAME? Fejl.

Hvordan undgås #NAME fejl i excel?

For at undgå #NAME fejl i excel skal du altid stave funktionsnavne korrekt. Du kan bruge Excel -forslag til at være sikker på, at du bruger en gyldig funktion. Når vi skriver tegn efter lighedstegnet, viser excel funktioner og navngivne områder på arket, startende fra det/de tegn. Rul ned til funktionsnavn eller områdenavn i forslagslisten, tryk på fanen for at bruge denne funktion.

Excel #DIV/0! Fejl
Som navnet antyder, opstår denne fejl, når en formel resulterer i division med nul. Denne fejl kan også opstå, når du sletter en vis værdi fra en celle, som en divisionsformel er afhængig af.

Sådan løses #DIV/0! Fejl.

Dette kan let løses ved at være forsigtig med data og kontrollere, om en formel vil resultere i #DIV/0! fejl. Her er en prøveformel til at gøre det.

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

Vi får kun DIV/0 -fejl, hvis divisoren er 0 eller tom. Derfor kontrollerer vi divisoren (B2), hvis det er et nul, udskriver A2 ellers dividerer A2 med B2. Dette vil også fungere for tomme celler.

Excel #NUM fejl

Denne fejl opstår, når et nummer ikke kan vises på skærmen. Årsagen kan være, at tallet er for lille eller for stort til at blive vist. En anden grund kan være, at en beregning ikke kan udføres med et givet tal.

= SQRT (ABS (A3))

Dette vil returnere 4. Hvis du vil få den negative værdi, skal du bruge det negative tegn før funktionen. Så kan du selvfølgelig bruge fejlhåndteringsfunktionen selvfølgelig.
Ved løsning af #NUM -fejl har vi en dedikeret artikel. Du kan tjekke det her.

#NULL Fejl i Excel
Dette er en sjælden type fejl. #NULL fejl forårsaget af forkert cellehenvisning. For eksempel hvis du vil give reference til et område A2: A5 i SUM -funktionen, men ved en fejl skriver du A2 A5. Dette vil generere #NULL fejl. Som du kan se i billedet herunder, returnerer formlen #NULL -fejl.
Hvis du erstatter mellemrum med kolonne (:) så er #NULL -fejlen væk, og du får summen af A2: A5. Hvis du erstatter mellemrum med komma (,), får du summen af A2 og A5.

Hvordan løses #NULL fejl?

Da vi ved, at #NULL -fejlen skyldes skrivefejl. For at undgå det, prøv at vælge områder ved hjælp af curser i stedet for at skrive det manuelt.

Der vil være tidspunkter, hvor du skal skrive områdeadresse fra tastaturet. Pas altid på den forbindende symbolkolonne (:) eller komma (,) for at undgå.

Hvis du har en eksisterende #NULL -fejl, skal du kontrollere referencerne. Sandsynligvis har du savnet kolonne (:) eller komma (,) mellem to cellereferencer. Erstat dem med passende symbol, og du er klar til at gå.
###### Fejl i Excel
Nogle gange tror vi, at denne fejl skyldes utilstrækkelig plads til at vise en værdi, men det er ikke tilfældet. I dette tilfælde kan du bare forlænge cellens bredde for at se værdien i cellen. Jeg vil ikke kalde det en fejl.
Faktisk forårsagede denne fejl, når vi forsøger at vise en negativ tidsværdi (der er ikke noget, der hedder negativ tid). For eksempel, hvis vi forsøger at trække 1 fra 12:21:00, returnerer det ######. I excel er den første dato 1/1/1900 00:00. Hvis du forsøger at trække tiden fra, der går før det, vil excel vise dig ###### fejl. Jo mere du udvider bredden, jo mere # får du. Jeg har uddybet det i denne artikel.

Hvordan undgås ####### Excel -fejl?
Inden du laver aritmetiske beregninger i excel med tidsværdi, skal du huske på disse ting.

Den mindste tidsværdi er 1/1/1900 00:00. Du kan ikke have en gyldig dato før dette i excel
1 er lig med 24 timer (1 dag) i excel. Mens du trækker timer, minutter og sekunder fra, konverterer du dem til ækvivalente værdier. For eksempel, for at trække 1 time fra 12:21, skal du trække 1/24 fra den.

Sporing af fejl i Excel
Nu ved vi, hvad almindelige excelformler er, og hvorfor de forekommer. Vi diskuterede også, hvilken mulig løsning der kan være for hver type excel -fejl.
Nogle gange i excel -rapporter får vi fejl, og vi kunne ikke vide, hvor fejlen faktisk opstår. Det bliver svært at løse denne slags fejl. For at spore disse fejl giver excel fejlsporingsfunktionalitet i formelfanen.

Jeg har i detaljer diskuteret fejlsporing i excel.

Løsning af logiske fejl i formlen

Logiske fejl er svære at finde og løse. Ingen massage vist af excel, når du har en logisk fejl i din funktion. Alt ser fint ud. Men kun du ved, at der er noget galt der. For eksempel, mens du får procentdel af en del af helhed, ville du dividere del med total (= (del/total)*100). Det skal altid være lig med eller mindre end 100%. Når du får mere end 100%, ved du, at der er noget galt.

Dette var en simpel logisk fejl. Men nogle gange kommer dine data fra flere kilder, i så fald bliver det svært at løse logiske problemer. En måde er at evaluere din formel.

På formelfanen er evalueringsformel tilgængelig. Vælg din formel, og klik på den. Hvert trin i din beregning vil blive vist for dig, der fører til dit endelige resultat. Her kan du kontrollere, hvor problemet er opstået, eller hvor beregningen er gået galt.

Du kan også spore afhængigheder og præcedenser for at se, hvilke referencer din formel afhænger af, og hvilke formler der afhænger af en pertikulær celle.

Så ja fyre, det var nogle almindelige fejltyper, hver Excel -bruger står over for. Vi lærte, hvorfor hver type fejl opstår, og hvordan kan vi undgå dem. Vi lærte også om dedikeret excel -funktion til fejlhåndtering. I denne artikel har vi links til relaterede sider, der diskuterer problemet i detaljer. Du kan tjekke dem ud. Hvis du har en bestemt type fejl, der irriterer dig, skal du nævne det i kommentarfeltet herunder. Du får løsningen sorteret.