Une valeur aberrante est une valeur nettement supérieure ou inférieure à la plupart des valeurs de vos données. Lors de l'utilisation d'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 pourrait vraiment refléter ses valeurs. Excel fournit des fonctions utiles pour vous aider à gérer vos valeurs aberrantes, alors jetons un coup d'oeil.
Un exemple rapide
Dans l'image ci-dessous, les valeurs aberrantes sont assez faciles à repérer: La valeur de deux attribuée à Eric et la valeur de 173 attribué à Ryan. Dans un ensemble de données comme celui-ci, il est assez facile de détecter et de traiter ces valeurs aberrantes manuellement.
Dans un ensemble de données plus important, ce ne sera pas le cas. Être capable d'identifier les valeurs aberrantes et de les supprimer des calculs statistiques est essentiel, et c'est ce que nous verrons dans ce post.
Comment trouver des valeurs aberrantes dans vos données
Pour trouver des valeurs aberrantes dans un ensemble de données, nous utilisons les étapes suivantes:
- Trouver les premier et troisième quartiles (on va parler un peu de ce qu'ils sont).
- Évaluer l'intervalle interquartile (nous l'expliquerons aussi un peu plus tard).
- Renvoie les limites supérieure et inférieure de notre plage de données.
- 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.
Commençons.
Première étape: calculer les quartiles
Si vous divisez vos données en quarts, chacun de ces ensembles est appelé un quartile. le 25% le plus petit nombre de nombres dans la plage constitue le premier quartile, le suivant 25% le deuxième quartile, et ainsi de suite. Nous commençons par cette étape car la définition la plus couramment utilisée d'une valeur aberrante est un point de données qui est supérieur à 1,5 intervalles interquartiles (IQR) en dessous du premier quartile et 1,5 intervalles interquartiles au-dessus du troisième quartile. Pour définir ces valeurs, il faut d'abord savoir quels sont les quartiles.
Excel fournit une fonction QUARTILE pour calculer les quartiles. Vous avez besoin de deux informations: la matrice et le trimestre.
=QUARTILE(Tableau, Quart)
Les entraînement 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 lui 1S t quartile, 2 pour le deuxième quartile et ainsi de suite).
Noter: dans excel 2010, Microsoft a publié des fonctionnalités quartiles. INC et QUARTILE. EXC comme améliorations de la fonction QUARTILE. quartile est plus rétrocompatible lorsque vous travaillez dans plusieurs versions d’Excel.
Revenons à notre exemple de tableau.
Pour calculer le 1S t 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'alternatives pour l'argument d'un quart.
Pour calculer le 3rd quartile, nous pouvons entrer une formule comme ci-dessus dans la cellule F3, mais en utilisant trois au lieu d'un.
=QUARTILE(B2:B14,3)
À présent, nous avons les points de données quartiles affichés dans les cellules.
Deuxième étape: examiner l'intervalle interquartile
L'intervalle interquartile (IQR) c'est lui 50% moyenne des valeurs dans vos données. Il est calculé comme la différence entre la valeur du premier quartile et la valeur du troisième quartile.
Nous allons utiliser une formule simple dans la cellule F4 qui soustrait le 1S t quartile de 3rd paturon:
=F3-F2
À présent, nous pouvons voir notre gamme interquartile montrée.
Troisième étape: retourner 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. Toute valeur inférieure ou supérieure à 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 et plus tard en le soustrayant du point de données Q1:
=F2-(1.5*F4)
Noter: Les parenthèses dans cette formule ne sont pas nécessaires car la partie multiplication sera calculée avant la partie soustraction, mais ils rendent la formule plus facile à lire.
Pour calculer la limite supérieure dans la cellule F6, nous allons multiplier l'IQR par 1.5 de nouveau, mais cette fois-ci ajouter au point de données Q3:
=F3+(1.5*F4)
Quatrième étape: identifier les valeurs aberrantes
Maintenant que nous avons configuré toutes nos données sous-jacentes, il est temps d'identifier nos points de données périphériques, 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:
=OU(B2<$F$5,B2>$6 $F)
Ensuite, nous copierons 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 moyenne
L'utilisation de la fonction QUARTILE nous permet de calculer l'IQR et de travailler avec la définition la plus utilisée d'une valeur aberrante.. Malgré cela, lors du calcul de la moyenne moyenne pour une plage de valeurs et en ignorant les valeurs aberrantes, il y a une fonction plus rapide et plus facile à utiliser. Cette technique n'identifiera pas une valeur aberrante comme avant, mais cela nous permettra d'être flexible avec ce que nous pourrions considérer comme notre part de valeurs aberrantes.
La fonction dont nous avons besoin s'appelle TRIMMEAN, et vous pouvez voir sa syntaxe ci-dessous:
= TRIMMÉAN(Tableau, pour cent)
Les entraînement est la plage de valeurs que vous souhaitez moyenner. Les pourcent 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 entrons la formule ci-dessous dans la cellule D3 de notre exemple pour calculer la moyenne et exclure le 20% valeurs aberrantes.
= TRIMMÉAN(B2:B14, 20%)
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 en tant que moyennes, Excel a une fonction qui s'adapte à vos besoins.
