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 MITTELWERT und STABW.S oder STABW.P verwenden, um den Mittelwert und die Standardabweichung Ihrer Daten zu berechnen und dann diese Ergebnisse verwenden, um den Z-Score jedes Wertes zu bestimmen.
Inhaltsverzeichnis
Was ist ein Z-Score und was machen die Funktionen AVERAGE, STABW.S und STABW.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, die vom Mittelwert eines Datenpunkts entfernt sind. Die allgemeine Formel sieht so 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 in der Abschlussprüfung 95 % und der Schüler in der anderen Klasse 87 % erreicht hat.
Auf den ersten Blick ist die 95%-Note beeindruckender, aber was wäre, wenn der Lehrer der zweiten Klasse eine schwierigere Prüfung ablegte? Sie können den Z-Score 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-Scores der beiden Schüler konnte zeigen, dass der Schüler mit der 87%-Punktzahl im Vergleich zum Rest ihrer Klasse besser abschneidet als der Schüler mit der 98%-Punktzahl im Vergleich zum Rest ihrer Klasse.
Der erste statistische Wert, den Sie benötigen, ist der ‚Mittelwert‘ und die Funktion „MITTELWERT“ 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 Excel-Versionen verfügten nur über die Funktion „STDEV“, die die Standardabweichung berechnet, während die Daten als „Stichprobe“ einer Grundgesamtheit behandelt werden. Excel 2010 hat das in zwei Funktionen unterteilt, die die Standardabweichung berechnen:
STABW.S: Diese Funktion ist identisch mit der vorherigen „STABW“-Funktion. Es berechnet die Standardabweichung, während die Daten als „Stichprobe“ einer Grundgesamtheit behandelt werden. Eine Stichprobe einer Population könnte etwa die für ein Forschungsprojekt gesammelten Mücken oder Autos sein, die beiseite gelegt und für Crash-Sicherheitstests verwendet wurden.
STABW.P: Diese Funktion berechnet die Standardabweichung, während die Daten als 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 „STABW.P“ ist immer kleiner als das Ergebnis der Funktion „STABW.S“ für denselben Datensatz. Es ist ein konservativerer Ansatz, anzunehmen, dass die Daten eine größere Variabilität aufweisen.
Schauen wir uns ein Beispiel an
In unserem Beispiel haben wir zwei Spalten („Values“ 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 Zufallszahlen, die um 500 herum zentriert sind, und in der Spalte „Z-Score“ berechnen wir den Z-Score anhand der in den „Helfer“-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 Funktion „MITTELWERT“ speichern möchten.
Geben Sie die folgende Formel ein und drücken Sie die Eingabetaste – oder verwenden Sie das Menü „Formeln“.
=AVERAGE(E2:E13)
Um über das Menü „Formeln“ auf die Funktion zuzugreifen, wählen Sie das Dropdown-Menü „Weitere Funktionen“, wählen Sie die Option „Statistisch“ und klicken Sie dann auf „Durchschnitt“.
Wählen Sie im Fenster Funktionsargumente alle Zellen in der Spalte „Werte“ als Eingabe für das Feld „Zahl1“. 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 „STABW.S“ oder „STABW.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 „STABW.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 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“. Auch um das Feld „Nummer2“ brauchen Sie sich hier keine Gedanken zu machen.
Drücken Sie nun „OK“.
Als nächstes berechnen wir die Standardabweichung mit der Funktion „STABW.P“. Wählen Sie die Zelle aus, in der das Ergebnis gespeichert werden soll.
Um die Standardabweichung mit der Funktion „STABW.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 über das Menü „Formeln“ auf die Funktion 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 die Formel „STABW.P“.
Wählen Sie im Fenster Funktionsargumente alle Zellen in der Spalte „Werte“ als Eingabe für das Feld „Zahl1“. Auch hier müssen Sie sich nicht um das Feld „Nummer2“ 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“. Für dieses Beispiel verwenden wir das Ergebnis der Funktion „STABW.S“, Sie können aber auch das Ergebnis von „STABW.P“ verwenden.
Geben Sie die folgende Formel ein und drücken Sie die Eingabetaste:
=(E3-$G$3)/$H$3
Alternativ könnten Sie die folgenden Schritte verwenden, um die Formel einzugeben, anstatt sie einzugeben:
Klicken Sie auf Zelle F3 und geben Sie =(
Wählen Sie Zelle E3 aus. (Sie können die linke Pfeiltaste einmal drücken oder die Maus verwenden)
Geben Sie das Minuszeichen ein –
Wählen Sie Zelle G3 aus und drücken Sie dann F4, um die „$“-Zeichen hinzuzufügen, um einen „absoluten“ Verweis auf die Zelle zu erstellen (es wird durch „G3“ > „$G$3″ > „G$3″ > „$G3″ > „G3 .“ durchlaufen ” wenn Sie weiterhin F4) drücken
Typ )/
Wählen Sie Zelle H3 (oder I3, wenn Sie „STDEV.P“ verwenden) und drücken Sie F4, um die beiden „$“-Zeichen hinzuzufügen.
Drücken Sie Enter
Für den ersten Wert wurde der Z-Score berechnet. Sie 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, der Wert macht also Sinn.
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 oberen Zelle nach unten durch alle anderen ausgewählten Zellen kopiert wird.
Jetzt wurde die Formel für alle Zellen „aufgefüllt“, und jede wird aufgrund der „$“-Zeichen immer auf die richtigen Zellen „AVERAGE“ und „STABW.S“ oder „STABW.P“ verweisen. Wenn Sie Fehler erhalten, gehen Sie zurück und stellen Sie sicher, dass die Zeichen „$“ in der eingegebenen Formel enthalten sind.
Berechnen des Z-Scores ohne Verwendung von ‚Helper‘-Zellen
Helferzellen 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 einer, der die Funktion „STDEV.S“ verwendet:
=(Value-AVERAGE(Values))/STDEV.S(Values)
Und einer mit der Funktion „STEV.P“:
=(Value-AVERAGE(Values))/STDEV.P(Values)
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 „STABW.S“ oder „STABW.P“ nicht jedes Mal berechnet wird, was Prozessorressourcen spart und beschleunigt 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 standardmäßige 32-Bit-Version von Excel auf 2 GB RAM beschränkt ist (die 64-Bit-Version hat keine Einschränkungen hinsichtlich der RAM-Nutzung).