Comment calculer un score Z à l'aide de Microsoft Excel

Un Z-Score est une valeur statistique qui vous indique le nombre d'écarts-types qu'une valeur particulière se trouve être par rapport à la moyenne de l'ensemble de données. Vous pouvez utiliser les formules AVERAGE et STDEV.S ou STDEV.P pour calculer la moyenne et l'écart type de vos données, puis utiliser ces résultats pour déterminer le Z-Score de chaque valeur..

Qu'est-ce qu'un Z-Score et que font les fonctions AVERAGE, STDEV.S et STDEV.P??

Un Z-Score est un moyen simple de comparer les valeurs de deux ensembles de données différents. Il est défini comme le nombre d'écarts-types en dehors de la moyenne d'un point de données. La formule générale ressemble à ceci:

= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)

Voici un exemple pour aider à clarifier. Supposons que vous vouliez comparer les résultats des tests de deux étudiants en algèbre enseignés par des enseignants différents. Vous savez que le premier élève a obtenu 95% à l'examen final d'une classe et que l'élève de l'autre classe a obtenu 87%.

À première vue, la note de 95% est plus impressionnante, mais que se passe-t-il si l'enseignant de la deuxième classe donne un examen plus difficile? Vous pouvez calculer le score Z du score de chaque élève en fonction des scores moyens dans chaque classe et de l'écart type des scores dans chaque classe. La comparaison des scores Z des deux élèves pourrait révéler que l'élève avec le score de 87% obtenait de meilleurs résultats par rapport au reste de la classe que l'étudiant avec le score de 98% le comparait au reste de la classe..

La première valeur statistique dont vous avez besoin est la "moyenne" et la fonction "AVERAGE" d'Excel calcule cette valeur. Il ajoute simplement toutes les valeurs d'une plage de cellules et divise cette somme par le nombre de cellules contenant des valeurs numériques (il ignore les cellules vides)..

L'autre valeur statistique dont nous avons besoin est "l'écart type" et Excel a deux fonctions différentes pour calculer l'écart type de manière légèrement différente..

Les versions précédentes d'Excel ne comportaient que la fonction «STDEV», qui calcule l'écart type tout en traitant les données comme un «échantillon» d'une population. Excel 2010 a scindé cela en deux fonctions permettant de calculer l'écart type:

• STDEV.S: Cette fonction est identique à la précédente fonction “STDEV”. Il calcule l'écart type tout en traitant les données comme un "échantillon" d'une population. Un échantillon d'une population pourrait être quelque chose comme les moustiques particuliers collectés pour un projet de recherche ou les voitures qui ont été mises de côté et utilisées pour les tests de sécurité.
• STDEV.P: Cette fonction calcule l'écart type tout en traitant les données comme l'ensemble de la population. Une population entière serait quelque chose comme tous les moustiques sur Terre ou chaque voiture dans un cycle de production d'un modèle spécifique.

Ce que vous choisissez est basé sur votre ensemble de données. La différence sera généralement faible, mais le résultat de la fonction “STDEV.P” sera toujours inférieur au résultat de la fonction “STDEV.S” pour le même ensemble de données. C’est une approche plus conservatrice de supposer que la variabilité des données est plus grande..

Regardons un exemple

Pour notre exemple, nous avons deux colonnes («Values» et «Z-Score») et trois cellules «auxiliaires» pour stocker les résultats des fonctions «AVERAGE», «STDEV.S» et «STDEV.P». La colonne «Valeurs» contient dix nombres aléatoires centrés autour de 500, et la colonne «Z-Score» indique où nous allons calculer le Z-Score en utilisant les résultats stockés dans les cellules «auxiliaires»..

Tout d'abord, nous allons calculer la moyenne des valeurs à l'aide de la fonction «MOYENNE». Sélectionnez la cellule où vous allez stocker le résultat de la fonction "AVERAGE".

Tapez la formule suivante et appuyez sur entrer -ou- utilisez le menu "Formules".

= MOYENNE (E2: E13)

Pour accéder à la fonction via le menu «Formules», sélectionnez le menu déroulant «Plus de fonctions», sélectionnez l’option «Statistiques», puis cliquez sur «AVERAGE».

Dans la fenêtre Arguments de la fonction, sélectionnez toutes les cellules de la colonne «Valeurs» comme entrée du champ «Number1». Vous n'avez pas besoin de vous soucier du champ "Numéro2".

Maintenant, appuyez sur "OK".

Ensuite, nous devons calculer l'écart type des valeurs à l'aide de la fonction «STDEV.S» ou «STDEV.P». Dans cet exemple, nous allons vous montrer comment calculer les deux valeurs, en commençant par «STDEV.S». Sélectionnez la cellule où le résultat sera stocké..

Pour calculer l'écart type à l'aide de la fonction «STDEV.S», tapez cette formule et appuyez sur Entrée (ou accédez-y via le menu «Formules»).

= STDEV.S (E3: E12)

Pour accéder à cette fonction via le menu «Formules», sélectionnez le menu déroulant «Plus de fonctions», sélectionnez l’option «Statistiques», faites défiler un peu, puis cliquez sur la commande «STDEV.S»..

Dans la fenêtre Arguments de la fonction, sélectionnez toutes les cellules de la colonne «Valeurs» comme entrée du champ «Number1». Vous n'avez pas besoin de vous inquiéter à propos du champ «Numéro2» ici non plus..

Maintenant, appuyez sur "OK".

Ensuite, nous allons calculer l'écart type à l'aide de la fonction «STDEV.P». Sélectionnez la cellule où le résultat sera stocké.

Pour calculer l'écart type à l'aide de la fonction «STDEV.P», tapez cette formule et appuyez sur Entrée (ou accédez-y via le menu «Formules»).

= STDEV.P (E3: E12)

Pour accéder à cette fonction via le menu «Formules», sélectionnez le menu déroulant «Plus de fonctions», sélectionnez l’option «Statistiques», faites défiler un peu puis cliquez sur la formule «STDEV.P»..

Dans la fenêtre Arguments de la fonction, sélectionnez toutes les cellules de la colonne «Valeurs» comme entrée du champ «Number1». Encore une fois, vous n’aurez pas à vous soucier du champ «Numéro2».

Maintenant, appuyez sur "OK".

Maintenant que nous avons calculé la moyenne et l'écart type de nos données, nous avons tout ce dont nous avons besoin pour calculer le Z-Score. Nous pouvons utiliser une formule simple qui référence les cellules contenant les résultats des fonctions “AVERAGE” et “STDEV.S” ou “STDEV.P”.

Sélectionnez la première cellule de la colonne «Z-Score». Nous allons utiliser le résultat de la fonction «STDEV.S» pour cet exemple, mais vous pouvez également utiliser le résultat de «STDEV.P».

Tapez la formule suivante et appuyez sur Entrée:

= (E3- $ G $ 3) / $ H $ 3

Vous pouvez également utiliser les étapes suivantes pour entrer la formule au lieu de taper:

1. Cliquez sur la cellule F3 et tapez = (
2. Sélectionnez la cellule E3. (Vous pouvez appuyer sur le bouton touche flèche gauche une fois ou utilisez la souris)
3. Tapez le signe moins -
4. Sélectionnez la cellule G3 puis appuyez sur F4 pour ajouter les caractères «$» afin de faire une référence «absolue» à la cellule (le cycle passera par «G3»> «$g$3 ">“ G$3 ">“$G3 ">“ G3 ”si vous continuez à appuyer sur F4)
5. Type ) /
6. Sélectionnez la cellule H3 (ou I3 si vous utilisez «STDEV.P») et appuyez sur F4 ajouter les deux caractères “$”.
7. appuyez sur Entrée

Le Z-Score a été calculé pour la première valeur. Il est de 0,155945 écart-type en dessous de la moyenne. Pour vérifier les résultats, vous pouvez multiplier l'écart type par ce résultat (6.271629 * -0.15945) et vérifier que le résultat est égal à la différence entre la valeur et la moyenne (499-500). Les deux résultats sont égaux, donc la valeur est logique.

Calculons les scores Z du reste des valeurs. Mettez en surbrillance la colonne 'Z-Score' entière en commençant par la cellule contenant la formule.

Appuyez sur Ctrl + D pour copier la formule dans la cellule supérieure dans toutes les autres cellules sélectionnées..

Maintenant, la formule a été «remplie» dans toutes les cellules et chacune fera toujours référence aux bonnes cellules «AVERAGE» et «STDEV.S» ou «STDEV.P» en raison des caractères «$». Si vous obtenez des erreurs, revenez en arrière et assurez-vous que les caractères «$» sont inclus dans la formule que vous avez entrée.

Calculer le Z-Score sans utiliser les cellules 'Helper'

Les cellules auxiliaires stockent un résultat, comme celles stockant les résultats des fonctions «AVERAGE», «STDEV.S» et «STDEV.P». Ils peuvent être utiles mais ne sont pas toujours nécessaires. Vous pouvez les ignorer lors du calcul d’un Z-Score en utilisant les formules généralisées suivantes au lieu de.

En voici une qui utilise la fonction «STDEV.S»:

= (Valeur-MOYENNE (valeurs)) / STDEV.S (valeurs)

Et l’un avec la fonction «STEV.P»:

= (Valeur-MOYENNE (valeurs)) / STDEV.P (valeurs)

Lors de la saisie des plages de cellules pour les «Valeurs» dans les fonctions, veillez à ajouter des références absolues («$» à l'aide de F4) de manière à ne pas calculer la moyenne ou l'écart type d'une plage différente. de cellules dans chaque formule.

Si vous avez un grand ensemble de données, il peut être plus efficace d’utiliser des cellules auxiliaires car elle ne calcule pas le résultat des fonctions "AVERAGE", "STDEV.S" ou "STDEV.P" accélérer le temps nécessaire pour calculer les résultats.

En outre, «$ G $ 3» nécessite moins d'octets à stocker et moins de RAM à charger que «MOYENNE (3 $ E: 12 $)». Ceci est important car la version 32 bits standard d’Excel est limitée à 2 Go de RAM (la version 64 bits n’indique aucune limite quant à la quantité de mémoire RAM pouvant être utilisée)..