Acest tutorial arată cum să utilizați funcția Excel IFERROR pentru a prinde erori de formulă, înlocuindu-le cu o altă formulă, valoare necompletată, 0 sau un mesaj personalizat.
Prezentare generală a funcției IFERROR
Funcția IFERROR Verifică dacă o formulă are ca rezultat o eroare. Dacă este FALS, returnează rezultatul original al formulei. Dacă este TRUE, returnează o altă valoare specificată.
Sintaxa IFERROR
Pentru a utiliza funcția Foaie de lucru IFERROR Excel, selectați o celulă și tastați:= IFERROR (
Observați cum apar intrările de formulă IFERROR:
Sintaxa și intrările funcției IFERROR:
| 1 | = IFERROR (VALUE, value_if_error) |
valoare - O expresie. Exemplu: 4 / A1
value_if_error - Valoare sau Calcul pentru a efectua dacă intrarea anterioară are ca rezultat o eroare. Exemplul 0 sau „” (necompletat)
Care este funcția IFERROR?
Funcția IFERROR se încadrează în categoria Funcții logice în Microsoft Excel, care include ISNA, ISERROR și ISERR. Toate aceste funcții ajută la detectarea și gestionarea erorilor de formulă.
IFERROR vă permite să efectuați un calcul. Dacă calculul nu rezultă o eroare, apoi se afișează rezultatul calculului. Dacă calculul face rezultă o eroare, apoi se efectuează un alt calcul (sau o valoare statică, cum ar fi 0, necompletată sau este scos un text).
Când ați folosi funcția IFERROR?
- La împărțirea numerelor pentru a evita erorile cauzate de împărțirea la 0
- Când efectuați căutări pentru a preveni erorile dacă valoarea nu este găsită.
- Când doriți să efectuați un alt calcul dacă primul rezultă într-o eroare (de exemplu, Căutați o valoare într-un 2nd tabel dacă nu se găsește în primul tabel)
Erorile de formulă care nu sunt tratate pot provoca erori în registrul de lucru, dar erorile vizibile fac, de asemenea, foaia de calcul mai puțin vizibilă.
Dacă eroare, atunci 0
Să vedem un exemplu de bază. Mai jos împărțiți două numere. Dacă încercați să împărțiți la zero, veți primi o eroare:
În schimb, introduceți calculul în funcția IFERROR și, dacă împărțiți la zero, se va emite 0 în locul unei erori:
| 1 | = IFERROR (A2 / B2,0) |
Dacă Eroare, atunci Gol
În loc să setați erorile la 0, le puteți seta la „gol” cu ghilimele duble („”):
| 1 | = IFERROR (A2 / B2, "") |
Ne vom uita la mai multe utilizări IFERROR cu funcția VLOOKUP …
IFERROR cu VLOOKUP
Funcțiile de căutare precum VLOOKUP vor genera erori dacă valoarea de căutare nu este găsită. După cum se arată mai sus, puteți utiliza funcția IFERROR pentru a înlocui erorile cu spații goale („”) sau 0:
| 1 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), „nu a fost găsit”) |
Dacă eroare, atunci faceți altceva
Funcția IFERROR poate fi, de asemenea, utilizată pentru a efectua un al doilea calcul dacă primul calcul are ca rezultat o eroare:
| 12 | = IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE),VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALS)) |
Aici, dacă datele nu sunt găsite în „LookupTable1”, se efectuează un VLOOKUP pe „LookupTable2”.
Mai multe exemple de formule IFERROR
IFERROR imbricat - VLOOKUP foi multiple
Puteți cuibua un IFERROR într-un alt IFERROR pentru a efectua 3 calcule separate. Aici vom folosi două IFERROR-uri pentru a efectua VLOOKUP-uri pe 3 foi de lucru separate:
| 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
Desigur, IFERROR va funcționa și cu formule Index / Match și XLOOKUP.
IFERROR XLOOKUP
Funcția XLOOKUP este o versiune avansată a funcției VLOOKUP.
| 1 | = IFERROR (XLOOKUP (A2, LookupTable1! $ A $ 2: $ A $ 4, LookupTable1! $ B $ 2: $ B $ 4), „Nu a fost găsit”) |
INDICE / MATCH IFERROR
INDEX și MATCH pot fi utilizate pentru a crea VLOOKUP-uri mai puternice (similar cu modul în care funcționează noua funcție XLOOKUP) în Excel.
| 1 | = IFERROR (INDEX (LookupTable1! $ B $ 2: $ B $ 4, MATCH (A3, LookupTable1! $ A $ 2: $ A $ 4,0)), „Nu a fost găsit”) |
IFERROR în matrice
Formulele matrice în Excel sunt utilizate pentru a efectua mai multe calcule printr-o singură formulă. Să presupunem că există trei coloane de An, Vânzări și Preț mediu. Puteți afla cantitatea totală cu următoarea formulă în coloana E.
| 1 | {= SUM ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4)} |
Formula funcționează bine până când încearcă să se divizeze cu zero, rezultând # DIV / 0! eroare.
Puteți utiliza funcția IFERROR astfel pentru a rezolva eroarea:
| 1 | {= SUM (IFERROR ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4,0))} |
Observați că funcția IFERROR trebuie să fie imbricată în interiorul funcției SUM, altfel IFERROR se va aplica suma totală și nu fiecărui articol individual din matrice.
IFNA vs. IFERROR
Funcția IFNA funcționează exact la fel ca funcția IFERROR, cu excepția funcției IFNA care va prinde numai erori # N / A. Acest lucru este extrem de util atunci când se lucrează cu funcții de căutare: vor fi detectate în continuare erori regulate de formulă, dar nu va apărea nicio eroare dacă nu se găsește valoarea căutării.
| 1 | = IFNA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), „Nu a fost găsit”) |
Dacă ISERROR
Dacă utilizați în continuare Microsoft Excel 2003 sau o versiune mai veche, atunci puteți înlocui IFERROR cu o combinație de IF și ISERROR. Iată un scurt exemplu:
| 1 | = IF (ISERROR (A2 / B2), 0, A2 / B2) |
IFERROR în Foi de calcul Google
Funcția IFERROR funcționează exact la fel în Foi de calcul Google ca în Excel:
Exemple IFERROR în VBA
VBA nu are o funcție încorporată IFERROR, dar puteți accesa și funcția Excel IFERROR din VBA:
| 12 | Dim n cât timpn = Application.WorksheetFunction.IfError (Value, value_if_error) |
Application.WorksheetFunction vă oferă acces la multe funcții Excel (nu la toate) din VBA.
De obicei, IFERROR este utilizat la citirea valorilor din celule. Dacă o celulă conține o eroare, VBA poate afișa un mesaj de eroare atunci când încearcă să proceseze valoarea celulei. Încercați acest lucru cu exemplul de cod de mai jos (unde celula B2 conține o eroare):
| 1234567891011 | Sub IFERROR_VBA ()Dim n As Long, m As Long- IFERRORn = Application.WorksheetFunction.IfError (Range ("b2"). Valoare, 0)- Nu IFERRORm = Interval ("b2"). ValoareSfârșitul Sub |
Codul atribuie celula B2 unei variabile. A doua atribuire a variabilelor generează o eroare deoarece valoarea celulei este # N / A, dar prima funcționează bine datorită funcției IFERROR.
De asemenea, puteți utiliza VBA pentru a crea o formulă care conține funcția IFERROR:
| 1 | Interval ("C2"). FormulaR1C1 = "= IFERROR (RC [-2] / RC [-1], 0)" |
Tratarea erorilor în VBA este mult diferită decât în Excel. De obicei, pentru a gestiona erorile din VBA, veți utiliza VBA Error Handling. VBA Error Handling arată astfel:
| 12345678910111213141516171819 | Sub TestWS ()MsgBox DoesWSExist („test”)Sfârșitul SubFuncția DoesWSExist (wsName As String) ca BooleanDim ws Ca foaie de lucruLa eroare Reluați în continuareSet ws = Sheets (wsName)„Dacă eroarea WS nu existăDacă Err.Number 0 AtunciDoesWSExist = FalseAltfelDoesWSExist = AdevăratEnd IfLa eroare GoTo -1Funcția de sfârșit |
Observați că îl folosim Dacă Err.Number 0 Atunci pentru a identifica dacă a apărut o eroare. Acesta este un mod tipic de a prinde erori în VBA. Cu toate acestea, funcția IFERROR are unele utilizări atunci când interacționează cu celule Excel.
