Gaußsche Normalverteilung Excel – Vorlage Download

In diesem Tutorial wird gezeigt, wie Sie eine Glockenkurve mit Normalverteilung in allen Versionen von Excel erstellen können: 2007, 2010, 2013, 2016 und 2019.

In der Statistik ist eine Glockenkurve (auch bekannt als Standardnormalverteilung oder Gaußsche Kurve) ein symmetrisches Diagramm, das die Tendenz von Daten veranschaulicht, sich um einen Mittelwert in einem bestimmten Datensatz zu gruppieren. Die y-Achse stellt die relative Wahrscheinlichkeit des Auftretens eines bestimmten Wertes im Datensatz dar, während die x-Achse die Werte selbst in das Diagramm einträgt, so dass eine glockenförmige Kurve entsteht, daher der Name. Das Diagramm hilft uns bei der Analyse, ob ein bestimmter Wert Teil der erwarteten Schwankung ist oder statistisch signifikant ist und daher genauer untersucht werden muss.

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

In diesem Tutorial lernen Sie Schritt für Schritt, wie Sie eine Glockenkurve der Normalverteilung in Excel von Grund auf erstellen können:

 

 

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

• Der Mittelwert (auch bekannt als Standardmaß). Er bestimmt den Mittelpunkt der Kurve, der wiederum die Lage der Kurve charakterisiert.
• Die Standardabweichung (SD) der Messungen. Damit wird die Streuung Ihrer Daten in der Normalverteilung definiert – oder einfach ausgedrückt, wie breit die Kurve sein sollte. In der oben gezeigten Glockenkurve entspricht beispielsweise eine Standardabweichung des Mittelwerts der Spanne zwischen den Prüfungsergebnissen 53 und 85.

Je niedriger der SD-Wert ist, desto höher ist die Kurve und desto weniger streuen die Daten, und umgekehrt. Es lohnt sich, die 68-95-99,7-Regel zu erwähnen, die auf jede Normalverteilungskurve angewendet werden kann. Das bedeutet, dass ungefähr 68 % Ihrer Daten innerhalb einer SD vom Mittelwert entfernt liegen werden, 95 % innerhalb von zwei SD und 99,7 % innerhalb von drei SD.

 

 

Nachdem Sie nun das Wesentliche wissen, wollen wir von der Theorie zur Praxis übergehen.

Erste Schritte

Zur Veranschaulichung: Nehmen wir an, Sie haben die Testergebnisse von 200 Schülern und möchten sie „auf einer Kurve“ benoten, d. h. die Noten der Schüler basieren auf ihren relativen Leistungen im Vergleich zum Rest der Klasse:

 

Schritt #1: Ermitteln Sie den Mittelwert.

Normalerweise werden Ihnen die Mittelwerte und die SD-Werte von Anfang an vorgegeben, aber wenn das nicht der Fall ist, können Sie diese Werte in wenigen einfachen Schritten berechnen. Fangen wir mit dem Mittelwert an. Da der Mittelwert den Durchschnittswert einer Stichprobe oder Grundgesamtheit von Daten angibt, können Sie Ihre Standardmessung mithilfe der Funktion MITTELWERT ermitteln. Geben Sie die folgende Formel in eine beliebige leere Zelle (in diesem Beispiel F1) neben Ihren eigentlichen Daten (Spalten A und B) ein, um den Durchschnitt der Prüfungsergebnisse im Datensatz zu berechnen:

=MITTELWERT(B2:B201)

Ein kurzer Hinweis: In den meisten Fällen müssen Sie die Ausgabe der Formel aufrunden. Dazu verpacken Sie sie einfach in die Funktion RUNDEN wie folgt:

=RUNDEN(MITTELWERT(B2:B201),0)

 

Schritt #2: Ermitteln Sie die Standardabweichung.

Einer geschafft, einer fehlt noch. Glücklicherweise verfügt Excel über eine spezielle Funktion, mit der Sie die Standardabweichung selbst ermitteln können:

=STABW.N(B2:B201)

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

=RUNDEN(STABW.N(B2:B201),0)

 

Schritt #3: Legen Sie die Werte für die X-Achse der Kurve fest.

Im Grunde genommen 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 Werte der X-Achse verwendet, um ein bestimmtes Prüfungsergebnis darzustellen, während die Werte der Y-Achse die Wahrscheinlichkeit angeben, mit der ein Schüler diese Punktzahl in der Prüfung erreicht. Technisch gesehen können Sie so viele Intervalle einbeziehen, wie Sie möchten – Sie können die überflüssigen Daten später mühelos löschen, indem Sie die Skala der horizontalen Achse ändern. Achten Sie nur darauf, dass Sie einen Bereich wählen, der die drei Standardabweichungen einschließt. Beginnen wir mit einer Zählung bei eins (da es keine Möglichkeit gibt, dass ein Schüler ein negatives Prüfungsergebnis erzielt) und gehen wir bis 150 – es spielt keine Rolle, ob es 150 oder 1500 sind – um eine weitere Hilfstabelle zu erstellen.

1. Wählen Sie eine beliebige leere Zelle unter den Diagrammdaten (z. B. E4) und geben Sie „1“ ein, den Wert, der das erste Intervall definiert.
2. Wechseln Sie zur Registerkarte Start.
3. Wählen Sie in der Gruppe “ Bearbeiten “ die Option „Ausfüllen“.„
4. Wählen Sie unter „Reihe in“ die Option „Spalten“.„
5. Geben Sie für „Schrittwert“ den Wert „1“ ein. Dieser Wert bestimmt die Schritte, die automatisch addiert werden, bis Excel das letzte Intervall erreicht.
6. Geben Sie für „Endwert“ den Wert „150″ ein, der für das letzte Intervall steht, und klicken Sie auf „OK“

 

 

Wie durch ein Wunder sind 149 Zellen in Spalte E (E5:E153) mit den Werten von 2 bis 150 gefüllt worden. HINWEIS: Blenden Sie die ursprünglichen Datenzellen nicht aus, wie auf den Bildschirmfotos gezeigt. Andernfalls wird die Technik nicht funktionieren.

Schritt #4: Berechnen Sie die Werte der Normalverteilung für jeden Wert der X-Achse.

Ermitteln Sie nun für jedes der Intervalle die Werte der Normalverteilung – also die Wahrscheinlichkeit, dass ein Schüler ein bestimmtes Prüfungsergebnis erzielt, das durch einen bestimmten Wert auf der X-Achse dargestellt wird. Zu Ihrem Glück verfügt Excel über ein Arbeitstier, das all diese Berechnungen für Sie erledigt: die Funktion NORM.VERT. Geben Sie die folgende Formel in die Zelle rechts(F4) von Ihrem ersten Intervall (E4) ein:

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

Hier ist die entschlüsselte Version, damit Sie die Formel entsprechend anpassen können:

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

Sie sperren die Werte für Mittelwert und Standardabweichung, so dass Sie die Formel für die übrigen Intervalle (E5:E153) mühelos ausführen können.

 

 

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

Schritt #5: Erstellen Sie ein Punktdiagramm mit glatten Linien.

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

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

 

 

Schritt #6: Erstellen Sie die Beschriftungstabelle.

Technisch gesehen, haben Sie Ihre Glockenkurve. Aber sie wäre schwer zu lesen, da sie keine Daten enthält, die sie beschreiben. Um die Normalverteilung informativer zu gestalten, fügen wir die Beschriftungen hinzu, die alle Standardabweichungswerte unterhalb und oberhalb des Mittelwerts darstellen (Sie können sie stattdessen auch für die Darstellung der Z-Werte verwenden). Dazu erstellen Sie eine weitere Hilfstabelle wie folgt:

 

 

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

 

 

Berechnen Sie dann die Standardabweichung unterhalb des Mittelwerts, indem Sie diese einfache Formel in Zelle I4 eingeben:

=I5-$F$2

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

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

=I5+$F$2

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

Füllen Sie schließlich die Werte der Y-Achsenbeschriftung (J2:J8) mit Nullen, da Sie die Datenmarkierungen auf der horizontalen Achse platzieren möchten.

 

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

Fügen Sie nun alle Daten ein, die Sie vorbereitet haben. Klicken Sie mit der rechten Maustaste auf das Diagramm und wählen Sie „Daten auswählen“.

 

 

Wählen Sie in dem sich öffnenden Dialogfeld „Hinzufügen“

 

 

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

 

 

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

Der nächste 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 die Diagrammdarstellung und wählen Sie „Diagrammtyp ändern“. Als Nächstes entwerfen Sie ein Kombidiagramm:

1. Navigieren Sie zur Registerkarte “ Kombi“.
2. Ändern Sie für die Reihe „Datenreihen 2“ den Diagrammtyp in „Punkt (XY)“.
   • Hinweis: Vergewissern Sie sich, dass „Datenreihen 1“ als „Punkte mit interpolierten Linien“ erhalten bleibt Stellen Sie außerdem sicher, dass „Datenreihen 1“ 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 Skala der horizontalen Achse.

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

 

 

Sobald das Aufgabenfenster erscheint, gehen Sie wie folgt vor:

• Wechseln Sie auf die Registerkarte Achsenoptionen.
• Setzen Sie den Wert Minimum auf „15„
• Setzen Sie den Wert für Maximum auf „125„

Sie können den Achsenskalierungsbereich nach Belieben anpassen, aber da Sie die Standardabweichungsbereiche kennen, stellen Sie die Werte für die Grenzen etwas weiter weg von jeder Ihrer dritten Standardabweichungen ein, um das „Ende“ der Kurve anzuzeigen.

 

 

Schritt #10: Einfügen und Positionieren der benutzerdefinierten Datenbeschriftungen.

Wenn Sie Ihr Diagramm aufpolieren, fügen Sie unbedingt die benutzerdefinierten Datenbeschriftungen hinzu. Klicken Sie zunächst mit der rechten Maustaste auf einen beliebigen Punkt, der die Reihe „Datenreihen 2“ darstellt, und wählen Sie „Datenbeschriftungen hinzufügen“.“

 

 

Ersetzen Sie dann die Standardbeschriftungen durch die zuvor eingerichteten und platzieren Sie sie über den Datenmarkierungen.

1. Klicken Sie mit der rechten Maustaste auf ein Datenetikett der Reihe „Datenreihen 2“.
2. Wählen Sie „Datenbeschriftungen formatieren“.„
3. Wechseln Sie im Aufgabenbereich auf die Registerkarte “ Beschriftungsoptionen „.
4. Aktivieren Sie das Kontrollkästchen „X-Wert„.
5. Deaktivieren Sie das Kontrollkästchen „Y-Wert„.
6. Wählen Sie unter „Beschriftungsposition“ die Option „Über„

 

 

Außerdem können Sie jetzt die Gitternetzlinien entfernen (Rechtsklick darauf > Löschen).

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

Zum Schluss färben Sie die Punkte um, damit sie zu Ihrem Diagrammstil passen.

1. Klicken Sie mit der rechten Maustaste auf ein Datenetikett der Reihe „Datenreihe 2“.
2. Klicken Sie auf die Schaltfläche „Füllung„.
3. Wählen Sie die gewünschte Farbe aus der erscheinenden Palette.

 

 

Entfernen Sie auch die Ränder um die Punkte:

1. Klicken Sie erneut mit der rechten Maustaste auf die gleiche Datenmarkierung und wählen Sie „Kontur„
2. Wählen Sie „Keine Kontur„

 

 

Schritt #12: Vertikale Linien hinzufügen (optional).

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

• Wählen Sie die Diagrammdarstellung 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 „Linien“.

Halten Sie die „SHIFT“ -Taste gedrückt, während Sie die Maus ziehen, um von jedem Punkt bis zu der Stelle, an der die Linien auf die Glockenkurve treffen, genau senkrechte Linien zu ziehen.

 

 

Ändern Sie den Titel des Diagramms, und Ihre verbesserte Glockenkurve ist fertig und zeigt Ihre wertvollen Verteilungsdaten.

 

 

Und so geht’s. Sie können nun einen beliebigen Datensatz auswählen und mit diesen einfachen Schritten eine Glockenkurve mit Normalverteilung erstellen!