Comment calculer un écart type sur Excel ou Google Sheet ?

Calculer un écart type sur Excel ou sur Google Sheets est une opération incontournable en analyse de données, tous domaines confondus, et notamment en marketing. Le nombre retourné par l'écart type indique la dispersion d'un jeu de données autour de leur moyenne. Excel propose des fonctions ECARTYPE.STANDARD() et ECARTYPE.PEARSON() pour calculer automatiquement cette valeur. Google Sheets offre des fonctions similaires avec STDEV() et STDEV.P().

À quoi sert l'écart type ?

L'écart type sert à évaluer le niveau d'homogénéité au sein d'un jeu de données, pour en affiner l'analyse. C'est en quelque sorte un signal d'alerte : si l'écart type est élevé, les données sont dispersées auquel cas la moyenne est peu significative.

Imaginons un CSAT à 80 % : ce score indique un très bon niveau de satisfaction client, pourtant il peut refléter 2 situations très différentes. Regardons comment on peut obtenir ce score de 80 % :

Dans la situation A, l'entreprise peut se réjouir de la très bonne satisfaction globale de tous ses clients alors que dans la situation B, 30 % des clients sont très mécontents. Si l'équipe marketing se contente d'analyser le score CSAT, elle constate un très bon niveau de satisfaction. Ce n'est qu'en calculant l'écart type qu'elle réalise que 30 % des clients sont à haut risque. Sans l'écart type, l'entreprise passe à côté d'éléments d'analyse importants dissimulés par la moyenne.

Quels sont les éléments de calcul de l'écart type ?

L'écart-type équivaut à la racine carrée de la variance. La variance, quant à elle, correspond à l'écart carré moyen entre chaque donnée et le centre de la distribution représenté par la moyenne. L'écart-type désigne donc la moyenne quadratique des écarts entre les valeurs observées et leur moyenne arithmétique. Les symboles s et σ sont utilisés pour représenter respectivement l'écart-type d'un échantillon et l'écart-type d'une population entière.

La formule de l'écart-type comprend les valeurs suivantes :

• ∑ : somme de toutes les valeurs de la série.
• xi : ième valeur de la série de données.
• x̄ ou μ : moyenne arithmétique de l'échantillon ou de la population.
• n ou N : taille de l'échantillon ou de la population.

L'écart-type est alors calculé comme suit :

• Pour un échantillon : s = √(∑(xi - x)²/(n - 1)
• Pour une population : σ = √(∑(xi - μ)²)/N

Voici le processus à suivre pour obtenir l'écart-type :

1. Calculer la moyenne arithmétique de la distribution.
2. Calculer le carré de l'écart à la moyenne de chaque valeur de la distribution.
3. Faire la somme des valeurs obtenues.
4. Diviser par l'effectif de la série.
5. Calculer la racine carrée de la variance.

ECARTYPE.STANDARD() et STDEV()

Les formules ECARTYPE.STANDARD() sur Excel et STDEV() sur Google Sheets permettent de calculer en un clic l'écart type sur un échantillon de données. Elles font partie des fonctions indispensables à connaître en analyse de données. À savoir sur ces formules :

• Prennent en argument des nombres, références ou matrices comportant des données chiffrées.
• Excluent automatiquement les textes.
• Ignorent les cellules vides, les valeurs logiques et les erreurs.
• Évaluent la moyenne de l'ensemble pour en extraire l'écart type.

ECARTYPE.PEARSON() et STDEV.P()

Les formules ECARTYPE.PEARSON() sur Excel et STDEV.P() sur Google Sheets calculent automatiquement l'écart type d'une population. À savoir sur ces formules :

• Contiennent les arguments nombre1 (obligatoire), qui correspond à une population, et nombre2 (facultatif) qui désigne une population entière.
• Comptent jusqu'à 254 valeurs numériques.
• Prennent en arguments des nombres, des noms, des matrices ou des références contenant des données chiffrées.
• Prennent en compte les valeurs logiques et les représentations textuelles de nombres saisies directement dans la liste des arguments.
• Excluent les valeurs logiques et le texte.
• Ignorent les cellules vides et les erreurs.

La syntaxe de la formule correspond à : ECARTYPE.PEARSON(nombre1,[nombre2],...)

Les étapes de calcul de l'écart type sur Excel et Google Sheets

1 - Préparer l'ensemble de données

La première étape pour calculer l'écart type d'un jeu de données sur Excel ou sur Google Sheets est de préparer les valeurs de la série à analyser. Les données peuvent être entières, décimales ou bien prendre la forme d'une matrice ou de références contenant des nombres.

Les valeurs logiques ou textuelles peuvent aussi être présentes dans les cellules. Si elles sont traduisibles en nombres (comme un pourcentage, par exemple), elles entrent dans le calcul. Sinon, elles sont ignorées par la formule de l'écart type. Attention en revanche aux dates qui sont interprétées et faussent l'évaluation.

2 - Choisir une cellule de destination

La formule de l'écart type peut être appelée depuis n'importe quelle cellule vide du classeur ou d'une autre feuille. Comme toutes les cases Excel et Google Sheets, elle reste modifiable. Il est par exemple possible de faire ressortir un écart type bas qui révèle une faible dispersion par rapport à des valeurs plus élevées qui indiquent un grand étalement autour de la moyenne. La cellule contenant l'écart type peut aussi être dupliquée pour calculer rapidement la variance de plusieurs colonnes.

3 - Appeler la formule sur une sélection de cellules

Pour appeler la formule dans une cellule, il faut taper directement ECARTYPE.STANDARD() ou STDEV(), puis la plage de valeurs concernée en paramètres.

Un message d'erreur remonte si la donnée ne peut pas être incluse. Il est aussi possible d'ajouter une plage de valeurs en entrant la position de la première cellule suivie de deux points, puis celle de la dernière, par exemple, C1:C5.

À noter que les formules ECARTYPE.STANDARD() et STDEV() considèrent que les arguments ne représentent qu'un échantillon d'une population. Si les valeurs correspondent à l'ensemble de la population, il faut taper ECARTYPE.PEARSON() sur Excel et STDEV.P() sur Google Sheets.

4 - Interpréter le résultat

L'écart type rend compte de l'étalement des données : plus l'écart type est élevé, plus la dispersion est grande. En toute logique, le résultat est nul lorsque les valeurs numériques sont toutes identiques.

En marketing, des écarts types élevés indiquent, selon l'objet de l'analyse :

• Des comportements clients hétérogènes.
• Des offres d'inégale attractivité.
• Des pages web plus ou moins pertinentes.

Exemple : une campagne marketing diffusée sur divers canaux génère un taux de clic moyen de 3 %. Si l'écart type est de 0,1, tous les canaux sont aussi performants. Si en revanche l'écart type est supérieur à 3, certains canaux sont beaucoup plus efficaces que d'autres : c'est une piste intéressante pour gagner en rentabilité.

Comment calculer l'écart type avec l'outil Fonction ?

Excel et Google Sheets permettent de calculer automatiquement l'écart type dans un jeu de données, de deux manières : en écrivant la formule dans une cellule, en allant chercher la formule avec les commandes « Insertion » puis « Fonction ».

Exemples d'applications concrètes de l'écart type

L'écart type trouve des applications concrètes dans de nombreux secteurs d'activité, dont le marketing. Comprendre son interprétation selon le contexte permet d'affiner les analyses, d'optimiser la prise de décision et d'identifier des opportunités d'amélioration.

Applications en marketing digital

En marketing digital, l'écart type révèle la variabilité des performances et aide à identifier les tendances significatives.

Pour l'analyse du trafic web, par exemple, il permet de détecter les pages avec des comportements utilisateurs hétérogènes : un temps de session moyen de 2 minutes avec un écart type élevé peut indiquer que certains visiteurs quittent immédiatement tandis que d'autres restent longtemps. Cette situation mérite une analyse approfondie des causes, pour en tirer des pistes concrètes d'amélioration.

Autre exemple en segmentation client, où l'écart type aide à identifier les groupes homogènes : des clients avec des montants d'achat ayant un écart type faible peuvent vraisemblablement former un segment spécifique, au comportement de consommation similaire.

Dans les tests A/B, l'écart type des conversions permet de déterminer la significativité statistique des résultats. Pour le ROI publicitaire, il mesure la volatilité des performances : un écart type élevé indique des résultats imprévisibles nécessitant des ajustements stratégiques.

Applications en finance et gestion

En finance, l'écart type mesure la volatilité et le risque des investissements. Pour un portefeuille d'actions, il quantifie l'ampleur des fluctuations. L'analyse des risques utilise l'écart type pour calculer la Value at Risk (VaR) et déterminer les pertes potentielles maximales.

Dans les prévisions budgétaires, l'écart type des revenus historiques aide à établir des fourchettes de prédiction réalistes. Un chiffre d'affaires mensuel avec un écart type faible permet des prévisions plus précises. Pour le contrôle des coûts, il révèle les postes de dépenses les plus volatils nécessitant une surveillance accrue.

En analyse de performance KPI, l'écart type identifie les indicateurs les plus stables : un taux de marge avec un écart type faible indique une rentabilité cohérente, tandis qu'un écart type élevé suggère des variations importantes à investiguer.

Applications en contrôle qualité

Le contrôle qualité industriel s'appuie massivement sur l'écart type pour maintenir les standards de production. Dans la définition des tolérances, il détermine les limites acceptables.

Pour évaluer la stabilité des processus, l'écart type mesure la variabilité dans le temps. Un processus stable présente un écart type constant, tandis qu'une augmentation signale une dérive nécessitant une intervention.

Quels graphiques créer pour visualiser l'écart type ?

La visualisation graphique de l'écart type facilite l'interprétation des données et la communication des résultats. Excel et Google Sheets offrent plusieurs types de graphiques pour représenter efficacement la dispersion des données.

Histogramme de distribution

L'histogramme révèle la forme de la distribution et permet de visualiser intuitivement l'écart type. Pour créer un histogramme dans Excel ou Google Sheets, il faut sélectionner les données, puis aller dans « Insertion » > « Graphique » > « Histogramme ». L'étalement des barres indique visuellement la dispersion : plus les données sont étalées, plus l'écart type est important.

Pour améliorer la lisibilité, il est possible d'ajuster le nombre d'intervalles en fonction de la taille de l'échantillon. Une règle empirique suggère d'utiliser la racine carrée du nombre de données comme nombre d'intervalles optimal.

Courbe de Gauss (distribution normale)

La courbe de Gauss ou courbe en cloche représente la distribution normale théorique. Sur cette base, il est aisé d'évaluer l'écart type d'un jeu de données.

Pour créer une courbe de Gauss dans Excel, il faut créer une colonne de valeurs théoriques s'étalant autour de votre moyenne, puis utiliser la fonction LOI.NORMALE() pour calculer les probabilités correspondantes. Il faut ensuite créer un graphique en courbes en sélectionnant les données. La forme de la courbe révèle l'écart type : plus la courbe est aplatie et étalée, plus l'écart type est important, alors qu'une courbe haute et étroite indique un faible écart type.

Sur Google Sheets, la fonction à utiliser est NORMDIST().

Diagramme en boîte (box plot)

Le diagramme en boîte, aussi appelé box plot et boîte à moustaches, affiche visuellement la médiane, les quartiles et les valeurs aberrantes, offrant une vue complémentaire à l'écart type.

La longueur de la boîte représente l'écart interquartile (IQR), tandis que les « moustaches » s'étendent jusqu'aux valeurs extrêmes. Les points isolés au-delà des moustaches indiquent des valeurs aberrantes. Un graphique avec une boîte large et de longues moustaches suggère une forte variabilité, cohérente avec un écart type élevé.

Graphique de contrôle statistique

Le graphique de contrôle statistique utilise l'écart-type pour définir les limites de surveillance et détecter les variations anormales. Créez d'abord trois lignes horizontales : la moyenne (ligne centrale), la limite de contrôle supérieure (moyenne + 3 écarts-types), et la limite de contrôle inférieure (moyenne - 3 écarts-types).

Dans Excel, utilisez un graphique en courbes pour afficher vos données dans le temps, puis ajoutez ces trois lignes comme séries supplémentaires avec un formatage différent (lignes pointillées colorées). Les points qui dépassent les limites de contrôle signalent des variations significatives nécessitant une investigation.

Sur Google Sheets, le processus est similaire en utilisant l'éditeur de graphiques pour personnaliser l'affichage des limites. Ce type de visualisation est essentiel en contrôle qualité pour monitorer la stabilité des processus. Sept points consécutifs du même côté de la ligne centrale ou des tendances marquées indiquent également des signaux d'alarme même sans dépassement des limites.

FAQ sur l'écart type avec Excel et Google Sheets

Pour aller plus loin, apprenez à créer des graphiques, des macros et des bases de données en téléchargeant les modèles pour maîtriser Excel et Google Sheets, ou découvrez l'outil de marketing analytics de HubSpot.