In der statistischen Analyse wird der Abweichung zwischen Mitgliedern eines Datensatzes zeigt, wie weit die Datenpunkte von einer Trendlinie entfernt sind, die auch als a bezeichnet wird Regressionsgerade. Je höher die Varianz, desto stärker sind die Datenpunkte verteilt. Die Untersuchung der Varianzanalyse zeigt, welche Teile der Varianz durch Eigenschaften der Daten erklärt werden können und welche zufälligen Faktoren zugeordnet werden können. Der Teil der Varianz, der nicht erklärt werden kann, wird als Restvarianz bezeichnet.
Excel-Kalkulationstabellen zur Berechnung der Restabweichung verwenden
Die Formel zur Berechnung der Residualvarianz umfasst zahlreiche komplexe Berechnungen. Bei kleinen Datensätzen kann die manuelle Berechnung der Restvarianz mühsam sein. Bei großen Datensätzen kann die Aufgabe sehr anstrengend sein. Bei Verwendung einer Excel-Tabelle müssen Sie nur die Datenpunkte eingeben und die richtige Formel auswählen. Das Programm wickelt die komplexen Berechnungen ab und liefert schnell ein Ergebnis.
Datenpunkte
Öffnen Sie eine neue Excel-Tabelle und geben Sie die Datenpunkte in zwei Spalten ein. Regressionszeilen erfordern, dass jeder Datenpunkt zwei Elemente hat. Statistiker bezeichnen diese Elemente typischerweise mit "X" und "Y". Beispielsweise möchte die Generic Insurance Co. die Restvarianz der Größe und des Gewichts ihrer Mitarbeiter ermitteln. Die Variable X steht für die Höhe und die Variable Y für das Gewicht. Geben Sie die Höhen in Spalte A und die Gewichte in Spalte B ein.
Den Mittelwert finden
Das bedeuten repräsentiert den Durchschnitt für jedes Element im Datensatz. In diesem Beispiel möchte die generische Versicherung den Durchschnitt, die Standardabweichung und die Kovarianz der Höhen und Gewichte von 10 Mitarbeitern ermitteln. Der Durchschnitt der in Spalte A aufgeführten Höhen kann durch Eingabe der Funktion "= MITTELWERT (A1: A10)" in Zelle F1 ermittelt werden. Der Durchschnitt der in Spalte B aufgeführten Gewichte kann durch Eingabe der Funktion "= DURCHSCHNITT (B1: B10)" in Zelle F3 ermittelt werden.
Ermittlung der Standardabweichung und Kovarianz
Das Standardabweichung misst, wie weit die Datenpunkte vom Mittelwert entfernt sind. Das Kovarianz misst, wie stark sich die beiden Elemente des Datenpunkts zusammen ändern. Die Standardabweichung der Höhen wird durch Eingabe der Funktion "= STDEV (A1: A10)" in Zelle F2 ermittelt. Die Standardabweichung der Gewichte wird durch Eingabe der Funktion "= STDEV (B1: B10)" in Zelle F4 ermittelt. Die Kovarianz zwischen den Höhen und Gewichten wird durch Eingabe der Funktion "= COVAR (A1: A10; B1: B10)" in Zelle F5 ermittelt.
Die Regressionslinie finden
Das Regressionsgerade repräsentiert eine lineare Funktion, die dem Trend der Datenpunkte folgt. Die Formel für die Regressionslinie sieht folgendermaßen aus: Y = aX + b.
Der Benutzer kann die Werte für "a" und "b" anhand der Berechnungen für Mittelwerte, Standardabweichungen und Kovarianz ermitteln. Der Wert für "b" steht für den Punkt, an dem die Regressionslinie die Y-Achse abfängt. Der Wert kann ermittelt werden, indem die Kovarianz durch das Quadrat der Standardabweichung der X-Werte dividiert wird. Die Excel-Formel geht in Zelle F6 und sieht folgendermaßen aus: = F5 / F2 ^ 2.
Der Wert für "a" steht für die Steigung der Regressionsgerade. Die Excel-Formel geht in Zelle F7 und sieht folgendermaßen aus: = F3-F6 * F1.
Um die Formel für die Regressionszeile anzuzeigen, geben Sie diese Zeichenfolgenverkettung in Zelle F8 ein:
= CONCATENATE ("Y ="; ROUND (F6; 2); "X"; IF (SIGN (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Y-Werte berechnen
Der nächste Schritt umfasst das Berechnen der Y-Werte auf der Regressionslinie für die angegebenen X-Werte im Datensatz. Die Formel zur Ermittlung der Y-Werte geht in Spalte C und sieht folgendermaßen aus:
= $ F $ 6 * A (i) + $ F $ 7
Dabei ist A (i) der Wert für Spalte A in Zeile (i). Die Formeln sehen in der Tabelle folgendermaßen aus:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7 und so weiter
Die Einträge in Spalte D zeigen die Unterschiede zwischen den erwarteten und tatsächlichen Werten für Y. Die Formeln sehen folgendermaßen aus:
= B (i) -C (i), Dabei sind B (i) und C (i) die Werte in der Zeile (i) in den Spalten B und C.
Ermittlung der Restvarianz
Das Formel für die Restvarianz geht in Zelle F9 und sieht so aus:
= SUMSQ (D1: D10) / (ZÄHLUNG (D1: D10) -2)
Dabei ist SUMSQ (D1: D10) die Summe der Quadrate der Differenzen zwischen den tatsächlichen und erwarteten Y-Werten und (COUNT (D1: D10) -2) die Anzahl der Datenpunkte, minus 2 für die Freiheitsgrade Daten.
