Come utilizzare la funzione Outlier in Excel

Quando analizzi i dati, i valori anomali possono distorcere i risultati e renderli meno affidabili. Ad esempio, se uno o più dei tuoi valori sono sostanzialmente superiori alla maggior parte dei dati, rendono la media più alta, il che potrebbe non riflettere i dati nel loro insieme. La rimozione di questi punti dati periferici è, quindi, un passaggio importante nell'esecuzione di calcoli statistici affidabili. Senza una funzione outlier dedicata, il modo più semplice per eseguire un test outlier in Excel è usare il intervallo interquartile per identificare gli outlier, ma puoi anche usare la funzione Trimmean per approssimare lo stesso risultato.

L'intervallo di dati interquartile è l'intervallo coperto dalla "scatola" su un diagramma a scatola e baffi, o più appunto, il risultato della sottrazione del valore del primo quartile dei dati da quello del terzo quartile. Excel contiene una funzionalità integrata per calcolare qualsiasi quartile per i tuoi dati. Trova una cella di riserva e digita "=QUARTILE ([intervallo dati], [numero quartile])" e inserisci l'intervallo di celle per i tuoi dati dove dice "[intervallo di dati]" e il quartile che vuoi dove dice "[quartile numero]."

Ad esempio, se sono presenti dati nelle celle da A2 a A101 e si desidera il valore per il primo quartile, immettere "=QUARTILE(A2:A101, 1)" per trovare il valore. Per la prima parte dell'argomento, puoi evidenziare le celle pertinenti con il mouse, ma dopo la virgola devi scrivere il numero del quartile che desideri. Per il terzo quartile con gli stessi dati, digita "=QUARTILE(A2:A101, 3)" per ottenere il risultato.

Usando un'altra cella vuota, sottrai il valore della cella del primo quartile dal valore della cella del terzo quartile. Se il primo quartile è nella cella C2 e il terzo quartile è nella cella D2, digita "= D2-C2" per ottenere il risultato. Questo è l'intervallo interquartile.

Per trovare i valori anomali, ora puoi utilizzare l'intervallo interquartile nella formula dei valori anomali, che afferma che il limite superiore dei dati è il valore del terzo quartile più 1,5 volte l'intervallo interquartile e il limite inferiore è il valore del primo quartile meno 1,5 volte l'intervallo interquartile gamma.

Se il valore del primo quartile è in C2, il valore del terzo quartile è nella cella D2 e ​​l'intervallo interquartile è nella cella E2, digitare "=C2-(1.5 * E2)" per trovare il limite inferiore e "=D2+(1.5 * E2)" per trovare il limite superiore limite. In generale, inserisci "=[primo quartile] – (1.5 * [intervallo interquartile])" per trovare il limite inferiore e "=[terzo quartile] + (1.5 * [intervallo interquartile])" per trovare il limite superiore.

Qualsiasi cosa al di sotto del limite inferiore o al di sopra del limite superiore è un valore anomalo.

Per completare il test degli outlier in Excel, usa la funzione logica "OR" per identificare quali valori nella tua classe di dati sono outlier in modo efficiente. Immettere "=OR([cella dati]>[limite superiore], [cella dati]$F$2, A2

Se il valore in A2 è superiore al limite superiore o inferiore al limite inferiore, viene visualizzato "TRUE", a indicare che il valore è un valore anomalo. Puoi trascinare questa formula verso il basso facendo clic sull'angolo in basso a destra della cella con la formula e trascinandolo verso il basso in modo che finisca accanto alla cella dati finale per eseguire lo stesso calcolo su ciascuna punto dati.

Puoi anche evidenziare i dati e andare su "Formattazione condizionale" nella sezione "Stili" della scheda "Home" se desideri modificare la formattazione per i valori anomali. Scegli "Nuova regola" ed evidenzia l'opzione "Usa una formula per determinare quali celle formattare". Digita la stessa formula del paragrafo precedente, quindi fai clic sull'opzione "Formato" per scegliere la formattazione univoca per i valori anomali.

La funzione "Trimmean" è un modo più semplice per identificare gli outlier. Digita "=TRIMMEAN([data range], [proportion to trim])" per utilizzare la funzione, con l'intervallo di celle che contiene i dati al posto di "[data range]" e un decimale percentuale che vuoi tagliare dove dice "[proporzione da tagliare]". Questo rimuove i valori estremi in alto e in basso e quindi calcola la media in base a quelli residuo. Quindi, se tagliassi il 10 percento, rimuoverebbe il 5 percento in alto e il 5 percento in basso prima di calcolare la media.

Se i dati vanno da A2 a A101 e si desidera tagliare l'estremo 5% dei valori, immettere "=TRIMMEAN(A2:A101, 0.05)" per trovare la media corretta. Puoi tagliare il 15% scrivendo invece "=TRIMMEAN(A2:A101, 0.15)".