So berechnen Sie einen Z-Score mit Microsoft Excel

Ein Z-Score ist ein statistischer Wert, der angibt, wie viele Standardabweichungen ein bestimmter Wert vom Mittelwert des gesamten Datensatzes 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 dann diese Ergebnisse verwenden, um den Z-Score jedes Werts zu bestimmen.

Was ist ein Z-Score und was machen 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 vom Mittelwert, um den ein Datenpunkt liegt. Die allgemeine Formel sieht so aus:

= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)

Hier ist ein Beispiel zur Verdeutlichung. Angenommen, Sie wollten die Testergebnisse zweier Algebra-Schüler vergleichen, die von verschiedenen Lehrern unterrichtet wurden. Sie wissen, dass der erste Schüler in einer Klasse 95% der Abschlussprüfung erhielt, und der Schüler der anderen Klasse erzielte 87%..

Auf den ersten Blick ist die Note von 95% beeindruckender, aber was wäre, wenn der Lehrer der zweiten Klasse eine schwierigere Prüfung ablegen würde? Sie können den Z-Score für jeden Schüler anhand der Durchschnittswerte in jeder Klasse und der Standardabweichung der Ergebnisse in jeder Klasse berechnen. Ein Vergleich der Z-Scores der beiden Schüler konnte zeigen, dass der Schüler mit der 87-Prozent-Bewertung im Vergleich zum Rest der Klasse besser abschnitt als der Schüler mit der 98-Prozent-Bewertung im Vergleich zum Rest der Klasse.

Der erste statistische Wert, den Sie benötigen, ist der "Mittelwert", und die Funktion "AVERAGE" 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 benötigen, ist die „Standardabweichung“. Excel bietet zwei verschiedene Funktionen, um die Standardabweichung auf unterschiedliche Weise zu berechnen.

Frühere Versionen von Excel hatten nur die Funktion "STDEV", mit der die Standardabweichung berechnet wird, während die Daten als "Stichprobe" einer Grundgesamtheit behandelt werden. In Excel 2010 wurde das in zwei Funktionen unterteilt, die die Standardabweichung berechnen:

• STDEV.S: Diese Funktion ist identisch mit der vorherigen Funktion „STDEV“. Es berechnet die Standardabweichung und behandelt die Daten als "Stichprobe" einer Population. Eine Stichprobe einer Population könnte etwa den Mücken entsprechen, 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 und behandelt die Daten wie die gesamte Grundgesamtheit. Eine Gesamtbevölkerung wäre so etwas wie alle Moskitos auf der Erde oder jedes Auto in einer Serie eines bestimmten Modells.

Welche Sie auswählen, hängt von Ihrem Datensatz ab. Der Unterschied ist normalerweise klein, aber das Ergebnis der Funktion "STDEV.P" ist immer kleiner als das Ergebnis der Funktion "STDEV.S" für denselben Datensatz. Es ist ein eher konservativer Ansatz, anzunehmen, dass die Daten mehr Schwankungen aufweisen.

Schauen wir uns ein Beispiel an

In unserem Beispiel haben wir zwei Spalten ("Werte" und "Z-Score") und drei "Helfer" -Zellen zum Speichern der Ergebnisse der Funktionen "AVERAGE", "STDEV.S" und "STDEV.P". Die Spalte „Werte“ enthält zehn um 500 zentrierte Zufallszahlen, und in der Spalte „Z-Score“ berechnen wir den Z-Score anhand der Ergebnisse, die in den Helferzellen gespeichert sind.

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

Geben Sie die folgende Formel ein und drücken Sie eingeben -oder- verwenden Sie das Menü "Formeln".

= DURCHSCHNITT (E2: E13)

Um auf die Funktion über das Menü "Formeln" zuzugreifen, wählen Sie die Dropdown-Liste "Weitere Funktionen" aus, wählen Sie die Option "Statistisch" und klicken Sie auf "DURCHSCHNITTLICH".

Wählen Sie im Fenster Funktionsargumente alle Zellen in der Spalte "Werte" als Eingabe für das Feld "Number1" aus. Sie müssen sich nicht um das Feld „Number2“ kümmern.

Drücken Sie nun "OK".

Als Nächstes müssen wir die Standardabweichung der Werte entweder 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 rufen Sie sie über das Menü "Formulas" auf)..

= STDEV.S (E3: E12)

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

Wählen Sie im Fenster Funktionsargumente alle Zellen in der Spalte "Werte" als Eingabe für das Feld "Number1" aus. Sie müssen sich hier auch nicht um das Feld „Number2“ 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 rufen Sie sie über das Menü "Formulas" auf)..

= STDEV.P (E3: E12)

Um auf die Funktion über das Menü "Formeln" zuzugreifen, wählen Sie die Dropdown-Liste "Weitere Funktionen" aus, wählen Sie die Option "Statistisch", scrollen Sie ein wenig nach unten und klicken Sie auf die Formel "STDEV.P".

Wählen Sie im Fenster Funktionsargumente alle Zellen in der Spalte "Werte" als Eingabe für das Feld "Number1" aus. Sie müssen sich auch nicht um das Feld „Number2“ kümmern.

Drücken Sie nun "OK".

Nachdem wir nun den Mittelwert und die Standardabweichung unserer Daten berechnet haben, haben wir alles, 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. Wir werden das Ergebnis der Funktion "STDEV.S" für dieses Beispiel verwenden, Sie können jedoch auch das Ergebnis aus "STDEV.P" verwenden.

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

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

Alternativ können Sie anstelle der Eingabe die folgenden Schritte zur Eingabe der Formel verwenden:

1. Klicken Sie auf Zelle F3 und geben Sie ein = (
2. Wählen Sie die Zelle E3 aus. (Sie können die Taste drücken linke Pfeiltaste einmal oder mit der Maus)
3. Geben Sie das Minuszeichen ein -
4. Markieren Sie Zelle G3 und drücken Sie F4 Hinzufügen der "$" -Zeichen, um eine "absolute" Referenz auf die Zelle zu setzen (sie wird durch "G3"> durchlaufen)$G$3 ">" G$3 ">"$G3 ">" G3 ", wenn Sie weiter drücken F4)
5. Art ) /
6. Wählen Sie die Zelle H3 (oder I3, wenn Sie "STDEV.P" verwenden) und drücken Sie F4 um die beiden "$" - Zeichen hinzuzufügen.
7. Drücken Sie Enter

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

Lassen Sie uns die Z-Werte 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, um die Formel in der oberen Zelle durch alle anderen ausgewählten Zellen zu kopieren.

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

Berechnung des Z-Scores ohne Verwendung von Helferzellen

Helferzellen speichern ein Ergebnis, z. B. die Ergebnisse der Funktionen "AVERAGE", "STDEV.S" und "STDEV.P". Sie können nützlich sein, sind aber nicht immer notwendig. Sie können sie bei der Berechnung eines Z-Scores ganz auslassen, indem Sie stattdessen die folgenden allgemeinen Formeln verwenden.

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

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

Und mit der Funktion „STEV.P“:

= (Value-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 „Auffüllen“ nicht die Durchschnitts- oder Standardabweichung eines anderen Bereichs berechnen von Zellen in jeder Formel.

Wenn Sie über einen großen Datensatz verfügen, ist es möglicherweise effizienter, Hilfszellen zu verwenden, da das Ergebnis der Funktionen "AVERAGE" und "STDEV.S" oder "STDEV.P" nicht jedes Mal berechnet wird, wodurch Prozessorressourcen gespart werden Beschleunigung der Zeit, die zum Berechnen der Ergebnisse benötigt wird.

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 32-Bit-Standardversion von Excel auf 2 GB RAM beschränkt ist (für die 64-Bit-Version gibt es keine Einschränkungen, wie viel RAM verwendet werden kann)..