In diesem Lernprogramm wird gezeigt, wie Sie eine Glockenkurve für die Normalverteilung 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 symmetrischer Graph, der die Tendenz von Daten veranschaulicht, sich um einen Mittelwert in einem bestimmten Datensatz zu gruppieren.

Die y-Achse repräsentiert die relative Wahrscheinlichkeit, 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 zu bieten hat, müssen Sie es selbst zeichnen. Aus diesem Grund haben wir das Chart Creator Add-In entwickelt, ein Tool, mit dem Sie mit nur wenigen Klicks erweiterte Excel-Diagramme erstellen können.

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

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

• Der Mittelwert (auch als Standardmessung bezeichnet). Dies bestimmt den Mittelpunkt der Kurve, der wiederum die Position der Kurve kennzeichnet.
• 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 beispielsweise eine Standardabweichung des Mittelwerts den Bereich zwischen den 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 entfernt liegen, 95% innerhalb von zwei SD und 99,7% innerhalb von drei SD.

Nachdem Sie nun 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, 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 den Mittelwert und die SD-Werte, aber wenn dies nicht der Fall ist, können Sie diese Werte in nur wenigen einfachen Schritten einfach berechnen. Lassen Sie uns zuerst den Mittelwert angehen.

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

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

Eine kurze Anmerkung: In den meisten Fällen müssen Sie möglicherweise die Formelausgabe aufrunden. Wickeln Sie es dazu einfach wie folgt in die RUNDE Funktion:

Schritt #2: Finden Sie die Standardabweichung.

Eins runter, eins zu gehen. Glücklicherweise verfügt Excel über eine spezielle Funktion, mit der Sie die schmutzige Arbeit zum Ermitteln der Standardabweichung für Sie erledigen können:

Auch hier nimmt die Formel alle Werte aus dem angegebenen Zellbereich (B2: B201) und berechnet die Standardabweichung — vergessen Sie nicht, auch die Ausgabe aufzurunden.

Schritt #3: Set up die x-achse werte für die kurve.

Grundsätzlich besteht das Diagramm aus einer großen Anzahl von Intervallen (betrachten Sie sie als Schritte), die mit einer Linie verbunden sind, um eine glatte Kurve zu erzeugen.

In unserem Fall werden die x-Achsenwerte verwendet, um eine bestimmte Prüfungsnote zu veranschaulichen, während die y-Achsenwerte die Wahrscheinlichkeit angeben, mit der ein Schüler diese Punktzahl in der Prüfung erhält.

Technisch gesehen können Sie beliebig viele Intervalle einschließen — 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 (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 unterhalb der Diagrammdaten aus (z. B. E4) und geben Sie „1“ ein, den Wert, der das erste Intervall definiert.
2. Navigieren Sie zur Registerkarte Start.
3. Wählen Sie in der Bearbeitungsgruppe „Füllen“.“
4. Wählen Sie unter „Serie in „die Spalte „aus.“
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 wurden 149 Zellen in Spalte E (E5: E153) mit den Werten von 2 bis 150 gefüllt.

HINWEIS: Verstecken Sie nicht die ursprünglichen Datenzellen, wie auf 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üfungsnote erhält, die durch einen bestimmten X-Achsenwert dargestellt wird — für jedes der Intervalle. Zum Glück hat Excel das Arbeitspferd, um all diese Berechnungen für Sie durchzuführen: die NORM.DIST funktion.

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

Hier ist die decodierte Version, damit Sie sich entsprechend anpassen können:

Sie sperren den Mittelwert und SD-Werte, so dass Sie mühelos die Formel für die verbleibenden Intervalle 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 die Zeit gekommen, die Glockenkurve zu bauen:

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

Schritt #6: Richten Sie die Beschriftungstabelle ein.

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

Machen wir die Normalverteilung informativer, indem wir die Beschriftungen hinzufügen, die alle Standardabweichungswerte unter und über dem Mittelwert veranschaulichen (Sie können sie stattdessen auch zum Anzeigen der Z-Werte 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üllpunkt nach oben, um die Formel in die verbleibenden zwei Zellen (I2: I3) zu kopieren.

Wiederholen Sie den gleichen Vorgang für die Standardabweichungen über dem Mittelwert unter Verwendung 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 die Datenmarkierungen auf der horizontalen Achse platziert werden sollen.

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

Fügen Sie nun alle Daten hinzu, die Sie vorbereitet haben. Klicken Sie mit der rechten Maustaste auf das Diagrammdiagramm 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 „Werte der Serie X“ und J2: J8 für „Werte der Serie Y“ — und klicken Sie auf „OK.“

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

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 Diagrammdiagramm und wählen Sie „Diagrammtyp ändern“.“

Entwerfen Sie als Nächstes ein Kombinationsdiagramm:

1. Navigieren Sie zur Registerkarte Combo.
2. Ändern Sie für die Serie „Series2“ den Diagrammtyp in „Scatter“.“
   • Hinweis: Stellen Sie sicher, dass „Series1“ als „Scatter mit glatten Linien“ bleibt. Stellen Sie außerdem sicher, dass „Serie1“ nicht auf die sekundäre Achse verschoben wird — das Kontrollkästchen neben dem Diagrammtyp sollte nicht markiert sein.
3. Klicken Sie auf „OK.“

Schritt #9: Ändern Sie die horizontale 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ü.

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

• Wechseln Sie zur Registerkarte Achsenoptionen.
• Setzen Sie den minimalen Grenzwert auf „15.“
• Setzen Sie den maximalen Grenzwert auf „125.“

Da Sie jedoch die Standardabweichungsbereiche kennen, legen Sie die Grenzwerte ein wenig von jeder Ihrer dritten Standardabweichungen entfernt fest, um den „Schwanz“ der Kurve anzuzeigen.

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

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

Ersetzen Sie anschließend die Standardbeschriftungen durch die zuvor eingerichteten und platzieren Sie sie über den Datenmarkierungen.

1. Klicken Sie mit der rechten Maustaste auf eine beliebige Datenbezeichnung der Serie „Series2“.
2. Wählen Sie „Daten formatieren“.“
3. Wechseln Sie im Aufgabenbereich zur Registerkarte Beschriftungsoptionen.
4. Aktivieren Sie das Kontrollkästchen „X-Wert“.
5. Deaktivieren Sie das Kontrollkästchen „Y-Wert“.
6. Wählen Sie oben unter „Beschriftungsposition“ die Option „.“

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

Schritt #11: Färben Sie die Datenmarkierungen 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 Datenbezeichnung der Serie „Series2“.
2. Klicken Sie auf die Schaltfläche „Füllen“.
3. Wählen Sie Ihre Farbe aus der angezeigten Palette.

Entfernen Sie auch die Ränder um die Punkte:

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

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 (auf diese Weise werden die Linien direkt in das Diagramm eingefügt).
• Wechseln Sie zur Registerkarte Einfügen.
• Klicken Sie auf die Schaltfläche „Formen“.
• Wählen Sie „Zeile.“

Halten Sie die „SHIFT“ -Taste gedrückt, während Sie die Maus ziehen, um perfekt vertikale Linien von jedem Punkt an die Stelle zu zeichnen, an der jede Linie auf die Glockenkurve trifft.

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

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