Prevenzione degli errori di formula in Excel utilizzando SE.ERRORE o IFNA. Questi sono migliori dei vecchi ISERROR ISERR e ISNA. Scopri di più qui.
Gli errori di formula possono essere comuni. Se disponi di un set di dati con centinaia di record, di tanto in tanto apparirà una divisione per zero o un # N / A.
In passato, prevenire gli errori richiedeva sforzi erculei. Annuisci consapevolmente con la testa se hai mai messo KO =IF(ISNA(VLOOKUP(A2,Table,2,0),"Not Found",VLOOKUP(A2,Table,2,0)). Oltre ad essere molto lunga da digitare, quella soluzione richiede che Excel esegua il doppio dei CERCA.VERT. Innanzitutto, esegui un CERCA.VERT per vedere se CERCA.VERT produrrà un errore. Quindi fai di nuovo lo stesso CERCA.VERT per ottenere il risultato non di errore.
Excel 2010 ha introdotto il notevolmente migliorato = SE.ERRORE (formula, valore se errore). So che IFERROR suona come le vecchie funzioni ISERROR, ISERR, ISNA, ma è completamente diverso.
Questa è una funzione geniale: =IFERROR(VLOOKUP(A2,Table,2,0),"Not Found"). Se si dispone di 1.000 CERCA.VERT e solo 5 restituiscono # N / D, i 995 che hanno funzionato richiedono solo un singolo CERCA.VERT. È solo il 5 che ha restituito # N / A che deve passare al secondo argomento di SE.ERRORE.
Stranamente, Excel 2013 ha aggiunto la funzione IFNA (). È proprio come IFERROR ma cerca solo errori # N / A. Si potrebbe immaginare una strana situazione in cui viene trovato il valore nella tabella di ricerca, ma la risposta risultante è una divisione per 0. Se si desidera mantenere l'errore di divisione per zero per qualche motivo, IFNA () lo farà.
Ovviamente, la persona che ha creato la tabella di ricerca avrebbe dovuto utilizzare IFERROR per impedire la divisione per zero in primo luogo. Nella figura seguente, "nm" è il codice di un ex manager per "non significativo".
Grazie a Justin Fishman, Stephen Gilmer e Excel di Joe.
Guarda un video
- Ai vecchi tempi, useresti
=IF(ISNA(Formula),0,Formula) - A partire da Excel 2010,
=IFERROR(Formula,0) - Ma IFERROR tratta gli errori DIV / 0 allo stesso modo di un # N / A! errori
- A partire da Excel 2013, utilizzare
=IFNA(Formula,0)per rilevare solo errori # N / D - Grazie a Justin Fishman, Stephen Gilmer e Excel di Joe.
Trascrizione del video
Impara Excel dal podcast, episodio 2042 - IFERROR e IFNA!
Trasmetterò in podcast tutti i miei suggerimenti da questo libro, fai clic sulla "i" nell'angolo in alto a destra per accedere all'intera playlist!
Va bene, torniamo ai vecchi tempi, Excel 2007 o prima, se avevi bisogno di prevenire il # N / A! da una formula CERCA.VERT come questa, facevamo questa cosa folle. Raccogli tutti i caratteri di CERCA.VERT eccetto =, Ctrl + C per metterlo negli appunti, = SE (ISNA (, premi il tasto Fine per arrivare alla fine, se è # N / A !, quindi noi dì "Non trovato", virgola, altrimenti facciamo il CERCA.VERT! Bene, lo incollo lì e guarda quanto è lunga quella formula, Ctrl + Invio, aggiungi a) proprio lì, Ctrl + Invio, va bene guarda, è carino. Va bene, ma il problema è che, sebbene risolva il problema del # N / A! Ogni singola formula esegue il VOOKUP due volte. Non vuole che diciamo "Ehi, è un errore? Oh, no, non lo è un errore. Facciamolo di nuovo! "Quindi è necessario il doppio del tempo per eseguire tutti questi CERCA.VERT. In Excel 2010,ci danno la fantastica, fantastica formula di IFERROR!
Ora so che suona come quello che avevamo prima, ISERROR o ISERR, ma questo è IFERROR. E il modo in cui funziona, prendi qualsiasi formula che potrebbe restituire un errore, e poi dici = SE.ERRORE, c'è la formula, virgola, cosa fare se si tratta di un errore, quindi "Non trovato". Va bene ora, la cosa bella di questo è, diciamo che hai mille CERCA.VERT da fare e 980 di loro funzionano. Per il 980 fa solo il CERCA.VERT, non è un errore, restituisce la risposta, non va mai avanti con il secondo CERCA.VERT, questo è il bello di SE.ERRORE. L'IFERRORE di Excel è arrivato in Excel 2010, ma ovviamente l'unico problema davvero stupido qui è che la tabella stessa restituisce un errore. Giusto, qualcuno aveva 0 quantità, Entrate / Quantità, quindi stiamo ottenendo un # DIV / 0! errore lì, e quell'errore verrà rilevato come errore anche da IFERROR. Tutto a posto,e sembrerà che non è stato trovato, è stato trovato, è solo che chiunque abbia costruito questo tavolo non ha fatto un buon lavoro a costruire questo tavolo.
Va bene, quindi, scelta # 1, Excel 2013 o più recente, passa alla funzione aggiunta nel 2013 che non cerca tutti gli errori, t cerca solo # N / A! Ctrl + Invio, e ora otterremo Not Found per ExcelIsFun, ma restituisce # DIV / 0! errore. In realtà, però, ciò che dovremmo fare è qui fuori in questa formula, dovremmo gestire questa formula per evitare quella divisione per zero in primo luogo. Quindi = SE.ERRORE, funziona per tutti i tipi di cose, premere il tasto Fine per arrivare alla fine, virgola e poi cosa fare? Metterei sempre uno zero qui come prezzo medio.
Una volta ho avuto un manager, Susanna (?), "Non è matematicamente corretto, devi mettere" nm "per non significativo." Esattamente così, è pazzesco per quanto tempo il mio manager mi abbia fatto impazzire con la loro folle richiesta, quindi, copiamolo, Paste Special Formulas, alt = "" ES F. Ora otteniamo un errore "non significativo" qui, il " Non trovato "viene trovato da SE.ERRORE e" non significativo "è in realtà il risultato di CERCA.VERT. Va bene, quindi un paio di modi diversi per andare, probabilmente la cosa sicura da fare qui è usare IFNA, a condizione che tu abbia Excel 2013 e tutti quelli con cui condividi la tua cartella di lavoro abbiano Excel 2013. Questo libro, più un intero gruppo di altri sono in questo libro, è gocciolante di punte piccanti, 200 pagine, facile da leggere, a colori fantastico, fantastico libro. Controlla, fai clic sulla "I" nell'angolo in alto a destra,puoi comprare il libro.
Va bene, riepilogo dell'episodio: ai vecchi tempi usavamo un formato lungo = SE (ISNA (la formula, 0, altrimenti la formula, la formula era calcolata due volte, il che è orribile per CERCA.VERT. A partire da Excel 2010, = SE.ERRORE , inserisci la formula una volta, virgola, cosa fare se si tratta di un errore. SE.ERRORE, tuttavia, tratta gli errori # DIV / 0! come gli errori # N / A!, quindi a partire da Excel 2013 la funzione IFNA funziona proprio come SE.ERRORE, ma rileverà solo gli errori # N / A!.
Questo suggerimento, tra l'altro, suggerito dai lettori Justin Fishman, Stephen Gilmer e Excel di Joe. Grazie a loro per aver suggerito questo, e grazie a te per essere passato, ci vediamo la prossima volta per un altro netcast da!
Download file
Scarica il file di esempio qui: Podcast2042.xlsm
