Când lucrați cu date și formule în Excel, veți întâlni erori.
Pentru a gestiona erorile, Excel a furnizat o funcție utilă - funcția IFERROR.
Înainte de a intra în mecanica utilizării funcției IFERROR în Excel, să trecem mai întâi la diferitele tipuri de erori pe care le puteți întâlni atunci când lucrați cu formule.
Tipuri de erori în Excel
Cunoașterea erorilor din Excel vă va ajuta să identificați motivul posibil și cel mai bun mod de a le rezolva.
Mai jos sunt tipurile de erori pe care le-ați putea găsi în Excel.
# N / A Eroare
Aceasta se numește eroare „Value Not Available”.
Veți vedea acest lucru când utilizați o formulă de căutare și nu poate găsi valoarea (deci Nu este disponibil).
Mai jos este un exemplu în care folosesc formula VLOOKUP pentru a găsi prețul unui articol, dar returnează o eroare când nu poate găsi acel articol în tabelul de tabele.
# DIV / 0! Eroare
Este posibil să vedeți această eroare atunci când un număr este împărțit la 0.
Aceasta se numește eroare de divizare. În exemplul de mai jos, dă un # DIV / 0! eroare deoarece valoarea cantității (divizorul din formulă) este 0.
#VALOARE! Eroare
Eroarea de valoare apare atunci când utilizați un tip de date incorect într-o formulă.
De exemplu, în exemplul de mai jos, când încerc să adaug celule care au numere și caracterul A, dă eroarea de valoare.
Acest lucru se întâmplă deoarece puteți adăuga doar valori numerice, dar, în schimb, am încercat să adaug un număr cu un caracter text.
#REF! Eroare
Aceasta se numește eroare de referință și o veți vedea atunci când referința din formulă nu mai este valabilă. Acesta ar putea fi cazul atunci când formula se referă la o referință de celulă și această referință de celulă nu există (se întâmplă atunci când ștergeți un rând / coloană sau foaie de lucru la care s-a făcut referire în formulă).
În exemplul de mai jos, în timp ce formula originală era = A2 / B2, când am șters coloana B, toate referințele la aceasta au devenit #REF! și a dat și #REF! eroare ca rezultat al formulei.
EROARE #NAME
Această eroare este probabil ca urmare a unei funcții greșite de ortografie.
De exemplu, dacă în loc de VLOOKUP, utilizați din greșeală VLOKUP, acesta va da o eroare de nume.
EROARE #NUM
Eroarea Num poate apărea dacă încercați să calculați o valoare foarte mare în Excel. De exemplu, = 187 549 va returna o eroare de număr.
O altă situație în care puteți obține eroarea NUM este atunci când dați un argument non-valid unei formule. De exemplu, dacă calculați rădăcina pătrată dacă este un număr și dați un argument negativ ca argument, acesta va returna o eroare de număr.
De exemplu, în cazul funcției Rădăcină pătrată, dacă dați un argument negativ ca argument, acesta va returna o eroare de număr (așa cum se arată mai jos).
Deși am arătat doar câteva exemple aici, pot exista multe alte motive care pot duce la erori în Excel. Când primiți erori în Excel, nu îl puteți lăsa acolo. Dacă datele sunt utilizate în continuare în calcule, trebuie să vă asigurați că erorile sunt tratate în mod corect.
Funcția Excel IFERROR este o modalitate excelentă de a gestiona toate tipurile de erori în Excel.
Funcția Excel IFERROR - o prezentare generală
Folosind funcția IFERROR, puteți specifica ce doriți să returneze formula în loc de eroare. Dacă formula nu returnează o eroare, atunci propriul rezultat este returnat.
Sintaxa funcției IFERROR
= IFERROR (valoare, valoare_if_error)
Argumente de intrare
- valoare - acesta este argumentul verificat pentru eroare. În majoritatea cazurilor, este fie o formulă, fie o referință de celulă.
- value_if_error - aceasta este valoarea returnată dacă există o eroare. Următoarele tipuri de erori evaluate: # N / A, #REF !, # DIV / 0 !, #VALUE !, #NUM !, #NAME ?, și #NULL !.
Note Aditionale:
- Dacă utilizați „” ca argument value_if_error, celula nu afișează nimic în cazul unei erori.
- Dacă argumentul value sau value_if_error se referă la o celulă goală, acesta este tratat ca o valoare șir gol de funcția Excel IFERROR.
- Dacă argumentul valoare este o formulă matrice, IFERROR va returna o matrice de rezultate pentru fiecare element din intervalul specificat în valoare.
Funcția Excel IFERROR - Exemple
Iată trei exemple de utilizare a funcției IFERROR în Excel.
Exemplul 1 - Returnează celula goală în loc de eroare
Dacă aveți funcții care pot returna o eroare, o puteți înfășura în funcția IFERROR și puteți specifica gol ca valoare de returnat în caz de eroare.
În exemplul prezentat mai jos, rezultatul în D4 este # DIV / 0! eroare deoarece divizorul este 0.
În acest caz, puteți utiliza următoarea formulă pentru a reveni necompletat în locul erorii DIV urâte.
= IFERROR (A1 / A2, ””)
Această funcție IFERROR ar verifica dacă calculul duce la o eroare. În caz contrar, returnează pur și simplu un gol așa cum se specifică în formulă.
Aici puteți specifica, de asemenea, orice alt șir sau formulă de afișat în loc de gol.
De exemplu, formula de mai jos ar returna textul „Eroare”, în locul celulei goale.
= IFERROR (A1 / A2, „Eroare”)
Notă: Dacă utilizați Excel 2003 sau o versiune anterioară, nu veți găsi funcția IFERROR în ea. În astfel de cazuri, trebuie să utilizați combinația dintre funcția IF și funcția ISERROR.
Exemplul 2 - Returnează „Nu a fost găsit” când VLOOKUP nu poate găsi o valoare
Când utilizați funcția Excel VLOOKUP și nu poate găsi valoarea de căutare în intervalul specificat, va returna eroarea # N / A.
De exemplu, mai jos este un set de date cu numele studenților și notele acestora. Am folosit funcția VLOOKUP pentru a obține notele a trei studenți (în D2, D3 și D4).
În timp ce formula VLOOKUP din exemplul de mai sus găsește numele primilor doi studenți, nu poate găsi numele lui Josh pe listă și, prin urmare, returnează eroarea # N / A.
Aici putem folosi funcția IFERROR pentru a returna un text necompletat sau un text semnificativ în locul erorii.
Mai jos este formula care va returna „Not Found” în loc de eroare.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 12,2,0), „Nu a fost găsit”)
Rețineți că puteți utiliza și IFNA în loc de IFERROR cu VLOOKUP. În timp ce IFERROR ar trata toate tipurile de valori ale erorilor, IFNA ar funcționa numai pe erorile # N / A și nu ar funcționa cu alte erori.
Exemplul 3 - Returnează 0 în caz de eroare
Dacă nu specificați valoarea de returnat de IFERROR în cazul unei erori, aceasta va returna automat 0.
De exemplu, dacă împart 100 cu 0 așa cum se arată mai jos, ar întoarce o eroare.
Cu toate acestea, dacă folosesc funcția IFERROR de mai jos, ar întoarce un 0 în schimb. Rețineți că trebuie să utilizați în continuare o virgulă după primul argument.
Exemplul 4 - Utilizarea IFERROR imbricat cu VLOOKUP
Uneori, când utilizați VLOOKUP, poate fi necesar să vă uitați prin tabelul fragmentat al matricelor. De exemplu, să presupunem că aveți înregistrările tranzacțiilor de vânzare în 2 foi de lucru separate și doriți să căutați un număr de articol și să vedeți valoarea acestuia.
Pentru a face acest lucru, trebuie să utilizați IFERROR imbricat cu VLOOKUP.
Să presupunem că aveți un set de date așa cum se arată mai jos:
În acest caz, pentru a găsi scorul pentru Grace, trebuie să utilizați formula IFERROR imbricată mai jos:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), „Nu a fost găsit”))
Acest tip de imbricare a formulelor vă asigură că obțineți valoarea din oricare dintre tabele și orice eroare returnată este tratată.
Rețineți că, în cazul în care tabelele sunt pe aceeași foaie de lucru, totuși, într-un exemplu din viața reală, este probabil să apară pe foi de lucru diferite.
Funcția Excel IFERROR - VIDEO
- Excel ȘI Funcție.
- Funcția Excel SAU.
- Funcția Excel NU.
- Funcția Excel IF.
- Funcția Excel IFS.
- Funcția FALSĂ Excel.
- Funcția Excel TRUE.
