Basta uno zero in più perché un ordine da 150 diventi 1500 e il totale del mese schizzi verso l’alto. Gli errori di inserimento raramente saltano all’occhio in un foglio con centinaia di righe, ma falsano medie, grafici e decisioni.
Qui imparerai a trovare i valori sospetti con metodi semplici e affidabili: un primo sguardo con le funzioni di base, il metodo dello scarto interquartile, il punteggio z, la formattazione condizionale e alcune formule per errori specifici. Alla fine vedrai come evitare che gli errori entrino nel foglio.
Anomalo non significa sempre errore
Un valore anomalo è un valore molto lontano dagli altri. Può essere un errore, ma anche un dato reale: un cliente che ha fatto un ordine enorme o un mese con vendite eccezionali. Per questo la regola d’oro è segnalare, controllare e poi decidere, mai cancellare in automatico.
Gli errori di inserimento più comuni sono:
- Zeri in più o in meno: 1500 invece di 150.
- Separatore decimale sbagliato: 1250 invece di 12,50.
- Cifre invertite: 516 invece di 156.
- Unità mescolate: grammi in una colonna di chili.
- Valori impossibili: quantità negative, età di 250 anni o date future in un registro vendite.
- Testo in colonne numeriche: “n/d”, “in sospeso” o un trattino.
- Record duplicati: la stessa riga inserita due volte.
Primo sguardo: minimo, massimo e mediana
Prima di applicare metodi più raffinati, calcola tre valori sulla colonna (nell’esempio, importi in B2:B200):
=MIN(B2:B200)
=MAX(B2:B200)
=MEDIANA(B2:B200)
Se la mediana è 140 e il massimo è 14000, sai già da dove cominciare. Confronta anche la mediana con MEDIA: quando la media è molto più alta della mediana, pochi valori estremi la stanno trascinando verso l’alto.
Un altro modo rapido è ordinare la colonna dal più grande al più piccolo con Dati › Ordina e controllare le prime e le ultime righe. Se non vuoi perdere l’ordine originale, aggiungi prima una colonna con numeri progressivi per poterlo ripristinare.
Il metodo dello scarto interquartile
È il metodo più robusto per i fogli di calcolo, perché non si lascia influenzare dagli stessi valori estremi. Divide i dati in quattro parti e considera anomalo ciò che resta molto lontano dalla metà centrale.
- Calcola il primo quartile in F1:
=INC.QUARTILE($B$2:$B$200;1) - Calcola il terzo quartile in F2:
=INC.QUARTILE($B$2:$B$200;3) - Calcola lo scarto interquartile in F3:
=F2-F1 - Limite inferiore in F4:
=F1-1,5*F3 - Limite superiore in F5:
=F2+1,5*F3
Poi, in una colonna di appoggio, segnala ogni riga che resta fuori da questi limiti:
=O(B2<$F$4;B2>$F$5)
Con dati di esempio si presenta così:
| Ordine | Importo | Anomalo? |
|---|---|---|
| P-101 | 145 | FALSO |
| P-102 | 160 | FALSO |
| P-103 | 1500 | VERO |
| P-104 | 138 | FALSO |
| P-105 | 15,2 | VERO |
Il fattore 1,5 è quello abituale. Se vuoi segnalare solo i casi più estremi, usa 3.
Il punteggio z
Il punteggio z indica a quante deviazioni standard si trova ogni valore dalla media. In C2:
=(B2-MEDIA($B$2:$B$200))/DEV.ST.C($B$2:$B$200)
E per segnalare i valori lontani:
=ASS(C2)>3
Un valore assoluto superiore a 3 è molto raro se i dati seguono una distribuzione normale. Tieni presenti due limiti: gli stessi valori anomali gonfiano la deviazione standard e possono nascondersi, e con pochi dati (meno di 30, per esempio) il metodo è poco affidabile. In questi casi lo scarto interquartile funziona meglio.
Evidenziare i valori sospetti con la formattazione condizionale
Se preferisci vedere i valori anomali senza colonne di appoggio:
- Seleziona B2:B200.
- Vai su Home › Formattazione condizionale › Nuova regola.
- Scegli Utilizza una formula per determinare le celle da formattare.
- Scrivi
=O($B2<$F$4;$B2>$F$5)e scegli un colore di riempimento.
Per un’analisi rapida senza calcoli, le Regole primi/ultimi dello stesso menu evidenziano, per esempio, il 10% dei valori più alti. È utile come prima esplorazione, anche se segnalerà sempre qualcosa, che ci siano errori o no.
Formule per errori di inserimento specifici
Testo in una colonna numerica
La differenza tra le celle non vuote e quelle che contengono numeri rivela quanti testi si sono infilati:
=CONTA.VALORI(B2:B200)-CONTA.NUMERI(B2:B200)
Valori impossibili
Conta le quantità negative e le date successive a oggi (con le date nella colonna D):
=CONTA.SE(B2:B200;"<0")
=CONTA.SE(D2:D200;">"&OGGI())
Uno zero o una virgola spostati
Dividi ogni valore per la mediana. Risultati vicini a 10 o a 0,1 indicano di solito uno zero in più o un decimale fuori posto:
=B2/MEDIANA($B$2:$B$200)
Record ripetuti
Per sapere se un numero d’ordine compare più di una volta:
=CONTA.SE($A$2:$A$200;A2)>1
Duplicati e confronto tra versioni
Le righe inserite due volte sono tra gli errori di inserimento più frequenti, e con migliaia di record controllarle a mano non è pratico. Il nostro strumento gratuito per rimuovere i duplicati elimina le righe ripetute da un file Excel, CSV o ODS in pochi secondi. E quando hai finito le correzioni, puoi confrontare il file originale con quello corretto per verificare esattamente quali celle hai modificato.
Come prevenire gli errori con la convalida dei dati
Il modo migliore per ridurre gli errori è impedire che entrino. Con la convalida dei dati, Excel rifiuta i valori fuori dall’intervallo che definisci:
- Seleziona la colonna in cui si inseriscono le quantità.
- Vai su Dati › Convalida dati.
- In Consenti, scegli Numero intero o Decimale e fissa un minimo e un massimo ragionevoli, per esempio tra 1 e 500.
- Nella scheda Messaggio di errore, scrivi un avviso chiaro per chi compila il foglio.
Per i campi con valori fissi, come categorie o stati, usa l’opzione Elenco: eviti varianti come “In sospeso”, “in sospeso” e “Sosp.”. Tieni presente che la convalida non controlla i dati già presenti nel foglio né quelli incollati sopra le celle.
Conclusione
Individuare gli errori di inserimento è questione di metodo: parti da minimo, massimo e mediana, segnala i valori anomali con lo scarto interquartile o il punteggio z, evidenziali con la formattazione condizionale e usa formule specifiche per testi, valori impossibili e duplicati. Controlla ogni caso prima di correggerlo e, per il futuro, proteggi l’inserimento dei dati con la convalida.
Domande frequenti
Quale metodo è migliore, lo scarto interquartile o il punteggio z?
Per la maggior parte dei fogli, lo scarto interquartile, perché gli stessi valori estremi non lo falsano e funziona anche con pochi dati. Il punteggio z è adatto quando i dati sono molti e la distribuzione è più o meno simmetrica.
Devo cancellare i valori anomali?
Solo se confermi che sono errori. Se un valore è reale, conservalo e, se falsa un’analisi, usa la mediana invece della media oppure analizzalo a parte.
Perché INC.QUARTILE restituisce #VALORE! o risultati strani?
INC.QUARTILE ignora il testo, quindi i numeri memorizzati come testo restano fuori dal calcolo. Se l’argomento del quartile non è un numero valido, restituisce un errore. Converti prima i testi in numeri.
Posso individuare i valori anomali per categoria?
Sì, e di solito è la scelta giusta: un importo normale per un prodotto può essere anomalo per un altro. Filtra per categoria e applica il metodo a ogni gruppo, oppure calcola i quartili con FILTRO in Excel 365, per esempio =INC.QUARTILE(FILTRO($B$2:$B$200;$E$2:$E$200=E2);1).
Prova i nostri strumenti gratuiti
Confronta, converti, unisci e pulisci i tuoi fogli di calcolo dal browser, senza installare nulla.
