Comment (et pourquoi) utiliser la fonction Outliers dans Excel

• Alan Murray

  @Computergaga1

• Mise à jour le 12 janvier 2019, 5:03am EDT

Une valeur aberrante est une valeur nettement supérieure ou inférieure à la plupart des valeurs de vos données. Lorsque vous utilisez Excel pour analyser des données, les valeurs aberrantes peuvent fausser les résultats. Par exemple, la moyenne d’un ensemble de données pourrait refléter réellement vos valeurs. Excel fournit quelques fonctions utiles pour vous aider à gérer vos valeurs aberrantes, alors regardons-les.

Un exemple rapide

Dans l’image ci-dessous, les valeurs aberrantes sont raisonnablement faciles à repérer – la valeur de deux attribuée à Éric et la valeur de 173 attribuée à Ryan. Dans un ensemble de données comme celui-ci, il est assez facile de repérer et de traiter ces valeurs aberrantes manuellement.

Dans un ensemble de données plus important, ce ne sera pas le cas. Pouvoir identifier les valeurs aberrantes et les supprimer des calculs statistiques est important – et c’est ce que nous allons voir comment faire dans cet article.

Comment trouver les valeurs aberrantes dans vos données

Pour trouver les valeurs aberrantes dans un ensemble de données, nous utilisons les étapes suivantes :

1. Calculer les 1er et 3e quartiles (nous parlerons de ce que ce sont dans un instant).
2. Evaluer l’écart interquartile (nous les expliquerons également un peu plus bas).
3. Retourner les limites supérieure et inférieure de notre plage de données.
4. Utiliser ces limites pour identifier les points de données aberrants.

La plage de cellules à droite de l’ensemble de données vu dans l’image ci-dessous sera utilisée pour stocker ces valeurs.

Démarrons.

Etape un : calculer les quartiles

Si vous divisez vos données en quarts, chacun de ces ensembles est appelé un quartile. Les 25 % de nombres les plus bas de la plage constituent le 1er quartile, les 25 % suivants le 2e quartile, et ainsi de suite. Nous commençons par cette étape car la définition la plus répandue d’une valeur aberrante est un point de données qui se situe à plus de 1,5 intervalle interquartile (IQR) en dessous du 1er quartile et à plus de 1,5 intervalle interquartile au-dessus du 3ème quartile. Pour déterminer ces valeurs, nous devons d’abord savoir quels sont les quartiles.

Excel fournit une fonction QUARTILE pour calculer les quartiles. Elle nécessite deux informations : le tableau et le quart.

=QUARTILE(array, quart)

Le tableau est la plage de valeurs que vous évaluez. Et le quart est un nombre qui représente le quartile que vous souhaitez renvoyer (par exemple, 1 pour le 1er quartile, 2 pour le 2e quartile, et ainsi de suite).

Note : Dans Excel 2010, Microsoft a publié les fonctions QUARTILE.INC et QUARTILE.EXC comme améliorations de la fonction QUARTILE. QUARTILE est plus rétrocompatible lorsque vous travaillez sur plusieurs versions d’Excel.

Revenons à notre tableau d’exemple.

Pour calculer le 1er quartile, nous pouvons utiliser la formule suivante dans la cellule F2.

=QUARTILE(B2:B14,1)

Au fur et à mesure que vous saisissez la formule, Excel fournit une liste d’options pour l’argument quart.

Pour calculer le 3e quartile, nous pouvons entrer une formule comme la précédente dans la cellule F3, mais en utilisant un trois au lieu d’un.

=QUARTILE(B2:B14,3)

Maintenant, nous avons les points de données du quartile affichés dans les cellules.

Deuxième étape : évaluer l’écart interquartile

L’écart interquartile (ou IQR) correspond à la moitié intermédiaire des valeurs de vos données. Il est calculé comme la différence entre la valeur du 1er quartile et la valeur du 3e quartile.

Nous allons utiliser une formule simple dans la cellule F4 qui soustrait le 1er quartile du 3ème quartile :

=F3-F2

Maintenant, nous pouvons voir notre écart interquartile affiché.

Etape trois : Renvoyer les limites inférieures et supérieures

Les limites inférieures et supérieures sont les valeurs les plus petites et les plus grandes de la plage de données que nous voulons utiliser. Toutes les valeurs inférieures ou supérieures à ces valeurs limites sont les valeurs aberrantes.

Nous allons calculer la limite inférieure dans la cellule F5 en multipliant la valeur IQR par 1.5, puis en la soustrayant du point de données Q1:

=F2-(1.5*F4)

Note : Les parenthèses dans cette formule ne sont pas nécessaires car la partie multiplication sera calculée avant la partie soustraction, mais elles rendent la formule plus facile à lire.

Pour calculer la limite supérieure dans la cellule F6, nous allons multiplier l’IQR par 1.5 à nouveau, mais cette fois, nous l’ajouterons au point de données Q3:

=F3+(1.5*F4)

Quatrième étape : Identifiez les valeurs aberrantes

Maintenant que nous avons configuré toutes nos données sous-jacentes, il est temps d’identifier nos points de données aberrants – ceux qui sont inférieurs à la valeur de la borne inférieure ou supérieurs à la valeur de la borne supérieure.

Nous allons utiliser la fonction OU pour effectuer ce test logique et montrer les valeurs qui répondent à ces critères en entrant la formule suivante dans la cellule C2:

=OR(B2<$F$5,B2>$F$6)

Nous allons ensuite copier cette valeur dans nos cellules C3-C14. Une valeur VRAIE indique une valeur aberrante, et comme vous pouvez le voir, nous en avons deux dans nos données.

Ignorer les valeurs aberrantes lors du calcul de la moyenne

L’utilisation de la fonction QUARTILE nous a permis de calculer l’IQR et de travailler avec la définition la plus largement utilisée d’une valeur aberrante. Cependant, lorsque vous calculez la moyenne d’une plage de valeurs et que vous ignorez les valeurs aberrantes, il existe une fonction plus rapide et plus facile à utiliser. Cette technique ne permettra pas d’identifier une valeur aberrante comme auparavant, mais elle nous permettra d’être flexibles avec ce que nous pourrions considérer comme notre partie aberrante.

La fonction dont nous avons besoin s’appelle TRIMMEAN, et vous pouvez en voir la syntaxe ci-dessous :

=TRIMMEAN(array, percent)

Le tableau est la plage de valeurs dont vous voulez faire la moyenne. Le pourcentage est le pourcentage de points de données à exclure du haut et du bas de l’ensemble de données (vous pouvez le saisir sous forme de pourcentage ou de valeur décimale).

Nous avons saisi la formule ci-dessous dans la cellule D3 de notre exemple pour calculer la moyenne et exclure 20 % de valeurs aberrantes.

=TRIMMEAN(B2:B14, 20%)

Vous avez là deux fonctions différentes pour traiter les valeurs aberrantes. Que vous souhaitiez les identifier pour certains besoins de reporting ou les exclure de calculs tels que les moyennes, Excel dispose d’une fonction adaptée à vos besoins.

.