Amikor a VLOOKUP képletet használja az Excelben, néha a csúnya #N/A hiba léphet fel. Ez akkor fordul elő, ha a képlet nem találja a keresési értéket.
Ebben az oktatóanyagban különböző módszereket mutatok be az IFERROR és a VLOOKUP együttes használatára a munkalapon megjelenő #N/A hibák kezelésére.
Az IFERROR és a VLOOKUP kombinációja lehetővé teszi, hogy valami értelmeset mutasson a #N/A hiba (vagy bármilyen más hiba) helyett.
Mielőtt belekezdenénk ennek a kombinációnak a használatába, először gyorsan nézzük át az IFERROR funkciót, és nézzük meg, hogyan működik.
IFERROR funkció magyarázata
Az IFERROR funkcióval megadhatja, hogy mi történjen, ha egy képlet vagy cellahivatkozás hibát ad vissza.
Itt található az IFERROR függvény szintaxisa.
= IFERROR (érték, érték_ha_hiba)
- érték - ezt az érvet ellenőrzik a hiba miatt. A legtöbb esetben ez vagy képlet, vagy cellahivatkozás. Ha a VLOOKUP -t IFERROR -al használja, akkor a VLOOKUP képlet lenne ez az argumentum.
- érték_ha_hiba - ezt az értéket adja vissza hiba esetén. A következő hibatípusokat értékelték: #N/A, #REF !, #DIV/0!, #VALUE !, #NUM !, #NAME ?, és #NULL !.
A VLOOKUP lehetséges okai #N/A hiba visszaadása
A VLOOKUP funkció #N/A hibát adhat vissza a következő okok miatt:
- A keresési érték nem található a keresési tömbben.
- A keresési értékben (vagy a tábla tömbben) vezető, záró vagy kettős szóköz található.
- Helyesírási hiba van a keresési értékben vagy a keresési tömb értékeiben.
Mindezeket a hiba okokat kezelheti az IFERROR és a VLOOKUP kombinációjával. Azonban figyelnie kell a 2. és 3. okra, és ezeket ki kell javítania a forrásadatokban, ahelyett, hogy hagyja, hogy az IFERROR kezelje ezeket.
Megjegyzés: Az IFERROR kezelné a hibát, függetlenül attól, hogy mi okozta azt. Ha csak azokat a hibákat szeretné kezelni, amelyeket a VLOOKUP nem tud megkeresni, akkor használja az IFNA -t. Ez biztosítja, hogy a #N/A -tól eltérő hibákat ne kezeljék, és megvizsgálhatja ezeket a hibákat.
A TRIM funkcióval kezelheti a vezető, a záró és a kettős szóközöket.
A VLOOKUP #N/A hiba cseréje értelmes szövegre
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Mint látható, a VLOOKUP képlet hibát ad vissza, mivel a keresési érték nincs a listában. Arra törekszünk, hogy megkapjuk a Glen pontszámát, ami nem szerepel a pontszámok táblázatában.
Bár ez egy nagyon kicsi adatkészlet, hatalmas adathalmazokat kaphat, ahol ellenőriznie kell számos elem előfordulását. Minden esetben, amikor az érték nem található, #N/A hibaüzenetet kap.
Íme a képlet, amellyel valami értelmeset kaphat a #N/A hiba helyett.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Not Found")
A fenti képlet a „Not Found” szöveget adja vissza a #N/A hiba helyett. Ugyanezt a képletet használhatja üres, nulla vagy bármilyen más értelmes szöveg visszaadására is.
VLOOKUP beágyazása IFERROR funkcióval
Abban az esetben, ha a VLOOKUP szolgáltatást használja, és a keresési táblázat ugyanazon a munkalapon vagy különböző munkalapokon töredezett, ellenőrizze a VLOOKUP értékét ezeken a táblázatokon keresztül.
Például az alábbi adatkészletben két külön táblázat található a diáknevekről és a pontszámokról.
Ha meg kell találnom a Grace pontszámát ebben az adatkészletben, akkor a VLOOKUP funkciót kell használnom az első táblázat ellenőrzéséhez, és ha az érték nem található meg benne, akkor ellenőrizze a második táblázatot.
Íme a beágyazott IFERROR képlet, amellyel megkereshetem az értéket:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
A VLOOKUP használata IF és ISERROR esetén (az Excel 2007 előtti verziók)
Az IFERROR funkciót az Excel 2007 for Windows és a Excel 2016 a Mac rendszerben vezették be.
Ha a korábbi verziókat használja, akkor az IFERROR funkció nem működik a rendszerben.
Az IFERROR függvény működését megismételheti az IF függvény és az ISERROR függvény kombinációjával.
Hadd mutassam meg gyorsan, hogyan kell használni az IF és az ISERROR kombinációját az IFERROR helyett.
A fenti példában az IFERROR használata helyett a B3 cellában látható képletet is használhatja:
= HA (ISERROR (A3), „Not Found”, A3)
A képlet ISERROR része ellenőrzi a hibákat (beleértve a #N/A hibát is), és IGAZ értéket ad vissza, ha hibát talál, és hamis, ha nem.
- Ha IGAZ (ez azt jelenti, hogy hiba történt), az IF függvény a megadott értéket adja vissza (ebben az esetben „Nem található”).
- Ha HAMIS (ez azt jelenti, hogy nincs hiba), az IF függvény ezt az értéket adja vissza (A3 a fenti példában).
IFERROR vs IFNA
Az IFERROR mindenféle hibát kezel, míg az IFNA csak a #N/A hibát.
A VLOOKUP által okozott hibák kezelésekor győződjön meg arról, hogy a megfelelő képletet használja.
Használja az IFERROR -t amikor mindenféle hibát kezelni szeretne. Most egy hibát különböző tényezők okozhatnak (például a rossz képlet, a hibásan elnevezett tartomány, a keresési érték nem megtalálása és a hibaérték visszaadása a keresési táblázatból). Az IFERROR nem számít, és ezeket a hibákat a megadott értékkel helyettesíti.
Használjon IFNA -t ha csak #N/A hibát szeretne kezelni, amelyeket nagyobb valószínűséggel az okozhat, hogy a VLOOKUP képlet nem találja a keresési értéket.
Az alábbi Excel oktatóanyagok is hasznosak lehetnek:
- Hogyan lehet a VLOOKUP kis- és nagybetűket érzékenyíteni?
- VLOOKUP vs. INDEX/MATCH - A vita itt ér véget!
- Használja a VLookup programot az Excel utolsó listájának megtekintéséhez.
- A VLOOKUP használata több kritériummal
- #NAME hiba az Excelben - mi okozza és hogyan javítható!
