Comment analyser la dispersion de vos données avec l'écart-type dans Excel ?

Dans ce tutoriel, je vais vous montrer comment utiliser l'écart-type dans Excel pour analyser efficacement la dispersion de vos données et découvrir des informations cachées que les simples moyennes ne révèlent pas.

Nous verrons comment l'écart-type nous aide à comprendre si nos données sont homogènes ou dispersées, et comment cela peut changer complètement notre interprétation des résultats.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons pouvoir utiliser le tableau suivant dans lequel sont collectées des données concernant la satisfaction des clients pour une chaîne de restaurants présente dans différentes villes de France.

Chaque restaurant a reçu des évaluations sur quatre critères distincts : la propreté, le service, la qualité des plats et le rapport qualité-prix. Une note globale a également été attribuée à chaque établissement.

Dans ce tableau, la colonne « Note globale », n’est pas une simple formule de moyenne, mais une série de valeurs qui représentent la note globale attribuée par les clients, qui peut être légèrement différente de la moyenne arithmétique des quatre critères.

2. Comprendre le concept d'écart-type

   2.1. Pourquoi l'écart-type est important

Avant de plonger dans les calculs, prenons un moment pour comprendre ce qu'est l'écart-type et pourquoi il est si utile dans l'analyse de données. L'écart-type est une mesure statistique qui nous indique comment les valeurs d'un ensemble de données sont dispersées autour de la moyenne. En termes simples, il nous dit si nos données sont regroupées près de la moyenne ou si elles sont éparpillées sur une large gamme de valeurs.

Pourquoi est-ce important ? Parce que deux ensembles de données peuvent avoir exactement la même moyenne, mais raconter des histoires complètement différentes.

Dans le contexte de notre analyse de satisfaction client, cela peut être crucial. Un restaurant avec un écart-type faible offre une expérience cohérente, tandis qu'un restaurant avec un écart-type élevé peut avoir des points forts remarquables, mais aussi des faiblesses notables. Cette information peut orienter les décisions de gestion, comme la formation du personnel ou les investissements en équipement.

   2.2. Les différentes fonctions d'écart-type dans Excel

Excel propose plusieurs fonctions pour calculer l'écart-type, et il est important de choisir la bonne en fonction de notre situation.

La première distinction à faire est celle entre l'écart-type d'une population et l'écart-type d'un échantillon. Si nous avons des données pour l'ensemble complet des éléments que nous étudions (comme c'est le cas dans notre exemple avec tous les restaurants de la chaîne), nous utilisons l'écart-type de population.

Si nous n'avons qu'un échantillon d'une population plus large (par exemple, si nous n'avions que quelques restaurants d'une chaîne qui en compte des centaines), nous utiliserions l'écart-type d'échantillon.

Dans Excel, les fonctions à utiliser sont :

- `ECARTYPE.STANDARD` (ou `STDEV.P` en anglais) : pour calculer l'écart-type d'une population complète.

- `ECARTYPE` (ou `STDEV.S` en anglais) : pour calculer l'écart-type d'un échantillon.

Il existe également d'anciennes fonctions comme `ECARTYPEP` et `ECARTYPE`, mais elles sont maintenues uniquement pour des raisons de compatibilité avec les versions antérieures d'Excel. Il est recommandé d'utiliser les nouvelles fonctions, qui sont plus précises et mieux documentées.

Attention, de bien utiliser la fonction d'écart-type adaptée. Si nous utilisons `ECARTYPE` au lieu de `ECARTYPE.STANDARD` pour une population complète, nous obtiendrons une valeur légèrement plus élevée, car la formule d'échantillon compense le fait que vous n'avez pas toutes les données de la population.

Maintenant que nous avons compris le concept d'écart-type et les fonctions disponibles dans Excel, passons au calcul concret de l'écart-type pour nos données de satisfaction client.

3. Calculer l'écart-type dans notre tableau

   3.1. Ajouter une colonne pour l'écart-type par restaurant

Maintenant que nous avons compris l'importance de l'écart-type, ajoutons une colonne à notre tableau pour le calculer pour chaque restaurant. Cette colonne nous permettra de voir rapidement quels restaurants ont des évaluations cohérentes sur les différents critères et lesquels présentent des variations importantes.

Dans la cellule G8, nous saisissons « Écart-type »

Maintenant, nous allons calculer l'écart-type pour le premier restaurant, Paris Centre.

Dans la cellule G9, nous saisissons la formule suivante :

  =ECARTYPE.STANDARD(B9:E9)

Après avoir appuyé sur [Entrée], nous voyons le résultat de notre calcul. Pour Paris Centre, nous obtenons un écart-type d'environ 1,12.

Cela indique une certaine variation dans les évaluations de ce restaurant.

Maintenant, nous devons étendre cette formule pour tous les autres restaurants en positionnant le curseur sur le petit carré noir dans le coin inférieur droit de la cellule (appelé la poignée de recopie). Le curseur se transforme en une croix noire. Nous cliquons et maintenons le bouton de la souris enfoncé, puis nous faisons glisser vers le bas jusqu'à la dernière ligne de notre tableau. Lorsque nous relâchons le bouton de la souris, Excel copie automatiquement la formule pour toutes les lignes sélectionnées, en ajustant les références de cellules pour chaque ligne.

Nous avons maintenant calculé l'écart-type pour tous nos restaurants. Prenons un moment pour examiner les résultats.

   3.2. Interpréter les résultats

D'abord, rappelons-nous qu'un écart-type faible indique que les évaluations sont regroupées autour de la moyenne, ce qui suggère une expérience cohérente sur tous les critères. Un écart-type élevé, en revanche, indique que les évaluations sont dispersées, ce qui suggère une expérience inégale avec des points forts et des points faibles.

En examinant nos résultats, nous pouvons identifier des tendances intéressantes.

Par commencer, nous constatons que le restaurant de Nice Promenade a un écart-type d'environ 1,48, le plus élevé de notre tableau. Cela indique une grande variation dans les évaluations. En regardant les données, nous voyons que ce restaurant a d'excellentes notes pour la propreté (9), mais une note beaucoup plus faible pour le rapport qualité-prix (5).

Cette information est précieuse pour la direction, car elle indique un domaine spécifique où des améliorations sont nécessaires.

À l'inverse, le restaurant de Toulouse Capitole a un écart-type d'environ 0,71, tout comme celui de Montpellier Comédie. Cela indique des évaluations cohérentes sur tous les critères. En regardant les données, nous voyons que les notes varient de 6 à 8, sans écarts extrêmes. Cela suggère une expérience client équilibrée, sans points faibles majeurs, mais aussi sans points forts exceptionnels.

Un autre cas intéressant est celui du restaurant de Lyon Bellecour. Il a la meilleure note globale (8,3) et un écart-type relativement faible (0,83), ce qui indique qu'il offre une expérience de haute qualité et cohérente sur tous les critères. C'est un exemple à suivre pour les autres restaurants de la chaîne.

Ces interprétations nous montrent comment l'écart-type peut enrichir notre analyse au-delà des simples moyennes. Nous pouvons maintenant identifier non seulement les restaurants qui ont les meilleures évaluations globales, mais aussi ceux qui offrent l'expérience la plus cohérente et ceux qui ont des domaines spécifiques à améliorer.

Pour rendre ces informations encore plus utiles, passons à la visualisation de nos résultats avec des outils Excel avancés.

4. Améliorer notre analyse avec des outils visuels

   4.1. Utiliser le formatage conditionnel pour visualiser les écarts-types

Pour rendre notre analyse plus intuitive et visuelle, nous allons utiliser le formatage conditionnel dans Excel. Cette fonctionnalité permet de formater automatiquement les cellules en fonction de leur valeur, ce qui facilite l'identification des tendances et des anomalies.

Nous commençons par sélectionner la colonne G, puis nous nous rendons dans l'onglet « Accueil » du ruban Excel et nous cliquons sur le bouton « Mise en forme conditionnelle ». Dans le menu déroulant, nous sélectionnons « Nuances de couleurs ».

Excel nous propose différentes options de couleurs. Pour notre analyse, nous choisissons une échelle qui va du vert (pour les valeurs faibles d'écart-type, indiquant une cohérence) au rouge (pour les valeurs élevées d'écart-type, indiquant une dispersion). Spécifiquement, nous cliquons sur l'option « Vert - Jaune - Rouge ».

Maintenant, notre colonne d'écart-type est colorée en fonction des valeurs. Les restaurants avec des écarts-types faibles (expérience cohérente) sont en vert, tandis que ceux avec des écarts-types élevés (expérience variable) sont en rouge. Cela nous permet de repérer instantanément les restaurants qui présentent des variations importantes dans leurs évaluations.

Avec ce formatage, nous pouvons rapidement identifier les points forts et les points faibles de chaque restaurant, ainsi que leur niveau de cohérence global.

   4.2. Créer un graphique pour comparer moyenne et écart-type

Pour approfondir notre analyse, créons un nuage de points qui nous permettra de visualiser la relation entre la note globale et l'écart-type pour chaque restaurant.

Pour cela, nous commençons par sélectionner les données de note globale (colonne F) et d'écart-type (colonne G). Ensuite, nous nous rendons dans l'onglet « Insertion » du ruban Excel et nous cliquons sur le bouton « Nuage de points » dans le groupe « Graphiques ».

Excel crée un graphique qui place chaque restaurant comme un point dans un repère où l'axe des X (abscisses) représente la note globale et l'axe des Y (ordonnées) l'écart-type.

Pour améliorer la lisibilité, ajoutons un titre comme « Relation entre note globale et écart-type par restaurant » et des étiquettes d'axes appropriées.

Pour identifier facilement chaque point, nous pouvons ajouter des étiquettes de données avec le nom du restaurant. Il suffit de faire un clic droit sur les points et de sélectionner « Ajouter des étiquettes de données », puis de personnaliser ces étiquettes pour afficher le nom des restaurants (colonne A).

Ce nuage de points permet de diviser nos restaurants en quatre catégories selon les quadrants formés par les valeurs moyennes de note globale et d'écart-type :

1. Quadrant supérieur droit : Restaurants avec notes élevées mais forte variabilité (performance inconstante)

2. Quadrant supérieur gauche : Restaurants avec notes faibles et forte variabilité (problèmes importants à résoudre)

3. Quadrant inférieur gauche : Restaurants avec notes faibles mais constantes (qualité constamment médiocre)

4. Quadrant inférieur droit : Restaurants avec notes élevées et faible variabilité (excellence constante)

Les restaurants comme Lyon Bellecour se démarquent dans le quadrant inférieur droit avec une note globale élevée et un écart-type relativement faible (0,83), ce qui en fait un modèle d'excellence constante pour la chaîne. À l'inverse, des établissements comme Nice Promenade présentent une variabilité importante malgré une note moyenne correcte, suggérant une expérience client inconsistante qui nécessite d'être stabilisée.