Calcul de l’écart type sur Excel

Dans les statistiques descriptives, la moyenne arithmétique (également appelée moyenne) et l’écart type sont deux concepts étroitement liés. Mais alors que le premier est bien compris par la plupart, le second est peu compris. 

1 Qu’est-ce que l’écart type ?

L’ écart type est une mesure qui indique dans quelle mesure les valeurs de l’ensemble de données s’écartent (s’étalent) de la moyenne. En d’autres termes, l’écart type indique si vos données sont proches de la moyenne ou fluctuent beaucoup.

Le but de l’écart type est de vous aider à comprendre si la moyenne renvoie vraiment une donnée “typique”. Plus l’écart type est proche de zéro, plus la variabilité des données est faible et plus la moyenne est fiable. L’écart type égal à 0 indique que chaque valeur de l’ensemble de données est exactement égale à la moyenne. Plus l’écart type est élevé, plus il y a de variations dans les données et moins la moyenne est précise.

Pour avoir une meilleure idée de la façon dont cela fonctionne, veuillez consulter les données suivantes :

Pour la biologie, l’écart type est de 5 (arrondi à un nombre entier), ce qui nous indique que la majorité des notes ne sont pas à plus de 5 points de la moyenne. Est-ce bon? Eh bien, oui, cela indique que les notes de biologie des étudiants sont assez cohérents.

Pour les mathématiques, l’écart type est de 25. Cela montre qu’il y a une énorme dispersion dans les notes, ce qui signifie que certains élèves ont obtenu de bien meilleurs résultats et/ou que certains ont obtenu des résultats bien inférieurs à la moyenne.

En pratique, l’écart type est souvent utilisé par les analystes commerciaux comme mesure du risque d’investissement – plus l’écart type est élevé, plus la volatilité des rendements est élevée.

L’écart-type de l’échantillon et l’écart-type de la population

En ce qui concerne l’écart type, vous pouvez souvent entendre les termes « échantillon » et « population », qui font référence à l’exhaustivité des données avec lesquelles vous travaillez. La principale différence est la suivante :

La population comprend tous les éléments d’un ensemble de données.

L’échantillon est un sous-ensemble de données qui comprend un ou plusieurs éléments de la population.

Les chercheurs et les analystes opèrent sur l’écart-type d’un échantillon et d’une population dans différentes situations. Par exemple, lors de la synthèse des résultats d’examen d’une classe d’étudiants, un enseignant utilisera l’écart type de la population. Les statisticiens calculant le score moyen national utiliseraient un écart type d’échantillon car ils sont présentés avec les données d’un échantillon uniquement, et non de l’ensemble de la population.

2. Comprendre la formule de l’écart type

La raison pour laquelle la nature des données est importante est que l’écart type de la population et l’écart type de l’échantillon sont calculés avec des formules légèrement différentes :

Écart-type de l’échantillon Écart-type de la population

Où:

i sont des valeurs individuelles dans l’ensemble de données

x est la moyenne de toutes les valeurs x

n est le nombre total de valeurs x dans l’ensemble de données

Des difficultés à comprendre les formules ? Les décomposer en étapes simples pourrait aider. Mais d’abord, laissez-nous avoir quelques exemples de données sur lesquelles travailler :

  1. Calculer la moyenne (moyenne)

Tout d’abord, vous trouvez la moyenne de toutes les valeurs de l’ensemble de données ( x dans les formules ci-dessus). Lorsque vous calculez à la main, vous additionnez les nombres, puis vous divisez la somme par le nombre de ces nombres, comme ceci :

(1+2+4+5+6+8+9)/7=5

Pour trouver la moyenne dans Excel, utilisez la fonction MOYENNE( ), par exemple =MOYENNE(A2:G2)

  1. Pour chaque nombre, soustrayez la moyenne et placez le résultat au carré.

C’est la partie de la formule de l’écart type qui dit : ( i – x ) 2

Pour visualiser ce qui se passe réellement, veuillez regarder les images suivantes.

Dans cet exemple, la moyenne est de 5, nous calculons donc la différence entre chaque point de données et 5.

Ensuite, vous mettez les différences au carré, en les transformant toutes en nombres positifs :

  1. Additionnez les différences au carré

Pour dire « résumer les choses » en mathématiques, vous utilisez sigma Σ. Donc, ce que nous faisons maintenant est d’additionner les différences au carré pour compléter cette partie de la formule : Σ( i – x ) 2

16 + 9 + 1 + 1 + 9 + 16 = 52

  1. Divisez le total des différences au carré par le nombre de valeurs

Jusqu’à présent, les formules d’écart-type de l’échantillon et d’écart-type de la population ont été identiques. À ce stade, ils sont différents.

Pour l’écart-type de l’échantillon , vous obtenez la variance de l’échantillon en divisant le total des différences au carré par la taille de l’échantillon moins 1 :

52 / (7-1) = 8,67

Pour l’écart-type de la population , vous obtenez la moyenne des différences au carré en divisant le total des différences au carré par leur nombre :

52 / 7 = 7,43

Pourquoi cette différence dans les formules ? 

Parce que dans la formule d’écart type de l’échantillon, vous devez corriger le biais dans l’estimation d’une moyenne d’échantillon au lieu de la moyenne réelle de la population. Et vous faites cela en utilisant n – 1 au lieu de n , qui s’appelle la correction de Bessel.

  1. Prenez la racine carrée

Enfin, prenez la racine carrée des nombres ci-dessus et vous obtiendrez votre écart type (dans les équations ci-dessous, arrondi à 2 décimales) :

Écart-type de l’échantillon Écart-type de la population
√ 8,67 = 2,94 √ 7,43 = 2,73

Dans Microsoft Excel, l’écart type est calculé de la même manière, mais tous les calculs ci-dessus sont effectués en arrière-plan. L’essentiel pour vous est de choisir une fonction d’écart type appropriée, à propos de laquelle la section suivante vous donnera quelques indices.

3. Comment calculer l’écart type dans Excel

Dans l’ensemble, il existe six fonctions différentes pour trouver l’écart type dans Excel. Lequel utiliser dépend principalement de la nature des données avec lesquelles vous travaillez – qu’il s’agisse de l’ensemble de la population ou d’un échantillon.

3.1 Fonctions pour calculer l’écart type de l’échantillon

Pour calculer l’écart type sur la base d’un échantillon, utilisez l’une des formules suivantes (toutes sont basées sur la méthode “n-1” décrite ci-dessus).

Fonction ECARTYPE( )

ECARTYPE (nombre1;[nombre2];…)est la plus ancienne fonction Excel permettant d’estimer l’écart type sur la base d’un échantillon, et elle est disponible dans toutes les versions d’Excel 2003 à 2019.

Dans Excel 2007 et versions ultérieures, ECARTYPE( )peut accepter jusqu’à 255 arguments pouvant être représentés par des nombres, des tableaux, des plages nommées ou des références à des cellules contenant des nombres. Dans Excel 2003, la fonction ne peut accepter que 30 arguments maximum.

Les valeurs logiques et les représentations textuelles des nombres fournies directement dans la liste des arguments sont comptées. Dans les tableaux et les références, seuls les nombres sont comptés ; les cellules vides, les valeurs logiques VRAI et FAUX, les valeurs de texte et d’erreur sont ignorées.

REMARQUE

La fonction Excel ECARTYPE( )est une fonction obsolète, qui est conservée dans les nouvelles versions d’Excel uniquement à des fins de compatibilité descendante. Cependant, Microsoft ne fait aucune promesse concernant les futures versions. Ainsi, dans Excel 2010 et versions ultérieures, il est recommandé d’utiliser ECARTYPE.STANDARD() au lieu de ECARTYPE().

Fonction ECARTYPE.STANDARD( )

ECARTYPE.STANDARD(nombre1 ; [nombre2] ;…) est une version améliorée de ECARTYPE ( ), introduite dans Excel 2010.

Comme ECARTYPE ( ), la fonction ECARTYPE.STANDARD( ) calcule l’écart type d’échantillon d’un ensemble de valeurs en fonction de la formule classique d’écart type d’échantillon décrite dans la section précédente.

Fonction Excel STDEVA( )

= STDEVA(valeur1 ; [valeur2] ; …)est une autre fonction pour calculer l’écart type d’un échantillon dans Excel. Il ne diffère des deux précédents que par la manière dont il gère les valeurs logiques et textuelles :

Toutes les valeurs logiques sont comptées, qu’elles soient contenues dans des tableaux ou des références, ou tapées directement dans la liste des arguments (VRAI vaut 1, FAUX vaut 0).

Les valeurs de texte dans les tableaux ou les arguments de référence sont comptées comme 0, y compris les chaînes vides (“”), les représentations textuelles des nombres et tout autre texte. Les représentations textuelles des nombres fournis directement dans la liste des arguments sont comptées comme les nombres qu’ils représentent.

Les cellules vides sont ignorées.

REMARQUE

Pour qu’un exemple de formule d’écart type fonctionne correctement, les arguments fournis doivent contenir au moins deux valeurs numériques, sinon l’erreur #DIV/0! est renvoyée.

3.2 Fonctions pour calculer l’écart type de la population

Si vous avez affaire à l’ensemble de la population, utilisez l’une des fonctions suivantes pour faire un écart type dans Excel. Ces fonctions sont basées sur la méthode “n”.

Fonction ECARTYPEP ( )

ECARTYPEP (nombre1 ; [nombre2] ;…)est l’ancienne fonction Excel pour trouver l’écart type d’une population.

Dans les nouvelles versions d’Excel 2010, 2013, 2016 et 2019, elle est remplacée par la fonction améliorée ECARTYPE.PEARSON( ), mais est toujours conservée pour la compatibilité descendante.

Fonction Excel ECARTYPE.PEARSON ( )

= ECARTYPE.PEARSON (nombre1 ; [nombre2] ;…) est la version moderne de la fonction STDEVPA( ) qui offre une précision améliorée. Il est disponible dans Excel 2010 et versions ultérieures.

Comme leurs homologues d’écart type, dans les tableaux ou les arguments de référence, les fonctions ECARTYPEP( )et ECARTYPE.PEARSON( ) ne comptent que des nombres. Dans la liste des arguments, ils comptent également les valeurs logiques et les représentations textuelles des nombres.

Fonction STDEVPA( )  

STDEVPA(valeur1 ; [valeur2] ; …) calcule l’écart type d’une population, y compris le texte et les valeurs logiques. En ce qui concerne les valeurs non numériques, STDEVPA( ) fonctionne exactement comme la fonction STDEVA( ).

Remarque

Quelle que soit la formule d’écart type Excel que vous utilisez, elle renverra une erreur si un ou plusieurs arguments contiennent une valeur d’erreur renvoyée par une autre fonction ou un texte qui ne peut pas être interprété comme un nombre.

Quelle fonction d’écart type d’Excel utiliser ?

Une variété de fonctions d’écart type dans Excel peut certainement causer des dégâts, en particulier pour les utilisateurs inexpérimentés. Pour choisir la bonne formule d’écart type pour une tâche particulière, répondez simplement aux 3 questions suivantes :

Calculez-vous l’écart type d’un échantillon ou d’une population ?

Quelle version d’Excel utilisez-vous ?

Votre ensemble de données comprend-il uniquement des nombres ou des valeurs logiques et du texte ?

Pour calculer l’écart type en fonction d’un échantillon numérique, utilisez la fonction ECARTYPE.STANDARD ( )dans Excel 2010 et versions ultérieures ; ECARTYPE ( )dans Excel 2007 et versions antérieures.

Pour trouver l’écart type d’une population, utilisez la fonction ECARTYPE.PEARSON dans Excel 2010 et versions ultérieures ; ECARTYPEP dans Excel 2007 et versions antérieures.

Si vous souhaitez que des valeurs logiques ou textuelles soient incluses dans le calcul, utilisez STDEVA (écart type d’échantillon) ou STDEVPA (écart type de population). Bien que je ne puisse penser à aucun scénario dans lequel l’une ou l’autre fonction peut être utile en elle-même, elles peuvent être utiles dans des formules plus grandes, où un ou plusieurs arguments sont renvoyés par d’autres fonctions sous forme de valeurs logiques ou de représentations textuelles de nombres.

4 -Exemples de formules d’écart type

Une fois que vous avez choisi la fonction qui correspond à votre type de données, il ne devrait y avoir aucune difficulté à écrire la formule – la syntaxe est si simple et transparente qu’elle ne laisse aucune place aux erreurs 🙂 Les exemples suivants illustrent quelques formules d’écart type Excel en action.

4.1 Calcul de l’écart type d’un échantillon et d’une population

Selon la nature de vos données, utilisez l’une des formules suivantes :

Pour calculer l’écart type sur la base de la population entière , c’est-à-dire la liste complète des valeurs (B2:B14 dans cet exemple), utilisez la fonction  ECARTYPE.PEARSON:

=ECARTYPE.PEARSON (B2:B14)

Pour trouver l’écart type basé sur un échantillon qui constitue une partie ou un sous-ensemble de la population (B2:B10 dans cet exemple), utilisez la fonction  ECARTYPE.STANDARD:

=ECARTYPE.STANDARD (B2:B10)

Comme vous pouvez le voir dans la capture d’écran ci-dessous, les formules renvoient des nombres légèrement différents (plus un échantillon est petit, plus la différence est grande) :

Dans Excel 2007 et versions antérieures, vous utiliseriez plutôt les fonctions STDEVPA( ) et STDEVA ( ) :

Pour obtenir l’écart-type de la population :

=STDEVPA(B2:B14)

Pour calculer l’écart type de l’échantillon :

=STDEVA(B2:B10)

Calcul de l’écart type pour les représentations textuelles des nombres

Lors de l’examen de différentes fonctions pour calculer l’écart type dans Excel, nous avons parfois mentionné les “représentations textuelles des nombres” et vous pourriez être curieux de savoir ce que cela signifie réellement.

Dans ce contexte, les “représentations textuelles des nombres” sont simplement des nombres formatés en tant que texte. Comment de tels nombres peuvent-ils apparaître dans vos feuilles de calcul ? Le plus souvent, ils sont exportés à partir de sources externes. Ou, renvoyées par des fonctions dites de texte conçues pour manipuler des chaînes de texte, par exemple TEXTE( ) , STXT() ,DROITE( ) , GAUCHE( ) , etc. Certaines de ces fonctions peuvent également fonctionner avec des nombres, mais leur sortie est toujours du texte, même s’il semble beaucoup comme un nombre.

Pour mieux illustrer ce point, veuillez considérer l’exemple suivant. Supposons que vous ayez une colonne de codes de produits comme “Jeans-105” où les chiffres après un trait d’union indiquent la quantité. Votre objectif est d’extraire la quantité de chaque article, puis de trouver l’écart type des nombres extraits.

Tirer la quantité vers une autre colonne n’est pas un problème :

=DROITE(A2;NBCAR(A2)-CHERCHE(“-“;A2;1))

Le problème est que l’utilisation d’une formule d’écart type Excel sur les nombres extraits renvoie soit #DIV/0 ! ou 0 comme indiqué dans la capture d’écran ci-dessous :

Pourquoi des résultats aussi étranges ? Comme mentionné ci-dessus, la sortie de la fonction DROITE( ) est toujours une chaîne de texte. Mais ni ECARTYPE.STANDARD( ) ni STDEVA( ) ne peuvent gérer les nombres formatés en tant que texte dans les références (le premier les ignore simplement tandis que le second compte comme des zéros). Pour obtenir l’écart type de ces “numéros de texte”, vous devez les fournir directement à la liste des arguments, ce qui peut être fait en incorporant toutes les fonctions DROITE dans votre formule ECARTYPE.STANDARD( ) OU STDEVA( ) :

=ECARTYPE.STANDARD(DROITE(A2 ;NBCAR(A2)-CHERCHE(“-” ;A2 ;1)) ; DROITE(A3 ;NBCAR(A3)-CHERCHE(“-” ;A3 ;1)) ; DROITE(A4 ;NBCAR(A4)-CHERCHE(“-” ;A4 ;1)) ; DROITE(A5 ;NBCAR(A5)-CHERCHE(“-” ;A5 ;1)))

=STDEVA(DROITE(A2;NBCAR(A2)-CHERCHE(“-“;A2;1)); DROITE(A3;NBCAR(A3)-CHERCHE(“-“;A3;1));DROITE(A4;NBCAR(A4)-CHERCHE(“-“;A4;1));DROITE(A5;NBCAR(A5)-CHERCHE(“-“;A5;1)))

Les formules sont un peu lourdes, mais cela pourrait être une solution de travail pour un petit échantillon. Pour un plus grand, sans parler de l’ensemble de la population, ce n’est certainement pas une option. Dans ce cas, une solution plus élégante serait que la fonction CNUM( ) convertisse les “numéros de texte” en nombres que toute formule d’écart type peut comprendre (veuillez noter les nombres alignés à droite dans la capture d’écran ci-dessous par opposition aux chaînes de texte alignées à gauche sur la capture d’écran ci-dessus):

4.2 Comment calculer l’erreur standard de la moyenne

En statistique, il existe une autre mesure pour estimer la variabilité des données – l’erreur standard de la moyenne , qui est parfois raccourcie (bien que, à tort) à juste “l’erreur standard”. L’écart type et l’erreur type de la moyenne sont deux concepts étroitement liés, mais pas identiques.

Alors que l’écart type mesure la variabilité d’un ensemble de données par rapport à la moyenne, l’erreur type de la moyenne estime à quel point la moyenne de l’échantillon est susceptible d’être éloignée de la vraie moyenne de la population. Autrement dit – si vous preniez plusieurs échantillons de la même population, l’erreur type de la moyenne montrerait la dispersion entre ces moyennes d’échantillons. Étant donné que nous calculons généralement une seule moyenne pour un ensemble de données, et non plusieurs moyennes, l’erreur type de la moyenne est estimée plutôt que mesurée.

En mathématiques, l’erreur standard de la moyenne (ESM) est calculée avec cette formule :

Où SD est l’écart type et n est la taille de l’échantillon (le nombre de valeurs dans l’échantillon).

Dans vos feuilles de calcul Excel, vous pouvez utiliser la fonction NB( ) pour obtenir le nombre de valeurs dans un échantillon, RACINE( ) pour prendre une racine carrée de ce nombre et ECARTYPE.STANDARD pour calculer l’écart type d’un échantillon.

En mettant tout cela ensemble, vous obtenez l’erreur standard de la formule moyenne dans Excel :

ECARTYPE.STANDARD( plage )/RACINE(NB( plage ))

En supposant que les exemples de données se trouvent dans B2 : B10, notre formule SEM serait la suivante :

=ECARTYPE.STANDARD(B2:B10)/RACINE(NB(B2:B10))

Et le résultat pourrait ressembler à ceci :

Comment ajouter des barres d’écart type dans Excel

Pour afficher visuellement une marge de l’écart type, vous pouvez ajouter des barres d’écart type à votre graphique Excel. Voici comment faire:

  1. Créez un graphique de la manière habituelle (onglet Insertion / groupe Graphiques ).
  2. Cliquez n’importe où sur le graphique pour le sélectionner, puis cliquez sur le bouton Éléments du graphique .
  3. Cliquez sur la flèche en regard de Barres d’erreur , puis sélectionnez Écart type.

Cela insérera les mêmes barres d’écart type pour tous les points de données.

Voici comment faire l’écart type sur Excel.

S’abonner
Notifier de
0 Commentaires
Inline Feedbacks
Voir tous les commentaires

Initiation à Excel

Fonctions Excel

Excel VBA

Macros VBA Utiles

Plus d'outils

Sur Facebook

Sur YouTube

0
Nous aimerions avoir votre avis, veuillez laisser un commentaire.x