Prevenirea erorilor de formulă în Excel folosind IFERROR sau IFNA. Acestea sunt mai bune decât vechile ISERROR ISERR și ISNA. Aflați mai multe aici.
Erorile de formulă pot fi frecvente. Dacă aveți un set de date cu sute de înregistrări, o divizare la zero sau un # N / A trebuie să apară din când în când.
În trecut, prevenirea erorilor presupunea eforturi herculene. Încuviințează-ți capul cu bună știință dacă ai eliminat vreodată =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Pe lângă faptul că este foarte lungă pentru a tasta, acea soluție necesită ca Excel să facă de două ori mai multe VLOOKUP-uri. Mai întâi, faceți o VLOOKUP pentru a vedea dacă VLOOKUP va produce o eroare. Apoi faceți același VLOOKUP din nou pentru a obține rezultatul fără eroare.
Excel 2010 a introdus mult îmbunătățit = IFERROR (Formula, Value If Error). Știu că IFERROR sună ca vechile funcții vechi ISERROR, ISERR, ISNA, dar este complet diferit.
Aceasta este o funcție genial: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Dacă aveți 1.000 de VLOOKUP-uri și doar 5 returnează # N / A, atunci 995 care au funcționat necesită doar un singur VLOOKUP. Numai cei 5 care au returnat # N / A trebuie să treacă la al doilea argument al IFERROR.
În mod ciudat, Excel 2013 a adăugat funcția IFNA (). Este la fel ca IFERROR, dar caută doar erori # N / A. S-ar putea imagina o situație ciudată în care se găsește valoarea din tabelul de căutare, dar răspunsul rezultat este o împărțire la 0. Dacă ați dorit să păstrați eroarea de divizare la zero din anumite motive, atunci IFNA () va face acest lucru.
Desigur, persoana care a construit tabelul de căutare ar fi trebuit să utilizeze IFERROR pentru a preveni divizarea la zero în primul rând. În figura de mai jos, „nm” este un cod al unui fost manager pentru „nu are sens”.
Mulțumesc lui Justin Fishman, Stephen Gilmer și Excel de Joe.
Urmăriți videoclipul
- Pe vremuri, ai folosi
=IF(ISNA(Formula),0,Formula) - Începând cu Excel 2010,
=IFERROR(Formula,0) - Dar IFERROR tratează erorile DIV / 0 la fel a fost un # N / A! erori
- Începând cu Excel 2013, utilizați butonul
=IFNA(Formula,0)pentru a detecta numai # erori N / A - Mulțumesc lui Justin Fishman, Stephen Gilmer și Excel de Joe.
Transcriere video
Aflați Excel din podcast, episodul 2042 - IFERROR și IFNA!
Voi fi în podcast toate sfaturile mele din această carte, dați clic pe „i” în colțul din dreapta sus pentru a ajunge la întregul playlist!
Bine, să ne întoarcem la vechile vremuri, Excel 2007 sau înainte, dacă ați avut nevoie să preveniți # N / A! dintr-o formulă VLOOKUP precum aceasta, obișnuiam să facem acest lucru nebunesc. Scoateți toate caracterele amestecului VLOOKUP, cu excepția =, Ctrl + C pentru ao pune pe clipboard, = IF (ISNA (, apăsați tasta End pentru a ajunge la final, dacă este # N / A !, atunci noi spune „Nu s-a găsit”, virgulă, altfel facem VLOOKUP! Așa, o lipesc acolo și uite cât de lungă este acea formulă, Ctrl + Enter, adaugă a) chiar acolo, Ctrl + Enter, bine uite, asta e dulce. Bine, dar problema este, deși rezolvă problema # N / A! Fiecare formulă face VOOKUP de două ori. Nu vrea să spunem „Hei, este o eroare? Oh, nu, nu este o eroare. Hai să o facem din nou! ”Deci durează de două ori mai mult pentru a face toate aceste VLOOKUP-uri. În Excel 2010,ne oferă formula minunată, minunată a IFERROR!
Acum știu că sună ca ceea ce aveam înainte, ISERROR sau ISERR, dar acesta este IFERROR. Și modul în care funcționează, luați orice formulă care ar putea returna o eroare și apoi spuneți = IFERROR, există formula, virgulă, ce trebuie să faceți dacă este o eroare, deci „Nu a fost găsit”. Bine acum, un lucru frumos despre asta este, să presupunem că ai avut o mie de VLOOKUP-uri de făcut, iar 980 dintre ele funcționează. Pentru 980 face doar VLOOKUP, nu este o eroare, returnează răspunsul, nu continuă niciodată să facă al doilea VLOOKUP, asta e frumusețea despre IFERROR. IFERROR-ul Excel a apărut în Excel 2010, dar, desigur, singura problemă cu adevărat stupidă este că tabelul în sine returnează o eroare. Corect, cineva avea 0 cantitate, venituri / cantități, așa că primim un # DIV / 0! eroare acolo și această eroare va fi, de asemenea, detectată ca o eroare de IFERROR. Bine,și va arăta că nu este găsit, el este găsit, doar că oricine a construit această masă nu a făcut o treabă bună construind această masă.
În regulă, alegerea # 1, Excel 2013 sau o versiune mai nouă, treceți la funcția pe care au adăugat-o în 2013, care nu caută toate erorile, nu caută decât # N / A! Ctrl + Enter și acum vom obține Not Found for ExcelIsFun, dar returnează # DIV / 0! eroare. Cu toate acestea, ceea ce ar trebui să facem, este aici, în această formulă, ar trebui să gestionăm această formulă pentru a preveni divizarea la zero în primul rând. Deci = IFERROR, acest lucru funcționează pentru tot felul de lucruri, apăsați tasta End pentru a ajunge la final, virgulă, și apoi ce să faceți? Aș pune întotdeauna un zero aici ca preț mediu.
Am avut o dată manager, Susanna (?), „Nu este corect din punct de vedere matematic, trebuie să introduceți„ nm ”pentru că nu este semnificativ.” Exact așa, este o nebunie cât de mult timp managerul meu m-a înnebunit cu cererea lor nebună, așa că hai să copiem asta, Lipiți formule speciale, alt = "" ES F. Acum primim o eroare „fără sens” aici, „ Nu este găsit ”este găsit de IFERROR, iar„ lipsit de sens ”este de fapt rezultatul VLOOKUP. Bine, deci câteva modalități diferite de urmat, probabil cel mai sigur lucru pe care îl puteți face aici este să utilizați IFNA, cu condiția să aveți Excel 2013 și toți cei cu care vă partajați registrul de lucru să aibă Excel 2013. Această carte, plus o grămadă de altele sunt în această carte, picură cu sfaturi picante, 200 de pagini, ușor de citit, culoare întreagă minunată, carte minunată. Verificați-l, dați clic pe „Eu” din colțul din dreapta sus,poți cumpăra cartea.
Bine, recapitulare episod: În vremurile vechi foloseam un format lung = IF (ISNA (formula, 0, altfel formula, formula a fost calculată de două ori, ceea ce este oribil pentru VLOOKUP-uri. Începând din Excel 2010, = IFERROR , puneți formula o singură dată, virgulă, ce să faceți dacă este o eroare. IFERROR tratează totuși erorile # DIV / 0! la fel ca erorile # N / A!, deci începând cu Excel 2013 funcția IFNA funcționează exact ca IFERROR, dar va detecta numai erorile # N / A !.
Apropo, acest sfat, sugerat de cititorii Justin Fishman, Stephen Gilmer și Excel de Joe. Vă mulțumim pentru că ați sugerat acest lucru și vă mulțumim că ați trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2042.xlsm
