So erstellen Sie eine Normalverteilungs-Glockenkurve in Excel

In diesem Tutorial wird gezeigt, wie Sie in allen Excel-Versionen eine Normalverteilungs-Glockenkurve erstellen: 2007, 2010, 2013, 2016 und 2022.

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

Die y-Achse stellt die relative Wahrscheinlichkeit dar, dass ein bestimmter Wert im Datensatz auftritt, während die x-Achse die Werte selbst im Diagramm darstellt, um eine glockenförmige Kurve zu erstellen, daher der Name.

Die Grafik 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 bietet, müssen Sie es selbst zeichnen. Aus diesem Grund haben wir das Chart Creator Add-In entwickelt, ein Tool, mit dem Sie mit wenigen Klicks erweiterte Excel-Diagramme erstellen können.

In diesem Schritt-für-Schritt-Tutorial erfahren Sie, wie Sie eine Normalverteilungs-Glockenkurve in Excel von Grund auf erstellen:

Um eine Gaußsche Kurve zu zeichnen, müssen Sie zwei Dinge wissen:

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

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

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

Nachdem Sie nun das Wesentliche kennen, gehen wir von der Theorie zur Praxis über.

Einstieg

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

Schritt 1: Finden Sie den Mittelwert.

Normalerweise erhalten Sie von Anfang an die Mittel- und SD-Werte, aber wenn dies nicht der Fall ist, können Sie diese Werte in wenigen einfachen Schritten leicht berechnen. Gehen wir zuerst den Mittelwert an.

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

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

1	=MITTEL(B2:B201)

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

1	=RUNDE(DURCHSCHNITT(B2:B201),0)

Schritt 2: Finden Sie die Standardabweichung.

Einer runter, einer zu gehen. Glücklicherweise hat Excel eine spezielle Funktion, um die ganze Drecksarbeit zum Ermitteln der Standardabweichung für Sie zu erledigen:

1	=STABW.P(B2:B201)

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

1	=RUNDE(STABW.P(B2:B201),0)

Schritt #3: Richten Sie die x-Achsen-Werte für die Kurve ein.

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

In unserem Fall werden die x-Achsen-Werte verwendet, um eine bestimmte Prüfungsnote zu veranschaulichen, während die y-Achsen-Werte uns die Wahrscheinlichkeit angeben, mit der ein Schüler diese Prüfungsnote 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 Achsenskalierung ändern. Stellen Sie nur sicher, dass Sie einen Bereich auswählen, der die drei Standardabweichungen enthält.

Beginnen wir mit der Zählung bei eins (da ein Schüler auf keinen Fall eine negative Prüfungsnote erhalten kann) und gehen wir bis zu 150 hoch – es spielt keine Rolle, ob es 150 oder 1500 sind – um einen weiteren Helfertisch einzurichten.

1. Wählen Sie eine leere Zelle unter den Diagrammdaten (z. B. E4) und Typ “1,” der Wert, der das erste Intervall definiert.
2. Navigieren Sie zum Heim Tab.
3. In dem Bearbeitung Gruppe, wählen Sie „Füllen.”
4. Unter "Serie im," auswählen "Spalte.”
5. Zum "Schrittwert," Typ “1.” Dieser Wert bestimmt die Inkremente, die automatisch addiert werden, bis Excel das letzte Intervall erreicht.
6. Zum "Stoppwert," Typ "150,” 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 den Werten von 2 bis 150 gefüllt.

HINWEIS: Blenden Sie die ursprünglichen Datenzellen nicht aus, wie auf den Screenshots gezeigt. Andernfalls funktioniert die Technik nicht.

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

Ermitteln Sie nun die Normalverteilungswerte – die Wahrscheinlichkeit, dass ein Schüler eine bestimmte Prüfungspunktzahl erhält, die durch einen bestimmten x-Achsen-Wert repräsentiert wird – für jedes der Intervalle. Zum Glück für Sie hat Excel das Arbeitspferd, um all diese Berechnungen für Sie zu erledigen: die Funktion NORM.VERT.

Geben Sie die folgende Formel in die Zelle rechts ein (F4) Ihres ersten Intervalls (E4):

1	=NORM.VERT(E4,$F$1,$F$2,FALSE)

Hier ist die decodierte Version, die Ihnen bei der entsprechenden Anpassung hilft:

1	=NORM.VERT([das erste Intervall],[der Mittelwert(absolute Referenz)],[die Standardabweichung(absolute Referenz),FALSE)

Sie sperren die Mittel- und 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 bauen:

1. Wählen Sie einen beliebigen Wert in der Hilfstabelle aus, der die Werte der x- und y-Achse enthält (E4:F153).
2. Gehe zum Einfügung Tab.
3. Drücke den "Streudiagramm (X, Y) oder Blasendiagramm einfügen" Taste.
4. Wählen "Streuen Sie mit glatten Linien.“

Schritt #6: Richten Sie die Etikettentabelle ein.

Technisch gesehen haben Sie Ihre Glockenkurve. Aber es wäre schwer zu lesen, da es keine Daten zur Beschreibung gibt.

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-Werte verwenden).

Richten Sie dazu noch eine weitere Hilfstabelle wie folgt ein:

Kopieren Sie zuerst den Mittelwert (F1) neben der entsprechenden Zelle in Spalte X-Wert (I5).

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

1

=I5-$F$2

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

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

1

=I5+$F$2

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

Füllen Sie schließlich die Beschriftungswerte der y-Achse (J2: J8) mit Nullen, da die Datenmarkierungen auf der horizontalen Achse platziert werden sollen.

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 Diagrammdiagramm und wählen Sie „Wählen Sie Daten.”

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

Markieren Sie die jeweiligen Zellbereiche aus der Hilfstabelle-I2: I8 zum "Werte der Serie X" und J2: J8 zum "Serie Y-Werte“-und klicken Sie auf „OK.”

Schritt #8: Ändern Sie den Diagrammtyp der Etikettenserie.

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

Entwerfen Sie als Nächstes ein Kombinationsdiagramm:

1. Navigieren Sie zum Combo Tab.
2. Zum Serie „Serie2“, Veränderung "Diagramm Typ" zu "Streuen.”
   • Hinweis: Stellen Sie sicher, dass „Serie1“ bleibt als “Streuen mit glatten Linien.“ Manchmal ändert Excel es, wenn Sie a Combo Stellen Sie außerdem sicher, dass „Serie1“ wird nicht auf die Sekundärachse verschoben – das Kontrollkästchen neben dem Diagrammtyp sollte nicht markiert werden.
3. Klicken "OK.”

Schritt 9: Ändern Sie die Skalierung der horizontalen Achse.

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

Sobald der Aufgabenbereich angezeigt wird, gehen Sie wie folgt vor:

• Gehe zum Achsenoptionen Tab.
• Stellen Sie die Mindestgrenzen Wert auf „15.”
• Stellen Sie die Maximale Grenzen Wert auf „125.”

Sie können den Bereich der Achsenskalierung nach Belieben anpassen, aber da Sie die Standardabweichungsbereiche kennen, stellen Sie die Bounds-Werte ein wenig von jeder Ihrer dritten Standardabweichungen entfernt ein, um das "Ende" der Kurve anzuzeigen.

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

Achten Sie beim Verfeinern Ihres Diagramms darauf, die benutzerdefinierten Datenlabels hinzuzufügen. Klicken Sie zuerst mit der rechten Maustaste auf einen beliebigen Punkt, der darstellt Serie „Serie2“ und wählen Sie „Datenlabels hinzufügen.”

Ersetzen Sie als Nächstes die Standardbeschriftungen durch die zuvor eingerichteten und platzieren Sie sie über den Datenmarkierungen.

1. Klicken Sie mit der rechten Maustaste auf eine beliebige Serie „Serie2“ Datenetikett.
2. Auswählen "Datenetiketten formatieren.”
3. Wechseln Sie im Aufgabenbereich zum Etikettenoptionen Tab.
4. Überprüf den "X-Wert" Kasten.
5. Deaktivieren Sie das "Y-Wert" Kasten.
6. Unter "Etikettenposition," wählen "Über.”

Außerdem können Sie jetzt die Gitternetzlinien entfernen (klicken Sie mit der rechten Maustaste darauf> Löschen).

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

Zum Schluss färben Sie die Punkte neu, damit sie in Ihren Diagrammstil passen.

1. Klicken Sie mit der rechten Maustaste auf eine beliebige Serie „Serie2“ Datenetikett.
2. Drücke den "Füllen" Taste.
3. Wählen Sie Ihre Farbe aus der angezeigten Palette aus.

Entfernen Sie auch die Ränder um die Punkte:

1. Klicken Sie erneut mit der rechten Maustaste auf denselben Datenmarker und wählen Sie „Umriss.”
2. Wählen "Kein Umriss.”

Schritt #12: Fügen Sie vertikale Linien hinzu (optional).

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

• Wählen Sie das Diagrammdiagramm aus (dadurch werden die Linien direkt in das Diagramm eingefügt).
• Gehe zum Einfügung Tab.
• Drücke den "Formen" Taste.
• Wählen "Leitung.”

Halten Sie die "SCHICHT" gedrückt halten, während Sie die Maus ziehen, um perfekt vertikale Linien von jedem Punkt zu der Stelle zu zeichnen, an der jede Linie auf die Glockenkurve trifft.

Ändern Sie den Diagrammtitel, und Ihre verbesserte Glockenkurve zeigt Ihre wertvollen Verteilungsdaten an.

Und so machst du es. Sie können jetzt einen beliebigen Datensatz auswählen und mit diesen einfachen Schritten eine Normalverteilungs-Glockenkurve erstellen!