Ta vadnica prikazuje, kako uporabite funkcijo Excel IFERROR za odkrivanje napak v formuli in jih nadomestite z drugo formulo, prazno vrednostjo, 0 ali sporočilom po meri.
Pregled funkcije IFERROR
Funkcija IFERROR preveri, ali formula povzroči napako. Če je FALSE, vrnite izvirni rezultat formule. Če je TRUE, vrnite drugo podano vrednost.
Če želite uporabiti funkcijo delovnega lista Excel IFERROR, izberite celico in vnesite:
(Upoštevajte, kako so prikazani vnosi formule)
Sintaksa in vnosi funkcij IFERROR:
= IFERROR (VALUE, value_if_error)
vrednost - Izraz. Primer: 4/A1
value_if_error - Vrednost ali izračun, če prejšnji vnos povzroči napako. Primer 0 ali »« (prazno)
Kaj je funkcija IFERROR?
Funkcija IFERROR spada v kategorijo logičnih funkcij v programu Microsoft Excel, ki vključuje ISNA, ISERROR in ISERR. Vse te funkcije pomagajo pri odkrivanju in odpravljanju napak v formuli.
IFERROR vam omogoča izračun. Če izračun ne povzroči napako, se prikaže rezultat izračuna. Če izračun naredi povzroči napako, potem se izvede še en izračun (ali pa se prikaže statična vrednost, kot je 0, prazno ali nekaj besedila).
Kdaj bi uporabili funkcijo IFERROR?
- Pri deljenju števil se izognete napakam, ki jih povzroči deljenje z 0
- Pri iskanju preprečite napake, če vrednosti ne najdete.
- Ko želite izvesti nov izračun, če prvi povzroči napako (npr. Poiščite vrednost v 2nd tabela, če je ne najdete v prvi tabeli)
Napake v neobdelani formuli lahko povzročijo napake v vašem delovnem zvezku, vendar zaradi vidnih napak vaša preglednica postane manj vidno privlačna.
Če je napaka, potem 0
Poglejmo osnovni primer. Spodaj delite dve številki. Če poskusite deliti z ničlo, boste prejeli napako:
Namesto tega vnesite izračun v funkcijo IFERROR in če delite z ničlo, se namesto napake prikaže 0:
= NAPAKA (A2/B2,0)
Če je napaka, potem je prazno
Namesto da bi napake nastavili na 0, jih lahko nastavite na 'prazno' z dvojnimi narekovaji (»«):
= NAPAKA (A2/B2, "")
Preučili bomo več uporab IFERROR s funkcijo VLOOKUP …
NAPAKA z VLOOKUP -om
Iskalne funkcije, kot je VLOOKUP, bodo ustvarile napake, če iskalne vrednosti ne najdete. Kot je prikazano zgoraj, lahko s funkcijo IFERROR zamenjate napake s praznimi ("") ali 0:
= NAPAKA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "ni najdeno")
Če pride do napake, potem naredite kaj drugega
Funkcijo IFERROR lahko uporabite tudi za izvedbo drugega izračuna, če je pri prvem izračunu prišlo do napake:
= IFERROR (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), VLOOKUP (A2, LookupTable2! $ A $ 2: $ B $ 4,2, FALSE))
Če podatkov ni v "LookupTable1", se namesto tega izvede "VLOOKUP" v "LookupTable2".
Več primerov formule IFERROR
Vgnezdena IFERROR - VLOOKUP Več listov
IFERROR lahko ugnezdite v drugo IFERROR, da izvedete 3 ločene izračune. Tukaj bomo uporabili dve napaki IFERROR za izvajanje VLOOKUP -ov na 3 ločenih delovnih listih:
= 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
Seveda bo IFERROR deloval tudi s formulami Index / Match in XLOOKUP.
IFERROR XLOOKUP
Funkcija XLOOKUP je napredna različica funkcije VLOOKUP.
= IFERROR (XLOOKUP (A2, LookupTable1! $ A $ 2: $ A $ 4, LookupTable1! $ B $ 2: $ B $ 4), "Ni najdeno")
INDEKS IFEROROR / MATCH
INDEX in MATCH lahko uporabite za ustvarjanje zmogljivejših VLOOKUP -ov (podobno kot deluje nova funkcija XLOOKUP) v Excelu.
= IFERROR (INDEX (LookupTable1! $ B $ 2: $ B $ 4, MATCH (A3, LookupTable1! $ A $ 2: $ A $ 4,0)), "Ni najdeno")
IFERROR v nizih
Formule nizov v Excelu se uporabljajo za izvajanje več izračunov z eno formulo. Predpostavimo, da obstajajo tri stolpce Leto, Prodaja in Povprečna cena. Skupno količino lahko ugotovite po naslednji formuli v stolpcu E.
{= SUM ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4)}
Formula deluje dobro, dokler se ne poskuša ločiti od nič, kar ima za posledico #DIV/0! napaka.
Za odpravo napake lahko uporabite funkcijo IFERROR:
{= SUM (IFERROR ($ B $ 2: $ B $ 4/$ C $ 2: $ C $ 4,0))}
Upoštevajte, da mora biti funkcija IFERROR ugnezdena v funkciji SUM, sicer bo IFERROR veljal za vsoto vsote in ne za vsako posamezno postavko v matriki.
IFNA proti IFERROR
Funkcija IFNA deluje popolnoma enako kot funkcija IFERROR, le da funkcija IFNA lovi samo #N/A napake. To je izredno uporabno pri delu s funkcijami iskanja: običajne napake v formuli bodo še vedno zaznane, vendar napaka ne bo prikazana, če iskalne vrednosti ni mogoče najti.
= IFNA (VLOOKUP (A2, LookupTable1! $ A $ 2: $ B $ 4,2, FALSE), "Ni najdeno")
Če ISERROR
Če še vedno uporabljate Microsoft Excel 2003 ali starejšo različico, lahko IFERROR zamenjate s kombinacijo IF in ISERROR. Tu je kratek primer:
= IF (ISERROR (A2/B2), 0, A2/B2)
IFERROR v Google Preglednicah
Funkcija IFERROR deluje v Google Preglednicah popolnoma enako kot v Excelu:
Primeri IFERROR v VBA
VBA nima vgrajene funkcije IFERROR Fucntion, lahko pa dostopate tudi do funkcije Excel IFERROR iz VBA:
Zatemni n tako dolgo, kot je n = Application.WorksheetFunction.IfError (Value, value_if_error)
Application.WunksheetFunction vam omogoča dostop do številnih (ne vseh) Excelovih funkcij v VBA.
Običajno se IFERROR uporablja pri branju vrednosti iz celic. Če celica vsebuje napako, lahko VBA pri poskusu obdelave vrednosti celice prikaže sporočilo o napaki. Poskusite to s spodnjo kodo primera (kjer celica B2 vsebuje napako):
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
Koda dodeljuje celico B2 spremenljivki. Druga dodelitev spremenljivke povzroči napako, ker je vrednost celice #N/A, vendar prva deluje dobro zaradi funkcije IFERROR.
Z VBA lahko ustvarite tudi formulo, ki vsebuje funkcijo IFERROR:
Razpon ("C2"). Formula R1C1 = "= NAPAKA (RC [-2]/RC [-1], 0)"
Ravnanje z napakami v VBA je precej drugačno kot v Excelu. Običajno za odpravljanje napak v VBA uporabite VBA Error Handling. Obdelava napak VBA izgleda tako:
Sub TestWS () MsgBox AliWSExist ("test") End Sub Funkcija AliWSExist (wsName As String) Kot Boolean Dim ws Kot delovni list o napaki Nadaljuj Naslednji niz ws = Sheets (wsName) 'Če napaka WS ne obstaja Če številka napake 0 potem DoesWSExist = False Else DoesWSExist = True End If If On Error GoTo -1 End Funkcija
Opomba, ki jo uporabljamo Če Err.Številka 0 Potem ugotoviti, ali je prišlo do napake. To je tipičen način odkrivanja napak v VBA. Vendar pa ima funkcija IFERROR nekaj koristi pri interakciji z celicami Excel.
