Excel : Comment calculer la moyenne en excluant les valeurs aberrantes

Il existe deux façons de calculer une moyenne tout en excluant les valeurs aberrantes dans Excel :

1. Calculez la moyenne et utilisez TRIMMEAN pour exclure les valeurs aberrantes

2. Calculez la moyenne et utilisez l’intervalle interquartile pour exclure les valeurs aberrantes

Nous utiliserons l’ensemble de données suivant dans Excel pour illustrer comment utiliser les deux méthodes :

Méthode 1 : calculer la moyenne et utiliser TRIMMEAN pour exclure les valeurs aberrantes

La fonction TRIMMEAN dans Excel peut être utilisée pour calculer la moyenne d’une plage de valeurs tout en excluant un certain pourcentage d’observations du haut et du bas de l’ensemble de données.

Par exemple, nous pouvons utiliser la formule suivante pour calculer la valeur moyenne dans la colonne A en excluant un total de 20 % des observations (10 % en haut et 10 % en bas) :

=TRIMMEAN(A2:A16, 20%)

Puisque nous avons 15 valeurs dans notre ensemble de données, 10 % équivaut à 1,5, qui est arrondi à 1. Ainsi, cette formule calculera la moyenne des valeurs de la plage tout en excluant la plus petite valeur et la plus grande valeur :

La moyenne, hors valeurs aberrantes, s’avère être de 58,30769 .

Méthode 2 : calculer la moyenne et utiliser l’intervalle interquartile pour exclure les valeurs aberrantes

L’ intervalle interquartile (IQR) est la différence entre le 75e centile (Q3) et le 25e centile (Q1) dans un ensemble de données. Il mesure la répartition des 50 % moyens des valeurs.

Nous pouvons définir une observation comme étant aberrante si elle est 1,5 fois l’écart interquartile supérieur au troisième quartile (Q3) ou 1,5 fois l’écart interquartile inférieur au premier quartile (Q1).

Nous pouvons utiliser la formule suivante pour calculer l’intervalle interquartile de notre ensemble de données dans Excel :

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

La capture d’écran suivante montre comment utiliser cette formule :

Ensuite, nous pouvons utiliser la formule suivante pour utiliser l’IQR afin d’identifier les valeurs aberrantes et attribuer un « 1 » à toute valeur qui est une valeur aberrante dans l’ensemble de données :

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

La capture d’écran suivante montre comment utiliser cette formule :

Nous constatons qu’une seule valeur – 164 – s’avère être une valeur aberrante dans cet ensemble de données.

Enfin, nous pouvons utiliser la formule suivante pour calculer la moyenne de toutes les valeurs de l’ensemble de données qui ne sont pas des valeurs aberrantes :

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

La capture d’écran suivante montre comment utiliser cette formule :

La moyenne, hors valeurs aberrantes, s’avère être de 55,42857 .

Ressources additionnelles

Les didacticiels suivants expliquent comment effectuer d’autres tâches courantes dans Excel :

Comment calculer l’intervalle interquartile (IQR) dans Excel
Comment calculer la moyenne par groupe dans Excel
Comment calculer les moyennes mobiles pondérées dans Excel