WorksheetFunction.LinEst(Object, Object, Object, Object) Methode

public object LinEst(object Arg1, object Arg2, object Arg3, object Arg4);

Public Function LinEst (Arg1 As Object, Optional Arg2 As Object, Optional Arg3 As Object, Optional Arg4 As Object) As Object

Parameter

Gibt zurück

Hinweise

Die Formel einer solchen Geraden lautet:

y = mx + b oder

y = m1x1 + m2x2 + ... + b (bei mehreren Bereichen mit x-Werten)

Dabei ist der abhängige y-Wert eine Funktion der unabhängigen x-Werte. Die m-Werte sind Koeffizienten, die zu den jeweiligen x-Werten gehören, und b ist eine Konstante. Es ist zu beachten, dass y, x und m Vektoren sein können. Das Array, das LinEst zurückgibt, ist {mn,mn-1,...,m1,b}. LinEst kann auch zusätzliche Regressionsstatistiken zurückgeben.

Wenn sich das Array Y_Werte in einer einzelnen Spalte befindet, wird jede Spalte mit X_Werte als separate Variable interpretiert.

Wenn sich das Array Bekannt_y in einer einzelnen Zeile befindet, wird jede Zeile mit Bekannt_x als separate Variable interpretiert.

Das Array Bekannt_x kann eine oder mehrere Gruppen mit Variablen enthalten. Wenn nur eine Variable verwendet wird, können Bekannt_y und Bekannt_x Bereiche beliebiger Form darstellen, solange sie gleiche Abmessungen aufweisen. Wenn mehrere Variablen verwendet werden, muss Bekannt_y einen Vektor darstellen (d. h. einen Bereich mit einer Höhe von einer Zeile oder einer Breite von einer Spalte).

Wenn Bekannt_x ausgelassen wird, wird angenommen, dass dies dem Array {1,2,3,...} entspricht, das dieselbe Größe wie Bekannt_y aufweist.

Wenn const wahr ist oder weggelassen wird, wird b normal berechnet.

Wenn const falsch ist, wird b gleich 0 gesetzt und die m-Werte werden an y = mx angepasst.

If stats is truuue, LinEst gibt die zusätzliche Regressionsstatistik zurück, sodass das zurückgegebene Array {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; R2,Sey; F, df; ssreg,ssresid}.

Wenn stats false ist oder weggelassen wird, gibt LinEst nur die m-Koeffizienten und die Konstante b zurück.

Die folgenden Regressionskenngrößen können zusätzlich ermittelt werden:

Die folgende Abbildung zeigt, in welcher Reihenfolge die zusätzlichen Regressionskenngrößen zurückgegeben werden.

Abbildung 1: Arbeitsblatt

Sie können jede gerade Linie mit der Steigung und dem y-Achsenabschnitt beschreiben:

Steigung (m):

Um die Steigung einer Linie zu finden, die oft als m geschrieben wird, nehmen Sie zwei Punkte auf der Geraden, (x1,y1) und (x2,y2); Die Steigung ist gleich (y2 - y1)/(x2 - x1).

Y-Achsenabschnitt (b):

Der y-Schnittpunkt einer Geraden, oft als b geschrieben, ist der Wert von y an dem Punkt, an dem die Linie die y-Achse kreuzt.

Die Gleichung einer Geraden lautet y = mx + b. Sobald Sie die Werte von m und b kennen, können Sie jeden Punkt auf der Geraden berechnen, indem Sie den y- oder x-Wert in diese Gleichung einfügen. Sie können die Trend(Object, Object, Object, Object) Funktion auch verwenden.

Wenn Sie nur über eine unabhängige x-Variable verfügen, können Sie die Werte für die Steigung und den y-Schnittpunkt direkt mithilfe der folgenden Formeln abrufen:

Steigung:

=INDEX(LINEST(known_y's;known_x's);1)

Y-Achsenabschnitt:

=INDEX(LINEST(Y_Werte,X_Werte),2)

Die Genauigkeit der von LineEst berechneten Linie hängt vom Grad der Streuung in Ihren Daten ab. Je linearer die Daten, desto genauer das LineEst-Modell . LineEst verwendet die Methode der kleinsten Quadrate, um die beste Anpassung für die Daten zu ermitteln. Wenn nur eine unabhängige x-Variable vorliegt, werden m und b entsprechend der folgenden Formeln berechnet:

Abbildung 2: Gleichung

Abbildung 3: Gleichung

, wobei x und y beispielhafte Mittelwerte sind, z. B. x = AVERAGE(X_Werte) und y = AVERAGE(Y_Werte).

Die Linien- und Kurvenanpassungsfunktionen LineEst und LogEst(Object, Object, Object, Object) kann die beste gerade Linie oder exponentielle Kurve berechnen, die zu Ihren Daten passt. Sie müssen dennoch entscheiden, welches der beiden Ergebnisse Ihren Daten eher entspricht. Bei einer Geraden können Sie TREND(Y_Werte, X_Werte) und bei einer Exponentialkurve GROWTH(Y_Werte, X_Werte) berechnen. Werden diese Funktionen ohne das Argument Neue_X_Werte verwendet, geben sie ein Array mit y-Werten zurück, die an den x-Werten Ihrer tatsächlichen Datenpunkte als Vorhersagewerte auf der Geraden oder Exponentialkurve liegen. Diese Vorhersagewerte können Sie mit den tatsächlichen Werten vergleichen. Um eine bessere Vergleichsmöglichkeit zu haben, kann es sinnvoll sein, die Werte in Diagrammen darzustellen.

Bei der Regressionsanalyse berechnet Microsoft Excel für jeden Punkt das Quadrat der Differenz zwischen dem für diesen Punkt erwarteten y-Wert und dem entsprechenden tatsächlichen y-Wert. Die Summe dieser quadrierten Differenzen wird als Residual-Quadratsumme (ssresid) bezeichnet. Anschließend berechnet Microsoft Excel die Gesamtsumme der Abweichungsquadrate (sstotal). Wenn const = wahr ist oder weggelassen wird, ist die Gesamtsumme der Quadrate die Summe der quadrierten Differenzen zwischen den tatsächlichen y-Werten und dem Durchschnitt der y-Werte. Wenn const = falsch ist, ist die Gesamtsumme der Quadrate die Summe der Quadrate der tatsächlichen y-Werte (ohne den durchschnittlichen y-Wert von jedem einzelnen y-Wert abzuziehen). Anschließend kann die Regressions-Quadratsumme (ssreg) anhand der folgenden Formel berechnet werden: ssreg = sstotal - ssresid. Je kleiner die Residual-Quadratsumme im Vergleich zur Gesamtsumme der Abweichungsquadrate ist, desto größer ist der Wert des Bestimmtheitsmaßes (r2), das angibt, wie gut die aus der Regressionsanalyse resultierende Formel die zwischen den Variablen bestehende Beziehung beschreibt. r2 ist gleich ssreg/sstotal.

In einigen Fällen haben eine oder mehrere der X-Spalten (unter der Annahme, dass sich Y und X in Spalten befinden) möglicherweise keinen zusätzlichen Vorhersagewert, wenn die anderen X-Spalten vorhanden sind. Mit anderen Worten: Das Entfernen einer oder mehrerer X-Spalten kann zu gleich genauen vorhergesagten Y-Werten führen. In diesem Fall sollten diese redundanten X-Spalten aus dem Regressionsmodell weggelassen werden. Dieses Phänomen wird als "Kollinearität" bezeichnet, da jede redundante X-Spalte als Summe von Vielfachen der nicht redundanten X-Spalten ausgedrückt werden kann. LinEst überprüft die Kollinearität und entfernt alle redundanten X-Spalten aus dem Regressionsmodell, wenn sie identifiziert werden. Entfernte X-Spalten können in der LinEst-Ausgabe sowohl als Koeffizienten als auch mit 0 Se's erkannt werden. Wenn eine oder mehrere Spalten als redundant entfernt werden, ist df betroffen, da df von der Anzahl der X-Spalten abhängt, die tatsächlich für Vorhersagezwecke verwendet werden. Wenn df geändert wird, weil redundante X-Spalten entfernt werden, sind auch die Werte von sey und F betroffen. Kollinearität sollte in der Praxis relativ selten sein. Ein Fall, in dem es jedoch wahrscheinlicher ist, dass es auftritt, ist, wenn einige X-Spalten nur 0 und 1 als Indikatoren dafür enthalten, ob ein Subjekt in einem Experiment Mitglied einer bestimmten Gruppe ist oder nicht. Wenn const = true ist oder weggelassen wird, fügt LinEst effektiv eine zusätzliche X-Spalte aller 1en ein, um den Schnittpunkt zu modellieren. Wenn Sie eine Spalte mit einer 1 für jedes Subjekt haben, wenn männlich, oder 0, wenn nicht, und Sie auch eine Spalte mit einer 1 für jedes Subjekt haben, wenn weiblich, oder 0, wenn nicht, ist diese letztere Spalte redundant, da Einträge in ihr erhalten werden können, indem der Eintrag in der Spalte "männlicher Indikator" von dem Eintrag in der zusätzlichen Spalte aller von LineEst hinzugefügten 1en subtrahiert wird.

df wird wie folgt berechnet, wenn aufgrund von Kollinearität keine X-Spalten aus dem Modell entfernt werden: Wenn k Spalten known_x vorhanden sind und const = wahr oder weggelassen wird, dann ist df = n – k – 1. Wenn const = false, dann ist df = n - k. In beiden Fällen erhöht jede X-Spalte, die aufgrund der Kollinearität entfernt wird, df um 1.

Formeln, die Arrays zurückgeben, müssen als Arrayformeln eingegeben werden. When entering an array constant such as known_x's as an argument, use commas to separate values in the same row and semicolons to separate rows. Die Trennzeichen können je nach Ihrer Ländereinstellung in den Regions- und Sprachoptionen in der Systemsteuerung unterschiedlich sein.

Beachten Sie, dass mithilfe einer Regressionsformel vorhergesagte y-Werte möglicherweise ungültig sind, wenn diese außerhalb des Bereichs der y-Werte liegen, die Sie zur Ermittlung der Formel verwendet haben. Der der LinEst-Funktion zugrunde liegende Algorithmus unterscheidet sich vom zugrunde liegenden Algorithmus, der in den Slope(Object, Object) UND-Funktionen Intercept(Object, Object) verwendet wird. Bei unbestimmten und kollinearen Daten kann der Unterschied zwischen diesen Algorithmen zu unterschiedlichen Ergebnissen führen. Wenn beispielsweise die Datenpunkte in Y_Werte den Wert 0 und die Datenpunkte in X_Werte den Wert 1 aufweisen, geschieht Folgendes:

"LineEst " gibt den Wert "0" zurück. Der LinEst-Algorithmus ist so konzipiert, dass er vernünftige Ergebnisse für kollineare Daten zurückgibt, und in diesem Fall kann mindestens eine Antwort gefunden werden. Slope(Object, Object) und Intercept(Object, Object) eine #DIV/0 zurückgeben! zurück. Der Slope(Object, Object) UND-Algorithmus Intercept(Object, Object) ist so konzipiert, dass er nach nur einer Antwort sucht. In diesem Fall kann es mehrere Antworten geben.