Jimmy in Huntsville möchte eine Glockenkurve zeichnen, die die Durchschnittswerte mehrerer Personen zeigt. Als Jimmy die Frage während meines Power Excel-Seminars stellte, dachte ich an eines meiner beliebtesten Videos auf YouTube zurück.
In Podcast 1665 - Erstellen einer Glockenkurve in Excel erkläre ich, dass Sie zum Erstellen einer Glockenkurve den Mittelwert und die Standardabweichung berechnen müssen. Ich generiere dann 30 Punkte entlang der x-Achse, die eine hypothetische Population von Menschen umfassen. In diesem Video habe ich einen Wert generiert, der sich von -3 Standardabweichungen bis +3 Standardabweichungen um einen Mittelwert erstreckt.
Wenn Sie beispielsweise einen Mittelwert von 50 und eine Standardabweichung von 10 haben, würde ich eine x-Achse erstellen, die von 70 bis 130 verläuft. Die Höhe jedes Punkts wird mit berechnet =NORM.DIST(x,mean,standard deviation,False).
Im obigen Bild sind die Zahlen in A10: A40 im Wesentlichen "gefälschte Datenpunkte". Ich generiere 31 Zahlen, um eine schöne glatte Kurve zu erstellen. Wenn ich nur 7 Datenpunkte verwendet hätte, würde die Kurve folgendermaßen aussehen:
Für Jimmys Datensatz sind die tatsächlichen Durchschnittswerte seiner Mitarbeiter im Wesentlichen Punkte entlang einer x-Achse. Um sie auf eine Glockenkurve zu passen, müssen Sie die Höhe oder den Y-Wert für jeden Mitarbeiter ermitteln.
Folge diesen Schritten:
-
Sortieren Sie die Daten so, dass die Punktzahlen am niedrigsten bis am höchsten erscheinen.
Sortieren Sie die Daten - Berechnen Sie einen Mittelwert mit der Funktion DURCHSCHNITTLICH.
- Berechnen Sie eine Standardabweichung mit der STDEV-Funktion.
-
Berechnen Sie den Y-Wert rechts von den Scores mit
=NORM.DIST(L2,$H$2,$H$3,FALSE). Der Y-Wert erzeugt eine Höhe des Punktes jeder Person entlang der Glockenkurve. Die NORM.DIST-Funktion sorgt dafür, dass Personen in der Nähe des Mittelwerts an einer höheren Stelle als Personen in der Nähe des oberen oder unteren Bereichs dargestellt werden.
Generieren Sie eine Reihe von Y-Werten. - Wählen Sie Ihre Daten in L1: M15
-
In Excel wurde kürzlich ein seltsamer Fehler angezeigt. Um den Erfolg sicherzustellen, wählen Sie auf der Registerkarte Einfügen die Option Alle Diagramme.
Der Dialogstarter führt Sie zu allen Diagrammtypen Klicken Sie im Dialogfeld Diagramm einfügen auf die Registerkarte Alle Diagramme. Klicken Sie links auf XY (Streuung). Wählen Sie das zweite Symbol oben. Wählen Sie die Vorschau rechts.
Vier Klicks, um das Diagramm auszuwählen Ihre anfängliche Glockenkurve sieht folgendermaßen aus:
Die Glockenkurve
Gehen Sie folgendermaßen vor, um die Glockenkurve zu bereinigen:
- Klicken Sie auf den Titel und drücken Sie die Entf-Taste.
- Doppelklicken Sie auf eine beliebige Zahl entlang der Y-Achse am unteren Rand des Diagramms. Das Fenster "Achse formatieren" wird angezeigt.
-
Geben Sie neue Werte für Minimum und Maximum ein. Der Bereich hier sollte gerade groß genug sein, um alle auf der Karte anzuzeigen. Ich habe 50 bis 90 verwendet.
Ändern Sie das Minimum und Maximum - Vergrößern Sie das Diagramm, indem Sie den Rand des Diagramms ziehen.
- Klicken Sie auf das Symbol + rechts neben dem Diagramm und wählen Sie Datenbeschriftungen. Machen Sie sich keine Sorgen, dass die Etiketten noch keinen Sinn ergeben.
- Doppelklicken Sie auf eine Beschriftung, um das Bedienfeld Beschriftungen formatieren zu öffnen.
- Am oberen Rand des Bedienfelds befinden sich vier Symbole. Wählen Sie das Symbol, das ein Säulendiagramm anzeigt.
- Klicken Sie auf den Pfeil neben Beschriftungsoptionen, um diesen Teil des Bedienfelds zu erweitern.
- Wählen Sie Wert aus Zellen. Ein Dialogfeld wird angezeigt, in dem Sie nach der Position der Beschriftungen gefragt werden. Wählen Sie die Namen in K2: K15.
-
Deaktivieren Sie im Bedienfeld „Datenbeschriftung formatieren“ die Auswahl der Y-Werte. Es ist wichtig, Schritt 15 zu beenden, bevor Sie Schritt 16 ausführen. Andernfalls werden die Etiketten versehentlich entfernt.
Holen Sie sich die Beschriftungen aus den Zellen, die Namen enthalten.
Hinweis
Die Möglichkeit, Beschriftungen von Zellen abzurufen, wurde in Excel 2013 hinzugefügt. Wenn Sie Excel 2010 oder früher verwenden, laden Sie das XY Chart Labeler-Add-In von Rob Bovey herunter. (Google, um es zu finden).
Überprüfen Sie zu diesem Zeitpunkt, ob Diagrammbeschriftungen vorhanden sind, die ineinander stoßen. Befolgen Sie diese Schritte sorgfältig, um sie zu beheben.
- Klicken Sie einmal auf eine Diagrammbezeichnung. Dadurch werden alle Beschriftungen ausgewählt.
- Klicken Sie einmal auf eines der Etiketten, das sich über einem anderen Etikett befindet, um nur dieses Etikett auszuwählen.
- Bewegen Sie den Mauszeiger über verschiedene Teile des Etiketts, bis Sie einen vierköpfigen Pfeil sehen. Klicken Sie auf die Beschriftung und ziehen Sie sie an eine neue Position.
-
Sobald Sie nur ein einzelnes Etikett ausgewählt haben, können Sie auf ein anderes Etikett klicken, um dieses Etikett auszuwählen. Wiederholen Sie diesen Vorgang für alle anderen Etiketten, die verschoben werden müssen.
Das endgültige Diagramm
Schau Video
Video-Transkript
Lernen Sie Excel aus Podcast, Episode 2217: Platzieren Sie Menschen auf einer Glockenkurve.
Hey, willkommen zurück im Netcast, ich bin Bill Jelen. Die heutige Frage von Jimmy in meinem Seminar in Huntsville, Alabama. Jimmy hat Daten, er möchte diese Daten zusammenfassen und dann die Ergebnisse auf einer Glockenkurve darstellen.
In Ordung? Eines meiner beliebtesten Videos auf YouTube ist dieses: Nummer 1663, Erstellen einer Glockenkurve in Excel. Und angesichts eines Mittelwerts und einer Standardabweichung habe ich das Tief herausgefunden, das dreimal so hoch ist wie der Mittelwert, und das Hoch - dreimal so hoch wie die Standardabweichung, mehr als der Mittelwert -, wo die Lücke ist - und Verwenden Sie hier eine Reihe von X-Werten. Um die Höhe zu ermitteln, verwenden Sie diese Funktion: = NORM.DIST des X-Werts, des Mittelwerts und der Standardabweichung, Komma falsch (= NORM.DIST (A10, $ B $ 2, $) B $ 3, FALSE)).
Und wenn Sie darüber nachdenken, verwendet dieses Video hier wirklich nur eine Reihe von falschen X-Werten, um eine gut aussehende Kurve zu erhalten. Und wir werden hier das gleiche Konzept verwenden, aber anstelle von falschen X-Werten werden wir tatsächlich die Leute hier unten haben und dann wird die Höhe genau dieselbe Formel sein. In Ordung.
Also wollte Jimmy jetzt eine Pivot-Tabelle erstellen. Also werden wir PivotTable einfügen, hier auf dieses Blatt setzen und auf OK klicken. Leute auf der linken Seite und dann ihre durchschnittliche Punktzahl. Okay, also fängt es mit der Summe der Punkte an. Ich doppelklicke dort und ändere das auf einen Durchschnitt. Groß. Ganz unten möchte ich keine Gesamtsumme - klicken Sie mit der rechten Maustaste und entfernen Sie die Gesamtsumme - und wir möchten diese Personen hoch bis niedrig anordnen. Dies ist in einer Pivot-Tabelle einfach zu bewerkstelligen. Daten von A bis Z - ausgezeichnet. In Ordung. Jetzt machen wir genau das Gleiche wie im Podcast 1663 und berechnen einen Mittelwert und eine Standardabweichung. Der Mittelwert ist also ein Durchschnitt dieser Bewertungen und entspricht dann der Standardabweichung dieser Bewertungen. In Ordung. Jetzt, wo ich das weiß, kann ich meinen y-Wert erstellen.
Okay, also ein paar Dinge, die wir hier machen werden. Zunächst einmal können Sie keine Pivot-Tabelle - ein Streudiagramm - aus einer Pivot-Tabelle erstellen. Also werde ich all diese Daten kopieren und das nur mit = D2 machen. Beachten Sie, dass ich nicht mit der Maus oder den Pfeiltasten auf diese zeigen darf. Und so haben wir hier unsere Werte. Diese werden zu X-Werten, der Y-Wert wird zu = NORM.DIST, hier ist der x-Wert, Komma, für den Mittelwert dieser Zahl, ich drücke F4, um das zu sperren; Für die Standardabweichung ist es diese Zahl. Drücken Sie erneut F4, um diese zu sperren, und kumulieren Sie FALSE. (= NORM.DIST (K2, $ H $ 2, $ H $ 3, FALSE)) Und wir doppelklicken, um das zu kopieren. In Ordung. Und dann wählen Sie nicht die Etiketten,Wählen Sie einfach das XY und wir fügen ein Streudiagramm mit Linien ein. Sie können entweder das mit gekrümmten Linien oder ein wenig geraden Linien auswählen. Hier gehe ich mit solchen gekrümmten Linien. Und jetzt haben wir alle unsere Leute auf eine Glockenkurve gesetzt.
In Ordung. Nun, einige Dinge - einige Dinge vom Typ Formatierung - werden wir hier tun: Doppelklicken Sie zunächst hier unten auf der Skala, und es sieht so aus, als ob unsere niedrigste Zahl wahrscheinlich irgendwo bei 50 liegt - also werde ich Setze eine Min. von 50 - und unsere größte Zahl - unsere größte Zahl - ist 88 - also setze ich eine Maximalzahl von 90. In Ordnung. Und jetzt müssen wir diese Punkte kennzeichnen. Wenn Sie sich in Excel 2013 oder höher befinden, ist dies einfach. Wenn Sie sich jedoch in einer älteren Version von Excel befinden, müssen Sie Rob Boveys Chart Labeler-Add-In erneut verwenden, damit diese Punktbeschriftungen von einer Stelle stammen, die nicht im Diagramm enthalten ist. Okay, also fangen wir hier an. Wir werden Datenetiketten hinzufügen, und es werden Zahlen hinzugefügt, und sie sehen schrecklich aus. Ich werde hierher kommen und sagen, dass ich mehr Optionen, Beschriftungsoptionen, möchte.und ich möchte den Wert von Zellen erhalten - Wert von Zellen. In Ordung? Wenn der Zellenbereich genau dort ist, klicken Sie auf OK. Es ist sehr wichtig, Wert aus Zellen zu verwenden, bevor ich den Y-Wert deaktiviere. Es fängt an gut auszusehen. Ich werde das loswerden. Der ganze Schlüssel hier - weil Sie einige Leute haben, die sich gegenseitig überschreiben - ist, zu versuchen, das Diagramm so groß wie möglich zu machen. Wir brauchen dort oben keine Überschrift. Warum? Löschen Sie das einfach. Und ich sehe immer noch, dass Kelly und Lou und Andy und Flo fast am selben Ort sind; Jared und … Okay. Das wird jetzt frustrierend - diese, die sich überschneiden. Wenn wir jedoch auf ein Etikett klicken, haben wir alle Etiketten ausgewählt, dann erneut auf ein Etikett geklickt und nur ein einziges Etikett ausgewählt. In Ordung? Also jetzt. sehr vorsichtig. Versuchen Sie, auf Andy zu klicken, und ziehen Sie Andy einfach nach links.Sieht so aus, als wären Jared und Ike zusammen. Jetzt, wo ich mich im Einzeletiketten-Auswahlmodus befinde, ist es einfacher. Und dann ziehen Kelly und Lou sie so hoch. Vielleicht gibt es einen besseren Ort, an dem Lou nicht überrannt wird, oder ich kann ihn sogar auf beide Seiten ziehen. Okay, was haben wir? Wir haben mit einer Reihe von Daten begonnen, eine Pivot-Tabelle erstellt, den Mittelwert und die Standardabweichung ermittelt, mit denen wir nur die Höhe ermitteln können - die Y-Position für jede dieser Bewertungen und hoffentlich die Höhe dieser Werte. Wir werden die Leute in eine schöne parabelförmige Glockenkurve bringen.Okay, was haben wir? Wir haben mit einer Reihe von Daten begonnen, eine Pivot-Tabelle erstellt, den Mittelwert und die Standardabweichung ermittelt, mit denen wir nur die Höhe ermitteln können - die Y-Position für jede dieser Bewertungen und hoffentlich die Höhe dieser Werte. Wir werden die Leute in eine schöne parabelförmige Glockenkurve bringen.Okay, was haben wir? Wir haben mit einer Reihe von Daten begonnen, eine Pivot-Tabelle erstellt, den Mittelwert und die Standardabweichung ermittelt, mit denen wir nur die Höhe ermitteln können - die Y-Position für jede dieser Bewertungen und hoffentlich die Höhe dieser Werte. Wir werden die Leute in eine schöne parabelförmige Glockenkurve bringen.
Ich liebe diese Frage von Jimmy, diese Frage ist nicht in diesem Buch, aber es wird das nächste Mal sein, wenn ich dieses Buch schreibe. Ich muss das hinzufügen - es ist eine coole Anfrage und ein cooler kleiner Trick. Glockenkurven sind in Excel sehr beliebt.
Aber schauen Sie sich mein Buch LIVe an, die 54 besten Excel-Tipps aller Zeiten.
Okay, Abschluss dieser Episode: Jimmy aus Huntsville möchte Leute auf einer Glockenkurve arrangieren. Also verwenden wir eine Pivot-Tabelle, um die durchschnittliche Punktzahl zu ermitteln, sortieren die Pivot-Tabellen nach den Punktzahlen - hoch bis niedrig angeordnet - und entfernen die Gesamtsumme unten - dies sind im Wesentlichen die X-Werte - Berechnen Sie dann den Durchschnitt und die Standardabweichung dieser Bewertungen und verwenden Sie Formeln, um die Daten aus der Pivot-Tabelle in einen neuen Bereich zu kopieren, da Sie kein XY-Diagramm haben können, das sich mit einer Pivot-Tabelle schneidet. Berechnen Sie einen y-Wert für jede Person mit = NORM.DIST aus ihrem x-Wert, dem Mittelwert, der Standardabweichung, dem Komma FALSE; Erstellen Sie ein XY-Streudiagramm mit glatten Linien. Wenn Sie Excel 2010 oder früher sind, verwenden Sie das Chart Labeler-Add-In von Ron Bovey. Ich werde Sie das googeln lassen, weil,Falls Rob seine URL ändert, möchte ich hier nicht die falsche URL. In Excel 2013 mussten Datenbeschriftungen aus Zellen die Namen und dann einige Anpassungen angeben. Ändern Sie die Skalierung unten, ich ändere sie in und Max und verschiebe dann die Beschriftungen, die sich gegenseitig überlagern.
Verwenden Sie die URL in der YouTube-Beschreibung, um die Arbeitsmappe aus dem heutigen Video herunterzuladen. Ich möchte Jimmy für diese großartige Frage in Huntsville danken, und ich möchte Ihnen dafür danken, dass Sie vorbeischauen. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Laden Sie die Excel-Datei herunter
So laden Sie die Excel-Datei herunter: place-people-on-bell-kurve.xlsx
Vielen Dank an Jimmy in Huntsville für die heutige Frage!
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Wenn Sie Excel im letzten Monat in den manuellen Neuberechnungsmodus versetzt haben, ist es Zeit für Power Pivot (Sie werden den manuellen Modus nie wieder benötigen)."
Rob Collie
