Comment tracer une courbe en cloche dans Excel (et un raccourci de 30 secondes)

Vous avez une colonne de nombres : notes de test, délais de livraison, poids de produits ou tailles. Quelqu'un demande : « Est-ce normal ? Où se situent la plupart des valeurs ? » Une courbe en cloche répond à cela en une seule image.

Le problème, c'est qu'Excel n'a pas de bouton « courbe en cloche » pour créer un graphique. Vous devez calculer la courbe vous-même, puis la transformer en graphique. Ce guide vous présente les deux méthodes, afin que vous puissiez choisir celle qui correspond au temps dont vous disposez.

Qu'est-ce qu'une courbe en cloche ?

Une courbe en cloche est un graphique lissé d'une distribution normale. Elle montre comment les valeurs se répartissent autour de la moyenne. La plupart des valeurs se situent près du centre, et moins de valeurs apparaissent à mesure que l'on s'éloigne de chaque côté.

Deux nombres définissent chaque courbe en cloche :

• Moyenne : la moyenne. Elle détermine l'emplacement du pic.
• Écart type (ET) : la distance typique par rapport à la moyenne. Il détermine la largeur de la courbe.

Une fois ces deux nombres connus, vous pouvez aussi utiliser la règle 68-95-99,7. Environ 68 % des valeurs se situent à moins de 1 écart type de la moyenne, 95 % à moins de 2 écarts types, et 99,7 % à moins de 3 écarts types.

Pour approfondir l'écart type lui-même, consultez ce guide sur l'écart type dans Excel.

Le fichier d'exemple

Les deux méthodes ci-dessous utilisent le même jeu de données public : weight-height.csv. Il contient 10 000 lignes et trois colonnes.

La taille est en pouces. L'objectif est simple : tracer la courbe en cloche des tailles pour les hommes et les femmes sur le même graphique et voir à quel point elles sont éloignées.

Méthode A : tracer une courbe en cloche dans Excel manuellement

Cela fonctionne dans toute version récente d'Excel. Comptez 15 à 30 minutes la première fois, davantage avec deux groupes.

Étape 1 : calculer la moyenne et l'écart type

Placez ces formules dans des cellules vides. L'exemple suppose que Gender est dans la colonne A et Height dans la colonne B, lignes 2 à 10001.

Male mean:    =AVERAGEIF(A2:A10001,"Male",B2:B10001)
Male SD:      =STDEV.S(IF(A2:A10001="Male",B2:B10001))
Female mean:  =AVERAGEIF(A2:A10001,"Female",B2:B10001)
Female SD:    =STDEV.S(IF(A2:A10001="Female",B2:B10001))

Avec un seul groupe, vous pouvez utiliser =AVERAGE(B2:B10001) et =STDEV.S(B2:B10001).

Pour ce fichier, vous devriez obtenir une moyenne masculine d'environ 69,03, un écart type de 2,86, et une moyenne féminine d'environ 63,71, un écart type de 2,70.

Étape 2 : créer une colonne de valeurs x

Le graphique a besoin de points le long de l'axe des x. Commencez environ 3 écarts types en dessous de la moyenne la plus basse et terminez environ 3 écarts types au-dessus de la moyenne la plus élevée.

Pour les tailles, 54 à 80 pouces par pas de 0,5 fonctionne bien. Saisissez 54 en D2, puis en D3 :

=D2+0.5

Recopiez vers le bas jusqu'à atteindre 80.

Étape 3 : calculer la hauteur de la courbe avec NORM.DIST

En E2, calculez la courbe masculine. En F2, calculez la courbe féminine. Remplacez les références de cellules par celles qui contiennent votre moyenne et votre écart type.

E2: =NORM.DIST(D2, $H$2, $H$3, FALSE)
F2: =NORM.DIST(D2, $H$4, $H$5, FALSE)

Le dernier argument doit être FALSE. Cela renvoie la hauteur de la courbe (la densité). TRUE renvoie un total cumulé, ce qui dessine une forme en S au lieu d'une cloche.

Recopiez les deux formules vers le bas pour qu'elles correspondent aux valeurs x.

Étape 4 : insérer un graphique en nuage de points avec courbes lissées

1. Sélectionnez les colonnes D, E et F.
2. Allez dans Insertion > Graphiques > Nuage de points > Nuage de points avec courbes lissées.
3. Nommez les séries « Homme » et « Femme ».
4. Nommez l'axe des x « Taille (pouces) ».
5. Facultatif : ajoutez une ligne verticale à chaque moyenne avec une série supplémentaire à deux points.

Vous disposez maintenant d'une courbe en cloche. Cela fonctionne, mais chaque nouveau fichier ou groupe implique de répéter les colonnes auxiliaires et la configuration du graphique.

Méthode B : tracer une courbe en cloche avec une phrase dans RowSpeak

RowSpeak est un espace de travail IA pour les fichiers Excel et CSV. Au lieu de créer des colonnes auxiliaires, vous décrivez le graphique souhaité et RowSpeak effectue les calculs et le tracé.

Voici l'enregistrement réel, utilisant le même fichier weight-height.csv :

Étape 1 : importer votre fichier

Ouvrez RowSpeak et glissez-déposez votre fichier Excel ou CSV. Il lit les colonnes et trouve les nombres que vous souhaitez tracer.

Étape 2 : demander le graphique

Saisissez votre demande en langage naturel :

Tracez les courbes en cloche de la taille pour les hommes et les femmes sur le même graphique.

RowSpeak affiche son plan avant de commencer : confirmer les données de taille par genre, générer les courbes en cloche superposées, puis fournir le graphique et un résumé.

Étape 3 : examiner le résultat

Vous obtenez un graphique « Répartition des tailles par genre » avec les deux courbes, une ligne en pointillés à chaque moyenne et une infobulle au survol. Sous le graphique, RowSpeak écrit ce que montrent les courbes :

• Hommes : moyenne 69,03 po, écart type 2,86 po.
• Femmes : moyenne 63,71 po, écart type 2,70 po.
• Les deux courbes ont presque la même forme. La courbe des femmes se situe environ 5,3 pouces à gauche.
• Elles se chevauchent modérément entre environ 60 et 72 pouces.

Vous pouvez poursuivre la conversation. Demandez-lui d'ombrer les plages de ±1, ±2 et ±3 écarts types, de passer aux centimètres ou de changer les couleurs. Vous pouvez aussi essayer cela sur la page de création de graphique en courbe en cloche avec un fichier d'exemple.

Invites que vous pouvez copier

Remplacez les noms de colonnes par ceux de votre fichier.

Tracez une courbe en cloche de la colonne Score. Marquez la moyenne et affichez les plages de ±1, ±2 et ±3 écarts types.

Superposez une courbe normale à un histogramme du délai de livraison et dites-moi si les données semblent suivre une distribution normale.

Tracez une courbe en cloche de la valeur des commandes et listez les lignes qui se trouvent à plus de 2 écarts types de la moyenne.

Tracez une courbe en cloche des notes d'examen et indiquez quelles notes correspondent à A, B, C, D et F si les notes suivent la règle 68-95-99,7.

Comment vérifier votre courbe en cloche avant de la partager

Une courbe en cloche semble convaincante même lorsqu'elle ne convient pas aux données. Effectuez d'abord ces vérifications.

1. Les données n'ont-elles qu'un seul pic ? Tracez d'abord un histogramme. S'il montre deux bosses ou une longue queue d'un côté, une courbe en cloche induira en erreur. Ce guide sur les histogrammes montre comment en réaliser un.
2. La moyenne et l'écart type correspondent-ils à votre fichier ? Comparez-les avec un rapide AVERAGE et STDEV.S, ou demandez à RowSpeak d'afficher les nombres dans un tableau.
3. L'échantillon est-il suffisamment grand ? Avec moins d'environ 30 valeurs, une courbe lissée suggère plus de certitude que vous n'en avez. Affichez plutôt les valeurs brutes.
4. La règle 68-95-99,7 se vérifie-t-elle globalement ? Dans le fichier d'exemple, 68,9 % des hommes se situent à moins de 1 écart type et 95,4 % à moins de 2 écarts types. Si vos proportions sont très différentes, les données ne sont probablement pas normales.

Erreurs courantes

• Utiliser TRUE dans NORM.DIST. Vous obtenez une courbe cumulée en forme de S, pas une cloche.
• Choisir un graphique en courbes au lieu d'un nuage de points. Un graphique en courbes traite les valeurs x comme des étiquettes, donc des pas irréguliers déforment la courbe.
• Trop peu de valeurs x. Des pas trop larges rendent la courbe irrégulière. Utilisez au moins 40 à 60 points sur toute la plage.
• Tracer des données asymétriques comme une cloche. Les revenus, les prix et les valeurs de commande ont généralement une longue queue à droite. Utilisez un histogramme pour celles-ci.
• Mélanger les groupes. Les tailles des hommes et des femmes réunies forment une seule courbe large qui ne décrit aucun des deux groupes. Séparez d'abord par groupe.

Quelle méthode utiliser ?

Utilisez Excel manuellement si vous avez besoin d'un modèle intégré à un classeur et que vous tracez souvent la même colonne.

Utilisez RowSpeak si vous voulez le graphique immédiatement, devez comparer des groupes ou souhaitez une explication écrite à coller dans un rapport. C'est aussi la voie la plus rapide lorsque le fichier suivant arrive avec des colonnes différentes.

Si votre objectif est une courbe en cloche claire à partir de vos propres données, commencez par le fichier que vous avez déjà. Importez-le dans le générateur de graphique en courbe en cloche RowSpeak, demandez la courbe en une phrase, et vérifiez la moyenne et l'écart type avant de la partager.