So berechnen Sie einen Z-Score mit Microsoft Excel

Ein Z-Score ist ein statistischer Wert, der angibt, wie viele Standardabweichungen ein bestimmter Wert zufällig vom Mittelwert des gesamten Datensatzes aufweist. 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 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. Es ist definiert als die Anzahl der Standardabweichungen vom Mittelwert eines Datenpunkts. Die allgemeine Formel sieht folgendermaßen aus:

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

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

Auf den ersten Blick ist die 95% -Note beeindruckender, aber was ist, wenn der Lehrer der zweiten Klasse eine schwierigere Prüfung abgelegt hat? Sie können den Z-Score der Punktzahl jedes Schülers basierend auf den Durchschnittswerten in jeder Klasse und der Standardabweichung der Werte in jeder Klasse berechnen. Ein Vergleich der Z-Scores der beiden Schüler konnte zeigen, dass der Schüler mit der 87% -Bewertung im Vergleich zum Rest seiner Klasse besser abschnitt als der Schüler mit der 98% -Bewertung im Vergleich zum Rest seiner Klasse.

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

Frühere Versionen von Excel hatten nur die Funktion „STDEV“, die die Standardabweichung berechnet und die Daten als „Stichprobe“ einer Population behandelt. Excel 2010 hat dies in zwei Funktionen unterteilt, die die Standardabweichung berechnen:

  • STDEV.S: Diese Funktion ist identisch mit der vorherigen Funktion „STDEV“. Es berechnet die Standardabweichung, während die Daten als "Stichprobe" einer Population behandelt werden. 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 Gesamtpopulation behandelt werden. Eine ganze Bevölkerung wäre so etwas wie alle Mücken auf der Erde oder jedes Auto in einem Produktionslauf 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 variabler sind.

Schauen wir uns ein Beispiel an

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

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

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

= DURCHSCHNITT (E2: E13)

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

Wählen Sie im Fenster Funktionsargumente alle Zellen in der Spalte „Werte“ als Eingabe für das Feld „Nummer1“ aus. Sie müssen sich keine Gedanken über das Feld „Nummer2“ machen.

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 greifen Sie über das Menü „Formeln“ darauf zu).

= STDEV.S (E3: E12)

Um über das Menü „Formeln“ auf die Funktion zuzugreifen, wählen Sie die Dropdown-Liste „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 „Nummer1“ aus. Auch hier müssen Sie sich keine Gedanken über das Feld „Nummer2“ machen.

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).

= STDEV.P (E3: E12)

Um über das Menü „Formeln“ auf die Funktion zuzugreifen, wählen Sie die Dropdown-Liste „Weitere Funktionen“, wählen Sie die Option „Statistisch“, 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 „Nummer1“ aus. Auch hier müssen Sie sich keine Gedanken über das Feld „Nummer 2“ machen.

Drücken Sie nun "OK".

Nachdem wir den Mittelwert und die Standardabweichung unserer Daten berechnet haben, haben wir alles, was wir zur Berechnung des Z-Scores benötigen. 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. In diesem Beispiel wird das Ergebnis der Funktion "STDEV.S" verwendet. Sie können jedoch 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 ausführen, um die Formel einzugeben, anstatt sie einzugeben:

  1. Klicken Sie auf Zelle F3 und geben Sie ein =(
  2. Wählen Sie Zelle E3 aus. (Sie können die linke Pfeiltaste einmal drücken oder die Maus verwenden)
  3. Geben Sie das Minuszeichen ein -
  4. Wählen Sie Zelle G3 aus und drücken Sie F4 , um die Zeichen "$" hinzuzufügen, um einen "absoluten" Verweis auf die Zelle zu erstellen (sie durchläuft "G3"> " $ G $ 3"> "G $ 3"> " $ G3"> "G3", wenn Sie weiterhin F4 drücken )
  5. Art )/
  6. Wählen Sie 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. Es liegt 0,15945 Standardabweichungen unter dem Mittelwert. Um die Ergebnisse zu überprü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.

Berechnen wir die Z-Scores der restlichen Werte. 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 aufgrund der "$" -Zeichen immer auf die richtigen "AVERAGE" - und "STDEV.S" - oder "STDEV.P" -Zellen. Wenn Sie Fehler erhalten, gehen Sie zurück und stellen Sie sicher, dass die Zeichen "$" in der von Ihnen 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 insgesamt überspringen, indem Sie stattdessen die folgenden verallgemeinerten Formeln verwenden.

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

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

Und eine mit der Funktion „STEV.P“:

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

Fügen Sie bei der Eingabe der Zellbereiche für die „Werte“ in den Funktionen unbedingt absolute Referenzen („$“ mit F4) hinzu, 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, ist die Verwendung von Hilfszellen möglicherweise effizienter, da das Ergebnis der Funktionen „AVERAGE“ und „STDEV.S“ oder „STDEV.P“ nicht jedes Mal berechnet wird, wodurch Prozessorressourcen und Ressourcen gespart werden Beschleunigung der Zeit für die Berechnung der Ergebnisse.

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 (die 64-Bit-Version unterliegt keinen Einschränkungen hinsichtlich der Verwendung von RAM).