Crédit d'image : nd3000/iStock/GettyImages
Lorsque vous analysez des données, les valeurs aberrantes peuvent fausser les résultats et les rendre moins fiables. Par exemple, si une ou plusieurs de vos valeurs sont considérablement plus élevées que la majorité des données, elles augmentent la moyenne, ce qui peut ne pas refléter les données dans leur ensemble. La suppression de ces points de données aberrants est donc une étape importante pour effectuer des calculs statistiques fiables. Sans fonction dédiée aux valeurs aberrantes, le moyen le plus simple d'effectuer un test de valeurs aberrantes dans Excel consiste à utiliser le plage interquartile pour identifier les valeurs aberrantes, mais vous pouvez également utiliser la fonction Trimmean pour approcher le même résultat.
Calcul de l'intervalle interquartile
La plage de données interquartile est la plage couverte par la "boîte" sur un diagramme en boîte et en moustaches, ou plus précisément, le résultat de la soustraction de la valeur du premier quartile des données de celle du troisième quartile. Excel contient une fonctionnalité intégrée pour calculer n'importe quel quartile pour vos données. Trouvez une cellule de rechange et tapez "=QUARTILE([plage de données], [nombre quartile])" et placez la plage de cellules pour vos données où il est dit "[plage de données]" et le quartile que vous voulez où il est dit "[quartile numéro]."
Vidéo du jour
Par exemple, si vous avez des données dans les cellules A2 à A101 et que vous voulez la valeur pour le premier quartile, vous entrez "=QUARTILE(A2:A101, 1)" pour trouver la valeur. Pour la première partie de l'argument, vous pouvez mettre en évidence les cellules pertinentes avec votre souris, mais après la virgule, vous devez écrire le numéro du quartile souhaité. Pour le troisième quartile avec les mêmes données, vous tapez "=QUARTILE(A2:A101, 3)" pour obtenir le résultat.
En utilisant une autre cellule vide, soustrayez la valeur de la première cellule quartile de la valeur de la troisième cellule quartile. Si le premier quartile est dans la cellule C2 et le troisième quartile est dans la cellule D2, tapez "=D2-C2" pour obtenir le résultat. C'est l'intervalle interquartile.
Analyse des valeurs aberrantes dans Excel
Pour trouver les valeurs aberrantes, vous pouvez désormais utiliser l'intervalle interquartile dans la formule des valeurs aberrantes, qui indique que la limite supérieure des données est la valeur de la troisième quartile plus 1,5 fois l'intervalle interquartile, et la limite inférieure est la valeur du premier quartile moins 1,5 fois l'interquartile intervalle.
Si la valeur du premier quartile est dans C2, la valeur du troisième quartile est dans la cellule D2 et la plage interquartile est dans la cellule E2, vous tapez "=C2-(1.5 * E2)" pour trouver la limite inférieure et "=D2+(1.5 * E2)" pour trouver la limite supérieure limite. En général, vous entrez "=[premier quartile] – (1,5 * [intervalle interquartile])" pour trouver la limite inférieure et "=[troisième quartile] + (1,5 * [intervalle interquartile])" pour trouver la limite supérieure.
Tout ce qui est inférieur à la limite inférieure ou supérieur à la limite supérieure est une valeur aberrante.
Pour terminer le test des valeurs aberrantes dans Excel, utilisez la fonction logique « OU » pour identifier les valeurs de votre classe de données qui sont des valeurs aberrantes de manière efficace. Entrez "=OR([cellule de données]>[limite supérieure], [cellule de données]$F$2, A2
Si la valeur dans A2 est supérieure à la limite supérieure ou inférieure à la limite inférieure, il affiche « VRAI », indiquant que la valeur est une valeur aberrante. Vous pouvez faire glisser cette formule vers le bas en cliquant sur le coin inférieur droit de la cellule avec la formule et en le faisant glisser vers le bas pour qu'il se termine à côté de la cellule de données finale pour effectuer le même calcul sur chaque point de données.
Vous pouvez également mettre les données en surbrillance et accéder à « Mise en forme conditionnelle » dans la section « Styles » de l'onglet « Accueil » si vous souhaitez modifier la mise en forme des valeurs aberrantes. Choisissez "Nouvelle règle" et mettez en surbrillance l'option "Utiliser une formule pour déterminer les cellules à formater". Tapez la même formule que dans le paragraphe précédent, puis cliquez sur l'option "Format" pour choisir le formatage unique pour les valeurs aberrantes.
Utilisation de Trimmean
La fonction "Trimmean" est un moyen plus simple d'identifier les valeurs aberrantes. Tapez "=TRIMMEAN([data range], [proportion to trim])" pour utiliser la fonction, avec la plage de cellules contenant des données à la place de "[data range]" et une décimale pourcentage que vous souhaitez rogner là où il est indiqué "[proportion à rogner]". Cela supprime les valeurs extrêmes en haut et en bas, puis calcule la moyenne en fonction de celles-ci. restant. Donc, si vous coupez 10 %, cela supprimera les 5 % supérieurs et les 5 % inférieurs avant de calculer la moyenne.
Si les données s'étendent de A2 à A101 et que vous souhaitez rogner les 5 % extrêmes des valeurs, vous entrez "=TRIMMEAN(A2:A101, 0,05)" pour trouver la moyenne ajustée. Vous pouvez réduire de 15 % en écrivant "=TRIMMEAN(A2:A101, 0,15)" à la place.
