LINEST-Funktion Excel - Lineare Regressionsstatistik

Beispielarbeitsbuch herunterladen

Laden Sie die Beispielarbeitsmappe herunter

Dieses Tutorial zeigt, wie Sie die Excel LINEST-Funktion in Excel, um Statistiken zu einer Trendlinie zu berechnen.

Übersicht über die LINEST-Funktion

Die LINEST-Funktion Berechnet Statistiken über eine Trendlinie, die mit der Methode der kleinsten Quadrate an bekannte Datenpunkte angepasst wurde.

Um die Excel-Arbeitsblattfunktion REST zu verwenden, wählen Sie eine Zelle aus und geben Sie Folgendes ein:

(Beachten Sie, wie die Formeleingaben angezeigt werden)

LINEST-Funktion Syntax und Eingaben

1	=LINEST(bekannt_ys,bekannt_xs,const,Stats)

bekannt_y's - Ein Array bekannter Y-Werte.

bekannt_x's - Ein Array bekannter X-Werte.

const - OPTIONAL. Logischer Wert, der angibt, ob B (der Achsenabschnitt in y = mx + b) nach der Methode der kleinsten Quadrate (TRUE oder Ommitted) berechnet oder B = 0 (FALSE) manuell gesetzt wird.

Statistiken - OPTIONAL. Zusätzliche Statistiken zurückgeben (TRUE) oder nur m (Slope) und b (Intercept) zurückgeben (FALSE oder Omitted)

Was ist LINEST?

Die LINEST-Funktion in Excel ist eine Funktion zum Generieren von Regressionsstatistiken für ein lineares Regressionsmodell. LINEST ist eine Matrixformel und kann allein oder mit anderen Funktionen verwendet werden, um spezifische Statistiken über das Modell zu berechnen.

Die lineare Regression ist eine Methode in der Statistik, die zur Vorhersage von Daten verwendet wird, die einer geraden Linie unter Verwendung bekannter Daten folgen. Regression wird verwendet, um Werte wie Umsatzwachstum, Lagerbedarf oder einfache Markttrends vorherzusagen.

REST ist wie FORECAST insofern, als es ein ähnliches Ergebnis erzielt, jedoch mit weit mehr Informationen über Ihr Regressionsmodell sowie der Option, mehr als eine unabhängige Variable anzupassen.

Angenommen, ich habe eine Datentabelle mit x und ja Werte wo x ist die unabhängige Variable und ja ist die abhängige Variable:

Ich möchte wissen, was die Regressionsgleichung der obigen Daten ist. Verwenden von LINEST:

1	=LINEST(B3:B7,C3:C7,WAHR,FALSCH)

Der y-Achsenabschnittswert entspricht hier 0 in wissenschaftlicher Schreibweise.

Die Geradengleichung ist y= 2x + 0 . Beachten Sie, dass LINEST zurückgibt beide die Steigung und der Schnittpunkt der Linie. Um beide Werte zurückzugeben, muss die Formel als Matrixformel eingegeben werden. Mehr zu Array-Formeln später.

So verwenden Sie LINEST

Die LINEST-Funktion benötigt vier Argumente:

1	=LINEST(bekannte_y, bekannte_x, konstant, Statistik)

Woher,

Streit	Beschreibung
bekannt_y's und bekannt_x's	Ist der x und ja Daten in Ihrer Datentabelle
const	TRUE/FALSE Option, ob der y-Achsenabschnitt auf 0 gezwungen oder normal berechnet werden soll
Statistiken	TRUE/FALSE Option, ob zusätzliche Regressionsstatistiken zurückgegeben werden sollen

In unserem ersten Beispiel wird die Funktion wie folgt geschrieben:

1	=LINEST(B3:B7,C3:C7,WAHR,FALSCH)

Wenn das Statistiken Option auf TRUE gesetzt ist, sieht die Organisation der Regressionsstatistik wie folgt aus:

Sie fragen sich vielleicht, was jede Variable bedeutet.

Statistik	Beschreibung
mn	Steigungskoeffizienten für x Variablen
B	y-Achsenabschnitt
sen	Standardfehler für jeden Steigungskoeffizienten
seB	Standardfehler für den y-Achsenabschnitt
R2	Bestimmtheitsmaß
seja	Standardfehler für die ja schätzen
F	Die F-Statistik (um festzustellen, ob die Beziehung der Variablen zufällig auftritt)
DF	Freiheitsgrade
ssreg	Regressionssumme der Quadrate
sswohne	Restsumme der Quadrate

Die wichtigsten zu verstehenden Statistiken sind die Steigungskoeffizienten, der y-Achsenabschnitt und das Bestimmtheitsmaß oder der r² Wert des Modells.

Verwenden Sie das obige Beispiel und wählen Sie TRUE für die Statistiken Parameter:

Die hervorgehobenen Zellen zeigen die Steigung = 2, den Achsenabschnitt = 0 und r² = 1.

Der r² Wert ist ein Indikator für die Stärke der Korrelation des Modells. Es kann als Indikator für die Passform angesehen werden. Ein niedriger r² Wert würde eine schlechte Korrelation zwischen Ihren abhängigen und unabhängigen Variablen bedeuten, und das Gegenteil gilt für hohe r² Werte, mit r² = 1 passt perfekt.

In Versionen nach Januar 2022 von Excel in Microsoft 365 (früher Office 365) haben dynamische Arrays die Art und Weise geändert, wie Arrayformeln ausgewertet werden. Es ist nicht mehr erforderlich, STRG + UMSCHALT + EINGABETASTE zu verwenden oder den Zellenbereich hervorzuheben, den das Array einnimmt. Geben Sie einfach die Formel ein und klicken Sie auf die Eingabetaste, und die resultierenden Zellen werden in das Array "ausgeschüttet".

Für den Rest dieses Artikels verweisen wir auf die Verwendung von LINEST in Bezug auf dynamische Arrays in Microsoft 365 Excel.

Prognose mit REST (einfache Regression)

Die Kombination der Funktionen RUND und SUMME kann verwendet werden, um den Wert einer abhängigen Variablen vorherzusagen ja, bekannt gegeben x und ja Daten. Unten ist ein Beispiel, das zeigt, was die ja Wert ist, wenn x = 14 ist.

1	=SUMME(LINEST(C3:C7,B3:B7)*{14,1})

Das Modell hat die Form y = mx + b . Dies ist dasselbe wie y = a+ bx, nur eine andere Art, die Gleichung darzustellen. Ein Tipp, den Sie bei linearen Gleichungen beachten sollten, ist die Variable neben x ist immer die Steigung, und die Variable, die einem Plus- oder Minuszeichen folgt, ist immer der Achsenabschnitt, unabhängig von den in der Gleichung verwendeten Buchstaben.

Mit der Formel: =SUM(LINEST(C3:C7,B3:B7)*{14,1}) wird das Ergebnis von 28 zurückgegeben. Da es sich um ein einzelnes Ergebnis handelt, ist die Eingabe als Array nicht erforderlich.

Das Ende der obigen Formel *{14,1} gibt die unabhängige Variable an, die für die Vorhersage der abhängigen Variablen verwendet werden soll, in diesem Fall 14.

Wir können dies überprüfen, indem wir x= 14 in die Geradengleichung eingeben, y = 2x + 0.

Prognosen mit REST (Multiple Linear Regression)

Die folgende Datentabelle stammt von der LINEST-Seite der Microsoft Support-Website.

In einigen Fällen gibt es mehr als eine unabhängige Variable, die bei der Erstellung eines linearen Regressionsmodells berücksichtigt werden sollte. Dies wird als multiple lineare Regression (d. h. mehrere unabhängige Variablen) bezeichnet. Wenn ich die Kosten eines Bürogebäudes abschätzen möchte, würden Dinge wie Grundfläche, Anzahl der Gebäudeeingänge, Alter des Gebäudes und Anzahl der Büros ein Teil der Gleichung sein. Sehen wir uns ein Beispiel an.

Wenn wir die LINEST-Formel in Zelle G29 eingeben und ausführen, erhalten wir:

1	=LINEST(E3:E13,A3:D13,WAHR,WAHR)

Das Modell kommt in der Form:

Denken Sie daran, dass das LINEST-Ergebnis-Array in umgekehrter Reihenfolge zur Gleichung ist. Im obigen Beispiel ist 52,317,8 unser Achsenabschnitt, b und 27,6 ist unser m₁ oder der Steigungswert für die Variable Floor space, x₁.

Unter Verwendung der LINEST-Funktion mit den bereitgestellten Daten lautet unser Regressionsmodell:

Mit einem r² Wert von 0,997, was auf ein starkes oder stark korreliertes Modell hinweist. Mithilfe des Modells können Sie nun vorhersagen, wie hoch der geschätzte Wert eines Bürogebäudes aus einer beliebigen Kombination der oben genannten unabhängigen Variablen sein wird.

REST-Tipps

1. Stellen Sie sicher, dass Sie über die aktuellste Version von Microsoft 365 verfügen, um LINEST mit dynamischen Arrays zu verwenden. Möglicherweise müssen Sie den aktuellen Office Insider-Kanal (Vorschau) aktivieren, um dynamische Array-Funktionen zu verwenden. Auf der Kontoseite:
   
2. Wenn Sie eine Nicht-Microsoft 365-Version verwenden, müssen Sie zum Auswerten von Arrayformeln die alte STRG + UMSCHALT + EINGABETASTE (CSE) verwenden.
3. Wenn Sie die Legacy-Methode verwenden, ist die Anzahl der hervorzuhebenden Spalten bei der Eingabe einer LINEST-Array-Funktion immer die Anzahl von x Variablen in Ihren Daten plus 1. Die Anzahl der für das Array auszuwählenden Zeilen beträgt 5.
4. Wenn Sie Ihre dynamische Array-fähige Version von Excel für jemanden freigeben, der eine nicht von Microsoft 365 stammende Version verwendet, verwenden Sie die Legacy-CSE-Methode, um Kompatibilitätsprobleme zu vermeiden.

Interessiert an weiteren Prognosen?

Lesen Sie unsere anderen Artikel über Prognosen mit exponentiellen Glättungs-, TREND-, GROWTH- und LOGEST-Funktionen.

LINEST Funktion in Google Tabellen

Die LINEST-Funktion funktioniert in Google Sheets genauso wie in Excel.

LINEST-Beispiele in VBA

Sie können auch die LINEST-Funktion in VBA verwenden. Typ:
application.worksheetfunction.linest(known_ys,known_xs,const,stats)

Ausführen der folgenden VBA-Anweisung

1	Range("D2") = Application.WorksheetFunction.LinEst(Range("A2:A8"), Range("B2:B8"))

führt zu folgenden Ergebnissen

Für die Funktionsargumente (known_y’s usw.) können Sie diese entweder direkt in die Funktion eingeben oder stattdessen Variablen definieren.

Zurück zur Liste aller Funktionen in Excel