Comment (et pourquoi) utiliser la fonction des valeurs éloignées 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 biaiser les résultats. Par exemple, la moyenne moyenne d'un ensemble de données peut réellement refléter vos valeurs. Excel fournit quelques fonctions utiles pour vous aider à gérer vos valeurs aberrantes, jetons donc un coup d'œil..

Un exemple rapide

Dans l'image ci-dessous, les valeurs aberrantes sont relativement faciles à repérer: la valeur de deux affecté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 gérer manuellement ces valeurs aberrantes..

Dans un plus grand ensemble de données, ce ne sera pas le cas. Il est important de pouvoir identifier les valeurs aberrantes et de les supprimer des calculs statistiques. C'est ce que nous allons voir dans cet article..

Comment trouver des valeurs éloignées dans vos données

Pour rechercher les valeurs aberrantes dans un ensemble de données, procédez comme suit:

Calculez les 1er et 3ème quartiles (nous parlerons de ce qu'ils sont dans un peu).
Évaluez l'intervalle interquartile (nous l'expliquerons également un peu plus bas).
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 située à 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 trimestres, chacun de ces ensembles est appelé quartile. Les 25% les plus bas de la fourchette constituent le premier quartile, les 25% suivants le deuxième quartile, etc. Nous commençons par cette étape car la définition la plus largement utilisée d’une valeur aberrante est un point de données situé à plus de 1,5 intervalle interquartile (IQR) en dessous du 1er quartile et de 1,5 intervalle interquartile au dessus du 3e quartile. Pour déterminer ces valeurs, nous devons d’abord comprendre ce que sont les quartiles..

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

= QUARTILE (tableau, quart)

le tableau est la plage de valeurs que vous évaluez. Et le litre est un nombre qui représente le quartile que vous souhaitez retourner (par exemple, 1 pour le 1^st quartile, 2 pour le deuxième quartile, etc.).

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 compatible avec les versions antérieures lorsque vous utilisez plusieurs versions d'Excel.

Revenons à notre exemple de tableau.

Pour calculer le 1^st Quartile nous pouvons utiliser la formule suivante dans la cellule F2.

= QUARTILE (B2: B14,1)

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

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

= QUARTILE (B2: B14,3)

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

Deuxième étape: Évaluer l'intervalle interquartile

La plage interquartile (ou IQR) correspond aux 50% de valeurs moyennes 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 1^st quartile du 3^rd quartile:

= F3-F2

Maintenant, nous pouvons voir notre gamme interquartile affichée.

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

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

Pour calculer la limite supérieure dans la cellule F6, nous multiplions le IQR par 1,5, mais cette fois-ci ajouter au point de données Q3:

= F3 + (1.5 * F4)

Quatrième étape: identifier les valeurs aberrantes

Maintenant que toutes nos données sous-jacentes sont configurées, il est temps d'identifier nos points de données périphériques - ceux qui sont inférieurs à la valeur de la limite inférieure ou supérieurs à la valeur de la limite supérieure..

Nous allons utiliser la fonction OU pour effectuer ce test logique et afficher les valeurs correspondant à ces critères en entrant la formule suivante dans la cellule C2:

= OU (B2 $ F 6 $)

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

Ignorer les valeurs aberrantes lors du calcul de la moyenne moyenne

À l'aide de la fonction QUARTILE, calculons l'IQR et travaillons 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 avant, mais elle nous permettra d'être flexible 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 (tableau, pourcentage)

le tableau est la plage de valeurs que vous voulez moyenner. le pour cent 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 dans notre exemple pour calculer la moyenne et exclure 20% des valeurs aberrantes..

= TRIMMEAN (B2: B14, 20%)

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