WorksheetFunction.Linest-Methode (Excel)

Berechnet die Statistik für eine Linie mithilfe der Methode der kleinsten Quadrate, um eine gerade Linie zu berechnen, die am besten zu Ihren Daten passt, und gibt ein Array zurück, das die Linie beschreibt. Da diese Funktion einen Array von Werten zurückgibt, muss sie als Arrayformel eingegeben werden.

Syntax

Ausdruck verwendet werden. LinEst (arg1, arg2, arg3, arg4)

Ausdruck Eine Variable, die ein WorksheetFunction-Objekt darstellt.

Parameter

Rückgabewert

Variant

Bemerkungen

Die Gleichung für die Linie ist y = mx + b oder y = m1x1 + m2x2 + ... + b (wenn mehrere Bereiche von x-Werten vorhanden sind), wobei der abhängige y-Wert eine Funktion der unabhängigen x-Werte ist. 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 known_y in einer einzelnen Spalte befindet, wird jede Spalte von known_x als separate Variable interpretiert.

Wenn sich die Matrix known_y in einer einzelnen Zeile befindet, wird jede Zeile known_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 known_x weggelassen wird, wird davon ausgegangen, dass es sich um das Array {1,2,3,...} handelt, das die gleiche Größe wie das von known_y hat.

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

• Wenn const False ist, wird b auf 0 gesetzt und die m-Werte werden angepasst.y = mx

• Wenn stats True ist, gibt LinEst die zusätzliche Regressionsstatistik zurück, sodass das zurückgegebene Array ist {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.

Sie können jede gerade Linie mit der Steigung und dem y-Schnittpunkt beschreiben: Slope (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-Achsenabschnitt einer Linie, 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. Nachdem 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 auch die Funktion TREND 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(known_y's,known_x's),2)

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

, wobei x und y Stichprobenmittelwerte sind, d. h. x = MITTELWERT(bekannte x's) und y = MITTELWERT(known_y's).

Die Linien- und Kurvenanpassungsfunktionen LinEst und LogEst 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. Sie können für eine gerade Linie oder GROWTH(known_y's, known_x's) für eine exponentielle Kurve berechnenTREND(known_y's,known_x's). 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 die quadrierte Differenz zwischen dem für diesen Punkt geschätzten y-Wert und seinem tatsächlichen y-Wert. Die Summe dieser quadrierten Differenzen wird als Residual-Quadratsumme (ssresid) bezeichnet. Excel berechnet dann die Gesamtsumme der Quadrate, sstotal. Ist Konstante mit TRUE belegt oder nicht angegeben, entspricht die Gesamtsumme der Abweichungsquadrate der Summe der quadratischen Differenzen zwischen den tatsächlichen y-Werten und dem Mittelwert der y-Werte. Wenn Konstante mit FALSE belegt ist, entspricht die Gesamtsumme der Abweichungsquadrate den Quadraten der tatsächlichen y-Werte (ohne Subtraktion der Mittelwerte aller y-Werte von jedem einzelnen y-Wert). Dann kann die Regressionssumme der Quadrate, ssreg, aus gefunden ssreg = sstotal - ssresidwerden. Je kleiner die Restsumme der Quadrate im Vergleich zur Gesamtsumme der Quadrate ist, desto größer ist der Wert des Bestimmungskoeffizienten r2, der ein Indikator dafür ist, wie gut die aus der Regressionsanalyse resultierende Gleichung die Beziehung zwischen den Variablen erklärt; 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 die Einträge in dieser Spalte erhalten werden können, indem der Eintrag in der männlichen Indikatorspalte von dem Eintrag in der zusätzlichen Spalte aller von LinEst 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 von known_x vorhanden sind und const = TRUE oder weggelassen wird, df = n - k - 1. If const = FALSE, 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 von dem zugrunde liegenden Algorithmus, der in den Funktionen "Steigung " und "Schnittpunkt" 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:

• LinEst 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.
• Steigung und Schnittpunkt geben #DIV/0 zurück! zurück. Der Steigungs - und Schnittpunktalgorithmus ist so konzipiert, dass er nach einer einzigen Antwort sucht, und in diesem Fall kann es mehr als eine Antwort geben.

Support und Feedback

Haben Sie Fragen oder Feedback zu Office VBA oder zu dieser Dokumentation? Unter Office VBA-Support und Feedback finden Sie Hilfestellung zu den Möglichkeiten, wie Sie Support erhalten und Feedback abgeben können.