Comment Calculer Z-ScOUe dans Excel - Guide Pratique

Apprenez à calculer le Z-Score dans Excel pour standardiser les valeurs de données et identifier les valeurs aberrantes. Le Z-Score mesure le nombre d'écarts-types d'un point de données par rapport à la moyenne, essentiel pour l'analyse statistique et le contrôle de qualité.

Pourquoi c'est important

Le Z-Score est crucial pour détecter les anomalies, comparer les ensembles de données à différentes échelles, et effectuer des analyses statistiques dans de nombreux secteurs.

Prérequis

•Compréhension de base des concepts de moyenne et d'écart-type
•Connaissance des fonctions Excel et des références de cellules

Instructions étape par étape

Préparez vos données

Entrez votre ensemble de données dans une seule colonne (par exemple, A2:A20). Incluez des en-têtes à la ligne 1 pour étiqueter vos données.

Calculez la moyenne

Dans une cellule sous vos données, tapez =MOYENNE(A2:A20) pour calculer la moyenne de votre ensemble de données.

Calculez l'écart-type

Dans une autre cellule, entrez =ECARTYPE(A2:A20) pour calculer l'écart-type. Utilisez ECARTYPE.S pour l'échantillon ou ECARTYPE.P pour la population.

Créez la formule Z-Score

Dans la colonne B (à côté de vos données), entrez =(A2-$E$2)/$E$3 où E2 est votre cellule de moyenne et E3 est votre cellule d'écart-type. Utilisez les références absolues ($).

Appliquez la formule à tous les lignes

Cliquez sur la cellule avec votre formule Z-Score, puis faites glisser la poignée de remplissage vers le bas pour l'appliquer à toutes les lignes de données.

Méthodes alternatives

Utiliser la fonction CENTRAGE REDUCTION

Excel offre la fonction intégrée =CENTRAGE.REDUCTION(valeur, moyenne, ecartype) qui calcule directement le Z-Score sans construction de formule manuelle.

Approche par formule matricielle

Utilisez =ECARTYPE(plage) dans la formule comme =((A2-MOYENNE($A$2:$A$20))/ECARTYPE($A$2:$A$20)) pour combiner tous les calculs dans une seule cellule.

Astuces et conseils

✓Utilisez des références absolues ($) pour les cellules de moyenne et d'écart-type pour qu'elles ne changent pas lors de la copie vers le bas.
✓Les Z-Scores vont généralement de -3 à +3; les valeurs en dehors de cette plage sont probablement des valeurs aberrantes.
✓Pour les grands ensembles de données, considérez l'utilisation de l'outil d'analyse pour le calcul automatique du Z-Score.

Astuces avancées

★Créez une colonne d'aide pour signaler les valeurs aberrantes en utilisant =SI(ABS(B2)>3,"Aberrant","Normal") pour identifier rapidement les anomalies.
★Utilisez la mise en forme conditionnelle (Accueil > Mise en forme conditionnelle > Échelles de couleurs) pour mettre en évidence visuellement les distributions Z-Score.
★Combinez Z-Score avec les tableaux croisés dynamiques pour analyser efficacement les valeurs aberrantes par catégorie.

Résolution de problèmes

La formule Z-Score retourne une erreur #DIV/0!

Cela se produit lorsque l'écart-type est égal à zéro (toutes les valeurs sont identiques). Vérifiez la variation de vos données ou ajustez l'ensemble de données.

Les Z-Scores apparaissent comme de très grands nombres positifs/négatifs

Vérifiez si vous avez utilisé ECARTYPE.P au lieu de ECARTYPE.S, ou si vos données contiennent des valeurs aberrantes extrêmes.

La formule ne se copie pas correctement vers d'autres cellules

Assurez-vous que vos références de cellules de moyenne et d'écart-type utilisent la notation absolue ($E$2) pour les cellules du dénominateur.

Formules Excel associées

AVERAGE STDEV STDEV.S STDEV.P STANDARDIZE NORM.DIST NORM.S.DIST

Questions fréquentes

Qu'est-ce qu'un Z-Score de 2,5?

Un Z-Score de 2,5 signifie que le point de données est 2,5 écarts-types au-dessus de la moyenne. Cela est relativement loin de la moyenne et généralement considéré comme inhabituel mais pas une valeur aberrante extrême.

Dois-je utiliser ECARTYPE.S ou ECARTYPE.P pour mes données?

Utilisez ECARTYPE.S (écart-type d'échantillon) si vos données sont un échantillon d'une population plus grande. Utilisez ECARTYPE.P (écart-type de population) uniquement si vos données représentent toute la population.

Les Z-Scores peuvent-ils être négatifs?

Oui, les Z-Scores sont négatifs lorsque les points de données sont en dessous de la moyenne et positifs lorsqu'ils sont au-dessus. Un Z-Score négatif indique simplement que la valeur est en dessous de la moyenne.

Comment identifier les valeurs aberrantes à l'aide des Z-Scores dans Excel?

Généralement, les valeurs avec des Z-Scores au-delà de ±2,5 ou ±3 sont considérées comme des valeurs aberrantes. Vous pouvez utiliser la mise en forme conditionnelle ou la fonction SI pour les mettre en évidence automatiquement.