Kai „Excel“ dirbate su duomenimis ir formulėmis, susiduriate su klaidomis.
Klaidoms tvarkyti „Excel“ pateikė naudingą funkciją - funkciją IFERROR.
Prieš pradėdami nagrinėti IFERROR funkcijos „Excel“ naudojimo mechaniką, pirmiausia apžvelkime įvairių tipų klaidas, su kuriomis galite susidurti dirbdami su formulėmis.
„Excel“ klaidų tipai
Žinodami „Excel“ klaidas, galėsite geriau nustatyti galimą priežastį ir geriausią būdą jas pašalinti.
Toliau pateikiami klaidų tipai, kuriuos galite rasti „Excel“.
#N/A Klaida
Tai vadinama klaida „Vertė nepasiekiama“.
Tai pamatysite, kai naudosite paieškos formulę ir ji neras vertės (taigi nepasiekiama).
Žemiau yra pavyzdys, kai naudoju formulę VLOOKUP, norėdamas rasti elemento kainą, tačiau ji grąžina klaidą, kai neranda to elemento lentelės masyve.
#DIV/0! Klaida
Tikriausiai matysite šią klaidą, kai skaičius bus padalintas iš 0.
Tai vadinama padalijimo klaida. Žemiau pateiktame pavyzdyje jis pateikia #DIV/0! klaida, nes kiekio reikšmė (daliklis formulėje) yra 0.
#VERTĖ! Klaida
Vertės klaida atsiranda, kai formulėje naudojate neteisingą duomenų tipą.
Pavyzdžiui, žemiau esančiame pavyzdyje, kai bandau pridėti langelius, turinčius skaičius ir simbolį A, tai parodo vertės klaidą.
Taip atsitinka, nes galite pridėti tik skaitines reikšmes, bet aš bandžiau pridėti skaičių su teksto simboliu.
#REF! Klaida
Tai vadinama atskaitos klaida ir tai pamatysite, kai formulėje esanti nuoroda nebegalioja. Tai gali būti atvejis, kai formulė nurodo langelio nuorodą ir ta ląstelės nuoroda neegzistuoja (atsitinka, kai ištrinate formulėje nurodytą eilutę/stulpelį ar darbalapį).
Toliau pateiktame pavyzdyje, nors pradinė formulė buvo = A2/B2, kai ištryniau B stulpelį, visos nuorodos į jį tapo #REF! ir taip pat davė #REF! klaida dėl formulės.
#NAME KLAIDA
Ši klaida greičiausiai atsirado dėl klaidingai parašytos funkcijos.
Pvz., Jei vietoj VLOOKUP klaidingai naudojate VLOKUP, tai duos pavadinimo klaidą.
#NUM KLAIDA
Skaičių klaida gali atsirasti, jei bandysite apskaičiuoti labai didelę „Excel“ vertę. Pavyzdžiui, = 187^549 grąžins skaičiaus klaidą.
Kita situacija, kai galite gauti NUM klaidą, yra tada, kai formulei pateikiate netinkamą skaičiaus argumentą. Pvz., Jei skaičiuojate kvadratinę šaknį, jei skaičius ir kaip argumentą nurodote neigiamą skaičių, tai grąžins skaičiaus klaidą.
Pavyzdžiui, „Square Root“ funkcijos atveju, jei kaip argumentą nurodysite neigiamą skaičių, jis grąžins skaičiaus klaidą (kaip parodyta žemiau).
Nors aš čia parodžiau tik keletą pavyzdžių, gali būti daug kitų priežasčių, dėl kurių gali atsirasti „Excel“ klaidų. Kai „Excel“ gaunate klaidų, negalite to tiesiog palikti. Jei duomenys toliau naudojami skaičiuojant, turite įsitikinti, kad klaidos yra tvarkomos teisingai.
„Excel IFERROR“ funkcija yra puikus būdas tvarkyti visų tipų „Excel“ klaidas.
„Excel IFERROR“ funkcija - apžvalga
Naudodami funkciją IFERROR, galite nurodyti, ką norite, kad formulė grąžintų, o ne klaidą. Jei formulė nepateikia klaidos, grąžinamas jos rezultatas.
Funkcijos IFERROR sintaksė
= IFERROR (vertė, vertė_jei_klaidos)
Įvesties argumentai
- vertė - tai yra argumentas, kuris tikrinamas dėl klaidos. Daugeliu atvejų tai yra formulė arba langelio nuoroda.
- value_if_error - tai vertė, kuri grąžinama, jei yra klaida. Įvertinti šie klaidų tipai: #N/A, #REF !, #DIV/0!, #VALUE !, #NUM !, #NAME ?, ir #NULL !.
Papildomi užrašai:
- Jei kaip argumentą value_if_error naudojate „“, klaidos atveju langelis nerodo nieko.
- Jei argumentas reikšmė arba reikšmė_if_klaida nurodo tuščią langelį, „Excel IFERROR“ funkcija jį traktuoja kaip tuščią eilutės reikšmę.
- Jei vertės argumentas yra masyvo formulė, IFERROR grąžins rezultatų masyvą kiekvienam elementui, nurodytam reikšmėje.
„Excel IFERROR“ funkcija - pavyzdžiai
Pateikiame tris IFERROR funkcijos naudojimo „Excel“ pavyzdžius.
1 pavyzdys - vietoj klaidos grąžinkite tuščią langelį
Jei turite funkcijų, kurios gali grąžinti klaidą, galite ją įjungti į funkciją IFERROR ir nurodyti tuščią kaip reikšmę, kurią reikia grąžinti, jei įvyktų klaida.
Žemiau pateiktame pavyzdyje rezultatas D4 yra #DIV/0! klaida, nes daliklis yra 0.
Tokiu atveju galite naudoti šią formulę, kad grąžintumėte tuščią, o ne negražią DIV klaidą.
= IFERROR (A1/A2, "")
Ši IFERROR funkcija patikrintų, ar skaičiavimas nesukelia klaidos. Jei taip, jis tiesiog grąžina tuščią, kaip nurodyta formulėje.
Čia taip pat galite nurodyti bet kurią kitą eilutę ar formulę, kurią norite rodyti vietoj tuščios vietos.
Pavyzdžiui, žemiau pateikta formulė grąžins tekstą „Klaida“, o ne tuščią langelį.
= IFERROR (A1/A2, „Klaida“)
Pastaba: Jei naudojate „Excel 2003“ ar ankstesnę versiją, joje nerasite funkcijos IFERROR. Tokiais atvejais turite naudoti IF ir ISERROR funkcijų derinį.
2 pavyzdys - grąžinkite „Not Found“, kai VLOOKUP negali rasti vertės
Kai naudojate „Excel VLOOKUP“ funkciją ir ji negali rasti paieškos vertės nurodytame diapazone, ji grąžins #N/A klaidą.
Pavyzdžiui, žemiau pateikiamas studentų vardų ir jų pažymių duomenų rinkinys. Aš naudoju funkciją VLOOKUP, norėdamas gauti trijų mokinių (D2, D3 ir D4) pažymius.
Nors aukščiau pateiktame pavyzdyje esanti VLOOKUP formulė nustato pirmųjų dviejų mokinių vardus, ji negali rasti Josho vardo sąraše ir todėl pateikia klaidą #N/A.
Čia mes galime naudoti funkciją IFERROR, kad vietoj klaidos grąžintume tuščią arba prasmingą tekstą.
Žemiau yra formulė, kuri vietoj klaidos grąžins „Nerasta“.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 12,2,0), „Nerasta“)
Atminkite, kad su „VLOOKUP“ taip pat galite naudoti IFNA vietoj IFERROR. Nors IFERROR apdorotų visų rūšių klaidų vertes, IFNA veiktų tik su #N/A klaidomis ir nedirbtų su kitomis klaidomis.
3 pavyzdys - grąžinkite 0, jei įvyktų klaida
Jei klaidos atveju nenurodysite vertės, kurią grąžins IFERROR, ji automatiškai grąžins 0.
Pavyzdžiui, jei padalinu 100 iš 0, kaip parodyta žemiau, tai grąžins klaidą.
Tačiau, jei aš naudoju žemiau pateiktą funkciją IFERROR, vietoj jos bus pateikta 0. Atminkite, kad po pirmojo argumento vis tiek turite naudoti kablelį.
4 pavyzdys. Įdėto IFERROR naudojimas su VLOOKUP
Kartais naudojant „VLOOKUP“ gali tekti peržvelgti suskaidytą masyvų lentelę. Pvz., Tarkime, kad turite pardavimo sandorių įrašus 2 atskiruose darbalapiuose ir norite ieškoti prekės numerio ir pamatyti jo vertę.
Tam reikia naudoti įdėtą IFERROR su VLOOKUP.
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:
Tokiu atveju, norėdami rasti „Grace“ balą, turite naudoti žemiau pateiktą IFERROR formulę:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Nerasta"))
Šio tipo formulės įdėjimas užtikrina, kad gausite vertę iš bet kurios lentelės ir bus grąžinta bet kokia grąžinama klaida.
Atminkite, kad tuo atveju, jei lentelės yra tame pačiame darbalapyje, realiame gyvenime pavyzdys greičiausiai bus skirtinguose darbalapiuose.
„Excel IFERROR“ funkcija - VIDEO
- „Excel“ ir funkcija.
- „Excel“ ARBA funkcija.
- „Excel“ NE funkcija.
- „Excel IF“ funkcija.
- „Excel IFS“ funkcija.
- „Excel FALSE“ funkcija.
- „Excel TRUE“ funkcija.
