Comment mettre en évidence les valeurs aberrantes statistiques dans Excel

Lorsque vous effectuez une analyse de données, vous supposez généralement que vos valeurs se regroupent autour d'un point de données central (une médiane). Mais parfois, quelques-unes des valeurs tombent trop loin du point central. Ces valeurs sont appelées valeurs aberrantes (elles se situent en dehors de la plage attendue). Les valeurs aberrantes peuvent fausser vos analyses statistiques, vous conduisant à des conclusions fausses ou trompeuses sur vos données.

Vous pouvez utiliser quelques formules simples et une mise en forme conditionnelle pour mettre en évidence les valeurs aberrantes dans vos données.

La première étape pour identifier les valeurs aberrantes consiste à localiser le centre statistique de la plage. Pour faire ce repérage, vous commencez par trouver les 1er et 3e quartiles. Un quartile est une division statistique d'un ensemble de données en quatre groupes égaux, chaque groupe représentant 25 pour cent des données. Les 25 % supérieurs d'une collection sont considérés comme le 1er quartile, tandis que les 25 % inférieurs sont considérés comme le 4e quartile.

Dans Excel, vous pouvez facilement obtenir des valeurs de quartile en utilisant la fonction QUARTILE. Cette fonction requiert deux arguments : une plage de données et le nombre de quartile souhaité.

Dans l'exemple illustré, les valeurs des cellules E3 et E4 sont les 1er et 3e quartiles des données de la plage B3:B20.

En prenant ces deux quartiles, vous pouvez calculer les 50 % statistiques de l'ensemble de données en soustrayant le 3e quartile du 1er quartile. Cette statistique de 50 pour cent est appelée l'intervalle interquartile (IQR). La figure 9-18 affiche l'IQR dans la cellule E5.

Maintenant, la question est de savoir à quelle distance des 50 % du milieu une valeur peut-elle se situer et être toujours considérée comme une valeur « raisonnable » ? Les statisticiens conviennent généralement que l'IQR*1.5 peut être utilisé pour établir une clôture supérieure et inférieure raisonnable :

La clôture inférieure est égale au 1er quartile – IQR*1,5.

La clôture supérieure est égale au 3e quartile + IQR*1,5.

Comme vous pouvez le voir, les cellules E7 et E8 calculent les clôtures supérieures et inférieures finales. Toute valeur supérieure à la clôture supérieure ou inférieure à la clôture inférieure est considérée comme une valeur aberrante.

À ce stade, la règle de mise en forme conditionnelle est facile à mettre en œuvre.

Pour créer cette règle de mise en forme de base, procédez comme suit :

Sélectionnez les cellules de données dans votre plage cible (cellules B3:B20 dans cet exemple), cliquez sur l'onglet Accueil du ruban Excel, puis sélectionnez Mise en forme conditionnelle → Nouvelle règle.

Cela ouvre la boîte de dialogue Nouvelle règle de formatage.

Dans la zone de liste en haut de la boîte de dialogue, cliquez sur l'option Utiliser une formule pour déterminer les cellules à formater.

Cette sélection évalue les valeurs en fonction d'une formule que vous spécifiez. Si une valeur particulière est évaluée à VRAI, la mise en forme conditionnelle est appliquée à cette cellule.

Dans la zone de saisie de la formule, entrez la formule affichée ici.

Notez que vous utilisez la fonction OU pour comparer la valeur de votre cellule cible (B3) aux clôtures supérieure et inférieure trouvées dans les cellules $E$7 et $E$8, respectivement. Si la cellule cible est supérieure à la clôture supérieure ou inférieure à la clôture inférieure, elle est considérée comme une valeur aberrante et sera donc évaluée à VRAI, déclenchant la mise en forme conditionnelle.

=OU(B3<$e$8,b3>$E$7)

Notez que dans la formule, vous excluez les symboles de référence absolue du dollar ($) pour la cellule cible (B3). Si vous cliquez sur la cellule B3 au lieu de taper la référence de cellule, Excel rend automatiquement votre référence de cellule absolue. Il est important de ne pas inclure les symboles de référence absolue du dollar dans votre cellule cible, car vous avez besoin d'Excel pour appliquer cette règle de mise en forme en fonction de la valeur de chaque cellule.

Cliquez sur le bouton Formater.

Cela ouvre la boîte de dialogue Format de cellule, dans laquelle vous disposez d'un ensemble complet d'options pour formater la police, la bordure et le remplissage de votre cellule cible. Une fois que vous avez terminé de choisir vos options de formatage, cliquez sur le bouton OK pour confirmer vos modifications et revenir à la boîte de dialogue Nouvelle règle de formatage.

De retour dans la boîte de dialogue Nouvelle règle de mise en forme, cliquez sur le bouton OK pour confirmer votre règle de mise en forme.

Si vous devez modifier votre règle de mise en forme conditionnelle, placez simplement votre curseur dans l'une des cellules de données de votre plage de mise en forme, puis accédez à l'onglet Accueil et sélectionnez Mise en forme conditionnelle → Gérer les règles. Cela ouvre la boîte de dialogue Gestionnaire de règles de mise en forme conditionnelle. Cliquez sur la règle que vous souhaitez modifier, puis cliquez sur le bouton Modifier la règle.