Comment calculer le Z-score avec Microsoft Excel

Travailler avec des statistiques, c'est trouver des moyens de donner du sens à vos données, et Microsoft Excel est un outil puissant pour cela. Le Z-score (ou score standard) est une mesure statistique de base qui indique le nombre d'écarts-types qu'un point de données particulier se trouve par rapport à la moyenne de l'échantillon. Cela facilite grandement la comparaison de diverses valeurs en termes de leur relation à la moyenne du groupe. Calculer un Z-score dans Excel est un processus simple, quelle que soit la version d'Excel utilisée ou la taille de votre jeu de données.

Organiser les données

Avant de commencer le calcul, il est préférable d'organiser les données principales dans une seule colonne et de s'assurer d'avoir de la place pour les autres informations nécessaires avant d'utiliser Excel comme calculateur de Z-score. Prenons l'exemple d'une classe de 20 élèves ayant tous passé un test. Vous pourriez lister les noms des élèves dans une colonne (par exemple colonne A, lignes 2 à 21), leurs notes dans la colonne voisine (B) et un espace vide pour chaque Z-score dans la colonne d'à côté (C). Vous avez aussi besoin d'une cellule unique chacun pour la moyenne et l'écart-type du jeu de données, que vous pouvez placer n'importe où, mais pour cet exemple, ils sont en D2 et E2 respectivement.

Calculs de la moyenne et de l'écart-type

Excel dispose de fonctions intégrées pour remplir les espaces pour la moyenne et l'écart-type de vos données. Allez dans la cellule prévue pour la moyenne et tapez « =MOYENNE(plage) » avec la plage de cellules contenant les données (notes de test, par exemple) à la place de « plage ». Dans l'exemple, vous taperiez « =MOYENNE(B2:B21) », mais vous pouvez aussi sélectionner les cellules à la souris après avoir ouvert la parenthèse. Cela renvoie la moyenne des données.

Tapez « =ECARTYPE(plage) » pour trouver l'écart-type des données sur les versions d'Excel antérieures à 2010, et soit « =ECARTYPE.PEARSON(plage) » soit « =ECARTYPE.STANDARD(plage) » sur les versions plus récentes. La version « PEARSON » est pour des données qui ne représentent qu'un échantillon de la population totale, comme si vous voulez tirer des conclusions sur les notes de toute l'école mais n'avez que les données d'une classe. La version « STANDARD » est pour des données de toute la population, quand vous vous intéressez seulement à votre classe, ou si vous avez toutes les notes de l'école. À nouveau, saisissez ou cliquez la plage de cellules contenant vos données à la place de « plage ». Dans l'exemple, vous taperiez « =ECARTYPE.STANDARD(B2:B21) ».

Utiliser la formule du Z-score

La formule pour calculer le Z-score dans Excel est simple. Avec X représentant le point de données en question, x̄ pour la moyenne de l'échantillon et σ pour l'écart-type, la formule est :

Z = (X − x̄) ÷ σ

Dans Excel, avec l'organisation des cellules établie jusqu'ici, c'est facile à calculer. Allez dans la cellule à côté de votre premier point de données, qui dans l'exemple est la cellule C2 à côté de la donnée en B2. Tapez « =(B2-$D$2)/$E$2 » pour dire à Excel de soustraire la moyenne du point de données, puis de diviser le résultat par l'écart-type, et appuyez sur « Entrée » pour valider. En mots, sans utiliser les références de cellules de l'exemple, la formule est « =([point de données] – [moyenne]) / [écart-type] » avec les références de cellules appropriées à la place des crochets.

Dans l'exemple, les signes dollar ($D$2 et $E$2) disent à Excel de garder ces cellules exactes quand la formule est étirée vers le bas. Cliquez sur le coin inférieur droit de la cellule avec la formule et étirez-la vers le bas pour copier la formule sur toute la colonne de Z-scores. Cela change la première référence de cellule pour qu'elle désigne le point de données directement à gauche et renvoie une série de Z-scores, vous indiquant combien d'écarts-types chaque résultat est éloigné de la moyenne. Un résultat négatif indique que la note est en dessous de la moyenne, un résultat positif qu'elle est au-dessus.