Comment trouver des valeurs aberrantes dans Excel (et comment les gérer)

Lorsque vous travaillez avec des données dans Excel, vous aurez souvent des problèmes de gestion des valeurs aberrantes dans votre ensemble de données.

Les valeurs aberrantes sont assez courantes dans toutes sortes de données, et il est important d'identifier et de traiter ces valeurs aberrantes pour s'assurer que votre analyse est correcte et plus significative.

Dans ce tutoriel, je vais vous montrer comment trouver des valeurs aberrantes dans Excel, et certaines des techniques que j'ai utilisées dans mon travail pour gérer ces valeurs aberrantes.

Que sont les valeurs aberrantes et pourquoi est-il important de les trouver ?

Une valeur aberrante est un point de données qui se situe bien au-delà des autres points de données de l'ensemble de données. Lorsque vous avez une valeur aberrante dans les données, cela peut fausser vos données, ce qui peut conduire à des inférences incorrectes.

Permettez-moi de vous donner un exemple simple.

Disons que 30 personnes voyagent dans un bus de la destination A à la destination B. Toutes les personnes appartiennent à un groupe de poids et de revenu similaire. Aux fins de ce didacticiel, considérons que le poids moyen est de 220 livres et que le revenu annuel moyen est de 70 000 $.

Quelque part au milieu de notre itinéraire, le bus s'arrête et Bill Gates monte à bord.

Maintenant, que pensez-vous que cela ferait au poids moyen et au revenu moyen des personnes dans le bus.

Bien que le poids moyen ne soit pas susceptible de beaucoup changer, le revenu moyen des personnes dans le bus va fortement monter en flèche.

C'est parce que le revenu de Bill Gates est une valeur aberrante dans notre groupe, et cela nous donne une mauvaise interprétation des données. Le revenu moyen de chaque personne dans le bus serait de quelques milliards de dollars, ce qui est bien au-delà de la valeur réelle.

Lorsque vous travaillez avec des ensembles de données réels dans Excel, vous pouvez avoir des valeurs aberrantes dans n'importe quelle direction (c'est-à-dire une valeur aberrante positive ou une valeur aberrante négative).

Et pour vous assurer que votre analyse est correcte, vous devez d'une manière ou d'une autre identifier ces valeurs aberrantes, puis décider de la meilleure façon de les traiter.

Voyons maintenant quelques façons de trouver des valeurs aberrantes dans Excel.

Trouver des valeurs aberrantes en triant les données

Avec de petits ensembles de données, un moyen rapide d'identifier les valeurs aberrantes consiste simplement à trier les données et à parcourir manuellement certaines des valeurs en haut de ces données triées.

Et comme il peut y avoir des valeurs aberrantes dans les deux sens, assurez-vous d'abord de trier les données par ordre croissant, puis par ordre décroissant, puis de parcourir les valeurs supérieures.

Permettez-moi de vous montrer un exemple.

Ci-dessous, j'ai un ensemble de données où j'ai des durées d'appel (en secondes) pour 15 appels de service client.

Voici les étapes pour trier ces données afin que nous puissions identifier les valeurs aberrantes dans l'ensemble de données :

  1. Sélectionnez l'en-tête de colonne de la colonne que vous souhaitez trier (cellule B1 dans cet exemple)
  2. Cliquez sur l'onglet Accueil
  3. Dans le groupe Édition, cliquez sur l'icône Trier et filtrer.
  4. Cliquez sur Tri personnalisé
  5. Dans la boîte de dialogue Trier, sélectionnez « Durée » dans la liste déroulante Trier par et « Du plus grand au plus petit » dans la liste déroulante Ordre
  6. Cliquez sur OK

Les étapes ci-dessus trieraient la colonne de durée d'appel avec les valeurs les plus élevées en haut. Vous pouvez maintenant analyser manuellement les données et voir s'il y a des valeurs aberrantes.

Dans notre exemple, je peux voir que les deux premières valeurs sont bien plus élevées que le reste des valeurs (et les deux dernières sont bien plus basses).

Remarque : cette méthode fonctionne avec de petits ensembles de données où vous pouvez analyser manuellement les données. Ce n'est pas une méthode scientifique mais ça marche bien

Recherche de valeurs aberrantes à l'aide des fonctions quartiles

Parlons maintenant d'une solution plus scientifique qui peut vous aider à identifier s'il y a des valeurs aberrantes ou non.

En statistique, un quartile correspond au quart de l'ensemble de données. Par exemple, si vous avez 12 points de données, le premier quartile correspond aux trois derniers points de données, le deuxième quartile correspond aux trois points de données suivants, et ainsi de suite.

Ci-dessous se trouve l'ensemble de données où je veux trouver les valeurs aberrantes. Pour ce faire, je devrai calculer le 1er et le 3ème quartile, puis en l'utilisant calculer la limite supérieure et la limite inférieure.

Voici la formule pour calculer le premier quartile dans la cellule E2 :

=QUARTILE.INC($B$2:$B$15,1)

et voici celui pour calculer le troisième quartile dans la cellule E3 :

=QUARTILE.INC($B$2:$B$15,3)

Maintenant, je peux utiliser les deux calculs ci-dessus pour obtenir l'intervalle interquartile (qui représente 50% de nos données dans le 1er et le 3e quartile)

=F3-F2

Nous allons maintenant utiliser l'intervalle interquartile pour trouver la limite inférieure et supérieure qui contiendrait la plupart de nos données.

Tout ce qui est en dehors de ces limites inférieures et supérieures serait alors considéré comme des valeurs aberrantes.

Voici la formule pour calculer la limite inférieure :

=Quartile1 - 1,5*(Plage interquartile)

qui dans notre exemple devient :

=F2-1,5*F4

Et la formule pour calculer la limite supérieure est :

=Quartile3 + 1,5*(Plage interquartile)

qui dans notre exemple devient :

=F3+1.5*F4

Maintenant que nous avons la limite supérieure et inférieure dans notre ensemble de données, nous pouvons revenir aux données d'origine et identifier rapidement les valeurs qui ne se situent pas dans cette plage.

Un moyen rapide de le faire serait de vérifier chaque valeur et de renvoyer un VRAI ou FAUX dans une nouvelle colonne.

J'ai utilisé la formule OR ci-dessous pour obtenir VRAI pour les valeurs aberrantes.

=OU(B2$F$6)

Vous pouvez maintenant filtrer la colonne Outlier et afficher uniquement les enregistrements dont la valeur est VRAI.

Alternativement, vous pouvez également utiliser la mise en forme conditionnelle pour mettre en évidence toutes les cellules où la valeur est VRAI

Noter: Bien que ce soit une méthode plus acceptée pour trouver des valeurs aberrantes dans les statistiques. Je trouve cette méthode un peu inutilisable dans des scénarios réels. Dans l'exemple ci-dessus, la limite inférieure calculée par la formule est -103, alors que l'ensemble de données dont nous disposons ne peut être que positif. Cette méthode peut donc nous aider à trouver des valeurs aberrantes dans un sens (valeurs élevées), elle est inutile pour identifier les valeurs aberrantes dans l'autre sens.

Recherche des valeurs aberrantes à l'aide des fonctions LARGE/SMALL

Si vous travaillez avec beaucoup de données (valeurs dans plusieurs colonnes), vous pouvez extraire les 5 ou 7 valeurs les plus grandes et les plus petites et voir s'il contient des valeurs aberrantes.

S'il y a des valeurs aberrantes, vous pourrez les identifier sans avoir à parcourir toutes les données dans les deux sens.

Supposons que nous ayons l'ensemble de données ci-dessous et que nous voulions savoir s'il y a des valeurs aberrantes.

Vous trouverez ci-dessous la formule qui vous donnera la plus grande valeur de l'ensemble de données :

= GRAND($B$2:$B$16,1)

De même, la deuxième plus grande valeur sera donnée par

= GRAND($B$2:$B$16,1)

Si vous n'utilisez pas Microsoft 365, qui a des tableaux dynamiques, vous pouvez utiliser la formule ci-dessous et elle vous donnera les cinq plus grandes valeurs de l'ensemble de données avec une seule formule :

=LARGE($B$2:$B$16,LIGNE($1:5))

De même, si vous voulez les 5 plus petites valeurs, utilisez la formule ci-dessous :

=PETIT($B$2:$B$16,LIGNE($1:5))

ou ce qui suit si vous n'avez pas de tableaux dynamiques :

=PETIT($B$2:$B$16,1)

Une fois que vous avez ces valeurs, il est très facile de trouver des valeurs aberrantes dans l'ensemble de données.

Bien que j'ai choisi d'extraire les 5 valeurs les plus grandes et les plus petites, vous pouvez choisir d'en obtenir 7 ou 10 en fonction de la taille de votre ensemble de données.

Je ne sais pas si c'est une méthode acceptable pour trouver des valeurs aberrantes dans Excel ou non, mais c'est la méthode que j'ai utilisée lorsque j'ai dû travailler avec beaucoup de données financières dans mon travail il y a quelques années. Par rapport à toutes les autres méthodes abordées dans ce tutoriel, j'ai trouvé celle-ci la plus efficace.

Comment gérer les valeurs aberrantes de la bonne façon

Jusqu'à présent, nous avons vu les méthodes qui nous aideront à trouver les valeurs aberrantes dans notre ensemble de données. Mais que faire une fois que vous savez qu'il y a des valeurs aberrantes.

Voici quelques méthodes que vous pouvez utiliser pour gérer les valeurs aberrantes afin que votre analyse de données soit correcte.

Supprimer les valeurs aberrantes

Le moyen le plus simple de supprimer les valeurs aberrantes de votre ensemble de données consiste simplement à les supprimer. De cette façon, cela ne faussera pas votre analyse.

C'est une solution plus viable lorsque vous avez de grands ensembles de données et que la suppression de quelques valeurs aberrantes n'aura pas d'impact sur l'analyse globale. Et bien sûr, avant de supprimer les données, assurez-vous de créer une copie et d'explorer la cause de ces valeurs aberrantes.

Normaliser les valeurs aberrantes (ajuster la valeur)

Normaliser les valeurs aberrantes est ce que je faisais quand j'étais dans mon travail à temps plein. Pour toutes les valeurs aberrantes, je les remplacerais simplement par une valeur légèrement supérieure à la valeur maximale de l'ensemble de données.

Cela m'a permis de ne pas supprimer les données, mais en même temps, je ne les laisse pas fausser mes données.

Pour vous donner un exemple concret, si vous analysez la marge bénéficiaire nette des entreprises, où la plupart des entreprises se situent entre -10 % et 30 %, et il y a quelques valeurs qui sont supérieures à 100 %, je modifierait simplement ces valeurs aberrantes à 30 % ou 35 %.

Voici donc quelques-unes des méthodes que vous pouvez utiliser dans Excel pour trouver les valeurs aberrantes.

Une fois que vous avez identifié les valeurs aberrantes, vous pouvez approfondir les données et rechercher ce qui les cause, en même temps choisir l'une des techniques pour gérer ces valeurs aberrantes (ce qui pourrait être de les supprimer ou de les normaliser en ajustant la valeur)

J'espère que vous avez trouvé ce tutoriel utile.

Vous contribuerez au développement du site, partager la page avec vos amis

wave wave wave wave wave