Mehrfache nichtlineare Regression in Excel. Grundlegende Regressionsaufgaben in Excel: Ein Beispiel für die Erstellung eines Modells

Mit MS Excel können Sie eine Gleichung erstellen lineare Regression Die meiste Arbeit ist sehr schnell erledigt. Es ist wichtig zu verstehen, wie die erhaltenen Ergebnisse zu interpretieren sind. Um ein Regressionsmodell zu erstellen, wählen Sie das Element Dienst \ Datenanalyse \ Regression (in Excel 2007 befindet sich dieser Modus im Abschnitt Daten / Datenanalyse / Regression). Kopieren Sie dann die erhaltenen Ergebnisse zur Analyse in den Block.

Ausgangsdaten:

Analyseergebnisse

In Bericht aufnehmen
Berechnung der Parameter der Regressionsgleichung
Theoretisches Material
Regressionsgleichung auf Standardskala
Mehrfachkorrelationskoeffizient (Mehrfachkorrelationsindex)
Partielle Elastizitätskoeffizienten
Vergleichende Bewertung des Einflusses der analysierten Faktoren auf den effektiven Indikator (d - Einzelbestimmungskoeffizienten)

Überprüfung der Qualität der konstruierten Regressionsgleichung
Bedeutung der Regressionskoeffizienten b i (t-Statistik. Schülertest)
Bedeutung der Gleichung als Ganzes (F-Statistik. Fisher-Kriterium). Bestimmungskoeffizient
Private F-Tests

Signifikanzniveau 0.005 0.01 0.025 0.05 0.1 0.25 0.4

IM Excel Es gibt eine noch schnellere und bequemere Möglichkeit, lineare Regressionen (und sogar grundlegende Arten nichtlinearer Regressionen, wie unten beschrieben) darzustellen. Dies kann wie folgt erfolgen:

1) Spalten mit Daten auswählen X und Ja(Sie müssen in dieser Reihenfolge sein!);

2) anrufen Diagrammassistent und in der Gruppe auswählen Eine Art – Punkt und drücke sofort Getan;

3) ohne die Auswahl aus dem Diagramm zu löschen, wählen Sie das erscheinende Element des Hauptmenüs Diagramm, in dem Sie den Artikel auswählen sollten Trendlinie hinzufügen;

4) im angezeigten Dialogfeld Trendlinie in der Registerkarte Eine Art wählen Linear;

5) in der Registerkarte Parameter der Schalter kann aktiviert werden Gleichung im Diagramm anzeigen, wodurch Sie die lineare Regressionsgleichung (4.4) sehen können, in der die Koeffizienten (4.5) berechnet werden.

6) In derselben Registerkarte können Sie den Schalter aktivieren activate Setzen Sie den Näherungswert (R ^ 2) auf das Diagramm... Diese Größe ist das Quadrat des Korrelationskoeffizienten (4.3) und zeigt, wie gut die berechnete Gleichung die experimentelle Abhängigkeit beschreibt. Wenn ein R 2 nahe Eins ist, dann beschreibt die theoretische Regressionsgleichung die experimentelle Abhängigkeit gut (die Theorie stimmt gut mit dem Experiment überein), und wenn R 2 ist nahe Null, dann gegebene Gleichung nicht geeignet zur Beschreibung der experimentellen Abhängigkeit (Theorie stimmt nicht mit Experiment überein).

Als Ergebnis der beschriebenen Aktionen erhalten Sie ein Diagramm mit einem Regressionsgraphen und seiner Gleichung.

§4.3. Die wichtigsten Arten der nichtlinearen Regression

Parabolische und polynomielle Regression.

Parabolisch Abhängigkeit der Menge Ja auf den Wert X heißt die durch eine quadratische Funktion (Parabel 2. Ordnung) ausgedrückte Abhängigkeit:

Diese Gleichung heißt parabolische Regressionsgleichung Y auf der X... Parameter aber, b, von werden genannt parabolische Regressionskoeffizienten... Die Berechnung von parabolischen Regressionskoeffizienten ist immer umständlich, daher wird empfohlen, für Berechnungen einen Computer zu verwenden.

Gleichung (4.8) der parabolischen Regression ist ein Spezialfall einer allgemeineren Regression namens Polynom. Polynom Abhängigkeit der Menge Ja auf den Wert X heißt die durch das Polynom ausgedrückte Abhängigkeit nein-te Bestellung:

wo sind die zahlen und ich (ich=0,1,…, nein) werden genannt polynomiale Regressionskoeffizienten.

Leistungsregression.

Exponentiell Abhängigkeit der Menge Ja auf den Wert X die Abhängigkeit der Form heißt:

Diese Gleichung heißt Potenzregressionsgleichung Y auf der X... Parameter aber und b werden genannt Potenzregressionskoeffizienten.

ln = ln ein+b ln x. (4.11)

Diese Gleichung beschreibt eine Gerade in einer Ebene mit logarithmischen Koordinatenachsen ln x und ln. Kriterium für die Anwendbarkeit der Potenzregression ist daher die Forderung, dass die Punkte der Logarithmen der empirischen Daten ln x ich und ln ich waren der Geraden (4.11) am nächsten.

Exponentielle Regression.

Indikativ(oder exponentiell) durch die Abhängigkeit der Größe Ja auf den Wert X die Abhängigkeit der Form heißt:

(oder ). (4.12)

Diese Gleichung heißt die Exponentialgleichung(oder exponentiell) Regression Y auf der X... Parameter aber(oder k) und b werden genannt exponentiell(oder exponentiell) Rückschritte.

Wenn wir den Logarithmus beider Seiten der Potenzregressionsgleichung nehmen, erhalten wir die Gleichung

ln = x ln ein+ ln b(oder ln = k x+ ln b). (4.13)

Diese Gleichung beschreibt lineare Beziehung Logarithmus einer Größe ln von einer anderen Größe x... Kriterium für die Anwendbarkeit der Potenzregression ist daher die Forderung, dass die empirischen Datenpunkte gleicher Größe x ich und die Logarithmen einer anderen Größe ln ich waren der Geraden (4.13) am nächsten.

Logarithmische Regression.

Logarithmisch Abhängigkeit der Menge Ja auf den Wert X die Abhängigkeit der Form heißt:

=ein+b ln x. (4.14)

Diese Gleichung heißt logarithmische Regressionsgleichung Y auf der X... Parameter aber und b werden genannt logarithmische Regressionskoeffizienten.

Hyperbolische Regression.

Hyperbolisch Abhängigkeit der Menge Ja auf den Wert X die Abhängigkeit der Form heißt:

Diese Gleichung heißt hyperbolische Regressionsgleichung Y auf der X... Parameter aber und b werden genannt hyperbolische Regressionskoeffizienten und werden nach der Methode der kleinsten Quadrate bestimmt. Die Anwendung dieser Methode führt zu den Formeln:

In Formeln (4.16-4.17) erfolgt die Summation über den Index ich von einer bis zur Anzahl der Beobachtungen nein.

Leider in Excel Es gibt keine Funktion, die die Koeffizienten der hyperbolischen Regression berechnet. In Fällen, in denen nicht vorher bekannt ist, dass die gemessenen Größen umgekehrt proportional zueinander sind, wird empfohlen, anstelle der hyperbolischen Regressionsgleichung nach der Potenzregressionsgleichung zu suchen, wie in Excel Es gibt ein Verfahren, um es zu finden. Wird zwischen den Messwerten eine hyperbolische Abhängigkeit angenommen, so müssen die Koeffizienten ihrer Regression mit Hilfe von Rechenhilfstabellen und Summationsoperationen mit Formeln (4.16-4.17) berechnet werden.

Regressionsanalyse- Dies ist eine statistische Forschungsmethode, mit der Sie die Abhängigkeit eines Parameters von einer oder mehreren unabhängigen Variablen zeigen können. In der Vor-Computer-Ära war seine Anwendung eher schwierig, insbesondere wenn es um große Datenmengen ging. Nachdem Sie heute gelernt haben, eine Regression in Excel zu erstellen, können Sie komplexe statistische Probleme in nur wenigen Minuten lösen. Unten sind die konkrete Beispiele aus dem Bereich der Wirtschaftswissenschaften.

Regressionstypen

Das Konzept wurde 1886 in die Mathematik eingeführt. Regression geschieht:

• linear;
• parabolisch;
• Machtgesetz;
• exponentiell;
• hyperbolisch;
• indikativ;
• logarithmisch.

Beispiel 1

Betrachten wir das Problem der Ermittlung der Abhängigkeit der Zahl der Arbeitnehmer, die ihren Arbeitsplatz aufgeben, vom Durchschnittsgehalt in 6 Industrieunternehmen.

Eine Aufgabe. Sechs Unternehmen analysierten das durchschnittliche Monatsgehalt und die Zahl der Mitarbeiter, die freiwillig gekündigt haben. In tabellarischer Form haben wir:

		Anzahl der Austritte	Das Gehalt
			30.000 Rubel
			35.000 Rubel
			40.000 Rubel
			45.000 Rubel
			50.000 Rubel
			55.000 Rubel
			60.000 Rubel

Für das Problem der Ermittlung der Abhängigkeit der Anzahl gekündigter Arbeitnehmer vom Durchschnittsgehalt in 6 Unternehmen hat das Regressionsmodell die Form der Gleichung Y = a 0 + a 1 x 1 + ... + akxk, wobei xi die Einflussgrößen, ai sind die Regressionskoeffizienten und ak ist die Anzahl der Faktoren.

Für diese Aufgabe ist Y der Indikator für die ausscheidenden Mitarbeiter und der Einflussfaktor ist das Gehalt, das wir mit X bezeichnen.

Nutzung der Fähigkeiten des Excel-Tabellenprozessors

Der Regressionsanalyse in Excel muss die Anwendung integrierter Funktionen auf die vorhandenen Tabellendaten vorausgehen. Für diese Zwecke ist es jedoch besser, das sehr nützliche Add-In "Analysepaket" zu verwenden. Um es zu aktivieren, benötigen Sie:

• Gehen Sie von der Registerkarte "Datei" zum Abschnitt "Parameter";
• Wählen Sie im sich öffnenden Fenster die Zeile "Add-Ins" aus;
• Klicken Sie unten rechts neben der Zeile "Steuerung" auf die Schaltfläche "Los".
• Setzen Sie ein Häkchen neben dem Namen „Analysepaket“ und bestätigen Sie Ihre Aktionen mit „OK“.

Wenn alles richtig gemacht wurde, erscheint die erforderliche Schaltfläche auf der rechten Seite der Registerkarte "Daten", die sich über dem Arbeitsblatt "Excel" befindet.

in Excel

Nachdem wir nun alle notwendigen virtuellen Werkzeuge zur Durchführung ökonometrischer Berechnungen zur Hand haben, können wir mit der Lösung unseres Problems beginnen. Dafür:

• Klicken Sie auf die Schaltfläche "Datenanalyse";
• Klicken Sie im sich öffnenden Fenster auf die Schaltfläche "Regression".
• Geben Sie in der angezeigten Registerkarte den Wertebereich für Y (die Anzahl der Mitarbeiter, die gekündigt haben) und für X (ihre Gehälter) ein.
• wir bestätigen unsere Aktionen durch Drücken des "Ok"-Buttons.

Als Ergebnis füllt das Programm automatisch das neue Blatt des Tabellenkalkulationsprozessors mit den Daten der Regressionsanalyse aus. Beachten Sie! Excel hat die Möglichkeit, den von Ihnen bevorzugten Standort für diesen Zweck unabhängig zu definieren. Zum Beispiel könnte es dasselbe Blatt sein wie die Y- und X-Werte oder sogar Ein neues Buch speziell für die Speicherung solcher Daten entwickelt.

Analyse der Regressionsergebnisse für das R-Quadrat

IM Excel-Daten Die im Zuge der Verarbeitung erhaltenen Daten des betrachteten Beispiels haben die Form:

Zuallererst sollten Sie auf den Wert des R-Quadrats achten. Es stellt das Bestimmtheitsmaß dar. IM dieses Beispiel R-Quadrat = 0,755 (75,5%), dh die berechneten Parameter des Modells erklären den Zusammenhang zwischen den betrachteten Parametern um 75,5%. Je höher der Wert des Bestimmtheitsmaßes ist, desto eher wird das gewählte Modell für eine bestimmte Aufgabe als geeigneter erachtet. Es wird angenommen, dass es die reale Situation richtig beschreibt, wenn der R-Quadrat-Wert über 0,8 liegt. Wenn R-Quadrat<0,5, то такой анализа регрессии в Excel нельзя считать резонным.

Quotenanalyse

Die Zahl 64.1428 zeigt den Wert von Y, wenn alle Variablen xi in unserem Modell auf Null gesetzt werden. Mit anderen Worten kann argumentiert werden, dass der Wert des analysierten Parameters von anderen Faktoren beeinflusst wird, die in einem bestimmten Modell nicht beschrieben sind.

Der nächste Koeffizient -0,16285, der sich in Zelle B18 befindet, zeigt die Bedeutung des Einflusses der Variablen X auf Y. Dies bedeutet, dass das durchschnittliche Monatsgehalt der Mitarbeiter innerhalb des betrachteten Modells die Anzahl der Mitarbeiter beeinflusst, die mit einem Gewicht von -0,16285 . gekündigt haben , dh der Grad seines Einflusses ist überhaupt klein. Ein „-“-Zeichen zeigt an, dass der Koeffizient negativ ist. Das liegt auf der Hand, denn jeder weiß: Je höher das Gehalt im Unternehmen, desto weniger Menschen äußern den Wunsch, den Arbeitsvertrag zu kündigen oder zu kündigen.

Multiple Regression

Dieser Begriff wird als Randbedingungsgleichung mit mehreren unabhängigen Variablen der Form verstanden:

y = f (x 1 + x 2 +… x m) + ε, wobei y ein effektives Merkmal (abhängige Variable) und x 1, x 2,… x m Merkmalsfaktoren (unabhängige Variablen) sind.

Parameter Schätzung

Bei der multiplen Regression (MR) wird sie nach der Methode der kleinsten Quadrate (OLS) durchgeführt. Für lineare Gleichungen der Form Y = a + b 1 x 1 +… + b m x m + ε konstruieren wir ein System von Normalgleichungen (siehe unten)

Um das Prinzip der Methode zu verstehen, betrachten Sie den Zwei-Faktoren-Fall. Dann haben wir eine Situation beschrieben durch die Formel

Von hier erhalten wir:

wobei σ die im Index widergespiegelte Varianz des entsprechenden Merkmals ist.

OLS wird auf einer standardisierten Skala auf die MR-Gleichung angewendet. In diesem Fall erhalten wir die Gleichung:

wobei t y, t x 1,… t xm standardisierte Variablen sind, für die die Mittelwerte gleich 0 sind; β i sind die standardisierten Regressionskoeffizienten und die Standardabweichung beträgt 1.

Beachten Sie, dass alle β i in diesem Fall als normalisiert und zentralisiert angegeben werden, sodass ihr Vergleich miteinander als korrekt und gültig angesehen wird. Darüber hinaus ist es üblich, Faktoren herauszufiltern und diejenigen mit den kleinsten Werten von βi zu verwerfen.

Problem mit einer linearen Regressionsgleichung

Angenommen, Sie haben eine Tabelle mit der Preisdynamik für ein bestimmtes Produkt N in den letzten 8 Monaten. Es ist notwendig, eine Entscheidung über die Ratsamkeit des Kaufs seiner Charge zu einem Preis von 1850 Rubel / t zu treffen.

	Monatsnummer	Name des Monats	Produktpreis N
			1750 Rubel pro Tonne
			1755 Rubel pro Tonne
			1767 Rubel pro Tonne
			1760 Rubel pro Tonne
			1770 Rubel pro Tonne
			1790 Rubel pro Tonne
			1810 Rubel pro Tonne
			1840 Rubel pro Tonne

Um dieses Problem im Excel-Tabellenkalkulationsprozessor zu lösen, müssen Sie das bereits aus dem oben vorgestellten Beispiel bekannte Tool zur Datenanalyse verwenden. Wählen Sie als nächstes den Abschnitt "Regression" und stellen Sie die Parameter ein. Es ist zu beachten, dass im Feld "Eingabeintervall Y" ein Wertebereich für die abhängige Variable (in diesem Fall der Preis des Produkts in bestimmten Monaten des Jahres) und im Feld "Eingabe" eingegeben werden muss Intervall X" - für die unabhängige Variable (Monatsnummer). Wir bestätigen die Aktionen mit einem Klick auf "Ok". Auf einem neuen Blatt (sofern angegeben) erhalten wir die Daten für die Regression.

Wir verwenden sie, um eine lineare Gleichung der Form y = ax + b zu konstruieren, wobei die Koeffizienten der Geraden mit dem Namen der Monatszahl und die Koeffizienten und Geraden "Y-Schnitt" aus dem Blatt mit den Ergebnissen der Regressionsanalyse wirken als Parameter a und b. Somit wird die lineare Regressionsgleichung (SD) für Problem 3 geschrieben als:

Produktpreis N = 11,714 * Monatsnummer + 1727,54.

oder in algebraischer Notation

y = 11,714 x + 1727,54

Analyse der Ergebnisse

Um zu entscheiden, ob die erhaltene lineare Regressionsgleichung angemessen ist, werden mehrere Korrelations- und Bestimmungskoeffizienten sowie der Fisher-Test und der Student-Test verwendet. In der Excel-Tabelle mit den Regressionsergebnissen werden sie als multiple R-, R-Quadrat-, F-Statistik bzw. t-Statistik bezeichnet.

KMC R ermöglicht es, die Nähe der probabilistischen Beziehung zwischen den unabhängigen und abhängigen Variablen zu beurteilen. Sein hoher Wert weist auf einen recht starken Zusammenhang zwischen den Variablen „Monatszahl“ und „Produktpreis N in Rubel pro Tonne“ hin. Die Art dieser Verbindung bleibt jedoch unbekannt.

Das Quadrat des Bestimmtheitsmaßes R 2 (RI) ist eine numerische Kenngröße für den Anteil der Gesamtstreuung und gibt an, welche Streuung welcher Teil der Versuchsdaten, d.h. Werte der abhängigen Variablen entspricht der linearen Regressionsgleichung. Im betrachteten Problem beträgt dieser Wert 84,8%, d. h. statistische Daten werden mit hoher Genauigkeit durch die erhaltene SD beschrieben.

Die F-Statistik, auch Fisher-Test genannt, wird verwendet, um die Signifikanz einer linearen Beziehung zu beurteilen und die Hypothese ihrer Existenz zu widerlegen oder zu bestätigen.

(Studentenkriterium) hilft, die Signifikanz des Koeffizienten mit einem unbekannten oder freien Term einer linearen Beziehung zu beurteilen. Ist der Wert des t-Kriteriums > t cr, dann ist die Hypothese der Bedeutungslosigkeit des freien Termes Lineargleichung abgelehnt.

In dem betrachteten Problem für einen freien Term unter Verwendung der Excel-Tools wurde erhalten, dass t = 169,20903 und p = 2,89E-12 ist, d abgelehnt. Für den Koeffizienten bei unbekanntem t = 5,79405 und p = 0,001158. Mit anderen Worten, die Wahrscheinlichkeit, dass die richtige Hypothese über die Bedeutungslosigkeit des Koeffizienten mit der Unbekannten abgelehnt wird, beträgt 0,12 %.

Somit kann argumentiert werden, dass die erhaltene lineare Regressionsgleichung angemessen ist.

Das Problem der Zweckmäßigkeit des Kaufs eines Aktienpakets

Die Mehrfachregression in Excel wird mit demselben Datenanalysetool durchgeführt. Betrachten wir eine spezifische angewandte Aufgabe.

Die Geschäftsführung der Gesellschaft "NNN" muss entscheiden, ob es ratsam ist, eine 20%ige Beteiligung an der JSC "MMM" zu kaufen. Die Kosten für das Paket (JV) betragen 70 Millionen US-Dollar. NNN-Spezialisten haben Daten zu ähnlichen Transaktionen gesammelt. Es wurde beschlossen, den Wert des Aktienpakets anhand folgender Parameter, ausgedrückt in Millionen US-Dollar, zu bewerten:

• Kreditorenbuchhaltung (VK);
• das Volumen des Jahresumsatzes (VO);
• Debitorenbuchhaltung (VD);
• die Kosten des Anlagevermögens (SOF).

Darüber hinaus ist die Kenngröße der Lohnrückstand des Unternehmens (V3 P) in Tausend US-Dollar.

Excel-Tabellenkalkulationslösung

Zunächst müssen Sie eine Tabelle mit Ausgangsdaten erstellen. Es sieht aus wie das:

• rufen Sie das Fenster "Datenanalyse" auf;
• wählen Sie den Abschnitt "Regression";
• der Wertebereich der abhängigen Variablen aus Spalte G wird in das Feld "Eingabeintervall Y" eingegeben;
• Klicken Sie auf das Symbol mit einem roten Pfeil rechts neben dem Fenster "Input X-Intervall" und wählen Sie auf dem Blatt den Bereich aller Werte von Spalten B, C, D, F.

Aktivieren Sie den Punkt "Neues Arbeitsblatt" und klicken Sie auf "Ok".

Rufen Sie eine Regressionsanalyse für eine bestimmte Aufgabe ab.

Untersuchung der Ergebnisse und Schlussfolgerungen

Wir "sammeln" aus den oben auf dem Tabellenblatt Excel präsentierten gerundeten Daten die Regressionsgleichung:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

In einer bekannteren mathematischen Form kann es geschrieben werden als:

y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265,844

Daten für JSC "MMM" sind in der Tabelle dargestellt:

Setzt man sie in die Regressionsgleichung ein, sind es 64,72 Millionen US-Dollar. Das bedeutet, dass die Aktien der JSC "MMM" nicht gekauft werden sollten, da ihr Wert von 70 Millionen US-Dollar eher überbewertet ist.

Wie Sie sehen, ermöglichte die Verwendung des Excel-Tabellenkalkulationsprozessors und der Regressionsgleichung eine fundierte Entscheidung über die Zweckmäßigkeit einer ganz bestimmten Transaktion.

Jetzt wissen Sie, was Regression ist. Die oben besprochenen Beispiele in Excel helfen Ihnen, praktische Probleme im Bereich der Ökonometrie zu lösen.

Die Regressionsanalyse ist eine der gefragtesten Methoden der statistischen Forschung. Es kann verwendet werden, um den Grad des Einflusses unabhängiger Variablen auf die abhängige Variable zu bestimmen. In der Funktionalität von Microsoft Excel gibt es Werkzeuge, die entwickelt wurden, um diese Art von Analyse durchzuführen. Werfen wir einen Blick darauf, was sie sind und wie man sie verwendet.

Anbindung des Analysepakets

Um jedoch die Funktion zur Durchführung von Regressionsanalysen nutzen zu können, müssen Sie zunächst das Analysepaket aktivieren. Erst dann erscheinen die für diesen Vorgang notwendigen Werkzeuge auf der Excel-Multifunktionsleiste.

1. Wechseln Sie zum Reiter "Datei".
2. Gehen Sie zum Abschnitt "Parameter".
3. Das Fenster Excel-Optionen wird geöffnet. Gehen Sie zum Unterabschnitt "Add-ons".
4. Ganz unten im sich öffnenden Fenster verschieben Sie den Schalter im Block „Control“ auf die Position „Excel Add-Ins“, falls er sich an einer anderen Position befindet. Klicken Sie auf die Schaltfläche "Los".
5. Das Fenster mit den verfügbaren Excel-Add-Ins wird geöffnet. Neben dem Punkt „Analysepaket“ setzen wir ein Häkchen. Klicken Sie auf die Schaltfläche "OK".

Wenn wir jetzt auf die Registerkarte "Daten" gehen, sehen wir im Menüband in der Toolbox "Analyse" eine neue Schaltfläche - "Datenanalyse".

Arten der Regressionsanalyse

Es gibt verschiedene Arten von Regressionen:

• parabolisch;
• Machtgesetz;
• logarithmisch;
• exponentiell;
• indikativ;
• hyperbolisch;
• lineare Regression.

Wir werden ausführlicher über die Leistung der letzten Art der Regressionsanalyse in Excel sprechen.

Lineare Regression in Excel

Nachfolgend finden Sie als Beispiel eine Tabelle, die die durchschnittliche tägliche Außenlufttemperatur und die Anzahl der Ladenkäufer für den entsprechenden Arbeitstag anzeigt. Lassen Sie uns mit Hilfe der Regressionsanalyse genau herausfinden, wie sich die Wetterbedingungen in Form der Lufttemperatur auf den Besuch der Steckdose auswirken können.

Die allgemeine lineare Regressionsgleichung lautet wie folgt: Y = a0 + a1x1 +… + akhk. In dieser Formel bedeutet Y die Variable, den Einfluss von Faktoren, die wir untersuchen wollen. In unserem Fall ist dies die Anzahl der Käufer. Der x-Wert sind die verschiedenen Faktoren, die die Variable beeinflussen. Die Parameter a sind die Regressionskoeffizienten. Das heißt, sie bestimmen die Bedeutung dieses oder jenes Faktors. Index k bezeichnet die Gesamtzahl dieser gleichen Faktoren.

Analyse der Analyseergebnisse

Die Ergebnisse der Regressionsanalyse werden in Form einer Tabelle an der in den Einstellungen angegebenen Stelle angezeigt.

Einer der Hauptindikatoren ist das R-Quadrat. Es zeigt die Qualität des Modells an. In unserem Fall gegebener Koeffizient gleich 0,705 oder etwa 70,5%. Dies ist ein akzeptables Qualitätsniveau. Eine Abhängigkeit von weniger als 0,5 ist schlecht.

Ein weiterer wichtiger Indikator befindet sich in der Zelle am Schnittpunkt der Zeile "Y-Schnitt" und der Spalte "Koeffizienten". Es gibt an, welchen Wert Y haben wird, und in unserem Fall ist dies die Anzahl der Käufer, wobei alle anderen Faktoren gleich Null sind. In dieser Tabelle ist dieser Wert 58,04.

Der Wert am Schnittpunkt der Spalten „Variable X1“ und „Koeffizienten“ zeigt den Grad der Abhängigkeit von Y von X. In unserem Fall ist es der Grad der Abhängigkeit der Anzahl der Filialkunden von der Temperatur. Ein Verhältnis von 1,31 gilt als relativ hoher Wirkungsindikator.

Wie Sie sehen können, verwenden Sie Microsoft-Programme Excel ist recht einfach, eine Regressionsanalysetabelle zu erstellen. Aber nur eine geschulte Person kann mit den am Ausgang erhaltenen Daten arbeiten und ihr Wesen verstehen.

Wir freuen uns, dass wir Ihnen bei der Lösung des Problems helfen konnten.

Stellen Sie Ihre Frage in den Kommentaren und beschreiben Sie den Kern des Problems. Unsere Experten werden versuchen, so schnell wie möglich zu antworten.

Hat Ihnen dieser Artikel geholfen?

Die lineare Regression ermöglicht es uns, eine gerade Linie zu beschreiben, die am besten zu einer Reihe von geordneten Paaren (x, y) passt. Die Gleichung für eine gerade Linie, die als lineare Gleichung bekannt ist, wird unten gezeigt:

ŷ ist der erwartete Wert von y für einen gegebenen Wert von x,

x ist eine unabhängige Variable,

a - Segment auf der y-Achse für eine Gerade,

b - Steigung einer geraden Linie.

Die folgende Abbildung zeigt dieses Konzept grafisch:

Die obige Abbildung zeigt die durch die Gleichung ŷ = 2 + 0,5x beschriebene Linie. Das Segment auf der y-Achse ist der Schnittpunkt der Linie mit der y-Achse; in unserem Fall a = 2. Die Steigung der Linie, b, das Verhältnis der Steigung der Linie zur Länge der Linie, hat einen Wert von 0,5. Eine positive Steigung bedeutet, dass die Linie von links nach rechts ansteigt. Bei b = 0 ist die Linie waagerecht, d. h. es besteht kein Zusammenhang zwischen abhängigen und unabhängigen Variablen. Mit anderen Worten, das Ändern des x-Werts hat keinen Einfluss auf den y-Wert.

Ŷ und y werden oft verwechselt. Der Graph zeigt 6 geordnete Punktpaare und eine Linie gemäß dieser Gleichung

Diese Abbildung zeigt den Punkt, der dem geordneten Paar x = 2 und y = 4 entspricht. Beachten Sie, dass der Erwartungswert von y gemäß der Linie bei x= 2 ist . Dies können wir mit folgender Gleichung bestätigen:

= 2 + 0,5x = 2 +0,5 (2) = 3.

Der y-Wert ist der tatsächliche Punkt und the-value ist der erwartete y-Wert unter Verwendung einer linearen Gleichung bei einem gegebenen x-Wert.

Der nächste Schritt besteht darin, die lineare Gleichung zu bestimmen, die der Menge der geordneten Paare am nächsten kommt. Wir haben darüber im vorherigen Artikel gesprochen, in dem wir die Form der Gleichung mit der Methode der kleinsten Quadrate bestimmt haben.

Verwenden von Excel zum Definieren der linearen Regression

Um das in Excel integrierte Regressionsanalysetool verwenden zu können, müssen Sie das Add-In aktivieren Analysepaket... Sie finden es, indem Sie auf die Registerkarte klicken Datei -> Optionen(2007+), im angezeigten Dialogfeld ParameterExcel gehe auf die Registerkarte Add-ons. Auf dem Feld Steuerung wählen Add-onsExcel und klicke Gehen. Setzen Sie im erscheinenden Fenster ein Häkchen gegenüber Analysepaket, wir drücken OK.

Auf der Registerkarte Daten in einer Gruppe Analyse ein neuer Button wird erscheinen Datenanalyse.

Um zu demonstrieren, wie das Add-In funktioniert, verwenden wir die Daten aus dem vorherigen Artikel, in dem sich ein Mann und ein Mädchen einen Tisch im Badezimmer teilen. Tragen Sie die Daten für unser Badewannenbeispiel in die Spalten A und B der leeren Tafel ein.

Gehen Sie auf die Registerkarte Daten, in einer Gruppe Analyse klicken Datenanalyse. Im erscheinenden Fenster Datenanalyse wählen Rückschritt wie gezeigt und klicken Sie auf OK.

Stellen Sie die erforderlichen Regressionsparameter im Fenster ein Rückschritt, wie auf dem Bild gezeigt:

Klicken OK. Die folgende Abbildung zeigt die erhaltenen Ergebnisse:

Diese Ergebnisse stimmen mit denen überein, die wir im vorherigen Artikel durch Selbstberechnung erhalten haben.

Die Regressionsanalyse ist eine statistische Forschungsmethode, mit der Sie die Abhängigkeit eines Parameters von einer oder mehreren unabhängigen Variablen zeigen können. In der Vor-Computer-Ära war seine Anwendung eher schwierig, insbesondere wenn es um große Datenmengen ging. Nachdem Sie heute gelernt haben, wie man eine Regression in Excel erstellt, können Sie komplexe statistische Probleme in nur wenigen Minuten lösen. Nachfolgend finden Sie konkrete Beispiele aus dem Bereich der Wirtschaftswissenschaften.

Regressionstypen

Das Konzept selbst wurde 1886 von Francis Galton in die Mathematik eingeführt. Regression geschieht:

• linear;
• parabolisch;
• Machtgesetz;
• exponentiell;
• hyperbolisch;
• indikativ;
• logarithmisch.

Beispiel 1

Betrachten wir das Problem der Ermittlung der Abhängigkeit der Zahl der Arbeitnehmer, die ihren Arbeitsplatz aufgeben, vom Durchschnittsgehalt in 6 Industrieunternehmen.

Eine Aufgabe. Sechs Unternehmen analysierten das durchschnittliche Monatsgehalt und die Zahl der Mitarbeiter, die freiwillig gekündigt haben. In tabellarischer Form haben wir:

Für das Problem der Ermittlung der Abhängigkeit der Zahl der gekündigten Beschäftigten vom Durchschnittsgehalt in 6 Unternehmen hat das Regressionsmodell die Form der Gleichung Y = a0 + a1 × 1 +… + akxk, wobei xi Einflussgrößen sind, ai sind Regressionskoeffizienten und k ist die Anzahl der Faktoren.

Für diese Aufgabe ist Y der Indikator für die ausscheidenden Mitarbeiter und der Einflussfaktor ist das Gehalt, das wir mit X bezeichnen.

Nutzung der Fähigkeiten des Excel-Tabellenprozessors

Der Regressionsanalyse in Excel muss die Anwendung integrierter Funktionen auf die vorhandenen Tabellendaten vorausgehen. Für diese Zwecke ist es jedoch besser, das sehr nützliche Add-In "Analysepaket" zu verwenden. Um es zu aktivieren, benötigen Sie:

• Gehen Sie von der Registerkarte "Datei" zum Abschnitt "Parameter";
• Wählen Sie im sich öffnenden Fenster die Zeile "Add-Ins" aus;
• Klicken Sie unten rechts neben der Zeile "Steuerung" auf die Schaltfläche "Los".
• Setzen Sie ein Häkchen neben dem Namen „Analysepaket“ und bestätigen Sie Ihre Aktionen mit „OK“.

Wenn alles richtig gemacht wurde, erscheint die erforderliche Schaltfläche auf der rechten Seite der Registerkarte "Daten", die sich über dem Arbeitsblatt "Excel" befindet.

Lineare Regression in Excel

Nachdem wir nun alle notwendigen virtuellen Werkzeuge zur Durchführung ökonometrischer Berechnungen zur Hand haben, können wir mit der Lösung unseres Problems beginnen. Dafür:

• Klicken Sie auf die Schaltfläche "Datenanalyse";
• Klicken Sie im sich öffnenden Fenster auf die Schaltfläche "Regression".
• Geben Sie in der angezeigten Registerkarte den Wertebereich für Y (die Anzahl der Mitarbeiter, die gekündigt haben) und für X (ihre Gehälter) ein.
• wir bestätigen unsere Aktionen durch Drücken des "Ok"-Buttons.

Als Ergebnis füllt das Programm automatisch das neue Blatt des Tabellenkalkulationsprozessors mit den Daten der Regressionsanalyse. Beachten Sie! Excel hat die Möglichkeit, den von Ihnen bevorzugten Standort für diesen Zweck unabhängig zu definieren. Dies kann beispielsweise dasselbe Blatt sein, das die Y- und X-Werte enthält, oder sogar eine neue Arbeitsmappe, die speziell zum Speichern solcher Daten entwickelt wurde.

Analysieren von Regressionsergebnissen für R-Quadrat

In Excel sehen die im Zuge der Verarbeitung der Daten des betrachteten Beispiels gewonnenen Daten wie folgt aus:

Zuallererst sollten Sie auf den Wert des R-Quadrats achten. Es stellt das Bestimmtheitsmaß dar. In diesem Beispiel ist R-Quadrat = 0,755 (75,5%), dh die berechneten Parameter des Modells erklären die Beziehung zwischen den betrachteten Parametern um 75,5%. Je höher der Wert des Bestimmtheitsmaßes ist, desto eher wird das gewählte Modell für eine bestimmte Aufgabe als geeigneter erachtet. Es wird angenommen, dass es die reale Situation richtig beschreibt, wenn der R-Quadrat-Wert über 0,8 liegt. Wenn das R-Quadrat tcr ist, wird die Hypothese der Bedeutungslosigkeit des freien Termes der linearen Gleichung abgelehnt.

In dem betrachteten Problem für einen freien Term unter Verwendung der Excel-Tools wurde erhalten, dass t = 169,20903 und p = 2,89E-12 ist, d abgelehnt. Für den Koeffizienten bei unbekanntem t = 5,79405 und p = 0,001158. Mit anderen Worten, die Wahrscheinlichkeit, dass die richtige Hypothese über die Bedeutungslosigkeit des Koeffizienten mit der Unbekannten abgelehnt wird, beträgt 0,12 %.

Somit kann argumentiert werden, dass die erhaltene lineare Regressionsgleichung angemessen ist.

Das Problem der Zweckmäßigkeit des Kaufs eines Aktienpakets

Die Mehrfachregression in Excel wird mit demselben Datenanalysetool durchgeführt. Betrachten wir eine spezifische angewandte Aufgabe.

Die Geschäftsführung der Gesellschaft "NNN" muss entscheiden, ob es ratsam ist, eine 20%ige Beteiligung an der JSC "MMM" zu kaufen. Die Kosten für das Paket (JV) betragen 70 Millionen US-Dollar. NNN-Spezialisten haben Daten zu ähnlichen Transaktionen gesammelt. Es wurde beschlossen, den Wert des Aktienpakets anhand folgender Parameter, ausgedrückt in Millionen US-Dollar, zu bewerten:

• Kreditorenbuchhaltung (VK);
• das Volumen des Jahresumsatzes (VO);
• Debitorenbuchhaltung (VD);
• die Kosten des Anlagevermögens (SOF).

Darüber hinaus ist die Kenngröße der Lohnrückstand des Unternehmens (V3 P) in Tausend US-Dollar.

Excel-Tabellenkalkulationslösung

Zunächst müssen Sie eine Tabelle mit Ausgangsdaten erstellen. Es sieht aus wie das:

• rufen Sie das Fenster "Datenanalyse" auf;
• wählen Sie den Abschnitt "Regression";
• der Wertebereich der abhängigen Variablen aus Spalte G wird in das Feld "Eingabeintervall Y" eingegeben;
• Klicken Sie auf das Symbol mit einem roten Pfeil rechts neben dem Fenster "Eingabeintervall X" und wählen Sie auf dem Blatt den Bereich aller Werte aus den Spalten B, C, D, F aus.

Aktivieren Sie den Punkt "Neues Arbeitsblatt" und klicken Sie auf "Ok".

Rufen Sie eine Regressionsanalyse für eine bestimmte Aufgabe ab.

Untersuchung der Ergebnisse und Schlussfolgerungen

Wir "sammeln" aus den oben auf dem Tabellenblatt Excel präsentierten gerundeten Daten die Regressionsgleichung:

SP = 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

In einer bekannteren mathematischen Form kann es geschrieben werden als:

y = 0,103 * x1 + 0,541 * x2 - 0,031 * x3 + 0,405 * x4 + 0,691 * x5 - 265,844

Daten für JSC "MMM" sind in der Tabelle dargestellt:

Setzt man sie in die Regressionsgleichung ein, sind es 64,72 Millionen US-Dollar. Das bedeutet, dass die Aktien der JSC "MMM" nicht gekauft werden sollten, da ihr Wert von 70 Millionen US-Dollar eher überbewertet ist.

Wie Sie sehen, ermöglichte die Verwendung des Excel-Tabellenkalkulationsprozessors und der Regressionsgleichung eine fundierte Entscheidung über die Zweckmäßigkeit einer ganz bestimmten Transaktion.

Jetzt wissen Sie, was Regression ist. Die oben besprochenen Beispiele in Excel helfen Ihnen, praktische Probleme im Bereich der Ökonometrie zu lösen.

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 - Schüchternheit in Punkten

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

4. Wir haben also 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 aber 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 Gitter ). Die wichtigsten Änderungen und Einstellungen werden in der Registerkarte vorgenommen Layout

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