Tento tutoriál ukazuje, jak pomocí funkce Excel IFERROR zachytit chyby vzorců a nahradit je jiným vzorcem, prázdnou hodnotou, 0 nebo vlastní zprávou.
Přehled funkcí IFERROR
Funkce IFERROR zkontroluje, zda vzorec způsobí chybu. Pokud je FALSE, vraťte původní výsledek vzorce. Je -li PRAVDA, vraťte jinou zadanou hodnotu.
Chcete -li použít funkci listu IFERROR Excel, vyberte buňku a zadejte:
(Všimněte si, jak se zobrazují vstupy vzorců)
Syntaxe a vstupy funkce IFERROR:
= IFERROR (VALUE, value_if_error)
hodnota - Výraz. Příklad: 4/A1
value_if_error - Hodnota nebo výpočet, který se má provést, pokud předchozí zadání způsobí chybu. Příklad 0 nebo „“ (prázdné)
Co je funkce IFERROR?
Funkce IFERROR spadá do kategorie Logické funkce v aplikaci Microsoft Excel, která zahrnuje ISNA, ISERROR a ISERR. Všechny tyto funkce pomáhají detekovat a zpracovávat chyby vzorců.
IFERROR vám umožňuje provést výpočet. Pokud výpočet ne výsledkem chyby, pak se zobrazí výsledek výpočtu. Pokud výpočet dělá způsobí chybu, pak se provede další výpočet (nebo se zobrazí statická hodnota jako 0, prázdné místo nebo nějaký text).
Kdy byste použili funkci IFERROR?
- Při dělení čísel se vyhnete chybám způsobeným dělením 0
- Při provádění vyhledávání, aby se zabránilo chybám, pokud není hodnota nalezena.
- Pokud chcete provést další výpočet, pokud první způsobí chybu (např. Vyhledejte hodnotu ve 2nd tabulka, pokud není nalezena v první tabulce)
Neošetřené chyby vzorců mohou způsobit chyby ve vašem sešitu, ale viditelné chyby také způsobí, že vaše tabulka bude méně viditelně atraktivní.
Pokud chyba, pak 0
Podívejme se na základní příklad. Níže rozdělujete dvě čísla. Pokud se pokusíte dělit nulou, zobrazí se chyba:
Místo toho vložte výpočet do funkce IFERROR a pokud vydělíte nulou, místo chyby se zobrazí 0:
= IFERROR (A2/B2,0)
Pokud chyba, pak prázdné
Místo nastavení chyb na 0 je můžete nastavit na „prázdné“ s dvojitými uvozovkami („“):
= IFERROR (A2/B2, "")
Podíváme se na další použití IFERROR s funkcí VLOOKUP…
IFERROR s VLOOKUP
Vyhledávací funkce jako VLOOKUP vygenerují chyby, pokud není vyhledávací hodnota nalezena. Jak je uvedeno výše, můžete použít funkci IFERROR k nahrazení chyb mezerami („“) nebo 0s:
= IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "not found")
Pokud dojde k chybě, udělejte něco jiného
Funkci IFERROR lze také použít k provedení 2. výpočtu, pokud 1. výpočet způsobí chybu:
= IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE))
Pokud zde data nejsou nalezena v „LookupTable1“, místo toho se na „LookupTable2“ provede VLOOKUP.
Více vzorců IFERROR
Vnořené IFERROR - VLOOKUP více listů
IFERROR můžete vnořit do jiného IFERROR a provést 3 samostatné výpočty. Zde použijeme dva IFERRORY k provedení VLOOKUPů na 3 samostatných listech:
= IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), IFERROR (VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE), VLOOKUP (A2, LookupTable3! $ A $ 2: $ B $ 4,2, FALSE)))
Index / Match & XLOOKUP
IFERROR bude samozřejmě fungovat také se vzorci Index / Match a XLOOKUP.
IFERROR XLOOKUP
Funkce XLOOKUP je pokročilou verzí funkce VLOOKUP.
= IFERROR (XLOOKUP (A2, LookupTable1! $ A $ 2: $ A $ 4, LookupTable1! $ B $ 2: $ B $ 4), „Nenalezeno“)
IFERROR INDEX / MATCH
INDEX a MATCH lze použít k vytváření výkonnějších VLOOKUPů (podobně jako funguje nová funkce XLOOKUP) v Excelu.
= IFERROR (INDEX (LookupTable1! $ B $ 2: $ B $ 4, MATCH (A3, LookupTable1! $ A $ 2: $ A $ 4,0)), „Nenalezeno“)
IFERROR v polích
Maticové vzorce v Excelu se používají k provedení několika výpočtů pomocí jednoho vzorce. Předpokládejme, že existují tři sloupce Rok, Prodej a Prům. Cena. Celkové množství zjistíte pomocí následujícího vzorce ve sloupci E.
{= SUMA ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4)}
Vzorec funguje dobře, dokud se nepokusí vydělit nulou, což má za následek #DIV/0! chyba.
K vyřešení chyby můžete použít funkci IFERROR takto:
{= SUMA (IFERROR ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4,0))}
Všimněte si, že funkce IFERROR musí být vnořena uvnitř funkce SUMA, jinak se IFERROR použije na celkovou částku a ne na každou jednotlivou položku v poli.
IFNA vs. IFERROR
Funkce IFNA funguje úplně stejně jako funkce IFERROR kromě toho, že funkce IFNA zachytí pouze #N/A chyb. To je velmi užitečné při práci s vyhledávacími funkcemi: pravidelné chyby vzorců budou stále detekovány, ale žádná chyba se neobjeví, pokud není nalezena vyhledávací hodnota.
= IFNA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), „Nenalezeno“)
Pokud ISERROR
Pokud stále používáte Microsoft Excel 2003 nebo starší verzi, můžete IFERROR nahradit kombinací IF a ISERROR. Zde je krátký příklad:
= IF (ISERROR (A2/B2), 0, A2/B2)
IFERROR v Tabulkách Google
Funkce IFERROR funguje v Tabulkách Google úplně stejně jako v Excelu:
IFERROR Příklady ve VBA
VBA nemá vestavěnou IFERROR Fucntion, ale k funkci Excel IFERROR můžete přistupovat také z VBA:
Dim n as long n = Application.WorksheetFunction.IfError (Value, value_if_error)
Application.WorksheetFunction vám poskytuje přístup k mnoha (ne všem) funkcím Excelu ve VBA.
Při čtení hodnot z buněk se obvykle používá IFERROR. Pokud buňka obsahuje chybu, může VBA při pokusu o zpracování hodnoty buňky vyvolat chybovou zprávu. Zkuste to pomocí níže uvedeného příkladu kódu (kde buňka B2 obsahuje chybu):
Sub IFERROR_VBA () Dim n As Long, m As Long 'IFERROR n = Application.WorksheetFunction.IfError (Range ("b2"). Value, 0)' No IFERROR m = Range ("b2"). Value End Sub
Kód přiřadí buňku B2 proměnné. Druhé přiřazení proměnné vyvolá chybu, protože hodnota buňky je #N/A, ale první funguje dobře kvůli funkci IFERROR.
Pomocí VBA můžete také vytvořit vzorec obsahující funkci IFERROR:
Rozsah ("C2"). FormulaR1C1 = "= IFERROR (RC [-2]/RC [-1], 0)"
Zpracování chyb ve VBA je hodně odlišné než v Excelu. Ke zpracování chyb ve VBA obvykle použijete zpracování chyb VBA. Zpracování chyb VBA vypadá takto:
Dílčí testWS () MsgBox DoesWSExist ("test") End Sub Function DoesWSExist (wsName As String) As Boolean Dim ws As Worksheet On Error Resume Next Set ws = Sheets (wsName) 'If Error WS neexistuje, If Err.Number 0 Then DoesWSExist = False Else DoesWSExist = True End If On Error GoTo -1 End Function
Všimněte si, že používáme Pokud Err.Number 0 Then zjistit, zda došlo k chybě. Toto je typický způsob, jak zachytit chyby ve VBA. Funkce IFERROR má však určité využití při interakci s buňkami aplikace Excel.
