Dans les longues listes de valeurs de données, on veut souvent surtout identifier les valeurs aberrantes. Moyenne cellules non vides. Excel permet de déterminer et de visualiser assez facilement les écarts.
Comment identifier rapidement dans une liste les valeurs qui s’écartent de la moyenne ?
Supposons que vous ayez saisi une liste de valeurs dans les champs B2 à B11, comme par exemple la consommation d’électricité par jour .
Dans le champ C2, écrivez la formule=MoyenneB2(B$2:B$11) : (Cellules moyenne tension). La cellule indique maintenant dans quelle mesure la valeur de B2 s’écarte de la valeur moyenne de toutes les données.
Copiez la formule en faisant glisser le petit carré en bas à droite de C2 vers le bas dans les champs C3 à C11 (Excel moyenne cellules non contigues). La liste se présente maintenant à peu près comme ceci :
Sélectionnez les cellules C2 à C11
Allez dans le ruban Accueil sur l’icône Mise en forme conditionnelle, choisissez dans celle-ci Barres de données et par exemple la variante Barres de données bleues.
Les barres de la colonne C indiquent maintenant l’écart par rapport à la valeur de la colonne B, ce qui vous aide à repérer les données aberrantes.
Pour la plage de cellules de MOYENNE(), nous utilisons les signes du dollar, car sinon Excel ferait automatiquement glisser la plage de valeurs vers le bas lors de la copie de la formule – ce que nous ne voulons pas. Moyenne cellules filtrÃes. Pour en savoir plus sur ce sujet, cliquez ici : Adressage relatif et absolu des cellules ? voici la différence.
Vous voulez savoir comment intégrer des unités, comme ici le « kWh » dans la dernière capture d’écran, dans vos propres cellules ? Pour cela, utilisez le formatage personnalisé des cellules avec 0,0 « kWh » comme contenu. Moyenne cellules non adjacentes. Vous trouverez plus d’informations à ce sujet dans le conseil Comment utiliser les unités dans les cellules Excel.
Voici comment optimiser l’affichage des écarts pour votre cas.
Avec la procédure décrite ci-dessus, vous voyez les écarts vers le bas et vers le haut dans une couleur différente (Moyenne cellules visibles). Si vous ne le souhaitez pas, car seul l’écart en lui-même vous intéresse – comme par exemple pour les tolérances de fabrication – il suffit d’étendre la formule du point 2) avec le montant de l’écart : =ABS(B2-Moyenne(B$2:B$11)) : Moyenne de cellules non contigues. La représentation se présente alors comme suit :
Vous préférez peut-être utiliser une coloration plutôt que des barres pour indiquer où se trouvent les écarts les plus importants. Moyenne plusieurs cellules excel. Dans ce cas, n’utilisez pas les bases de données pour la mise en forme conditionnelle de l’étape 5), mais par exemple des échelles de couleurs – échelle de couleurs rouge-jaune-vert. Moyenne sans cellules vides. Celle-ci indique les écarts minimes en vert et passe au jaune ou au rouge à mesure que la valeur s’écarte davantage.
Si vous ne souhaitez pas baser l’écart sur la moyenne de toutes les valeurs, mais sur une valeur de référence fixe, utilisez cette valeur numérique dans la formule de la colonne C au lieu de la partie MOYENNE(B$2:B$11).
Si la valeur numérique de la colonne C ne vous intéresse pas tant que ça, mais plutôt la largeur de la barre, donnez simplement un gris clair comme couleur de police aux cellules C2 à C11.
Parfois, la fonction MOYENNE() n’est peut-être pas la plus adaptée à votre problème. Lisez la suite : Quelle est la différence entre MOYENNE() et MEDIANE() ?
