Comment créer une courbe de distribution normale en cloche dans Excel

Ce didacticiel montrera comment créer une courbe en cloche de distribution normale dans toutes les versions d'Excel : 2007, 2010, 2013, 2016 et 2022.

En statistiques, une courbe en cloche (également appelée distribution normale standard ou courbe gaussienne) est un graphique symétrique qui illustre la tendance des données à se regrouper autour d'une valeur centrale, ou moyenne, dans un ensemble de données donné.

L'axe des y représente la probabilité relative qu'une valeur donnée se produise dans l'ensemble de données tandis que l'axe des x trace les valeurs elles-mêmes sur le graphique pour créer une courbe en forme de cloche, d'où le nom.

Le graphique nous aide à analyser si une valeur particulière fait partie de la variation attendue ou est statistiquement significative et, par conséquent, doit être examinée de plus près.

Étant donné qu'Excel n'a aucune solution intégrée à offrir, vous devrez le tracer vous-même. C'est pourquoi nous avons développé le Chart Creator Add-in, un outil qui vous permet de créer des graphiques Excel avancés en quelques clics.

Dans ce didacticiel pas à pas, vous apprendrez à créer une courbe en cloche de distribution normale dans Excel à partir de zéro :

Pour tracer une courbe de Gauss, vous devez savoir deux choses :

• La moyenne (également connue sous le nom de mesure standard). Cela détermine le centre de la courbe qui, à son tour, caractérise la position de la courbe.
• L'écart type (SD) des mesures. Cela définit la répartition de vos données dans la distribution normale - ou en anglais simple, la largeur de la courbe. Par exemple, dans la courbe en cloche illustrée ci-dessus, un écart type de la moyenne représente la plage entre les notes d'examen de 53 et 85.

Plus le SD est bas, plus la courbe est haute et moins vos données seront étalées, et vice versa.

Il convient de mentionner la règle 68-95-99,7 qui peut être appliquée à n'importe quelle courbe de distribution normale, ce qui signifie qu'environ 68% de vos données seront placées dans un SD de la moyenne, 95% dans deux SD et 99,7% dans trois SD.

Maintenant que vous connaissez l'essentiel, passons de la théorie à la pratique.

Commencer

À des fins d'illustration, supposons que vous ayez les résultats des tests de 200 étudiants et que vous souhaitiez les noter « sur une courbe », ce qui signifie que les notes des étudiants seront basées sur leurs performances relatives par rapport au reste de la classe :

Étape 1 : Trouvez la moyenne.

En règle générale, on vous donne les valeurs moyennes et SD dès le début, mais si ce n'est pas le cas, vous pouvez facilement calculer ces valeurs en quelques étapes simples. Attaquons-nous d'abord à la moyenne.

Étant donné que la moyenne indique la valeur moyenne d'un échantillon ou d'une population de données, vous pouvez trouver votre mesure standard à l'aide de la fonction MOYENNE.

Tapez la formule suivante dans n'importe quelle cellule vide (F1 dans cet exemple) à côté de vos données réelles (colonnes A et B) pour calculer la moyenne des notes d'examen dans l'ensemble de données :

1	=MOYENNE(B2:B201)

Une note rapide : le plus souvent, vous devrez peut-être arrondir le résultat de la formule. Pour ce faire, enveloppez-le simplement dans la fonction ROUND comme suit :

1	=ROND(MOYENNE(B2:B201),0)

Étape #2 : Trouvez l'écart type.

Un à terre, un à faire. Heureusement, Excel a une fonction spéciale pour faire tout le sale boulot de trouver l'écart type pour vous :

1	=STDEV.P(B2:B201)

Encore une fois, la formule sélectionne toutes les valeurs de la plage de cellules spécifiée (B2:B201) et calcule son écart type - n'oubliez pas d'arrondir également la sortie.

1	=ROND(DEV.ST.P(B2:B201),0)

Étape #3 : Configurez les valeurs de l'axe des X pour la courbe.

Fondamentalement, le graphique constitue un grand nombre d'intervalles (considérez-les comme des étapes) réunis par une ligne pour créer une courbe lisse.

Dans notre cas, les valeurs de l'axe des x seront utilisées pour illustrer un score d'examen particulier tandis que les valeurs de l'axe des y nous indiqueront la probabilité qu'un étudiant obtienne ce score à l'examen.

Techniquement, vous pouvez inclure autant d'intervalles que vous le souhaitez - vous pouvez facilement effacer les données redondantes plus tard en modifiant l'échelle de l'axe horizontal. Assurez-vous simplement de choisir une plage qui incorporera les trois écarts types.

Commençons un compte à un (car il n'y a aucun moyen pour un étudiant d'obtenir une note négative à l'examen) et allons jusqu'à 150 - peu importe que ce soit 150 ou 1500 - pour mettre en place une autre table d'aide.

1. Choisissez n'importe quelle cellule vide sous les données du graphique (telles que E4) et tapez “1,” la valeur qui définit le premier intervalle.
2. Naviguez vers le Accueil languette.
3. Dans le Édition groupe, choisissez "Remplir.”
4. Sous "Série en," sélectionnez "Colonne.”
5. Pour "Valeur de pas," taper “1.” Cette valeur détermine les incréments qui seront automatiquement ajoutés jusqu'à ce qu'Excel atteigne le dernier intervalle.
6. Pour "valeur d'arrêt," taper "150,” la valeur qui représente le dernier intervalle, et cliquez sur "D'ACCORD.”

Miraculeusement, 149 cellules dans la colonne E (E5:E153) ont été remplis avec les valeurs allant de 2 à 150.

REMARQUE : Ne masquez pas les cellules de données d'origine comme indiqué sur les captures d'écran. Sinon, la technique ne fonctionnera pas.

Étape 4: Calculez les valeurs de distribution normales pour chaque valeur de l'axe des x.

Maintenant, trouvez les valeurs de distribution normales - la probabilité qu'un étudiant obtienne un certain score d'examen représenté par une valeur particulière de l'axe des x - pour chacun des intervalles. Heureusement pour vous, Excel a le cheval de bataille pour faire tous ces calculs pour vous : la fonction NORM.DIST.

Tapez la formule suivante dans la cellule de droite (F4) de votre premier intervalle (E4):

1	=NORM.DIST(E4,$F$1,$F$2,FAUX)

Voici la version décodée pour vous aider à vous ajuster en conséquence :

1	=NORM.DIST([le premier intervalle],[la moyenne(référence absolue)],[l'écart type(référence absolue),FALSE)

Vous verrouillez les valeurs moyennes et SD afin de pouvoir exécuter sans effort la formule pour les intervalles restants (E5:E153).

Maintenant, double-cliquez sur la poignée de remplissage pour copier la formule dans le reste des cellules (F5:F153).

Étape 5 : Créez un nuage de points avec des lignes lisses.

Enfin, le temps de construire la courbe en cloche est venu :

1. Sélectionnez n'importe quelle valeur dans la table d'assistance contenant les valeurs des axes x et y (E4:F153).
2. Allez au Insérer languette.
3. Clique le "Insérer un nuage de points (X, Y) ou un graphique à bulles" bouton.
4. Choisir "Dispersez avec des lignes douces.

Étape #6: Configurez la table d'étiquettes.

Techniquement, vous avez votre courbe en cloche. Mais il serait difficile à lire car il manque de données le décrivant.

Rendons la distribution normale plus informative en ajoutant les étiquettes illustrant toutes les valeurs d'écart-type au-dessous et au-dessus de la moyenne (vous pouvez également les utiliser pour afficher les scores z à la place).

Pour cela, configurez encore une autre table d'assistance comme suit :

Copiez d'abord la valeur moyenne (F1) à côté de la cellule correspondante dans la colonne X-Value (I5).

Ensuite, calculez les valeurs d'écart-type en dessous de la moyenne en entrant cette formule simple dans cellule I4:

1

=I5-$F$2

En termes simples, la formule soustrait la somme des valeurs d'écart type précédentes de la moyenne. Maintenant, faites glisser la poignée de remplissage vers le haut pour copier la formule dans les deux cellules restantes (I2:I3).

Répétez le même processus pour les écarts types au-dessus de la moyenne en utilisant la formule miroir :

1

=I5+$F$2

De la même manière, exécutez la formule pour les deux autres cellules (I7:I8).

Enfin, remplissez les valeurs des étiquettes de l'axe des y (J2:J8) avec des zéros comme vous voulez que les marqueurs de données soient placés sur l'axe horizontal.

Étape #7 : Insérez les données de l'étiquette dans le graphique.

Maintenant, ajoutez toutes les données que vous avez préparées. Faites un clic droit sur le tracé de la carte et choisissez "Sélectionnez Données.”

Dans la boîte de dialogue qui s'ouvre, sélectionnez "Ajouter.”

Mettez en surbrillance les plages de cellules respectives dans le tableau d'aide-I2:I8 pour "Valeurs de la série X" et J2:J8 pour "Valeurs de la série Y »-et cliquez sur "D'ACCORD.”

Étape n°8 : modifiez le type de graphique de la série d'étiquettes.

Notre prochaine étape consiste à modifier le type de graphique de la série nouvellement ajoutée pour que les marqueurs de données apparaissent sous forme de points. Pour ce faire, faites un clic droit sur le tracé de la carte et sélectionnez "Changer le type de graphique.”

Ensuite, concevez un graphique combiné :

1. Naviguez vers le Combiné languette.
2. Pour Série « Série2 » monnaie "Type de graphique" à "Dispersion.”
   • Remarque : Assurez-vous que "Série1" reste comme "Dispersion avec des lignes douces. " Parfois, Excel le modifie lorsque vous effectuez un Combiné Assurez-vous également "Série1" n'est pas poussé vers l'axe secondaire - la case à côté du type de graphique ne doit pas être cochée.
3. Cliquez sur "d'accord.”

Étape #9 : Modifiez l'échelle de l'axe horizontal.

Centrez le graphique sur la courbe en cloche en ajustant l'échelle de l'axe horizontal. Faites un clic droit sur l'axe horizontal et choisissez "Axe de formatage" dans le menu.

Une fois le volet Office affiché, procédez comme suit :

• Allez au Options d'axe languette.
• Met le Limites minimales valeur à "15.”
• Met le Limites maximales valeur à "125.”

Vous pouvez modifier la plage d'échelle de l'axe comme bon vous semble, mais puisque vous connaissez les plages d'écart type, définissez les valeurs des limites un peu à l'écart de chacun de vos troisièmes écarts types pour afficher la « queue » de la courbe.

Étape #10 : Insérez et positionnez les étiquettes de données personnalisées.

Au fur et à mesure que vous peaufinez votre graphique, assurez-vous d'ajouter les étiquettes de données personnalisées. Tout d'abord, faites un clic droit sur n'importe quel point représentant Série « Série2 » et sélectionnez "Ajouter des étiquettes de données.”

Ensuite, remplacez les étiquettes par défaut par celles que vous avez précédemment configurées et placez-les au-dessus des marqueurs de données.

1. Faites un clic droit sur n'importe quel Série « Série2 » étiquette de données.
2. Sélectionnez "Formater les étiquettes de données.”
3. Dans le volet Office, basculez vers le Options d'étiquette languette.
4. Vérifier la "Valeur X" boîte.
5. Décochez la case "Valeur Y" boîte.
6. Sous "Position de l'étiquette," choisir "Dessus.”

De plus, vous pouvez maintenant supprimer le quadrillage (clic droit dessus > Supprimer).

Étape 11 : Recolorez les marqueurs de données (facultatif).

Enfin, recolorez les points pour les aider à s'adapter à votre style de graphique.

1. Faites un clic droit sur n'importe quel Série « Série2 » étiquette de données.
2. Clique le "Remplir" bouton.
3. Choisissez votre couleur dans la palette qui apparaît.

Supprimez également les bordures autour des points :

1. Cliquez à nouveau avec le bouton droit sur le même marqueur de données et sélectionnez "Contour.”
2. Choisir "Pas de contour.”

Étape n°12 : ajoutez des lignes verticales (facultatif).

En guise d'ajustement final, vous pouvez ajouter des lignes verticales au graphique pour aider à mettre en valeur les valeurs SD.

• Sélectionnez le tracé du graphique (de cette façon, les lignes seront insérées directement dans le graphique).
• Allez au Insérer languette.
• Clique le "Formes" bouton.
• Choisir "Ligne.”

Maintenez le "DÉCALAGE" tout en faisant glisser la souris pour tracer des lignes parfaitement verticales de chaque point à l'endroit où chaque ligne rencontre la courbe en cloche.

Changez le titre du graphique et votre courbe en cloche améliorée est prête à afficher vos précieuses données de distribution.

Et c'est comme ça que tu fais. Vous pouvez maintenant choisir n'importe quel ensemble de données et créer une courbe en cloche de distribution normale en suivant ces étapes simples !