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

En statistique, 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 ordonnées représente la probabilité relative qu’une valeur donnée se produise dans l’ensemble de données tandis que l’axe des abscisses trace les valeurs elles-mêmes sur le graphique pour créer une courbe en forme de cloche, d’où son nom.

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

Comme Excel n’a pas de solutions intégrées à offrir, vous devrez le tracer vous-même. C’est pourquoi nous avons développé le module complémentaire Chart Creator, un outil qui vous permet de créer des graphiques Excel avancés en quelques clics.

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

Pour tracer une courbe gaussienne, 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 propagation de vos données dans la distribution normale — ou en clair, la largeur de la courbe devrait être. Par exemple, dans la courbe en cloche illustrée ci-dessus, un écart-type de la moyenne représente la plage entre les scores à l’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 à une SD de la moyenne, 95% à deux SD et 99,7% à trois SD.

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

Mise en route

À des fins d’illustration, supposons que vous ayez les résultats aux tests de 200 élèves et que vous souhaitiez les noter « sur une courbe », ce qui signifie que les notes des élèves seront basées sur leur performance relative par rapport au reste de la classe:

Étape #1: Trouvez la moyenne.

En règle générale, les valeurs mean et SD vous sont données dès le début, mais si ce n’est pas le cas, vous pouvez facilement calculer ces valeurs en quelques étapes simples. Abordons d’abord la moyenne.

Puisque 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 scores à l’examen dans l’ensemble de données:

Une note rapide: le plus souvent, vous devrez peut-être arrondir la sortie de la formule. Pour ce faire, il suffit de l’envelopper dans la fonction RONDE comme suit:

Étape #2: Trouvez l’écart-type.

Un vers le bas, un pour aller. Heureusement, Excel a une fonction spéciale pour faire tout le sale boulot de trouver l’écart type pour vous:

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.

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

Fondamentalement, le graphique constitue un nombre massif d’intervalles (considérez-les comme des étapes) reliés entre eux par une ligne pour créer une courbe lisse.

Dans notre cas, les valeurs de l’axe des abscisses seront utilisées pour illustrer un score d’examen particulier tandis que les valeurs de l’axe des ordonnées 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 effacer sans effort les données redondantes ultérieurement en modifiant l’échelle de l’axe horizontal. Assurez-vous simplement de choisir une plage qui intégrera les trois écarts types.

Commençons un dé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 configurer une autre table d’assistance.

1. Choisissez n’importe quelle cellule vide sous les données du graphique (comme E4) et tapez « 1 », la valeur qui définit le premier intervalle.
2. Accédez à l’onglet Accueil.
3. Dans le groupe d’édition, choisissez « Remplir. »
4. Sous la colonne « Série dans, « sélectionner ». »
5. Pour « Valeur de pas « , tapez « 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 », tapez « 150 », la valeur qui représente le dernier intervalle, puis cliquez sur « OK. »

Miraculeusement, 149 cellules de la colonne E (E5:E153) ont été remplies 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 abscisses.

Maintenant, trouvez les valeurs de distribution normales — la probabilité qu’un étudiant obtienne une certaine note à l’examen représentée par une valeur particulière de l’axe des abscisses – pour chacun des intervalles. Heureusement pour vous, Excel a le cheval de bataille pour faire tous ces calculs pour vous: la NORME.Fonction DIST.

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

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

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 moment 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. Accédez à l’onglet Insérer.
3. Cliquez sur le bouton « Insérer un nuage de points (X, Y) ou un graphique à bulles ».
4. Choisissez « Scatter avec des lignes lisses. »

É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 une autre table d’assistance comme suit:

Tout d’abord, copiez la valeur moyenne (F1) à côté de la cellule correspondante dans la colonne X-Valeur (I5).

Ensuite, calculez les valeurs d’écart-type inférieures à la moyenne en entrant cette formule simple dans la cellule I4:

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:

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

Enfin, remplissez les valeurs d’étiquette de l’axe 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 graphique et choisissez « Sélectionner des données. »

Dans la boîte de dialogue qui apparaît, sélectionnez « Ajouter. »

Mettez en Surbrillance les plages de cellules respectives du tableau d’assistance – I2:I8 pour « Valeurs de la série X » et J2: J8 pour « Valeurs de la série Y » — et cliquez sur « OK. »

Étape #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, cliquez avec le bouton droit sur le graphique et sélectionnez « Modifier le type de graphique. »

Ensuite, concevez un graphique combiné:

1. Accédez à l’onglet Combo.
2. Pour la série « Series2 », remplacez « Type de graphique » par « Dispersion. »
   • Note: Assurez-vous que « Series1 » reste comme « Dispersion avec des lignes lisses. »Parfois, Excel le changera lorsque vous créez un Combo, assurez—vous également que « Series1 » n’est pas poussé sur l’axe secondaire – la case à cocher à côté du type de graphique ne doit pas être cochée.
3. Cliquez sur « OK. »

É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 « Formater l’axe » dans le menu.

Une fois que le volet des tâches apparaît, procédez comme suit:

• Accédez à l’onglet Options de l’axe.
• Définissez la valeur des limites minimales sur « 15. »
• Définissez la valeur Limite maximale sur « 125. »

Vous pouvez modifier la plage d’échelle de l’axe comme bon vous semble, mais comme vous connaissez les plages d’écart-type, définissez les valeurs des limites un peu éloignées de chacune 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.

Lorsque 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 la série « Series2 » 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. Cliquez avec le bouton droit sur n’importe quelle étiquette de données de la série « Series2 ».
2. Sélectionnez  » Formater les étiquettes de données. »
3. Dans le volet des tâches, passez à l’onglet Options d’étiquette.
4. Cochez la case « Valeur X ».
5. Décochez la case « Valeur Y ».
6. Sous « Position de l’étiquette », choisissez « Ci-dessus. »

De plus, vous pouvez maintenant supprimer les lignes de quadrillage (cliquez dessus avec le bouton droit de la souris > Supprimer).

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

Enfin, recolorisez les points pour les aider à s’adapter au style de votre graphique.

1. Cliquez avec le bouton droit sur n’importe quelle étiquette de données de la série « Series2 ».
2. Cliquez sur le bouton « Remplir ».
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. Choisissez « Pas de contour. »

Étape #12: Ajoutez des lignes verticales (facultatif).

En guise de réglage final, vous pouvez ajouter des lignes verticales au graphique pour accentuer les valeurs SD.

• Sélectionnez le tracé du graphique (de cette façon, les lignes seront insérées directement dans le graphique).
• Accédez à l’onglet Insérer.
• Cliquez sur le bouton « Formes ».
• Choisissez la ligne « . »

Maintenez la touche « SHIFT » enfoncée 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 — montrant vos précieuses données de distribution.

Et c’est comme ça que vous le faites. Vous pouvez maintenant choisir n’importe quel jeu de données et créer une courbe en cloche de distribution normale en suivant ces étapes simples !