Fogli google: calcola la media escludendo i valori anomali

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

1. Utilizzare TRIMMEAN per escludere valori anomali

2. Utilizzare l’intervallo interquartile per escludere valori anomali

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

Metodo 1: utilizzare TRIMMEAN per escludere valori anomali

La funzione TRIMMEAN calcola 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 arrotonda 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: 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 Fogli Google:

 =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:

L’unico valore che risulta essere un valore anomalo è 164 .

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 Fogli Google:

Come calcolare la media per gruppo in Fogli Google
Come calcolare la percentuale media in Fogli Google
Come calcolare una media ponderata in Fogli Google