Při práci s daty a vzorci v aplikaci Excel se musíte setkat s chybami.
Pro zpracování chyb Excel poskytl užitečnou funkci - funkci IFERROR.
Než se dostaneme k mechanice používání funkce IFERROR v Excelu, pojďme si nejprve projít různé druhy chyb, se kterými se můžete při práci se vzorci setkat.
Typy chyb v aplikaci Excel
Znalost chyb v aplikaci Excel vám lépe umožní identifikovat možný důvod a nejlepší způsob, jak je zvládnout.
Níže jsou uvedeny typy chyb, se kterými se můžete v aplikaci Excel setkat.
#N/A Chyba
Toto se nazývá chyba „Hodnota není k dispozici“.
Uvidíte to, když použijete vyhledávací vzorec a nemůže najít hodnotu (proto není k dispozici).
Níže je uveden příklad, kde používám vzorec VLOOKUP k nalezení ceny položky, ale vrátí chybu, když nemůže najít tuto položku v poli tabulky.
#DIV/0! Chyba
Tuto chybu pravděpodobně uvidíte, když je číslo děleno 0.
Tomu se říká chyba rozdělení. V níže uvedeném příkladu dává #DIV/0! chyba jako hodnota množství (dělitel ve vzorci) je 0.
#HODNOTA! Chyba
K chybě hodnoty dojde, když ve vzorci použijete nesprávný datový typ.
Například v následujícím příkladu, když se pokusím přidat buňky, které mají čísla a znak A, zobrazí se chyba hodnoty.
To se stává, protože můžete přidat pouze číselné hodnoty, ale místo toho jsem se pokusil přidat číslo s textovým znakem.
#REF! Chyba
Toto se nazývá chyba odkazu a uvidíte to, když odkaz ve vzorci již není platný. To může být případ, kdy vzorec odkazuje na odkaz na buňku a tento odkaz na buňku neexistuje (nastane, když odstraníte řádek/sloupec nebo list, na který se ve vzorci odkazovalo).
V níže uvedeném příkladu, zatímco původní vzorec byl = A2/B2, když jsem odstranil sloupec B, všechny odkazy na něj se staly #REF! a také to dalo #REF! chyba jako výsledek vzorce.
#NAME ERROR
Tato chyba je pravděpodobně důsledkem chybně napsané funkce.
Pokud například místo VLOOKUP omylem použijete VLOKUP, zobrazí se chyba názvu.
#ČÍSLA CHYBA
Pokud se pokusíte vypočítat velmi velkou hodnotu v aplikaci Excel, může dojít k chybě. Například = 187^549 vrátí chybu čísla.
Jiná situace, kde můžete získat chybu NUM, je, když vzorec zadáte neplatný argument čísla. Pokud například vypočítáváte odmocninu, pokud jako argument zadáte číslo a záporné číslo, vrátí chybu čísla.
Pokud například v případě funkce Square Root zadáte jako argument záporné číslo, vrátí chybu čísla (jak je uvedeno níže).
I když jsem zde ukázal pouze několik příkladů, může existovat mnoho dalších důvodů, které mohou vést k chybám v aplikaci Excel. Když se vám v Excelu zobrazí chyby, nemůžete to tam nechat. Pokud se data dále používají ve výpočtech, musíte zajistit, aby byly chyby zpracovány správným způsobem.
Funkce Excel IFERROR je skvělý způsob, jak zvládnout všechny typy chyb v aplikaci Excel.
Funkce Excel IFERROR - přehled
Pomocí funkce IFERROR můžete určit, co má vzorec vrátit místo chyby. Pokud vzorec nevrací chybu, vrátí se jeho vlastní výsledek.
Syntaxe funkce IFERROR
= IFERROR (hodnota, hodnota_if_error)
Vstupní argumenty
- 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.
- 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 !.
Další poznámky:
- Pokud jako argument value_if_error použijete „“, buňka v případě chyby nic nezobrazí.
- Pokud argument value nebo value_if_error odkazuje na prázdnou buňku, je funkcí IFERROR aplikace Excel považována za hodnotu prázdného řetězce.
- Pokud je argumentem hodnota vzorec pole, IFERROR vrátí pole výsledků pro každou položku v rozsahu uvedeném v hodnotě.
Funkce Excel IFERROR - příklady
Zde jsou tři příklady použití funkce IFERROR v aplikaci Excel.
Příklad 1 - Vraťte prázdnou buňku místo chyby
Pokud máte funkce, které mohou vracet chybu, můžete ji zabalit do funkce IFERROR a jako hodnotu vrátit v případě chyby zadat prázdné.
V níže uvedeném příkladu je výsledkem v D4 #DIV/0! chyba, protože dělitel je 0.
V takovém případě můžete místo ošklivé chyby DIV vrátit následující vzorec.
= IFERROR (A1/A2, ””)
Tato funkce IFERROR by zkontrolovala, zda výpočet vede k chybě. Pokud ano, jednoduše vrátí mezeru, jak je uvedeno ve vzorci.
Zde můžete také zadat libovolný jiný řetězec nebo vzorec, který se má zobrazit místo prázdného místa.
Níže uvedený vzorec například vrátí místo prázdné buňky text „Chyba“.
= IFERROR (A1/A2, „chyba“)
Poznámka: Pokud používáte Excel 2003 nebo předchozí verzi, nenajdete v něm funkci IFERROR. V takových případech musíte použít kombinaci funkce IF a funkce ISERROR.
Příklad 2 - Pokud VLOOKUP nemůže najít hodnotu, vraťte „Nenalezeno“
Když použijete funkci Excel VLOOKUP a nenajde hodnotu vyhledávání v zadaném rozsahu, vrátí chybu #N/A.
Níže je například uveden soubor dat o jménech studentů a jejich známkách. Použil jsem funkci VLOOKUP k načtení známek tří studentů (v D2, D3 a D4).
Zatímco vzorec VLOOKUP ve výše uvedeném příkladu najde jména prvních dvou studentů, nemůže najít Joshovo jméno v seznamu, a proto vrací chybu #N/A.
Zde můžeme použít funkci IFERROR k vrácení prázdného nebo nějakého smysluplného textu místo chyby.
Níže je vzorec, který místo chyby vrátí „Nenalezeno“.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 12,2,0), „Nenalezeno“)
Všimněte si toho, že můžete také použít IFNA místo IFERROR s VLOOKUP. Přestože IFERROR bude zpracovávat všechny druhy hodnot chyb, IFNA bude fungovat pouze na chybách #N/A a nebude fungovat s jinými chybami.
Příklad 3 - Vrácení 0 v případě chyby
Pokud nezadáte hodnotu, kterou má IFERROR vrátit v případě chyby, automaticky vrátí 0.
Pokud například rozdělím 100 na 0, jak je uvedeno níže, vrátilo by to chybu.
Pokud však použiji níže uvedenou funkci IFERROR, vrátí místo toho 0. Všimněte si, že po prvním argumentu musíte stále používat čárku.
Příklad 4 - Použití vnořené IFERROR s VLOOKUP
Někdy při použití funkce VLOOKUP může být nutné podívat se na fragmentovanou tabulku polí. Předpokládejme například, že máte záznamy o prodejních transakcích ve 2 samostatných listech a chcete vyhledat číslo položky a zjistit její hodnotu.
To vyžaduje použití vnořené IFERROR s VLOOKUP.
Předpokládejme, že máte datovou sadu, jak je uvedeno níže:
V tomto případě, abyste našli skóre pro Grace, musíte použít níže vnořený vzorec IFERROR:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), „Nenalezeno“))
Tento druh vnoření vzorců zajistí, že získáte hodnotu z kterékoli z tabulky a jakákoli vrácená chyba bude zpracována.
Všimněte si, že v případě, že jsou tabulky na stejném listu, ale v reálném příkladu to bude pravděpodobně na různých listech.
Funkce Excel IFERROR - VIDEO
- Excel A funkce.
- Excel NEBO funkce.
- Excel NENÍ funkce.
- Funkce IF IF.
- Excel IFS funkce.
- Funkce FALSE aplikace Excel.
- Funkce Excel TRUE.
