Comment mettre en évidence les valeurs aberrantes statistiques dans Excel

Lorsqu'on effectue une analyse de données, vous assumez généralement que vos valeurs regroupent autour certain point central de données (une médiane). Mais parfois, quelques-unes des valeurs tombent trop loin du point central. Ces valeurs sont appelées valeurs aberrantes (ils se trouvent en dehors de la fourchette prévue). Les valeurs aberrantes peuvent biaiser vos analyses statistiques, vous menant à des conclusions fausses ou trompeuses sur vos données.

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

La première étape dans l'identification des valeurs aberrantes est d'identifier le centre statistique de la gamme. Pour ce faire, 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 faisant jusqu'à 25 pour cent des données. La partie supérieure 25 pour cent d'une collection est considéré comme le premier quartile, tandis que la partie inférieure 25 pour cent est considérée comme la quatrième quartile.

Dans Excel, vous pouvez facilement obtenir des valeurs de premier quartile en utilisant la fonction de quartile. Cette fonction nécessite deux arguments: une gamme de données et le nombre de quartile vous voulez.

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

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

Maintenant, la question est, comment loin de la moyenne de 50 pour cent, une valeur peut asseoir et encore être considéré comme un “ raisonnable ” valeur? Statisticiens conviennent généralement que IQR * 1.5 peut être utilisé pour établir une clôture supérieure et inférieure raisonnable:

La clôture inférieure est égale à la 1er quartile - IQR * 1.5.

La clôture supérieure est égale à la 3e quartile + IQR * 1.5.

Comme vous pouvez le voir, les cellules E7 et E8 calculer 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 mise en forme conditionnelle est facile à mettre en œuvre.

Pour construire cette règle de formatage de base, procédez comme suit:

1. Sélectionnez les cellules de données dans votre fourchette cible (cellules B3: B20 dans cet exemple), cliquez sur l'onglet Accueil du ruban Excel, puis sélectionnez la règle de mise en forme conditionnelle-New.

   Cela ouvre la boîte de dialogue Nouvelle règle de mise en forme.

   

2. Dans la zone de liste en haut de la boîte de dialogue, cliquez sur le utiliser une formule pour déterminer les cellules à Format option.

   Cette sélection évalue les valeurs basées sur une formule que vous spécifiez. Si une valeur particulière TRUE, la mise en forme conditionnelle est appliquée à cette cellule.

3. Dans la boîte de saisie de la formule, saisissez la formule indiquée ici.

   Notez que vous utilisez la fonction OU pour comparer la valeur dans votre cellule cible (B3) à la fois les clôtures supérieures et inférieures trouvés dans les cellules $ E $ et 8 $ 7 E $, respectivement. Si la cellule cible est supérieure à la clôture supérieure ou inférieure à la clôture inférieure, il est considéré comme une valeur aberrante et donc évaluera à TRUE, le déclenchement de la mise en forme conditionnelle.

   = OU (B3lt; 8 $ E $, B3> $ E 7 $)

   Notez que dans la formule, vous excluez les symboles de dollars de référence absolue ($) 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 que vous de ne pas inclure les symboles de dollars de référence absolue dans votre cellule cible parce que vous avez besoin d'Excel pour appliquer cette règle de mise en forme sur la base de la valeur propre de chaque cellule.

4. Cliquez sur le bouton Format.

   Cela ouvre la boîte de dialogue Format de cellules, où vous avez un ensemble complet d'options pour le formatage de la police, frontière, et de remplir votre cellule cible. Après 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 mise en forme.

5. 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, il suffit de placer votre curseur dans l'une des cellules de données au sein de votre gamme formaté, puis allez dans l'onglet Accueil et sélectionnez règles de formatage-Gérer conditionnelles. Cela ouvre la boîte de dialogue Gestionnaire de règles de formatage conditionnel. Cliquez sur la règle que vous souhaitez modifier puis cliquez sur le bouton Modifier la règle.