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

Definition

Namespace:

Microsoft.Office.Interop.Excel

Assembly:

Microsoft.Office.Interop.Excel.dll

Berechnet die Parameter eines linearen Trends unter Verwendung der Methode der kleinsten Quadrate, um eine passende Gerade zu berechnen und einen Array zurückzugeben, der die Gerade beschreibt. Da diese Funktion einen Array von Werten zurückgibt, muss sie als Arrayformel eingegeben werden.

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

Arg1

Object

Y_Werte - die bereits bekannten y-Werte in der Beziehung y = mx + b.

Arg2

Object

X_Werte - eine optionale Menge mit möglicherweise bereits bekannten x-Werten in der Beziehung y = mx + b.

Arg3

Object

Konstante - ein Wahrheitswert, mit dem angegeben wird, ob erzwungen werden soll, dass die Konstante b gleich 0 sein soll.

Arg4

Object

Stats - ein Wahrheitswert, der angibt, ob weitere Regressionskenngrößen zurückgegeben werden sollen.

Gibt zurück

Object

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 Von LinEst zurückgegebene Array 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 true ist oder ausgelassen wird, wird b normal berechnet.

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

Wenn stats truuue ist, gibt LinEst die zusätzlichen Regressionsstatistiken 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 ausgelassen wird, gibt LinEst nur die m-Koeffizienten und die Konstante b zurück.

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

se1,se2,...,sen	Sind die Standardfehlerwerte der Koeffizienten m1;m2;...;mn.
seb	Der Standardfehlerwert für die Konstante b (seb = #N/A, wenn const false ist).
R2	Der Bestimmungskoeffizient. Vergleicht geschätzte und tatsächliche y-Werte und Wertebereiche von 0 bis 1. Wenn es 1 ist, besteht eine perfekte Korrelation in der Stichprobe – es gibt keinen Unterschied zwischen dem geschätzten y-Wert und dem tatsächlichen y-Wert. Wenn der Bestimmungskoeffizient 0 ist, ist die Regressionsgleichung bei der Vorhersage eines y-Werts nicht hilfreich.
sey	Der Standardfehler des Schätzwerts y (Prognosewert).
F	Die F-Statistik (oder der berechnete F-Wert). Anhand der F-Statistik können Sie entscheiden, ob die zwischen der abhängigen und der unabhängigen Variablen beobachtete Beziehung zufällig ist oder nicht.
df	Die Anzahl der Freiheitsgrade. Mit den Freiheitsgraden können Sie den jeweiligen kritischen F-Wert (Quantil F) aus einer entsprechenden statistischen Tabelle entnehmen. Vergleichen Sie die in der Tabelle gefundenen Werte mit der von LinEst zurückgegebenen F-Statistik, um ein Konfidenzniveau für das Modell zu ermitteln.
ssreg	Die Regressionssumme der Quadrate.
ssresid	Die Residualsumme der Quadrate.

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-Intercept beschreiben:

Steigung (m):

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

Y-Intercept (b):

Der y-Abschnitt einer Linie, die häufig als b geschrieben wird, ist der Wert von y an dem Punkt, an dem die Linie die y-Achse kreuzt.

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

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

Steigung:

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

Y-Intercept:

=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 sind, desto genauer ist das LineEst-Modell . LineEst verwendet die Methode der geringsten Quadrate, um die beste Eignung für die Daten zu bestimmen. Wenn nur eine unabhängige x-Variable vorliegt, werden m und b entsprechend der folgenden Formeln berechnet:

Abbildung 2: Formel

Abbildung 3: Formel

, 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) können 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 = true oder ausgelassen wird, ist die Gesamtsumme der Quadrate die Summe der quadratischen Unterschiede zwischen den tatsächlichen y-Werten und dem Mittelwert der y-Werte. Bei const = false ist die Gesamtsumme der Quadrate die Summe der Quadrate der tatsächlichen y-Werte (ohne den durchschnittlichen y-Wert von jedem einzelnen y-Wert zu subtrahieren). 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 kann eine oder mehrere der X-Spalten (davon ausgehen, dass Y- und X-Spalten in Spalten enthalten sind) möglicherweise keinen zusätzlichen Vorhersagewert im Vorhandensein der anderen X-Spalten aufweisen. Anders ausgedrückt: Das Entfernen einer oder mehrerer X-Spalten kann zu vorhergesagten Y-Werten führen, die genauso genau sind. 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 auf Kollinearität und entfernt alle redundanten X-Spalten aus dem Regressionsmodell, wenn sie identifiziert werden. Entfernte X-Spalten können in der LinEst-Ausgabe als Koeffizienten von 0 und 0 se 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 sey und F betroffen. Kollinearität sollte in der Praxis relativ selten sein. Ein Fall, in dem die Wahrscheinlichkeit höher ist, ist jedoch, dass 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 oder ausgelassen wird, fügt LinEst effektiv eine zusätzliche X-Spalte aller 1 ein, um den Intercept zu modellieren. Wenn Sie über eine Spalte mit einer 1 für jedes Thema verfügen, wenn sie männlich ist, oder 0, wenn nicht, und Wenn nicht, haben Sie auch eine Spalte mit einer 1 für jeden Betreff, wenn weiblich, oder 0, wenn nicht, ist diese zweite Spalte redundant, da die Einträge darin aus dem Subtrahieren des Eintrags in der Spalte "männlicher Indikator" vom Eintrag in der zusätzlichen Spalte aller durch LineEst hinzugefügten 1 erhalten werden können.

df wird wie folgt berechnet, wenn keine X-Spalten aufgrund von Kollinearität aus dem Modell entfernt werden: Wenn es k Spalten mit known_x und const = true oder ausgelassen gibt, dann df = n – k – 1. Wenn const = false, dann df = n - k. In beiden Fällen erhöht sich jede X-Spalte, die aufgrund von Kollinearität entfernt wurde, 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. Trennzeichen können je nach Gebietsschemaeinstellung in den Regional- und Sprachoptionen in 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 zugrunde liegende Algorithmus, der in der LinEst-Funktion verwendet wird, unterscheidet sich von dem zugrunde liegenden Algorithmus, der in den Slope(Object, Object) Funktionen und 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) geben einen #DIV/0 zurück! zurück. Der Slope(Object, Object) Algorithmus und Intercept(Object, Object) ist so konzipiert, dass nach einer einzigen Antwort gesucht wird. In diesem Fall kann es mehrere Antworten geben.