Come gestire gli errori di Excel

Stasera parliamo di come gestire gli errori di Excel. Questa vuole essere una piccola guida per prevenire e gestire correttamente gli errori e risparmiare tempo.

Cosa sono gli errori per Excel e perché compaiono?

Gli errori compaiono in Excel a causa nostra, di noi esseri umani che creiamo e inseriamo nei nostri fogli di lavoro formule con funzioni e a cui non forniamo input, o non li forniamo corretti.

Per esempio se usiamo una funzione matematica, questa si aspetta che tutti gli input siano numeri, se non è così la conseguenza è la comparsa di un errore, oppure se usiamo una formula Excel si aspetta che vengano rispettare le regole della matematica, quindi che non si cerchi di fare divisioni per zero o radici quadrate di numeri negativi, eccetera.

Ogni volta che non si rispettano le regole e non si soddisfano le aspettative delle formule e funzioni che usiamo nei fogli di lavoro, Excel restituisce non il valore atteso, ma un errore.

Quali sono gli errori che Excel restituisce?

Gli errori che Excel controlla e restituisce sono i seguenti:

1. #VALORE!

È l’errore più comune e più generale e compare per indicare che una formula non è stata digitata correttamente, oppure che i riferimenti alle celle non sono validi. L’origine dell’errore può non essere immediatamente individuabile e dipende anche dalle formule e funzioni impostate. Ecco alcuni esempi di cause di questo tipo di errore:

1. Presenza di spazi (“ ”)

Lo spazio è in realtà una presenza estranea che cambia il tipo di dato da numerico a testo. Excel non riconoscendo il numero non può eseguire il calcolo e restituisce l’errore. Attenzione, celle che sembrano vuote possono in realtà contenere spazi.

2. Presenza di caratteri speciali o testo (“A” o “ ’ ”)

La presenza di testo o caratteri speciali come l’apostrofo possono alterare il tipo di dato da numero a testo ed essere anche difficilmente riconoscibili come tali.

3. Funzioni che fanno riferimento a celle che contengono errori #Valore!, come: SE, CONTA.SE, MEDIA, SOMMA, CONCATENA, ecc,
4. Situazioni o condizioni particolari quando utilizziamo funzioni specifiche, come per esempio: date scritte come testo con la funzione GIORNI, oppure la funzione TROVA quando non riesce a trovare la stringa ricercata, o quando viene indirizzata male (inizio più grande della lunghezza testo), oppure quando si usano le funzioni INDICE e CONFRONTA insieme per recuperare un valore, ma non si impostano le formule come formule di matrice (Ctrl+Maius+Invio), e altre ancora.

2. #DIV/0!

È l’errore che Excel restituisce quando una qualsiasi formula si ritrova a dividere per zero, situazione che può essere determinata da un valore zero di una cella della tabella dati, o da una cella vuota, o dal risultato di una funzione (es: somma) interna alla formula o a cui la formula si riferisce e che appunto restituisce zero.

3. #NUM!

Excel mostra questo errore quando:

1. una formula o una funzione contiene o si riferisce a valori numerici non validi. Per esempio, valori formattati (es: €, %), o valori negativi quando non è permesso (es: Radq(–100));
2. una formula calcola un numero troppo grande o uno troppo piccolo, oltre i limiti di Excel;
3. o quando usiamo funzioni che eseguono iterazioni, come TASSO e TIR.COST, che non trovano un isultato e noi non abbiamo limitato il numero di iterazioni.

4. #RIF!

È un errore generato da riferimenti sbagliati o rimossi in formule e funzioni. Le cause più frequenti possono essere:

1. interventi successivi all’impostazione di formule con riferimenti ad altre celle, per esempio quando si rimuovono le celle a cui si fa riferimento,
2. oppure può essere generato dal tentativo di copiare formule con riferimenti che non possono essere garantiti, per esempio quando si copia una formula da destra a sinistra della pagina con un riferimento ancora più a sinistra fino ad uscire dal bordo (es: =#RIF!/A3),
3. o da indicazioni errate nell’impostazione degli input di funzioni complesse, come CERCA.VERT, INDICE, o funzioni database.

5. #NOME?

È un errore causato da un errore di sintassi della formula o funzione che abbiamo scritto, o talvolta dei riferimenti. Alcuni esempi di errore possono essere:

1. errori nella digitazione di riferimenti, es: =A+3+B3,
2. errori nella digitazione delle funzioni, es: =SOMMASE(D2:D100;”Ricambi”),
3. se ci si dimentica di chiudere le virgolette a fine di un testo,
4. se ci si dimentica i due punti (“:”) in un intervallo, es: SOMMA(H4H8),
5. quando facciamo riferimento a nomi non definiti,
6. o facciamo errori nel citare nomi definiti.

Grazie all’autocompilazione, al controllo degli errori di Excel e alla finestra delle funzioni (con Maiusc+F3), #Nome? è un errore sempre più raro, facilmente identificabile e prevenibile.

6. #N/D

È l’errore restituito da Excel nel caso una formula non trovi un valore richiesto. La causa più comune sono le funzioni CERCA.VERT, CERCA.ORIZZ, CERCA o CONFRONTA, quando non riescono a trovare il valore a cui si fa riferimento. È un tipo di errore difficile da prevenire, in quanto abbiamo a che fare con funzioni di ricerca, ma che si può intercettare usando le funzioni apposite per la gestione degli errori, come SE.ERRORE.

7.#NULLO!

Un errore tra i più rari, viene restituito se viene usato un operatore di intervallo errato in una formula o se viene usato un operatore di intersezione tra i riferimenti all’intervallo per specificare un’intersezione di due intervalli che non si intersecano (es: “ ”, cioè spazio invece che “;”).

Come si gestiscono gli errori di Excel?

Gli errori di Excel si gestiscono nei seguenti modi:

1. Risolvendoli

Quando gli errori non possono essere prevenuti o intercettati, o non si è voluto o potuto, allora è necessario risolverli:

1. Eliminare le cause

Cioè eliminando o correggendo i dati che provocano errore, per esempio rimuovendo da dati che dovrebbero essere numerici i caratteri estranei che li rendono “testo”. Questo può essere fatto manualmente per pochi valori, o con strumenti come “Sostituisci” (Ctrl+S, o comando omonimo della scheda “Home” nella barra multifunzione), o usando i filtri delle tabelle per individuare i valori difformi.

2. Correggere gli input a formule e funzioni

Correggendo riferimenti inesatti, criteri e indici sbagliati. Come? Intervenendo a mano sulla formula, per poi propagarla copiandola, oppure usando “Sostituisci” (Ctrl+S) per sostituire le parte sbagliate in tutte le formule di un intervallo di celle.

3. Ripristinare celle o intervalli rimossi

Quando si rimuovono celle e riferimenti di formule e funzioni, è opportuno ripristinarli subito con lo strumento “Annulla”. In caso non ci accorgiamo immediatamente del problema e non possiamo usare Annulla, è necessario ripristinare un backup del foglio di lavoro o intervenire manualmente sulle celle per ripristinare i riferimenti mancanti.

4. Risalire agli errori propagati dalle funzioni per risolverli

In caso di funzioni che propagano errori, cioè restituiscono un errore perché presente nei dati che elaborano, è necessario risalire all’origine effettiva dell’errore e intervenire a monte, risolvendo l’errore o impostando le formule per intercettarli.

2. Intercettando l’errore

Gli errori possono essere intercettati prima di venire restituiti dalle formule con le funzioni specifiche che Excel fornisce che sono descritte più sotto. Per esempio possiamo usare la funzione SE.ERRORE che esegue un controllo sulla formula e nel caso restituisca errore, fornisce un valore o una formula alternativa. Per esempio, =SE.ERRORE(A3/B3; “Attenzione, Errore!”), oppure =SE.ERRORE(A3/B3; A3/C3).

Intercettare gli errori consiste nella modifica delle formule che possono restituire un errore con l’aggiunta di funzioni in grado appunto di intercettarli, cioè riconoscerli e sostituirli con un’alternativa. Questo richiede una buona conoscenza delle formule/funzioni e degli errori.

Il primo passo è comprendere dove è necessario intervenire perché sussiste il rischio di comparsa di errori. Il mio consiglio è:

1. rivedere le formule alla fine della stesura, controllare che siano scritte correttamente, valutarne gli input e testarle nell’ottica di potenziali errori;
2. valutare la base dati che stai elaborando per comprendere quanto sia affidabile, per individuare la presenza di errori, per esempio di campi numerici “sporchi” con valori di testo, e per studiare le caratteristiche dei campi da analizzare (distribuzione, minimo–massimo, valori zero, ecc).
3. impostare sempre controlli per intercettare gli errori in caso di: valori nulli, valori uguale a zero, valori molto grandi o piccoli, e naturalmente in presenza di errori nei dati.
4. impostare sempre controlli per intercettare gli errori in caso di utilizzo di funzioni di ricerca (es: CERCA.VERT, TROVA, CERCA, INDICE/CONFRONTA).
5. valutare sempre l’impiego di controlli per intercettare gli errori in presenza di formule complesse con riferimenti molteplici e funzioni con input complessi.

3. Prevenendo l’errore

Una parte degli errori possono anche essere prevenuti. Quali? Gli errori presenti nella base dati o provocati dalle caratteristiche dei dati.

Come?

Ponendo dei controlli a monte delle formule, sulla base dati per identificare, intercettare e correggere gli errori presenti e, nel caso la fonte non sia affidabile (es: come un server sql), porre controlli su estrazione, gestione e inserimento dei dati. Per esempio se i dati vengono inseriti manualmente su un foglio Excel, è opportuno valutare l’efficacia dei controlli all’inserimento, o se vengono estratti da un database Access è opportuno rivedere l’efficacia della query, eccetera.

L’obiettivo è quello di fare in modo che i dati su cui lavoriamo siano esenti da errori e che certe condizioni che possono provocarli non si presentino affatto.

Qual è il modo più efficace per gestire gli errori di Excel?

Dove possibile è meglio prevenire gli errori, dove non è possibile è consigliabile intercettarli agendo sulle formule dove è probabile che gli errori si presentino. Per farlo in modo efficiente è necessario studiare errori, funzioni e formule a livello almeno intermedio e valutare sistematicamente le formule che inserisci nei tuoi fogli di lavoro anche nell’ottica della gestione degli errori, come descritto sopra.

Infine dove non è possibile prevenire nè intercettare gli errori, è utile imparare a cercare e identificare le cause degli errori e imparare come intervenire per correggerli. Quindi se la causa è una funzione impostata in modo impreciso o scorretto è utile studiarla meglio, se il problema è causato da dati “sporchi” da correggere è utile imparare bene l’uso di strumenti come “Sostituisci” e filtri.

Considerata la complessità dell’argomento e delle situazioni possibili, devo ammettere che la migliore maestra con gli errori è l’esperienza, cioè affrontarli, gestirli, risolverli e prevenirli ripetutamente. D’altra parte il problema non è così rilevante com’era un tempo fino alla versione 2007 di Excel. Oggi il controllo degli errori integrato di Excel e gli strumenti che ci assistono nell’uso delle funzioni (autocompilazione, finestra funzioni, ecc) intercettano la maggior parte dei problemi.

Quali sono gli strumenti per gestire gli errori di Excel?

Gli strumenti per gestire gli errori sono i seguenti:

1. Funzioni specifiche

Essendo la maggior parte degli errori generata da formule e funzioni, gli strumenti più efficaci sono le funzioni che Excel mette a disposizione per questo scopo, per esempio:

1. VAL.ERRORE(valore)

La funzione VAL.ERRORE riconosce tutti i tipi di errore e restituisce il valore VERO. Altrimenti restituisce FALSO. Utile per controllare il valore prima di eseguire calcoli o formule, è usato classicamente con la funzione SE per intercettare gli errori, in questo modo: =SE(VAL.ERRORE(B3);0;A3/B3). Sostituita nelle versioni di Excel successive alla 2007 con la funzione SE.ERRORE.

2. SE.ERRORE(valore; valore_se_errore)

La funzione SE.ERRORE combina le funzioni SE e VAL.ERRORE per intercettare qualunque errore e sostituirlo con un valore o una formula alternativa. L’esempio appena visto qui sopra, =SE(VAL.ERRORE(B3);0;A3/B3), con SE.ERRORE diventa: =SE.ERRORE(A3/B3;0).

Un uso tipico di questa funzione è quello con CERCA.VERT per evitare la comparsa di #N/D: =SE.ERRORE(CERCA.VERT(N3;A2:E29;5;0);“Dato non trovato”). Naturalmente può essere utilizzato anche nidificato per intercettare possibili errori della formula alternativa: =SE.ERRORE(CERCA.VERT(N3;A2:E29;5;0);SE.ERRORE(CERCA.VERT(N3;A2:E29;4;0);“Dato non trovato”)).

Un altro uso tipico è nei campi di controllo calcolati applicati direttamente nella pagina dati, o meglio nella tabella dati, oppure applicati esternamente da una pagina di controllo.

3. VAL.ERR(valore)

La funzione VAL.ERR è una versione più vecchia e limitata di VAL.ERRORE, in quanto valuta tutte le tipologie di errore ad eccezione di #N/D. L’utilizzo è scarso ed è analogo a quello di VAL.ERRORE, di solito insieme a SE: =SE(VAL.ERR(B3);0;A3/B3).

4. VAL.NON.DISP(Valore)

La funzione VAL.NON.DISP è simile a VAL.ERRORE e VAL.ERR, riconosce un errore #N/D e restituisce VERO. In caso di assenza di errori o in presenza di errori diversi restituisce FALSO. Anche in questo caso l’utilizzo è scarso ed è analogo a quello di VAL.ERRORE, di solito insieme a SE: =SE(VAL.NON.DISP(CERCA.VERT(N3;A2:E29;5;0));”Dato non trovato”; CERCA.VERT(N3;A2:E29;5;0)).

5. ERRORE.TIPO(errore)

La funzione ERRORE.TIPO serve a riconoscere il tipo di errore e restituisce un numero da 1 a 8, che corrisponde al tipo di errore identificato:

1. 1 = #NULLO!
2. 2 = #DIV/0!
3. 3 = #VALORE!
4. 4 = #RIF!
5. 5 = #NOME?
6. 6 = #NUM!
7. 7 = #N/D
8. 8 = #ESTRAZIONE_DATI_IN_CORSO

Se non trova alcun errore la funzione restituisce un errore #N/D e per questo è di solito utilizzata insieme a SE.ERRORE, oppure SE e VAL.ERRORE.

L’uso tipico di ERRORE.TIPO è per i controlli sui dati per identificare velocemente il tipo di errore e risalire più facilmente alle cause. Un esempio classico è: =SE(VAL.ERRORE(B3);ERRORE.TIPO(B3);“”) per restituire il tipo di errore che può essere reso parlante con una colonna aggiuntiva adiacente e un CERCA.VERT indirizzato a una lista come questa copiata sulla pagina:

1. 1 | #NULLO! – Errore nell’indicazione dell’intervallo
2. 2 | #DIV/0! – Divisione per 0
3. 3 | #VALORE! – Controlla i dati o la compilazione delle funzioni
4. 4 | #RIF! – Riferimenti rimossi o errati
5. 5 | #NOME? – Errore nella formula o nei riferimenti
6. 6 | #NUM! – Valori non validi o troppo grandi o piccoli
7. 7 | #N/D – Valore non determinato

2. Funzioni non specifiche

2. 1. SE (condizione è Vera, esegui qualcosa, altrimenti fai qualcos’altro)

La prima delle funzioni con cui eseguire i controlli è SE, con cui è possibile intercettare per esempio la presenza di zero, valori negativi, celle vuote, eccetera. Un esempio: =SE(B3=0;0;A3/B3). SE è una funzione molto potente che immagino tu conosca, in caso di dubbi ti invito a visitare la pagina relativa del sito Office.

2. VAL.VUOTO(valore)

Controlla se il valore del riferimento è vuoto, in tal caso restituisce il valore VERO. Utile per intercettare cella vuote senza valori che possono generare errori (es: nel caso di divisioni). L’uso è semplice: =SE(VAL.VUOTO(B3);“Attenzione valore mancante”;B3).

3. VAL.NUMERO(valore)

Funzione che controlla se il valore del riferimento è un numero e in tal caso restituisce VERO. È utile per verificare la presenza di dati “sporchi”, cioè della presenza di valori di tipo diverso (es: testo) che darebbero errore se elaborati da funzioni matematiche. Per esempio: =SE(VAL.NUMERO(B3);“NUMERO”;“Attenzione, non è un numero! Verificare”).

4. VAL.TESTO(valore)

Funzione simile alla precedente, controlla se il valore del riferimento è un testo e in tal caso restituisce VERO. Anch’essa è utile per controllare dati e individuare la presenza di dati anomali o sporchi. L’uso è analogo.

3. Filtri delle tabelle

I filtri delle tabelle excel e delle tabelle pivot sono estremamente utili nella ricerca degli errori e delle loro cause. Spesso è sufficiente un controllo veloce della lista dei valori del filtro per individuare la presenza di dati precisi (es: vuoti, zero, ecc) o fuori dall’ordinario (es: troppo grandi, troppo piccoli, con caratteri speciali, ecc).

4. Trova e Sostituisci

Usati per individuare velocemente e correggere o eliminare le cause degli errori anche su un intervallo molto grande. Usa Ctrl+Maiusc+T per richiamare velocemente Trova e Ctrl+Maiusc+S per richiamare velocemente Sostituisci. Sono strumenti che già conosci, per un approfondimento o un ripasso ti rimando a questa pagina.

5. Annulla

Excel memorizza ogni attività e permette di tornare indietro con questo strumento richiamabile dalla barra o con un semplice Ctrl+Z. Nella gestione degli errori è usato soprattutto per eliminare errori #RIF! generati dalla rimozione non voluta di celle, righe o colonne. È uno degli strumenti che usiamo più spesso per correggere i nostri errori sul foglio di lavoro.

Non solo errori

All’inizio è capitato a tutti di incappare in alcune risposte strane a vedersi, ma che non sono errori.

Il primo è una cella o una colonna piena di cancelletti (#####), che di solito Excel usa per evidenziare problemi di formattazione, per esempio che una colonna è troppo stretta per visualizzare correttamente i valori, o che si sta cercando di visualizzare come data un valore negativo, per esempio quando si fanno operazioni sulle date. In entrambi i casi i dati sono ancora disponibili nella cella, ma per visualizzarli è necessario risolvere i problemi di formattazione. Nel primo caso basta espandere la colonna per adattarla al contenuto e i cancelletti scompariranno, nel secondo caso basta cambiare il formato da data a numero.

Un altro messaggio inconsueto di Excel è #ESTRAZIONE_DATI_IN_CORSO, difficile da vedersi con le ultime versioni del foglio di calcolo se non in condizioni particolari (es: formule concatenate) e con grandi volumi di dati. È solo un messaggio temporaneo che avverte che Excel sta elaborando e che il dato verrà fornito una volta completato il calcolo.

Conclusioni

Gli errori che si presentano nell’elaborazione dei dati con Excel compaiono ogni volta che non si rispettano le regole o non si soddisfano le aspettative delle formule e funzioni che usiamo nei fogli di lavoro. Direttamente e indirettamente siamo noi la causa, in quanto operatori e registi dei nostri fogli di lavoro.

La gestione degli errori nel tempo è diventata sempre meno rilevante grazie al controllo degli errori integrato e agli strumenti di excel sempre più evoluti, ma non per questo è diventata meno importante o meno critica. Nessuno vuole presentare risultati sbagliati o mostrare dashboard e report con degli errori.

Per quanto sia un argomento noioso, è opportuno conoscere bene i limiti delle formule e delle funzioni di Excel e gli errori che possono insorgere. Sapere come prevenirli e gestirli ti farà risparmiare tempo ed errori. Non a caso i professionisti si vedono anche nella competenza con cui gestiscono gli errori di Excel.

Prima di andartene registrati a Excel professionale in questa pagina e scarica i contenuti gratuiti: le macro, i foglio di lavoro automatizzati e il corso “Usa Excel come un professionista”.

PS: Se questa guida ti è piaciuta o ti è stato utile, condividila con gli amici sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie