Kai naudojate „VLOOKUP“ formulę „Excel“, kartais gali atsirasti negraži klaida #N/A. Taip atsitinka, kai jūsų formulė neranda paieškos vertės.
Šioje pamokoje parodysiu įvairius būdus, kaip naudoti IFERROR su VLOOKUP, kad būtų pašalintos šios #N/A klaidos, atsirandančios jūsų darbalapyje.
Naudodami IFERROR ir VLOOKUP derinį galite parodyti kažką prasmingo vietoj klaidos #N/A (arba bet kokios kitos klaidos).
Prieš imdamiesi išsamios informacijos apie šio derinio naudojimą, pirmiausia greitai peržvelkime funkciją IFERROR ir pažiūrėkime, kaip ji veikia.
IFERROR funkcija paaiškinta
Naudodami funkciją IFERROR, galite nurodyti, kas turėtų atsitikti, jei formulė ar langelio nuoroda pateikia klaidą.
Čia yra funkcijos IFERROR sintaksė.
= IFERROR (vertė, vertė_jei_klaidos)
- vertė - tai yra argumentas, kuris tikrinamas dėl klaidos. Daugeliu atvejų tai yra formulė arba langelio nuoroda. Naudojant VLOOKUP su IFERROR, VLOOKUP formulė būtų šis argumentas.
- 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 !.
Galimos VLOOKUP priežastys, grąžinančios #N/A klaidą
Funkcija VLOOKUP gali grąžinti #N/A klaidą dėl bet kurios iš šių priežasčių:
- Peržiūros vertė nerasta paieškos masyve.
- Peržiūros vertėje (arba lentelės masyve) yra pirmaujanti, galinė arba dviguba tarpas.
- Peržiūros vertėje arba paieškos masyvo reikšmėse yra rašybos klaida.
Visas šias klaidų priežastis galite išspręsti derindami IFERROR ir VLOOKUP. Tačiau turėtumėte stebėti 2 ir 3 priežastis ir ištaisyti juos šaltinio duomenyse, o ne leisti IFERROR juos spręsti.
Pastaba: IFERROR pašalintų klaidą, neatsižvelgiant į tai, kas ją sukėlė. Jei norite tik ištaisyti klaidas, atsiradusias dėl to, kad VLOOKUP nepavyko rasti paieškos vertės, naudokite IFNA. Tai užtikrins, kad kitos klaidos, išskyrus #N/A, nebus gydomos ir galėsite ištirti šias kitas klaidas.
Galite apdoroti priekines, galines ir dvigubas erdves naudodami TRIM funkciją.
VLOOKUP #N/A klaida pakeičiama prasmingu tekstu
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:
Kaip matote, VLOOKUP formulė pateikia klaidą, nes paieškos vertės nėra sąraše. Mes norime gauti Gleno rezultatą, kurio nėra balų lentelėje.
Nors tai labai mažas duomenų rinkinys, galite gauti didžiulius duomenų rinkinius, kuriuose turite patikrinti daugelio elementų atsiradimą. Kiekvienu atveju, kai vertė nerasta, gausite klaidą #N/A.
Štai formulė, kurią galite naudoti norėdami gauti kažką prasmingo, o ne klaidą #N/A.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Nerasta")
Aukščiau pateikta formulė grąžina tekstą „Nerasta“, o ne klaidą #N/A. Tą pačią formulę taip pat galite naudoti norėdami grąžinti tuščią, nulį ar bet kokį kitą prasmingą tekstą.
Įdėti VLOOKUP su IFERROR funkcija
Jei naudojate VLOOKUP ir jūsų peržiūros lentelė yra suskaidyta tame pačiame darbalapyje arba skirtinguose darbalapiuose, turite patikrinti VLOOKUP reikšmę per visas šias lenteles.
Pavyzdžiui, toliau pateiktame duomenų rinkinyje yra dvi atskiros mokinių vardų ir balų lentelės.
Jei šiame duomenų rinkinyje turiu rasti „Grace“ balą, turiu naudoti funkciją VLOOKUP, kad patikrintų pirmąją lentelę, o jei joje nerasta, tada patikrinkite antrąją lentelę.
Čia yra įdėta IFERROR formulė, kurią galiu naudoti norėdami ieškoti vertės:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Nerasta"))
VLOOKUP naudojimas su IF ir ISERROR (versijos iki „Excel 2007“)
Funkcija IFERROR buvo įdiegta „Excel 2007“, skirta „Windows“, ir „Excel 2016“, skirta „Mac“.
Jei naudojate ankstesnes versijas, IFERROR funkcija neveiks jūsų sistemoje.
Funkcijos IFERROR funkcionalumą galite pakartoti naudodami IF funkcijos ir ISERROR funkcijos derinį.
Leiskite greitai parodyti, kaip naudoti IF ir ISERROR derinį vietoj IFERROR.
Anksčiau pateiktame pavyzdyje, vietoj IFERROR, taip pat galite naudoti B3 langelyje pateiktą formulę:
= JEI (ISERROR (A3), „Not Found“, A3)
Formulės dalis ISERROR tikrina, ar nėra klaidų (įskaitant klaidą #N/A), ir, jei randama klaida, grąžina TRUE, o jei ne - FALSE.
- Jei tai tiesa (tai reiškia, kad įvyko klaida), funkcija IF grąžina nurodytą reikšmę (šiuo atveju „Nerasta“).
- Jei tai FALSE (tai reiškia, kad nėra klaidos), funkcija IF grąžina šią vertę (A3 aukščiau pateiktame pavyzdyje).
IFERROR prieš IFNA
IFERROR tvarko visų rūšių klaidas, o IFNA - tik #N/A klaidą.
Tvarkydami klaidas, kurias sukelia VLOOKUP, turite įsitikinti, kad naudojate tinkamą formulę.
Naudokite IFERROR kai nori gydyti visokias klaidas. Dabar klaidą gali sukelti įvairūs veiksniai (pvz., Neteisinga formulė, klaidingai įvardytas diapazonas, nerasta paieškos vertės ir grąžinama klaidos vertė iš peržiūros lentelės). IFERROR tai nesvarbu ir visas šias klaidas pakeis nurodyta verte.
Naudokite IFNA kai norite gydyti tik #N/A klaidas, kurias greičiausiai sukelia tai, kad VLOOKUP formulė negali rasti paieškos vertės.
Taip pat gali būti naudingi šie „Excel“ vadovai:
- Kaip padaryti, kad VLOOKUP būtų jautrus didžiosioms ir mažosioms raidėms.
- VLOOKUP vs. INDEX/MATCH - diskusijos baigiasi čia!
- Naudokite „VLookup“, kad gautumėte paskutinį „Excel“ sąrašo numerį.
- Kaip naudoti VLOOKUP su keliais kriterijais
- #NAME klaida „Excel“ - kas tai sukelia ir kaip ją išspręsti!
