Einfache lineare Regression in Excel-Beispielen. Konrad Carlberg. Regressionsanalyse in Microsoft Excel

Thema: KORRELATIONS- UND REGRESSIONSANALYSE INAUSGEZEICHNET

LABORARBEIT Nr. 1

1. BESTIMMUNG DES PAARKORRELATIONSKOEFFIZIENTEN IM PROGRAMMAUSGEZEICHNET

Korrelation- Dies ist eine unvollständige, probabilistische Beziehung zwischen Indikatoren, die sich nur in der Masse der Beobachtungen manifestiert.

Paarweise Korrelation- Dies ist die Beziehung zwischen zwei Indikatoren, von denen einer faktoriell und der andere resultierend ist.

Mehrfachkorrelation entsteht durch das Zusammenspiel mehrerer Faktoren mit einem wirksamen Indikator.

Notwendige Voraussetzungen für den Einsatz der Korrelationsanalyse:

1. Das Vorliegen einer ausreichend großen Anzahl von Beobachtungen über den Wert der untersuchten Faktor- und Leistungsindikatoren.

2. Die untersuchten Faktoren müssen quantitativ gemessen und in bestimmten Informationsquellen widergespiegelt werden.

Durch den Einsatz der Korrelationsanalyse können wir folgende Probleme lösen:

1. Bestimmen Sie die Veränderung des Leistungsindikators unter dem Einfluss eines oder mehrerer Faktoren.

2. Ermitteln Sie den relativen Grad der Abhängigkeit des Leistungsindikators von jedem Faktor.

Übung 1.

Es liegen Daten für 20 landwirtschaftliche Betriebe vor. Finden Korrelationskoeffizient zwischen den Erträgen der Getreidekulturen und der Qualität des Bodens und bewerten seine Bedeutung. Die Daten sind in der Tabelle aufgeführt.

Tisch. Abhängigkeit des Getreideertrags von der Bodenqualität

Um den Korrelationskoeffizienten zu ermitteln, verwenden Sie die Funktion KORREL.

Anhand des Kriteriums wird die Signifikanz des Korrelationskoeffizienten überprüft Schülertest.

Für das betrachtete Beispiel gilt r=0,99, n=18.

Um das Quantil der Student-Verteilung zu ermitteln, verwenden Sie die Funktion STUDISCOVER mit den folgenden Argumenten: Wahrscheinlichkeit –0,05, Abschlüsse Freiheit –18.

Ziehen Sie durch Vergleich des Werts der t-Statistik mit dem Quantil der Student-Verteilung Rückschlüsse auf die Signifikanz des Paarkorrelationskoeffizienten. Wenn der berechnete Wert der t-Statistik größer als das Quantil der Student-Verteilung ist, ist der Wert des Korrelationskoeffizienten signifikant.

AUFBAU EINES REGRESSIONSMODELLS DER BEZIEHUNG ZWISCHEN ZWEI MENGEN

Aufgabe 2.

Gemäß Aufgabe 1:

1) Erstellen Sie eine Regressionsgleichung (lineares Modell), die die lineare Beziehung zwischen Landqualität und Produktivität charakterisiert;

2). Überprüfen Sie die Angemessenheit des resultierenden Modells.

1 - Weg.

1. Wählen Sie in einer Excel-Tabelle ein Array freier Zellen aus, das aus fünf Zeilen und zwei Spalten besteht.

2. Rufen Sie die Funktion auf LINEST.

3.Geben Sie die folgenden Argumente für die Funktion an: Izv_value_j Produktivität, c/ha;Izv_value_X- Spalte mit Indikatorwerten Grundstücksqualität, Punktzahl; Konstante –1, Statistik – 1(Ermöglicht die Berechnung von Indikatoren zur Überprüfung der Angemessenheit des Modells. Wenn Statistik – 0, dann werden solche Indikatoren nicht berechnet.

4. Drücken Sie die Tastenkombination Strg- Schicht- Eingeben.

In den ausgewählten Zellen werden die Modellkoeffizienten sowie Indikatoren angezeigt, mit denen Sie das Modell auf Angemessenheit überprüfen können (Tabelle 2).

A 1 , A 0 – Modellkoeffizienten;

S e 1 S e 0 – Standardfehler der Koeffizienten. Je genauer das Modell ist, desto kleiner sind diese Werte.

R 2 - Bestimmtheitsmaß. Je größer es ist, desto genauer ist das Modell.

F– Statistiken zum Testen der Aussagekraft des Modells.

N- k-1 – Anzahl der Freiheitsgrade (n-Stichprobengröße, k-Anzahl der Eingabevariablen; in diesem Beispiel n=20, k=1)

Q R– Quadratsumme aufgrund der Regression;

Q e– Summe der quadrierten Fehler.

5. Um die Angemessenheit des Modells zu überprüfen, ermitteln Sie das Quantil der Fisher-Verteilung F F . Verwendung der Funktion FENTDECKEN. Tragen Sie dazu die Funktion in eine beliebige freie Zelle ein FENTDECKEN mit folgenden Argumenten: Wahrscheinlichkeit – 0,05, Freiheitsgrade _1–1, Freiheitsgrade _2–18. Wenn F > F f , dann ist das Modell den Originaldaten angemessen

6. Überprüfen Sie die Angemessenheit des konstruierten Modells anhand des berechneten Signifikanzniveaus (P). Funktion eingeben FDIST mit folgenden Argumenten: X– Statistikwert F, Freiheitsgrade_1–1, Freiheitsgrade_2– 18. Wenn das berechnete Signifikanzniveau P<α =0,05, то модель адекватна исходным данным.

2. Methode.

Bestimmung von Modellkoeffizienten mit Erhalt von Indikatoren zur Überprüfung ihrer Angemessenheit und der Signifikanz der Koeffizienten.

Team auswählen Service/Datenanalyse/Regression. Stellen Sie im Dialogfeld Folgendes ein: EingabeintervallY– Indikatorwerte Produktivität, c/ha,EingabeintervallX– Indikatorwerte Grundstücksqualität, Punktzahl.

Kontrollkästchen Stichworte. Im Gebiet Ausgabeoptionen Optionsfeld auswählen Ausgabeintervall und geben Sie die Zelle an, in der die Ergebnisse beginnen sollen. Um die Ergebnisse zu erhalten, klicken Sie auf OK.

Interpretation der Ergebnisse.

Die erforderlichen Modellkoeffizienten finden Sie in der Spalte Chancen:

Für dieses Beispiel lautet die Modellgleichung:

Y=2,53+0,5X

In diesem Beispiel erhöht sich bei einer Steigerung der Bodenqualität um einen Punkt der Getreideertrag um durchschnittlich 0,5 c/ha.

Überprüfung der Angemessenheit des Modells wird mit dem in der Spalte angegebenen berechneten Signifikanzniveau P durchgeführt BedeutungF. Wenn das berechnete Signifikanzniveau kleiner als das angegebene Signifikanzniveau α = 0,05 ist, ist das Modell angemessen.

Prüfung auf statistische Signifikanz Die Berechnung der Modellkoeffizienten erfolgt anhand der berechneten Signifikanzniveaus P, die in der Spalte angegeben sind P-Bedeutung. Wenn das berechnete Signifikanzniveau kleiner als das angegebene Signifikanzniveau α = 0,05 ist, ist der entsprechende Modellkoeffizient statistisch signifikant.

MehrereR – Korrelationskoeffizient. Je näher sein Wert bei 1 liegt, desto enger ist die Beziehung zwischen den untersuchten Indikatoren. Für dieses Beispiel ist R= 0,99. Dies lässt den Schluss zu, dass die Qualität des Bodens einer der Hauptfaktoren ist, von denen der Ertrag von Getreidekulturen abhängt.

R-Quadrat – Bestimmtheitsmaß. Er wird durch Quadrieren des Korrelationskoeffizienten erhalten – R 2 =0,98. Es zeigt, dass 98 % der Getreideerträge von der Bodenqualität abhängen, während andere Faktoren 0,02 % ausmachen.

3. Methode. GRAFISCHE METHODE ZUM ERSTELLEN EINES MODELLS.

Erstellen Sie selbst ein Streudiagramm, das den Zusammenhang zwischen Ertrag und Bodenqualität zeigt.

Erhalten Sie ein lineares Modell der Abhängigkeit des Getreideertrags von der Landqualität.

Es ist dafür bekannt, dass es in verschiedenen Tätigkeitsbereichen nützlich ist, einschließlich einer Disziplin wie der Ökonometrie, in der dieses Software-Dienstprogramm bei der Arbeit eingesetzt wird. Grundsätzlich werden alle Aktionen des Praxis- und Laborunterrichts in Excel durchgeführt, was die Arbeit durch detaillierte Erläuterungen zu bestimmten Aktionen erheblich erleichtert. Daher wird eines der Analysewerkzeuge „Regression“ verwendet, um mithilfe der Methode der kleinsten Quadrate ein Diagramm für eine Reihe von Beobachtungen auszuwählen. Schauen wir uns an, was dieses Programmtool ist und welche Vorteile es für Benutzer hat. Nachfolgend finden Sie auch eine kurze, aber klare Anleitung zum Aufbau eines Regressionsmodells.

Hauptaufgaben und Arten der Regression

Die Regression stellt die Beziehung zwischen gegebenen Variablen dar und ermöglicht so die Vorhersage des zukünftigen Verhaltens dieser Variablen. Variablen sind verschiedene periodische Phänomene, darunter auch menschliches Verhalten. Diese Art der Excel-Analyse wird verwendet, um die Auswirkung der Werte einer oder mehrerer Variablen auf eine bestimmte abhängige Variable zu analysieren. Beispielsweise werden die Verkäufe in einem Geschäft von mehreren Faktoren beeinflusst, darunter Sortiment, Preise und Standort des Geschäfts. Dank der Regression in Excel können Sie den Grad des Einflusses jedes dieser Faktoren anhand der Ergebnisse bestehender Verkäufe ermitteln und die erhaltenen Daten dann auf die Umsatzprognose für einen anderen Monat oder für ein anderes Geschäft in der Nähe anwenden.

Typischerweise wird die Regression als eine einfache Gleichung dargestellt, die die Beziehungen und Stärken der Beziehungen zwischen zwei Variablengruppen offenlegt, wobei eine Gruppe abhängig oder endogen und die andere unabhängig oder exogen ist. Wenn es eine Gruppe miteinander verbundener Indikatoren gibt, wird die abhängige Variable Y auf der Grundlage der Argumentationslogik bestimmt, und der Rest fungiert als unabhängige X-Variablen.

Die Hauptaufgaben beim Aufbau eines Regressionsmodells sind wie folgt:

1. Auswahl signifikanter unabhängiger Variablen (X1, X2, ..., Xk).
2. Auswahl der Funktionsart.
3. Erstellen von Schätzungen für Koeffizienten.
4. Konstruktion von Konfidenzintervallen und Regressionsfunktionen.
5. Überprüfung der Signifikanz der berechneten Schätzungen und der erstellten Regressionsgleichung.

Es gibt verschiedene Arten der Regressionsanalyse:

• gepaart (1 abhängige und 1 unabhängige Variable);
• multiple (mehrere unabhängige Variablen).

Es gibt zwei Arten von Regressionsgleichungen:

1. Linear: Veranschaulichung einer streng linearen Beziehung zwischen Variablen.
2. Nichtlinear – Gleichungen, die Potenzen, Brüche und trigonometrische Funktionen umfassen können.

Anleitung zum Bau eines Modells

Um eine bestimmte Konstruktion in Excel auszuführen, müssen Sie den Anweisungen folgen:

Für weitere Berechnungen verwenden Sie die Funktion „Linear()“ und geben Y-Werte, X-Werte, Konstante und Statistiken an. Bestimmen Sie anschließend die Menge der Punkte auf der Regressionsgeraden mit der Funktion „Trend“ – Y-Werte, X-Werte, Neue Werte, Konst. Berechnen Sie anhand der angegebenen Parameter den unbekannten Wert der Koeffizienten, basierend auf den gegebenen Bedingungen des Problems.

Auch die statistische Datenverarbeitung kann über ein Add-on erfolgen ANALYSEPAKET(Abb. 62).

Wählen Sie aus den vorgeschlagenen Artikeln den Artikel „ REGRESSION" und klicken Sie mit der linken Maustaste darauf. Klicken Sie anschließend auf OK.

Es erscheint ein Fenster wie in Abb. 63.

Analysetool " REGRESSION» wird verwendet, um ein Diagramm mithilfe der Methode der kleinsten Quadrate an eine Reihe von Beobachtungen anzupassen. Regression wird verwendet, um die Auswirkung der Werte einer oder mehrerer unabhängiger Variablen auf eine einzelne abhängige Variable zu analysieren. Beispielsweise beeinflussen mehrere Faktoren die sportliche Leistung eines Sportlers, darunter Alter, Größe und Gewicht. Es ist möglich, den Grad zu berechnen, in dem jeder dieser drei Faktoren die Leistung eines Sportlers beeinflusst, und diese Daten dann zu verwenden, um die Leistung eines anderen Sportlers vorherzusagen.

Das Regressionstool 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 Kopfzeilen vorhanden sind. In diesem Fall werden automatisch passende Header für die Ausgabetabellendaten erstellt.

Zuverlässigkeitsstufe Aktivieren Sie das Kontrollkästchen, um eine zusätzliche Stufe in die Ausgabeübersichtstabelle aufzunehmen. Geben Sie im entsprechenden Feld zusätzlich zum Standardniveau von 95 % das Konfidenzniveau ein, das Sie anwenden möchten.

Konstante – Null Aktivieren Sie das Kontrollkästchen, um zu erzwingen, dass die Regressionslinie durch den Ursprung verläuft.

Ausgabebereich Geben Sie den Verweis auf die obere linke Zelle des Ausgabebereichs ein. Stellen Sie mindestens sieben Spalten für die Ausgabezusammenfassungstabelle bereit, die Folgendes enthält: ANOVA-Ergebnisse, Koeffizienten, Standardfehler der Y-Berechnung, Standardabweichungen, Anzahl der Beobachtungen, Standardfehler für Koeffizienten.

Neues Arbeitsblatt Wählen Sie diese Option aus, um ein neues Arbeitsblatt in der Arbeitsmappe zu öffnen und die Analyseergebnisse beginnend in Zelle A1 einzufügen. Geben Sie bei Bedarf einen Namen für das neue Blatt in das Feld gegenüber dem entsprechenden Optionsfeld ein.

Neue Arbeitsmappe Wählen Sie diese Option aus, um eine neue Arbeitsmappe zu erstellen und die Ergebnisse einem neuen Arbeitsblatt hinzuzufügen.

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

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

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

Diagramm anpassen Aktivieren Sie das Kontrollkästchen, um die vorhergesagten gegenüber den beobachteten Werten darzustellen.

Normales Wahrscheinlichkeitsdiagramm Aktivieren Sie das Kontrollkästchen, um ein normales Wahrscheinlichkeitsdiagramm zu zeichnen.

Funktion LINEST

Um Berechnungen durchzuführen, wählen Sie mit dem Cursor die Zelle aus, in der wir den Durchschnittswert anzeigen möchten, und drücken Sie die Taste = auf der Tastatur. Geben Sie anschließend im Feld Name beispielsweise die gewünschte Funktion an DURCHSCHNITT(Abb. 22).

Funktion LINEST Berechnet Statistiken für eine Reihe mithilfe der Methode der kleinsten Quadrate, um die gerade Linie zu berechnen, die den verfügbaren Daten am besten entspricht, 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 linear sind (deren unbekannte Parameter linear sind), einschließlich Polynom-, Logarithmus-, Exponential- 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:

y=m 1 x 1 +m 2 x 2 +…+b (bei mehreren Bereichen von x-Werten),

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

LINEST(bekannte_Werte_y; bekannte_Werte_x; const; Statistik)

Bekannte_y_Werte – ein Satz von y-Werten, die für die Beziehung y=mx+b bereits bekannt sind.

Wenn das Array „known_y_values“ eine Spalte hat, wird jede Spalte im Array „known_x_values“ als separate Variable behandelt.

Wenn das Array „known_y_values“ eine Zeile hat, wird jede Zeile im Array „known_x_values“ als separate Variable behandelt.

Bekannte_x-Werte sind ein optionaler Satz von x-Werten, die für die Beziehung y=mx+b bereits bekannt sind.

Das Arrayknown_x_values ​​​​kann einen oder mehrere Variablensätze enthalten. Wenn nur eine Variable verwendet wird, können die Arrays „known_y_values“ und „known_x_values“ jede beliebige Form haben – solange sie die gleiche Dimension haben. Wenn mehr als eine Variable verwendet wird, müssen bekannte_y_Werte ein Vektor sein (d. h. ein Intervall mit einer Zeile Höhe oder einer Spalte Breite).

Wenn array_known_x_values ​​​​weggelassen wird, wird davon ausgegangen, dass das Array (1;2;3;...) dieselbe Größe wie array_known_values_y hat.

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

Wenn das Argument „const“ TRUE ist oder weggelassen wird, wird die Konstante b wie gewohnt ausgewertet.

Wenn das Argument „const“ FALSE ist, dann wird der Wert von b auf 0 gesetzt und die Werte von m werden so ausgewählt, dass die Beziehung y=mx erfüllt ist.

Statistiken – Ein boolescher Wert, der angibt, ob zusätzliche Regressionsstatistiken zurückgegeben werden sollen.

Wenn Statistiken TRUE sind, gibt LINEST zusätzliche Regressionsstatistiken zurück. Das zurückgegebene Array sieht folgendermaßen aus: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid).

Wenn Statistiken FALSE sind oder weggelassen werden, gibt LINEST nur die Koeffizienten m und die Konstante b zurück.

Zusätzliche Regressionsstatistiken (Tabelle 17)

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

Anmerkungen:

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

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

Y-Achsenabschnitt (b): Der y-Achsenabschnitt einer Linie, normalerweise mit b bezeichnet, ist der y-Wert für den Punkt, an dem die Linie die y-Achse schneidet.

Die Gleichung der Geraden lautet y=mx+b. Wenn die Werte von m und b bekannt sind, kann jeder Punkt auf der Linie berechnet werden, indem die Werte von y oder x in die Gleichung eingesetzt werden. 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 mithilfe der folgenden Formeln ermitteln:

Steigung: INDEX(LINEST(known_y_values;known_x_values); 1)

Y-Achsenabschnitt: INDEX(LINEST(known_y_values;known_x_values); 2)

Die Genauigkeit der Näherung mithilfe der durch die LINEST-Funktion berechneten Geraden hängt vom Grad der Datenstreuung ab. Je näher die Daten an einer geraden Linie liegen, desto genauer ist das von der LINEST-Funktion verwendete Modell. Die LINEST-Funktion verwendet die Methode der kleinsten Quadrate, um die beste Anpassung an die Daten zu ermitteln. Wenn es nur eine unabhängige Variable x gibt, werden m und b nach den folgenden Formeln berechnet:

Dabei sind x und y Beispielmittelwerte, zum Beispiel x = AVERAGE(known_x's) und y = AVERAGE(known_y's).

Die Anpassungsfunktionen LINEST und LGRFPRIBL können die gerade Linie oder Exponentialkurve berechnen, die am besten zu den Daten passt. Sie beantworten jedoch nicht die Frage, welches der beiden Ergebnisse besser zur Lösung des Problems geeignet ist. Sie können auch die Funktion TREND(bekannte_y's; bekannte_x's) für eine gerade Linie oder die GROW(bekannte_y's; bekannte_x's)-Funktion für eine Exponentialkurve auswerten. Sofern keine neuen_x-Werte angegeben sind, geben diese Funktionen ein Array berechneter y-Werte für die tatsächlichen x-Werte entlang einer Linie oder Kurve zurück. Anschließend können Sie die berechneten Werte mit den tatsächlichen Werten vergleichen. Sie können auch Diagramme zum visuellen Vergleich erstellen.

Bei der Durchführung einer Regressionsanalyse berechnet Microsoft Excel 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. Anschließend berechnet Microsoft Excel die Gesamtsumme der Quadrate (sstotal). Wenn const = TRUE oder der Wert dieses Arguments nicht angegeben ist, ist die Gesamtsumme der Quadrate gleich der Summe der Quadrate der Differenzen zwischen den tatsächlichen y-Werten und den durchschnittlichen y-Werten. Wenn const = FALSE ist, ist die Gesamtsumme der Quadrate gleich der Summe der Quadrate der realen y-Werte (ohne Subtraktion des durchschnittlichen y-Werts vom partiellen y-Wert). Die Regressionsquadratsumme kann dann wie folgt berechnet werden: ssreg = sstotal – ssresid. Je kleiner die Restquadratsumme ist, desto größer ist der Wert des Bestimmtheitsmaßes r2, der zeigt, wie gut die mithilfe der Regressionsanalyse erhaltene Gleichung die Beziehungen zwischen Variablen erklärt. Der Koeffizient r2 ist gleich ssreg/sstotal.

In einigen Fällen haben eine oder mehrere X-Spalten (seien Y- und X-Werte in Spalten) keinen zusätzlichen prädikativen Wert in anderen die gleiche Präzision. In diesem Fall werden die redundanten X-Spalten aus dem Regressionsmodell ausgeschlossen. Dieses Phänomen wird „Kollinearität“ genannt, da die redundanten Spalten von X als Summe mehrerer nicht redundanter Spalten dargestellt werden können. Die LINEST-Funktion prüft auf Kollinearität und entfernt alle redundanten X-Spalten aus dem Regressionsmodell, wenn sie diese erkennt. Entfernte Weitere Informationen zur Berechnung von df finden Sie in Beispiel 4 unten. Wenn sich df aufgrund der Entfernung redundanter Spalten ändert, ändern sich auch die Werte von sey und F. Es wird nicht empfohlen, Kollinearität häufig zu verwenden. Es sollte jedoch verwendet werden, wenn einige X-Spalten 0 oder 1 enthalten, als Indikator dafür, ob der Versuchsgegenstand zu einer separaten Gruppe gehört. Wenn const = TRUE oder kein Wert für dieses Argument angegeben ist, fügt LINEST eine zusätzliche X-Spalte ein, um den Schnittpunkt zu modellieren. Wenn es eine Spalte mit den Werten 1 für Männer und 0 für Frauen und eine Spalte mit den Werten 1 für Frauen und 0 für Männer gibt, wird die letzte Spalte entfernt, da ihre Werte abgerufen werden können aus der Spalte „männlicher Indikator“.

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

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

Wenn Sie ein Array von Konstanten als Argument eingeben, zum Beispiel „known_x_values“, sollten Sie ein Semikolon verwenden, um Werte in derselben Zeile zu trennen, und einen Doppelpunkt, um Zeilen zu trennen. Die Trennzeichen können je nach den Einstellungen im Fenster „Sprache und Einstellungen“ in der Systemsteuerung variieren.

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.

Grundlegender Algorithmus, der in der Funktion verwendet wird LINEST, unterscheidet sich vom Hauptfunktionsalgorithmus NEIGUNG Und LINIENSEGMENT. Der Unterschied zwischen Algorithmen kann bei unsicheren und kollinearen Daten zu unterschiedlichen Ergebnissen führen. Wenn beispielsweise die Datenpunkte des Arguments „known_y_values“ 0 und die Datenpunkte des Arguments „known_x_values“ 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 diesem Fall kann mindestens eine Antwort gefunden werden.

Die Funktionen SLOPE und LINE geben den Fehler #DIV/0! zurück. Der Algorithmus der Funktionen SLOPE und INTERCEPT wird verwendet, um nur eine Antwort zu finden, in diesem Fall kann es jedoch mehrere geben.

Zusätzlich zur Berechnung von Statistiken für andere Regressionstypen kann LINEST zur Berechnung von Bereichen für andere Regressionstypen verwendet werden, indem Funktionen der x- und y-Variablen als Reihen der x- und y-Variablen für LINEST eingegeben werden. Zum Beispiel die folgende Formel:

LINEST(y_values, x_values^COLUMN($A:$C))

arbeitet mit einer Spalte mit Y-Werten und einer Spalte mit X-Werten, um eine Würfelnäherung (Polynom 3. Grades) der folgenden Form zu berechnen:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

Die Formel kann geändert werden, um andere Regressionstypen zu berechnen. In einigen Fällen müssen jedoch möglicherweise die Ausgabewerte und andere Statistiken angepasst werden.

Regressions- und Korrelationsanalyse sind statistische Forschungsmethoden. Dies sind die gebräuchlichsten Methoden, um die Abhängigkeit eines Parameters von einer oder mehreren unabhängigen Variablen darzustellen.

Im Folgenden werden wir anhand konkreter praktischer Beispiele diese beiden unter Wirtschaftswissenschaftlern sehr beliebten Analysen betrachten. Wir geben auch ein Beispiel für die Erzielung von Ergebnissen, wenn man sie kombiniert.

Regressionsanalyse in Excel

Zeigt den Einfluss einiger Werte (unabhängig, unabhängig) auf die abhängige Variable. Wie hängt beispielsweise die Zahl der Erwerbsbevölkerung von der Zahl der Unternehmen, den Löhnen 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 es Ihnen, Prioritäten hervorzuheben. Und basierend auf den Hauptfaktoren können Sie die Entwicklung vorrangiger Bereiche vorhersagen, planen und Managemententscheidungen treffen.

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).

Schauen wir uns ein Beispiel für die Erstellung eines Regressionsmodells in Excel und die Interpretation der Ergebnisse an. Nehmen wir die lineare Art der Regression.

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

Das lineare Regressionsmodell sieht folgendermaßen aus:

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

Dabei sind a Regressionskoeffizienten, x Einflussvariablen und k die Anzahl der Faktoren.

In unserem Beispiel ist Y der Indikator für das Ausscheiden von Mitarbeitern. Der Einflussfaktor ist der Lohn (x).

Excel verfügt über integrierte Funktionen, die Ihnen bei der Berechnung der Parameter eines linearen Regressionsmodells helfen können. Das Add-on „Analysis Package“ erledigt dies jedoch schneller.

Wir aktivieren ein leistungsstarkes Analysetool:

Nach der Aktivierung ist das Add-on auf der Registerkarte „Daten“ verfügbar.

Lassen Sie uns nun die Regressionsanalyse selbst durchführen.

Zunächst achten wir auf R-Quadrat und Koeffizienten.

R-Quadrat ist das Bestimmtheitsmaß. In unserem Beispiel – 0,755 oder 75,5 %. Dies bedeutet, dass die berechneten Parameter des Modells 75,5 % des Zusammenhangs zwischen den untersuchten Parametern erklären. Je höher das Bestimmtheitsmaß, desto besser 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, wie Y aussehen wird, wenn alle Variablen im betrachteten Modell gleich 0 sind. Das heißt, der Wert des analysierten Parameters wird auch von anderen Faktoren beeinflusst, die nicht im Modell beschrieben sind.

Der Koeffizient -0,16285 zeigt das Gewicht der Variablen Das „-“-Zeichen weist auf eine negative Auswirkung hin: Je höher das Gehalt, desto weniger Menschen kündigen. Was fair ist.

Korrelationsanalyse in Excel

Die Korrelationsanalyse hilft festzustellen, ob ein Zusammenhang zwischen Indikatoren in einer oder zwei Stichproben besteht. Zum Beispiel zwischen der Betriebszeit einer Maschine und den Reparaturkosten, dem Preis der Ausrüstung und der Betriebsdauer, der Größe und dem Gewicht von Kindern usw.

Wenn ein Zusammenhang besteht, dann führt eine Erhöhung eines Parameters zu einer Erhöhung (positive Korrelation) oder einer Verringerung (negativ) des anderen. Mithilfe der Korrelationsanalyse kann der Analyst feststellen, ob der Wert eines Indikators zur Vorhersage des möglichen Werts eines anderen Indikators verwendet werden kann.

Der Korrelationskoeffizient wird mit r bezeichnet. Variiert von +1 bis -1. Die Klassifizierung der Korrelationen für verschiedene Bereiche wird unterschiedlich sein. Wenn der Koeffizient 0 ist, besteht keine lineare Beziehung zwischen den Stichproben.

Schauen wir uns an, wie Sie den Korrelationskoeffizienten mit Excel ermitteln.

Um gepaarte Koeffizienten zu finden, wird die CORREL-Funktion verwendet.

Ziel: Bestimmen Sie, ob ein Zusammenhang zwischen der Betriebszeit einer Drehmaschine und den Kosten für deren Wartung besteht.

Platzieren Sie den Cursor in einer beliebigen Zelle und drücken Sie die FX-Taste.

1. Wählen Sie in der Kategorie „Statistisch“ die Funktion CORREL aus.
2. Argument „Array 1“ – der erste Wertebereich – Maschinenbetriebszeit: A2:A14.
3. Argument „Array 2“ – zweiter Wertebereich – Reparaturkosten: B2:B14. OK klicken.

Um die Art der Verbindung zu bestimmen, müssen Sie sich die absolute Zahl des Koeffizienten ansehen (jeder Tätigkeitsbereich hat seine eigene Skala).

Für die Korrelationsanalyse mehrerer Parameter (mehr als 2) ist es bequemer, „Data Analysis“ (das Add-on „Analysis Package“) zu verwenden. Sie müssen Korrelation aus der Liste auswählen und das Array festlegen. Alle.

Die resultierenden Koeffizienten werden in der Korrelationsmatrix angezeigt. So was:

Korrelations- und Regressionsanalyse

In der Praxis werden diese beiden Techniken häufig gemeinsam eingesetzt.

Beispiel:

Jetzt sind die Daten der Regressionsanalyse sichtbar.

Zeigt den Einfluss einiger Werte (unabhängig, unabhängig) auf die abhängige Variable. Wie hängt beispielsweise die Zahl der Erwerbsbevölkerung von der Zahl der Unternehmen, den Löhnen 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 es Ihnen, Prioritäten hervorzuheben. Und basierend auf den Hauptfaktoren können Sie die Entwicklung vorrangiger Bereiche vorhersagen, planen und Managemententscheidungen treffen.

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).

Schauen wir uns ein Beispiel für die Erstellung eines Regressionsmodells in Excel und die Interpretation der Ergebnisse an. Nehmen wir die lineare Art der Regression.

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

Das lineare Regressionsmodell sieht folgendermaßen aus:

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

Dabei sind a Regressionskoeffizienten, x Einflussvariablen und k die Anzahl der Faktoren.

In unserem Beispiel ist Y der Indikator für das Ausscheiden von Mitarbeitern. Der Einflussfaktor ist der Lohn (x).

Excel verfügt über integrierte Funktionen, die Ihnen bei der Berechnung der Parameter eines linearen Regressionsmodells helfen können. Das Add-on „Analysis Package“ erledigt dies jedoch schneller.

Wir aktivieren ein leistungsstarkes Analysetool:

1. Klicken Sie auf die Schaltfläche „Office“ und wechseln Sie zur Registerkarte „Excel-Optionen“. „Add-ons“.

2. Unten, unter der Dropdown-Liste, befindet sich im Feld „Verwalten“ die Aufschrift „Excel-Add-Ins“ (falls nicht vorhanden, klicken Sie auf das Kontrollkästchen rechts und wählen Sie es aus). Und der „Los“-Button. Klicken.

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

Nach der Aktivierung ist das Add-on auf der Registerkarte „Daten“ verfügbar.

Lassen Sie uns nun die Regressionsanalyse selbst durchführen.

1. Öffnen Sie das Menü des Tools „Datenanalyse“. Wählen Sie „Regression“.

2. Es öffnet sich ein Menü zur Auswahl von Eingabewerten und Ausgabeoptionen (wo das Ergebnis angezeigt werden soll). In den Feldern für die Ausgangsdaten geben wir den Bereich des beschriebenen Parameters (Y) und den ihn beeinflussenden Faktor (X) an. Der Rest muss nicht ausgefüllt werden.

3. Nachdem Sie auf „OK“ geklickt haben, zeigt das Programm die Berechnungen auf einem neuen Blatt an (Sie können ein Intervall auswählen, das auf dem aktuellen Blatt angezeigt werden soll, oder die Ausgabe einer neuen Arbeitsmappe zuweisen).

Zunächst achten wir auf R-Quadrat und Koeffizienten.

R-Quadrat ist das Bestimmtheitsmaß. In unserem Beispiel – 0,755 oder 75,5 %. Dies bedeutet, dass die berechneten Parameter des Modells 75,5 % des Zusammenhangs zwischen den untersuchten Parametern erklären. Je höher das Bestimmtheitsmaß, desto besser 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, wie Y aussehen wird, wenn alle Variablen im betrachteten Modell gleich 0 sind. Das heißt, der Wert des analysierten Parameters wird auch von anderen Faktoren beeinflusst, die nicht im Modell beschrieben sind.

Der Koeffizient -0,16285 zeigt das Gewicht der Variablen Das „-“-Zeichen weist auf eine negative Auswirkung hin: Je höher das Gehalt, desto weniger Menschen kündigen. Was fair ist.