Ecart type - Excel et Google Sheets

Ce didacticiel montre comment utiliser le Fonction d'écart type Excel dans Excel pour calculer l'écart type pour une population entière.

Écart-type Présentation de la fonction

La fonction DEVIATION STANDARD Calcule calcule l'écart type pour une population entière.

Pour utiliser la fonction de feuille de calcul Excel DEVIATION STANDARD, sélectionnez une cellule et tapez :

(Remarquez comment les entrées de formule apparaissent)

Fonction STANDARD DEVIATION Syntaxe et entrées :

1	=STDEV(nombre1,[nombre2],… )

Nombres- Valeurs pour obtenir la variance standard

Comment calculer l'écart type dans Excel

Chaque fois que vous traitez des données, vous souhaiterez effectuer des tests de base pour vous aider à les comprendre. Vous commencerez généralement par calculer la moyenne, en utilisant la fonction Excel MOYENNE <>.

Cela vous donne une idée de l'endroit où se trouve le « milieu » des données. Et à partir de là, vous voudrez voir à quel point les données sont réparties autour de ce point médian. C'est là qu'intervient l'écart type.

Excel vous propose un certain nombre de fonctions pour calculer l'écart type - STDEV, STDEV.P, STDEV.S et DSTDEV. Nous les aborderons tous, mais d'abord, apprenons quel est l'écart type est, exactement.

Qu'est-ce que l'écart type ?

L'écart type vous donne une idée de la distance entre vos points de données et la moyenne. Prenez l'ensemble de données de résultats de test suivant sur 100 :

1	48,49,50,51,52

La moyenne de cet ensemble de données est de 50 (additionnez tous les nombres et divisez par n, où n est le nombre de valeurs dans la plage).

Regardez maintenant cet ensemble de données suivant :

1	10,25,50,75,90

La moyenne de cet ensemble de données est aussi 50 - mais les deux gammes racontent une histoire très différente. Si vous utilisiez simplement la moyenne, vous pourriez penser que les deux groupes étaient à peu près égaux dans leurs aptitudes - et en moyenne, ils le sont.

Mais dans le premier groupe, nous avons 5 personnes qui ont obtenu des scores très similaires, très médiocres. Et dans le deuxième groupe, nous avons un couple de haut vol équilibré par un couple de mauvais buteurs, avec une personne au milieu. Les diffuser des scores est très différent, ce qui rend votre interprétation des données très différente également.

L'écart type est une mesure de cet écart.

Comment l'écart type est calculé

Pour comprendre ce qu'est l'écart type et comment il fonctionne, il peut être utile de travailler sur un exemple à la main. De cette façon, vous saurez ce qui se passe "sous le capot" une fois que nous aurons atteint les fonctions Excel que vous pouvez utiliser.

Pour calculer l'écart type, vous suivez ce processus :

1) Calculer la moyenne

Prenons notre premier ensemble de données ci-dessus : 48,49,50,51,52

Nous connaissons déjà la moyenne (50), que j'ai confirmée ici avec la fonction Excel MOYENNE<> :

1	=MOYENNE(C4:C8)

2) Soustraire la moyenne de chaque valeur de l'ensemble de données

Je l'ai fait avec la formule suivante :

1

=C4-$H$4

Notre moyenne est en H4, et j'ai "verrouillé" la référence de cellule en plaçant les signes dollar avant la colonne et la ligne (en appuyant sur F4). Cela signifie que je peux copier la formule dans la colonne sans mettre à jour la référence de cellule.

Le résultat:

Maintenant, arrêtons-nous ici une seconde. Si vous jetez un œil à la nouvelle colonne, vous verrez que les nombres ici s'additionnent à zéro. La moyenne de ces nombres est également nulle.

Bien sûr, la propagation de nos données ne peut pas être nulle - nous savons qu'il existe des variations là-bas. Nous avons besoin d'un moyen de représenter cette variation, sans que la moyenne soit nulle.

3) Égaliser les différences

Nous pouvons y parvenir en comparant les différences. Alors, ajoutons une nouvelle colonne et cadrons les nombres dans la colonne D :

1

=D4*D4

Cela a l'air mieux. Nous avons maintenant une certaine variation, et la quantité de variation est liée à la distance de chaque score par rapport à la moyenne.

4) Calculer la variance - la moyenne des différences au carré

L'étape suivante consiste à obtenir la moyenne de ces différences au carré. Il y a en fait deux façons de le faire lors du calcul de l'écart type.

• Si vous utilisez données démographiques, vous prenez simplement la moyenne (additionnez les valeurs et divisez par n)
• Si vous utilisez exemples de données, vous additionnez les valeurs et divisez par n-1

Les données de population signifient que vous disposez de « l'ensemble complet » de vos données, par exemple, vous avez des données sur chaque élève d'une classe donnée.

Les données d'échantillon signifient que vous n'avez pas toutes vos données, juste un échantillon tiré d'une population plus large. En règle générale, votre objectif avec des exemples de données est de faire une estimation de la valeur dans la population plus large.

Un sondage d'opinion politique est un bon exemple d'échantillon de données - les chercheurs interrogent, disons, 1 000 personnes pour avoir une idée de ce que pense un pays ou un État dans son ensemble.

Ici, nous n'avons pas d'échantillon. Nous avons juste cinq membres de la famille à l'esprit statistique qui veulent calculer l'écart type d'un test qu'ils ont tous passé. Nous avons tous les points de données, et nous ne faisons pas d'estimation pour un groupe plus large de personnes. Ce sont des données de population - nous pouvons donc simplement prendre la moyenne ici :

1	=MOYENNE(E4:E8)

OK, nous en avons donc 2. Ce score est connu sous le nom de « variance », et c'est le point de base pour de nombreux tests statistiques, y compris l'écart type. Vous pouvez en savoir plus sur la variance sur sa page principale : comment calculer la variance dans Excel<>.

5) Obtenez la racine carrée de la variance

Nous avons mis nos chiffres au carré plus tôt, ce qui gonfle évidemment un peu les valeurs. Donc, pour ramener le chiffre en ligne avec les différences réelles des scores par rapport à la moyenne, nous devons racine carrée du résultat de l'étape 4 :

1

=SQRT(H4)

Et nous avons notre résultat : l'écart type est de 1,414

Parce que nous avons mis la racine carrée de nos nombres au carré précédemment, l'écart type est donné dans les mêmes unités que les données d'origine. L'écart type ici est donc de 1,414 points de test.

Ecart type lorsque les données sont plus étalées

Auparavant, nous avions un deuxième exemple de plage de données : 10,25,50,75,90

Juste pour le plaisir, voyons ce qui se passe lorsque nous calculons l'écart type sur ces données :

Toutes les formules sont exactement les mêmes qu'avant (notez que la moyenne globale est toujours de 50).

La seule chose qui a changé était la répartition des scores dans la colonne C. Mais maintenant, notre écart type est beaucoup plus élevé, à 29,832 points de test.

Bien sûr, puisque nous n'avons que 5 points de données, il est très facile de voir que la répartition des scores est différente entre les deux ensembles. Mais lorsque vous avez des centaines ou des milliers de points de données, vous ne pouvez pas le dire en scannant simplement rapidement les données. Et c'est exactement pourquoi nous utilisons l'écart type.

Les fonctions Excel pour calculer l'écart type

Maintenant que vous savez comment fonctionne l'écart type, vous n'avez plus besoin de parcourir tout ce processus pour arriver à l'écart type. Vous pouvez simplement utiliser l'une des fonctions intégrées d'Excel.

Excel a plusieurs fonctions à cet effet :

• P calcule l'écart type pour les données de population (en utilisant la méthode exacte que nous avons utilisée dans l'exemple ci-dessus)
• S calcule l'écart type pour les données d'échantillon (en utilisant la méthode n-1 que nous avons abordée plus tôt)
• STDEV est exactement le même que STDEV.S. Il s'agit d'une ancienne fonction qui a été remplacée par STDEV.S et STDEV.P.
• STDEVA est très similaire à STDEV.S, sauf qu'il inclut des cellules de texte et des cellules booléennes (TRUE/FALSE) lors de son calcul.
• STDEVPA est très similaire à STDEV.P, sauf qu'il inclut des cellules de texte et des cellules booléennes (TRUE/FALSE) lors de son calcul.

Wow, beaucoup d'options ici! Ne vous laissez pas intimider - dans la grande majorité des cas, vous utiliserez soit STDEV.P soit STDEV.S.

Examinons chacun d'eux tour à tour, en commençant par STDEV.P, puisque c'est la méthode que nous venons de suivre.

La fonction Excel STDEV.P

STDEV.P calcule l'écart type des données de population. Vous l'utilisez comme ceci :

1	=STDEV.P(C4:C8)

Vous définissez un argument dans STDEV.P : la plage de données pour laquelle vous souhaitez calculer l'écart type.

C'est le même exemple que nous avons parcouru étape par étape ci-dessus lorsque nous avons calculé l'écart type à la main. Et comme vous pouvez le voir ci-dessus, nous obtenons exactement le même résultat - 1,414.

Remarque STDEV.P ignore toutes les cellules contenant du texte ou des valeurs booléennes (TRUE/FALSE). Si vous devez les inclure, utilisez STDEVPA.

La fonction Excel STDEV.S

STDEV.S calcule l'écart type pour les données d'échantillon. Utilisez-le comme ceci :

1	=STDEV.S(C4:C8)

Encore une fois, cela prend un argument - la plage de données pour laquelle vous souhaitez connaître l'écart type.

Avant d'entrer dans un exemple, discutons de la différence entre STDEV.S et STDEV.P.

Comme nous l'avons déjà évoqué, STDEV.S doit être utilisé sur des exemples de données - lorsque vos données font partie d'un ensemble plus vaste. Supposons donc maintenant que dans notre exemple ci-dessus, plus de personnes ont passé le test. Nous voulons estimer l'écart type de tous ceux qui ont passé le test, en utilisant uniquement ces cinq scores. Nous utilisons maintenant des exemples de données.

Maintenant, le calcul diffère de l'étape (4) ci-dessus, lorsque nous calculons la variance - la moyenne de la différence au carré de chaque score par rapport à la moyenne globale.

Au lieu d'utiliser la méthode normale - additionner toutes les valeurs et diviser par n, nous additionnerions toutes les valeurs et diviserions par n-1:

1	=SOMME(E4:E8) / (COMPTE(E4:E8)-1)

Dans cette formule :

• SUM obtient la somme des différences au carré
• COUNT renvoie notre n, que nous soustrayons 1 de
• Nous divisons ensuite simplement notre somme par notre n-1

Cette fois, la moyenne des différences au carré est de 2,5 (vous vous souviendrez peut-être qu'elle était de 2 auparavant, c'est donc un peu plus).

Alors pourquoi divisons-nous par n-1 au lieu de n lorsque nous traitons des échantillons de données ?

La réponse est assez complexe, et si vous essayez simplement d'exécuter vos chiffres pour comprendre vos données, ce n'est pas quelque chose dont vous devez vraiment vous préoccuper. Assurez-vous simplement que vous utilisez STDEV.S pour les exemples de données et STDEV.P pour les données de population, et tout ira bien.

Si vous êtes vraiment curieux de savoir pourquoi, consultez la page principale sur la façon de calculer la variance dans Excel<>.

OK, nous avons maintenant la variance de l'échantillon, donc pour obtenir l'écart type de l'échantillon, nous aurions simplement la racine carrée de la variance :

1

=SQRT(H4)

Nous obtenons 1,581.

STDEV.S effectue tous les calculs ci-dessus pour nous et renvoie l'écart type de l'échantillon dans une seule cellule. Voyons donc ce que cela donne…

1	=STDEV.S(C4:C8)

Oui, encore 1.581.

La fonction Excel STDEV

La fonction STDEV d'Excel fonctionne exactement de la même manière que STDEV.S, c'est-à-dire qu'elle calcule l'écart type pour un échantillon de données.

Vous l'utilisez de la même manière :

1	=STDEV(C4:C8)

Encore une fois, nous obtenons le même résultat.

Note importante: STDEV est une "fonction de compatibilité", ce qui signifie essentiellement que Microsoft s'en débarrasse. Cela fonctionne toujours pour le moment, donc toutes les anciennes feuilles de calcul continueront de fonctionner normalement. Mais dans les futures versions d'Excel, Microsoft pourrait l'abandonner complètement, vous devez donc utiliser STDEV.S au lieu de STDEV dans la mesure du possible.

La fonction Excel STDEVA

STDEVA est également utilisé pour calculer l'écart type d'un échantillon, mais il présente quelques différences importantes que vous devez connaître :

• Les valeurs VRAIES sont comptées comme 1
• Les valeurs FAUX sont comptées comme 0
• Les chaînes de texte sont comptées comme 0

Utilisez-le comme suit :

1	=STDEVA(C4:C8)

Quatre autres amis et membres de la famille ont donné leurs résultats aux tests. Ceux-ci sont indiqués dans la colonne C et la colonne D indique comment STDEVA interprète ces données.

Étant donné que ces cellules sont interprétées comme des valeurs si faibles, cela crée un écart beaucoup plus large entre nos données que ce que nous avons vu auparavant, ce qui a considérablement augmenté l'écart type, maintenant à 26,246.

La fonction Excel STDEVPA

STDEVPA calcule l'écart type d'une population de la même manière que STDEV.P. Cependant, il inclut également des valeurs booléennes et des chaînes de texte dans le calcul, qui sont interprétées comme suit :

• Les valeurs VRAIES sont comptées comme 1
• Les valeurs FAUX sont comptées comme 0
• Les chaînes de texte sont comptées comme 0

Vous l'utilisez comme ceci :

1	=STDEVPA(C4:C12)

Filtrage des données avant le calcul de l'écart type

Dans le monde réel, vous n'aurez pas toujours les données exactes dont vous avez besoin dans un joli tableau bien rangé. Souvent, vous aurez une grande feuille de calcul pleine de données, que vous devrez filtrer avant de calculer l'écart type.

Vous pouvez le faire très facilement avec les fonctions de base de données d'Excel : DSTDEV (pour les échantillons) et DSTDEVP (pour les populations).

Ces fonctions vous permettent de créer une table de critères, dans laquelle vous pouvez définir tous les filtres dont vous avez besoin. Les fonctions appliquent ces filtres en arrière-plan avant de renvoyer l'écart type. De cette façon, vous n'avez pas besoin de toucher à un filtre automatique ou d'extraire des données dans une feuille séparée - DSTDEV et SDTDEVP peuvent faire tout cela pour vous.

En savoir plus sur la page principale des fonctions Excel DSTDEV et DSTDEVP<>.

Fonction STANDARD DEVIATION dans Google Sheets

La fonction STANDARD DEVIATION fonctionne exactement de la même manière dans Google Sheets que dans Excel :