Ein Regressionsmodell in Excel bauen. Konrad Carlberg. Regressionsanalyse in Microsoft Excel
Das MS Excel-Paket ermöglicht die Konstruktion einer Gleichung der Gleichung lineare Regression Der größte Teil des Jobs erfolgt sehr schnell. Es ist wichtig zu verstehen, wie Sie die erzielten Ergebnisse interpretieren können.
Muss arbeiten Analysepaket.was Sie im Menüpunkt aktivieren möchten Service \\ Add-In
In Excel 2007, um das Analysepaket zu aktivieren, müssen Sie auf den Block klicken Excel-EinstellungenDurch Drücken der Taste in der oberen linken Ecke und dann die Taste " Excel-Einstellungen»Am unteren Rand des Fensters:
Um ein Regressionsmodell aufzubauen, müssen Sie Element auswählen Service \\ Data Analysis \\ Regression. (In Excel 2007 befindet sich dieser Modus im Block Daten- / Datenanalyse / Regression). Ein Dialogfeld scheint auszufüllen:
1) Eingabeintervall y. ¾ Enthält einen Link zu Zellen, die Werte der Leistung enthalten y.. Werte müssen sich in der Spalte befinden.
2) Eingabeintervall X. ¾ Enthält einen Link zu Zellen, die die Werte der Faktoren enthalten. Werte müssen sich in Spalten befinden.
3) Zeichen Stichworte Es wird angehoben, wenn die ersten Zellen erläuterndes Text enthalten (Datensignaturen);
4) Zuverlässigkeitsstufe ¾ Dies ist eine Vertrauenswahrscheinlichkeit, die standardmäßig als 95% betrachtet wird. Wenn dieser Wert nicht angeht, müssen Sie diese Funktion aufnehmen und den gewünschten Wert eingeben.
5) Zeichen Constanta null. Es schaltet ein, wenn es notwendig ist, eine Gleichung aufzubauen, in der eine freie Variable ist;
6) Ausgangsparameter Bestimmen Sie, wo die Ergebnisse platziert werden müssen. Der Standardmodus ist Neues Arbeitsblatt;
7) Block Rückstände Ermöglicht das Einschalten der Ausgabe der Rückstände und der Konstruktion ihrer Grafiken.
Infolgedessen werden Informationen angezeigt, die alle erforderlichen Informationen enthalten, und in drei Blöcke gruppiert: Regressionsstatistik., Dispersionsanalyse, Fazit Rückstand. Betrachten Sie sie näher.
1. Regressionsstatistik.:
mehrere R. bestimmt von der Formel ( pearson-Korrelationskoeffizient);
R. 
(bestimmtheitsmaß);
Normiert R.-KVadrat wird von der Formel berechnet 
(wird verwendet für mehrere Regression.);
Standart Fehler S. Berechnet durch Formel. 
;
Beobachtungen ¾ Dies ist die Datenmenge n..
2. Dispersionsanalyse, Linie Regression.:
Parameter dF. Rabe m. (Anzahl der Faktorengruppen x.);
Parameter Ss. bestimmt durch die Formel;
Parameter FRAU. bestimmt durch die Formel;
Statistiken F. bestimmt durch die Formel;
Bedeutung F.. Wenn die erhaltene Zahl übersteigt, wird die Hypothese aufgenommen (es gibt keine lineare Beziehung), da sonst die Hypothese entnommen wird (eine lineare Beziehung).
3. Dispersionsanalyse, Linie Rückstand:
Parameter dF. gleich;
Parameter Ss. Formel ist bestimmt 
;
Parameter FRAU. Von der Formel bestimmt.
4. Dispersionsanalyse, Linie GESAMT Enthält die Summe der ersten beiden Spalten.
5. Dispersionsanalyse, Linie Y-crossing. Enthält den Wert des Koeffizienten, des Standardfehlers und t.-Statistiken.
P.-NOTION ¾ ist der Wert der Signifikanzgrade, die dem berechnen t.-Statistiken. Bestimmt durch die Funktion von stouturasp ( t.-Statistiken; ). Wenn ein P.-NOTION überschreitet, die entsprechende Variable ist statistisch unbedeutend und es kann vom Modell ausgeschlossen werden.
Niedriger 95% und Top 95% ¾ Dies ist die untere und obere Grenze von 95 Prozent Konfidenzintervallen für die Koeffizienten der linearen Regression theoretischen Gleichung. Wenn in dem Dateneingabeblock der Konfidenzwahrscheinlichkeit standardmäßig standardmäßig hinterlassen wurde, werden die letzten beiden Spalten die vorherigen duplizieren. Wenn der Benutzer seine Vertrauenswahrscheinlichkeit eingegeben hat, enthalten die letzten beiden Spalten die Werte der unteren und oberen Grenze für die angegebene Vertrauenswahrscheinlichkeit.
6. DispersionsanalyseSaiten enthalten die Werte der Koeffizienten, Standardfehler, t.-Statistiker, P.- Annäherungen und Vertrauensintervalle für relevante.
7. Block Fazit Rückstand Enthält vorhergesagte Werte y. (In unseren Bezeichnungen es) und den Resten.
IM Aufheben Es gibt noch eine schnellere und bequemere Möglichkeit, einen linearen Regressionsplan (und sogar die Haupttypen der nichtlinearen Regressionen, als cm. Nächstes) aufzubauen. Dies kann wie folgt durchgeführt werden:
1) Zuordnen von Spalten mit Daten X. und Y. (Sie sollten sich in dieser Reihenfolge befinden!);
2) Anruf Master-Chart und wählen Sie in einer Gruppe Eine Art – Pagle Und sofort drücken Bereit;
3) Wenn Sie die Auswahl aus dem Diagramm aus dem Diagramm ablegen, wählen Sie den ersten Elementelement aus, das angezeigt wird Diagrammin dem Sie den Artikel auswählen sollten Trendlinie hinzufügen;
4) Im Dialogfeld, das Dialogfeld angezeigt wird Trendlinie Auf der Registerkarte Eine Artwählen Linear;
5) Auf der Registerkarte Parametersie können den Switch aktivieren Zeigen Sie die Gleichung auf dem DiagrammDies ermöglicht es, die lineare Regressionsgleichung (4.4) zu sehen, in der die Koeffizienten (4.5) berechnet werden.
6) In derselben Registerkarte können Sie den Schalter aktivieren Platzieren Sie den Wert der Genauigkeit der Annäherung (R ^ 2) im Diagramm (R ^ 2). Diese Größe ist das Quadrat des Korrelationskoeffizienten (4.3) und es zeigt, wie gut die berechnete Gleichung experimentelle Abhängigkeit beschreibt. Wenn ein R. 2 sind nahe an der Einheit, dann beschreibt die theoretische Regressionsgleichung eine gut experimentelle Abhängigkeit (die Theorie ist mit dem Experiment gut vereinbart) und wenn R. 2 in der Nähe von Null, dann diese Gleichung. Nicht geeignet für die Beschreibung experimenteller Abhängigkeiten (die Theorie ist nicht mit dem Experiment überein).
Infolge der Ausführung der beschriebenen Aktionen wird ein Diagramm mit Regressionszeitplan und seiner Gleichung erhalten.
§4.3. Haupttypen der nichtlinearen Regression
Parabol- und Polynomregression.
Parabolisch Die Abhängigkeit der Größe Y. von der Größenordnung H. Die Abhängigkeit wird als quadratische Funktion bezeichnet (2. Ordnung Parabola):
Diese Gleichung wird aufgerufen die Gleichung der parabolischen Regression y auf der H.. Parameter aber, b., von namens koeffizienten der parabolischen Regression. Die Berechnung der parabolischen Regressionskoeffizienten ist immer umständlich, daher wird empfohlen, einen Computer für Berechnungen zu verwenden.
Gleichung (4.8) der parabolischen Regression ist ein Sonderfall einer allgemeineren Regression, das als Polynom genannt wird. Polynom Die Abhängigkeit der Größe Y. von der Größenordnung H. wird als Abhängigkeit genannt, ausgedrückt durch Polynom n.Auftrag:
wo Zahlen ein I. (iCH.=0,1,…, n.) Namens koeffizienten der Polynomregression.
Stromregression.
Leistung Die Abhängigkeit der Größe Y. von der Größenordnung H. Die Abhängigkeit des Formulars wird aufgerufen:
Diese Gleichung wird aufgerufen gleichung der Stromregression y auf der H.. Parameter aber und b. namens koeffizienten der Stromregression.
ln \u003d ln. eIN.+b ·ln. x.. (4.11)
Diese Gleichung beschreibt die direkte in der Ebene mit den logarithmischen Koordinatenachsen von LN x. und ln. Daher ist das Kriterium für die Anwendbarkeit der Stromregression die Anforderung, dass die Punkte von Logarithmen der empirischen Daten ln x I. und ln. iCH. Sie waren der Linie am nächsten (4.11).
Indikative Regression.
Indikativ(oder exponentiell) Die Abhängigkeit der Größe Y. von der Größenordnung H. Die Abhängigkeit des Formulars wird aufgerufen:
(oder ). (4.12)
Diese Gleichung wird aufgerufen die Gleichung ist indikativ (oder exponentiell) regression Y. auf der H.. Parameter aber (oder k.) ICH. b. namens bekämpfungskoeffizienten (oder exponentiell) regression..
Wenn beide Teile der Stromregressionsgleichung prologiert werden, wird die Gleichung sein
ln \u003d. x ·ln. eIN.+ Ln. b. (oder ln \u003d k · X.+ Ln. b.). (4.13)
Diese Gleichung beschreibt die lineare Abhängigkeit des Logarithmus eines einzelnen LN-Werts von einem anderen Wert. x.. Daher ist das Kriterium für die Anwendbarkeit der Stromregression die Anforderung, dass die Punkte der empirischen Daten derselben Größes x I. Und die Logarithmen einer anderen LN-Größenordnung iCH. Sie waren direkterseitig (4.13) am nächsten.
Logarithmische Regression.
Logarithmischdie Abhängigkeit der Größe Y. von der Größenordnung H. Die Abhängigkeit des Formulars wird aufgerufen:
=eIN.+b ·ln. x.. (4.14)
Diese Gleichung wird aufgerufen die Gleichung der logarithmischen Regression y auf der H.. Parameter aber und b. namens koeffizienten logarithmischer Regression.
Hyperbolische Regression.
Hyperbolisch Die Abhängigkeit der Größe Y. von der Größenordnung H. Die Abhängigkeit des Formulars wird aufgerufen:
Diese Gleichung wird aufgerufen die Gleichung der hyperbolischen Regression y auf der H.. Parameter aber und b. namens die Koeffizienten der hyperbolischen Regression und werden durch das Verfahren kleinster Quadrate bestimmt. Die Verwendung dieser Methode führt zu Formeln:
In den Formeln (4.16-4.17) erfolgt die Summation durch Index iCH. von einem bis zur Anzahl der Beobachtungen n..
Leider in. Aufheben Es gibt keine Funktionen, die die Koeffizienten der hyperbolischen Regression berechnen. In Fällen, in denen nicht bekannt ist, dass die Messwerte mit inversen Proportionalität verbunden sind, wird er anstelle der hyperbolischen Regressionsgleichung empfohlen, um die Stromregressionsgleichung zu suchen, also in Aufheben Es gibt ein Verfahren für den Standort. Wenn eine hyperbolische Abhängigkeit zwischen den Messwerten angenommen wird, müssen seine Regressionskoeffizienten unter Verwendung der Hilfsberechnungstabellen und Summationsvorgänge gemäß den Formeln (4.16-4.17) berechnet werden.
Regressions- und Korrelationsanalyse - statistische Forschungsmethoden. Dies sind die häufigsten Möglichkeiten, um die Abhängigkeit von Parameter von einem oder mehreren unabhängigen Variablen anzuzeigen.
Nachfolgend zu bestimmten praktischen Beispielen berücksichtigen Sie diese beiden sehr beliebten Analysen im Umweltumfeld. Und geben Sie auch ein Beispiel, um Ergebnisse beim Kombinieren zu erhalten.
Regressionsanalyse in Excel
Zeigt den Effekt einiger Werte (unabhängig, unabhängig) auf der abhängigen Variablen an. Wenn zum Beispiel die Anzahl der wirtschaftlich aktiven Bevölkerung auf der Anzahl der Unternehmen, die Werte von Löhnen usw. abhängt, hängt der Parameter ab. Oder: So beeinflussen man ausländische Investitionen, Energiepreise und andere. Auf der Ebene des BIP.
Das Ergebnis der Analyse ermöglicht es Ihnen, Prioritäten hervorzuheben. Und basierend auf den Hauptfaktoren prognostizieren Sie die Entwicklung von vorrangigen Bereichen, um Managemententscheidungen vorzunehmen.
Regression passiert:
- linear (y \u003d a + bx);
- parabolic (y \u003d a + bx + cx 2);
- exponentiell (y \u003d a * exp (bx));
- leistung (y \u003d a * x ^ b);
- hyperbolisch (y \u003d b / x + a);
- logarithmisch (y \u003d b * 1n (x) + a);
- anzeige (y \u003d a * b ^ x).
Betrachten Sie das Beispiel den Bau eines Regressionsmodells in Excel und der Interpretation der Ergebnisse. Nehmen linearer Typ Regression.
Eine Aufgabe. Bei 6 Unternehmen wurden das durchschnittliche monatliche Gehalt und die Anzahl der abosolierten Mitarbeiter analysiert. Es ist notwendig, die Abhängigkeit der Anzahl der absolvierten Mitarbeiter aus dem Durchschnittsgehalt zu ermitteln.
Das lineare Regressionsmodell hat das folgende Formular:
Y \u003d A 0 + A 1 x 1 + ... + A bis X bis.
Wo A die Regressionskoeffizienten, X-Beeinflussungsvariablen, zur Anzahl der Faktoren ist.
In unserem Beispiel, als ob der Indikator für diejenigen, die destillierte Arbeiter sind, sind. Beeinflussungsfaktor - Löhne (x).
Excel verfügt über integrierte Funktionen, mit denen Sie die Parameter des linearen Regressionsmodells berechnen können. Aber schneller wird es ein Add-In "Analysepaket" machen.
Aktivieren Sie ein leistungsfähiges analytisches Werkzeug:
Nach der Aktivierung steht das Add-In auf der Registerkarte Daten zur Verfügung.
Jetzt beschäftigen wir uns direkt mit der direkten Regressionsanalyse.
Achten Sie zunächst auf den R-Quadrat und die Koeffizienten.
R-Quadrat - Bestimmungskoeffizient. In unserem Beispiel - 0,755 oder 75,5%. Dies bedeutet, dass die berechneten Parameter des Modells um 75,5% die Beziehung zwischen den untersuchten Parametern erklären. Je höher der Bestimmungskoeffizient, 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, wie y sein wird, wenn alle Variablen in dem unter Berücksichtigen Modell 0 sein werden. Das heißt, andere Faktoren, die nicht im Modell beschrieben werden, werden durch den Wert des analysierenden Parameters beeinflusst.
Der Koeffizient -0,16285 zeigt das Gewicht der Variablen x auf y. Das heißt, der durchschnittliche monatliche Lohn in diesem Modell beeinflusst den Betrag von -0,16285, der mit dem Gewicht zitiert wird (dies ist ein kleiner Einflussgrad). Das Zeichen "-" gibt einen negativen Effekt an: je mehr Gehalt, desto weniger abgeschafft. Was ist richtig.
Korrelationsanalyse in Excel
Die Korrelationsanalyse hilft, festzustellen, ob eine Verbindung zwischen den Indikatoren in einem oder zwei Proben vorliegt. Beispielsweise zwischen der Betriebszeit der Maschine und der Reparaturkosten, des Ausrüstungspreises und der Dauer des Betriebs, des Wachstums und des Gewichts von Kindern usw.
Wenn die Verbindung verfügbar ist, erhöht sich eine Erhöhung eines Parameters (positive Korrelation) oder eine Abnahme in (negativ) eines anderen. Die Korrelationsanalyse hilft der Analyse, zu entscheiden, ob es möglich ist, den möglichen Wert des anderen in der Größe eines Indikators vorherzusagen.
Der Korrelationskoeffizient wird von R. bezeichnet. Variiert von +1 bis -1. Die Klassifizierung von Korrelationsbindungen für verschiedene Kugeln ist unterschiedlich. Mit dem Wert des Koeffizienten 0 lineare Abhängigkeit. Es gibt keine Proben.
Überlegen Sie, wie mit excel Funds. Finden Sie den Korrelationskoeffizienten.
Um gepaarte Koeffizienten zu finden, wird die CORRELA-Funktion verwendet.
Aufgabe: Bestimmen Sie, ob es eine Beziehung zwischen der Zeit des Betriebs der Drehmaschine und den Kosten des Dienstes gibt.
Wir setzen den Cursor in eine beliebige Zelle und drücken Sie die FX-Taste.
- Wählen Sie in der Kategorie "statistisch" die Funktion des Cornel aus.
- Argument "Array 1" - der erste Wertebereich - die Betriebszeit der Maschine: A2: A14.
- Argument "Array 2" - der zweite Wertebereich - die Reparaturkosten: B2: B14. OK klicken.
Um die Art der Kommunikation zu ermitteln, müssen Sie die absolute Anzahl des Koeffizienten sehen (für jeden Aktivitätsbereich gibt es eine eigene Skala).
Zur Korrelationsanalyse mehrerer Parameter (mehr als 2) ist es bequemer, "Datenanalyse" (Add-On-Analysepaket ") anwenden. Die Liste muss eine Korrelation auswählen und ein Array benennen. Alles.
Die resultierenden Koeffizienten werden in der Korrelationsmatrix angezeigt. So was:
Korrelationsregressionsanalyse
In der Praxis werden diese beiden Techniken oft zusammen verwendet.
Beispiel:
Diese Regressionsanalyse wird auch sichtbar.
Korrelations- und Regressionsanalyse inFRAU. Aufheben
1. Erstellen Sie die Quelldatendatei in MS Excel (z. B. Tabelle 2).
2. Bau des Korrelationsfeldes
Ein Korrelationsfeld aufbauen befehlszeile Wählen Sie das Menü aus Kasten / Diagramm. Wählen Sie im angezeigten Dialogfeld Diagrammtyp aus: Pagle; Aussicht: Punkt-DiagrammDamit können Sie Dämpfe vergleichen (Abb. 22).
Abbildung 22 - Wählen Sie einen Diagrammtyp aus
Abbildung 23 - Ansicht des Fensters bei der Auswahl eines Bereichs und Reihen
Abbildung 25 - Fensteransicht, Schritt 4
2. B. kontextmenü Ein Team wählen Fügen Sie der Trendlinie hinzu.
3. Wählen Sie im angezeigten Dialogfeld die Art des Diagramms (in unserem Beispiel linear) und den Parametern der Gleichung aus, wie in Abbildung 26 gezeigt.
OK klicken. Das Ergebnis ist in Abbildung 27 dargestellt.
Abbildung 27 - Korrelationsfeld der Arbeitsproduktivität Abhängigkeit von Lagerreparatur
In ähnlicher Weise bauen wir ein Korrelationsfeld der Arbeitsproduktivitätsabhängigkeit vom Änderungskoeffizienten. (Abbildung 28).
 |
Abbildung 28 - Korrelationsfeld der Arbeitsproduktivitätsabhängigkeit
aus dem Ersatzkoeffizienten der Ausrüstung
3. Bau der Korrelationsmatrix.
So erstellen Sie eine Korrelationsmatrix im Menü Bedienung Wählen Datenanalyse.
Verwenden des Datenanalyse-Tools Regression.Neben den Ergebnissen der Regressionsstatistiken, Dispersionsanalyse und Konfidenzintervalle können Sie Rückstände und Diagramme der Wiederherstellungslinie, Rückstände und der normalen Wahrscheinlichkeit erhalten. Überprüfen Sie dazu den Zugriff auf das Analysepaket. Wählen Sie im Hauptmenü nacheinander aus Service / Add-In. Aktivieren Sie das Kontrollkästchen Analysepaket. (Abbildung 29)
Abbildung 30 - Dialogfeld Datenanalyse
Geben Sie nach dem Drücken von OK in dem angezeigten Dialogfeld das Eingabeintervall (in unserem Beispiel A2: D26) an, und gruppieren (in unserem Fall über Spalten) und Ausgabeparameter, wie in Abbildung 31 gezeigt.
 |
Abbildung 31 - Dialogfeld Korrelation
Das Ergebnis der Berechnungen ist in Tabelle 4 dargestellt.
Tabelle 4 - Korrelationsmatrix
Spalte 1. | Spalte 2. | Spalte 3. |
|
Spalte 1. | |||
Spalte 2. | |||
Spalte 3. |
Single-Factor-Regressionsanalyse
Verwenden des Regressionswerkzeugs.
Um die Regressionsanalyse der Abhängigkeit von Arbeitsproduktivität aus Lagerprojekten im Menü durchzuführen Bedienung Wählen Datenanalyse und geben Sie das Analysewerkzeug an Regression. (Abbildung 32).
Abbildung 33 - Dialogfeld Regression.
Das MS Excel-Paket ermöglicht den Bau einer linearen Regressionsgleichung der meisten der Arbeit sehr schnell. Es ist wichtig zu verstehen, wie Sie die erzielten Ergebnisse interpretieren können. Um ein Regressionsmodell aufzubauen, müssen Sie Service \\ Data-Analyse \\ Regression auswählen (in Excel 2007 Dieser Modus befindet sich in der Daten- / Datenanalyseeinheit / Regression). Dann werden die Ergebnisse in die Analyseeinheit kopiert.
