Un score Z est une valeur statistique qui vous indique le nombre d'écarts types d'une valeur particulière par rapport à la moyenne de l'ensemble de données.. Vous pouvez utiliser les formules AVERAGE et STDEV ou les formules STDEV.P pour calculer la moyenne et l'écart type de vos données, puis utiliser ces résultats pour établir le score Z pour chaque valeur..
Qu'est-ce qu'un Z-Score et à quoi servent les fonctions MOYENNE ?, DESVEST.S et DESVEST.P?
Un Z-Score est un moyen simple de comparer les valeurs de deux ensembles de données différents. Il est établi comme le nombre d'écarts types par rapport à la moyenne dans laquelle se trouve un point de données. La formule générale ressemble à ceci:
=(DataPoint-AVERAGE(Base de données))/STDEV(Base de données)
Voici un exemple pour aider à expliquer. Supposons que vous vouliez comparer les résultats des tests de deux étudiants en algèbre enseignés par des professeurs différents. Tu sais que le premier élève a eu un 95% à l'examen final dans une classe et l'élève de l'autre classe a obtenu un 87%.
À première vue, la qualification de la 95% est plus impressionnant, Mais et si le professeur de la deuxième classe donnait un test 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 la 87% a fait mieux par rapport au reste de sa classe que l'élève avec le 98% par rapport au reste de sa catégorie.
La première valeur statistique dont vous avez besoin est le “médias” et fonction “MOYENNE” d’Excel calcule cette valeur. Il suffit de faire la somme de toutes les valeurs d'une plage de cellules et de diviser cette somme par le nombre de cellules contenant des valeurs numériques (ignorer 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ères légèrement différentes.
Les versions précédentes d’Excel n’avaient que le “STDEV”, qui calcule l’écart-type tout en traitant les données comme un “spectacles” d’une population. Excel 2010 divisé en deux fonctions qui calculent l'écart type:
- STDEV.S: cette fonction est identique à la fonction précédente “STDEV”. Calcule l’écart-type tout en traitant les données comme un “spectacles” d’une population. Un échantillon d'une population pourrait être quelque chose comme des moustiques particuliers collectés pour un projet de recherche ou des voitures qui ont été mises de côté et utilisées pour des 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 production d'un modèle spécifique.
Ce que vous choisissez est basé sur votre ensemble de données. La différence est généralement faible, mais le résultat de la fonction “DESVEST. P” sera toujours inférieur au résultat de la fonction “DESVEST. S” pour le même jeu de données. C'est une approche plus conservatrice de supposer qu'il y a plus de variabilité dans les données.
Voyons un exemple
Pour notre exemple, nous avons deux colonnes (“Valeurs” et “Z Score”) et trois cellules “Auxiliaire” pour enregistrer les résultats de la fonction “MOYENNE”, “DESVEST. S” et “DESVEST. P”. La colonne “Valeurs” contient dix nombres aléatoires centrés autour 500, et la colonne “Z Score” c’est là que nous calculerons le score Z en utilisant les résultats stockés dans les cellules “Auxiliaire”.
Premier, nous allons calculer la moyenne des valeurs à l'aide de la fonction "AVERAGE". Sélectionnez la cellule où vous allez stocker le résultat de la fonction "MOYENNE".
Tapez la formule suivante et appuyez sur entrer dans -ou bien- utiliser le menu “Formules”.
= MOYENNE(E2:E13)
Pour entrer la fonction via le menu “Formules”, sélectionnez le menu déroulant “Plus de fonctionnalités”, sélectionner une option “Statistiques” puis cliquez sur “MOYENNE”.
Dans la fenêtre Arguments de fonction, sélectionner toutes les cellules de la colonne “Valeurs” comme entrée pour le champ “Numéro1”. Vous n’avez pas à vous soucier de la campagne “Numéro2”.
Maintenant, appuyez sur “Accepter”.
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, 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", entrez cette formule et appuyez sur Entrée (ou y accéder via le menu "Formules").
=STDEV.S(E3:E12)
Pour entrer la fonction via le menu “Formules”, sélectionnez le menu déroulant “Plus de fonctionnalités”, sélectionner une option “Statistiques”, faites défiler un peu vers le bas, puis cliquez sur la commande “DESVEST. S”.
Dans la fenêtre Arguments de fonction, sélectionner toutes les cellules de la colonne “Valeurs” comme entrée pour le champ “Numéro1”. Vous n’avez pas non plus à vous soucier de la campagne “Numéro2” ici.
Maintenant, appuyez sur “Accepter”.
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", entrez cette formule et appuyez sur Entrée (ou y accéder via le menu "Formules").
= DESVEST.P (E3: E12)
Pour entrer la fonction via le menu “Formules”, sélectionnez le menu déroulant “Plus de fonctionnalités”, sélectionner une option “Statistiques”, faites défiler un peu vers le bas, puis cliquez sur la formule “DESVEST. P”.
Dans la fenêtre Arguments de fonction, sélectionner toutes les cellules de la colonne “Valeurs” comme entrée pour le champ “Numéro1”. De nouveau, vous n’aurez pas à vous soucier de la campagne “Numéro2”.
Maintenant, appuyez sur “Accepter”.
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. On peut utiliser une formule simple qui fait référence aux cellules qui contiennent les résultats des fonctions « MOYENNE » et « STDEV.S » ou « STDEV.P ».
Sélectionnez la première cellule de la colonne “Z-Score”. Nous utiliserons le résultat de la fonction “DESVEST. S” pour cet exemple, mais vous pouvez également utiliser le résultat de “DESVEST. P”.
Tapez la formule suivante et appuyez sur Entrée:
=(E3-$G$3)/$H$3
Alternativement, vous pouvez 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 sur le touche flèche gauche une fois ou utilisez la souris)
- Écrire le signe moins
- - Sélectionnez la cellule G3, puis appuyez sur F4 pour ajouter les caractères “$” pour faire une référence “absolu” à la cellule (passera par “G3″> “PSGRAMMEPS3 > "GPS3 “>”PSG3 ″> « G3 » si vous continuez à appuyer F4)
- Écrit
)/ - 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. Est 0,15945 écarts types inférieurs à 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 les mêmes, 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.
Présion Ctrl + ré, qui copie la formule dans la cellule du haut vers le bas à travers toutes les autres cellules sélectionnées.
Maintenant, la formule a été 'complétée’ dans toutes les cellules, et chacun se référera toujours aux cellules correctes “MOYENNE” et “DESVEST. S” O “DESVEST. P” en raison de 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 de cellules auxiliaires’
Les cellules auxiliaires stockent un résultat, comme celles qui stockent les résultats des fonctions « MOYENNE », « DESVEST.S » et « DESVEST.P ». Ils peuvent être utiles mais ils ne sont pas toujours nécessaires. À sa place, vous pouvez les ignorer entièrement lors du calcul d'un Z-Score en utilisant les formules généralisées suivantes.
En voici un qui utilise la fonction “STDEV.S”:
=(Valeur-MOYENNE(Valeurs))/STDEV.S(Valeurs)
Et celui qui utilise la fonction “STEV. P”:
=(Valeur-MOYENNE(Valeurs))/STDEV.P(Valeurs)
En entrant les plages de cellules pour le “Valeurs” dans les fonctions, assurez-vous d'ajouter des références absolues (“$” en utilisant F4) de sorte qu'une fois « terminé »’ ne calcule pas 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 il ne calcule pas le résultat des fonctions “MOYENNE” et “DESVEST. S” O “DESVEST. P” chaque fois, économiser les ressources du processeur et accélérer le temps nécessaire pour calculer les résultats.
En même temps, « $ G $ 3"Nécessite moins d'octets à économiser et moins de RAM à charger que" MOYENNE ($ E $ 3: $ E $ 12)". Ceci est essentiel car la version standard de 32 bits d'Excel est limité à 2 Go de RAM (la version de 64 bits n'a aucune limitation sur la quantité de RAM qui peut être utilisée).
