Sommario:
Un valore anomalo è un valore notevolmente superiore o inferiore rispetto alla maggior parte dei valori nei dati. Quando si utilizza Excel per analizzare i dati, i valori anomali possono distorcere i risultati. Ad esempio, la media media di un set di dati potrebbe riflettere veramente i tuoi valori. Excel fornisce alcune funzioni utili per aiutarti a gestire i tuoi valori anomali, quindi diamo un’occhiata.
Un rapido esempio
Nell’immagine sottostante, i valori anomali sono ragionevolmente facili da individuare: il valore di due assegnato a Eric e il valore di 173 assegnato a Ryan. In un set di dati come questo, è abbastanza facile individuare e gestire manualmente questi valori anomali.
In un insieme più ampio di dati, non sarà così. Essere in grado di identificare i valori anomali e rimuoverli dai calcoli statistici è importante, ed è quello che vedremo come fare in questo articolo.
Come trovare valori anomali nei dati
Per trovare i valori anomali in un set di dati, utilizziamo i seguenti passaggi:
Calcola il 1 ° e il 3 ° quartile (parleremo di cosa sono tra poco).
Valuta l’intervallo interquartile (spiegheremo anche questi un po ‘più in basso).
Restituisce i limiti superiore e inferiore del nostro intervallo di dati.
Utilizzare questi limiti per identificare i punti dati periferici.
L’intervallo di celle a destra del set di dati visualizzato nell’immagine sottostante verrà utilizzato per memorizzare questi valori.
Iniziamo.
Fase uno: calcolare i quartili
Se dividi i tuoi dati in trimestri, ognuno di questi insiemi viene chiamato quartile. Il 25% più basso dei numeri nell’intervallo costituisce il 1 ° quartile, il successivo 25% il 2 ° quartile e così via. Facciamo questo primo passo perché la definizione più utilizzata di un valore anomalo è un punto dati che è più di 1,5 intervalli interquartili (IQR) al di sotto del 1 ° quartile e 1,5 intervalli interquartili sopra il 3 ° quartile. Per determinare questi valori, dobbiamo prima capire quali sono i quartili.
Excel fornisce una funzione QUARTILE per calcolare i quartili. Richiede due informazioni: l’array e il quarto.
=QUARTILE(array, quart)
La matrice è l’intervallo di valori che stai valutando. E il quarto è un numero che rappresenta il quartile che desideri restituire (ad esempio, 1 per il primo quartile, 2 per il secondo quartile e così via).
Nota: in Excel 2010, Microsoft ha rilasciato le funzioni QUARTILE.INC e QUARTILE.EXC come miglioramenti alla funzione QUARTILE. QUARTILE è più compatibile con le versioni precedenti quando si lavora su più versioni di Excel.
Torniamo alla nostra tabella di esempio.
Per calcolare il 1 ° quartile possiamo usare la seguente formula nella cella F2.
=QUARTILE(B2:B14,1)
Quando si immette la formula, Excel fornisce un elenco di opzioni per l’argomento quarto.
Per calcolare il 3 ° quartile, possiamo inserire una formula come la precedente nella cella F3, ma utilizzando un tre invece di uno.
=QUARTILE(B2:B14,3)
Ora, abbiamo i punti dati del quartile visualizzati nelle celle.
Fase due: valutare l’intervallo interquartile
L’intervallo interquartile (o IQR) è il 50% medio dei valori nei dati. Viene calcolato come differenza tra il valore del 1 ° quartile e il valore del 3 ° quartile.
Useremo una semplice formula nella cella F4 che sottrae il 1 ° quartile dal 3 ° quartile:
=F3-F2
Ora possiamo vedere il nostro intervallo interquartile visualizzato.
Fase tre: restituire i limiti inferiore e superiore
I limiti inferiore e superiore sono i valori più piccoli e più grandi dell’intervallo di dati che vogliamo utilizzare. Tutti i valori più piccoli o più grandi di questi valori limite sono i valori anomali.
Calcoleremo il limite inferiore nella cella F5 moltiplicando il valore IQR per 1,5 e quindi sottraendolo dal punto dati Q1:
=F2-(1.5*F4)
Nota: le parentesi in questa formula non sono necessarie perché la parte di moltiplicazione verrà calcolata prima della parte di sottrazione, ma rendono la formula più facile da leggere.
Per calcolare il limite superiore nella cella F6, moltiplicheremo nuovamente l’IQR per 1,5, ma questa volta lo aggiungiamo al punto dati Q3:
=F3+(1.5*F4)
Fase quattro: identificare i valori anomali
Ora che abbiamo impostato tutti i nostri dati sottostanti, è il momento di identificare i nostri punti dati esterni, quelli che sono inferiori al valore del limite inferiore o superiori al valore del limite superiore.
Useremo il file Funzione OR per eseguire questo test logico e mostrare i valori che soddisfano questi criteri inserendo la seguente formula nella cella C2:
=OR(B2$F$6)
Copieremo quindi quel valore nelle nostre celle C3-C14. Un valore TRUE indica un valore anomalo e, come puoi vedere, ne abbiamo due nei nostri dati.
Ignorare i valori anomali durante il calcolo della media media
Utilizzando la funzione QUARTILE, calcoliamo l’IQR e lavoriamo con la definizione più diffusa di outlier. Tuttavia, quando si calcola la media media per un intervallo di valori e si ignorano i valori anomali, è disponibile una funzione più rapida e semplice da utilizzare. Questa tecnica non identificherà un valore anomalo come prima, ma ci permetterà di essere flessibili con ciò che potremmo considerare la nostra parte anomala.
La funzione di cui abbiamo bisogno si chiama TRIMMEAN e puoi vedere la sintassi per essa di seguito:
=TRIMMEAN(array, percent)
L’array è l’intervallo di valori che vuoi calcolare nella media. La percentuale è la percentuale di punti dati da escludere dalla parte superiore e inferiore del set di dati (è possibile immetterla come percentuale o come valore decimale).
Abbiamo inserito la formula seguente nella cella D3 nel nostro esempio per calcolare la media ed escludere il 20% dei valori anomali.
=TRIMMEAN(B2:B14, 20%)
Sono disponibili due diverse funzioni per la gestione dei valori anomali. Sia che tu voglia identificarli per alcune esigenze di reportistica o escluderli da calcoli come le medie, Excel ha una funzione adatta alle tue esigenze.