Come (e perché) utilizzare la funzione di valori anomali in Excel
Un valore anomalo è un valore significativamente più alto o più basso della 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 davvero riflettere i tuoi valori. Excel offre alcune utili funzioni per aiutare 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 assegnati 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 ciò che sono in un po ').
- Valuta l'intervallo interquartile (ti spiegheremo anche un po 'più in basso).
- Restituisce i limiti superiore e inferiore del nostro intervallo di dati.
- Utilizzare questi limiti per identificare i punti di dati periferici.
L'intervallo di celle sulla destra del set di dati visto nell'immagine sottostante verrà utilizzato per memorizzare questi valori.
Iniziamo.
Fase uno: Calcola i quartili
Se dividi i tuoi dati in quarti, ognuno di questi set è chiamato quartile. Il più basso 25% dei numeri nell'intervallo compongono il 1 ° quartile, il 25% successivo il 2 ° quartile e così via. Facciamo questo primo passo perché la definizione più utilizzata di un outlier è un punto dati che è più di 1.5 intervalli interquartile (IQR) sotto il 1 ° quartile e 1,5 intervalli interquartili sopra il 3 ° quartile. Per determinare questi valori, dobbiamo prima capire cosa sono i quartili.
Excel fornisce una funzione QUARTILE per calcolare i quartili. Richiede due informazioni: la matrice e il quarto.
= QUARTILE (array, quarto)
Il schieramento è l'intervallo di valori che stai valutando. E il quarto di gallone è un numero che rappresenta il quartile che desideri restituire (ad es. 1 per 1st quartile, 2 per il 2 ° 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 1st Quartile possiamo usare la seguente formula nella cella F2.
= QUARTILE (B2: B14,1)
Quando si inserisce la formula, Excel fornisce un elenco di opzioni per l'argomento del quarto.
Per calcolare il 3rd quartile, possiamo inserire una formula come la precedente nella cella F3, ma usando un tre invece di uno.
= QUARTILE (B2: B14,3)
Ora, abbiamo i punti dati quartili 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 primo quartile e il valore del terzo quartile.
Utilizzeremo una semplice formula nella cella F4 che sottrae l'1st quartile dal 3rd quartile:
= F3-F2
Ora, possiamo vedere la nostra gamma interquartile visualizzata.
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. Qualsiasi valore inferiore o superiore a questi valori associati sono i valori anomali.
Calcoleremo il limite inferiore associato 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 di nuovo l'IQR di 1.5, ma questa volta Inserisci al punto dati Q3:
= F3 + (1,5 * F4)
Fase quattro: identificare i valori anomali
Ora che abbiamo impostato tutti i dati sottostanti, è il momento di identificare i nostri punti dati periferici, quelli inferiori al valore limite inferiore o superiore al valore limite superiore.
Useremo la funzione OR per eseguire questo test logico e mostreremo i valori che soddisfano questi criteri inserendo la seguente formula nella cella C2:
= O (B2 $ F $ 6)
Quindi coperemo quel valore nelle nostre celle C3-C14. Un valore VERO indica un valore anomalo e, come puoi vedere, ne abbiamo due nei nostri dati.
Ignorando i valori anomali quando si calcola la media media
Usando la funzione QUARTILE, calcoliamo l'IQR e lavoriamo con la definizione più utilizzata di un valore anomalo. Tuttavia, quando si calcola la media media per un intervallo di valori e si ignorano i valori anomali, è possibile utilizzare una funzione più rapida e semplice. Questa tecnica non identificherà un outlier come prima, ma ci consentirà di essere flessibili con ciò che potremmo considerare la nostra porzione anomala.
La funzione di cui abbiamo bisogno si chiama TRIMMEAN e puoi vedere la seguente sintassi:
= TRIMMEAN (matrice, percentuale)
Il schieramento è l'intervallo di valori che vuoi mediare. Il per cento è la percentuale di punti dati da escludere dalla parte superiore e inferiore del set di dati (puoi inserirla come percentuale o valore decimale).
Nel nostro esempio abbiamo inserito la formula seguente nella cella D3 per calcolare la media ed escludere il 20% dei valori anomali.
= TRIMMEAN (B2: B14, 20%)
Lì hai due diverse funzioni per gestire i valori anomali. Sia che tu voglia identificarli per alcune esigenze di reporting o escluderli da calcoli come medie, Excel ha una funzione che si adatta alle tue esigenze.
