Come evitare errori nelle formule

Excel genera un'ampia varietà di errori preceduti dal simbolo cancelletto (#), ad esempio #VALORE!, #RIF!, #NUM, #N/D, #DIV/0!, #NOME? e #NULLO!, per indicare che qualcosa non funziona nella formula. Ad esempio, l'errore #VALUE! è causato da una formattazione non corretta o da tipi di dati non supportati negli argomenti. In alternativa, è visibile l'errore #REF! se una formula fa riferimento a celle che sono state eliminate o sostituite con altri dati. Le indicazioni per la risoluzione dei problemi variano a seconda dell'errore.

Fare riferimento agli argomenti seguenti corrispondenti all'errore con il simbolo cancelletto visualizzato:

• Correggere un errore #NUM! errore

• Correggere un errore #VALORE! errore

• Correggere un errore #N/D

• Correggere un errore #DIV/0! errore

• Correggere un errore #RIF! errore

• Correggere un errore #NOME? errore

• Correggere un #NULL. #BUSY!

Ogni volta che si apre un foglio di calcolo contenente formule che fanno riferimento a valori contenuti in altri fogli di calcolo, viene chiesto se si vuole aggiornare i riferimenti o lasciarli come sono.

Excel visualizza questa finestra di dialogo per fare in modo che le formule nel foglio di calcolo corrente puntino sempre al valore più aggiornato, in caso il valore di riferimento sia cambiato. È possibile scegliere di aggiornare i riferimenti o ignorare questo messaggio se non si vuole aggiornarli. Anche se si sceglie di non aggiornare i riferimenti, è sempre possibile aggiornare manualmente i collegamenti nel foglio di calcolo in qualsiasi momento.

Si può anche disabilitare la finestra di dialogo per evitare che venga visualizzata all'avvio. A tale scopo, scegliere File > Opzioni > Impostazioni avanzate > Generale e deselezionare la casella Richiedi aggiornamento collegamenti automatici.

Se la formula non visualizza il valore, seguire questa procedura:

• Verificare che Excel sia impostato per visualizzare le formule nel foglio di calcolo. A tale scopo, selezionare la scheda Formule e nel gruppo Controllo formule selezionare Mostra formule.

  Suggerimento: È anche possibile usare la scelta rapida da tastiera CTRL + ` (il tasto sopra TAB). In questo modo le colonne vengono allargate automaticamente fino a visualizzare le formule, ma vengono ridimensionate appena si torna alla visualizzazione normale.

• Se il passaggio precedente non consente comunque di risolvere il problema, è possibile che la cella sia formattata come testo. Fare clic con il pulsante destro del mouse sulla cella e scegliere Formato celle > Generale (oppure CTRL+1), quindi premere F2 > INVIO per cambiare il formato.

• Se si ha una colonna con un intervallo di celle esteso formattate come testo, è possibile selezionare l'intervallo, applicare un formato numero a scelta e selezionare Dati > Testo in colonne > Fine. In questo modo il formato verrà applicato a tutte le celle selezionate.

  

Se una formula non viene calcolata, occorre verificare se il calcolo automatico è abilitato in Excel. Se è abilitato il calcolo manuale, le formule non vengono calcolate. Seguire questa procedura per verificare che il Calcolo automatico sia abilitato.

1. Selezionare la scheda File, selezionare Opzioni e quindi selezionare la categoria Formule.

2. Nella sezione Opzioni di calcolo, in Calcolo cartella di lavoro, verificare che il pulsante di opzione Automatico sia selezionato.

   

Per altre informazioni sui calcoli, vedere Cambiare il ricalcolo, l'iterazione o la precisione delle formule.

Un riferimento circolare ha luogo quando una formula fa riferimento alla cella in cui si trova. La soluzione consiste nello spostare la formula in un'altra cella o modificare la sintassi della formula, in modo da evitare i riferimenti circolari. In alcuni casi, tuttavia, i riferimenti circolari possono essere utili perché determinano l'iterazione di una funzione, ovvero una ripetizione della funzione finché non viene soddisfatta una condizione numerica specifica. In questi casi, è necessario abilitare Rimuovere o consentire un riferimento circolare.

Per altre informazioni sui riferimenti circolari, vedere Rimuovere o consentire un riferimento circolare.

Se la voce immessa non inizia con un segno di uguale, non si tratta di una formula e pertanto non viene calcolata. Si tratta di un errore comune.

Se si digita qualcosa di simile a SOMMA(A1:A10), Excel visualizza la stringa di testo SOMMA(A1:A10) invece del risultato di una formula. In alternativa, se si digita 2/11, Excel visualizza una data, ad esempio 02-nov o 02/11/2009 invece di dividere 2 per 11.

Per evitare questi risultati imprevisti, iniziare sempre con un segno di uguale. Ad esempio, digitare =SOMMA(A1:A10) e =2/11.

Quando si usa una funzione in una formula, per il corretto funzionamento della funzione è importante che ogni parentesi sia nella posizione corretta. Verificare che a una parentesi aperta corrisponda sempre una parentesi chiusa. Ad esempio, la formula =SE(B5<0)"Non valido";B5*1,05) non funziona poiché sono presenti due parentesi di chiusura e una sola di apertura. La formula corretta è: =SE(B5<0;"Non valido";B5*1.05).

Le funzioni di Excel includono argomenti, ossia valori che è necessario specificare perché la funzione venga calcolata. Solo per alcune funzioni, ad esempio PI.GRECO o OGGI, non sono previsti argomenti. Verificare la sintassi della formula visualizzata quando si inizia a digitare un segno di uguale seguito da una funzione per assicurarsi di immettere tutti gli argomenti necessari.

La funzione MAIUSC, ad esempio, accetta solo una stringa di testo o riferimento di cella come argomento: =MAIUSC("salve") o =MAIUSC(C2)

Inoltre, alcune funzioni, come SOMMA, richiedono solo argomenti numerici, mentre altre, come SOSTITUISCI, richiedono un valore di testo per almeno uno degli argomenti. Se si usa il tipo di dati non corretto, è possibile che vengano restituiti risultati imprevisti o che venga visualizzato un errore di tipo #VALORE!.

Se è necessario esaminare rapidamente la sintassi di una specifica funzione, vedere l'elenco di funzioni di Excel (per categoria).

Non immettere numeri formattati con il segno di dollaro ($) o separatori decimali (,) nelle formule, in quanto il segno di dollaro indica riferimenti assoluti e le virgole rappresentano i separatori degli argomenti. Invece di immettere $1.000 nella formula, immettere 1000.

Se negli argomenti vengono usati numeri formattati verrà restituito un risultato di calcolo imprevisto oppure l'errore #NUM!. Ad esempio, se si immette la formula =ABS(-2,134) per trovare il valore assoluto -2134, Excel mostra l'errore #NUM! perché funzione ABS accetta un solo argomento e vede -2, e 134 come argomenti separati.

La formula potrebbe non restituire i risultati previsti se il tipo di dati della cella non può essere usato nei calcoli. Ad esempio, se si immette una semplice formula =2+3 in una cella formattata come testo, Excel non riesce a calcolare i dati immessi. Nella cella verrà semplicemente visualizzato =2+3. Per correggere l'errore, modificare il tipo di dati della cella da Testo a Generale, come illustrato di seguito:

1. Selezionare la cella.

2. Selezionare Home e quindi la freccia per espandere il gruppo Numero o Formato numero oppure premere CTRL +1). Selezionare Generale.

3. Premere F2 per attivare la modalità di modifica per la cella e premere INVIO per accettare la formula.

Quando si immette una data in una cella per cui è impostato il tipo di dati Numero, è possibile che venga visualizzato un valore di data numerico invece di una data. Per visualizzare il numero dome data, selezionare un formato Data nella raccolta Formato numero.

In Excel l'operatore per la moltiplicazione dei numeri in una formula non è la consueta x, ma il simbolo asterisco (*). Se si usano costanti nella formula, Excel visualizza un messaggio di errore e può correggere la formula automaticamente sostituendo la x con l'asterisco (*).

Tuttavia, se si usano riferimenti di cella Excel restituisce un errore #NOME?. .

Per creare una formula che include testo, è necessario racchiudere il testo tra virgolette.

La formula ="Oggi è " & TESTO(OGGI();"gggg gg mmmm") combina il testo "Oggi è " con i risultati delle funzioni TESTO e OGGI per restituire la stringa seguente Oggi è lunedì 30 maggio nella cella.

Nella formula, "Oggi è " include un spazio prima delle virgolette di chiusura per fornire lo spazio vuoto necessario tra "Oggi è" e "lunedì 30 maggio". Se non si racchiude il testo tra virgolette, la formula potrebbe visualizza l'errore #NOME?..

È possibile combinare (o annidare) fino a 64 livelli di funzioni in una formula.

Ad esempio, la formula =SE(RADQ(PI.GRECO())<2;"Minore di due!";"Maggiore di due!"), presenta 3 livelli di funzioni: la funzione PI.GRECO è annidata all'interno della funzione RADQ che, a sua volta, è annidata all'interno della funzione SE.

Se si digita un riferimento a valori o celle in un altro foglio di lavoro e il nome del foglio contiene un carattere non alfabetico, ad esempio uno spazio, racchiudere il nome tra virgolette singole (').

Per restituire ad esempio il valore della cella D3 in un foglio di lavoro denominato Dati trimestrali nella cartella di lavoro, digitare ='Dati trimestrali'!D3. Se non si racchiude il nome del foglio tra virgolette, la formula visualizza l'errore #NOME?.

Si possono anche selezionare i valori o le celle di un altro foglio per farvi riferimento nella formula. Excel aggiunge quindi automaticamente le virgolette attorno ai nomi dei fogli.

Quando si digita un riferimento a valori o celle di un'altra cartella di lavoro, includere il nome della cartella di lavoro racchiuso tra parentesi quadre ([]) seguito dal nome del foglio di lavoro che contiene i valori o le celle.

Ad esempio, per fare riferimento all'intervallo di celle A1-A8 nel foglio Vendite nella cartella di lavoro Operazioni T2 aperta in Excel, digitare: =[Operazioni T2.xlsx]Vendite!A1:A8. Se non si includono le parentesi quadre, la formula restituisce l'errore #RIF!..

Se la cartella di lavoro non è aperta in Excel, digitare il percorso completo del file.

Ad esempio, =RIGHE('C:\Documenti\[Operazioni T2.xlsx]Vendite'!A1:A8).

La divisone di una cella per un'altra cella che contiene il valore zero (0) o nessun valore restituisce l'errore #DIV/0!.

Per evitare questo errore, è possibile correggerlo direttamente e verificare la presenza del denominatore. È possibile usare: 

=SE(B1;A1/B1;0)

che significa: SE(B1 esiste, dividere A1 per B1, altrimenti restituire 0).

Prima di eliminare qualsiasi cosa, verificare sempre se una o più formule fanno riferimento a dati contenuti in celle, intervalli, nomi definiti, fogli di lavoro o cartelle di lavoro. È quindi possibile sostituire queste formule con i relativi risultati prima di rimuovere i dati a cui si fa riferimento.

Se non è possibile sostituire le formule con i risultati, rivedere queste informazioni sugli errori e le possibili soluzioni:

• Se una formula fa riferimento a celle eliminate o sostituite con altri dati e restituisce un l'errore #REF!, selezionare la cella con l'errore #REF! . Nella barra della formula selezionare #RIF! ed eliminarlo. Reimmettere quindi l'intervallo per la formula.

• Se manca un nome definito e una formula che fa riferimento a tale nome restituisce un errore #NAME?, definire un nuovo nome che faccia riferimento all'intervallo desiderato oppure modificare la formula in modo che faccia riferimento direttamente all'intervallo di celle, ad esempio A2:D8.

• Se manca un foglio di lavoro e una formula che fa riferimento a tale foglio restituisce un errore #RIF!, non è purtroppo possibile correggere questo errore, poiché un foglio di lavoro eliminato non può essere recuperato.

• Se manca una cartella di lavoro, una formula che fa riferimento a essa rimane intatta finché non si aggiorna la formula.

  Ad esempio, se la formula è =[Cartella1.xlsx]Foglio1'!A1 e Cartella1.xlsx non esiste più, i valori a cui si fa riferimento nella cartella di lavoro rimangono disponibili. Tuttavia, se si modifica e salva una formula che fa riferimento a tale cartella di lavoro, Excel visualizza la finestra di dialogo Aggiorna valori e chiede di immettere un nome file. Selezionare Annulla e quindi verificare che i dati non vadano persi quando si sostituiscono le formule che fanno riferimento alla cartella di lavoro mancante con i risultati della formula.

Talvolta, quando si copia il contenuto della cella, si vorrebbe incollare solo il valore e non la formula sottostante visualizzata nella barra della formula.

Si potrebbe ad esempio voler copiare il valore risultante di una formula in una cella di un altro foglio di lavoro. Oppure potrebbe essere necessario eliminare i valori usati in una formula dopo aver copiato il risultato in un'altra cella del foglio di lavoro. Entrambe queste azioni causano un errore di riferimento di cella non valido (#RIF!) visualizzato nella cella di destinazione, perché non è più possibile fare riferimento alle celle contenenti i valori usati nella formula.

Per evitare questo errore, incollare nelle celle di destinazione i valori risultanti della formula senza la formula.

1. In un foglio di lavoro selezionare le celle contenenti i valori risultanti di una formula da copiare.

2. Nella scheda Home del gruppo Appunti, selezionare Copia .

   

   Scelta rapida da tastiera: premere CTRL+C.

3. Selezionare la cella superiore sinistra dell'area di incollamento.

   Suggerimento: Per spostare o copiare una selezione in una cartella di lavoro o in un foglio di lavoro diverso, selezionare un'altra cartella di lavoro o a un altro foglio di lavoro selezionando la relativa scheda, quindi selezionare la cella superiore sinistra dell'area in cui incollare.

4. Nella Home del gruppo Appunti, selezionare Incolla , quindi selezionare Incolla valori oppure premere ALT > E > S > V > INVIO per Windows, o Opzione > comando > V > V > INVIO in un Mac.

Per capire in che modo una formula complessa o annidata calcola il risultato finale, è possibile valutare tale formula.

1. Selezionare la formula da valutare.

2. Selezionare Formule > Valuta formula.

   

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

   

4. 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 la seconda volta che il riferimento appare nella formula o se la formula fa riferimento a una cella di un'altra cartella di lavoro.

5. Continuare finché ogni parte della formula non è stata valutata.

   Lo strumento Valuta formula non indica necessariamente perché la formula non funziona, ma può indicare il punto in cui è presente l'errore. Questo strumento può risultare molto utile nelle formule più lunghe, in cui sarebbe altrimenti difficile trovare il problema.

   Note: 

   • Alcune parti delle funzioni SE e SCELTA non vengono valutate e potrebbero restituire l'errore #N/D nella casella Valutazione.

   • I riferimenti vuoti vengono visualizzati come valori uguali a zero (0) nella casella Valutazione.

   • Alcune funzioni vengono ricalcolate ogni volta che il foglio di lavoro viene modificato. Sono incluse le funzioni CASUALE, AREE, INDEX, SCARTO, CELLA, INDIRETTO, RIGHE, COLONNE, ADESSO, OGGI e CASUALE.TRA, nella finestra di dialogo Valuta formula potrebbero essere visualizzati risultati diversi dai risultati effettivi nella cella o nel foglio di lavoro.