Förhindra formelfel i Excel med IFERROR eller IFNA. Dessa är bättre än de gamla ISERROR ISERR och ISNA. Läs mer här.
Formelfel kan vara vanliga. Om du har en datauppsättning med hundratals poster, kommer en divide-by-zero eller ett # N / A att dyka upp då och då.
Tidigare krävde stora ansträngningar att förebygga fel. Nod ditt huvud medvetet om du någonsin har slagit ut =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Förutom att den är riktigt lång att skriva kräver den lösningen att Excel gör dubbelt så många VLOOKUP. Först gör du en VLOOKUP för att se om VLOOKUP kommer att producera ett fel. Då gör du samma VLOOKUP igen för att få resultatet som inte är fel.
Excel 2010 introducerade det kraftigt förbättrade = IFERROR (formel, värde om fel). Jag vet att IFERROR låter som de gamla gamla ISERROR-, ISERR-, ISNA-funktionerna, men det är helt annorlunda.
Detta är en lysande funktion: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Om du har 1 000 VLOOKUP och bara 5 returnerar # N / A, kräver 995 som fungerade bara en enda VLOOKUP. Det är bara de 5 som returnerade # N / A som behöver gå vidare till det andra argumentet i FEL.
Konstigt nog lade Excel 2013 till funktionen IFNA (). Det är precis som IFERROR men letar bara efter # N / A-fel. Man kan föreställa sig en konstig situation där värdet i uppslagstabellen finns, men det resulterande svaret är en uppdelning med 0. Om du av någon anledning vill bevara divider-by-zero-felet kommer IFNA () att göra detta.
Naturligtvis borde personen som byggde uppslagstabellen ha använt IFERROR för att förhindra uppdelningen med noll i första hand. I figuren nedan är "nm" en tidigare chefskod för "inte meningsfull."
Tack till Justin Fishman, Stephen Gilmer och Excel av Joe.
Kolla på video
- I gamla dagar skulle du använda
=IF(ISNA(Formula),0,Formula) - Börjar i Excel 2010,
=IFERROR(Formula,0) - Men IFERROR behandlar DIV / 0-fel samma var ett # N / A! fel
- Från och med Excel 2013 använder du bara för
=IFNA(Formula,0)att upptäcka # N / A-fel - Tack till Justin Fishman, Stephen Gilmer och Excel av Joe.
Videoutskrift
Lär dig Excel från podcast, avsnitt 2042 - FEL och IFNA!
Jag podcastar alla mina tips från den här boken, klicka på “i” i det övre högra hörnet för att komma till hela spellistan!
Okej, låt oss gå tillbaka till gamla dagar, Excel 2007 eller tidigare, om du behövde förhindra # N / A! från en VLOOKUP-formel som den här brukade vi göra den här galna saken. Skaffa alla karaktärer i VLOOKUP blanda ihop förutom =, Ctrl + C för att lägga den på klippbordet, = IF (ISNA (, tryck på Avsluta-tangenten för att komma till slutet, om det är # N / A !, då säg "Inte hittat", komma, annars gör vi VLOOKUP! Rätt, jag klistrar in den där och ser hur lång formeln är, Ctrl + Enter, lägg till a) precis där, Ctrl + Enter, okej se, det är sött. Okej, men problemet är, medan det löser problemet med # N / A! Varje enskild formel gör VOOKUP två gånger. Det vill inte att vi ska säga "Hej, är detta ett fel? Åh, nej, det är inte ett fel. Låt oss göra det igen! "Så det tar dubbelt så lång tid att göra alla dessa VLOOKUP. I Excel 2010,de ger oss den fantastiska, fantastiska formeln för FEL!
Nu vet jag att det låter som vad vi hade tidigare, ISERROR eller ISERR, men det här är IFERROR. Och hur det fungerar, ta vilken formel som helst som kan returnera ett fel, och sedan säger du = FEL, det finns formeln, komma, vad du ska göra om det är ett fel, så "Inte hittat". Okej nu, det vackra med det här är, låt oss säga att du hade tusen VLOOKUPs att göra, och 980 av dem fungerar. För 980 gör det bara VLOOKUP, det är inte ett fel, det returnerar svaret, det fortsätter aldrig med att göra den andra VLOOKUP, det är skönheten i FEL. Excels IFERROR kom i Excel 2010, men naturligtvis är det riktigt dumma problemet här att själva tabellen returnerar ett fel. Rätt, någon hade 0 kvantitet, intäkter / kvantitet, och så får vi en # DIV / 0! fel där, och det felet kommer också att upptäckas som ett fel av IFERROR. OK,och det kommer att se ut som om det inte finns, han hittas, det är bara att den som byggde det här bordet inte gjorde ett bra jobb med att bygga detta bord.
Okej så, val # 1, Excel 2013 eller nyare, byt till funktionen de lade till 2013 som inte letar efter alla fel, t letar bara efter # N / A! Ctrl + Enter, och nu får vi Not Found för ExcelIsFun, men returnerar # DIV / 0! fel. Men vad vi borde göra finns här i denna formel, vi borde hantera denna formel för att förhindra den delningen med noll i första hand. Så = FEL, detta fungerar för alla slags saker, tryck på Avsluta-tangenten för att komma till slutet, komma och vad ska man göra? Jag skulle alltid sätta noll här som genomsnittspriset.
Jag hade en chef en gång, Susanna (?), "Det är inte matematiskt korrekt, du måste sätta in" nm "för att inte vara meningsfullt." Exakt så är det galet hur länge min chef bara gjorde mig galen med deras galna förfrågan, sso, låt oss kopiera ner det, Klistra in specialformler, alt = "" ES F. Nu får vi ett "inte meningsfullt" fel här, " Not Found ”hittas av FEL, och det” inte meningsfulla ”är faktiskt resultatet av VLOOKUP. Okej, så ett par olika sätt att gå, förmodligen är det säkraste att göra här att använda IFNA, förutsatt att du har Excel 2013, och alla du delar din arbetsbok med har Excel 2013. Den här boken, plus en hel massa andra finns i denna bok, är droppande med kryddiga tips, 200 sidor, lätt att läsa, fullfärg fantastisk, fantastisk bok. Kolla in det, klicka på "I" i det övre högra hörnet,du kan köpa boken.
Okej, avsnitt sammanfattning: Tillbaka i gamla dagar skulle vi använda ett långt format = IF (ISNA (formeln, 0, annars formeln, formeln beräknades två gånger, vilket är hemskt för VLOOKUPs. Börjar i Excel 2010, = FEL , sätt bara formeln en gång, komma, vad ska jag göra om det är ett fel. IFERROR behandlar dock # DIV / 0! -fel på samma sätt som # N / A! -fel, så från och med Excel 2013 fungerar IFNA-funktionen precis som IFERROR, men det kommer bara att upptäcka # N / A! -felen.
Detta tips förresten, föreslagit av läsarna Justin Fishman, Stephen Gilmer och Excel av Joe. Tack till dem för att föreslå detta, och tack till er för att ni kom förbi, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2042.xlsm
