Napake formule v Excelu in rešitve

"Napake so priložnost za izboljšanje". Pri kateri koli nalogi ali sistemu se pojavijo napake. Excel ni izjema. Ko poskušate nekaj narediti s formulo, boste naleteli na različne vrste napak v Excelu. Zaradi teh napak lahko vaša formula/nadzorna plošča/poročila postanejo popolnoma odpadki. In če ne veste, zakaj je prišlo do določene vrste napake, boste morda morali porabiti ure, da jih odpravite.

Pri delu v Excelu sem naletel na številne napake v Excelu. Z nekaj truda in iskanja v Googlu sem se izognil tem napakam. V tem članku bom razložil nekaj pogostih in nadležnih napak v Excelu, ki se pojavljajo v Excelu. Govorili bomo o tem, zakaj se pojavijo te napake in kako jih odpraviti.

Kaj so napake formule Excel
Med uporabo formule, ki povzroči napake, določene v Excelu (#NA, #VALUE, #NAME itd.), Imenujemo napake formule Excel. Te napake ujame Excel in natisne na liste. Razlogi za te napake so lahko, nedostopne vrednosti, napačne vrste argumentov, deljenje z 0 itd. Enostavno jih je ujeti in popraviti.

Excel logičnih napak ne zazna in jih je najtežje popraviti. Nedoslednost podatkov, napačen vnos podatkov, človeške napake itd. So pogosti razlogi za te napake. Lahko jih tudi popravimo, vendar potrebujejo čas in trud. Bolje je, da svoje podatke natančno pripravite, preden jih operirate.
Lovljenje napak formule Excel:

V Excelu obstaja nekaj namenskih funkcij za lovljenje in obravnavo določene vrste napak (na primer funkcija ISNA). Toda funkcija ISERROR in funkcija IFERROR sta dve funkciji, ki lahko ujameta kakršno koli napako Excel (razen logične).

Napaka v Excelu #NA
Napaka #NA se pojavi v Excelu, ko vrednosti ni mogoče najti. Preprosto pomeni NI NA VOLJO. Napaka #NA se pogosto pojavlja pri funkciji Excel VLOOKUP.

Na zgornji sliki dobimo napako #NA, ko v stolpcu A iščemo "Divya". To je zato, ker "Divya ni na seznamu.

Reševanje napake #NA

Če ste prepričani, da mora iskalna vrednost obstajati na iskalnem seznamu, morate najprej preveriti iskalno vrednost. Preverite, ali so iskalne vrednosti pravilno napisane. Če ne, potem popravite.

Drugič, lahko naredite delno ujemanje z VLOOKUP ali katero koli funkcijo iskanja. Če ste prepričani, da se morajo nekateri deli besedila ujemati, uporabite to.
Če niste prepričani, da vrednost obstaja ali ne, jo lahko uporabite za preverjanje, ali vrednost obstaja na seznamu ali ne. Na zgornji sliki lahko rečemo, da Divya ni na seznamu.

Če želite ujeti napako #NA in natisniti ali narediti kaj drugega namesto tiskanja napake #NA, lahko uporabite funkcijo Excel ISNA. Funkcija ISNA vrne TRUE, če funkcija vrne napako #NA. S tem se lahko izognemo napaki #NA. ISNA deluje neverjetno s funkcijo VLOOKUP. Preverite tukaj.

Napaka v Excelu #VALUE
#VALUE se pojavi, če podani argument ni podprte vrste. Če na primer poskusite dodati dva besedila z uporabo aritmetičnega operaterja plus (+), boste dobili napako #VALUE. Enako se bo zgodilo, če boste s funkcijo YEAR poskušali pridobiti leto neveljavne oblike datuma.

Kako popraviti napako #VALUE?

Najprej potrdite vrsto podatkov, na katero se nanašate. Če vaša funkcija zahteva številko, se prepričajte, da se sklicujete na številko. Če je številka oblikovana kot besedilo, jo s funkcijo VALUE pretvorite v številko. Če funkcija zahteva besedilo (na primer funkcijo DATEVALUE) in se nanašate na številko ali vrsto datuma, jih pretvorite v besedilo.

Če pričakujete, da bi lahko prišlo do napake #VALUE, in jih želite ujeti, potem lahko uporabite ISERR, ISERROR ali IFERROR, da ujamete in naredite nekaj drugega.

Napaka v Excelu #REF
"Ref" v #REF pomeni referenco. Ta napaka se pojavi, ko se formula nanaša na lokacijo, ki ne obstaja. To se zgodi, ko izbrišemo celice iz obsegov, na katere se nanaša tudi formula.

V spodnjem gifu se formula vsote nanaša na A2 in B2. Ko izbrišem A2, se formula spremeni v napako #REF.

Odpravite napako Excel #REF:
Najboljša stvar je, da bodite previdni, preden izbrišete celice v podatkih. Prepričajte se, da se nobena formula ne nanaša na to celico.

Če že imate napako #REF, jo sledite in jo nato izbrišite iz formule.

Ko na primer dobite napako #REF, bo vaša formula videti tako.

= A2+#REF!

Lahko samo odstranite #REF! Iz formule dobite formulo brez napak. Če želite to narediti v velikem obsegu, uporabite funkcijo iskanja in zamenjave. Pritisnite CTRL+H, da odprete iskanje in zamenjavo. V polje za iskanje napišite #REF. Polje za zamenjavo pustite prazno. Pritisnite gumb Zamenjaj vse.

Če želite sklic na novo celico prilagoditi, to naredite ročno in zamenjajte #REF! S to veljavno referenco.

Napaka Excel #NAME
#NAME se pojavi v Excelu, ko ne more prepoznati besedila v formuli. Če na primer napačno črkujete ime funkcije, bo Excel prikazal napako #NAME. Če se formula nanaša na ime, ki na listu ne obstaja, bo prikazana napaka #NAME.

Na zgornji sliki ima celica B2 formulo = MOČI (A2,2). POWERS ni veljavna funkcija v Excelu, zato vrne napako #NAME.

V celici B3 imamo = SUM (številke). SUM je veljavna funkcija programa excel, vendar imenovano območje "števil" ne obstaja na listu. Torej Excel vrača #NAME? Napaka.

Kako se izogniti napaki #NAME v Excelu?

Da bi se izognili napaki #NAME v Excelu, vedno pravilno črkujte imena funkcij. S predlogi programa Excel lahko preverite, ali uporabljate veljavno funkcijo. Kadar koli vnesemo znake za znak enakosti, excel prikaže funkcije in imenovane obsege na listu, začenši s tem znakom/znaki. Pomaknite se navzdol do imena funkcije ali imena obsega na seznamu predlogov, če želite uporabiti to funkcijo, pritisnite zavihek.

Excel #DIV/0! Napaka
Kot že ime pove, se ta napaka pojavi, ko formula povzroči deljenje z ničlo. Ta napaka se lahko pojavi tudi, če izbrišete neko vrednost iz celice, od katere je odvisna formula delitve.

Kako rešiti #DIV/0! Napaka.

To lahko enostavno rešite tako, da bodite previdni pri podatkih in preverite, ali bo formula povzročila #DIV/0! napaka. Tukaj je vzorčna formula za to.

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

Napako DIV/0 bomo imeli le, če je delitelj 0 ali prazen. Zato preverimo delitelj (B2), če je nič, potem natisnemo A2, sicer A2 delimo z B2. To bo delovalo tudi za prazne celice.

Napaka v Excelu #NUM

Ta napaka se pojavi, ko številke ni mogoče prikazati na zaslonu. Razlog je lahko v tem, da je številka premajhna ali prevelika za prikaz. Drugi razlog je lahko, da izračuna ni mogoče izvesti z dano številko.

= SQRT (ABS (A3))

To bo vrnilo 4. Če želite dobiti negativno vrednost, uporabite znak minus pred funkcijo. Seveda lahko seveda uporabite funkcijo obravnave napak.
O odpravljanju napake #NUM imamo namenski članek. To lahko preverite tukaj.

#NULL Napaka v Excelu
To je redka vrsta napake. #NULL napaka, ki je posledica napačnega sklicevanja na celico. Na primer, če želite podati obseg A2: A5 v funkciji SUM, vendar pomotoma vnesete A2 A5. To bo povzročilo napako #NULL. Kot lahko vidite na spodnji sliki, formula vrne napako #NULL.
Če presledek zamenjate s stolpcem (:), napaka #NULL izgine in dobite vsoto A2: A5. Če presledek zamenjate z vejico (,), dobite vsoto A2 in A5.

Kako rešiti napako #NULL?

Kot vemo, je napako #NULL povzročila tipkarska napaka. Da bi se temu izognili, poskusite izbrati obseg s pomočjo kurzorja, namesto da ga ročno vnesete.

Včasih boste morali vnesti naslov območja s tipkovnice. Vedno poskrbite za stolpec povezovalnih simbolov (:) ali vejico (,), da se izognete.

Če imate napako #NULL, preverite reference. Najverjetneje ste med dvema sklicema na celice zgrešili stolpec (:) ali vejico (,). Zamenjajte jih z ustreznim simbolom in ste pripravljeni.
###### Napaka v Excelu
Včasih mislimo, da je do te napake prišlo zaradi premajhnega prostora za prikaz vrednosti, vendar temu ni tako. V tem primeru lahko samo razširite širino celice, da si ogledate vrednost v celici. Temu ne bom rekel napaka.
Pravzaprav je ta napaka nastala, ko poskušamo prikazati negativno časovno vrednost (negativnega časa ni). Na primer, če poskušamo odšteti 1 od 12:21:00, se vrne ######. V Excelu je prvi zmenek 1/1/1900 00:00. Če poskusite odšteti čas, ki preteče pred tem, vam bo Excel pokazal napako ######. Bolj ko razširite širino, več # boste dobili. To sem podrobno razložil v tem članku.

Kako se izogniti napaki ###### Excel?
Preden izvedete aritmetične izračune v Excelu s časovno vrednostjo, upoštevajte te stvari.

Najmanjša vrednost časa je 1/1/1900 00:00. Pred tem v Excelu ne morete imeti veljavnega datuma
1 je enak 24 uram (1 dan) v Excelu. Med odštevanjem ur, minut in sekund jih pretvorite v enakovredne vrednosti. Če želite na primer od 12.21 odšteti 1 uro, morate od nje odšteti 1/24.

Sledenje napakam v Excelu
Zdaj vemo, kaj so skupne formule Excel in zakaj se pojavljajo. Pogovarjali smo se tudi o možni rešitvi za vsako vrsto napak v Excelu.
Včasih v Excelovih poročilih pride do napak in nismo mogli vedeti, od kod prihaja do napake. Te napake je težko rešiti. Za odkrivanje teh napak excel ponuja funkcijo sledenja napak na zavihku formule.

Podrobno sem obravnaval sledenje napak v Excelu.

Reševanje logičnih napak v formuli

Logične napake je težko najti in odpraviti. Excel ne prikazuje nobene masaže, če imate logično napako v svoji funkciji. Vse izgleda v redu. Toda samo vi veste, da je tam nekaj narobe. Na primer, ko dobite odstotek dela celote, bi del delili s skupno (= (del/skupaj)*100). Vedno mora biti enak ali manjši od 100%. Ko dobite več kot 100%, veste, da je nekaj narobe.

To je bila preprosta logična napaka. Včasih pa vaši podatki prihajajo iz več virov, v tem primeru je težko rešiti logične težave. Eden od načinov je oceniti svojo formulo.

Na zavihku formula je na voljo možnost oceni formule. Izberite formulo in kliknite nanjo. Vsak korak vašega izračuna vam bo pokazal, da boste prišli do končnega rezultata. Tu lahko preverite, kje je prišlo do težave ali kje je bil izračun napačen.

Lahko tudi sledite odvisnikom in predhodnikom, da vidite, od katerih sklicev je odvisna vaša formula in katere formule so odvisne od pertikularne celice.

Torej, fantje, to so bile nekatere pogoste vrste napak, s katerimi se srečuje vsak uporabnik excela. Izvedeli smo, zakaj pride do vsake vrste napak in kako se jim lahko izognemo. Spoznali smo tudi namensko obravnavo napak v Excelu. V tem članku imamo povezave do sorodnih strani, ki podrobno obravnavajo težavo. Lahko jih preverite. Če imate kakršno koli posebno napako, ki vas moti, jo omenite v spodnjem razdelku za komentarje. Rešitev boste nekako dobili.