Le formule di Excel non funzionano: possibili motivi e come risolverlo!

Se lavori con le formule in Excel, prima o poi incontrerai il problema in cui le formule di Excel non funzionano affatto (o danno il risultato sbagliato).

Anche se sarebbe stato fantastico se ci fossero stati solo pochi possibili motivi per le formule malfunzionanti. Sfortunatamente, ci sono troppe cose che possono andare storte (e spesso succede).

Ma poiché viviamo in un mondo che segue il principio di Pareto, se controlli alcuni problemi comuni, è probabile che risolva l'80% (o forse anche il 90% o il 95% dei problemi in cui le formule non funzionano in Excel).

E in questo articolo, evidenzierò quei problemi comuni che sono probabilmente la causa del tuo Le formule di Excel non funzionano.

Quindi iniziamo!

Sintassi errata della funzione

Vorrei iniziare sottolineando l'ovvio.

Ogni funzione in Excel ha una sintassi specifica, come il numero di argomenti che può accettare o il tipo di argomenti che può accettare.

E in molti casi, il motivo per cui le formule di Excel non funzionano o danno il risultato sbagliato potrebbe essere un argomento errato (o argomenti mancanti).

Ad esempio, la funzione CERCA.VERT accetta tre argomenti obbligatori e un argomento facoltativo.

Se fornisci un argomento sbagliato o non specifichi l'argomento opzionale (dove è necessario affinché la formula funzioni), ti darà un risultato sbagliato.

Ad esempio, supponiamo di avere un set di dati come mostrato di seguito in cui è necessario conoscere il punteggio di Mark in Exam 2 (nella cella F2).

Se utilizzo la formula seguente, otterrò il risultato sbagliato, perché sto utilizzando il valore sbagliato nel terzo argomento (uno che richiede il numero di indice della colonna).

=CERCA.VERT(A2,A2:C6,2,FALSO)

In questo caso, la formula calcola (in quanto restituisce un valore), ma il risultato non è corretto (invece del punteggio nell'Esame 2, dà il punteggio nell'Esame 1).

Un altro esempio in cui è necessario essere cauti sugli argomenti è quando si utilizza VLOOKUP con la corrispondenza approssimativa.

Poiché è necessario utilizzare un argomento facoltativo per specificare dove si desidera che CERCA.VERT esegua una corrispondenza esatta o approssimativa, non specificarlo (o utilizzare l'argomento sbagliato) può causare problemi.

Di seguito è riportato un esempio in cui ho i dati dei voti per alcuni studenti e voglio estrarre i voti nell'esame 1 per gli studenti nella tabella a destra.

Quando utilizzo la formula VLOOKUP sottostante mi dà un errore per alcuni nomi.

=CERCA.VERT(E2,$A$2:$C$6,2)

Ciò accade perché non ho specificato l'ultimo argomento (che viene utilizzato per determinare se eseguire una corrispondenza esatta o una corrispondenza approssimativa). Quando non specifichi l'ultimo argomento, esegue automaticamente una corrispondenza approssimativa per impostazione predefinita.

Poiché in questo caso dovevamo eseguire una corrispondenza esatta, la formula restituisce un errore per alcuni nomi.

Mentre ho preso l'esempio della funzione CERCA.VERT, in questo caso, questo è qualcosa che può essere applicabile a molte formule di Excel che hanno anche argomenti facoltativi.

Leggi anche: Identificare gli errori utilizzando il debug delle formule di Excel

Spazi extra che causano risultati imprevisti

Gli spazi iniziali e finali sono difficili da trovare e possono causare problemi quando si utilizza una cella che li contiene nelle formule.

Ad esempio, nell'esempio seguente, se provo a utilizzare CERCA.VERT per recuperare il punteggio per Mark, mi dà l'errore #N/A (l'errore non disponibile).

Mentre posso vedere che la formula è corretta e il nome "Mark" è chiaramente c'è l'elenco, quello che non riesco a vedere è che c'è uno spazio finale nella cella che ha il nome (nella cella D2).

Excel non considera il contenuto di queste due celle uguale e quindi lo considera una mancata corrispondenza quando si recupera il valore utilizzando CERCA.VERT (o potrebbe essere qualsiasi altra formula di ricerca).

Per risolvere questo problema, devi rimuovere questi caratteri di spazio extra.

Puoi farlo utilizzando uno dei seguenti metodi:

1. Pulisci la cella e rimuovi eventuali spazi iniziali/finali prima di utilizzarla nelle formule
2. Utilizzare la funzione TRIM all'interno della formula per assicurarsi che eventuali spazi iniziali/finali/doppi vengano ignorati.

Nell'esempio sopra, puoi invece utilizzare la formula seguente per assicurarti che funzioni.

=CERCA.VERT(TRIM(D2),$A$2:$B$6,2,0)

Anche se ho preso l'esempio CERCA.VERT, questo è anche un problema comune quando si lavora con le funzioni TESTO.

Ad esempio, se utilizzo la funzione LUNGHEZZA per contare il numero totale di caratteri in una cella, se ci sono spazi iniziali o finali, anche questi verranno conteggiati e daranno il risultato sbagliato.

Porta via / Come risolvereNota: se possibile, pulisci i dati rimuovendo eventuali spazi iniziali/finali o doppi prima di utilizzarli nelle formule. Se non è possibile modificare i dati originali, utilizzare la funzione TRIM nelle formule per occuparsene.

Utilizzo del calcolo manuale anziché automatico

Questa impostazione può farti impazzire (se non sai che è ciò che sta causando tutti i problemi).

Excel ha due modalità di calcolo: Automatico e Manuale.

Per impostazione predefinita, la modalità automatica è abilitata, il che significa che nel caso in cui utilizzo una formula o apporti modifiche alle formule esistenti, automaticamente (e istantaneamente) esegue il calcolo e mi dà il risultato.

Questa è l'impostazione a cui siamo tutti abituati.

Nell'impostazione automatica, ogni volta che si apporta una modifica al foglio di lavoro (come l'inserimento di una nuova formula anche in parte del testo in una cella), Excel ricalcola automaticamente tutto (sì, Tutto quanto).

Ma in alcuni casi, le persone abilitano l'impostazione del calcolo manuale.

Questo viene fatto principalmente quando si dispone di un file Excel pesante con molti dati e formule. In questi casi, potresti non volere che Excel ricalcoli tutto quando apporti piccole modifiche (poiché potrebbero essere necessari alcuni secondi o addirittura minuti) per il completamento di questo ricalcolo.

Se abiliti il ​​calcolo manuale, Excel non calcolerà a meno che tu non lo forzi.

E questo potrebbe farti pensare che la tua formula non stia calcolando.

Tutto quello che devi fare in questo caso è reimpostare il calcolo su automatico o forzare un ricalcolo premendo il tasto F9.

Di seguito sono riportati i passaggi per modificare il calcolo da manuale ad automatico:

1. Fare clic sulla scheda Formula
2. Fare clic su Opzioni di calcolo
3. Seleziona Automatico

Importante: nel caso in cui stai modificando il calcolo da manuale ad automatico, è una buona idea creare un backup della cartella di lavoro (nel caso in cui ciò renda la cartella di lavoro bloccata o faccia crashare Excel)

Porta via / Come risolvereNota: se noti che le tue formule non stanno dando il risultato previsto, prova qualcosa di semplice in qualsiasi cella (ad esempio aggiungendo 1 a una formula esistente. Una volta identificato il problema come quello in cui è necessario modificare la modalità di calcolo, esegui un calcolo della forza usando F9.

Eliminazione di righe/colonne/celle che portano a #REF! Errore

Una delle cose che può avere un effetto devastante sulle formule esistenti in Excel è quando elimini qualsiasi riga/colonna che è stata utilizzata nei calcoli.

Quando ciò accade, a volte, Excel regola il riferimento stesso e si assicura che le formule funzionino correttamente.

E qualche volta… non può.

Per fortuna, una chiara indicazione che si ottiene quando le formule si interrompono durante l'eliminazione di celle/righe/colonne è #REF! errore nelle celle. Questo è un errore di riferimento che ti dice che c'è qualche problema con i riferimenti nella formula.

Lascia che ti mostri cosa intendo usando un esempio.

Di seguito ho usato la formula SUM per aggiungere le celle A2:A6.

Ora, se elimino una di queste celle/righe, la formula SUM restituirà un #RIF! errore. Ciò accade perché quando ho eliminato la riga, la formula non sa a cosa fare riferimento ora.

Puoi vedere che il terzo argomento nella formula è diventato #RIF! (che in precedenza si riferiva alla cella che abbiamo eliminato).

Porta via / Come risolvere: prima di eliminare i dati utilizzati nelle formule, assicurarsi che non vi siano errori dopo l'eliminazione. Ti consigliamo inoltre di creare regolarmente un backup del tuo lavoro per assicurarti di avere sempre qualcosa su cui contare.

Posizionamento errato delle parentesi (BODMAS)

Man mano che le tue formule iniziano a diventare più grandi e complesse, è una buona idea usare le parentesi per essere assolutamente chiari su quale parte appartiene insieme.

In alcuni casi, potresti avere la parentesi nel posto sbagliato, il che può darti un risultato sbagliato o un errore.

E in alcuni casi, si consiglia di utilizzare le parentesi per assicurarsi che la formula comprenda ciò che deve essere raggruppato e calcolato per primo.

Ad esempio, supponiamo di avere la seguente formula:

=5+10*50

Nella formula sopra, il risultato è 505 poiché Excel esegue prima la moltiplicazione e poi l'aggiunta (poiché esiste un ordine di precedenza quando si tratta di operatori).

Se vuoi che faccia prima l'addizione e poi la moltiplicazione, devi usare la formula seguente:

=(5+10)*50

In alcuni casi, l'ordine di precedenza potrebbe funzionare per te, ma è comunque consigliabile utilizzare le parentesi per evitare qualsiasi confusione.

Inoltre, se sei interessato, di seguito è riportato l'ordine di precedenza per i vari operatori spesso utilizzati nelle formule:

Operatore	Descrizione	Ordine di precedenza
: (due punti)	Gamma	1
(spazio singolo)	Intersezione	2
, (virgola)	unione	3
-	Negazione (come in -1)	4
%	Percentuale	5
^	elevazione a potenza	6
* e /	Moltiplicazione e divisione	7
+ e -	Addizione e sottrazione	8
&	concatenazione	9
= =	Confronto	10

Porta via / Come risolvere: utilizzare sempre le parentesi per evitare qualsiasi confusione, anche se si conosce l'ordine di precedenza e lo si utilizza correttamente. La presenza di parentesi semplifica l'audit delle formule.

Uso errato di riferimenti di cella assoluti/relativi

Quando copi e incolli le formule in Excel, regola automaticamente i riferimenti. A volte, questo è esattamente quello che vuoi (soprattutto quando stai copiando e incollando le formule lungo la colonna), ea volte non vuoi che ciò accada.

Un riferimento assoluto è quando correggi un riferimento di cella (o un riferimento di intervallo) in modo che non cambi quando copi e incolli le formule e un riferimento relativo è uno che cambia.

Puoi leggere di più sui riferimenti assoluti, relativi e misti qui.

Potresti ottenere un risultato errato nel caso in cui ti dimentichi di cambiare il riferimento in uno assoluto (o viceversa). Questo è qualcosa che mi succede abbastanza spesso quando uso le formule di ricerca.

Lascia che ti mostri un esempio.

Di seguito ho un set di dati in cui voglio recuperare il punteggio nell'esame 1 per i nomi nella colonna E (un semplice caso d'uso di CERCA.VERT)

Di seguito è riportata la formula che utilizzo nella cella F2 e quindi copia in tutte le celle sottostanti:

=CERCA.VERT(E2,A2:B6,2,0)

Come puoi vedere, questa formula dà un errore in alcuni casi.

Questo accade perché non ho bloccato l'argomento dell'array della tabella: è A2: B6 nella cella F2, mentre avrebbe dovuto essere $A$2:$B$6

Avendo questi simboli del dollaro prima del numero di riga e dell'alfabeto della colonna in un riferimento di cella, sto costringendo Excel a mantenere fissi questi riferimenti di cella. Quindi, anche quando copio questa formula, l'array della tabella continuerà a fare riferimento a A2: B6

Suggerimento professionale: per convertire un riferimento relativo in assoluto, seleziona il riferimento di cella all'interno della cella e premi il tasto F4. Noteresti che cambia aggiungendo i segni del dollaro. Puoi continuare a premere F4 finché non ottieni il riferimento che desideri.

Riferimento errato ai nomi di fogli/cartelle di lavoro

Quando fai riferimento ad altri fogli o cartelle di lavoro in una formula, devi seguire un formato specifico. E nel caso in cui il formato non sia corretto, riceverai un errore.

Ad esempio, se voglio fare riferimento alla cella A1 in Foglio2, il riferimento sarebbe =Foglio2!A1 (dove c'è un punto esclamativo dopo il nome del foglio)

E nel caso in cui ci siano più parole nel nome del foglio (diciamo che sono dati di esempio), il riferimento sarebbe ='Dati di esempio'!A1 (dove il nome è racchiuso tra virgolette singole seguite da un punto esclamativo).

Nel caso in cui ti riferisci a una cartella di lavoro esterna (diciamo che ti riferisci alla cella A1 in "Foglio di esempio" nella cartella di lavoro denominata "Cartella di lavoro di esempio"), il riferimento sarà come mostrato di seguito:

='[Cartella di lavoro di esempio.xlsx]Foglio di esempio'!$A$1

E nel caso in cui chiudi la cartella di lavoro, il riferimento cambierà per includere l'intero percorso della cartella di lavoro (come mostrato di seguito):

='C:\Users\sumit\Desktop\[Example Workbook.xlsx]Example Sheet'!$A$1

Nel caso in cui finisci per cambiare il nome della cartella di lavoro o del foglio di lavoro a cui si riferisce la formula, ti darà un #RIF! errore.

Leggi anche: Come trovare collegamenti e riferimenti esterni in Excel

Riferimenti circolari

Un riferimento circolare è quando si fa riferimento (direttamente o indirettamente) alla stessa cella in cui viene calcolata la formula.

Di seguito è riportato un semplice esempio, in cui utilizzo la formula SUM nella cella A4 mentre la utilizzo nel calcolo stesso.

=SOMMA(A1:A4)

Sebbene Excel ti mostri un prompt che ti informa del riferimento circolare, non lo farà per ogni istanza. E questo potrebbe darti il ​​risultato sbagliato (senza alcun preavviso).

Nel caso in cui sospetti un riferimento circolare in gioco, puoi verificare quali celle lo hanno.

Per fare ciò, fare clic sulla scheda Formula e nel gruppo "Verifica formula", fare clic sull'icona a discesa Controllo errori (la piccola freccia rivolta verso il basso).

Passa il cursore sull'opzione Riferimento circolare e ti mostrerà la cella che ha il problema del riferimento circolare.

Celle formattate come testo

Se ti trovi in ​​una situazione in cui non appena digiti la formula quando premi invio, vedi la formula invece del valore, è un chiaro caso della cella formattata come testo.

Quando una cella è formattata come testo, considera la formula come una stringa di testo e la mostra così com'è.

Non lo costringe a calcolare e dare il risultato.

E ha una soluzione facile.

1. Cambia il formato in "Generale" da "Testo" (è nella scheda Home nel gruppo Numeri)
2. Vai alla cella che ha la formula, entra nella modalità di modifica (usa F2 o fai doppio clic sulla cella) e premi Invio

Nel caso in cui i passaggi precedenti non risolvano il problema, un'altra cosa da verificare è se la cella ha un apostrofo all'inizio. Molte persone aggiungono un apostrofo per convertire formule e numeri in testo.

Se c'è un apostrofo, puoi semplicemente rimuoverlo.

Testo convertito automaticamente in date

Excel ha questa cattiva abitudine di convertire una data simile a una data effettiva. Ad esempio, se inserisci 1/1, Excel lo convertirà in 01-gen dell'anno corrente.

In alcuni casi, questo potrebbe essere esattamente quello che desideri e, in alcuni casi, potrebbe funzionare contro di te.

E poiché Excel memorizza i valori di data e ora come numeri, non appena inserisci 1/1, lo converte in un numero che rappresenta il 1 gennaio dell'anno corrente. Nel mio caso, quando lo faccio, lo converte nel numero 43831 (per il 01-01-2020).

Questo potrebbe rovinare le tue formule se stai usando queste celle come argomento in una formula.

Come risolvere questo problema?

Ancora una volta, non vogliamo che Excel scelga automaticamente il formato per noi, quindi dobbiamo specificare chiaramente il formato da soli.

Di seguito sono riportati i passaggi per modificare il formato in testo in modo che non converta automaticamente il testo in date:

1. Seleziona le celle/l'intervallo in cui desideri modificare il formato
2. Fare clic sulla scheda Home
3. Nel gruppo Numero, fai clic sul menu a discesa Formato
4. Fare clic su Testo

Ora, ogni volta che inserisci qualcosa nelle celle selezionate, verrebbe considerato come testo e non modificato automaticamente.

Nota: i passaggi precedenti funzionerebbero solo per i dati inseriti dopo che la formattazione è stata modificata. Non modificherà alcun testo che è stato convertito in data prima che tu abbia apportato questa modifica di formattazione.

Un altro esempio in cui questo può essere davvero frustrante è quando si immette un testo/numero con zeri iniziali. Excel rimuove automaticamente questi zeri iniziali poiché li considera inutili. Ad esempio, se inserisci 0001 in una cella, Excel lo cambierà in 1. Nel caso in cui desideri mantenere questi zeri iniziali, utilizza i passaggi precedenti.

Righe/colonne nascoste possono dare risultati imprevisti

Questo non è un caso di formula che ti dà il risultato sbagliato, ma di usare la formula sbagliata.

Ad esempio, supponiamo di avere un set di dati come mostrato di seguito e di voler ottenere la somma di tutte le celle visibili nella colonna C.

Nella cella C12, ho utilizzato la funzione SUM per ottenere il valore di vendita totale per tutti questi record.

Fin qui tutto bene!

Ora applico un filtro alla colonna dell'articolo per mostrare solo i record per le vendite della stampante.

Ed ecco il problema: la formula nella cella C12 mostra ancora lo stesso risultato, ovvero la somma di tutti i record.

Come ho detto, la formula non sta dando il risultato sbagliato. In effetti, la funzione SUM funziona perfettamente.

Il problema è che abbiamo usato la formula sbagliata qui.

La funzione SOMMA non può tenere conto dei dati filtrati e fornire il risultato per tutte le celle (nascoste o visibili). Se vuoi solo ottenere la somma/conteggio/media delle celle visibili, usa le funzioni SUBTOTALE o AGGREGATO.

Chiave da asporto - Comprendere l'uso corretto e le limitazioni di una funzione in Excel.

Queste sono alcune delle cause comuni che ho visto dove sei Le formule di Excel potrebbero non funzionare o fornire risultati imprevisti o errati. Sono sicuro che ci sono molte altre ragioni per cui una formula di Excel non funziona o non si aggiorna.

Nel caso in cui conosci altri motivi (forse qualcosa che ti infastidisce spesso), fammi sapere nella sezione commenti.

Spero che tu abbia trovato utile questo tutorial!