“Oh mio Dio” le formule di excel non funzionano nel mio report.
Suona familiare vero?
Se è così, non preoccuparti, sei solo uno dei tanti utenti di excel che affrontano questo problema molto spesso.
Pensaci.
Hai costruito un modello excel per le previsioni di vendita usando varie logiche con formule abbastanza complicate.
E quando provi a usare quel modello, vedi che alcune delle tue formule non funzionano o sembrano rotte.
Fondamentalmente, due cose possono succedere qui:
Uno potrebbe voler dire qualcosa ad alta voce.
Secondo, si fa uno sforzo extra e si sistemano tutte le formule.
Quindi, per evitare tutto questo fastidio abbiamo scritto questo articolo approfondito che copre la maggior parte dei motivi per cui le formule di excel non funzionano.
E come sistemarle.
Le formule di excel non funzionano: Perché & Come risolverli
Secondo l’articolo del Washington Post sugli errori di Excel, “I ricercatori australiani hanno scoperto che circa 1 su 5 di questi documenti includevano errori nelle loro liste di geni che erano dovuti a Excel che convertiva automaticamente i nomi dei geni in cose come date del calendario o numeri casuali.”
La ricerca originale è stata condotta da Journal Genome Biology ha trovato che c’è una tendenza crescente anno dopo anno in termini di numero di errori.
È possibile scaricare il materiale completo della ricerca dal sito SourceForge (Open Source) per ulteriori letture.
La ricerca di cui sopra potrebbe non essere direttamente collegata alle formule che non funzionano in Excel, ma c’è un punto qui:
Molto ben costruito & software testato come MS Excel può andare male in termini di comprensione dei nostri dati che dire di voi & me?
Quindi, ecco le nostre 10 ragioni principali
#1 Opzioni di calcolo
Cambiare l’opzione di calcolo a ‘manuale’ è una buona pratica ma è necessario fare molta attenzione durante l’utilizzo.
Di default, l’opzione di calcolo sarà impostata su automatico in Excel.
Ma ci sono casi in cui potresti averla impostata su ‘manuale’ per accelerare il processo.
Se attivate questa opzione ‘ON’ allora le vostre formule non saranno aggiornate automaticamente, questo a sua volta aumenta la velocità di elaborazione del foglio di calcolo.
Una volta che avete finito, dovete cliccare su Salva o Aggiorna cartella di lavoro per ricalcolare. Se non l’avete fatto, vi ritroverete con risultati errati.
Come risolvere questo problema:
Andate su Formule → Calcolo → Cliccate su Calcola ora/Calcola foglio.
In alternativa potete usare il tasto di scelta rapida F9 per aggiornare la cartella di lavoro.
Qui trovate la nostra video guida sulle opzioni di calcolo di excel.
Suggerimenti:
- Prendi l’abitudine di premere Ctl+S
- Non usare questa opzione per ogni cartella di lavoro, a meno che non abbiate un file enorme con molte formule
- Questa opzione è specifica della cartella di lavoro, quindi è necessario attivare/disattivare in ogni cartella di lavoro che si desidera utilizzare
#2 Excel formula non calcola solo mostrando la formula
Mostra formule è un’opzione in Excel per visualizzare tutte le formule della cartella di lavoro con un solo clic.
Questa opzione è molto utile per scoprire tutte le formule in un foglio di lavoro.
Detto questo, dobbiamo stare un po’ attenti mentre usiamo l’opzione mostra formule, altrimenti, si potrebbe finire con qualcosa come sotto.
Puoi vedere che la formula Oggi viene visualizzata come una formula invece di mostrare la data effettiva?
Lo stesso vale anche per le formule Randbetween.
Come risolvere questo problema:
È molto semplice:
Vai a Formule → Sotto la formula Controllo → Clicca su “Mostra formule” (Con il primo clic le formule appariranno e con il secondo clic si nasconderanno dietro le celle e inizieranno a lavorare)
Leggi anche: 51 migliori consigli e trucchi per excel
#3 Spazio/i extra è un mal di testa in più
Si sono estratti dati usando il formato CSV (valori separati da virgola), quindi i dati sono per lo più esposti a spazi indesiderati.
Sai che gli spazi extra causano molti problemi quando usi la formula Vlookup?
A volte è abbastanza difficile identificare questi spazi.
Per esempio, diciamo che se vuoi fare Vlookup usando il nome dell’impiegato come base, allora il tuo nome dell’impiegato appare come sotto
Hai notato?
Anche se entrambi i nomi sembrano simili ad occhio nudo, c’è uno spazio vuoto nel secondo nome.
Questo non soddisferebbe i criteri di corrispondenza esatta nella formula di Vlookup e quindi risulta in un errore #N/A.
Come risolvere questo problema:
Il problema dello spazio extra può essere risolto usando la formula Trim.
In generale, la formula Trim troverà più di uno spazio tra le parole e lo cancellerà.
Suggerimenti:
- È sempre una buona idea usare la formula Trim prima di Vlookup in entrambe le tabelle (lookup & tabella dati)
- La funzione trim ignora gli spazi vuoti tra le lettere (funziona solo con le parole)
#4 Sbarazzati dei caratteri non stampabili/nascosti
Simile agli spazi iniziali, i caratteri non stampabili sono anche una delle ragioni per cui le formule di Excel non funzionano.
Nel caso, se avete dati con questi caratteri, è meglio toglierli prima di lavorare ulteriormente.
Come risolvere questo problema:
Simile a tagliare, potete usare la formula CLEAN per eliminare questi caratteri.
#5 Formattazione Excel – non cercare di confrontare mele con arance
È molto importante avere una formattazione corretta prima di usare formule come Vlookup, Hlookup, e Match & Index.
I numeri formattati come testo sono una delle ragioni comuni per cui le formule non funzionano in Excel.
Anche se sembrano numeri ma non lo sono.
Per esempio, sul lato sinistro, potete vedere che l’ID cliente sembra simile ai numeri.
Ma, se fate riferimento alla sezione di formattazione precedente, potete vedere che sono stati formattati come testo.
Questo, a sua volta, innescherà l’errore #N/A durante l’aggiunta della formula Vlookup.
La parte peggiore è che è un po’ difficile da identificare.
Come risolvere questo problema:
Assicurati che tutti i tuoi numeri siano formattati come numeri invece che come testo. Vale la pena di riformattare la colonna lookup prima di usare la formula Vlookup.
Suggerimenti:
- In generale, i numeri saranno sempre allineati al lato destro della cella (ma nel nostro esempio sopra non lo sono)
- A volte potete vedere un piccolo suggerimento verde in alto a sinistra, che indica qualcosa di sbagliato con quella cella
#6 Riferimenti circolari (Errori dovuti a un cerchio senza fine)
In termini semplici, la vostra formula è in un ciclo senza fine o è in un cerchio.
Secondo le regole di calcolo di Excel, le vostre formule non dovrebbero fare riferimento alle celle dei risultati come un intervallo. Se lo fate, appariranno errori di riferimento circolare.
Personalmente, ho lottato molte volte con questo tipo di errori.
Per esempio, nella nostra tabella di bilancio delle spese qui sotto, abbiamo bisogno di aggiungere qualche tappo (numero di aggiustamento) per abbinare gli obiettivi.
Così, abbiamo calcolato l’importo differenziale nella riga 13, e ora abbiamo solo bisogno di metterli nella riga 9.
Un modo semplice è di collegare le celle della riga 13 nella riga 9.
Se fate così, la vostra formula di collegamento non funzionerà invece, potrete vedere zero ed eventualmente potreste anche vedere delle frecce colorate blu che indicano i riferimenti delle celle.
In generale, dovrebbe funzionare.
Ma tecnicamente, sarà un errore perché i riferimenti alle celle della nostra formula sono in un ciclo.
Come risolvere questo problema:
Prima di tutto, dobbiamo identificare le celle di errore.
Vai alla scheda Formule → clicca sulla freccia a discesa Controllo errori → posiziona il cursore su Riferimenti cella → ora vedrai gli intervalli di errore
Secondo, ora conosci gli intervalli di riferimento circolare, vai ad ogni cella di errore e correggi le formule.
#7 Usare le doppie virgolette in modo errato – errore umano
Spesso usiamo le doppie virgolette mentre scriviamo le formule.
Ma è molto importante capire quando usare e quando no.
Per esempio, per unire due numeri possiamo usare una formula come questa.
=1234&5678 = 12345678
Alternativamente, per unire due stringhe di testo non si può fare lo stesso invece, è necessario specificare il testo tra virgolette doppie.
=John&Smith: Modo sbagliato di usare
=”John”&”Smith” = Questo è il modo corretto.
Lo stesso vale per le formule condizionali come IF, AND, OR. È molto importante specificare il testo tra virgolette doppie.
Altrimenti, questo è uno dei motivi per cui le formule excel non funzionano.
#8 BODMAS – regola di base per ogni calcolo
Sai che ogni calcolo in excel è basato sulla regola ‘BODMAS’?
Oh, aspetta… cos’è la regola BODMAS?
È una regola matematica per determinare un ordine di operazioni.
Ok, semplifichiamo ulteriormente.
Puoi indovinare qual è la risposta della seguente?
10+10*2 =?
È 40, congratulazioni ti sbagli.
Puoi provare lo stesso calcolo in excel?
Molto probabilmente vedrai 30, infatti è la risposta corretta… ma come?
Secondo la regola di BODMAS, il calcolo avverrà in base a una sequenza cioè → B- parentesi, O- ordine, D- divisione, M- moltiplicazione, A- addizione & S- sottrazione.
Quindi, la sequenza della formula precedente è → 10*2 = 20 → 20+10 =30.
Le vostre formule possono andare male se non considerate BODMAS.
Come risolvere questo problema:
È molto semplice, basta cambiare la sequenza usando le parentesi come qui sotto.
=(30+40)*2
Così, qualsiasi cosa tra parentesi sarà calcolata per prima e poi passerà all’operazione successiva.
#9 Uso scorretto della referenziazione “assoluta”
Tutti quelli che usano Excel sanno cos’è la referenziazione assoluta.
Se non lo sapete, fermatevi qui e imparate. Ecco un rapido tutorial sui riferimenti alle celle.
Con l’aiuto della referenziazione assoluta, potete fissare il vostro intervallo di formule in modo da poterle copiare da una cella all’altra.
Questa è davvero una caratteristica molto utile. Detto questo, se non sapete come usarla, allora le vostre formule non funzioneranno come intendono essere.
Certamente, questo è uno dei motivi principali per cui la formula di excel non funziona quando viene copiata da una cella all’altra.
Per esempio, nella nostra tabella qui sopra, abbiamo usato la referenziazione assoluta delle celle per facilitare la copia delle formule.
Ma abbiamo fatto un errore.
Invece di usare il blocco parziale, abbiamo usato il tasto di scelta rapida F4 e abbiamo fissato l’intero intervallo.
Questo ha effettivamente portato a risultati errati.
Quindi, il modo corretto avrebbe dovuto essere usare il riferimento assoluto solo per le righe come questo =SUM(D$3:D$6).
Come risolvere questo problema:
Purtroppo, non ci sono soluzioni veloci per questo tipo di errori. Bisogna farlo manualmente analizzando tutte le formule.
Ma il modo migliore che ho trovato finora è il controllo incrociato dei risultati delle formule & non appena uso la referenziazione relativa assoluta &.
Fammi sapere i tuoi workaround a questo punto.
Suggerimenti:
- Non usare il tasto di scelta rapida F4 per il congelamento parziale
- Prova a includere un segno di dollaro ($) manualmente per l’intervallo parziale
#10 Argomenti formula errati
Gli argomenti formula errati possono avere un impatto negativo sui risultati formula.
Solo per darvi una prospettiva, cerchiamo di capire la sintassi di vlookup qui sotto, specialmente l’ultima.
L’argomento range lookup ha due opzioni da selezionare.
- Vero – Corrispondenza approssimativa
- Falso – Corrispondenza esatta
Personalmente, credo che usare l’opzione “Corrispondenza approssimativa” sia come suicidarsi.
Perché la corrispondenza approssimativa non restituirà mai risultati accurati.
Nel nostro esempio precedente, abbiamo aggiunto la formula Vlookup nella cella F3 per trovare l’importo delle entrate corrispondenti.
Se osservate attentamente, l’importo delle entrate 25k per il cliente id 6378993 non è corretto. Avrebbe dovuto essere 65k invece.
Quindi, cosa è andato storto?
Come ho detto prima, ho usato ‘Approximate match’ sotto l’argomento range lookup.
Il “match” è un’altra formula dove avete un’opzione per selezionare argomenti simili.
Come risolvere questo problema:
Non usare mai ‘Approximate match’ nelle tue formule vlookup.
Ora tocca a te
Pensi di avere altre ragioni per cui le formule excel non funzionano?
O, forse hai una domanda da fare.