Excel: come calcolare la media escludendo i valori anomali

Esistono due modi per calcolare una media escludendo i valori anomali in Excel:

1. Calcolare la media e utilizzare TRIMMEAN per escludere valori anomali

2. Calcolare la media e utilizzare l’intervallo interquartile per escludere valori anomali

Utilizzeremo il seguente set di dati in Excel per illustrare come utilizzare entrambi i metodi:

Metodo 1: calcolare la media e utilizzare TRIMMEAN per escludere valori anomali

La funzione TRIMMEAN in Excel può essere utilizzata per calcolare la media di un intervallo di valori escludendo una certa percentuale di osservazioni dalla parte superiore e inferiore del set di dati.

Ad esempio, possiamo utilizzare la seguente formula per calcolare il valore medio nella colonna A escludendo un totale del 20% delle osservazioni (10% superiore e 10% inferiore):

 =TRIMMEAN( A2:A16 , 20%)

Poiché nel nostro set di dati sono presenti 15 valori, 10% equivale a 1,5, che viene arrotondato a 1. Quindi questa formula calcolerà la media dei valori nell’intervallo escludendo il valore più piccolo e il valore più grande:

La media, escludendo gli outlier, risulta essere 58.30769 .

Metodo 2: calcolare la media e utilizzare l’intervallo interquartile per escludere valori anomali

L’ intervallo interquartile (IQR) è la differenza tra il 75° percentile (Q3) e il 25° percentile (Q1) in un set di dati. Misura la distribuzione media del 50% dei valori.

Possiamo definire un’osservazione come un valore anomalo se è 1,5 volte l’intervallo interquartile al di sopra del terzo quartile (Q3) o 1,5 volte l’intervallo interquartile al di sotto del primo quartile (Q1).

Possiamo utilizzare la seguente formula per calcolare l’intervallo interquartile del nostro set di dati in Excel:

 =QUARTILE( A2:A16,3 )-QUARTILE( A2:A16,1 )

Lo screenshot seguente mostra come utilizzare questa formula:

Quindi possiamo utilizzare la seguente formula per utilizzare l’IQR per identificare i valori anomali e assegnare un “1” a qualsiasi valore che sia un valore anomalo nel set di dati:

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

Lo screenshot seguente mostra come utilizzare questa formula:

Vediamo che solo un valore – 164 – risulta essere un valore anomalo in questo set di dati.

Infine, possiamo utilizzare la seguente formula per calcolare la media di tutti i valori nel set di dati che non sono valori anomali:

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

Lo screenshot seguente mostra come utilizzare questa formula:

La media, escludendo i valori anomali, risulta essere 55.42857 .

Risorse addizionali

I seguenti tutorial spiegano come eseguire altre attività comuni in Excel:

Come calcolare l’intervallo interquartile (IQR) in Excel
Come calcolare la media per gruppo in Excel
Come calcolare le medie mobili ponderate in Excel