Tento tutoriál ukazuje, ako používať funkciu Excel IFERROR na zachytávanie chýb vzorcov a ich nahradenie iným vzorcom, prázdnou hodnotou, 0 alebo vlastnou správou.
Prehľad funkcií IFERROR
Funkcia IFERROR kontroluje, či vzorec spôsobí chybu. Ak je FALSE, vráťte pôvodný výsledok vzorca. Ak je PRAVDA, vráťte inú zadanú hodnotu.
Syntax IFERROR
Ak chcete použiť funkciu pracovného listu IFERROR Excel, vyberte bunku a zadajte:= IFERROR (
Všimnite si, ako sa zobrazujú vstupy vzorca IFERROR:
Syntax a vstupy funkcie IFERROR:
| 1 | = IFERROR (VALUE, value_if_error) |
hodnotu - Výraz. Príklad: 4/A1
value_if_error - Hodnota alebo výpočet, ktoré sa majú vykonať, ak predchádzajúci vstup spôsobí chybu. Príklad 0 alebo „“ (prázdne)
Čo je to funkcia IFERROR?
Funkcia IFERROR patrí do kategórie Logické funkcie v programe Microsoft Excel, ktorá zahŕňa ISNA, ISERROR a ISERR. Všetky tieto funkcie pomáhajú odhaľovať a spracovávať chyby vzorcov.
IFERROR vám umožňuje vykonať výpočet. Ak výpočet nie spôsobí chybu, potom sa zobrazí výsledok výpočtu. Ak výpočet robí výsledkom chyby, potom sa vykoná ďalší výpočet (alebo sa zobrazí statická hodnota ako 0, prázdne miesto alebo nejaký text).
Kedy by ste použili funkciu IFERROR?
- Pri delení čísel sa vyhneme chybám spôsobeným delením 0
- Pri vyhľadávaní zabrániť chybám, ak sa hodnota nenájde.
- Ak chcete vykonať ďalší výpočet, ak prvý spôsobí chybu (napr. Vyhľadajte hodnotu v 2nd tabuľka, ak sa nenachádza v prvej tabuľke)
Nespracované chyby vzorcov môžu spôsobiť chyby vo vašom zošite, ale vďaka viditeľným chybám je tabuľka menej viditeľne príťažlivá.
Ak chyba, potom 0
Pozrime sa na základný príklad. Nižšie delíte dve čísla. Ak sa pokúsite deliť nulou, zobrazí sa chyba:
Namiesto toho vložte výpočet do funkcie IFERROR a ak delíte nulou, namiesto chyby sa zobrazí 0:
| 1 | = IFERROR (A2/B2,0) |
Ak sa vyskytne chyba, potom je prázdne
Namiesto nastavenia chýb na 0 ich môžete nastaviť na „prázdne“ s dvojitými úvodzovkami („“):
| 1 | = IFERROR (A2/B2, "") |
Pozrime sa na ďalšie použitia IFERROR s funkciou VLOOKUP…
IFERROR s VLOOKUP
Ak nenájdete vyhľadávaciu hodnotu, funkcie vyhľadávania, ako napríklad VLOOKUP, budú generovať chyby. Ako je uvedené vyššie, funkciu IFERROR môžete použiť na nahradenie chýb prázdnymi miestami („“) alebo 0 s:
| 1 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), „not found“) |
Ak sa vyskytne chyba, urobte niečo iné
Funkciu IFERROR je možné použiť aj na vykonanie druhého výpočtu, ak pri prvom výpočte dôjde k chybe:
| 12 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE),VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE)) |
Ak sa údaje nenachádzajú v „LookupTable1“, v tomto prípade sa namiesto toho vykoná VLOOKUP na „LookupTable2“.
Viac príkladov vzorcov IFERROR
Vnorené IFERROR - VLOOKUP Viac listov
Môžete vložiť IFERROR do iného IFERROR a vykonať 3 samostatné výpočty. Tu použijeme dva IFERRORY na vykonanie VLOOKUPOV na 3 samostatných pracovných listoch:
| 123 | = 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 / zápas a XLOOKUP
IFERROR bude samozrejme fungovať aj so vzorcami Index / Match a XLOOKUP.
IFERROR XLOOKUP
Funkcia XLOOKUP je rozšírenou verziou funkcie VLOOKUP.
| 1 | = IFERROR (XLOOKUP (A2, LookupTable1! $ A $ 2: $ A $ 4, LookupTable1! $ B $ 2: $ B $ 4), „Nenašlo sa“) |
INDEX IFERROR / ZHODA
INDEX a MATCH je možné použiť na vytváranie výkonnejších VLOOKUP (podobne ako funguje nová funkcia XLOOKUP) v Exceli.
| 1 | = IFERROR (INDEX (LookupTable1! $ B $ 2: $ B $ 4, MATCH (A3, LookupTable1! $ A $ 2: $ A $ 4,0)), „Nenašlo sa“) |
IFERROR v poliach
Maticové vzorce v programe Excel sa používajú na vykonávanie niekoľkých výpočtov prostredníctvom jedného vzorca. Predpokladajme, že existujú tri stĺpce roku, predaja a priemernej ceny. Celkové množstvo môžete zistiť pomocou nasledujúceho vzorca v stĺpci E.
| 1 | {= SUM ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4)} |
Vzorec funguje dobre, kým sa nepokúsi vydeliť nulou, čo má za následok #DIV/0! chyba.
Na vyriešenie chyby môžete použiť funkciu IFERROR takto:
| 1 | {= SÚČET (IFERROR ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4,0))} |
Všimnite si, že funkcia IFERROR musí byť vnorená do funkcie SUMA, inak sa IFERROR použije na celkový súčet a nie na každú jednotlivú položku v poli.
IFNA vs. IFERROR
Funkcia IFNA funguje úplne rovnako ako funkcia IFERROR, okrem toho, že funkcia IFNA zachytí iba #N/A chýb. Toto je veľmi užitočné pri práci s vyhľadávacími funkciami: pravidelné chyby vzorcov sa budú naďalej zisťovať, ale pokiaľ sa vyhľadávacia hodnota nenájde, nezobrazí sa žiadna chyba.
| 1 | = IFNA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), „Nenašlo sa“) |
Ak ISERROR
Ak stále používate Microsoft Excel 2003 alebo staršiu verziu, môžete IFERROR nahradiť kombináciou IF a ISERROR. Tu je stručný príklad:
| 1 | = IF (ISERROR (A2/B2), 0, A2/B2) |
IFERROR v Tabuľkách Google
Funkcia IFERROR funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
IFERROR Príklady vo VBA
VBA nemá vstavanú IFERROR Fucntion, ale k funkcii Excel IFERROR máte prístup aj z VBA:
| 12 | Dim n tak dlhon = Application.WorksheetFunction.IfError (hodnota, hodnota_ak_ chyba) |
Application.WorksheetFunction vám poskytuje prístup k mnohým (nie všetkým) funkciám Excelu vo VBA.
Pri čítaní hodnôt z buniek sa zvyčajne používa IFERROR. Ak bunka obsahuje chybu, VBA môže pri pokuse o spracovanie hodnoty bunky zobraziť chybové hlásenie. Skúste to pomocou nižšie uvedeného ukážkového kódu (kde bunka B2 obsahuje chybu):
| 1234567891011 | Sub IFERROR_VBA ()Dim n As Long, m As Long„IFERRORn = Application.WorksheetFunction.IfError (rozsah ("b2"). hodnota, 0)„Žiadna IFERRORm = rozsah („b2“). HodnotaKoniec pod |
Kód priradí bunke B2 premennú. Druhé priradenie premennej vyvolá chybu, pretože hodnota bunky je #N/A, ale prvé funguje dobre kvôli funkcii IFERROR.
VBA môžete použiť aj na vytvorenie vzorca obsahujúceho funkciu IFERROR:
| 1 | Rozsah ("C2"). FormulaR1C1 = "= IFERROR (RC [-2]/RC [-1], 0)" |
Riešenie chýb vo VBA je veľmi odlišné ako v programe Excel. Na spracovanie chýb vo VBA budete spravidla používať spracovanie chýb VBA. Spracovanie chýb VBA vyzerá takto:
| 12345678910111213141516171819 | Sub testWS ()MsgBox DoesWSExist („test“)Koniec podFunkcia DoesWSExist (wsName As String) As BooleanDim ws As worksheetPri chybe Pokračovať ďalejNastaviť ws = hárky (wsName)„Ak chyba WS neexistujeAk Err.Number 0 PotomDoesWSExist = FalseInakDoesWSExist = PravdaKoniec AkPri chybe GoTo -1Koncová funkcia |
Všimnite si, že používame Ak Err.Number 0 Potom zistiť, či sa vyskytla chyba. Toto je typický spôsob, ako zachytiť chyby vo VBA. Pri interakcii s bunkami programu Excel má však funkcia IFERROR určité využitie.
