Kui kasutate Excelis VLOOKUP valemit, võib mõnikord ilmneda kole #N/A viga. See juhtub siis, kui teie valem ei leia otsinguväärtust.
Selles õpetuses näitan teile erinevaid viise IFERRORi kasutamiseks koos VLOOKUPiga nende töölehel ilmuvate #N/A vigade käsitlemiseks.
IFERRORi ja VLOOKUPi kombinatsiooni kasutamine võimaldab vea #N/A (või mis tahes muu vea) asemel näidata midagi sisukat.
Enne selle kombinatsiooni kasutamise üksikasjade uurimist vaatame kõigepealt kiiresti läbi funktsiooni IFERROR ja vaatame, kuidas see toimib.
Funktsioon IFERROR selgitatud
Funktsiooniga IFERROR saate määrata, mis peaks juhtuma, kui valem või lahtri viide tagastab vea.
Siin on funktsiooni IFERROR süntaks.
= IFERROR (väärtus, väärtus_ kui_viga)
- väärtus - seda argumenti kontrollitakse vea suhtes. Enamasti on see kas valem või lahtri viide. Kui kasutate funktsiooni VLOOKUP koos IFERRORiga, oleks see argument VLOOKUP -i valem.
- value_if_error - see väärtus tagastatakse vea korral. Hinnati järgmisi veatüüpe: #N/A, #REF !, #DIV/0!, #VALUE !, #NUM !, #NAME? Ja #NULL !.
VLOOKUPi võimalikud põhjused Tõrke #N/A tagastamine
Funktsioon VLOOKUP võib tagastada vea #N/A järgmistel põhjustel.
- Otsinguväärtust ei leitud otsingumassiivist.
- Otsinguväärtuses (või tabelimassiivis) on esi-, lõpp- või kahekordne tühik.
- Otsinguväärtuses või otsingumassiivi väärtustes on õigekirjaviga.
Kõigi nende vea põhjustega saate hakkama IFERRORi ja VLOOKUPi kombinatsiooniga. Siiski peaksite silma peal hoidma põhjusel 2 ja #3 ning parandama need lähteandmetes, selle asemel, et lasta IFERRORil nendega tegeleda.
Märkus. IFERROR käsitleb viga olenemata selle põhjustest. Kui soovite käsitleda ainult vigu, mis on põhjustatud sellest, et VLOOKUP ei leia otsinguväärtust, kasutage selle asemel IFNA -d. See tagab, et muid vigu kui #N/A ei töödelda, ja saate neid muid vigu uurida.
Funktsiooni TRIM abil saate töödelda esi-, lõpp- ja topeltruume.
VLOOKUP #N/A vea asendamine sisuka tekstiga
Oletame, et teil on allpool näidatud andmekogum:
Nagu näete, et VLOOKUP valem tagastab vea, kuna otsinguväärtust pole loendis. Otsime Gleni skoori, mida ei ole punktitabelis.
Kuigi see on väga väike andmekogum, võite saada tohutuid andmekogumeid, kus peate kontrollima paljude üksuste esinemist. Igal juhul, kui väärtust ei leita, kuvatakse tõrge #N/A.
Siin on valem, mille abil saate vea #N/A asemel midagi sisukat.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Not Found")
Ülaltoodud valem tagastab vea #N/A asemel teksti „Not Found”. Sama valemi abil saate tagastada tühja, nulli või muu sisuka teksti.
Pesitsev VLOOKUP IFERROR -funktsiooniga
Kui kasutate funktsiooni VLOOKUP ja teie otsingutabel on killustatud samal töölehel või erinevatel töölehtedel, peate kontrollima VLOOKUP väärtust kõigi nende tabelite kaudu.
Näiteks allpool näidatud andmekogumis on kaks eraldi tabelit õpilaste nimede ja tulemuste kohta.
Kui pean selles andmestikus leidma Grace'i skoori, pean esimese tabeli kontrollimiseks kasutama funktsiooni VLOOKUP ja kui väärtust sealt ei leita, siis kontrollige teist tabelit.
Siin on pesastatud IFERROR valem, mida saan kasutada väärtuse otsimiseks:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
VLOOKUPi kasutamine koos IF ja ISERROR (versioonid enne Excel 2007)
Funktsioon IFERROR võeti kasutusele rakenduses Excel 2007 for Windows ja Excel 2016 Macis.
Kui kasutate varasemaid versioone, siis IFERROR funktsioon teie süsteemis ei tööta.
Funktsiooni IFERROR funktsionaalsust saate kopeerida, kasutades funktsiooni IF ja funktsiooni ISERROR kombinatsiooni.
Näitan teile kiiresti, kuidas IFERRORi asemel kasutada IF ja ISERROR kombinatsiooni.
Ülaltoodud näites saate IFERRORi asemel kasutada ka lahtris B3 näidatud valemit:
= KUI (ISERROR (A3), „Not Found”, A3)
Valemi ISERROR osa kontrollib vigu (sh viga #N/A) ja tagastab vea leidmisel tõe ning kui mitte, tagastab tõe.
- Kui see on TÕENE (mis tähendab, et on viga), tagastab IF -funktsioon määratud väärtuse (antud juhul „Ei leitud”).
- Kui see on VÄÄR (mis tähendab, et viga pole), tagastab funktsioon IF selle väärtuse (ülaltoodud näites A3).
IFERROR vs IFNA
IFERROR käsitleb igasuguseid vigu, IFNA aga ainult #N/A viga.
VLOOKUP -i põhjustatud vigade käsitlemisel peate veenduma, et kasutate õiget valemit.
Kasutage IFERRORi kui soovite ravida igasuguseid vigu. Nüüd võivad vea põhjustada mitmesugused tegurid (näiteks vale valem, valesti kirjutatud nimega vahemik, otsinguväärtuse mitte leidmine ja vea väärtuse tagastamine otsingutabelist). IFERRORil poleks vahet ja see asendaks kõik need vead määratud väärtusega.
Kasutage IFNA -d kui soovite ravida ainult #puuduvaid vigu, mis on tõenäolisemalt põhjustatud sellest, et VLOOKUP valem ei leia otsinguväärtust.
Samuti võivad kasulikud olla järgmised Exceli õpetused:
- Kuidas muuta VLOOKUP tõstutundlikuks.
- VLOOKUP vs. INDEX/MATCH - Arutelu lõpeb siin!
- Kasutage Exceli loendis viimase numbri saamiseks VLookupi.
- VLOOKUPi kasutamine mitme kriteeriumiga
- #NAME viga Excelis - mis seda põhjustab ja kuidas seda parandada!
