in

Comment (et pourquoi) utiliser la fonction Outliers dans Excel

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 moyenne d’un ensemble de données peut vraiment refléter vos valeurs. Excel fournit quelques fonctions utiles pour vous aider à gérer vos valeurs aberrantes, alors jetons un coup d’œil.

Un exemple rapide

Dans l’image ci-dessous, les valeurs aberrantes sont raisonnablement faciles à repérer: la valeur de deux attribuée à Eric 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.

Plage de valeurs contenant des valeurs aberrantes

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

Comment trouver des valeurs aberrantes dans vos données

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

  1. Calculez les 1er et 3ème quartiles (nous parlerons de ce qu’ils sont juste un peu).
  2. Évaluez l’intervalle interquartile (nous les expliquerons également un peu plus bas).
  3. Renvoie les limites supérieure et inférieure de notre plage de données.
  4. Utilisez ces limites pour identifier les points de données périphériques.

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

Gamme pour quartiles

Commençons.

Première étape: calculer les quartiles

Si vous divisez vos données en trimestres, chacun de ces ensembles est appelé un quartile. Les 25% des nombres les plus bas de la plage constituent le 1er quartile, les 25% suivants le 2ème quartile, et ainsi de suite. Nous prenons cette étape en premier parce que la définition la plus largement utilisée d’une valeur aberrante est un point de données qui est plus de 1,5 intervalles interquartiles (IQR) en dessous du 1er quartile et 1,5 intervalles interquartiles au-dessus du 3e quartile. Pour déterminer ces valeurs, nous devons d’abord déterminer quels sont les quartiles.

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

=QUARTILE(array, quart)

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

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

Revenons à notre tableau d’exemple.

Gamme pour quartiles

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

=QUARTILE(B2:B14,1)

Lorsque vous entrez la formule, Excel fournit une liste d’options pour l’argument quart.

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

=QUARTILE(B2:B14,3)

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

Valeurs du 1er et du 3ème quartile

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

L’intervalle interquartile (ou IQR) est le milieu de 50% des valeurs de vos données. Il est calculé comme la différence entre la valeur du 1er quartile et la valeur du 3ème 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 intervalle interquartile affiché.

Valeur interquartile

Troisième étape: renvoyer les limites inférieure et supérieure

Les limites inférieure et supérieure 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 liées 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)

Formule Excel pour la valeur limite inférieure

Remarque: Les parenthèses dans cette formule ne sont pas nécessaires car la partie de multiplication calculera avant la partie de soustraction, mais elles facilitent la lecture de la formule.

Pour calculer la limite supérieure dans la cellule F6, nous multiplierons à nouveau l’IQR par 1,5, mais cette fois-ci au point de données Q3:

=F3+(1.5*F4)

Valeurs limites inférieure et supérieure

Étape quatre: identifier les valeurs aberrantes

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

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

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

Fonction OR pour identifier les valeurs aberrantes

Nous copierons ensuite cette valeur dans nos cellules C3-C14. Une valeur TRUE 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 moyenne

L’utilisation de la fonction QUARTILE nous permet de calculer l’IQR et de travailler avec la définition la plus largement utilisée d’une valeur aberrante. Cependant, lors du calcul de la moyenne moyenne pour une plage de valeurs et en ignorant les valeurs aberrantes, il existe une fonction plus rapide et plus facile à utiliser. Cette technique n’identifiera pas 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 voir la syntaxe ci-dessous:

=TRIMMEAN(array, percent)

Il s’agit de la plage de valeurs que vous souhaitez faire la moyenne. Le 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 entré la formule ci-dessous dans la cellule D3 de notre exemple pour calculer la moyenne et exclure 20% des valeurs aberrantes.

=TRIMMEAN(B2:B14, 20%)

Formule TRIMMEAN pour la moyenne hors valeurs aberrantes

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

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Microsoft ajoute un thème sombre à l'explorateur de fichiers dans la dernière mise à jour de Windows 10

Microsoft ajoute un thème sombre à l’explorateur de fichiers dans la dernière mise à jour de Windows 10

Apple ne collecte pas beaucoup de données vous concernant

Apple ne collecte pas beaucoup de données vous concernant