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

Ce didacticiel montre comment créer une courbe en cloche de distribution normale dans toutes les versions dExcel: 2007, 2010, 2013 , 2016 et 2019.

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

Laxe des y représente la probabilité relative quune valeur donnée se produise dans lensemble de données tandis que laxe des x trace les valeurs elles-mêmes sur le graphique pour créer une forme de cloche courbe, doù son 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é quExcel ne propose aucune solution intégrée, vous devrez le tracer vous-même. Cest pourquoi nous avons développé le complément Chart Creator, un outil qui vous permet de créer des graphiques Excel avancés en quelques clics.

Dans cette étape- 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 appelée mesure standard). Ceci 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 ci-dessus, un écart type de la moyenne représente la fourchette entre les notes dexamen de 53 et 85.

Plus lécart-type est bas, plus la courbe est haute et moins vos données seront réparties, et vice versa.

Il convient de mentionner la règle 68-95-99.7 qui peut être appliquée à nimporte quelle courbe de distribution normale, ce qui signifie quenviron 68% de vos données vont être placées à un écart-type de la moyenne, à 95% à deux écarts-types et à 99,7% à trois écarts-types.

Maintenant que vous connaître lessentiel, passons de la théorie à la pratique.

Premiers pas

À des fins dillustration, supposons que vous ayez les résultats des tests de 200 élèves et que vous vouliez les noter « sur une courbe », Ce qui signifie que les notes des élèves 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, la moyenne vous est donnée et les valeurs SD dès le départ, mais si ce n’est pas le cas, vous pouvez facilement calculer ces valeurs en quelques étapes simples. Commençons par la moyenne.

Puisque la moyenne indique la valeur moyenne dun échantillon ou dune population de données, vous pouvez trouver votre mesure standard à laide de la fonction MOYENNE.

Tapez la formule suivante dans nimporte 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 dexamen dans lensemble de données:

Une note rapide : le plus souvent, vous devrez peut-être arrondir la sortie de la formule. Pour ce faire, enveloppez-le simplement dans la fonction ROUND 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 recherche de 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. Noubliez pas darrondir la sortie également.

Étape n ° 3: configurer les valeurs de laxe des x pour la courbe.

Fondamentalement, le graphique constitue un nombre massif dintervalles ( Considérez-les comme des étapes) jointes avec une ligne pour créer une courbe lisse.

Dans notre cas, les valeurs de laxe des x seront utilisées pour illustrer un score dexamen particulier tandis que les valeurs de laxe des y le diront nous la probabilité quun élève obtienne ce score à lexamen.

Techniquement, vous pouvez inclure autant dintervalles que vous le souhaitez – vous pouvez effacer sans effort les données redondantes plus tard en modifiant léchelle de laxe horizontal. Assurez-vous simplement de choisir une fourchette qui intégrera les trois écarts types.

Commençons par compter un (car il ny a aucun moyen pour un élève dobtenir une note dexamen négative) et allons jusquà 150 – peu importe que ce soit 150 ou 1 500 – pour configurer une autre table daide.

1. Sélectionnez une cellule vide sous les données du graphique (comme E4) et tapez « 1 », la valeur qui définit le premier intervalle.
2. Accédez à longlet Accueil.
3. Dans le groupe Modification, choisissez « Remplir ».
4. Sous « Série dans, « Sélectionnez » Colonne « .
5. Pour » Valeur de létape « , saisissez » 1 « . Cette valeur détermine les incréments qui seront automatiquement ajoutés jusquà ce quExcel atteigne le dernier intervalle.
6. Pour « Valeur darrêt », tapez « 150 », la valeur correspondant au dernier intervalle, puis cliquez sur « OK. ”

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 dorigine comme indiqué sur les captures décran. Sinon, la technique ne fonctionnera pas.

Étape 4: Calculez les valeurs de distribution normale pour chaque valeur de laxe des x.

À présent, recherchez les valeurs de la distribution normale – la probabilité quun élève obtienne un certain score dexamen représenté par une valeur particulière de laxe 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 à droite (F4) de votre premier intervalle (E4):

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

Vous verrouillez les valeurs moyenne 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 recopie pour copier la formule dans le reste des cellules (F5: F153).

Étape n ° 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 une valeur dans le tableau daide contenant les valeurs des axes x et y (E4: F153).
2. Accédez à longlet Insertion.
3. Cliquez sur le bouton « Insérer un diagramme à bulles (X, Y) ou à bulles ».
4. Choisissez « Scatter avec des lignes lisses ».

Étape # 6: Configurez la table des libellés.

Techniquement, vous ayez votre courbe en cloche. Mais ce serait difficile à lire car il ny a aucune donnée la décrivant.

Rendons la distribution normale plus informative en ajoutant les étiquettes illustrant toutes les valeurs décart-type en 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 daide comme suit:

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

Ensuite, calculez les valeurs décart type sous 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 recopie 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 laxe y (J2: J8) avec des zéros comme vous le souhaitez marqueurs de données placés sur laxe horizontal.

Étape 7: Insérez les données détiquette dans le graphique.

Maintenant, ajoutez toutes les données que vous avez préparées. Cliquez avec le bouton droit de la souris sur le graphique et choisissez « Sélectionner les données ».

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

Mettez en surbrillance les plages de cellules respectives de la table dassistance — I2: I8 pour les » valeurs de la série X « et J2 : J8 pour « Valeurs de la série Y » – et cliquez sur « OK ».

Étape 8: Changer le type de graphique de la série détiquettes.

Létape suivante 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 de la souris sur le graphique et sélectionnez « Modifier Type de graphique. ”

Ensuite, créez un graphique combiné:

1. Accédez à la Onglet Combo.
2. Pour la série «Series2», remplacez «Chart Type» par «Scatter».
   • Remarque: assurez-vous que «Series1» reste «Scatter with Smooth Lines». Parfois, Excel le modifie lorsque vous créez un combo Assurez-vous également que « Series1 » nest pas poussé vers laxe secondaire – la case à cocher en regard du type de graphique ne doit pas être cochée.
3. Cliquez sur « OK ».

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

Centrez le graphique sur la courbe en cloche en ajustant léchelle de laxe horizontal. Cliquez avec le bouton droit sur laxe horizontal et sélectionnez « Format Axis » dans le menu.

Une fois que le volet Office apparaît, procédez comme suit:

• Accédez à longlet Options de laxe.
• Définissez la valeur Limites minimales sur « 15. »
• Définissez la valeur Limites maximales sur « 125 ».

Vous pouvez modifier la plage déchelle de laxe 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 n ° 10: insérez et positionnez les étiquettes de données personnalisées.

Lorsque vous peaufinez votre graphique, assurez-vous pour ajouter les étiquettes de données personnalisées. Tout dabord, faites un clic droit sur nimporte quel point représentant la série « Series2 » et sélectionnez « Ajouter des étiquettes de données ».

Ensuite, remplacez les étiquettes par défaut avec 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 de la souris sur nimporte quelle étiquette de données Série « Series2 ».
2. Sélectionnez « Format Étiquettes de données. »
3. Dans le volet Office, passez à longlet Options détiquette.
4. Cochez la case« Valeur X ».
5. Décochez la case« Valeur Y « .
6. Sous » Position du libellé « , choisissez » Au-dessus « .

En outre, vous pouvez maintenant supprimer les quadrillages (cliquez dessus avec le bouton droit de la souris > Supprimer).

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

Enfin, recolorez les points pour les aider à sintégrer dans votre style de graphique.

1. Cliquez avec le bouton droit de la souris sur une é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.

Aussi, r supprimez les bordures autour des points:

1. Cliquez à nouveau avec le bouton droit de la souris sur le même marqueur de données et sélectionnez «Contour».
2. Choisissez «Aucun contour».

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

En guise dajustement final, vous pouvez ajoutez 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).
• Aller dans longlet Insertion.
• Cliquez sur le bouton « Formes ».
• Choisissez « Ligne ».

Maintenez la touche « SHIFT » enfoncée tout en en faisant glisser la souris pour dessiner des lignes parfaitement verticales de chaque point à lendroit où chaque ligne rencontre la courbe en cloche.

Changer le titre du graphique , et votre courbe en cloche améliorée est prête – montrant vos précieuses données de distribution.

Et cest ainsi que vous procédez. Vous pouvez maintenant choisir nimporte quel ensemble de données et créer une courbe en cloche de distribution normale en suivant ces étapes simples!