Rilevare gli errori nelle formule

La tabella seguente riepiloga alcuni degli errori più comuni di immissione delle formule e spiega come correggerli.

È possibile implementare determinate regole per cercare errori nelle formule. Queste regole non garantiscono che il foglio di lavoro sia privo di errori, ma consentono di trovare quelli più comuni. Le regole possono essere attivate o disattivate singolarmente.

È possibile contrassegnare e correggere gli errori in due modi: uno alla volta, con modalità analoghe al correttore ortografico, oppure direttamente nel foglio di lavoro durante l'immissione dei dati.

È possibile risolvere un errore usando le opzioni visualizzate in Excel oppure ignorare l'errore selezionando Ignora errore. Se un errore viene ignorato in una cella specifica, non verrà visualizzato nei controlli futuri. È possibile tuttavia ripristinare tutti gli errori ignorati in precedenza in modo che vengano visualizzati di nuovo.

1. Per Excel in Windows, passare a Opzioni > file > Formule oppure
   per Excel per Mac selezionare il menu Excel > Preferenze > Controllo errori. 

   In Excel 2007 selezionare il pulsante Microsoft Office > Opzioni di Excel > Formule.

2. In Controllo errori selezionare Abilita controllo degli errori in background. Qualsiasi errore trovato viene contrassegnato con un triangolo nell'angolo superiore sinistro della cella.

   

3. Per cambiare il colore del triangolo che indica dove si trova un errore, selezionare il colore desiderato nella casella Indica errori utilizzando questo colore.

4. In Regole controllo errori selezionare o deselezionare le caselle di controllo relative alle regole seguenti:

   • Celle contenenti formule che reducono un errore: una formula non usa la sintassi, gli argomenti o i tipi di dati previsti. I valori di errore includono #DIV/0!, #N/D, #NAME?, #NULL!, #NUM!, #REF! e #VALUE!. Ognuno di questi valori di errore ha cause diverse e viene risolto in modi diversi.

     Nota: Se si immette un valore di errore direttamente in una cella, questo viene memorizzato come valore di errore ma non viene contrassegnato come errore. Se tuttavia questa cella viene usata come riferimento da una formula in un'altra cella, la formula restituirà il valore di errore contenuto in tale cella.

   • Formula per la colonna calcolata non coerente nelle tabelle: una colonna calcolata può includere singole formule diverse rispetto alla formula di colonna principale, dando origine a un'eccezione. Le eccezioni delle colonne calcolate vengono generate quando si esegue una delle operazioni illustrate di seguito.

     • Si digitano dati diversi da una formula in una cella di una colonna calcolata.

     • Digitare una formula in una cella di una colonna calcolata e quindi premere CTRL+Z o selezionare Annulla sulla barra di accesso rapido.

     • Si digita una nuova formula in una colonna calcolata che contiene già una o più eccezioni.

     • Si copiano dati nella colonna calcolata che non corrispondono alla formula in essa contenuta. Se i dati copiati contengono una formula, questa sovrascriverà i dati presenti nella colonna calcolata.

     • Spostare o eliminare una cella in un'altra area del foglio di lavoro alla quale fa riferimento una delle righe di una colonna calcolata.

   • Celle contenenti anni rappresentati da due cifre: la cella contiene una data il cui secolo può essere interpretato in modo errato nelle formule. La data nella formula =ANNO("1/1/31"), ad esempio, può riferirsi sia al 1931 sia al 2031. Con questa regola è possibile controllare le date ambigue.

   • Numeri formattati come testo o preceduti da apostrofo: la cella contiene numeri archiviati come testo. Ciò si verifica in genere con dati importati da altre origini. I numeri archiviati come testo possono causare risultati di ordinamento imprevisti, quindi è consigliabile convertirli in numeri. ‘=SOMMA(A1:A10) viene interpretato come testo.

   • Formule non coerenti con altre formule dell'area geografica: la formula non corrisponde allo schema delle altre formule vicine. In molti casi, le formule adiacenti ad altre formule differiscono solo nei riferimenti usati. Nell'esempio seguente di quattro formule adiacenti, Excel visualizza un errore accanto alla formula =SOMMA(A10:C10) nella cella D4 perché le formule adiacenti vengono incrementati di una riga e di 8 righe. Excel prevede che la formula =SOMMA(A4:C4).

     

     Se i riferimenti usati in una formula non sono coerenti con quelli delle formule adiacenti, Excel visualizza un errore.

   • Formule che omettono celle nell'area: una formula potrebbe non includere automaticamente riferimenti a dati inseriti tra l'intervallo di dati originale e la cella che contiene la formula. Questa regola confronta il riferimento in una formula con l'intervallo di celle effettivo adiacente alla cella che contiene la formula. Se le celle adiacenti contengono ulteriori valori e non sono vuote, viene visualizzato un errore accanto alla formula.

     Ad esempio, Excel inserisce un errore accanto alla formula =SOMMA(D2:D4) quando viene applicata questa regola, perché le celle D5, D6 e D7 sono adiacenti alle celle a cui si fa riferimento nella formula e alla cella che contiene la formula (D8) e tali celle contengono dati a cui avrebbe dovuto fare riferimento nella formula.

     

   • Celle non bloccate contenenti formule: la formula non è protetta. Per impostazione predefinita, quando il foglio di lavoro è protetto tutte le celle sono bloccate per impedirne la modifica. Questo consente di evitare l'eliminazione o la modifica accidentale delle formule. Questo errore indica che la cella è stata sbloccata, ma il foglio non è protetto. Verificare che la cella non sia bloccata.

   • Formula con riferimenti a celle vuote: la formula contiene un riferimento a una cella vuota. Ciò può portare a risultati indesiderati, come illustrato nell'esempio seguente.

     Si supponga di voler calcolare la media dei numeri nella colonna seguente di celle. Se la terza cella è vuota, non viene inclusa nel calcolo e il risultato è 22,75. Se invece contiene 0, il risultato sarà 18,2.

     

   • Immissione di dati non validi in una tabella: in una tabella è presente un errore di convalida. Controllare l'impostazione di convalida della cella facendo clic su Convalida dati nel gruppo Strumenti dati della scheda Dati.

1. Selezionare il foglio di lavoro in cui controllare gli errori.

2. Se il foglio di lavoro viene calcolato manualmente, premere F9 per eseguire nuovamente il calcolo.

   Se la finestra di dialogo Controllo errori non è visualizzata, selezionare Formule > Controllo formule > Controllo errori.

3. Se gli errori sono stati ignorati in precedenza, è possibile verificarli nuovamente eseguendo le operazioni seguenti: passare a Opzioni > file > Formule. Per Excel per Mac, selezionare il menu Excel > Preferenze > Controllo errori.

   Nella sezione Controllo errori selezionare Reimposta errori ignorati > OK.

   

   Nota: Il ripristino degli errori ignorati comporta il ripristino di tutti gli errori in tutti i fogli della cartella di lavoro attiva.

   Suggerimento: Può essere utile spostare la finestra di dialogo Controllo errori immediatamente al di sotto della barra della formula.

   

4. Selezionare uno dei pulsanti di azione sul lato destro della finestra di dialogo. Le opzioni disponibili variano a seconda del tipo di errore.

5. Seleziona Avanti.

1. Accanto alla cella selezionare Controllo errori , quindi selezionare l'opzione desiderata. I comandi disponibili variano in base al tipo di errore, che è descritto nella prima voce.

   Se si seleziona Ignora errore, l'errore viene contrassegnato per essere ignorato per ogni controllo consecutivo.

   

Se una formula non riesce a valutare correttamente un risultato, Excel visualizza un valore di errore, ad esempio #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! e #VALUE!. Ogni tipo di errore ha cause diverse e soluzioni diverse.

La tabella seguente contiene collegamenti ad articoli che descrivono questi errori nel dettaglio, oltre a una breve descrizione utile per iniziare.

Argomento	Descrizione
Correggere un errore ####	Excel visualizza questo errore quando una colonna non è abbastanza larga da visualizzare tutti i caratteri contenuti in una cella o quando una cella contiene valori di data o ora negativi. Ad esempio, una formula che sottrae una data futura da una data passata, come =15/06/2008-01/07/2008, restituisce un valore di data negativo. Suggerimento: Provare ad adattare automaticamente la cella al contenuto facendo doppio clic tra le intestazioni di colonna. Se ### viene visualizzato perché Excel non è in grado di visualizzare tutti i caratteri in questo modo, la corregge.
Correggere un errore #DIV/0! errore	Excel visualizza questo errore quando un numero viene diviso per zero (0) o per una cella che non contiene alcun valore. Suggerimento: Aggiungere un gestore di errori come nell'esempio seguente, che è =SE(C2;B2/C2;0)
Correggere un errore #N/D	Excel visualizza questo errore quando un valore non è disponibile per una funzione o una formula. Se si usa una funzione come CERCA.VERT, il valore cercato ha una corrispondenza nell'intervallo di ricerca? Nella maggior parte dei casi non è così. Provare a usare SE.ERRORE per eliminare l'errore #N/D. In questo caso si può usare: =SE.ERRORE(CERCA.VERT(D2;$D$6:$E$8;2;VERO),0)
Correggere un errore #NOME? errore	Excel visualizza questo errore quando non riconosce il testo di una formula. Ad esempio, il nome di un intervallo o di una funzione potrebbe essere digitato in modo non corretto. Nota: Se si usa una funzione, assicurarsi che il nome della funzione sia stato digitato correttamente. In questo caso SOMMA è digitato in modo errato. Rimuovere la "e" ed Excel la corregge.
Correggere un #NULL. #BUSY!	Excel visualizza questo errore quando si specifica un'intersezione di due aree che di fatto non si intersecano. L'operatore di intersezione è un carattere spazio che separa i riferimenti in una formula. Nota: Assicurarsi che gli intervalli siano separati in modo corretto. Le aree C2:C3 ed E4:E6 non si intersecano, quindi se si immette la formula =SOMMA(C2:C3 E4:E6) verrà restituito l'errore #NULLO!. Inserire una virgola tra gli intervalli C ed E corregge la virgola =SOMMA(C2:C3,E4:E6)
Correggere un errore #NUM! errore	Excel visualizza questo errore quando una formula o una funzione contiene valori numerici non validi. Si sta usando una formula che segue iterazioni, ad esempio TIR.COST o TASSO? In caso affermativo, probabilmente l'errore #NUM! è dovuto al fatto che la funzione non riesce a trovare un risultato. Per la procedura di risoluzione, fare riferimento all'argomento della Guida.
Correggere un errore #RIF! errore	Excel visualizza questo errore quando un riferimento di cella non è valido. È ad esempio possibile che siano state eliminate celle a cui fanno riferimento altre formule o che siano state incollate celle spostate sopra celle a cui fanno riferimento altre formule. È stata eliminata per errore una riga o una colonna? Abbiamo eliminato la colonna B e la formula =SOMMA(A2;B2;C2) restituisce l'errore #RIF!. Usare Annulla (CTRL+Z) per annullare l'eliminazione, ricreare la formula o usare un riferimento a un intervallo continuo simile a =SOMMA(A2:C2), che sarebbe stato aggiornato automaticamente quando la colonna B è stata eliminata.
Correggere un errore #VALORE! errore	Excel visualizza questo errore quando la formula include celle che contengono tipi di dati diversi. Si stanno usando operatori matematici (+, -, *, /, ^) con tipi di dati diversi? Se la risposta è sì, provare a usare una funzione. In questo caso, la funzione =SOMMA(F2:F5) consentirebbe di correggere il problema.

Quando le celle non sono visibili in un foglio di lavoro, è possibile watch tali celle e le relative formule nella barra degli strumenti Finestra controllo celle. La finestra Espressioni consente di ispezionare, controllare o confermare in modo agevole i calcoli delle formule e i risultati in fogli di lavoro di grandi dimensioni. Con la finestra Controllo celle non è necessario scorrere ripetutamente o passare a parti diverse del foglio di lavoro.

Questa barra degli strumenti può essere spostata o ancorata come qualsiasi altra barra degli strumenti. È possibile ad esempio ancorarla nella parte inferiore della finestra. La barra degli strumenti tiene traccia delle proprietà seguenti di una cella: 1) cartella di lavoro, 2) foglio, 3) nome (se la cella ha un intervallo denominato corrispondente), 4) indirizzo di cella, 5) valore e 6) formula.

Aggiungere celle alla Finestra controllo celle

1. Selezionare le celle da controllare.

   Per selezionare tutte le celle di un foglio di lavoro con formule, passare a Home > Modifica > selezionare Trova & Seleziona (oppure premere CTRL+G o CTRL+G nel Mac)> Vai aformule speciali >.

   

2. Passare a Formule > Controllo formule > selezionare Finestra controllo celle.

3. Selezionare Aggiungi controllo cella.

   

4. Verificare di aver selezionato tutte le celle da watch e selezionare Aggiungi.

   

5. Per modificare la larghezza di una colonna nella Finestra controllo celle, trascinare il bordo destro dell'intestazione di colonna.

6. Per visualizzare la cella a cui fa riferimento una voce della Finestra controllo celle, fare doppio clic sulla voce.

   Nota: Le celle che contengono riferimenti esterni ad altre cartelle di lavoro vengono visualizzate sulla barra degli strumenti Finestra controllo celle solo quando le cartelle di lavoro corrispondenti sono aperte.

Rimuovere celle dalla Finestra controllo celle

1. Se la barra degli strumenti Finestra controllo celle non è visualizzata, passare a Formule > Verifica formule > selezionare Finestra controllo celle.

2. Selezionare le celle da rimuovere.

   Per selezionare più celle, premere CTRL e quindi selezionare le celle.

3. Selezionare Elimina controllo cella.

   

Le modalità adottate da una formula annidata per il calcolo del risultato finale non sono sempre semplici da capire, in quanto esistono diversi test logici e calcoli intermedi. Tuttavia, tramite la finestra di dialogo Valuta formula è possibile visualizzare le diverse parti di una formula annidata valutate nell'ordine in cui la formula viene calcolata. Ad esempio, la formula =SE(MEDIA(D2:D5)>50;SOMMA(E2:E5);0) è più facile da capire quando vengono visualizzati i risultati intermedi seguenti:

1. Selezionare la cella da valutare. È possibile valutare solo una cella per volta.

2. Passare a Formule > Verifica formule > Valuta formula.

3. Selezionare Valuta per esaminare il valore del riferimento sottolineato. Il risultato della valutazione viene visualizzato in corsivo.

   Se la parte sottolineata della formula è un riferimento a un'altra formula, selezionare Entra per visualizzare l'altra formula nella casella Valutazione . Selezionare Esci per tornare alla celle e alla formula precedenti.

   Il pulsante Entra non è disponibile per il riferimento la seconda volta che questo viene visualizzato nella formula o se la formula fa riferimento a una cella di una cartella di lavoro distinta.

4. Continuare a selezionare Valuta fino a valutare ogni parte della formula.

5. Per visualizzare di nuovo la valutazione, seleziona Riavvia.

6. Per terminare la valutazione, selezionare Chiudi.