Come (e perché) usare la funzione Outliers in Excel

• Alan Murray

  @Computergaga1

• Updated January 12, 2019, 5:03am EDT

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. Per esempio, la media media di un set di dati potrebbe riflettere veramente i vostri valori. Excel fornisce alcune funzioni utili per aiutare a gestire i 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 una serie di dati come questa, è abbastanza facile individuare e trattare manualmente questi valori anomali.

In una serie di dati più grande, questo non sarà il caso. 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 gli outlier nei tuoi dati

Per trovare gli outlier in una serie di dati, usiamo i seguenti passi:

1. Calcolare il 1° e il 3° quartile (parleremo di cosa sono tra poco).
2. Valutare l’intervallo interquartile (spiegheremo anche questi tra poco).
3. Ritrova i limiti superiore e inferiore del nostro intervallo di dati.
4. Utilizza questi limiti per identificare i punti di dati fuori scala.

L’intervallo di celle sulla destra del set di dati visto nell’immagine qui sotto sarà usato per memorizzare questi valori.

Iniziamo.

Passo Uno: Calcolare i quartili

Se dividete i vostri dati in quarti, ciascuno di questi insiemi è chiamato quartile. Il 25% più basso dei numeri nell’intervallo costituisce il 1° quartile, il 25% successivo il 2° quartile, e così via. Facciamo prima questo passo perché la definizione più usata di outlier è un punto di dati che è più di 1,5 intervalli interquartili (IQR) sotto il 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: la matrice e il quartile.

=QUARTILE(array, quart)

La matrice è la gamma di valori che state valutando. E il quartile è un numero che rappresenta il quartile che volete restituire (ad esempio, 1 per il 1° 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 all’indietro 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)

Come si inserisce la formula, Excel fornisce una lista di opzioni per l’argomento quartile.

Per calcolare il 3° quartile, possiamo inserire una formula come la precedente nella cella F3, ma usando un tre invece di un 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 tuoi dati. Viene calcolato come la differenza tra il valore del 1° quartile e il valore del 3° quartile.

Utilizzeremo una semplice formula nella cella F4 che sottrae il 1° quartile dal 3° quartile:

=F3-F2

Ora, possiamo vedere visualizzato il nostro intervallo interquartile.

Fase tre: Restituire i limiti inferiori e superiori

I limiti inferiori e superiori 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 gli outlier.

Calcoleremo il limite inferiore nella cella F5 moltiplicando il valore IQR per 1.5 e poi 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, moltiplichiamo l’IQR per 1.5 di nuovo, ma questa volta aggiungendolo al punto dati Q3:

=F3+(1.5*F4)

Fase quattro: Identificare gli Outliers

Ora che abbiamo tutti i nostri dati di base impostati, è il momento di identificare i nostri punti di dati outlying – quelli che sono inferiori al valore limite inferiore o superiori al valore limite superiore.

Utilizzeremo la 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$5,B2>$F$6)

Copieremo quindi questo valore nelle nostre celle C3-C14. Un valore VERO indica un outlier e, come potete vedere, ne abbiamo due nei nostri dati.

Ignorare gli outlier quando si calcola la media media

L’utilizzo della funzione QUARTILE ci permette di calcolare l’IQR e di lavorare con la definizione più diffusa di outlier. Tuttavia, quando si calcola la media media per un intervallo di valori e si ignorano gli outlier, c’è una funzione più semplice e veloce da usare. Questa tecnica non identificherà un outlier come prima, ma ci permetterà di essere flessibili con ciò che potremmo considerare la nostra porzione di outlier.

La funzione di cui abbiamo bisogno si chiama TRIMMEAN, e potete vedere la sintassi per essa qui sotto:

=TRIMMEAN(array, percent)

L’array è l’intervallo di valori di cui volete fare la media. La percentuale è la percentuale di punti di dati da escludere dalla parte superiore e inferiore dell’insieme di dati (potete inserirla 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%)

Qui avete due funzioni diverse per gestire gli outlier. Sia che vogliate identificarli per alcune esigenze di reporting o escluderli da calcoli come le medie, Excel ha una funzione che si adatta alle vostre esigenze.