Errori formula in Excel e relative soluzioni
“Gli errori sono opportunità di miglioramento”. In qualsiasi attività o sistema, si verificano errori. Excel non fa eccezione. Durante il tentativo di fare qualcosa con una formula, incontrerai vari tipi di errori di Excel. Questi errori possono rendere la tua formula / dashboard / report uno spreco assoluto. E se non sai perché si è verificato un tipo specifico di errore, potresti dover passare ore per risolverli.
Mentre lavoravo su Excel, ho riscontrato molti errori di Excel. Con un po ‘di fatica e ricerche su Google, ho aggirato questi errori. In questo articolo, spiegherò alcuni errori Excel comuni e fastidiosi che si verificano in Excel. Discuteremo perché si verificano questi errori e come risolverli.
Cosa sono gli errori delle formule di Excel Durante l’applicazione di una formula che risulta in un errore definito in Excel (#NA, #VALORE, #NOME ecc.) Vengono chiamati errori di formula di Excel. Questi errori vengono rilevati da Excel e stampati sui fogli. Le ragioni di questi errori possono essere, valori non disponibili, tipo errato di argomenti, divisione per 0 ecc.
Sono facili da catturare e riparare.
Gli errori logici non vengono rilevati da Excel e sono molto difficili da correggere. Incoerenza nei dati, immissione errata dei dati, errori umani ecc. Sono le ragioni comuni alla base di questi errori. Possono anche essere riparati ma richiedono tempo e fatica. È meglio preparare perfettamente i dati prima di eseguire operazioni su di essi.
Recupero degli errori di formula di Excel:
Ci sono alcune funzioni dedicate in Excel per rilevare e gestire tipi specifici di errori (come la funzione ISNA). Ma link: / excel-formula-and-function-iserror-function [ISERROR function] e link: / logical-formas-excel-iferror-function [IFERROR function] sono due funzioni che possono catturare qualsiasi tipo di Excel Errore (eccetto logico).
Excel #NA Error #N’errore si verifica in Excel quando non viene trovato un valore. Significa semplicemente NON DISPONIBILE. L’errore #NA si verifica spesso con la funzione CERCA.VERT di Excel.
Nell’immagine sopra, stiamo ottenendo l’errore #NA quando cerchiamo “Divya” nella colonna A. Questo perché “Divya non è nell’elenco.
Risoluzione dell’errore #NA:
Se sei sicuro che il valore di ricerca deve esistere nell’elenco di ricerca, allora il la prima cosa che dovresti fare è controllare il valore di ricerca. Controlla se il valore di ricerca è scritto correttamente. In caso contrario, correggilo.
In secondo luogo, puoi fare un `link: / lookup-formas-partial-match -with-vlookup-function [corrispondenza parziale con CERCA.VERT] `o qualsiasi funzione di ricerca. Se sei sicuro che una parte del testo debba corrispondere, usa questo.
Se non sei sicuro che il valore esista o meno può essere utilizzato per `link: / lookup-formas-using-a-vlookup-formula-to-check-if-a-value-exist [controlla se un valore esiste nell’elenco o meno]”. Nell’immagine sopra, possiamo dire che Divya non è nella lista.
Se vuoi catturare l’errore #NA e stampare o fare qualcos’altro invece di stampare l’errore #NA, puoi usare link: / logical-formulas-excel -isna-function [funzione ISNA di Excel] `. ISNA functi on restituisce TRUE se una funzione restituisce l’errore #NA. Usando questo, possiamo evitare l’errore #NA. ISNA funziona in modo sorprendente con la funzione CERCA.VERT. Dai un’occhiata a `link: / tips-how-to-use-if-isna-and-vlookup-function-in-excel [qui].
Excel #VALUE Error Il #VALUE si verifica quando l’argomento fornito non è di tipo supportato. Ad esempio, se provi ad aggiungere due testi utilizzando l’operatore aritmetico più (+), otterrai un errore #VALORE. La stessa cosa accadrà se si tenta di ottenere l’anno di un formato di data non valido utilizzando la funzione ANNO.
Come correggere l’errore #VALUE?
Per prima cosa, conferma il tipo di dati a cui ti riferisci. Se la tua funzione richiede un numero, assicurati di fare riferimento a un numero. Se un numero è formattato come testo allora usa link: / excel-generals-excel-value-function [VALUE function] per convertirli in numero. Se una funzione richiede testo (come la funzione DATA.VALORE) e ti riferisci a un numero o tipo di data, convertili in testo.
Se ti aspetti che ci possa essere un errore #VALUE e vuoi prenderlo, puoi usare ISERR, link: / excel-formula-and-function-iserror-function [ISERROR]
o link: / logical-formas-excel-iferror-function [SE.ERRORE]
prendere e fare qualcos’altro.
Errore #REF di Excel Il ‘ref’ in #REF sta per riferimento. Questo errore si verifica quando una formula fa riferimento a una posizione che non esiste. Ciò accade quando eliminiamo celle da intervalli a cui si riferisce anche una formula.
Nella gif sottostante, la formula della somma si riferisce ad A2 e B2. Quando elimino A2, la formula si trasforma in errore #REF.
Risolvi l’errore #REF di Excel:
La cosa migliore è fare attenzione prima di eliminare celle in un dato. Assicurati che nessuna formula si riferisca a quella cella. Se hai già un errore #REF, traccia quindi eliminalo dalla formula.
Ad esempio, una volta che ottieni un errore #REF, la tua formula sarà simile a questa.
|
=A2+#REF! |
Puoi semplicemente rimuovere il #REF! Dalla formula per ottenere una formula senza errori. Se vuoi farlo in blocco, usa la funzione trova e sostituisci.
Premere CTRL + H per aprire Trova e sostituisci. Nella casella Trova, scrivi #REF. Lascia vuota la casella di sostituzione. Premi il pulsante Sostituisci tutto.
Se vuoi riadattare il riferimento a una nuova cella, fallo manualmente e sostituisci il #REF! Con quel riferimento valido.
Excel #NAME Error Il #NAME si verifica in Excel quando non è possibile identificare un testo in una formula.
Ad esempio, se si scrive in modo errato il nome di una funzione, Excel mostrerà l’errore #NOME. Se una formula fa riferimento a un nome che non esiste sul foglio, mostrerà l’errore #NAME.
Nell’immagine sopra, la cella B2 ha la formula = POTERI (A2,2). POWERS non è una funzione valida in Excel, quindi restituisce un errore #NAME.
Nella cella B3 abbiamo = SUM (numeri). La SOMMA è una funzione valida di excel ma “numeri”
link: / excel-range-name-all-about-named-range-in-excel [named range] non esiste sul foglio. Quindi Excel restituisce #NOME? Errore.
Come evitare l’errore #NOME in Excel?
Per evitare l’errore #NOME in Excel, scrivi sempre correttamente i nomi delle funzioni. Puoi utilizzare il suggerimento di Excel per assicurarti di utilizzare una funzione valida.
Ogni volta che digitiamo caratteri dopo il segno di uguale, Excel mostra le funzioni e gli intervalli denominati sul foglio, a partire da quel carattere / i. Scorri verso il basso fino al nome della funzione o al nome dell’intervallo nell’elenco dei suggerimenti, premi Tab per utilizzare quella funzione.
Excel # DIV / 0! Errore Come suggerisce il nome, questo errore si verifica quando una formula risulta in una divisione per zero. Questo errore può verificarsi anche quando si elimina un valore da una cella da cui dipende una formula di divisione.
|
= |
Avremo errore DIV / 0 solo se il divisore è 0 o vuoto. Quindi controlliamo il divisore (B2), se è zero allora stampa A2 altrimenti dividi A2 con B2. Funzionerà anche per le celle vuote.
Excel #NUM Errore Questo errore si verifica quando un numero non può essere visualizzato sullo schermo. Il motivo può essere che il numero è troppo piccolo o troppo grande per essere visualizzato.
Un altro motivo potrebbe essere che un calcolo non può essere eseguito con un dato numero.
Nell’immagine sopra, nella cella C2, stiamo cercando di ottenere il valore di -16309. Il valore è così piccolo che non può essere visualizzato.
Nella cella C3 stiamo cercando di ottenere la radice quadrata del valore -16. Poiché non esiste un valore come la radice quadrata di un valore negativo (eccetto i numeri immaginari), quindi excel mostra un #NUM! Errore.
Come risolvere #NUM! Errore?
Per risolvere l’errore #NUM, la prima cosa che puoi fare è controllare anche ogni valore a cui ti stai riferendo. Controlla se sono i numeri validi con cui la tua formula può funzionare.
Usa le funzioni del contatore per risolvere il formato del numero. Ad esempio, nell’esempio sopra, se si desidera ottenere la radice quadrata di -16 ma non si desidera modificare il valore nella cella, è possibile utilizzare la funzione ABS.
Questo restituirà 4. Se si desidera ottenere il valore negativo, utilizzare il segno negativo prima della funzione. Quindi ovviamente puoi usare la funzione di gestione degli errori.
Sulla risoluzione dell’errore #NUM abbiamo un articolo dedicato. Puoi controllarlo link: / tips-how-to-correct-a-num-error [qui].
#NULL Error in Excel Questo è un raro tipo di errore. #NULL errore causato da riferimenti di cella errati. Ad esempio, se si desidera fornire il riferimento di un intervallo A2: A5 nella funzione SOMMA, ma per errore si digita A2 A5. Questo genererà l’errore #NULL. Come puoi vedere nell’immagine sottostante, la formula restituisce l’errore #NULL. Se sostituisci lo spazio con la colonna (:), l’errore #NULL sparirà e otterrai la somma di A2: A5. Se sostituisci lo spazio con la virgola (,), otterrai la somma di A2 e A5.
Come risolvere l’errore #NULLO?
Come sappiamo che l’errore #NULL è causato da un errore di battitura. Per evitarlo, prova a selezionare gli intervalli utilizzando il cursore invece di digitarlo manualmente.
Ci saranno momenti in cui dovrai digitare l’indirizzo dell’intervallo dalla tastiera.
Fare sempre attenzione alla colonna del simbolo di connessione (: *) o alla virgola (,) per evitare.
Se hai un errore #NULL esistente, controlla i riferimenti. Molto probabilmente hai perso la colonna (:) o la virgola (,) tra due riferimenti di cella. Sostituiscili con il simbolo appropriato e sei a posto.
Errore in Excel A volte pensiamo che questo errore sia causato da uno spazio insufficiente per mostrare un valore, ma non è questo il caso. In questo caso puoi semplicemente estendere la larghezza della cella per visualizzare il valore nella cella. Non lo chiamerò un errore.
In effetti, questo errore si verifica quando proviamo a mostrare un valore temporale negativo (non esiste tempo negativo). Ad esempio, se proviamo a sottrarre 1 da 12:21:00 PM, restituirà . In Excel la prima data è 1/1/1900 00:00. Se provi a sottrarre il tempo precedente, Excel ti mostrerà l’errore . Più espandi la larghezza più # otterrai. L’ho elaborato in link: / excel-date-time-formas-convert-date-and-time-from-gmt-greenwich-mean-time-to-cst-central-standard-time [questo articolo].
Come evitare l’errore di Excel?
Prima di eseguire calcoli aritmetici in Excel con valore temporale, tieni a mente queste cose.
Il valore di tempo minimo è 1/1/1900 00:00. Non puoi avere una data valida prima che questa in Excel 1 sia uguale a 24 ore (1 giorno) in Excel. Sottraendo ore, minuti e secondi, convertili in valori equivalenti. Ad esempio, per sottrarre 1 ora dalle 12:21, è necessario sottrarre 1/24 da essa.
Rintracciare gli errori in Excel Ora sappiamo quali sono le formule Excel comuni e perché si verificano. Abbiamo anche discusso quale possibile soluzione può essere per ogni tipo di errore di Excel.
A volte nei rapporti Excel, otteniamo errori e non siamo in grado di sapere da dove si verifica effettivamente l’errore. Diventa difficile risolvere questo tipo di errori. Per rintracciare questi errori, Excel fornisce la funzionalità di tracciamento degli errori nella scheda formula.
Ho discusso link: / tips-how-to-trace-and-fix-formula-errors-in-excel [error tracing in excel in detail].
Risolvere gli errori logici nella formula
Gli errori logici sono difficili da trovare e risolvere. Nessun massaggio mostrato da Excel quando hai un errore logico nella tua funzione. Sembra tutto a posto.
Ma solo tu sai che c’è qualcosa che non va. Ad esempio, mentre ottieni la percentuale di una parte dell’intero, dividi la parte per il totale (= (parte / totale) * 100). Dovrebbe essere sempre uguale o inferiore al 100%.
Quando ottieni più del 100%, sai che qualcosa non va.
Questo è stato un semplice errore logico. Ma a volte i tuoi dati provengono da diverse fonti, in tal caso diventa difficile risolvere i problemi logici.
Un modo è valutare la tua formula.
Nella scheda formula, è disponibile l’opzione Valuta formula. Seleziona la tua formula e fai clic su di essa. Ogni fase del tuo calcolo ti verrà mostrata che porta al tuo risultato finale. Qui puoi controllare dove si è verificato il problema o dove il calcolo è andato storto.
Puoi anche tracciare dipendenti e precedenti per vedere da quali riferimenti dipende la tua formula e quali formule dipendono da una particolare cella.
Quindi sì ragazzi, questi erano alcuni tipi di errori comuni che ogni utente di Excel deve affrontare.
Abbiamo imparato perché si verifica ogni tipo di errore e come possiamo evitarlo. Abbiamo anche imparato a conoscere la funzione Excel dedicata alla gestione degli errori. In questo articolo, abbiamo collegamenti a pagine correlate che discutono il problema in dettaglio. Puoi controllarli. Se riscontri un tipo specifico di errore che ti dà fastidio, menzionalo nella sezione commenti qui sotto. Otterrai la soluzione in modo ordinato.
Articoli correlati:
`link: / tips-how-to-correct-a-num-error [Come correggere un #NUM! Errore] `
link: / tips-how-to-create-custom-error-bars-in-microsoft-excel-2010 [Crea barre di errore personalizzate in Excel 2016]
link: / tips-value-error-and-how-to-fix-it-in-excel [#VALUE Error and how to fix it in Excel]
link: / tips-how-to-trace-and-fix-formula-errors-in-excel [Come tracciare e correggere gli errori di formula in Excel]
Articoli popolari:
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [La funzione CERCA.VERT in Excel]
link: / tips-countif-in-microsoft-excel [CONTA.SE in Excel 2016]
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SOMMA.SE in Excel]