Un outlier è un valore che è significativamente più alto o più basso della maggior parte dei valori nei tuoi dati. Quando si utilizza Excel per analizzare i dati, i valori anomali possono distorcere i risultati. Come esempio, la media media di un insieme di dati potrebbe riflettere veramente i suoi valori. Excel fornisce alcune funzioni utili per aiutarti a gestire i tuoi valori anomali, quindi diamo un'occhiata.
Un rapido esempio
Nell'immagine qui sotto, 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 rilevare e gestire manualmente questi valori anomali.
In un set di dati più ampio, non sarà così. Essere in grado di identificare gli outlier e rimuoverli dai calcoli statistici è essenziale, ed è quello che vedremo in questo post.
Come trovare valori anomali nei tuoi dati
Per trovare valori anomali in un set di dati, usiamo i seguenti passaggi:
- Trova il primo e il terzo quartile (parleremo un po' di cosa sono).
- Valutare l'intervallo interquartile (lo spiegheremo anche un po' più avanti).
- 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.
Primo passo: calcola i quartili
Se dividi i tuoi dati in quarti, ciascuno di questi insiemi è chiamato quartile. Il 25% il numero più basso di numeri nell'intervallo costituisce il primo quartile, il prossimo 25% il secondo quartile, e così via. Facciamo prima questo passaggio perché la definizione più comunemente usata di un valore anomalo è un punto dati che è più di 1,5 intervalli interquartili (IQR) sotto il primo quartile e 1,5 intervalli interquartili al di sopra del terzo quartile. Per impostare quei valori, prima dobbiamo scoprire quali sono i quartili.
Excel fornisce una funzione QUARTILE per calcolare i quartili. Hai bisogno di due informazioni: la matrice e il quarto.
=QUARTILE(Array, Quart)
il addestramento è l'intervallo di valori che si sta valutando. E il Quart è un numero che rappresenta il quartile che si desidera restituire (come esempio, 1 per lui 1Ns quartile, 2 per il secondo quartile e così via).
Nota: In excel 2010, Microsoft ha rilasciato le funzionalità del quartile. INC e QUARTILE. EXC come miglioramento della funzione QUARTILE. Quartile è più compatibile con le versioni precedenti quando si lavora in più versioni di Excel.
Torniamo alla nostra tabella di esempio.
Per calcolare il 1Ns Quartile possiamo usare la formula successiva nella cella F2.
=QUARTILE(B2:B14,1)
Quando inserisci la formula, Excel fornisce un elenco di alternative per l'argomento di un quarto.
Per calcolare il 3rd quartile, possiamo inserire una formula come sopra nella cella F3, ma usando un tre invece di uno.
=QUARTILE(B2:B14,3)
Ora, abbiamo i punti dati del quartile visualizzati nelle celle.
Passo due: esaminare l'intervallo interquartile
L'intervallo interquartile (o IQR) è lui 50% media dei valori nei tuoi dati. Si calcola come differenza tra il valore del primo quartile e il valore del terzo quartile.
Useremo una semplice formula nella cella F4 che sottrae il 1Ns quartile di 3rd pastorale:
=F3-F2
Ora, possiamo vedere il nostro intervallo interquartile mostrato.
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 limite sono i valori anomali.
Calcoleremo il limite inferiore nella cella F5 moltiplicando il valore IQR per 1.5 e successivamente 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ù leggibile.
Per calcolare il limite superiore nella cella F6, moltiplicheremo l'IQR per 1.5 ancora, ma questa volta Inserisci al punto dati Q3:
=F3+(1.5*F4)
Fase quattro: identificare i valori anomali
Ora che abbiamo impostato tutti i nostri dati sottostanti, è ora di identificare i nostri punti dati periferici, quelli che sono inferiori al valore limite inferiore o superiori al valore limite superiore.
Useremo il Funzione OR per eseguire questo test logico e visualizzare i valori che soddisfano questi criteri inserendo la formula successiva nella cella C2:
=O(B2<$F$5,B2>$F$ 6)
Quindi copieremo quel valore nelle nostre celle C3-C14. Un valore VERO indica un valore anomalo e, come potete vedere, ne abbiamo due nei nostri dati.
Ignora i valori anomali durante il calcolo della media
L'utilizzo della funzione QUARTILE ci permette di calcolare l'IQR e lavorare con la definizione più utilizzata di outlier.. Nonostante questo, quando si calcola la media media per un intervallo di valori e si ignorano i valori anomali, c'è una funzione più veloce e più facile da usare. Questa tecnica non identificherà un valore anomalo come prima, ma ci permetterà di essere flessibili con quella che potremmo considerare la nostra parte di valori anomali.
La funzione di cui abbiamo bisogno si chiama TRIMMEAN, e puoi vedere la sua sintassi qui sotto:
= TRIMMEAN(Array, per cento)
il addestramento è 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 inserirlo come una percentuale o un valore decimale).
Inseriamo la formula seguente nella cella D3 nel nostro esempio per calcolare la media ed escludere il 20% valori anomali.
= TRIMMEAN(B2:B14, 20%)
Lì hai due diverse funzioni per gestire gli outlier. Sia che tu voglia identificarli per alcune esigenze di reporting o escluderli dai calcoli come medie, Excel ha una funzione che si adatta alle tue esigenze.
