Lineare Regression in Excel durch Datenanalyse. Regressionsgleichung wie man in Excel vorgeht

Dies ist die gebräuchlichste Methode, um die Abhängigkeit einer Variablen von anderen darzustellen, z. B. wie sie abhängt BIP-Niveau auf den Wert Auslandsinvestition oder von Kreditzins der Nationalbank oder von Preise für wichtige Energieressourcen.

Die Modellierung ermöglicht es Ihnen, das Ausmaß dieser Abhängigkeit (Koeffizienten) aufzuzeigen, wodurch Sie eine direkte Prognose erstellen und basierend auf diesen Prognosen eine Planung durchführen können. Auf der Grundlage der Regressionsanalyse ist es auch möglich, Managemententscheidungen zu treffen, die darauf abzielen, die vorrangigen Ursachen zu stimulieren, die das Endergebnis beeinflussen. Das Modell selbst wird dazu beitragen, diese vorrangigen Faktoren hervorzuheben.

Gesamtansicht des linearen Regressionsmodells:

Y = a 0 + a 1 x 1 + ... + a k x k

wo ein - Parameter (Koeffizienten) der Regression, x - beeinflussende Faktoren, k - die Anzahl der Faktoren im Modell.

Ausgangsdaten

Unter den Ausgangsdaten benötigen wir einen bestimmten Datensatz, bei dem es sich um mehrere aufeinanderfolgende oder miteinander verbundene Werte des endgültigen Parameters Y (zum Beispiel BIP) und die gleiche Anzahl von Indikatorwerten handelt, deren Einfluss wir untersuchen (für Beispiel Auslandsinvestitionen).

Die obige Abbildung zeigt eine Tabelle mit diesen allerersten Daten, da Y der Indikator für die Erwerbsbevölkerung ist und die Anzahl der Unternehmen, die Höhe der Kapitalinvestitionen und das Einkommen der Bevölkerung Einflussfaktoren sind, also Xs.

Aus der Abbildung kann man auch den irrigen Schluss ziehen, dass es sich bei der Simulation nur um dynamische Reihen handeln kann, d die Struktur, beispielsweise die in der Tabelle angegebenen Werte, können nicht nach Jahren, sondern nach Regionen aufgeschlüsselt werden.

Um adäquate lineare Modelle zu erstellen, ist es wünschenswert, dass die Anfangsdaten keine starken Einbrüche oder Einbrüche aufweisen. In solchen Fällen ist es ratsam, eine Glättung durchzuführen, aber wir werden beim nächsten Mal über die Glättung sprechen.

Analysepaket

Die Parameter des linearen Regressionsmodells können auch manuell mit der Methode der kleinsten Quadrate (OLS) berechnet werden, was jedoch recht zeitaufwendig ist. Etwas schneller kann es mit der gleichen Methode mit Formeln in Excel berechnet werden, wobei das Programm die Berechnungen selbst durchführt, Sie die Formeln jedoch immer noch manuell eingeben müssen.

Excel hat ein Add-In Analysepaket was ist hübsch leistungsfähiges Werkzeug um dem Analytiker zu helfen. Dieses Toolkit ist unter anderem in der Lage, die Regressionsparameter gemäß dem gleichen OLS mit nur wenigen Klicks zu berechnen, tatsächlich wird die weitere Verwendung dieses Tools besprochen.

Aktivierung des Analysepakets

Standardmäßig ist dieses Add-On deaktiviert und Sie finden es nicht im Registerkartenmenü. Sehen wir uns also Schritt für Schritt an, wie Sie es aktivieren.

Aktivieren Sie in Excel oben links die Registerkarte Datei, suchen Sie im sich öffnenden Menü nach dem Element Optionen und klicken Sie darauf.

Im sich öffnenden Fenster suchen wir links den Artikel Add-ons und aktivieren Sie es, in dieser Registerkarte unten wird eine Dropdown-Liste von Steuerelementen angezeigt, in die es standardmäßig geschrieben wird Excel-Add-Ins, rechts neben der Dropdown-Liste befindet sich eine Schaltfläche Gehe zu, und Sie müssen darauf klicken.

Ein Pop-up-Fenster bietet an, die verfügbaren Add-Ons auszuwählen, darin müssen Sie ein Häkchen vor setzen Analysepaket und gleichzeitig, nur für den Fall, Eine Lösung finden(auch eine nützliche Sache), und bestätigen Sie dann die Auswahl mit einem Klick auf den Button OK.

Anleitung zum Auffinden von Parametern der linearen Regression mit dem Analysepaket

Nach der Aktivierung des Analysis Pack-Add-Ins ist es immer im Hauptmenü-Tab verfügbar Daten unter dem link Datenanalyse

Im aktiven Fenster des Tools Datenanalyse aus der Liste der Möglichkeiten, die wir suchen und auswählen Rückschritt

Als nächstes öffnet sich ein Fenster zum Einstellen und Auswählen von Anfangsdaten für die Berechnung der Parameter des Regressionsmodells. Hier müssen Sie die Intervalle der Ausgangsdaten angeben, nämlich den beschriebenen Parameter (Y) und die ihn beeinflussenden Faktoren (X), wie in der folgenden Abbildung, die restlichen Parameter sind grundsätzlich optional einstellbar.

Nachdem Sie die Ausgangsdaten ausgewählt und auf die Schaltfläche OK geklickt haben, zeigt Excel Berechnungen auf einem neuen Blatt der aktiven Arbeitsmappe an (sofern in den Einstellungen nicht anders angegeben), diese Berechnungen lauten wie folgt:

Ich habe die Schlüsselzellen gelb ausgefüllt, sie sollten zuerst beachtet werden, die anderen Parameter sind auch wichtig, aber deren detaillierte Analyse erfordert vielleicht einen separaten Beitrag.

So, 0,865 - Das R2- das Bestimmtheitsmaß, das zeigt, dass 86,5% der berechneten Parameter des Modells, dh das Modell selbst, die Abhängigkeit und Änderungen des untersuchten Parameters erklären - Ja aus den untersuchten Faktoren - xs... Wenn übertrieben, dann dies ist ein Indikator für die Qualität des Modells und je höher desto besser. Es ist klar, dass es nicht mehr als 1 sein kann und es als gut angesehen wird, wenn R 2 größer als 0,8 ist, und wenn es kleiner als 0,5 ist, kann die Angemessenheit eines solchen Modells sicher in Frage gestellt werden.

Kommen wir nun zu Modellkoeffizienten:
2079,85 - Das ein 0- ein Koeffizient, der angibt, wie Y aussehen wird, wenn alle im Modell verwendeten Faktoren gleich 0 sind. Es wird angenommen, dass dies eine Abhängigkeit von anderen im Modell nicht beschriebenen Faktoren ist;
-0,0056 - ein 1- ein Koeffizient, der die Bedeutung des Einflusses des Faktors x 1 auf Y zeigt, d. h. die Anzahl der Unternehmen innerhalb dieses Modells beeinflusst den Indikator der Erwerbsbevölkerung mit einem Gewicht von nur -0,0056 (ein eher geringer Einfluss ). Das Minuszeichen zeigt, dass dieser Einfluss negativ ist, d. h. je mehr Unternehmen, desto weniger erwerbstätige Bevölkerung, so paradox seine Bedeutung auch sein mag;
-0,0026 - ein 2- Koeffizient des Einflusses des Kapitalanlagevolumens auf die Größe der erwerbstätigen Bevölkerung, nach dem Modell ist dieser Einfluss ebenfalls negativ;
0,0028 - ein 3- der Koeffizient des Einflusses des Bevölkerungseinkommens auf die Größe der Erwerbsbevölkerung, hier ist der Einfluss positiv, d .

Lassen Sie uns die berechneten Koeffizienten im Modell sammeln:

Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3

Tatsächlich ist dies das lineare Regressionsmodell, das für die im Beispiel verwendeten Ausgangsdaten genau so aussieht.

Modell berechnete Werte und Prognose

Wie oben besprochen, ist das Modell nicht nur so aufgebaut, dass es die Größenordnung der Abhängigkeiten des untersuchten Parameters von den Einflussfaktoren zeigt, sondern auch eine Prognose in Kenntnis dieser Einflussfaktoren möglich ist. Diese Vorhersage ist ganz einfach, Sie müssen nur die Werte der Einflussfaktoren an die Stelle der entsprechenden xes in der resultierenden Modellgleichung setzen. In der folgenden Abbildung werden diese Berechnungen in Excel in einer separaten Spalte durchgeführt.

Die tatsächlichen Werte (die in der Realität stattgefunden haben) und die berechneten Werte für das Modell in derselben Abbildung werden in Form von Grafiken angezeigt, um die Differenz und damit den Modellfehler zu zeigen.

Ich wiederhole es noch einmal, um eine Prognose nach dem Modell zu erstellen, ist es notwendig, dass bekannte Einflussfaktoren vorliegen, und wenn es sich um eine Zeitreihe und dementsprechend eine Prognose für die Zukunft handelt, zum Beispiel für im nächsten Jahr oder Monat, dann ist es bei weitem nicht immer möglich herauszufinden, welche Einflussfaktoren gerade in dieser Zukunft sein werden. In solchen Fällen ist auch eine Prognose der Einflussfaktoren notwendig, meistens erfolgt dies mit einem autoregressiven Modell - einem Modell, bei dem das untersuchte Objekt selbst und die Zeit die Einflussfaktoren sind, also die Abhängigkeit des Indikators nach dem Vorbild der Vergangenheit.

Wir werden im nächsten Artikel überlegen, wie man ein autoregressives Modell erstellt, aber jetzt nehmen wir an, dass wir wissen, wie die Werte der Einflussfaktoren in der zukünftigen Periode (im Beispiel von 2008) sein werden, indem wir diese Werte in die Berechnungen erhalten wir unsere Prognose für 2008.

Zeigt die Auswirkung einiger Werte (unabhängig, unabhängig) auf die abhängige Variable. Wie hängt beispielsweise die Zahl der erwerbstätigen Bevölkerung von der Zahl der Unternehmen, der Höhe der Löhne und anderen Parametern ab. Oder: Wie wirken sich ausländische Investitionen, Energiepreise etc. auf die Höhe des BIP aus?

Das Ergebnis der Analyse ermöglicht Ihnen, Prioritäten zu setzen. Und basierend auf den Hauptfaktoren prognostizieren, planen Sie die Entwicklung von Schwerpunktbereichen und treffen Sie Managemententscheidungen.

Regression geschieht:

Linear (y = a + bx);

Parabolisch (y = a + bx + cx 2);

Exponentiell (y = a * exp (bx));

Leistung (y = a * x ^ b);

Hyperbolisch (y = b / x + a);

Logarithmisch (y = b * 1n (x) + a);

Exponentiell (y = a * b ^ x).

Sehen wir uns ein Beispiel für die Erstellung eines Regressionsmodells in Excel und die Interpretation der Ergebnisse an. Lass uns nehmen linearer Typ Rückschritt.

Aufgabe. Bei 6 Unternehmen wurden das durchschnittliche Monatsgehalt und die Anzahl der ausscheidenden Mitarbeiter analysiert. Es ist notwendig, die Abhängigkeit der Anzahl der ausscheidenden Mitarbeiter vom Durchschnittsgehalt zu bestimmen.

Das lineare Regressionsmodell sieht wie folgt aus:

Y = a 0 + a 1 x 1 + ... + a k x k.

Wobei a - Regressionskoeffizienten, x - Einflussgrößen, k - Anzahl der Faktoren.

In unserem Beispiel ist Y der Indikator für ausscheidende Mitarbeiter. Der Einflussfaktor ist der Lohn (x).

Excel verfügt über integrierte Funktionen, mit denen Sie die Parameter eines linearen Regressionsmodells berechnen können. Aber das Analysis Package-Add-In macht es schneller.

Wir aktivieren ein leistungsstarkes Analysetool:

1. Drücken Sie die Schaltfläche „Office“ und wechseln Sie in die Registerkarte „Excel-Optionen“. "Add-ons".

2. Unten, unter der Dropdown-Liste, im Feld "Steuerung" befindet sich die Aufschrift "Excel-Add-Ins" (falls nicht vorhanden, klicken Sie auf das Kontrollkästchen rechts und wählen Sie). Und die Schaltfläche "Los". Wir drücken.

3. Eine Liste der verfügbaren Add-Ins wird geöffnet. Wählen Sie "Analysepaket" und klicken Sie auf OK.

Nach der Aktivierung steht das Add-In auf der Registerkarte Daten zur Verfügung.

Kommen wir nun direkt zur Regressionsanalyse.

1. Öffnen Sie das Werkzeugmenü "Datenanalyse". Wir wählen "Regression".

2. Es öffnet sich ein Menü zur Auswahl von Eingabewerten und Ausgabeparametern (wo das Ergebnis angezeigt wird). In den Feldern für die Ausgangsdaten geben wir die Spannweite des beschriebenen Parameters (Y) und den Einflussfaktor (X) an. Der Rest kann leer bleiben.

3. Nachdem Sie auf OK geklickt haben, zeigt das Programm die Berechnungen auf einem neuen Blatt an (Sie können das Intervall für die Anzeige auf dem aktuellen Blatt auswählen oder die Ausgabe einem neuen Buch zuweisen).

Achten Sie zunächst auf das R-Quadrat und die Koeffizienten.

R-Quadrat ist das Bestimmtheitsmaß. In unserem Beispiel - 0,755 oder 75,5%. Dies bedeutet, dass die berechneten Parameter des Modells den Zusammenhang zwischen den untersuchten Parametern zu 75,5% erklären. Je höher das Bestimmtheitsmaß, desto besser ist das Modell. Gut - über 0,8. Schlecht - weniger als 0,5 (eine solche Analyse kann kaum als vernünftig angesehen werden). In unserem Beispiel - "nicht schlecht".

Der Koeffizient 64,1428 zeigt, was Y sein wird, wenn alle Variablen im betrachteten Modell gleich 0 sind. Das heißt, andere Faktoren, die im Modell nicht beschrieben sind, beeinflussen auch den Wert des analysierten Parameters.

Der Koeffizient -0,16285 zeigt die Gewichtung der Variablen X auf Y. Das heißt, das durchschnittliche Monatsgehalt in diesem Modell beeinflusst die Anzahl der Personen, die mit einer Gewichtung von -0,16285 (dies ist ein geringer Einfluss) verlassen. Das „-“-Zeichen weist auf einen negativen Einfluss hin: Je höher das Gehalt, desto weniger Kündigungen. Was gerecht ist.

Auch eine statistische Verarbeitung der Daten kann über das Add-In erfolgen ANALYSEPAKET(Abb. 62).

Aus den vorgeschlagenen Artikeln wählt er den Artikel " REGRESSION"Und klicken Sie mit der linken Maustaste darauf. Klicken Sie dann auf OK.

Das in Abb. 63.

Analysetool " REGRESSION»Wird verwendet, um einen Graphen mit der Methode der kleinsten Quadrate an eine Reihe von Beobachtungen anzupassen. Die Regression wird verwendet, um die Wirkung der Werte einer oder mehrerer erklärender Variablen auf eine einzelne abhängige Variable zu analysieren. Beispielsweise beeinflussen mehrere Faktoren die sportliche Leistung eines Sportlers, einschließlich Alter, Größe und Gewicht. Sie können die Auswirkungen jedes dieser drei Faktoren auf die Leistung eines Sportlers berechnen und diese Daten dann verwenden, um die Leistung eines anderen Sportlers vorherzusagen.

Das Regressionswerkzeug verwendet die Funktion LINEST.

Dialogfeld REGRESSION

Beschriftungen Aktivieren Sie das Kontrollkästchen, wenn die erste Zeile oder erste Spalte des Eingabebereichs Überschriften enthält. Deaktivieren Sie dieses Kontrollkästchen, wenn keine Titel vorhanden sind. In diesem Fall werden automatisch entsprechende Header für die Ausgabetabellendaten generiert.

Konfidenzstufe Aktivieren Sie das Kontrollkästchen, um eine zusätzliche Stufe in die Ausgabesummentabelle aufzunehmen. Geben Sie in das entsprechende Feld die anzuwendende Zuverlässigkeitsstufe zusätzlich zum Standardwert von 95 % ein.

Konstante - Null Aktivieren Sie das Kontrollkästchen, damit die Regressionsgerade durch den Ursprung verläuft.

Ausgabeabstand Geben Sie einen Verweis auf die obere linke Zelle des Ausgabebereichs ein. Weisen Sie mindestens sieben Spalten für die Ausgabesummentabelle zu, die Folgendes enthält: ANOVA-Ergebnisse, Koeffizienten, Standardfehler der Y-Berechnung, Standardabweichungen, Anzahl der Beobachtungen, Standardfehler für Koeffizienten.

Neues Arbeitsblatt Wählen Sie diesen Schalter, um ein neues Arbeitsblatt in der Arbeitsmappe zu öffnen und die Analyseergebnisse ab Zelle A1 einzufügen. Geben Sie bei Bedarf im Feld gegenüber der entsprechenden Schalterstellung einen Namen für das neue Blatt ein.

Neue Arbeitsmappe Klicken Sie auf den Schalter in diese Position, um eine neue Arbeitsmappe zu erstellen, in der die Ergebnisse einem neuen Arbeitsblatt hinzugefügt werden.

Residuen Aktivieren Sie das Kontrollkästchen, um Residuen in die Ausgabetabelle aufzunehmen.

Standardisierte Residuen Aktivieren Sie das Kontrollkästchen, um standardisierte Residuen in die Ausgabetabelle aufzunehmen.

Residuen darstellen Aktivieren Sie das Kontrollkästchen, um die Residuen für jede unabhängige Variable darzustellen.

Anpassungsdiagramm Aktivieren Sie das Kontrollkästchen, um das Diagramm der vorhergesagten Werte gegenüber den beobachteten Werten darzustellen.

Diagramm der normalen Wahrscheinlichkeit Aktivieren Sie das Kontrollkästchen, um das normale Wahrscheinlichkeitsdiagramm zu zeichnen.

Funktion LINEST

Um Berechnungen durchzuführen, wählen Sie mit dem Cursor die Zelle aus, in der der Mittelwert angezeigt werden soll, und drücken Sie die Taste = auf der Tastatur. Geben Sie anschließend im Feld Name die gewünschte Funktion ein, z. B. DURCHSCHNITT(Abb. 22).

Funktion LINEST Berechnet Statistiken für eine Reihe unter Verwendung der kleinsten Quadrate, um die gerade Linie zu berechnen, die am besten zu den verfügbaren Daten passt, und gibt dann ein Array zurück, das die resultierende gerade Linie beschreibt. Sie können die Funktion auch kombinieren LINEST mit anderen Funktionen, um andere Arten von Modellen zu berechnen, die in unbekannten Parametern (deren unbekannte Parameter linear sind) linear sind, einschließlich polynomischer, logarithmischer, exponentieller und Potenzreihen. Da ein Array von Werten zurückgegeben wird, muss die Funktion als Array-Formel angegeben werden.

Die Gleichung für eine Gerade lautet wie folgt:

y = m 1 x 1 + m 2 x 2 +… + b (bei mehreren x-Wertebereichen),

wobei der abhängige Wert y eine Funktion des unabhängigen x-Werts ist, die m-Werte die Koeffizienten sind, die jeder unabhängigen x-Variablen entsprechen, und b eine Konstante ist. Beachten Sie, dass y, x und m Vektoren sein können. Funktion LINEST gibt ein Array zurück (mn; mn-1;…; m 1; b). LINEST kann auch zusätzliche Regressionsstatistiken zurückgeben.

LINEST(bekannte_y's; bekannte_x's; const; Statistik)

Known_y's ist die Menge der y-Werte, die bereits für die Beziehung y = mx + b bekannt sind.

Wenn bekannte_y's eine Spalte haben, dann wird jede Spalte in bekannten_x's als separate Variable interpretiert.

Wenn bekannte_y's eine einzelne Zeile haben, dann wird jede Zeile in bekannten_x's als separate Variable interpretiert.

Known_x's sind ein optionaler Satz von x-Werten, die bereits für y = mx + b bekannt sind.

Known_x's können einen oder mehrere Variablensätze enthalten. Wenn nur eine Variable verwendet wird, können bekannte_y's und bekannte_x's jede beliebige Form haben, solange sie die gleiche Dimension haben. Wenn mehr als eine Variable verwendet wird, müssen die bekannten_y ein Vektor sein (d. h. eine Zeile hoch oder eine Spalte breit).

Wenn array_known_x's weggelassen wird, wird angenommen, dass dieses Array (1; 2; 3; ...) dieselbe Größe hat wie array_known_y's.

Const ist ein boolescher Wert, der angibt, ob die Konstante b 0 sein muss.

Wenn const TRUE ist oder weggelassen wird, wird die Konstante b wie üblich ausgewertet.

Ist das Argument "const" FALSE, dann wird der Wert von b gleich 0 gesetzt und die Werte von m werden so gewählt, dass die Beziehung y = mx erfüllt ist.

Statistik ist ein boolescher Wert, der angibt, ob Sie zusätzliche Statistiken für die Regression zurückgeben möchten.

Wenn Statistics TRUE ist, gibt REST zusätzliche Regressionsstatistiken zurück. Das zurückgegebene Array sieht so aus: (mn; mn-1; ...; m1; b: sen; sen-1; ...; se1; seb: r2; sey: F; df: ssreg; ssresid).

Wenn Statistik FALSE ist oder weggelassen wird, gibt REST nur die Koeffizienten m und die Konstante b zurück.

Zusätzliche Regressionsstatistiken (Tabelle 17)

Die Größenordnung	Beschreibung
se1, se2, ..., sen	Standardfehlerwerte für die Koeffizienten m1, m2, ..., mn.
seb	Standardfehlerwert für Konstante b (seb = # N / A wenn const FALSE ist).
r2	Koeffizient des Determinismus. Die tatsächlichen y-Werte werden mit den aus der Geradengleichung erhaltenen Werten verglichen; Basierend auf den Ergebnissen des Vergleichs wird der Determinismuskoeffizient berechnet, normalisiert von 0 auf 1. Wenn er gleich 1 ist, besteht eine vollständige Korrelation mit dem Modell, dh es gibt keinen Unterschied zwischen dem tatsächlichen und dem geschätzten Werte von y. Andernfalls, wenn der Determinismuskoeffizient 0 ist, macht es keinen Sinn, eine Regressionsgleichung zu verwenden, um y-Werte vorherzusagen. Weitere Informationen zur Berechnung von r2 finden Sie in den "Anmerkungen" am Ende dieses Abschnitts.
sey	Standardfehler für die Schätzung von y.
F	F-Statistik oder F-beobachteter Wert. Die F-Statistik wird verwendet, um zu bestimmen, ob die beobachtete Beziehung zwischen den abhängigen und unabhängigen Variablen zufällig ist.
df	Freiheitsgrade. Freiheitsgrade sind nützlich, um F-kritische Werte in einer statistischen Tabelle zu finden. Um das Zuverlässigkeitsniveau des Modells zu bestimmen, vergleichen Sie die Werte in der Tabelle mit der von LINEST zurückgegebenen F-Statistik. Weitere Informationen zur Berechnung von df finden Sie in den Anmerkungen am Ende dieses Abschnitts. Beispiel 4 unten zeigt die Verwendung der F- und df-Werte.
ssreg	Regressionssumme der Quadrate.
ssresid	Restsumme der Quadrate. Weitere Informationen zum Berechnen der ssreg- und ssresid-Werte finden Sie in den Anmerkungen am Ende dieses Abschnitts.

Die folgende Abbildung zeigt die Reihenfolge, in der die zusätzlichen Regressionsstatistiken zurückgegeben werden (Abbildung 64).

Anmerkungen:

Jede Gerade kann durch ihre Steigung und den Schnittpunkt mit der y-Achse beschrieben werden:

Steigung (m): Um die Steigung einer geraden Linie zu bestimmen, die normalerweise mit m bezeichnet wird, müssen Sie zwei Punkte der geraden Linie (x 1, y 1) und (x 2, y 2) nehmen; die Steigung ist (y 2 -y 1) / (x 2 -x 1).

Y-Schnittpunkt (b): Der y-Schnittpunkt einer Linie, normalerweise mit b bezeichnet, ist der y-Wert des Punktes, an dem die Linie die y-Achse schneidet.

Die Geradengleichung hat die Form y = mx + b. Wenn Sie die Werte von m und b kennen, können Sie jeden Punkt auf der Linie berechnen, indem Sie die y- oder x-Werte in der Gleichung einsetzen. Sie können auch die TREND-Funktion verwenden.

Wenn es nur eine unabhängige Variable x gibt, können Sie die Steigung und den y-Achsenabschnitt direkt mit den folgenden Formeln ermitteln:

Steigung: INDEX (LINEST (bekannte_y; bekannte_x); 1)

Y-Schnittpunkt: INDEX (LINEST (bekannte_y; bekannte_x); 2)

Die Genauigkeit der LINEST-Linien-Approximation hängt vom Streuungsgrad der Daten ab. Je näher die Daten an einer geraden Linie liegen, desto genauer ist das LINEST-Modell. REST verwendet die Methode der kleinsten Quadrate, um die beste Anpassung an die Daten zu bestimmen. Wenn es nur eine unabhängige Variable x gibt, werden m und b mit den folgenden Formeln berechnet:

wobei x und y Abtastmittelwerte sind, zum Beispiel x = MITTELWERT (bekannte_x) und y = MITTELWERT (bekannte_y).

Die Anpassungsfunktionen LINEST und LOGEST können die gerade oder exponentielle Kurve berechnen, die die Daten am besten beschreibt. Sie beantworten jedoch nicht die Frage, welches der beiden Ergebnisse zur Lösung der gestellten Aufgabe besser geeignet ist. Sie können auch TREND (bekannte_y; bekannte_x) für eine gerade Linie oder WACHSTUM (bekannte_y; bekannte_x) für eine exponentielle Kurve berechnen. Diese Funktionen geben, wenn Sie new_x_values ​​nicht angeben, ein Array von berechneten y-Werten für die tatsächlichen x-Werte entlang einer geraden Linie oder Kurve zurück. Die berechneten Werte können dann mit den tatsächlichen Werten verglichen werden. Sie können auch Diagramme zum visuellen Vergleich erstellen.

Durch die Durchführung einer Regressionsanalyse, Microsoft Excel berechnet für jeden Punkt das Quadrat der Differenz zwischen dem vorhergesagten y-Wert und dem tatsächlichen y-Wert. Die Summe dieser quadrierten Differenzen wird als Residualsumme der Quadrate (ssresid) bezeichnet. Microsoft Excel berechnet dann die Gesamtsumme der Quadrate (stotal). Wenn const = TRUE oder weggelassen wird, entspricht die Gesamtsumme der Quadrate der Summe der Quadrate der Differenz zwischen den tatsächlichen y-Werten und den mittleren y-Werten. Bei const = FALSE ist die Gesamtsumme der Quadrate gleich der Summe der Quadrate der reellen Werte von y (ohne den Mittelwert von y vom Quotientenwert von y zu subtrahieren). Die Regressionsquadratsumme kann dann wie folgt berechnet werden: ssreg = sstotal - ssresid. Je kleiner die Residualsumme der Quadrate ist, desto größer ist der Wert des Determinismuskoeffizienten r2, der zeigt, wie gut die Gleichung mit Regressionsanalyse, erklärt die Beziehung zwischen Variablen. Der r2-Koeffizient ist ssreg / sstotal.

In einigen Fällen haben eine oder mehrere X-Spalten (lassen Sie die Y- und X-Werte in den Spalten sein) keinen zusätzlichen prädikativen Wert in den anderen X-Spalten. Mit anderen Worten, das Löschen einer oder mehrerer X-Spalten kann zu Y-Werten führen ​mit der gleichen Genauigkeit berechnet. In diesem Fall werden redundante X-Spalten aus dem Regressionsmodell ausgeschlossen. Dieses Phänomen wird als "Kollinearität" bezeichnet, da redundante X-Spalten als Summe mehrerer nicht redundanter Spalten dargestellt werden können. REST prüft auf Kollinearität und entfernt alle redundanten X-Spalten aus dem Regressionsmodell, wenn sie gefunden werden. Die gelöschten X-Spalten können in der LINEST-Ausgabe durch einen Faktor von 0 und einen se-Wert von 0 identifiziert werden. Das Entfernen einer oder mehrerer Spalten als redundant ändert den df-Wert, da er von der Anzahl der X-Spalten abhängt, die tatsächlich für Vorhersagezwecke verwendet werden. Weitere Informationen zur Berechnung von df finden Sie unten in Beispiel 4. Wenn sich df aufgrund des Entfernens redundanter Spalten ändert, ändern sich auch sey und F. Von Kollinearität wird oft abgeraten. Es sollte jedoch verwendet werden, wenn einige der X-Spalten 0 oder 1 enthalten, um anzuzeigen, ob die Versuchsperson in einer separaten Gruppe ist. Wenn const = TRUE oder weggelassen wird, fügt REST eine zusätzliche X-Spalte ein, um den Schnittpunkt zu simulieren. Wenn es eine Spalte mit Werten von 1 für Männer und 0 für Frauen gibt und es auch eine Spalte mit Werten von 1 für Frauen und 0 für Männer gibt, dann wird die letzte Spalte entfernt, da ihre Werte sein können aus der Spalte mit dem „Indikator für das männliche Geschlecht“ erhalten.

Die Berechnung von df für Fälle, in denen Spalten von X aufgrund von Kollinearität nicht aus dem Modell entfernt werden, ist wie folgt: Wenn es k Spalten mit bekannten_xen gibt und der Wert von const = WAHR oder nicht angegeben ist, dann ist df = n - k - 1. Wenn const = FALSE, dann df = n - k. In beiden Fällen erhöht das Entfernen der X-Spalten aufgrund von Kollinearität den df-Wert um 1.

Formeln, die Arrays zurückgeben, müssen als Arrayformeln eingegeben werden.

Wenn Sie ein Array von Konstanten für beispielsweise bekannte_x eingeben, verwenden Sie ein Semikolon, um die Werte in derselben Zeile zu trennen, und einen Doppelpunkt, um die Zeilen zu trennen. Die Trennzeichen variieren je nach den Optionen, die im Fenster Sprache und Standards auf dem Bedienfeld eingestellt sind.

Es ist zu beachten, dass die von der Regressionsgleichung vorhergesagten y-Werte möglicherweise nicht korrekt sind, wenn sie außerhalb des Bereichs der y-Werte liegen, die zur Definition der Gleichung verwendet wurden.

Der in der Funktion verwendete Hauptalgorithmus LINEST, unterscheidet sich vom Hauptalgorithmus der Funktionen NEIGUNG und SEKTION... Unterschiede zwischen den Algorithmen können bei undefinierten und kollinearen Daten zu unterschiedlichen Ergebnissen führen. Wenn beispielsweise die Datenpunkte von bekannten_y 0 und die Datenpunkte von bekannten_x 1 sind, dann:

Funktion LINEST gibt einen Wert gleich 0 zurück. Funktionsalgorithmus LINEST wird verwendet, um geeignete Werte für kollineare Daten zurückzugeben, und in in diesem Fall mindestens eine Antwort gefunden werden kann.

Die Funktionen SLOPE und INTERCEPT geben den # DIV / 0!-Fehler zurück. Der Funktionsalgorithmus SLOPE und INTERCEPT wird verwendet, um nur nach einer Antwort zu suchen, und in diesem Fall können mehrere sein.

Neben der Berechnung von Statistiken für andere Regressionstypen kann RUND zum Berechnen von Bereichen für andere Regressionstypen verwendet werden, indem die Funktionen von x und y als Reihe von x und y für RUND eingegeben werden. Zum Beispiel die folgende Formel:

RUND (y-Werte, x-Werte ^ SPALTE ($ A: $ C))

funktioniert mit einer Spalte mit Y-Werten und einer Spalte mit X-Werten, um eine Annäherung an einen Würfel (3. Grades Polynom) der folgenden Form zu berechnen:

y = m1x + m2x2 + m3x3 + b

Die Formel kann geändert werden, um andere Regressionsarten zu berechnen, aber in einigen Fällen sind Anpassungen der Ausgabewerte und anderer Statistiken erforderlich.

Die Regressionsgerade ist eine grafische Widerspiegelung der Beziehung zwischen Phänomenen. Sie können in Excel sehr anschaulich eine Regressionslinie erstellen.

Dafür braucht man:

1. Excel-Programm öffnen

2. Erstellen Sie Spalten mit Daten. In unserem Beispiel werden wir eine Regressionslinie oder Beziehung zwischen Aggressivität und Selbstzweifeln bei Erstklässlern aufbauen. An dem Experiment nahmen 30 Kinder teil, die Daten sind in der Excel-Tabelle dargestellt:

1 Spalte - Nr. des Themas

2 spaltig - Aggressivität in Punkten

3 Spalte - Selbstzweifel in Punkten

3. Dann müssen Sie beide Spalten auswählen (ohne den Namen der Spalte), klicken Sie auf die Registerkarte Einfügung , auswählen Punkt , und wählen Sie aus den vorgeschlagenen Layouts das allererste aus Stelle mit Markierungen .

4.Also haben wir ein Leerzeichen für die Regressionsgerade - die sogenannte - Streudiagramm... Um zur Regressionslinie zu gelangen, müssen Sie auf die resultierende Zahl klicken und auf die Registerkarte klicken Konstrukteur, auf Panel finden Diagrammlayouts und wähle m ein ket9 , heißt es auch f(x)

5. Wir haben also eine Regressionsgerade. Der Graph zeigt auch seine Gleichung und das Quadrat des Korrelationskoeffizienten

6. Es bleibt noch der Name des Graphen, der Namen der Achsen hinzuzufügen. Wenn Sie möchten, können Sie auch die Legende entfernen, die Anzahl der horizontalen Rasterlinien reduzieren (die Registerkarte Layout , dann Netz ). Grundlegende Änderungen und Einstellungen werden im Tab vorgenommen Layout

Die Regressionslinie wurde in MS Excel erstellt. Jetzt kann es zum Text der Arbeit hinzugefügt werden.

Die Konstruktion der linearen Regression, die Schätzung ihrer Parameter und ihrer Bedeutung kann mit dem Paket viel schneller durchgeführt werden Excel-Analyse(Rückschritt). Betrachten Sie die Interpretation der im allgemeinen Fall erhaltenen Ergebnisse ( k erklärende Variablen) gemäß Beispiel 3.6.

In der Tabelle Regressionsstatistik die Werte sind angegeben:

Mehrere R - multipler Korrelationskoeffizient;

R- Quadrat- Bestimmtheitsmaß R 2 ;

Normalisiert R - Quadrat- angepasst R 2 korrigiert um die Anzahl der Freiheitsgrade;

Standart Fehler- Standardfehler der Regression S;

Beobachtungen - Anzahl der Beobachtungen n.

In der Tabelle ANOVA sind gegeben:

1. Spalte df - die Anzahl der Freiheitsgrade, gleich

für Saite Rückschritt df = k;

für Saite Restdf = n – k – 1;

für Saite Gesamtdf = n– 1.

2. Spalte SS- Summe der Quadrate der Abweichungen gleich

für Saite Rückschritt ;

für Saite Rest ;

für Saite Gesamt .

3. Spalte FRAU Varianzen bestimmt durch die Formel FRAU = SS/df:

für Saite Rückschritt- faktorielle Varianz;

für Saite Rest- Restvarianz.

4. Spalte F - berechneter Wert F-Kriterium berechnet nach der Formel

F = FRAU(Rückschritt)/ FRAU(Rest).

5. Spalte Bedeutung F - der Wert des Signifikanzniveaus entsprechend dem berechneten F-Statistiken .

Bedeutung F= FIST ( F- Statistiken, df(Rückgang), df(Rest)).

Wenn die Bedeutung F < стандартного уровня значимости, то R 2 ist statistisch signifikant.

	Koeffizienten	Standart Fehler	t-Statistik	P-Wert	Untere 95%	Top 95 %
Ja	65,92	11,74	5,61	0,00080	38,16	93,68
x	0,107	0,014	7,32	0,00016	0,0728	0,142

Diese Tabelle zeigt:

1. Chancen- Koeffizientenwerte ein, B.

2. Standardfehler–Standardfehler der Regressionskoeffizienten S a, S b.

3. T- Statistiken- berechnete Werte T -Kriterien berechnet nach der Formel:

t-Statistik = Koeffizienten / Standardfehler.

4.R-Wert (Bedeutung T) Entspricht der Signifikanzniveauwert dem berechneten T- Statistiken.

R-Wert = TDIST(T-Statistiken, df(Rest)).

Wenn R-Bedeutung< стандартного уровня значимости, то соответствующий коэффициент статистически значим.

5... Die unteren 95 % und die oberen 95 %- die untere und obere Grenze der 95 %-Konfidenzintervalle für die Koeffizienten der theoretischen linearen Regressionsgleichung.

VERBLEIBENDE RÜCKNAHME
Überwachung	Vorausgesagtes y	Bleibt e
	72,70	-29,70
	82,91	-20,91
	94,53	-4,53
	105,72	5,27
	117,56	12,44
	129,70	19,29
	144,22	20,77
	166,49	24,50
	268,13	-27,13

In der Tabelle VERBLEIBENDE AUSZAHLUNG angegeben:

in Spalte Überwachung- Beobachtungsnummer;

in Spalte Das vorhergesagte ja - berechnete Werte der abhängigen Variablen;

in Spalte Reste e - die Differenz zwischen den beobachteten und berechneten Werten der abhängigen Variablen.

Beispiel 3.6. Es gibt Daten (konventionelle Einheiten) zu Lebensmittelkosten ja und Pro-Kopf-Einkommen x für neun Familiengruppen:

x
ja

Lassen Sie uns anhand der Ergebnisse des Excel-Analysepakets (Regression) die Abhängigkeit der Lebensmittelkosten von der Höhe des Pro-Kopf-Einkommens analysieren.

Es ist üblich, die Ergebnisse der Regressionsanalyse in der Form zu schreiben:

wobei die Standardfehler der Regressionskoeffizienten in Klammern angegeben sind.

Regressionskoeffizienten ein = 65,92 und B= 0,107. Kommunikationsrichtung zwischen ja und x bestimmt das Vorzeichen des Regressionskoeffizienten B= 0,107, d. h. die Verbindung ist direkt und positiv. Koeffizient B= 0,107 zeigt, dass bei einem Anstieg des Pro-Kopf-Einkommens um 1 Conv. Einheiten Lebensmittelkosten steigen um 0,107 Conv. Einheiten

Schätzen wir die Signifikanz der Koeffizienten des resultierenden Modells ab. Die Bedeutung der Koeffizienten ( a, b) wird geprüft von T-Prüfung:

P-Wert ( ein) = 0,00080 < 0,01 < 0,05

P-Wert ( B) = 0,00016 < 0,01 < 0,05,

Daher sind die Koeffizienten ( a, b) sind auf dem 1 %-Niveau signifikant und noch mehr auf dem 5 %-Signifikanzniveau. Somit sind die Regressionskoeffizienten signifikant und das Modell entspricht den Originaldaten.

Die Ergebnisse der Regressionsschätzung sind nicht nur mit den erhaltenen Werten der Regressionskoeffizienten kompatibel, sondern auch mit einem Teil ihrer Menge (Konfidenzintervall). Mit einer Wahrscheinlichkeit von 95 % betragen die Konfidenzintervalle für die Koeffizienten (38,16 - 93,68) für ein und (0,0728 - 0,142) für B.

Die Qualität des Modells wird durch das Bestimmtheitsmaß bewertet R 2 .

Die Größenordnung R 2 = 0,884 bedeutet, dass 88,4% der Variation (Spreizung) der Nahrungsmittelausgaben durch den Faktor des Pro-Kopf-Einkommens erklärt werden können.

Bedeutung R 2 wird geprüft von F- Test: Bedeutung F = 0,00016 < 0,01 < 0,05, следовательно, R 2 ist auf dem 1%-Niveau signifikant und noch mehr auf dem 5%-Signifikanzniveau.

Bei gepaarter linearer Regression kann der Korrelationskoeffizient definiert werden als ... Der erhaltene Wert des Korrelationskoeffizienten weist darauf hin, dass der Zusammenhang zwischen Nahrungsmittelausgaben und Pro-Kopf-Einkommen sehr eng ist.