Jimmy à Huntsville veut tracer une courbe en cloche montrant les scores moyens de plusieurs personnes. Lorsque Jimmy a posé la question lors de mon séminaire Power Excel, j'ai repensé à l'une de mes vidéos les plus populaires sur YouTube.
Dans Podcast 1665 - Créer une courbe en cloche dans Excel, j'explique que pour créer une courbe en cloche, vous devez calculer la moyenne et l'écart type. Je génère ensuite 30 points le long de l'axe des x qui couvrent une population hypothétique de personnes. Dans cette vidéo, j'ai généré des écarts allant de -3 écarts types à + 3 écarts types autour d'une moyenne.
Par exemple, si vous avez une moyenne de 50 et un écart type de 10, je créerais un axe des x qui allait de 70 à 130. La hauteur de chaque point est calculée à l'aide de =NORM.DIST(x,mean,standard deviation,False).
Dans l'image ci-dessus, les nombres en A10: A40 sont essentiellement des "faux points de données". Je génère 31 nombres pour créer une belle courbe lisse. Si je n'avais utilisé que 7 points de données, la courbe ressemblerait à ceci:
Pour l'ensemble de données de Jimmy, les scores moyens réels de ses employés sont essentiellement des points le long d'un axe x. Pour les ajuster sur une courbe en cloche, vous devez déterminer la hauteur ou la valeur Y de chaque employé.
Suivez ces étapes:
-
Triez les données pour que les scores apparaissent du plus bas au plus élevé.
Trier les données - Calculez une moyenne à l'aide de la fonction MOYENNE.
- Calculez un écart type à l'aide de la fonction STDEV.
-
Calculez la valeur Y à droite des scores en utilisant
=NORM.DIST(L2,$H$2,$H$3,FALSE). La valeur Y générera une hauteur du point de chaque personne le long de la courbe en cloche. La fonction NORM.DIST se chargera de tracer les personnes proches de la moyenne à un emplacement plus élevé que les personnes proches du haut ou du bas.
Générez une série de valeurs Y. - Sélectionnez vos données en L1: M15
-
Un bug étrange a récemment commencé à apparaître dans Excel, donc pour assurer le succès, choisissez Tous les graphiques dans l'onglet Insertion.
Le lanceur de dialogue vous amène à tous les types de graphiques Dans la boîte de dialogue Insérer un graphique, cliquez sur l'onglet Tous les graphiques. Cliquez sur XY (Scatter) sur la gauche. Choisissez la deuxième icône en haut. Choisissez l'aperçu à droite.
Quatre clics pour choisir le graphique Votre courbe en cloche initiale ressemblera à ceci:
La courbe en cloche
Pour nettoyer la courbe en cloche, procédez comme suit:
- Cliquez sur le titre et appuyez sur la touche Suppr.
- Double-cliquez sur n'importe quel nombre le long de l'axe Y en bas du graphique. Le panneau Format Axis apparaît.
-
Saisissez de nouvelles valeurs pour le minimum et le maximum. La plage ici doit être juste assez large pour montrer tout le monde sur le graphique. J'en ai utilisé 50 à 90.
Changer le minimum et le maximum - Agrandissez le graphique en faisant glisser le bord du graphique.
- Cliquez sur l'icône + à droite du graphique et sélectionnez Étiquettes de données. Ne vous inquiétez pas, les étiquettes n'ont pas encore de sens.
- Double-cliquez sur une étiquette pour ouvrir le panneau Format des étiquettes.
- Il y a quatre icônes en haut du panneau. Choisissez l'icône qui affiche un histogramme.
- Cliquez sur la flèche en regard des Options d'étiquette pour développer cette partie du panneau.
- Choisissez Valeur dans les cellules. Une boîte de dialogue apparaîtra demandant l'emplacement des étiquettes. Choisissez les noms dans K2: K15.
-
Toujours dans le panneau Format de l'étiquette de données, désélectionnez les valeurs Y. Il est important de terminer l'étape 15 avant de passer à l'étape 16 ou vous retirerez les étiquettes par inadvertance.
Obtenez les étiquettes des cellules contenant les noms.
Remarque
La possibilité d'obtenir des étiquettes à partir de cellules a été ajoutée dans Excel 2013. Si vous utilisez Excel 2010 ou une version antérieure, téléchargez le complément XY Chart Labeler de Rob Bovey. (Google pour le trouver).
À ce stade, voyez si vous avez des étiquettes de graphique qui se bloquent les unes sur les autres. Pour les réparer, suivez attentivement ces étapes.
- Cliquez une fois sur une étiquette de graphique. Cela sélectionne toutes les étiquettes.
- Cliquez une fois sur l'une des étiquettes qui se trouve au-dessus d'une autre étiquette pour sélectionner uniquement cette étiquette.
- Survolez différentes parties de l'étiquette jusqu'à ce que vous voyiez une flèche à quatre pointes. Cliquez et faites glisser l'étiquette vers une nouvelle position.
-
Une fois que vous n'avez sélectionné qu'une seule étiquette, vous pouvez cliquer une fois sur n'importe quelle autre étiquette pour sélectionner cette étiquette. Répétez pour toutes les autres étiquettes qui doivent être déplacées.
Le graphique final
Regarder la vidéo
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2217: Placez les gens sur une courbe en cloche.
Salut, bon retour sur le netcast, je suis Bill Jelen. La question d'aujourd'hui, de Jimmy lors de mon séminaire à Huntsville, Alabama. Jimmy a des données, il veut résumer ces données et ensuite tracer les résultats sur une courbe en cloche.
Bien? Maintenant, l'une de mes vidéos les plus populaires sur YouTube est celle-ci: le numéro 1663, Créer une courbe en cloche dans Excel. Et étant donné une moyenne et un écart-type, j'ai déterminé le bas, qui est 3 fois l'écart-type de moins que la moyenne, et le haut - 3 fois l'écart-type de plus que la moyenne - où l'écart est - et une série de valeurs X ici, et pour déterminer la hauteur, utilisez cette fonction: = NORM.DIST de la valeur X, la moyenne et l'écart type, virgule false (= NORM.DIST (A10, $ B $ 2, $ B $ 3, FAUX)).
Et si vous y réfléchissez bien, cette vidéo utilise simplement une série de fausses valeurs X ici afin d'obtenir une belle courbe. Et nous allons utiliser le même concept ici, mais au lieu de fausses valeurs X, nous allons en fait avoir les gens ici et la hauteur sera exactement la même formule. Bien.
Alors, maintenant, Jimmy voulait créer un tableau croisé dynamique. Nous allons donc insérer, tableau croisé dynamique, le mettre ici sur cette feuille, cliquez sur OK. Les gens du côté gauche, puis leur score moyen. D'accord, donc ça commence par Sum of Score, je double-clique là-bas et change ça en moyenne. Génial. Maintenant, tout en bas, je ne veux pas d'un grand total - cliquez avec le bouton droit et Supprimer le grand total - et nous voulons organiser ces personnes de haut en bas, ce qui est facile à faire dans un tableau croisé dynamique. Données, de A à Z - excellent. Bien. Maintenant, nous allons faire exactement la même chose que nous avons fait dans le Podcast 1663, et c'est calculer une moyenne et un écart type. La moyenne est donc une moyenne de ces scores, puis équivaut à l'écart type de ces scores. Bien. Maintenant que je le sais, je suis capable de créer ma valeur y.
Très bien, alors nous allons faire quelques choses ici. Tout d'abord, vous ne pouvez pas créer un tableau croisé dynamique - un graphique en nuage de points - à partir d'un tableau croisé dynamique. Je vais donc copier toutes ces données et je vais simplement le faire avec = D2. Remarquez que je fais attention de ne pas utiliser la souris ou les touches fléchées pour les pointer. Et donc nous avons nos valeurs ici. Celles-ci deviendront des valeurs X, la valeur Y deviendra = NORM.DIST, voici la valeur x, virgule, pour la moyenne, ce nombre, je vais appuyer sur F4 pour verrouiller cela; pour l'écart type, c'est ce nombre, encore une fois, appuyez sur F4 pour verrouiller cela, et cumulatif FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Et nous double-cliquerons pour copier cela. Bien. Et puis, ne choisissez pas les étiquettes,choisissez simplement le XY et nous insérerons un nuage de points avec des lignes - vous pouvez choisir celui avec des lignes courbes ou un peu de lignes droites. Ici, je vais utiliser des lignes courbes comme celle-ci. Et nous avons maintenant tous nos gens placés sur une courbe en cloche.
Bien. Maintenant, certaines choses - certaines choses de type formatage - nous allons faire ici: Tout d'abord, double-cliquez ici le long de l'échelle, et il semble que notre nombre le plus bas se situe probablement autour de 50 - donc je vais définir un minimum de 50 - et notre plus grand nombre - notre plus grand nombre - est 88 - donc je vais définir un maximum de 90. Très bien. Et maintenant, nous devons étiqueter ces points. Si vous êtes dans Excel 2013 ou plus récent, c'est facile à faire; mais si vous utilisez une ancienne version d'Excel, vous devrez revenir en arrière et utiliser le complément Chart Labeler de Rob Bovey pour que ces étiquettes de points proviennent d'un endroit qui ne figure pas dans le graphique. Très bien, alors nous commençons ici. Nous allons ajouter des étiquettes de données, et cela ajoute des chiffres et ils ont l'air horrible. Je vais venir ici et dire que je veux plus d'options, d'options d'étiquette,et je veux obtenir la valeur des cellules - Valeur des cellules. Bien? Donc, la plage de cellules est là, cliquez sur OK. Il est très important d'utiliser la valeur des cellules avant de décocher la valeur Y. Ça commence à bien paraître. Je vais m'en débarrasser. Maintenant, toute la clé ici - parce que vous avez des personnes qui se remplacent en quelque sorte - est d'essayer de rendre le graphique aussi grand que possible. Nous n'avons pas besoin d'un cap là-haut. Pourquoi? Supprimez simplement cela. Et je vois toujours, comme, Kelly et Lou et Andy et Flo sont presque au même endroit; Jared et … D'accord. Alors maintenant, cela va être frustrant - ceux qui se chevauchent. Mais lorsque nous cliquons sur une étiquette, nous sélectionnons toutes les étiquettes, puis cliquons à nouveau sur une étiquette, et nous ne sélectionnons qu'une seule étiquette. Bien? Alors maintenant. très soigneusement. essayez de cliquer sur Andy, et faites simplement glisser Andy vers la gauche.On dirait que Jared et Ike sont ensemble, alors maintenant que je suis en mode de sélection d'étiquette unique, c'est plus facile. Et puis Kelly et Lou, traînez-les comme ça. Peut-être qu'il y a un meilleur endroit qui ne dépasse pas Lou, ou même, ici, je peux le faire glisser de chaque côté. Bon, alors, qu'est-ce que nous avons? Nous avons commencé avec un tas de données, créé un tableau croisé dynamique, déterminé la moyenne et l'écart type, ce qui nous permet simplement de déterminer la hauteur - la position Y pour chacun de ces scores, et la hauteur de ceux-ci, espérons-le, nous allons amener les gens dans une belle courbe en cloche en forme de parabole, comme ça.Bon, alors, qu'est-ce que nous avons? Nous avons commencé avec un tas de données, créé un tableau croisé dynamique, déterminé la moyenne et l'écart type, ce qui nous permet simplement de déterminer la hauteur - la position Y pour chacun de ces scores, et la hauteur de ceux-ci, espérons-le, nous allons amener les gens dans une belle courbe en cloche en forme de parabole, comme ça.Bon, alors, qu'est-ce que nous avons? Nous avons commencé avec un tas de données, créé un tableau croisé dynamique, déterminé la moyenne et l'écart type, ce qui nous permet simplement de déterminer la hauteur - la position Y pour chacun de ces scores, et la hauteur de ceux-ci, espérons-le, nous allons amener les gens dans une belle courbe en cloche en forme de parabole, comme ça.
J'adore cette question de Jimmy, cette question n'est pas dans ce livre, mais ce sera la prochaine fois que j'écrirai ce livre. Je vais devoir ajouter ceci - c'est une demande cool et un petit truc sympa. Les courbes en cloche sont très populaires dans Excel.
Mais regardez mon livre, LIVe, les 54 meilleurs conseils Excel de tous les temps.
Très bien, récapitulation de cet épisode: Jimmy de Huntsville, veut organiser les gens sur une courbe en cloche. Nous utilisons donc un tableau croisé dynamique pour calculer le score moyen, trier les tableaux croisés dynamiques en fonction des scores - disposés de haut en bas - nous débarrasser du grand total en bas - ce seront essentiellement les valeurs X - puis sur le côté, calculez la moyenne et l'écart type de ces scores et utilisez des formules pour copier les données du tableau croisé dynamique vers une nouvelle plage, car vous ne pouvez pas avoir un graphique XY qui croise un tableau croisé dynamique. Calculez une valeur y pour chaque personne avec = NORM.DIST de leur valeur x, la moyenne, l'écart type, virgule FALSE; créer un diagramme de dispersion XY avec des lignes lisses - si vous êtes un Excel 2010 ou une version antérieure, vous allez utiliser le complément Chart Labeler de Ron Bovey. Je vais vous faire chercher sur Google parce que,au cas où Rob changerait son URL, je ne veux pas de la mauvaise URL ici. Dans Excel 2013, avez des étiquettes de données, à partir de cellules, spécifiez les noms, puis quelques ajustements - changez l'échelle en bas, je les change dans et Max, puis déplacez les étiquettes qui se sur-définissent.
Pour télécharger le classeur à partir de la vidéo d'aujourd'hui, utilisez l'URL dans la description YouTube. Je tiens à remercier Jimmy pour cette excellente question à Huntsville, et je tiens à vous remercier de votre visite. Je vous verrai la prochaine fois pour une autre diffusion sur Internet.
Télécharger le fichier Excel
Pour télécharger le fichier excel: place-people-on-bell-curve.xlsx
Merci à Jimmy à Huntsville pour la question d'aujourd'hui!
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Si vous avez mis Excel en mode de recalcul manuel au cours du mois dernier, il est temps pour le pivot de puissance (vous n'aurez plus jamais besoin du mode manuel)"
Rob Collie
