Quando lavori con i dati in Excel, avrai spesso problemi di gestione degli outlier nel tuo set di dati.
Avere valori anomali è abbastanza comune in tutti i tipi di dati ed è importante identificare e trattare questi valori anomali per assicurarsi che l'analisi sia corretta e più significativa.
In questo tutorial, te lo mostrerò come trovare valori anomali in Excele alcune delle tecniche che ho usato nel mio lavoro per gestire questi valori anomali.
Cosa sono gli outlier e perché è importante trovarli?
Un valore anomalo è un punto dati che si trova ben oltre gli altri punti dati nel set di dati. Quando hai un valore anomalo nei dati, può distorcere i tuoi dati che possono portare a deduzioni errate.
Ti faccio un semplice esempio.
Diciamo che 30 persone stanno viaggiando in un autobus dalla destinazione A alla destinazione B. Tutte le persone sono in un gruppo di peso e reddito simili. Ai fini di questo tutorial, consideriamo il peso medio di 220 libbre e il reddito medio annuo di $ 70.000.
Ora da qualche parte nel mezzo del nostro percorso, l'autobus si ferma e Bill Gates salta dentro.
Ora, cosa pensi che questo farebbe al peso medio e al reddito medio delle persone sull'autobus.
Sebbene il peso medio non cambi molto, il reddito medio delle persone sull'autobus salirà alle stelle.
Questo perché il reddito di Bill Gates è un valore anomalo nel nostro gruppo e questo ci dà un'interpretazione errata dei dati. Il reddito medio per ogni persona sull'autobus sarebbe di qualche miliardo di dollari, che è ben oltre il valore effettivo.
Quando si lavora con set di dati effettivi in Excel, è possibile avere valori anomali in qualsiasi direzione (ad esempio, valori anomali positivi o negativi).
E per assicurarti che la tua analisi sia corretta, devi in qualche modo identificare questi valori anomali e quindi decidere come trattarli al meglio.
Ora vediamo un paio di modi per trovare valori anomali in Excel.
Trova valori anomali ordinando i dati
Con piccoli set di dati, un modo rapido per identificare i valori anomali è semplicemente ordinare i dati e passare manualmente attraverso alcuni dei valori nella parte superiore di questi dati ordinati.
E poiché potrebbero esserci valori anomali in entrambe le direzioni, assicurati di ordinare prima i dati in ordine crescente e poi in ordine decrescente e quindi passare attraverso i valori superiori.
Lascia che ti mostri un esempio.
Di seguito ho un set di dati in cui ho le durate delle chiamate (in secondi) per 15 chiamate al servizio clienti.
Di seguito sono riportati i passaggi per ordinare questi dati in modo da poter identificare i valori anomali nel set di dati:
- Seleziona l'intestazione della colonna che desideri ordinare (cella B1 in questo esempio)
- Fare clic sulla scheda Home
- Nel gruppo Modifica, fai clic sull'icona Ordina e filtra.
- Fare clic su Ordinamento personalizzato
- Nella finestra di dialogo Ordina, seleziona "Durata" nel menu a discesa Ordina per e "Dal più grande al più piccolo" nel menu a discesa Ordine
- Fare clic su OK
I passaggi precedenti ordinano la colonna della durata della chiamata con i valori più alti in alto. Ora puoi scansionare manualmente i dati e vedere se ci sono valori anomali.
Nel nostro esempio, posso vedere che i primi due valori sono molto più alti del resto dei valori (e gli ultimi due sono molto più bassi).
Nota: questo metodo funziona con piccoli set di dati in cui è possibile eseguire la scansione manuale dei dati. Non è un metodo scientifico ma funziona bene
Trovare valori anomali usando le funzioni quartili
Ora parliamo di una soluzione più scientifica che può aiutarti a identificare se ci sono valori anomali o meno.
Nelle statistiche, un quartile è un quarto del set di dati. Ad esempio, se hai 12 punti dati, il primo quartile sarebbe i tre punti dati inferiori, il secondo quartile sarebbero i tre punti dati successivi e così via.
Di seguito è riportato il set di dati in cui voglio trovare i valori anomali. Per fare ciò, dovrò calcolare il 1° e il 3° quartile, quindi utilizzandolo calcolare il limite superiore e inferiore.
Di seguito è la formula per calcolare il primo quartile nella cella E2:
=QUARTILE.INC($B$2:$B$15,1)
ed ecco quello per calcolare il terzo quartile nella cella E3:
=QUARTILE.INC($B$2:$B$15,3)
Ora, posso usare i due calcoli precedenti per ottenere l'intervallo interquartile (che è il 50% dei nostri dati all'interno del 1° e del 3° quartile)
=F3-F2
Ora useremo l'intervallo interquartile per trovare il limite inferiore e superiore che conterrebbe la maggior parte dei nostri dati.
Tutto ciò che è al di fuori di questi limiti inferiore e superiore sarebbe quindi considerato outlier.
Di seguito la formula per calcolare il limite inferiore:
=Quartile1 - 1.5*(Intervallo interquartile)
che nel nostro esempio diventa:
=F2-1.5*F4
E la formula per calcolare il limite superiore è:
=Quartile3 + 1.5*(Intervallo interquartile)
che nel nostro esempio diventa:
=F3+1.5*F4
Ora che abbiamo il limite superiore e inferiore nel nostro set di dati, possiamo tornare ai dati originali e identificare rapidamente quei valori che non rientrano in questo intervallo.
Un modo rapido per farlo sarebbe controllare ogni valore e restituire un VERO o FALSO in una nuova colonna.
Ho usato la formula OR sottostante per ottenere TRUE per quei valori che sono valori anomali.
=OR(B2$F$6)
Ora puoi filtrare la colonna Outlier e mostrare solo i record in cui il valore è TRUE.
In alternativa, puoi anche utilizzare la formattazione condizionale per evidenziare tutte le celle in cui il valore è VERO
Nota: Anche se questo è un metodo più accettato per trovare valori anomali nelle statistiche. Trovo questo metodo un po' inutilizzabile in scenari di vita reale. Nell'esempio sopra, il limite inferiore calcolato dalla formula è -103, mentre il set di dati che abbiamo può essere solo positivo. Quindi questo metodo può aiutarci a trovare valori anomali in una direzione (valori alti), è inutile identificare valori anomali nell'altra direzione.Trovare i valori anomali utilizzando le funzioni LARGE/SMALL
Se lavori con molti dati (valori in più colonne), puoi estrarre i valori 5 o 7 più grandi e più piccoli e vedere se sono presenti valori anomali.
Se ci sono valori anomali, sarai in grado di identificarli senza dover esaminare tutti i dati in entrambe le direzioni.
Supponiamo di avere il set di dati seguente e di voler sapere se ci sono valori anomali.
Di seguito è la formula che ti darà il valore più grande nel set di dati:
=GRANDE($M$2:$M$16,1)
Allo stesso modo, il secondo valore più grande sarà dato da
=GRANDE($M$2:$M$16,1)
Se non stai utilizzando Microsoft 365, che dispone di array dinamici, puoi utilizzare la formula seguente e ti darà i cinque valori più grandi del set di dati con una singola formula:
=GRANDE($B$2:$B$16, RIGA($1:5))
Allo stesso modo, se vuoi i 5 valori più piccoli, usa la formula seguente:
=PICCOLO($B$2:$B$16, RIGA($1:5))
o quanto segue nel caso in cui non disponi di array dinamici:
=PICCOLO($M$2:$M$16,1)
Una volta ottenuti questi valori, è davvero facile scoprire eventuali valori anomali nel set di dati.
Mentre ho scelto di estrarre i 5 valori più grandi e più piccoli, puoi scegliere di ottenere 7 o 10 in base a quanto è grande il tuo set di dati.
Non sono sicuro che questo sia un metodo accettabile per trovare valori anomali in Excel o meno, ma questo è il metodo che ho usato quando ho dovuto lavorare con molti dati finanziari nel mio lavoro alcuni anni fa. Rispetto a tutti gli altri metodi trattati in questo tutorial, ho trovato questo il più efficace.
Come gestire gli outlier nel modo giusto
Finora abbiamo visto i metodi che ci aiuteranno a trovare i valori anomali nel nostro set di dati. Ma cosa fare una volta che si sa che ci sono valori anomali.
Ecco un paio di metodi che puoi utilizzare per gestire gli outlier in modo che l'analisi dei dati sia corretta.
Elimina gli outlier
Il modo più semplice per rimuovere gli outlier dal tuo set di dati è semplicemente eliminarli. In questo modo non distorcerà la tua analisi.
È una soluzione più praticabile quando si dispone di set di dati di grandi dimensioni e l'eliminazione di un paio di valori anomali non influirà sull'analisi complessiva. E, naturalmente, prima di eliminare i dati assicurati di creare una copia e approfondire cosa sta causando questi valori anomali.
Normalizza i valori anomali (regola il valore)
Normalizzare i valori anomali è quello che facevo quando ero nel mio lavoro a tempo pieno. Per tutti i valori anomali, li cambierei semplicemente in un valore leggermente superiore al valore massimo nel set di dati.
Questo ha assicurato che non stavo cancellando i dati ma allo stesso tempo non gli permettevo di distorcere i miei dati.
Per darti un esempio reale, se stai analizzando il margine di profitto netto delle aziende, dove la maggior parte delle aziende si trova tra -10% e 30%, e ci sono un paio di valori che sono superiori al 100%, io cambierebbe semplicemente questi valori anomali al 30% o al 35%.
Quindi questi sono alcuni dei metodi che puoi usare in Excel per trovare valori anomali.
Una volta identificati i valori anomali, puoi approfondire i dati e cercare cosa li sta causando, allo stesso tempo scegliere una delle tecniche per gestire questi valori anomali (che potrebbe rimuoverli o normalizzarli regolando il valore)
Spero che tu abbia trovato utile questo tutorial.
