Le Score Z, ou Z-score, est une mesure statistique qui quantifie l’écart d’une donnée spécifique par rapport à la moyenne d’un ensemble de données, exprimé en nombre d’écarts-types. Pour le calculer, on emploie les fonctions MOYENNE et ECARTYPE.STANDARD ou ECARTYPE.P d’Excel afin de déterminer la moyenne et l’écart-type des données. Ces résultats permettent ensuite de calculer le score Z de chaque donnée.
Le Z-score: Définition et utilité des fonctions MOYENNE, ECARTYPE.STANDARD et ECARTYPE.P
Un score Z offre une méthode simple pour comparer des valeurs issues de deux ensembles de données distincts. Il correspond au nombre d’écarts-types qui séparent une donnée de la moyenne. La formule générique est la suivante:
=(PointDeDonnée-MOYENNE(EnsembleDeDonnées))/ECARTYPE(EnsembleDeDonnées)
Pour illustrer cela, prenons un exemple. Imaginons que vous souhaitiez comparer les résultats obtenus par deux étudiants en algèbre, ayant suivi des cours avec des professeurs différents. L’un a obtenu 95% à l’examen final, tandis que l’autre a obtenu 87%.
De prime abord, le score de 95% semble plus performant. Toutefois, si le professeur du deuxième élève a donné un examen plus difficile, cela changerait la donne. Le calcul du score Z, basé sur la moyenne et l’écart-type des résultats de chaque classe, permet de relativiser ces scores. On pourrait ainsi découvrir que l’élève ayant obtenu 87% a, en réalité, mieux réussi par rapport à sa classe que celui ayant obtenu 95% par rapport à la sienne.
La première statistique nécessaire est la « moyenne ». La fonction « MOYENNE » d’Excel effectue ce calcul en additionnant toutes les valeurs d’une plage de cellules, puis en divisant cette somme par le nombre de cellules contenant des valeurs numériques (les cellules vides sont ignorées).
La deuxième statistique requise est « l’écart-type ». Excel propose deux fonctions différentes pour calculer cette valeur de manière légèrement distincte.
Les anciennes versions d’Excel disposaient uniquement de la fonction « ECARTYPE », qui calculait l’écart-type en considérant les données comme un « échantillon » d’une population. Depuis Excel 2010, cette fonction a été divisée en deux:
ECARTYPE.STANDARD: Cette fonction est identique à l’ancienne fonction « ECARTYPE ». Elle calcule l’écart-type en traitant les données comme un « échantillon » d’une population. Un échantillon peut être, par exemple, des moustiques prélevés pour une étude ou des voitures utilisées pour des crash tests.
ECARTYPE.P: Cette fonction calcule l’écart-type en considérant les données comme l’ensemble de la population. La population entière serait, par exemple, tous les moustiques de la planète ou toutes les voitures produites dans une série d’un modèle spécifique.
Le choix entre ces deux fonctions dépend de la nature de votre ensemble de données. La différence entre les résultats est souvent minime, mais la fonction « ECARTYPE.P » donnera toujours un résultat plus petit que « ECARTYPE.STANDARD » pour un même jeu de données. Il est plus prudent de considérer qu’il existe davantage de variabilité dans les données.
Un exemple concret
Dans notre exemple, nous avons deux colonnes: « Valeurs » et « Score Z », ainsi que trois cellules « d’aide » pour enregistrer les résultats des fonctions « MOYENNE », « ECARTYPE.STANDARD » et « ECARTYPE.P ». La colonne « Valeurs » contient dix nombres aléatoires autour de 500 et la colonne « Score Z » servira à calculer le score Z en utilisant les résultats stockés dans les cellules « d’aide ».
Nous allons commencer par calculer la moyenne des valeurs à l’aide de la fonction « MOYENNE ». Sélectionnez la cellule où vous souhaitez afficher le résultat.
Entrez la formule suivante et validez en appuyant sur Entrée, ou utilisez le menu « Formules ».
=MOYENNE(E2:E13)
Pour accéder à la fonction via le menu « Formules », cliquez sur « Plus de fonctions », sélectionnez « Statistiques », puis cliquez sur « MOYENNE ».
Dans la fenêtre « Arguments de la fonction », sélectionnez les cellules de la colonne « Valeurs » comme entrée pour le champ « Nombre1 ». Inutile de remplir le champ « Nombre2 ».
Cliquez ensuite sur « OK ».
Nous devons maintenant calculer l’écart-type des valeurs en utilisant soit la fonction « ECARTYPE.STANDARD », soit « ECARTYPE.P ». Dans cet exemple, nous allons calculer les deux, en commençant par « ECARTYPE.STANDARD ». Choisissez la cellule où vous voulez que le résultat soit affiché.
Pour calculer l’écart-type avec « ECARTYPE.STANDARD », entrez la formule suivante et validez (ou utilisez le menu « Formules »).
=ECARTYPE.STANDARD(E3:E12)
Pour accéder à la fonction via le menu « Formules », cliquez sur « Plus de fonctions », sélectionnez « Statistiques », descendez un peu et cliquez sur « ECARTYPE.STANDARD ».
Dans la fenêtre « Arguments de la fonction », sélectionnez toutes les cellules de la colonne « Valeurs » comme entrée pour le champ « Nombre1 ». Inutile de renseigner le champ « Nombre2 ».
Cliquez maintenant sur « OK ».
Ensuite, nous allons calculer l’écart-type en utilisant la fonction « ECARTYPE.P ». Choisissez la cellule où vous voulez afficher le résultat.
Pour calculer l’écart-type avec la fonction « ECARTYPE.P », entrez cette formule et validez (ou accédez-y via le menu « Formules »).
= ECARTYPE.P (E3: E12)
Pour accéder à la fonction via le menu « Formules », cliquez sur « Plus de fonctions », sélectionnez « Statistiques », descendez un peu et cliquez sur la formule « ECARTYPE.P ».
Dans la fenêtre « Arguments de la fonction », sélectionnez toutes les cellules de la colonne « Valeurs » comme entrée pour le champ « Nombre1 ». Là encore, il n’est pas nécessaire de renseigner le champ « Nombre2 ».
Cliquez sur « OK ».
Maintenant que la moyenne et l’écart-type sont calculés, nous avons tout ce qu’il faut pour calculer le score Z. Nous allons utiliser une formule simple qui référence les cellules contenant les résultats des fonctions « MOYENNE » et « ECARTYPE.STANDARD » ou « ECARTYPE.P ».
Sélectionnez la première cellule dans la colonne « Score Z ». Pour cet exemple, nous utiliserons le résultat de la fonction « ECARTYPE.STANDARD », mais vous pouvez tout aussi bien utiliser celui de « ECARTYPE.P ».
Entrez la formule suivante et validez:
=(E3-$G$3)/$H$3
Voici une autre méthode pour entrer la formule :
Cliquez sur la cellule F3 et tapez = (
Sélectionnez la cellule E3 (vous pouvez appuyer sur la flèche gauche ou utiliser la souris).
Tapez le signe moins –
Sélectionnez la cellule G3 puis appuyez sur F4 pour ajouter les caractères « $ » et rendre la référence « absolue » (elle passera par « G3 » > « $G$3 » > « G$3 » > « $G3 » > « G3 » si vous continuez d’appuyer sur F4).
Tapez ) /
Sélectionnez la cellule H3 (ou I3 si vous utilisez « ECARTYPE.P ») et appuyez sur F4 pour ajouter les deux caractères « $ ».
Appuyez sur Entrée.
Le score Z a été calculé pour la première valeur. Il est de 0,15945 écart-type inférieur à la moyenne. Pour vérifier, multipliez l’écart-type par ce résultat (6,271629 * -0,15945) et assurez-vous qu’il correspond à la différence entre la valeur et la moyenne (499-500). Les deux résultats étant équivalents, la valeur est correcte.
Calculons maintenant les scores Z pour les autres valeurs. Sélectionnez toute la colonne « Score Z » en commençant par la cellule contenant la formule.
Appuyez sur Ctrl + D pour copier la formule de la cellule supérieure vers toutes les autres cellules sélectionnées.
La formule a été « remplie » pour toutes les cellules. Grâce aux caractères « $ », chacune fait toujours référence aux cellules « MOYENNE » et « ECARTYPE.STANDARD » ou « ECARTYPE.P » correctes. Si vous obtenez des erreurs, vérifiez que les caractères « $ » sont bien présents dans la formule que vous avez saisie.
Calcul du Z-score sans les cellules « d’aide »
Les cellules « d’aide », qui stockent des résultats tels que ceux des fonctions « MOYENNE », « ECARTYPE.STANDARD » et « ECARTYPE.P », peuvent être utiles, mais ne sont pas indispensables. Il est tout à fait possible de calculer un score Z sans elles, en utilisant les formules généralisées suivantes.
Voici une formule utilisant la fonction « ECARTYPE.STANDARD »:
=(Valeur-MOYENNE(Valeurs))/ECARTYPE.STANDARD(Valeurs)
Et une autre utilisant la fonction « ECARTYPE.P »:
=(Valeur-MOYENNE(Valeurs))/ECARTYPE.P(Valeurs)
Lors de la saisie des plages de cellules pour les « Valeurs » dans les fonctions, n’oubliez pas d’ajouter des références absolues (« $ » en utilisant F4). Cela garantit qu’en « remplissant » les formules, vous ne calculerez pas la moyenne ou l’écart-type d’une plage de cellules différente à chaque fois.
Si vous travaillez sur un grand ensemble de données, l’utilisation de cellules d’aide peut être plus efficace. En effet, elles évitent de calculer le résultat des fonctions « MOYENNE » et « ECARTYPE.STANDARD » ou « ECARTYPE.P » à chaque itération. Cela économise les ressources du processeur et accélère le temps de calcul.
De plus, « $G$3 » prend moins d’octets à stocker et moins de mémoire vive à 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 restriction sur la quantité de RAM utilisable).