Ein Z-Score ist ein statistischer Messwert, der aufzeigt, wie weit ein bestimmter Datenpunkt vom Mittelwert einer Datenreihe entfernt liegt, gemessen in Standardabweichungen. Um den Z-Score zu ermitteln, benötigt man den Mittelwert und die Standardabweichung des Datensatzes. Diese können mit den Funktionen MITTELWERT und entweder STABW.S oder STABW.P berechnet werden. Anschließend kann der Z-Score jedes einzelnen Wertes aus diesen Ergebnissen abgeleitet werden.
Was bedeutet ein Z-Score und wofür stehen die Funktionen MITTELWERT, STABW.S und STABW.P?
Der Z-Score ermöglicht einen einfachen Vergleich von Werten aus unterschiedlichen Datensätzen. Er wird als die Anzahl der Standardabweichungen definiert, die ein Datenpunkt vom Mittelwert entfernt ist. Die grundlegende Formel lautet:
=(Datenpunkt-MITTELWERT(Datensatz))/STABW(Datensatz)
Ein praktisches Beispiel: Stellen Sie sich vor, Sie möchten die Testergebnisse von zwei Schülern vergleichen, die Algebra bei verschiedenen Lehrern gelernt haben. Der erste Schüler erreichte 95 % in seiner Abschlussprüfung, der zweite 87 %. Auf den ersten Blick scheint die 95%-Note besser zu sein. Aber was, wenn die Prüfung des zweiten Lehrers schwieriger war? Hier kommt der Z-Score ins Spiel. Er ermöglicht einen fairen Vergleich, indem er die Punktzahlen relativ zum Klassendurchschnitt und der Standardabweichung der Punktzahlen der jeweiligen Klasse setzt. Ein Vergleich der Z-Scores könnte zeigen, dass der Schüler mit 87 % im Vergleich zu seiner Klasse besser abgeschnitten hat als der Schüler mit 95 % im Vergleich zu seiner Klasse.
Der erste benötigte statistische Wert ist der Mittelwert. Die Funktion „MITTELWERT“ in Excel ermittelt diesen Wert, indem sie alle Werte innerhalb eines Zellenbereichs addiert und die Summe durch die Anzahl der Zellen mit numerischen Werten teilt. Leere Zellen werden dabei ignoriert.
Ein weiterer wichtiger Wert ist die Standardabweichung. Excel bietet hierfür zwei unterschiedliche Funktionen, die die Standardabweichung auf leicht verschiedene Weisen berechnen.
In älteren Excel-Versionen gab es nur die Funktion „STABW“, welche die Standardabweichung berechnete, indem sie die Daten als Stichprobe einer Grundgesamtheit behandelte. Excel 2010 führte eine Aufteilung in zwei Funktionen ein:
STABW.S: Diese Funktion entspricht der vorherigen „STABW“-Funktion. Sie berechnet die Standardabweichung, wobei die Daten als Stichprobe einer größeren Grundgesamtheit betrachtet werden. Ein Beispiel für eine Stichprobe wäre eine Gruppe von Mücken, die für ein Forschungsprojekt gesammelt wurden, oder Autos, die für Crashtests ausgewählt wurden.
STABW.P: Diese Funktion ermittelt die Standardabweichung, wenn die Daten als die gesamte Grundgesamtheit betrachtet werden. Eine gesamte Population wären zum Beispiel alle Mücken auf der Erde oder jedes Auto einer bestimmten Modellreihe.
Die Wahl der richtigen Funktion hängt von den vorliegenden Daten ab. Die Ergebnisse von STABW.P sind in der Regel etwas kleiner als die von STABW.S. Es ist konservativer anzunehmen, dass die Daten eine größere Variabilität aufweisen.
Ein praktisches Beispiel
In diesem Beispiel haben wir zwei Spalten mit den Bezeichnungen „Werte“ und „Z-Score“ sowie drei zusätzliche Zellen, in denen die Ergebnisse der Funktionen „MITTELWERT“, „STABW.S“ und „STABW.P“ gespeichert werden. Die Spalte „Werte“ enthält zehn zufällige Zahlen um den Wert 500. In der Spalte „Z-Score“ berechnen wir den Z-Score mithilfe der in den Zusatz-Zellen gespeicherten Ergebnisse.
Zuerst berechnen wir den Mittelwert der Werte mit der Funktion „MITTELWERT“. Wählen Sie die Zelle aus, in der Sie das Ergebnis speichern möchten.
Geben Sie diese Formel ein und bestätigen Sie mit der Eingabetaste oder verwenden Sie das Menü „Formeln“:
=MITTELWERT(E2:E13)
Um die Funktion über das Menü „Formeln“ zu erreichen, wählen Sie „Weitere Funktionen“, dann „Statistisch“ und klicken Sie auf „Mittelwert“.
Wählen Sie im Fenster „Funktionsargumente“ alle Zellen der Spalte „Werte“ als Eingabe für das Feld „Zahl1“. Das Feld „Zahl2“ wird in diesem Fall nicht benötigt.
Bestätigen Sie mit „OK“.
Als Nächstes berechnen wir die Standardabweichung der Werte mit der Funktion „STABW.S“ oder „STABW.P“. In diesem Beispiel zeigen wir, wie beide Werte berechnet werden, beginnend mit „STABW.S“. Wählen Sie die Zelle aus, in der das Ergebnis gespeichert werden soll.
Um die Standardabweichung mit „STABW.S“ zu berechnen, geben Sie diese Formel ein und bestätigen Sie mit der Eingabetaste (oder greifen Sie über das Menü „Formeln“ darauf zu):
=STABW.S(E3:E12)
Um die Funktion über das Menü „Formeln“ zu finden, wählen Sie „Weitere Funktionen“, dann „Statistisch“, scrollen Sie etwas nach unten und klicken Sie auf „STABW.S“.
Im Fenster „Funktionsargumente“ wählen Sie alle Zellen in der Spalte „Werte“ als Eingabe für das Feld „Zahl1“. Auch hier wird das Feld „Zahl2“ nicht benötigt.
Klicken Sie auf „OK“.
Als nächstes berechnen wir die Standardabweichung mit der Funktion „STABW.P“. Wählen Sie die Zelle aus, in der das Ergebnis angezeigt werden soll.
Um die Standardabweichung mit der Funktion „STABW.P“ zu berechnen, geben Sie diese Formel ein und drücken Sie die Eingabetaste (oder wählen Sie sie im Menü „Formeln“ aus).
=STABW.P(E3:E12)
Wählen Sie im Menü „Formeln“ „Weitere Funktionen“, dann „Statistisch“, scrollen Sie etwas nach unten und klicken Sie auf „STABW.P“.
Im Fenster „Funktionsargumente“ wählen Sie alle Zellen der Spalte „Werte“ als Eingabe für „Zahl1“. Auch hier ist „Zahl2“ nicht relevant.
Drücken Sie „OK“.
Nachdem wir nun den Mittelwert und die Standardabweichung der Daten berechnet haben, können wir den Z-Score ermitteln. Wir verwenden hierfür eine einfache Formel, die sich auf die Zellen bezieht, die die Ergebnisse der Funktionen „MITTELWERT“ und „STABW.S“ oder „STABW.P“ enthalten.
Wählen Sie die erste Zelle in der Spalte „Z-Score“. In diesem Beispiel wird das Ergebnis der Funktion „STABW.S“ verwendet. Sie können aber auch das Ergebnis von „STABW.P“ nutzen.
Geben Sie diese Formel ein und drücken Sie die Eingabetaste:
=(E3-$G$3)/$H$3
Alternativ können Sie die Formel auch mit den folgenden Schritten eingeben:
Klicken Sie auf die Zelle F3 und geben Sie = ( ein.
Wählen Sie die Zelle E3 aus (mit der linken Pfeiltaste oder der Maus).
Geben Sie das Minuszeichen ein (-).
Wählen Sie die Zelle G3 aus und drücken Sie dann F4, um die Dollarzeichen ($) einzufügen, um eine absolute Referenz auf die Zelle zu erstellen (es durchläuft “G3” > “$G$3” > “G$3” > “$G3” > “G3” wenn Sie F4 weiterhin drücken).
Geben Sie )/ ein.
Wählen Sie die Zelle H3 (oder I3, falls Sie „STABW.P“ verwendet haben) und drücken Sie F4, um die beiden Dollarzeichen hinzuzufügen.
Bestätigen Sie mit der Eingabetaste.
Der Z-Score für den ersten Wert wurde jetzt berechnet und liegt 0,15945 Standardabweichungen unter dem Mittelwert. Um die Ergebnisse zu verifizieren, multiplizieren Sie die Standardabweichung mit dem Ergebnis (6,271629 * -0,15945). Das Ergebnis sollte dem Unterschied zwischen dem Wert und dem Mittelwert entsprechen (499-500). In diesem Fall stimmen die Werte überein, daher ist das Ergebnis korrekt.
Lassen Sie uns nun die Z-Scores für die restlichen Werte berechnen. Markieren Sie die gesamte Spalte „Z-Score“ beginnend mit der Zelle, die die Formel enthält.
Drücken Sie Strg+D, um die Formel in der obersten Zelle auf alle anderen markierten Zellen zu kopieren.
Die Formel ist jetzt in allen Zellen „ausgefüllt“ und bezieht sich dank der Dollarzeichen immer auf die richtigen Zellen mit den Werten für „MITTELWERT“ und „STABW.S“ oder „STABW.P“. Falls Fehler auftreten, gehen Sie zurück und stellen Sie sicher, dass die Dollarzeichen ($) in der Formel korrekt eingefügt wurden.
Z-Score-Berechnung ohne Zusatz-Zellen
Zusatz-Zellen dienen dazu, Ergebnisse wie die der Funktionen „MITTELWERT“, „STABW.S“ und „STABW.P“ zu speichern. Sie können nützlich, aber nicht zwingend erforderlich sein. Bei der Berechnung eines Z-Scores können diese Zellen übersprungen werden, indem man stattdessen die folgenden allgemeinen Formeln verwendet.
Hier eine Formel, die die Funktion „STABW.S“ nutzt:
=(Wert-MITTELWERT(Werte))/STABW.S(Werte)
Und hier eine mit der Funktion „STABW.P“:
=(Wert-MITTELWERT(Werte))/STABW.P(Werte)
Achten Sie bei der Eingabe der Zellbereiche für die „Werte“ darauf, absolute Bezüge (mit „$“ und F4) hinzuzufügen, damit beim „Ausfüllen“ nicht der Durchschnitt oder die Standardabweichung eines anderen Zellbereichs in jeder Formel berechnet wird.
Bei einem großen Datensatz kann es effizienter sein, mit Zusatz-Zellen zu arbeiten, da die Ergebnisse der Funktionen „MITTELWERT“ und „STABW.S“ bzw. „STABW.P“ nicht bei jeder Berechnung neu ermittelt werden müssen, wodurch Rechenleistung gespart und die Ergebnisse schneller ermittelt werden.
Darüber hinaus benötigt „$G$3“ weniger Speicherplatz und RAM als „MITTELWERT($E$3:$E$12)“. Dies ist besonders wichtig, da die standardmäßige 32-Bit-Version von Excel auf 2 GB RAM beschränkt ist (die 64-Bit-Version hat keine solchen Einschränkungen hinsichtlich der RAM-Nutzung).