So berechnen Sie einen Z-Score mit Microsoft Excel

Veröffentlicht: 2022-01-29

Ein Z-Score ist ein statistischer Wert, der angibt, wie viele Standardabweichungen ein bestimmter Wert zufällig vom Mittelwert des gesamten Datensatzes entfernt ist. Sie können die Formeln AVERAGE und STDEV.S oder STDEV.P verwenden, um den Mittelwert und die Standardabweichung Ihrer Daten zu berechnen und diese Ergebnisse dann verwenden, um den Z-Score jedes Werts zu bestimmen.

Was ist ein Z-Score und was bewirken die Funktionen AVERAGE, STDEV.S und STDEV.P?

Ein Z-Score ist eine einfache Möglichkeit, Werte aus zwei verschiedenen Datensätzen zu vergleichen. Sie ist definiert als die Anzahl der Standardabweichungen, die ein Datenpunkt vom Mittelwert entfernt liegt. Die allgemeine Formel sieht so aus:

 =(Datenpunkt-AVERAGE(Datensatz))/STDEV(Datensatz)

Hier ist ein Beispiel zur Verdeutlichung. Angenommen, Sie möchten die Testergebnisse zweier Algebra-Schüler vergleichen, die von verschiedenen Lehrern unterrichtet werden. Sie wissen, dass der erste Schüler bei der Abschlussprüfung in einer Klasse 95 % erreicht hat und der Schüler in der anderen Klasse 87 %.

Auf den ersten Blick ist die 95%-Note beeindruckender, aber was wäre, wenn der Lehrer der zweiten Klasse eine schwierigere Prüfung abgeben würde? Sie könnten den Z-Wert der Punktzahl jedes Schülers basierend auf den durchschnittlichen Punktzahlen in jeder Klasse und der Standardabweichung der Punktzahlen in jeder Klasse berechnen. Ein Vergleich der Z-Werte der beiden Schüler könnte zeigen, dass der Schüler mit der Punktzahl von 87 % im Vergleich zum Rest seiner Klasse besser abschnitt als der Schüler mit der Punktzahl von 98 % im Vergleich zum Rest seiner Klasse.

Der erste statistische Wert, den Sie benötigen, ist der „Mittelwert“, und die „AVERAGE“-Funktion von Excel berechnet diesen Wert. Es addiert einfach alle Werte in einem Zellbereich und dividiert diese Summe durch die Anzahl der Zellen, die numerische Werte enthalten (leere Zellen werden ignoriert).

Der andere statistische Wert, den wir brauchen, ist die „Standardabweichung“, und Excel hat zwei verschiedene Funktionen, um die Standardabweichung auf leicht unterschiedliche Weise zu berechnen.

Frühere Versionen von Excel hatten nur die „STDEV“-Funktion, die die Standardabweichung berechnet, während die Daten als „Stichprobe“ einer Grundgesamtheit behandelt werden. Excel 2010 hat das in zwei Funktionen aufgeteilt, die die Standardabweichung berechnen:

STDEV.S: Diese Funktion ist identisch mit der vorherigen „STDEV“-Funktion. Es berechnet die Standardabweichung, während es die Daten als „Stichprobe“ einer Grundgesamtheit behandelt. Eine Stichprobe einer Population könnte so etwas wie die speziellen Mücken sein, die für ein Forschungsprojekt gesammelt wurden, oder Autos, die beiseite gelegt und für Crash-Sicherheitstests verwendet wurden.
STDEV.P: Diese Funktion berechnet die Standardabweichung, während die Daten als die gesamte Grundgesamtheit behandelt werden. Eine ganze Population wäre so etwas wie alle Mücken auf der Erde oder jedes Auto in einer Produktionsserie eines bestimmten Modells.

Welche Sie wählen, basiert auf Ihrem Datensatz. Der Unterschied ist normalerweise gering, aber das Ergebnis der Funktion „STDEV.P“ ist immer kleiner als das Ergebnis der Funktion „STDEV.S“ für denselben Datensatz. Es ist ein konservativerer Ansatz anzunehmen, dass die Daten mehr Variabilität aufweisen.

Sehen wir uns ein Beispiel an

In unserem Beispiel haben wir zwei Spalten („Werte“ und „Z-Score“) und drei „Hilfszellen“ zum Speichern der Ergebnisse der Funktionen „AVERAGE“, „STDEV.S“ und „STDEV.P“. Die Spalte „Werte“ enthält zehn Zufallszahlen, die um 500 herum zentriert sind, und in der Spalte „Z-Score“ berechnen wir den Z-Score unter Verwendung der in den „Hilfs“-Zellen gespeicherten Ergebnisse.

Zuerst berechnen wir den Mittelwert der Werte mit der Funktion „AVERAGE“. Wählen Sie die Zelle aus, in der Sie das Ergebnis der „AVERAGE“-Funktion speichern möchten.

Geben Sie die folgende Formel ein und drücken Sie die Eingabetaste – oder – verwenden Sie das Menü „Formeln“.

 =MITTELWERT(E2:E13)

Um auf die Funktion über das Menü „Formeln“ zuzugreifen, wählen Sie das Dropdown-Menü „Weitere Funktionen“, wählen Sie die Option „Statistisch“ und klicken Sie dann auf „MITTELWERT“.

Wählen Sie im Fenster „Funktionsargumente“ alle Zellen in der Spalte „Werte“ als Eingabe für das Feld „Zahl1“ aus. Um das Feld „Nummer2“ brauchen Sie sich keine Gedanken zu machen.

Drücken Sie nun „OK“.

Als nächstes müssen wir die Standardabweichung der Werte mit der Funktion „STDEV.S“ oder „STDEV.P“ berechnen. In diesem Beispiel zeigen wir Ihnen, wie Sie beide Werte berechnen, beginnend mit „STDEV.S.“ Wählen Sie die Zelle aus, in der das Ergebnis gespeichert werden soll.

Um die Standardabweichung mit der Funktion „STDEV.S“ zu berechnen, geben Sie diese Formel ein und drücken Sie die Eingabetaste (oder greifen Sie über das Menü „Formeln“ darauf zu).

 =STABW.S(E3:E12)

Um auf die Funktion über das Menü „Formeln“ zuzugreifen, wählen Sie das Dropdown-Menü „Weitere Funktionen“, wählen Sie die Option „Statistisch“, scrollen Sie ein wenig nach unten und klicken Sie dann auf den Befehl „STDEV.S“.

Wählen Sie im Fenster „Funktionsargumente“ alle Zellen in der Spalte „Werte“ als Eingabe für das Feld „Zahl1“ aus. Auch hier brauchen Sie sich um das Feld „Nummer2“ nicht zu kümmern.

Drücken Sie nun „OK“.

Als nächstes berechnen wir die Standardabweichung mit der Funktion „STDEV.P“. Wählen Sie die Zelle aus, in der das Ergebnis gespeichert werden soll.

Um die Standardabweichung mit der Funktion „STDEV.P“ zu berechnen, geben Sie diese Formel ein und drücken Sie die Eingabetaste (oder greifen Sie über das Menü „Formeln“ darauf zu).

=STABW.P(E3:E12)

Um auf die Funktion über das Menü „Formeln“ zuzugreifen, wählen Sie das Dropdown-Menü „Weitere Funktionen“, wählen Sie die Option „Statistik“, scrollen Sie ein wenig nach unten und klicken Sie dann auf die Formel „STDEV.P“.

Wählen Sie im Fenster „Funktionsargumente“ alle Zellen in der Spalte „Werte“ als Eingabe für das Feld „Zahl1“ aus. Auch hier brauchen Sie sich um das Feld „Number2“ nicht zu kümmern.

Drücken Sie nun „OK“.

Nachdem wir nun den Mittelwert und die Standardabweichung unserer Daten berechnet haben, haben wir alles, was wir brauchen, um den Z-Score zu berechnen. Wir können eine einfache Formel verwenden, die auf die Zellen verweist, die die Ergebnisse der Funktionen „AVERAGE“ und „STDEV.S“ oder „STDEV.P“ enthalten.

Wählen Sie die erste Zelle in der Spalte „Z-Score“ aus. Für dieses Beispiel verwenden wir das Ergebnis der Funktion „STDEV.S“, aber Sie können auch das Ergebnis von „STDEV.P“ verwenden.

Geben Sie die folgende Formel ein und drücken Sie die Eingabetaste:

 =(E3-$G$3)/$H$3

Alternativ können Sie die folgenden Schritte verwenden, um die Formel einzugeben, anstatt sie einzutippen:

Klicken Sie auf Zelle F3 und geben Sie =(
Markieren Sie die Zelle E3. (Sie können die linke Pfeiltaste einmal drücken oder die Maus verwenden)
Geben Sie das Minuszeichen ein -
Wählen Sie die Zelle G3 aus und drücken Sie dann F4 , um die „$“-Zeichen hinzuzufügen, um einen „absoluten“ Verweis auf die Zelle herzustellen (es wird durch „G3“ > „ $ G $ 3“ > „G $ 3“ > „ $ G3“ > durchlaufen „G3“, wenn Sie weiterhin F4 drücken)
Typ )/
Wählen Sie die Zelle H3 (oder I3, wenn Sie „STDEV.P“ verwenden) und drücken Sie F4 , um die beiden „$“-Zeichen hinzuzufügen.
Drücken Sie Enter

Der Z-Score wurde für den ersten Wert berechnet. Er liegt 0,15945 Standardabweichungen unter dem Mittelwert. Um die Ergebnisse zu überprüfen, können Sie die Standardabweichung mit diesem Ergebnis (6,271629 * -0,15945) multiplizieren und überprüfen, ob das Ergebnis gleich der Differenz zwischen dem Wert und dem Mittelwert (499-500) ist. Beide Ergebnisse sind gleich, daher ist der Wert sinnvoll.

Lassen Sie uns die Z-Scores der restlichen Werte berechnen. Markieren Sie die gesamte Spalte „Z-Score“, beginnend mit der Zelle, die die Formel enthält.

Drücken Sie Strg+D, wodurch die Formel in der obersten Zelle nach unten durch alle anderen ausgewählten Zellen kopiert wird.

Jetzt wurde die Formel für alle Zellen „ausgefüllt“, und jede verweist aufgrund der „$“-Zeichen immer auf die richtigen „AVERAGE“- und „STDEV.S“- oder „STDEV.P“-Zellen. Wenn Sie Fehler erhalten, gehen Sie zurück und vergewissern Sie sich, dass die „$“-Zeichen in der eingegebenen Formel enthalten sind.

Berechnung des Z-Scores ohne Verwendung von „Helfer“-Zellen

Hilfszellen speichern ein Ergebnis, wie diejenigen, die die Ergebnisse der Funktionen „AVERAGE“, „STDEV.S“ und „STDEV.P“ speichern. Sie können nützlich sein, sind aber nicht immer notwendig. Sie können sie bei der Berechnung eines Z-Scores ganz überspringen, indem Sie stattdessen die folgenden verallgemeinerten Formeln verwenden.

Hier ist eine, die die Funktion „STDEV.S“ verwendet:

 =(Wert-AVERAGE(Werte))/STDEV.S(Werte)

Und eine mit der Funktion „STEV.P“:

 =(Wert-AVERAGE(Werte))/STDEV.P(Werte)

Achten Sie bei der Eingabe der Zellbereiche für die „Werte“ in den Funktionen darauf, absolute Bezüge („$“ mit F4) hinzuzufügen, damit Sie beim „Ausfüllen“ nicht den Durchschnitt oder die Standardabweichung eines anderen Bereichs berechnen von Zellen in jeder Formel.

Wenn Sie über einen großen Datensatz verfügen, kann es effizienter sein, Hilfszellen zu verwenden, da das Ergebnis der Funktionen „AVERAGE“ und „STDEV.S“ oder „STDEV.P“ nicht jedes Mal berechnet wird, was Prozessorressourcen spart und Beschleunigung der Zeit, die benötigt wird, um die Ergebnisse zu berechnen.

Außerdem benötigt „$G$3“ weniger Bytes zum Speichern und weniger RAM zum Laden als „AVERAGE($E$3:$E$12).“. Dies ist wichtig, da die standardmäßige 32-Bit-Version von Excel auf 2 GB RAM beschränkt ist (die 64-Bit-Version hat keine Beschränkungen hinsichtlich der nutzbaren RAM-Größe).