Při použití vzorce VLOOKUP v aplikaci Excel může někdy dojít k ošklivé chybě #N/A. K tomu dochází, když váš vzorec nemůže najít hodnotu vyhledávání.
V tomto tutoriálu vám ukážu různé způsoby, jak pomocí IFERROR s VLOOKUP zvládnout tyto #N/A chyby oříznutí v listu.
Použití kombinace IFERROR s VLOOKUP vám umožní ukázat místo chyby #N/A (nebo jakékoli jiné chyby) něco smysluplného.
Než se dostaneme k podrobnostem o používání této kombinace, pojďme si nejprve rychle projít funkci IFERROR a zjistit, jak funguje.
Funkce IFERROR vysvětlena
Pomocí funkce IFERROR můžete určit, co se má stát v případě, že vzorec nebo odkaz na buňku vrátí chybu.
Zde je syntaxe funkce IFERROR.
= IFERROR (hodnota, hodnota_if_error)
- hodnota - toto je argument, u kterého se kontroluje chyba. Ve většině případů jde buď o vzorec, nebo o odkaz na buňku. Při použití funkce VLOOKUP s IFERROR bude tímto argumentem vzorec VLOOKUP.
- value_if_error - toto je hodnota, která je vrácena v případě chyby. Byly vyhodnoceny následující typy chyb: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, a #NULL !.
Možné příčiny VLOOKUP vrácení chyby #N/A
Funkce VLOOKUP může vrátit chybu #N/A z některého z následujících důvodů:
- Vyhledávací hodnota není ve vyhledávacím poli nalezena.
- Ve vyhledávací hodnotě (nebo v poli tabulky) je úvodní, koncový nebo dvojitý prostor.
- Ve vyhledávací hodnotě nebo v hodnotách vyhledávacího pole je pravopisná chyba.
Všechny tyto příčiny chyb můžete zvládnout kombinací IFERROR a VLOOKUP. Měli byste si však dávat pozor na příčinu č. 2 a č. 3 a opravit je ve zdrojových datech, místo abyste je nechali zpracovat IFERROR.
Poznámka: IFERROR by zpracoval chybu bez ohledu na to, co ji způsobilo. Pokud chcete ošetřit pouze chyby způsobené tím, že VLOOKUP nemůže najít vyhledávací hodnotu, použijte místo toho IFNA. Tím zajistíte, že nebudou zpracovány chyby jiné než #N/A, a můžete tyto další chyby prozkoumat.
Pomocí funkce TRIM můžete ošetřit úvodní, koncové a dvojité mezery.
Nahrazení chyby VLOOKUP #N/A smysluplným textem
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
Jak vidíte, vzorec VLOOKUP vrací chybu, protože vyhledávací hodnota není v seznamu. Hledáme skóre pro Glen, který není v tabulce skóre.
I když se jedná o velmi malou datovou sadu, můžete získat obrovské množiny dat, kde budete muset zkontrolovat výskyt mnoha položek. Pro každý případ, kdy není hodnota nalezena, se zobrazí chyba #N/A.
Zde je vzorec, který můžete použít k získání něčeho smysluplného namísto chyby #N/A.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), „Nenalezeno“)
Výše uvedený vzorec namísto chyby #N/A vrací text „Nenalezeno“. Stejný vzorec můžete také použít k vrácení prázdného, nulového nebo jiného smysluplného textu.
Vnoření VLOOKUP s funkcí IFERROR
V případě, že používáte VLOOKUP a vaše vyhledávací tabulka je fragmentována na stejném listu nebo různých listech, musíte zkontrolovat hodnotu VLOOKUP prostřednictvím všech těchto tabulek.
Například v níže uvedené datové sadě existují dvě samostatné tabulky jmen studentů a skóre.
Pokud mám najít skóre Grace v této datové sadě, musím použít funkci VLOOKUP ke kontrole první tabulky, a pokud v ní hodnota není nalezena, pak zkontrolovat druhou tabulku.
Zde je vnořený vzorec IFERROR, který mohu použít k vyhledání hodnoty:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), „Nenalezeno“))
Použití funkce VLOOKUP s IF a ISERROR (verze před Excel 2007)
Funkce IFERROR byla zavedena v aplikaci Excel 2007 pro Windows a Excel 2016 v systému Mac.
Pokud používáte předchozí verze, funkce IFERROR ve vašem systému nebude fungovat.
Funkci funkce IFERROR můžete replikovat pomocí kombinace funkce IF a funkce ISERROR.
Ukážu vám rychle, jak použít kombinaci IF a ISERROR místo IFERROR.
Ve výše uvedeném příkladu můžete místo použití IFERROR použít také vzorec zobrazený v buňce B3:
= IF (ISERROR (A3), „Not Found“, A3)
Část vzorce ISERROR kontroluje chyby (včetně chyby #N/A) a vrací TRUE, pokud je nalezena chyba, a FALSE, pokud ne.
- Pokud je PRAVDA (což znamená, že došlo k chybě), funkce IF vrátí zadanou hodnotu (v tomto případě „Nenalezeno“).
- Pokud je FALSE (což znamená, že neexistuje žádná chyba), funkce IF vrátí tuto hodnotu (A3 ve výše uvedeném příkladu).
IFERROR vs. IFNA
IFERROR zachází se všemi druhy chyb, zatímco IFNA zachází pouze s chybou #N/A.
Při zpracování chyb způsobených funkcí VLOOKUP se musíte ujistit, že používáte správný vzorec.
Použijte IFERROR když chcete ošetřit všechny druhy chyb. Nyní může být chyba způsobena různými faktory (například špatným vzorcem, chybně pojmenovaným rozsahem, nenalezením vyhledávací hodnoty a vrácením chybové hodnoty z vyhledávací tabulky). Pro IFERROR by to nevadilo a nahradilo by to všechny tyto chyby zadanou hodnotou.
Použijte IFNA když chcete ošetřit pouze chyby #N/A, které jsou pravděpodobně způsobeny tím, že vzorec VLOOKUP nedokáže najít hodnotu vyhledávání.
Také vám mohou být užitečné následující návody k Excelu:
- Jak zajistit, aby VLOOKUP rozlišoval velká a malá písmena.
- VLOOKUP vs. INDEX/MATCH - Debata zde končí!
- Použijte VLookup k získání posledního čísla v seznamu v aplikaci Excel.
- Jak používat VLOOKUP s více kritérii
- Chyba #NAME v Excelu - co to způsobuje a jak to opravit!
