Formulės klaidos „Excel“ ir sprendimuose

„Klaidos yra galimybė tobulėti“. Bet kurioje užduotyje ar sistemoje pasitaiko klaidų. „Excel“ nėra išimtis. Bandydami ką nors padaryti naudodami formulę, susidursite su įvairiomis „Excel“ klaidomis. Dėl šių klaidų jūsų formulė/prietaisų skydelis/ataskaitos gali būti švaistomi. Ir jei nežinote, kodėl įvyko tam tikros rūšies klaida, gali tekti praleisti valandas, kad jas išspręstumėte.

Dirbdamas su „Excel“ susidūriau su daugybe „Excel“ klaidų. Su tam tikra kova ir „Google“ paieškomis aš ištaisiau šias klaidas. Šiame straipsnyje paaiškinsiu keletą įprastų ir erzinančių „Excel“ klaidų, kurios atsiranda „Excel“. Mes aptarsime, kodėl šios klaidos atsiranda ir kaip jas išspręsti.

Kas yra „Excel“ formulės klaidos
Taikant formulę, dėl kurios atsiranda „Excel“ apibrėžtų klaidų (#NA, #VALUE, #NAME ir kt.), Vadinama „Excel“ formulės klaidomis. Šios klaidos užfiksuojamos „Excel“ ir spausdinamos ant lapų. Šių klaidų priežastys gali būti nepasiekiamos vertės, neteisingi argumentų tipai, padalijimas iš 0 ir tt Jas lengva pastebėti ir ištaisyti.

„Excel“ nesulaiko loginių klaidų ir jas sunkiausia ištaisyti. Dažnos šių klaidų priežastys yra duomenų nenuoseklumas, neteisingas duomenų įvedimas, žmogiškos klaidos ir kt. Jie taip pat gali būti ištaisyti, tačiau jiems reikia laiko ir pastangų. Prieš atlikdami operaciją, geriau puikiai paruoškite savo duomenis.
„Excel“ formulės klaidų gaudymas:

„Excel“ yra tam skirtos funkcijos, skirtos sugauti ir tvarkyti tam tikro tipo klaidas (pvz., ISNA funkcija). Tačiau funkcija ISERROR ir IFERROR yra dvi funkcijos, galinčios užfiksuoti bet kokią „Excel“ klaidą (išskyrus loginę).

„Excel #NA“ klaida
#NR klaida atsiranda „Excel“, kai vertė nerasta. Tai tiesiog reiškia, kad nėra. #NA klaida dažnai susiduriama naudojant „Excel VLOOKUP“ funkciją.

Aukščiau esančiame paveikslėlyje gauname #NA klaidą, kai A stulpelyje ieškome „Divya“. Taip yra todėl, kad „Divya nėra sąraše.

#NA klaidos sprendimas

Jei esate tikri, kad paieškos vertė turi būti paieškos sąraše, pirmiausia turėtumėte patikrinti paieškos vertę. Patikrinkite, ar paieškos vertės yra parašytos teisingai. Jei ne, tada pataisykite.

Antra, galite atlikti dalinį atitikimą naudodami VLOOKUP arba bet kurią paieškos funkciją. Jei esate tikri, kad kai kurios teksto dalys turi sutapti, naudokite tai.
Jei nesate tikri, ar vertė egzistuoja, ar ne, ją galima naudoti norint patikrinti, ar sąraše yra vertė, ar ne. Aukščiau esančiame paveikslėlyje galime pasakyti, kad Divya nėra sąraše.

Jei norite užfiksuoti #NA klaidą ir atsispausdinti ar padaryti ką nors kita, o ne spausdinti #NA klaidą, galite naudoti „Excel ISNA“ funkciją. Funkcija ISNA grąžina TRUE, jei funkcija grąžina #NA klaidą. Naudodami tai galime išvengti #NA klaidos. ISNA nuostabiai veikia su funkcija VLOOKUP. Patikrinkite čia.

„Excel“ #VALUE klaida
#VALUE atsiranda, kai pateiktas argumentas nėra palaikomo tipo. Pvz., Jei bandysite pridėti du tekstus naudodami aritmetinį pliuso operatorių (+), gausite #VALUE klaidą. Tas pats atsitiks, jei bandysite gauti metus netinkamo datos formato naudodami YEAR funkciją.

Kaip ištaisyti #VALUE klaidą?

Pirmiausia patvirtinkite nurodytą duomenų tipą. Jei jūsų funkcijai reikalingas skaičius, įsitikinkite, kad nurodote skaičių. Jei skaičius suformatuotas kaip tekstas, naudokite funkciją VALUE, kad konvertuotumėte juos į skaičių. Jei funkcijai reikalingas tekstas (pvz., DATEVALUE funkcija), o jūs nurodote skaičių ar datos tipą, konvertuokite juos į tekstą.

Jei tikitės, kad gali būti #VALUE klaida, ir norite juos sugauti, galite naudoti ISERR, ISERROR arba IFERROR, kad sugautumėte ir padarytumėte ką nors kita.

„Excel #REF“ klaida
Žodis #REF reiškia nuorodą. Ši klaida atsiranda, kai formulė nurodo vietą, kurios nėra. Taip atsitinka, kai mes ištriname langelius iš diapazonų, į kuriuos taip pat nurodoma formulė.

Žemiau esančiame gif sumos formulė reiškia A2 ir B2. Kai ištrinu A2, formulė virsta #REF klaida.

Išspręskite „Excel“ #REF klaidą:
Geriausia būti atsargiems prieš ištrinant duomenų langelius. Įsitikinkite, kad jokia formulė nenurodo tos ląstelės.

Jei jau turite #REF klaidą, atsekite, tada ištrinkite ją iš formulės.

Pavyzdžiui, kai gausite #REF klaidą, jūsų formulė atrodys taip.

= A2+#Nuoroda!

Galite tiesiog pašalinti #REF! Iš formulės gauti formulę be klaidų. Jei norite tai padaryti masiškai, naudokite paieškos ir pakeitimo funkciją. Paspauskite CTRL+H, kad atidarytumėte radimą ir pakeitimą. Ieškos laukelyje parašykite #REF. Pakeitimo dėžutę palikite tuščią. Paspauskite mygtuką Pakeisti viską.

Jei norite iš naujo sureguliuoti nuorodą į naują langelį, atlikite tai rankiniu būdu ir pakeiskite #REF! Su ta galiojančia nuoroda.

„Excel #NAME“ klaida
„#NAME“ atsiranda „Excel“, kai negali identifikuoti formulės teksto. Pvz., Jei neteisingai parašėte funkcijos pavadinimą, „Excel“ parodys klaidą #NAME. Jei formulė nurodo pavadinimą, kurio nėra lape, ji parodys #NAME klaidą.

Aukščiau esančiame paveikslėlyje ląstelė B2 turi formulę = GALIOS (A2,2). POWERS nėra tinkama „Excel“ funkcija, todėl ji grąžina #NAME klaidą.

B3 langelyje turime = SUM (skaičiai). SUM yra tinkama „Excel“ funkcija, tačiau lape nėra „skaičių“ pavadinto diapazono. Taigi „Excel“ grąžina #NAME? Klaida.

Kaip išvengti #NAME klaidos programoje „Excel“?

Kad išvengtumėte #NAME klaidos programoje „Excel“, visada rašykite funkcijų pavadinimus teisingai. Galite naudoti „Excel“ pasiūlymus, kad įsitikintumėte, jog naudojate tinkamą funkciją. Kai rašome simbolius po lygybės ženklo, „Excel“ rodo funkcijas ir pavadintus diapazonus lape, pradedant nuo to simbolio. Slinkite žemyn iki funkcijos pavadinimo arba diapazono pavadinimo pasiūlymų sąraše, norėdami naudoti šią funkciją, paspauskite skirtuką.

„Excel“ #DIV/0! Klaida
Kaip rodo pavadinimas, ši klaida atsiranda, kai formulė padalijama iš nulio. Ši klaida taip pat gali atsirasti, kai ištrinate tam tikrą reikšmę iš langelio, nuo kurio priklauso padalijimo formulė.

Kaip išspręsti #DIV/0! Klaida.

Tai galima lengvai išspręsti būnant atsargiems su duomenimis ir patikrinus, ar formulės rezultatas bus #DIV/0! klaida. Čia yra pavyzdinė formulė, kaip tai padaryti.

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

DIV/0 klaidą turėsime tik tuo atveju, jei daliklis bus 0 arba tuščias. Taigi mes patikriname daliklį (B2), jei jo nulis, tada atspausdinkite A2, tada padalinkite A2 su B2. Tai taip pat veiks tuščioms ląstelėms.

„Excel“ #NUM klaida

Ši klaida įvyksta, kai numeris negali būti rodomas ekrane. Priežastis gali būti ta, kad skaičius yra per mažas arba per didelis, kad būtų rodomas. Kita priežastis gali būti ta, kad skaičiavimo negalima atlikti naudojant nurodytą skaičių.

= SQRT (ABS (A3))

Tai grąžins 4. Jei norite gauti neigiamą vertę, prieš funkciją naudokite neigiamą ženklą. Žinoma, galite naudoti klaidų tvarkymo funkciją.
Norėdami išspręsti #NUM klaidą, turime specialų straipsnį. Tai galite patikrinti čia.

#NULL klaida „Excel“
Tai reta klaidos rūšis. #NULL klaida, kurią sukėlė neteisinga ląstelių nuoroda. Pavyzdžiui, jei SUM funkcijoje norite nurodyti diapazono A2: A5 nuorodą, bet per klaidą įvedate A2 A5. Tai sukurs #NULL klaidą. Kaip matote paveikslėlyje žemiau, formulė grąžina #NULL klaidą.
Jei pakeisite tarpą stulpeliu (:), #NULL klaida išnyks ir gausite A2: A5 sumą. Jei pakeisite tarpą kableliu (,), gausite A2 ir A5 sumą.

Kaip išspręsti #NULL klaidą?

Kaip žinome, #NULL klaidą sukelia klaida. Norėdami to išvengti, pabandykite pasirinkti diapazonus naudodami žymeklį, o ne įveskite rankiniu būdu.

Kai kuriais atvejais turėsite įvesti diapazono adresą iš klaviatūros. Visada pasirūpinkite jungiamuoju simbolių stulpeliu (:) arba kableliu (,), kad išvengtumėte.

Jei turite #NULL klaidą, patikrinkite nuorodas. Greičiausiai tarp dviejų langelių nuorodų praleidote stulpelį (:) arba kablelį (,). Pakeiskite juos atitinkamu simboliu ir galite eiti.
###### Klaida „Excel“
Kartais manome, kad ši klaida atsirado dėl nepakankamos vietos reikšmei parodyti, tačiau taip nėra. Tokiu atveju galite tiesiog išplėsti langelio plotį, kad pamatytumėte langelio vertę. Nepavadinsiu to klaida.
Tiesą sakant, ši klaida atsirado, kai bandome parodyti neigiamą laiko vertę (nėra tokio dalyko kaip neigiamas laikas). Pavyzdžiui, jei bandysime atimti 1 iš 12:21:00 PM, tai grąžins ######. „Excel“ pirmasis pasimatymas yra 1900-01-01 00:00. Jei bandysite atimti prieš tai einantį laiką, „Excel“ parodys ###### klaidą. Kuo daugiau išplėsite plotį, tuo daugiau gausite #. Aš tai išsamiai aprašiau šiame straipsnyje.

Kaip išvengti ###### „Excel“ klaidos?
Prieš atlikdami aritmetinius skaičiavimus „Excel“ su laiko verte, atminkite šiuos dalykus.

Minimali laiko vertė yra 1/1/1900 00:00. „Excel“ negali turėti tinkamos datos prieš tai
1 yra lygus 24 valandoms (1 dienai) „Excel“. Atimdami valandas, minutes ir sekundes, konvertuokite jas į lygiavertes vertes. Pavyzdžiui, norint atimti 1 valandą iš 12:21, reikia iš jo atimti 1/24.

„Excel“ klaidų stebėjimas
Dabar mes žinome, kokios yra įprastos „Excel“ formulės ir kodėl jos atsiranda. Mes taip pat aptarėme galimą sprendimą kiekvienam „Excel“ klaidų tipui.
Kartais „Excel“ ataskaitose gauname klaidų ir negalėjome žinoti, iš kur klaida iš tikrųjų įvyko. Sunku išspręsti tokias klaidas. Norėdami atsekti šias klaidas, „Excel“ pateikia klaidų sekimo funkciją formulės skirtuke.

Aš išsamiai aptariau klaidų sekimą „Excel“.

Loginių klaidų sprendimas formulėje

Logines klaidas sunku rasti ir išspręsti. „Excel“ nerodo jokio masažo, kai turite loginę savo funkcijos klaidą. Viskas atrodo gerai. Bet tik tu žinai, kad ten kažkas negerai. Pavyzdžiui, gaudami visos dalies procentą, dalį padalintumėte iš viso (= (dalis/iš viso)*100). Jis visada turėtų būti lygus arba mažesnis nei 100%. Kai gauni daugiau nei 100%, žinai, kad kažkas negerai.

Tai buvo paprasta loginė klaida. Tačiau kartais jūsų duomenys gaunami iš kelių šaltinių, tokiu atveju sunku išspręsti logines problemas. Vienas iš būdų yra įvertinti savo formulę.

Skirtuke „Formulė“ galima pasirinkti formulės parinktį. Pasirinkite formulę ir spustelėkite ją. Jums bus parodytas kiekvienas jūsų skaičiavimo žingsnis, kuris padės pasiekti galutinį rezultatą. Čia galite patikrinti, kur kilo problema arba kur įvyko neteisingas skaičiavimas.

Taip pat galite sekti priklausomus asmenis ir precedentus, kad pamatytumėte, nuo kokių nuorodų priklauso jūsų formulė ir kokios formulės priklauso nuo pertikulinės ląstelės.

Taigi taip, vaikinai, tai buvo keletas įprastų klaidų tipų, su kuriais susiduria kiekvienas „Excel“ vartotojas. Mes sužinojome, kodėl atsiranda kiekvienos rūšies klaida ir kaip galime jų išvengti. Mes taip pat sužinojome apie specialią „Excel“ klaidų tvarkymo funkciją. Šiame straipsnyje turime nuorodų į susijusius puslapius, kuriuose išsamiai aptariama problema. Galite juos patikrinti. Jei turite tam tikros rūšies klaidą, kuri jus erzina, paminėkite ją žemiau esančiame komentarų skyriuje. Sprendimą gausite tvarkingai.