So erstellen Sie eine Normalverteilungsglockenkurve in Excel

In diesem Lernprogramm wird gezeigt, wie Sie eine Normalverteilungsglockenkurve in allen Versionen von Excel erstellen: 2007, 2010, 2013 , 2016 und 2019.

In der Statistik ist eine Glockenkurve (auch als Standardnormalverteilung oder Gaußsche Kurve bezeichnet) ein symmetrisches Diagramm, das die Tendenz von Daten zur Clusterbildung veranschaulicht um einen Mittelpunkt oder Mittelwert in einem bestimmten Datensatz.

Die y-Achse repräsentiert die relative Wahrscheinlichkeit, dass ein bestimmter Wert im Datensatz auftritt, während die x-Achse die Werte selbst auf dem Diagramm darstellt, um eine Glockenform zu erstellen Kurve, daher der Name.

Das Diagramm hilft uns zu analysieren, ob ein bestimmter Wert Teil der erwarteten Variation ist oder statistisch signifikant ist und daher genauer untersucht werden muss.

Da Excel keine integrierten Lösungen anbietet, müssen Sie diese selbst zeichnen. Aus diesem Grund haben wir das Chart Creator Add-In entwickelt, ein Tool, mit dem Sie erweiterte Excel-Diagramme mit nur wenigen Klicks erstellen können.

In diesem Schritt- In einem schrittweisen Tutorial erfahren Sie, wie Sie in Excel von Grund auf eine Normalverteilungsglockenkurve erstellen:

To Wenn Sie eine Gaußsche Kurve zeichnen, müssen Sie zwei Dinge wissen:

• Der Mittelwert (auch als Standardmessung bekannt). Dies bestimmt den Mittelpunkt der Kurve, der wiederum die Position der Kurve kennzeichnet.
• Die Standardabweichung (SD) der Messungen. Dies definiert die Verteilung Ihrer Daten in der Normalverteilung – oder im Klartext, wie breit die Kurve sein soll. In der oben gezeigten Glockenkurve repräsentiert beispielsweise eine Standardabweichung des Mittelwerts den Bereich zwischen Prüfungsergebnissen von 53 und 85.

Je niedriger die SD, desto höher die Kurve und desto geringer Ihre Daten werden verteilt und umgekehrt.

Erwähnenswert ist die 68-95-99.7-Regel, die auf jede Normalverteilungskurve angewendet werden kann, dh, ungefähr 68% Ihrer Daten werden platziert innerhalb einer SD vom Mittelwert entfernt, 95% innerhalb von zwei SD und 99,7% innerhalb von drei SD.

Nun, da Sie Wenn Sie das Wesentliche kennen, gehen wir von der Theorie zur Praxis über.

Erste Schritte

Nehmen wir zur Veranschaulichung an, Sie haben die Testergebnisse von 200 Schülern und möchten sie „auf einer Kurve bewerten“ Dies bedeutet, dass die Noten der Schüler auf ihrer relativen Leistung zum Rest der Klasse basieren:

Schritt 1: Ermitteln Sie den Mittelwert.

In der Regel erhalten Sie den Mittelwert und SD-Werte von Anfang an, aber wenn dies nicht der Fall ist, können Sie diese Werte einfach in wenigen einfachen Schritten berechnen. Lassen Sie uns zuerst den Mittelwert angehen.

Da der Mittelwert den Durchschnittswert einer Stichprobe oder einer Datenpopulation angibt, können Sie Ihre Standardmessung mithilfe der Funktion DURCHSCHNITT ermitteln.

Geben Sie die folgende Formel ein in eine leere Zelle (in diesem Beispiel F1) neben Ihren tatsächlichen Daten (Spalten A und B), um den Durchschnitt der Prüfungsergebnisse im Datensatz zu berechnen:

Ein kurzer Hinweis : In den meisten Fällen müssen Sie möglicherweise die Formelausgabe aufrunden. Um dies zu tun, wickeln Sie es einfach wie folgt in die ROUND-Funktion ein:

Schritt 2: Ermitteln Sie die Standardabweichung.

Eins runter, eins zu gehen. Glücklicherweise hat Excel eine spezielle Funktion, mit der Sie die Standardabweichung für Sie ermitteln können:

Auch hier wählt die Formel alle Werte aus dem angegebenen Zellbereich (B2: B201) aus und berechnet die Standardabweichung – vergessen Sie nur nicht, aufzurunden auch die Ausgabe.

Schritt 3: Richten Sie die x-Achsenwerte für die Kurve ein.

Grundsätzlich besteht das Diagramm aus einer großen Anzahl von Intervallen ( Stellen Sie sich diese als Schritte vor, die mit einer Linie verbunden sind, um eine glatte Kurve zu erstellen.

In unserem Fall werden die x-Achsenwerte verwendet, um eine bestimmte Prüfungsbewertung zu veranschaulichen, während die y-Achsenwerte dies anzeigen Geben Sie die Wahrscheinlichkeit an, dass ein Schüler diese Punktzahl bei der Prüfung erreicht.

Technisch gesehen können Sie so viele Intervalle einschließen, wie Sie möchten. Sie können die redundanten Daten später mühelos löschen, indem Sie die horizontale Achsenskala ändern. Stellen Sie einfach sicher, dass Sie einen Bereich auswählen, der die drei Standardabweichungen enthält.

Beginnen wir mit einer Zählung bei eins (da ein Schüler auf keinen Fall eine negative Prüfungsnote erhalten kann) und gehen Sie bis zu 150 – es spielt keine Rolle, ob es 150 oder 1500 ist -, um eine andere Hilfstabelle einzurichten.

1. Wählen Sie eine leere Zelle unter den Diagrammdaten (z. B. E4) aus und geben Sie „1“ ein. Der Wert, der das erste Intervall definiert.
2. Navigieren Sie zur Registerkarte Startseite.
3. Wählen Sie in der Gruppe Bearbeiten die Option „Füllen“.
4. Unter „Serie in“. Wählen Sie „Spalte“.
5. Geben Sie für „Schrittwert“ „1“ ein. Dieser Wert bestimmt die Inkremente, die automatisch addiert werden, bis Excel das letzte Intervall erreicht.
6. Geben Sie für „Stoppwert“ „150“ ein, den Wert, der für das letzte Intervall steht, und klicken Sie auf „OK“. ”

Wie durch ein Wunder 149 Zellen in Spalte E (E5: E153) wurden mit Werten zwischen 2 und 150 gefüllt.

HINWEIS: Verstecken Sie die ursprünglichen Datenzellen nicht wie in den Screenshots gezeigt. Andernfalls funktioniert die Technik nicht.

Schritt 4: Berechnen Sie die Normalverteilungswerte für jeden x-Achsenwert.

Suchen Sie nun die Normalverteilungswerte – die Wahrscheinlichkeit, dass ein Schüler eine bestimmte Prüfungsbewertung erhält, die durch einen bestimmten x-Achsenwert dargestellt wird – Für jedes der Intervalle. Zum Glück hat Excel das Arbeitstier, um alle diese Berechnungen für Sie durchzuführen: die NORM.DIST-Funktion.

Geben Sie die folgende Formel in die Zelle rechts (F4) von ein Ihr erstes Intervall (E4):

Hier ist die dekodierte Version, mit deren Hilfe Sie sie entsprechend anpassen können:

Sie sperren den Mittelwert und die SD-Werte, damit Sie die Formel für die verbleibenden Intervalle mühelos ausführen können (E5: E153).

Doppelklicken Sie nun auf den Füllpunkt, um die Formel in die restlichen Zellen zu kopieren (F5: F153).

Schritt 5: Erstellen Sie ein Streudiagramm mit glatten Linien.

Endlich ist es an der Zeit, die Glockenkurve zu erstellen:

1. Wählen Sie einen beliebigen Wert in der Hilfstabelle aus, der die Werte für die x- und y-Achse enthält (E4: F153).
2. Gehen Sie zur Registerkarte Einfügen.
3. Klicken Sie auf die Schaltfläche „Streuung einfügen (X, Y) oder Blasendiagramm“.
4. Wählen Sie „Streuung mit glatten Linien“.

Schritt 6: Richten Sie die Beschriftungstabelle ein.

Technisch gesehen Haben Sie Ihre Glockenkurve. Aber es ist schwer zu lesen, da es keine Daten gibt, die sie beschreiben.

Lassen Sie uns die Normalverteilung informativer gestalten, indem wir die Beschriftungen hinzufügen, die alle Standardabweichungswerte unter und über dem Mittelwert darstellen ( Sie können sie stattdessen auch zum Anzeigen der Z-Scores verwenden.

Richten Sie dazu eine weitere Hilfstabelle wie folgt ein:

Kopieren Sie zunächst den Mittelwert (F1) neben die entsprechende Zelle in Spalte X-Wert (I5).

Berechnen Sie als Nächstes die Standardabweichungswerte unter dem Mittelwert, indem Sie diese einfache Formel in Zelle I4 eingeben:

Einfach ausgedrückt subtrahiert die Formel die Summe der vorhergehenden Standardabweichungswerte vom Mittelwert. Ziehen Sie nun den Füllgriff nach oben, um die Formel in die verbleibenden zwei Zellen (I2: I3) zu kopieren.

Wiederholen Sie denselben Vorgang für die Standardabweichungen über dem Mittelwert mit der Spiegelformel:

Führen Sie auf die gleiche Weise die Formel für die beiden anderen Zellen aus (I7) : I8).

Füllen Sie abschließend die Beschriftungswerte der y-Achse (J2: J8) mit Nullen, wie Sie möchten Datenmarkierungen auf der horizontalen Achse platziert.

Schritt 7: Fügen Sie die Etikettendaten in das Diagramm ein.

Fügen Sie nun alle von Ihnen vorbereiteten Daten hinzu. Klicken Sie mit der rechten Maustaste auf das Diagramm und wählen Sie „Daten auswählen“.

Wählen Sie im daraufhin angezeigten Dialogfeld “ Hinzufügen. ”

Markieren Sie die entsprechenden Zellbereiche aus der Hilfstabelle – I2: I8 für“ Series X-Werte „und J2 : J8 für „Serie Y-Werte“ – und klicken Sie auf „OK“.

Schritt 8: Ändern Sie den Diagrammtyp von die Beschriftungsserie.

Unser nächster Schritt besteht darin, den Diagrammtyp der neu hinzugefügten Serie so zu ändern, dass die Datenmarkierungen als Punkte angezeigt werden. Klicken Sie dazu mit der rechten Maustaste auf das Diagramm und wählen Sie „Ändern“ Diagrammtyp. ”

Entwerfen Sie als Nächstes ein Kombinationsdiagramm:

1. Navigieren Sie zu Registerkarte „Kombination“.
2. Ändern Sie für die Serie „Serie2“ „Diagrammtyp“ in „Streuung“.
   • Hinweis: Stellen Sie sicher, dass „Serie1“ als „Streuung mit glatten Linien“ erhalten bleibt. Manchmal ändert Excel dies, wenn Sie eine Kombination erstellen. Stellen Sie außerdem sicher, dass „Serie1“ nicht auf die Sekundärachse verschoben wird. Das Kontrollkästchen neben dem Diagrammtyp sollte nicht markiert sein.
3. Klicken Sie auf „OK“.

Schritt 9: Ändern der horizontalen Achsenskala.

Zentrieren Sie das Diagramm auf der Glockenkurve, indem Sie die horizontale Achsenskala anpassen. Klicken Sie mit der rechten Maustaste auf die horizontale Achse und wählen Sie „Achse formatieren“ aus dem Menü.

Führen Sie die folgenden Schritte aus, sobald der Aufgabenbereich angezeigt wird:

• Wechseln Sie zur Registerkarte Achsenoptionen.
• Setzen Sie den Wert für die Mindestgrenzen auf „15“.
• Setzen Sie den Wert für die Höchstgrenzen auf „125“.

Sie können den Achsenskalenbereich nach Belieben anpassen. Da Sie jedoch die Standardabweichungsbereiche kennen, stellen Sie die Grenzwerte ein wenig von jeder Ihrer dritten Standardabweichungen entfernt ein, um das „Ende“ der Kurve anzuzeigen .

Schritt 10: Fügen Sie die benutzerdefinierten Datenbeschriftungen ein und positionieren Sie sie.

Achten Sie beim Aufpolieren Ihres Diagramms darauf um die benutzerdefinierten Datenbeschriftungen hinzuzufügen. Klicken Sie zunächst mit der rechten Maustaste auf einen Punkt, der die Serie „Serie2“ darstellt, und wählen Sie „Datenbeschriftungen hinzufügen“.

Ersetzen Sie anschließend Die Standardbeschriftungen mit den zuvor eingerichteten Beschriftungen und platzieren Sie sie über den Datenmarkierungen.

1. Klicken Sie mit der rechten Maustaste auf eine Datenbeschriftung der Serie „Series2“.
2. Wählen Sie „Format“ Datenbeschriftungen. ”
3. Wechseln Sie im Aufgabenbereich zur Registerkarte Beschriftungsoptionen.
4. Aktivieren Sie das Kontrollkästchen“ X-Wert „.
5. Deaktivieren Sie das Kontrollkästchen“ Y-Wert “ „Feld“.
6. Wählen Sie unter „Beschriftungsposition“ die Option „Oben“.

Sie können jetzt auch die Gitterlinien entfernen (klicken Sie mit der rechten Maustaste darauf > Löschen).

Schritt 11: Färben Sie die Datenmarkierungen neu (optional).

Färben Sie die Punkte abschließend neu ein, damit sie in Ihren Diagrammstil passen.

1. Klicken Sie mit der rechten Maustaste auf ein Datenetikett der Serie „Series2“.
2. Klicken Sie auf Klicken Sie auf die Schaltfläche „Füllen“.
3. Wählen Sie Ihre Farbe aus der angezeigten Palette aus.

Auch r Entfernen Sie die Ränder um die Punkte:

1. Klicken Sie erneut mit der rechten Maustaste auf dieselbe Datenmarkierung und wählen Sie „Gliederung“.
2. Wählen Sie „Keine Gliederung“.

Schritt 12: Hinzufügen vertikaler Linien (optional).

Als letzte Anpassung können Sie Fügen Sie dem Diagramm vertikale Linien hinzu, um die SD-Werte hervorzuheben.

• Wählen Sie das Diagramm aus (auf diese Weise werden die Linien direkt in das Diagramm eingefügt).
• Los Klicken Sie auf die Schaltfläche „Formen“.
• Wählen Sie „Linie“.

Halten Sie die UMSCHALTTASTE gedrückt Ziehen Sie die Maus, um perfekt vertikale Linien von jedem Punkt bis zu der Stelle zu zeichnen, an der jede Linie auf die Glockenkurve trifft.

Ändern Sie den Diagrammtitel und Ihre verbesserte Glockenkurve ist fertig – zeigt Ihre wertvollen Verteilungsdaten.

Und so machen Sie es. Sie können jetzt einen beliebigen Datensatz auswählen und eine Normalverteilungsglockenkurve erstellen, indem Sie diese einfachen Schritte ausführen!