Ez az oktatóanyag bemutatja, hogyan kell használni az Excel IFERROR függvényt a képlethibák felkutatására, más képlet, üres érték, 0 vagy egyéni üzenet helyett.
Az IFERROR funkció áttekintése
Az IFERROR függvény ellenőrzi, hogy egy képlet hibát eredményez -e. Ha HAMIS, adja vissza a képlet eredeti eredményét. Ha IGAZ, adjon meg egy másik megadott értéket.
IFERROR szintaxis
Az IFERROR Excel munkalap funkció használatához jelöljön ki egy cellát és írja be:= IFERROR (
Figyelje meg, hogyan jelennek meg az IFERROR képlet bemenetei:
IFERROR funkció szintaxisa és bemenetei:
1 | = HIBÁS (VALUE, value_if_error) |
érték - Egy kifejezés. Példa: 4/A1
érték_ha_hiba - Érték vagy számítás elvégzésére, ha az előző bemenet hibát eredményez. 0. példa vagy „” (üres)
Mi az IFERROR függvény?
Az IFERROR függvény a Microsoft Excel logikai funkcióinak kategóriájába tartozik, amely magában foglalja az ISNA, ISERROR és ISERR elemeket. Mindezek a funkciók segítenek a képlethibák észlelésében és kezelésében.
Az IFERROR lehetővé teszi számítások elvégzését. Ha a számítás nem hibát eredményez, akkor megjelenik a számítási eredmény. Ha a számítás csinál hibát eredményez, akkor egy másik számítást hajtanak végre (vagy statikus értéket, például 0 -t, üreset vagy valamilyen szöveget adnak ki).
Mikor használja az IFERROR funkciót?
- Amikor osztja a számokat, hogy elkerülje a 0 -val való osztás okozta hibákat
- Amikor keresést végez a hibák megelőzése érdekében, ha az érték nem található.
- Ha egy másik számítást szeretne elvégezni, ha az első hibát eredményez (pl. 2 -es érték megkeresése)nd táblázat, ha nem található az első táblázatban)
A nem kezelt képlethibák hibákat okozhatnak a munkafüzetben, de a látható hibák miatt a táblázat kevésbé láthatóan vonzó.
Ha hiba akkor 0
Nézzünk egy alapvető példát. Az alábbiakban két számot osztunk. Ha nullával próbál osztani, akkor hibaüzenetet kap:
Ehelyett illessze be a számítást az IFERROR függvénybe, és ha elosztja nullával, akkor hiba helyett 0 -t ad ki:
1 | = HIBÁS (A2/B2,0) |
Ha hiba akkor üres
Ahelyett, hogy a hibákat 0 -ra állítaná, beállíthatja őket „üresre” dupla idézőjelekkel („”):
1 | = HIBÁS (A2/B2, "") |
További IFERROR -felhasználásokat fogunk vizsgálni a VLOOKUP funkcióval…
IFERROR a VLOOKUP segítségével
A keresési funkciók, például a VLOOKUP hibákat generálnak, ha a keresési érték nem található. Amint a fentiekben látható, az IFERROR függvény segítségével a hibákat üres helyekre („”) vagy 0 -kra cserélheti:
1 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "nem található") |
Ha hiba van, tegyen valami mást
Az IFERROR függvény akkor is használható második számítás elvégzésére, ha az 1. számítás hibát eredményez:
12 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE),VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE)) |
Itt, ha az adatok nem találhatók a „LookupTable1” -ben, akkor a VLOOKUP a „LookupTable2” -en történik.
További példák az IFERROR képletre
Beágyazott IFERROR - VLOOKUP Több lap
Egy IFERROR -t beágyazhat egy másik IFERROR -ba, és 3 külön számítást végezhet. Itt két IFERROR -t használunk a VLOOKUP -ok elvégzésére 3 külön munkalapon:
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 / Match & XLOOKUP
Természetesen az IFERROR az Index / Match és az XLOOKUP képletekkel is működik.
IFERROR XLOOKUP
Az XLOOKUP funkció a VLOOKUP funkció továbbfejlesztett változata.
1 | = IFERROR (XLOOKUP (A2, LookupTable1! $ A $ 2: $ A $ 4, LookupTable1! $ B $ 2: $ B $ 4), "Not Found") |
IFERROR INDEX / MATCH
Az INDEX és a MATCH segítségével hatékonyabb VLOOKUP -okat hozhat létre (hasonlóan az új XLOOKUP funkció működéséhez) az Excelben.
1 | = IFERROR (INDEX (LookupTable1! $ B $ 2: $ B $ 4, MATCH (A3, LookupTable1! $ A $ 2: $ A $ 4,0)), "Not Found") |
IFERROR a tömbökben
Az Excel tömbképleteit több számítás elvégzésére használják egyetlen képlet segítségével. Tegyük fel, hogy három oszlop van: Év, Értékesítés és Átlagár. A teljes mennyiséget az E oszlop alábbi képletével találhatja meg.
1 | {= SUM ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4)} |
A képlet jól teljesít mindaddig, amíg meg nem próbálja osztani nullával, így a #DIV/0! hiba.
Az IFERROR funkciót a következőképpen oldhatja meg:
1 | {= ÖSSZEG (HIBÁS ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4,0))} |
Vegye figyelembe, hogy az IFERROR függvényt be kell ágyazni a SUM függvénybe, ellenkező esetben az IFERROR az összesítésre vonatkozik, és nem a tömb minden egyes elemére.
IFNA vs IFERROR
Az IFNA függvény pontosan ugyanúgy működik, mint az IFERROR függvény, kivéve, ha az IFNA funkció csak a #N/A hibákat fogja fel. Ez rendkívül hasznos a keresési funkciókkal való munkavégzés során: a rendes képlethibák továbbra is észlelhetők, de nem jelenik meg hiba, ha a keresési érték nem található.
1 | = IFNA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "Not Found") |
Ha ISERROR
Ha még mindig a Microsoft Excel 2003 vagy régebbi verziót használja, akkor az IFERROR -t IF és ISERROR kombinációjával helyettesítheti. Íme egy rövid példa:
1 | = HA (ISERROR (A2/B2), 0, A2/B2) |
IFERROR a Google Táblázatokban
Az IFERROR funkció pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
IFERROR Példák a VBA -ban
A VBA nem rendelkezik beépített IFERROR Fucntion funkcióval, de az Excel IFERROR funkciót a VBA-n belül is elérheti:
12 | Dim n olyan hosszún = Application.WorksheetFunction.IfError (Érték, érték_ha_hiba) |
Application.WorksheetFunction hozzáférést biztosít számos (nem minden) Excel -funkcióhoz a VBA -ban.
Általában az IFERROR értéket használják a cellákból származó értékek leolvasásakor. Ha egy cella hibát tartalmaz, a VBA hibaüzenetet küldhet, amikor megpróbálja feldolgozni a cella értékét. Próbálja ki ezt az alábbi példakóddal (ahol a B2 cella hibát tartalmaz):
1234567891011 | IFERROR_VBA alrész ()Dim n olyan hosszú, m olyan hosszú'TÉNYn = Application.WorksheetFunction.IfError (Tartomány ("b2"). Érték, 0)- Nincs IFERRORm = Tartomány ("b2"). ÉrtékEnd Sub |
A kód hozzárendeli a B2 cellát egy változóhoz. A második változó -hozzárendelés hibát dob, mert a cella értéke #N/A, de az első jól működik az IFERROR függvény miatt.
A VBA segítségével az IFERROR függvényt tartalmazó képletet is létrehozhat:
1 | Tartomány ("C2"). KépletR1C1 = "= HIBÁS (RC [-2]/RC [-1], 0)" |
A hibakezelés a VBA -ban sokkal más, mint az Excelben. A VBA hibáinak kezelésére általában a VBA hibakezelést kell használni. A VBA hibakezelése így néz ki:
12345678910111213141516171819 | Rész tesztWS ()Az MsgBox létezikWSExist ("teszt")End SubA függvény WSExist (wsName As String) BooleanDim ws munkalapkéntHiba esetén Folytassa a következőtSet ws = Sheets (wsName)'Ha a WS hiba nem létezikHa Err.Number 0 AkkorDoesWSExist = HamisMásDoesWSExist = IgazVége HaGoTo -1 hiba eseténVége funkció |
Figyeljük meg, hogy használjuk Ha Err.Number 0 Akkor azonosítani, hogy történt -e hiba. Ez a tipikus módja a hibák észlelésének a VBA -ban. Az IFERROR függvénynek azonban van néhány felhasználása az Excel cellákkal való interakció során.