Pri použití vzorca VLOOKUP v Exceli môže niekedy skončiť škaredá chyba #N/A. K tomu dôjde, keď váš vzorec nemôže nájsť vyhľadávaciu hodnotu.
V tomto tutoriále vám ukážem rôzne spôsoby, ako použiť IFERROR s VLOOKUP na zvládnutie týchto #N/A chýb, ktoré sa objavujú vo vašom hárku.
Použitie kombinácie IFERROR s VLOOKUP vám umožní ukázať niečo zmysluplné namiesto chyby #N/A (alebo akejkoľvek inej chyby).
Predtým, ako sa dostaneme k podrobnostiam o použití tejto kombinácie, najskôr si rýchlo prejdeme funkciu IFERROR a zistíme, ako funguje.
Funkcia IFERROR vysvetlená
Pomocou funkcie IFERROR môžete určiť, čo sa má stať, ak vzorec alebo odkaz na bunku vráti chybu.
Tu je syntax funkcie IFERROR.
= IFERROR (hodnota, hodnota_ak_ chyba)
- hodnota - toto je argument, pri ktorom sa kontroluje chyba. Vo väčšine prípadov ide buď o vzorec, alebo o odkaz na bunku. Pri použití funkcie VLOOKUP s IFERROR bude týmto argumentom vzorec VLOOKUP.
- value_if_error - je to hodnota, ktorá sa vráti v prípade chyby. Hodnotené boli nasledujúce typy chýb: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, a #NULL !.
Možné príčiny chyby VLOOKUP pri vrátení chyby #N/A
Funkcia VLOOKUP môže vrátiť chybu #N/A z ktoréhokoľvek z nasledujúcich dôvodov:
- Hodnota vyhľadávania sa vo vyhľadávacom poli nenachádza.
- Vo vyhľadávacej hodnote (alebo v poli tabuľky) je počiatočný, koncový alebo dvojitý priestor.
- Vo vyhľadávacej hodnote alebo v hodnotách vyhľadávacieho poľa je pravopisná chyba.
Všetky tieto príčiny chýb môžete zvládnuť kombináciou IFERROR a VLOOKUP. Mali by ste však dávať pozor na príčinu č. 2 a č. 3 a opraviť ich v zdrojových údajoch, namiesto toho, aby sa o ne postarala spoločnosť IFERROR.
Poznámka: IFERROR bude liečiť chybu bez ohľadu na to, čo ju spôsobilo. Ak chcete liečiť iba chyby spôsobené tým, že VLOOKUP nemôže nájsť vyhľadávaciu hodnotu, použite namiesto toho IFNA. Zaistíte tak, že sa nebudú zaobchádzať s chybami inými ako #N/A, a budete môcť tieto ďalšie chyby prešetriť.
Úvodné, koncové a dvojité medzery môžete ošetriť pomocou funkcie TRIM.
Výmena chyby VLOOKUP #N/A za zmysluplný text
Predpokladajme, že máte množinu údajov, ako je uvedené nižšie:
Ako vidíte, vzorec VLOOKUP vracia chybu, pretože vyhľadávaná hodnota nie je v zozname. Hľadáme skóre pre Glen, ktorý nie je v tabuľke skóre.
Aj keď je to veľmi malá množina údajov, môžete získať obrovské množiny údajov, v ktorých musíte skontrolovať výskyt mnohých položiek. V každom prípade, keď sa hodnota nenájde, zobrazí sa chyba #N/A.
Tu je vzorec, ktorý môžete použiť na získanie niečoho zmysluplného namiesto chyby #N/A.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), „Nenašlo sa“)
Vyššie uvedený vzorec namiesto chyby #N/A vracia text „Nenašiel sa“. Rovnaký vzorec môžete použiť aj na vrátenie prázdneho, nulového alebo iného zmysluplného textu.
Vnorenie VLOOKUP s funkciou IFERROR
V prípade, že používate funkciu VLOOKUP a vaša vyhľadávacia tabuľka je fragmentovaná na rovnakom pracovnom hárku alebo rôznych pracovných hárkoch, musíte skontrolovať hodnotu VLOOKUP prostredníctvom všetkých týchto tabuliek.
Napríklad v nižšie uvedenej množine údajov existujú dve samostatné tabuľky mien študentov a skóre.
Ak mám nájsť skóre Grace v tejto množine údajov, musím použiť funkciu VLOOKUP na kontrolu prvej tabuľky, a ak sa v nej hodnota nenachádza, potom skontrolovať druhú tabuľku.
Tu je vnorený vzorec IFERROR, ktorý môžem použiť na vyhľadanie hodnoty:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), „Nenašlo sa“))
Použitie VLOOKUP s IF a ISERROR (verzie pred Excelom 2007)
Funkcia IFERROR bola predstavená v programe Excel 2007 pre Windows a Excel 2016 v systéme Mac.
Ak používate predchádzajúce verzie, funkcia IFERROR vo vašom systéme nebude fungovať.
Funkciu funkcie IFERROR môžete replikovať pomocou kombinácie funkcie IF a ISERROR.
Ukážem vám rýchlo, ako použiť kombináciu IF a ISERROR namiesto IFERROR.
Vo vyššie uvedenom príklade môžete namiesto použitia IFERROR použiť aj vzorec uvedený v bunke B3:
= IF (ISERROR (A3), „Not Found“, A3)
ISERROR časť vzorca vyhľadá chyby (vrátane chyby #N/A) a vráti TRUE, ak sa nájde chyba, a FALSE, ak nie.
- Ak je PRAVDA (čo znamená, že došlo k chybe), funkcia IF vráti zadanú hodnotu (v tomto prípade „Nenašlo sa“).
- Ak je FALSE (čo znamená, že neexistuje žiadna chyba), funkcia IF vráti túto hodnotu (A3 vo vyššie uvedenom príklade).
IFERROR vs. IFNA
IFERROR zaobchádza so všetkými druhmi chýb, zatiaľ čo IFNA považuje iba za chybu #N/A.
Pri manipulácii s chybami spôsobenými VLOOKUP sa musíte uistiť, že používate správny vzorec.
Použite IFERROR keď chcete liečiť všetky druhy chýb. Teraz môže byť chyba spôsobená rôznymi faktormi (napríklad nesprávnym vzorcom, nesprávne zadaným názvom rozsahu, nezistením vyhľadávanej hodnoty a vrátením chybovej hodnoty z vyhľadávacej tabuľky). Na IFERROR by to nevadilo a všetky tieto chyby by nahradilo uvedenou hodnotou.
Použite IFNA keď chcete liečiť iba #N/A chýb, ktoré sú pravdepodobnejšie spôsobené tým, že vzorec VLOOKUP nemôže nájsť vyhľadávaciu hodnotu.
Tiež vám môžu byť užitočné nasledujúce návody pre Excel:
- Ako zabezpečiť, aby VLOOKUP rozlišoval malé a veľké písmená.
- VLOOKUP vs. INDEX/ZÁPAS - Debata sa tu končí!
- Použite VLookup na získanie posledného čísla v zozname v programe Excel.
- Ako používať VLOOKUP s viacerými kritériami
- Chyba #NAME v programe Excel - čo ju spôsobuje a ako ju opraviť!
