In diesem Tutorial wird die Syntax der Linest -Funktion erl?utert und zeigt, wie sie in Excel eine lineare Regressionsanalyse durchführen.

Microsoft Excel ist kein statistisches Programm, hat jedoch eine Reihe statistischer Funktionen. Eine dieser Funktionen ist Linest, die für die Durchführung einer linearen Regressionsanalyse und der Rückgabestatistik ausgelegt ist. In diesem Tutorial für Anf?nger werden wir nur leicht über die Theorie und die zugrunde liegenden Berechnungen berühren. Unser Hauptaugenmerk liegt auf der Bereitstellung einer Formel, die einfach funktioniert und einfach für Ihre Daten angepasst werden kann.

Excel Linest -Funktion - Syntax und Basic -Verwendungen

Die Linest -Funktion berechnet die Statistik für eine gerade Linie, die die Beziehung zwischen der unabh?ngigen Variablen und einer oder mehreren abh?ngigen Variablen erkl?rt, und gibt ein Array zurück, das die Linie beschreibt. Die Funktion verwendet die Methode mit der kleinsten Quadrate , um die beste Passform für Ihre Daten zu finden. Die Gleichung für die Linie lautet wie folgt.

Einfache lineare Regressionsgleichung:

Multiple Regressionsgleichung:

Wo:

• y - Die abh?ngige Variable, die Sie vorhersagen m?chten.
• X - Die unabh?ngige Variable, die Sie verwenden, um y vorherzusagen.
• A - Der Abschnitt (gibt an, wo die Linie die y -Achse schneidet).
• B - Die Steigung (zeigt die Steilheit der Regressionslinie an, dh die ?nderungsrate für y als x ?ndert sich).

In ihrer Grundform gibt die Linest -Funktion den Abfang (a) und die Steigung (b) für die Regressionsgleichung zurück. Optional kann es auch zus?tzliche Statistiken für die Regressionsanalyse zurückgeben, wie in diesem Beispiel gezeigt.

Linest -Funktionssyntax

Die Syntax der Excel Linest -Funktion lautet wie folgt:

Wo:

• Bekanntes (erforderlich) ist ein Bereich der abh?ngigen y -Werte in der Regressionsgleichung. Normalerweise ist es eine einzelne Spalte oder eine einzelne Zeile.
• Bekannte_x (optional) ist ein Bereich der unabh?ngigen X-Werte. Wenn es weggelassen wird, wird angenommen, dass es sich um das Array {1,2,3, ...} der gleichen Gr??e wie bekannt .
• const (optional) - Ein logischer Wert, der bestimmt, wie der Abfang (Konstante a ) behandelt werden sollte:
  • Wenn wahr oder weggelassen, wird die Konstante A normal berechnet.
  • Wenn falsch, wird die Konstante A auf 0 gezwungen und die Steigung ( B -Koeffizient) wird berechnet, um y = bx anzupassen.
• Statistiken (optional) ist ein logischer Wert, der feststellt, ob zus?tzliche Statistiken ausgegeben werden sollen oder nicht:
  • Wenn wahr, gibt die Linest -Funktion ein Array mit zus?tzlichen Regressionsstatistiken zurück.
  • Wenn falsch oder weggelassen, gibt Linest nur die Abfangkonstante und den Steigungskoeffizienten (en) zurück.

Notiz. Da Linest ein Array von Werten zurückgibt, muss es als Array -Formel eingegeben werden, indem die T -T -Verknüpfung der STRL -Schicht eingegeben wird . Wenn es als regul?re Formel eingegeben wird, wird nur der erste Steigungskoeffizient zurückgegeben.

Zus?tzliche Statistiken, die von Linest zurückgegeben wurden

Das auf True festgelegte Statistikargument weist die Linest -Funktion an, die folgenden Statistiken für Ihre Regressionsanalyse zurückzugeben:

Die folgende Karte zeigt die Reihenfolge, in der Linest eine Reihe von Statistiken zurückgibt:

In den letzten drei Zeilen werden die Fehler #N/A in den dritten und nachfolgenden Spalten angezeigt, die nicht mit Daten gefüllt sind. Es ist das Standardverhalten der Linest -Funktion, aber wenn Sie die Fehlernotationen ausblenden m?chten, wickeln Sie Ihre Linest -Formel wie in diesem Beispiel gezeigt in das ISTERROR.

So verwenden Sie Linest in Excel - Formel Beispiele

Die Linest -Funktion ist m?glicherweise schwierig zu verwenden, insbesondere für Anf?nger, da Sie nicht nur eine Formel korrekt erstellen, sondern auch ihre Ausgabe ordnungsgem?? interpretieren sollten. Im Folgenden finden Sie einige Beispiele für die Verwendung von Linest -Formeln in Excel, die hoffentlich dazu beitragen, das theoretische Wissen einzuversenken :)

Einfache lineare Regression: Berechnung von Steigung und Abschnitt berechnen

Um den Intercept und die Steigung einer Regressionslinie zu erhalten, verwenden Sie die Linest -Funktion in ihrer einfachsten Form: Liefern Sie einen Bereich der abh?ngigen Werte für das Argument des bekannten_Y und einen Bereich der unabh?ngigen Werte für das Argument des Bekannten_x . Die letzten beiden Argumente k?nnen auf wahr oder weggelassen werden.

Zum Beispiel ist unsere lineare Regressionsformel so einfach wie bei Y -Werten (Verkaufszahlen) in C2: C13- und X -Werten (Werbekosten) in B2: B13:

=LINEST(C2:C13,B2:B13)

Um es in Ihrem Arbeitsblatt korrekt einzugeben, w?hlen Sie in diesem Beispiel zwei benachbarte Zellen in derselben Zeile, E2: F2, die Formel ein und drücken Sie die Strg -Eingabetaste, um sie zu vervollst?ndigen.

Die Formel gibt den Steigungskoeffizienten in der ersten Zelle (E2) und die Intercept -Konstante in der zweiten Zelle (F2) zurück:

Die Steigung betr?gt ungef?hr 0,52 (abgerundet auf zwei Dezimalstellen). Dies bedeutet, dass y , wenn x um 1 zunimmt, um 0,52 zunimmt.

Der y -Schnittpunkt ist negativ -4,99. Es ist der erwartete Wert von y , wenn x = 0. Wenn es in einem Diagramm aufgetragen wird, ist es der Wert, bei dem die Regressionslinie die y-Achse überschreitet.

Geben Sie die oben genannten Werte einer einfachen linearen Regressionsgleichung an, und Sie erhalten die folgende Formel, um die Verkaufsnummer basierend auf den Werbekosten vorherzusagen:

y = 0.52*x - 4.99

Wenn Sie beispielsweise 50 US -Dollar für Werbung ausgeben, werden Sie voraussichtlich 21 Regenschirme verkaufen:

0.52*50 - 4.99 = 21.01

Die Steigungs- und Intercept -Werte k?nnen auch separat unter Verwendung der entsprechenden Funktion oder durch Verschlucken der Linestformel in den Index erhalten werden:

Neigung

=SLOPE(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),1)

Abfangen

=INTERCEPT(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),2)

Wie im folgenden Screenshot gezeigt, liefern alle drei Formeln die gleichen Ergebnisse:

Multiple lineare Regression: Steigung und Abschnitt

Wenn Sie zwei oder mehr unabh?ngige Variablen haben, geben Sie sie unbedingt in benachbarte Spalten ein und liefern Sie diesen gesamten Bereich dem Argument des bekannten_x .

Beispielsweise verwenden Sie mit Verkaufszahlen ( Y -Werten) in D2: D13 Werbebereich (ein Satz von X -Werten) in B2: B13 und durchschnittlicher monatlicher Niederschlag (ein weiterer Satz von X -Werten) in C2: C13, diese Formel:

=LINEST(D2:D13,B2:C13)

Da die Formel ein Array von 3 Werten (2 Steigungskoeffizienten und die Abfangkonstante) zurückgeben wird, w?hlen wir drei zusammenh?ngende Zellen in derselben Zeile aus, geben die Formel ein und drücken Sie die Strg -Verschiebung Eingabetaste.

Bitte beachten Sie, dass die multiple Regressionsformel die Steigungskoeffizienten in umgekehrter Reihenfolge der unabh?ngigen Variablen (von rechts nach links) zurückgibt, dh B _N , B _N-1 ,…, B ₂ , B ₁ :

Um die Verkaufsnummer vorherzusagen, liefern wir die von der Linest -Formel zurückgegebenen Werte für die multiple Regressionsgleichung:

y = 0,3*x ₂ 0,19*x ₁ - 10,74

Beispielsweise werden mit 50 US -Dollar, die für Werbung ausgegeben werden, und einem durchschnittlichen monatlichen Niederschlag von 100 mm, voraussichtlich ungef?hr 23 Regenschirme verkaufen:

0.3*50 0.19*100 - 10.74 = 23.26

Einfache lineare Regression: Abh?ngige Variable vorhersagen

Abgesehen von der Berechnung der A- und B -Werte für die Regressionsgleichung kann die Excel -Linest -Funktion auch die abh?ngige Variable (y) basierend auf der bekannten unabh?ngigen Variablen (x) sch?tzen. Zu diesem Zweck verwenden Sie Linest in Kombination mit der Summen- oder Summenproduktfunktion.

So k?nnen Sie beispielsweise die Anzahl der Dachverk?ufe für den n?chsten Monat berechnen, z. B. Oktober, basierend auf dem Umsatz in den Vormonaten und dem Werbebudget von 50 US -Dollar im Oktober:

=SUM(LINEST(C2:C10, B2:B10)*{50,1})

Anstatt den X -Wert in der Formel festzustimmen, k?nnen Sie ihn als Zellreferenz bereitstellen. In diesem Fall müssen Sie auch die 1 -Konstante in einer Zelle eingeben, da Sie Referenzen und Werte in einer Array -Konstante nicht mischen k?nnen.

Mit dem X -Wert in E2 und der Konstante 1 in F2 wird eine der folgenden Formeln eine Leckerbissen wirken:

Regelm??ige Formel (eingegeben durch Drücken der Eingabetaste ):

=SUMPRODUCT(LINEST(C2:C10, B2:B10)*(E2:F2))

Array -Formel (eingegeben durch Drücken von STRG Shift Entry ):

=SUM(LINEST(C2:C10, B2:B10)*(E2:F2))

Um das Ergebnis zu überprüfen, k?nnen Sie den Abfang und die Steigung für dieselben Daten erhalten und dann die lineare Regressionsformel zur Berechnung von Y verwenden:

=E2*G2 F2

Wenn E2 die Steigung ist, ist G2 der x -Wert und F2 der Abfang:

Multiple Regression: Abh?ngige Variable vorhersagen

Falls Sie mit mehreren Pr?diktoren zu tun haben, enthalten dh ein paar verschiedene S?tze von X -Werten alle diese Pr?diktoren in die Array -Konstante. Zum Beispiel lautet die Formel wie folgt mit dem Werbebudget von 50 USD (x ₂ ) und einem durchschnittlichen monatlichen Niederschlag von 100 mm (x ₁ ):

=SUM(LINEST(D2:D10, B2:C10)*{50,100,1})

Wobei D2: D10 die bekannten Y -Werte sind und B2: C10 zwei S?tze von x -Werten:

Bitte achten Sie auf die Reihenfolge der x -Werte in der Array -Konstante. Wie bereits erw?hnt, wird die Steigungskoeffizienten von rechts nach links zurückgegeben, wenn die Excel -Linest -Funktion zur multiplen Regression verwendet wird. In unserem Beispiel wird der Werbekoeffizient zuerst zurückgegeben und dann der Niederschlagskoeffizient . Um die vorhergesagte Verkaufsnummer korrekt zu berechnen, müssen Sie die Koeffizienten mit den entsprechenden X -Werten multiplizieren, sodass Sie die Elemente der Array -Konstante in dieser Reihenfolge einfügen: {50,100,1}. Das letzte Element ist 1, da der letzte von Linest zurückgegebene Wert der Abfangen ist, der nicht ge?ndert werden sollte, sodass Sie ihn einfach mit 1 multiplizieren.

Anstatt eine Array -Konstante zu verwenden, k?nnen Sie alle x -Variablen in einigen Zellen eingeben und diese Zellen in Ihrer Formel verweisen, wie wir es im vorherigen Beispiel getan haben.

Regul?re Formel:

=SUMPRODUCT(LINEST(D2:D10, B2:C10)*(F2:H2))

Array -Formel:

=SUM(LINEST(D2:D10, B2:C10)*(F2:H2))

Wobei F2 und G2 die x -Werte sind und H2 1: 1:

Linestformel: Zus?tzliche Regressionsstatistik

Wie Sie sich vielleicht erinnern, geben Sie in das letzte Argument der Linest -Funktion, um mehr Statistiken für Ihre Regressionsanalyse zu erhalten. Die Formel wird auf unsere Beispieldaten angewendet und hat die folgende Form:

=LINEST(D2:D13, B2:C13, TRUE, TRUE)

Da wir in den Spalten B und C 2 unabh?ngige Variablen haben, w?hlen wir eine Wut aus, die aus 3 Zeilen (zwei X -Werte) und 5 Spalten bestehen, die obige Formel eingeben, die Strg -Schalteingabetaste drücken und dieses Ergebnis erhalten:

Um die #n/A -Fehler loszuwerden, k?nnen Sie das Linie in iFerror wie folgt einbinden:

=IFERROR(LINEST(D2:D13, B2:C13, TRUE, TRUE), "")

Der folgende Screenshot zeigt das Ergebnis und erkl?rt, was jede Zahl bedeutet:

Die Steigungskoeffizienten und der y-Schnittpunkt wurden in den vorherigen Beispielen erkl?rt. Schauen wir uns also einen kurzen Blick auf die anderen Statistiken.

Bestimmungskoeffizient (R ² ). Der Wert von R ² ist das Ergebnis der Teile der Regressionsbetrag der Quadrate durch die Gesamtsumme der Quadrate. Es zeigt Ihnen, wie viele Y -Werte durch x -Variablen erkl?rt werden. Es kann eine beliebige Zahl von 0 bis 1 sein, dh 0% bis 100%. In diesem Beispiel ist R ² ungef?hr 0,97, was bedeutet, dass 97% unserer abh?ngigen Variablen (Umsatzumsatz) durch die unabh?ngigen Variablen (Werbung durchschnittlich monatlich Niederschlag) erkl?rt werden, was eine hervorragende Passform entspricht!

Standardfehler . Im Allgemeinen zeigen diese Werte die Genauigkeit der Regressionsanalyse. Je kleiner die Zahlen, desto sicherer k?nnen Sie sich mit Ihrem Regressionsmodell handeln.

F Statistik . Sie verwenden die F -Statistik, um die Nullhypothese zu unterstützen oder abzulehnen. Es wird empfohlen, die F -Statistik in Kombination mit dem P -Wert zu verwenden, wenn die Gesamtergebnisse signifikant sind.

Freiheitsgrade (DF). Die Linest -Funktion in Excel gibt die verbleibenden Freiheitsgrade zurück, dh der gesamte DF abzüglich der Regression df . Sie k?nnen die Freiheitsgrade verwenden, um F-kritische Werte in einer statistischen Tabelle zu erhalten, und dann die F-kritischen Werte mit der F-Statistik vergleichen, um ein Konfidenzniveau für Ihr Modell zu bestimmen.

Regressionsumme der Quadrate (auch bekannt als die erkl?rte Summe der Quadrate oder Modellsumme der Quadrate ). Es ist die Summe der quadratischen Unterschiede zwischen den vorhergesagten Y -Werten und dem Mittelwert von Y, berechnet mit dieser Formel: = ∑ (? - ?) ² . Es zeigt an, wie viel von der Variation in der abh?ngigen Variablen Ihr Regressionsmodell erkl?rt.

Restbetragsumme . Es ist die Summe der quadratischen Unterschiede zwischen den tats?chlichen Y-Werten und den vorhergesagten Y-Werten. Es zeigt an, wie viel von der Variation in der abh?ngigen Variablen Ihr Modell nicht erkl?rt. Je kleiner die verbleibende Summe der Quadrate im Vergleich zur Gesamtbetrag der Quadrate ist, desto besser passt Ihr Regressionsmodell zu Ihren Daten.

5 Dinge, die Sie über Linest -Funktion wissen sollten

Um Linest -Formeln effizient in Ihren Arbeitsbl?ttern zu verwenden, m?chten Sie m?glicherweise ein bisschen mehr über die "innere Mechanik" der Funktion wissen:

1. Bekanntes und bekannt . In einem einfachen linearen Regressionsmodell mit nur einer Reihe von x -Variablen k?nnen Bekannte_Ys und Bekannte_x von einer beliebigen Form sein, solange sie die gleiche Anzahl von Zeilen und Spalten haben. Wenn Sie eine multiple Regressionsanalyse mit mehr als einem Satz unabh?ngiger X -Variablen durchführen, muss bekannt sind.
2. Die Konstante auf Null erzwingen . Wenn das const -Argument wahr ist oder weggelassen wird, wird die A -Konstante (Abfang) berechnet und in die Gleichung einbezogen: y = bx a. Wenn const auf false eingestellt ist, wird der Abschnitt als gleich 0 angesehen und in der Regressionsgleichung weggelassen: y = bx.

   In Statistiken wird seit Jahrzehnten diskutiert, ob es sinnvoll ist, die Abfangkonstante auf 0 zu erzwingen oder nicht. Viele glaubwürdige Regressionsanalyse -Praktiker glauben, dass die lineare Regression selbst ein falsches Modell für den Datensatz ist, wenn das Festlegen des Abschnitts auf Null (const = false) nützlich zu sein scheint. Andere gehen davon aus, dass die Konstante in bestimmten Situationen auf Null gezwungen werden kann, beispielsweise im Kontext von Regressionsdiskontinuit?tsdesigns. Im Allgemeinen wird empfohlen, in den meisten F?llen mit dem Standard const = true oder weggelassen zu werden.

3. Genauigkeit . Die Genauigkeit der Regressionsgleichung, berechnet durch die Linest -Funktion, h?ngt von der Dispersion Ihrer Datenpunkte ab. Je linear die Daten sind, desto genauer die Ergebnisse der Linest -Formel.
4. Redundante X -Werte . In einigen Situationen haben eine oder mehrere unabh?ngige X -Variablen m?glicherweise keinen zus?tzlichen Vorhersagewert, und das Entfernen solcher Variablen aus dem Regressionsmodell wirkt sich nicht auf die Genauigkeit der vorhergesagten Y -Werte aus. Dieses Ph?nomen ist als "Kollinearit?t" bekannt. Die Excel Linest -Funktion prüft die Kollinearit?t und l?sst alle redundanten X -Variablen aus, die sie aus dem Modell identifiziert. Die ausgelassenen X -Variablen k?nnen durch 0 Koeffizienten und 0 Standardfehlerwerte erkannt werden.
5. Linest vs. Steigung und Abschnitt . Die zugrunde liegende Algorithmikin der Linestfunktion unterscheidet sich von dem Algorithmus, der in den Steigungs- und Abfangfunktionen verwendet wird. Wenn die Quelldaten unbestimmt oder kollinear sind, k?nnen diese Funktionen unterschiedliche Ergebnisse zurückgeben.

Excel Linest -Funktion funktioniert nicht

Wenn Ihre Linest -Formel einen Fehler verursacht oder eine falsche Ausgabe erzeugt, liegt es in einem der folgenden Gründe:

1. Wenn die Linest -Funktion nur eine Zahl (Steigungskoeffizient) zurückgibt, haben Sie sie h?chstwahrscheinlich als regul?re Formel und nicht als Array -Formel eingegeben. Stellen Sie sicher, dass Sie die Strg -Eingabetaste drücken, um die Formel korrekt zu vervollst?ndigen. Wenn Sie dies tun, wird die Formel in die {Curly -Klammern} eingeschlossen, die in der Formelleiste sichtbar sind.
2. #REF! Fehler. Tritt auf, wenn die Bereiche Bekannter_x und Bekannter_y unterschiedliche Dimensionen aufweisen.
3. #WERT! Fehler. Tritt auf, wenn Bekannte_x oder Bekannte_Ys mindestens eine leere Zelle, Textwert oder Textdarstellung einer Nummer enth?lt, die Excel nicht als numerischen Wert erkennt. Au?erdem tritt der #Value -Fehler auf, wenn das Argument für das const oder das Statistik nicht auf True oder False bewertet werden kann.

So verwenden Sie Linest in Excel für eine einfache und multiple lineare Regressionsanalyse. Um die in diesem Tutorial diskutierten Formeln genauer zu betrachten, k?nnen Sie unser Beispiel -Arbeitsbuch unten herunterladen. Ich danke Ihnen für das Lesen und hoffe, Sie n?chste Woche in unserem Blog zu sehen!

üben Sie die Arbeitsmappe zum Download

Beispiele für Excel Linest -Funktion (.xlsx -Datei)

Das obige ist der detaillierte Inhalt vonExcel -Linest -Funktion mit Formel -Beispielen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!