Un Z-Score est une valeur statistique qui vous indique combien d’écarts types 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 score Z de chaque valeur.
Table des matières
Qu’est-ce qu’un score Z et à quoi servent 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 par rapport à 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 différents professeurs. Vous savez que le premier étudiant a obtenu 95% à l’examen final dans une classe, et l’élève de l’autre classe a obtenu 87%.
À première vue, la note de 95% est plus impressionnante, mais que se passerait-il si l’enseignant de la deuxième classe donnait un examen plus difficile? Vous pouvez calculer le score Z du score de chaque élève en fonction des scores moyens de chaque classe et de l’écart type des scores de chaque classe. La comparaison des scores Z des deux élèves pourrait révéler que l’élève avec le score de 87% a fait mieux par rapport au reste de sa classe que l’étudiant avec le score de 98% par rapport au reste de sa classe.
La première valeur statistique dont vous avez besoin est la «moyenne» et la fonction «MOYENNE» d’Excel calcule cette valeur. Il additionne 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 n’avaient que la fonction «STDEV», qui calcule l’écart type tout en traitant les données comme un «échantillon» d’une population. Excel 2010 a divisé cela en deux fonctions qui calculent l’écart type:
STDEV.S: Cette fonction est identique à la fonction précédente «STDEV». Il calcule l’écart type tout en traitant les données comme un «échantillon» d’une population. Un échantillon d’une population peut ê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é en cas de collision.
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 une série 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 plus petit que le résultat de la fonction «STDEV.S» pour le même ensemble de données. C’est une approche plus prudente de supposer qu’il y a plus de variabilité dans les données.
Regardons un exemple
Pour notre exemple, nous avons deux colonnes («Valeurs» et «Z-Score») et trois cellules «d’assistance» pour stocker les résultats des fonctions «MOYENNE», «STDEV.S» et «STDEV.P». La colonne «Valeurs» contient dix nombres aléatoires centrés autour de 500, et la colonne «Z-Score» est l’endroit où nous calculerons le Z-Score en utilisant les résultats stockés dans les cellules «helper».
Tout d’abord, nous allons calculer la moyenne des valeurs à l’aide de la fonction «MOYENNE». Sélectionnez la cellule dans laquelle vous stockerez le résultat de la fonction «MOYENNE».
Tapez la formule suivante et appuyez sur Entrée -ou- utilisez le menu «Formules».
=AVERAGE(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 «MOYENNE».
Dans la fenêtre Arguments de la fonction, sélectionnez toutes les cellules de la colonne «Valeurs» comme entrée pour le champ «Numéro1». Vous n’avez pas à vous soucier du champ «Numéro2».
Maintenant, appuyez sur « OK ».
Ensuite, nous devons calculer l’écart type des valeurs en utilisant soit 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 dans laquelle le résultat sera stocké.
Pour calculer l’écart type à l’aide de la fonction «STDEV.S», saisissez cette formule et appuyez sur Entrée (ou accédez-y via le menu «Formules»).
=STDEV.S(E3:E12)
Pour accéder à la fonction via le menu «Formules», sélectionnez le menu déroulant «Plus de fonctions», sélectionnez l’option «Statistiques», faites défiler un peu vers le bas, 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 pour le champ «Numéro1». Vous n’avez pas non plus à vous soucier du champ «Numéro2» ici.
Maintenant, appuyez sur « OK ».
Ensuite, nous calculerons l’écart type à l’aide de la fonction «STDEV.P». Sélectionnez la cellule dans laquelle 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 à la fonction via le menu «Formules», sélectionnez le menu déroulant «Plus de fonctions», sélectionnez l’option «Statistiques», faites défiler un peu vers le bas, 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 pour le champ «Numéro1». 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 «MOYENNE» et «STDEV.S» ou «STDEV.P».
Sélectionnez la première cellule dans la colonne «Z-Score». Nous utiliserons 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:
Cliquez sur la cellule F3 et tapez = (
Sélectionnez la cellule E3. (Vous pouvez appuyer une fois sur la touche fléchée gauche ou utiliser la souris)
Tapez le signe moins –
Sélectionnez la cellule G3 puis appuyez sur F4 pour ajouter les caractères «$» pour faire une référence «absolue» à la cellule (il passera par «G3»> «$ G $ 3»> «G $ 3»> «$ G3»> «G3 ”Si vous continuez d’appuyer sur F4)
Type) /
Sélectionnez la cellule H3 (ou I3 si vous utilisez «STDEV.P») et appuyez sur F4 pour ajouter les deux caractères «$».
appuyez sur Entrée
Le Z-Score a été calculé pour la première valeur. Il est de 0,15945 écart-type inférieur à 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 a du sens.
Calculons les scores Z du reste des valeurs. Mettez en surbrillance toute la colonne «Z-Score» en commençant par la cellule contenant la formule.
Appuyez sur Ctrl + D, ce qui copie la formule dans la cellule supérieure vers le bas à travers toutes les autres cellules sélectionnées.
Maintenant, la formule a été «remplie» pour toutes les cellules, et chacune fera toujours référence aux cellules «MOYENNE» et «STDEV.S» ou «STDEV.P» correctes à cause 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.
Calcul du Z-Score sans utiliser les cellules ‘Helper’
Les cellules d’assistance stockent un résultat, comme celles stockant les résultats des fonctions «MOYENNE», «STDEV.S» et «STDEV.P». Ils peuvent être utiles mais ne sont pas toujours nécessaires. Vous pouvez les ignorer complètement lors du calcul d’un Z-Score en utilisant plutôt les formules généralisées suivantes.
En voici un utilisant la fonction «STDEV.S»:
=(Value-AVERAGE(Values))/STDEV.S(Values)
Et un utilisant la fonction «STEV.P»:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Lorsque vous entrez les plages de cellules pour les «Valeurs» dans les fonctions, assurez-vous d’ajouter des références absolues («$» en utilisant F4) afin que lorsque vous «remplissez» vous ne calculez pas la moyenne ou l’écart type d’une plage différente de cellules dans chaque formule.
Si vous disposez d’un ensemble de données volumineux, il peut être plus efficace d’utiliser des cellules d’assistance car elles ne calculent pas le résultat des fonctions «MOYENNE» et «STDEV.S» ou «STDEV.P» à chaque fois, économisant ainsi les ressources du processeur et accélérer le temps de calcul des résultats.
De plus, «$ G $ 3» prend moins d’octets à stocker et moins de RAM à charger que «MOYENNE ($ E $ 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’a aucune limitation sur la quantité de RAM pouvant être utilisée).