Pri používaní vzorcov VLOOKUP v Exceli môžete niekedy naraziť na škaredé chyby #N/A.To sa stane, keď váš vzorec nemôže nájsť vyhľadávanú hodnotu.
V tomto návode vám ukážem rôzne spôsoby použitia IFERROR a VLOOKUP na spracovanie týchto chýb #N/A v pracovných hárkoch.
Použite IFERROR v kombinácii s VLOOKUP na zobrazenie niečoho zmysluplného namiesto chyby #N/A (alebo akejkoľvek inej chyby).
Než sa dostaneme k podrobnostiam o používaní tejto kombinácie, pozrime sa rýchlo na funkciu IFERROR, aby sme videli, ako funguje.
Obsah
Popis funkcie IFERROR
Pomocou funkcie IFERROR môžete určiť, čo sa má stať, keď vzorec alebo odkaz na bunku vráti chybu.
Toto je syntax funkcie IFERROR.
=IFERROR(hodnota, hodnota_ak_chyba)
- hodnota - Toto je parameter na kontrolu chýb.Vo väčšine prípadov je to buď vzorec alebo odkaz na bunku.Pri použití funkcie VLOOKUP s IFERROR bude týmto parametrom vzorec VLOOKUP.
- hodnota_ak_chyba – Toto je hodnota vrátená pri výskyte chyby.Boli vyhodnotené nasledujúce typy chýb: #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? a #NULL!.
Možné dôvody, prečo funkcia VLOOKUP vracia chyby #N/A
Funkcia VLOOKUP môže vrátiť chybu #N/A z niektorého z nasledujúcich dôvodov:
- Vyhľadávacia hodnota sa nenašla vo vyhľadávacom poli.
- Vo vyhľadávacej hodnote (alebo poli tabuľky) sú na začiatku, na konci alebo dvojité medzery.
- Vo vyhľadávacej hodnote alebo vyhľadávacej hodnote v poli je preklep.
Na zvládnutie všetkých týchto príčin chýb môžete použiť IFERROR v spojení s VLOOKUP.Mali by ste si však uvedomiť dôvody #2 a #3 a opraviť ich v zdrojových údajoch, než nechať IFERROR, aby ich riešil.
Poznámka: IFERROR spracuje chybu bez ohľadu na to, čo ju spôsobilo.Ak chcete spracovať iba chyby spôsobené tým, že funkcia VLOOKUP nedokáže nájsť vyhľadávaciu hodnotu, použite namiesto toho IFNA.Zabezpečíte tak, že sa nebudú riešiť chyby iné ako #N/A a tieto ďalšie chyby môžete preskúmať.
Funkciu TRIM môžete použiť na spracovanie medzier na začiatku, na konci a dvojitých medzier.
Nahraďte chyby VLOOKUP #N/A zmysluplným textom
Predpokladajme, že máte množinu údajov, ktorá vyzerá takto:
Ako vidíte, vzorec VLOOKUP vracia chybu, pretože vyhľadávaná hodnota nie je v zozname.Hľadáme skóre Glena, nie je v tabuľke skóre.
Aj keď ide o veľmi malý súbor údajov, môžete skončiť s veľkým súborom údajov, kde budete musieť skontrolovať výskyt mnohých položiek.Pre každý prípad, keď sa hodnota nenájde, sa zobrazí chyba #N/A.
Toto je vzorec, ktorý môžete použiť na získanie niečoho zmysluplného a nie #N/A nesprávne.
=IFERROR(VLOOKUP(D2;$A$2:$B$10,2,0;XNUMX;XNUMX);Nenájdené“)
Vyššie uvedený vzorec vráti text „Nenájdené“ namiesto chyby #N/A.Rovnaký vzorec môžete použiť aj na vrátenie prázdnych miest, núl alebo akéhokoľvek iného zmysluplného textu.
Vnorené VLOOKUP s funkciou IFERROR
Ak používate funkciu VLOOKUP a vaše vyhľadávacie tabuľky sú rozložené v rovnakom hárku alebo rôznych hárkoch, musíte skontrolovať hodnotu VLOOKUP vo všetkých týchto tabuľkách.
Napríklad v súbore údajov zobrazenom nižšie sú dve samostatné tabuľky mien a skóre študentov.
Ak mám nájsť skóre Grace v tomto súbore údajov, musím pomocou funkcie VLOOKUP skontrolovať prvú tabuľku a ak sa v nej hodnota nenájde, skontrolovať druhú tabuľku.
Tu je vnorený vzorec IFERROR, ktorý môžem použiť na nájdenie hodnoty:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
Používanie funkcie VLOOKUP s IF a ISERROR (pred Excelom 2007)
Funkcia IFERROR bola predstavená v Exceli 2007 pre Windows a Excel 2016 v Macu.
Ak používate predchádzajúcu verziu, funkcia IFERROR nebude vo vašom systéme fungovať.
Funkčnosť funkcie IFERROR môžete replikovať kombináciou funkcie IF s funkciou ISERROR.
Dovoľte mi rýchlo vám ukázať, 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 zobrazený v bunke B3:
=IF(ISERROR(A3);Nenájdené;A3)
Časť vzorca ISERROR skontroluje chyby (vrátane chýb #N/A) a v prípade nájdenia chyby vráti hodnotu TRUE, v opačnom prípade FALSE.
- Ak je TRUE (indikuje chybu), funkcia IF vráti zadanú hodnotu (v tomto prípade „Nenájdené“).
- Ak je FALSE (čo znamená, že nie je žiadna chyba), funkcia IF vráti túto hodnotu (A3 v príklade vyššie).
IFERROR a IFNA
IFERROR spracováva všetky typy chýb, zatiaľ čo IFNA spracováva iba chyby #N/A.
Pri riešení chýb spôsobených funkciou VLOOKUP sa musíte uistiť, že používate správny vzorec.
Ak chcete riešiť rôzne chyby, prosímPoužite IFERROR.Chyby môžu byť teraz spôsobené rôznymi faktormi (ako sú nesprávne vzorce, nesprávne napísané rozsahy, vyhľadávacie hodnoty sa nenašli a nesprávne hodnoty vrátené z vyhľadávacích tabuliek).IFERROR nevadí, všetky tieto chyby nahradí zadanou hodnotou.
IFNA použite, ak chcete spracovať iba chyby #N/A, čo je pravdepodobnejšie spôsobené tým, že vzorec VLOOKUP nedokáže nájsť vyhľadávanú hodnotu.
