Paarweise lineare Regression in Excel. Regression in Excel

Die Regressionsanalyse ist eine der beliebtesten Methoden der statistischen Forschung. Es kann verwendet werden, um den Grad des Einflusses unabhängiger Variablen auf die abhängige Variable zu bestimmen. An Funktionalität Microsoft Excel Für diese Art der Analyse stehen Tools zur Verfügung. Werfen wir einen Blick darauf, was sie sind und wie man sie benutzt.

Anschließen 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 in der Excel-Multifunktionsleiste.

1. Wechseln Sie zur Registerkarte "Datei".
2. Gehen Sie zum Abschnitt "Einstellungen".
3. Das Fenster Excel-Optionen wird geöffnet. Gehen Sie zum Unterabschnitt "Add-Ons".
4. Im ganz unteren Teil des sich öffnenden Fensters ordnen wir den Schalter im Block "Verwaltung" auf die Position "Excel-Add-Ins" um, falls er sich in einer anderen Position befindet. Klicken Sie auf die Schaltfläche "Los".
5. Das Fenster Excel-Add-Ins wird geöffnet. Aktivieren Sie das Kontrollkästchen neben "Analysepaket". Klicken Sie auf die Schaltfläche "OK".

Wenn wir jetzt zur Registerkarte „Daten“ in der Multifunktionsleiste im Werkzeugblock „Analyse“ gehen, sehen wir eine neue Schaltfläche – „Datenanalyse“.

Arten der Regressionsanalyse

Es gibt verschiedene Arten von Regressionen:

• parabolisch;
• Leistung;
• logarithmisch;
• exponentiell;
• Demonstration;
• hyperbolisch;
• lineare Regression.

Über die Ausführung der letzten Ansicht Regressionsanalyse Wir werden später mehr über Excel sprechen.

Lineare Regression in Excel

Unten finden Sie als Beispiel eine Tabelle, die die durchschnittliche tägliche Lufttemperatur auf der Straße und die Anzahl der Ladenkunden für den entsprechenden Arbeitstag zeigt. Lassen Sie uns mit Hilfe der Regressionsanalyse genau herausfinden, wie sich Wetterbedingungen in Form der Lufttemperatur auf die Anwesenheit eines Einzelhandelsgeschäfts auswirken können.

Die allgemeine lineare Regressionsgleichung sieht so aus: Y = a0 + a1x1 + ... + axk. In dieser Formel bedeutet Y die Variable, auf die wir versuchen, den Einfluss von Faktoren zu untersuchen. In unserem Fall ist dies die Anzahl der Käufer. Der x-Wert ist Unterschiedliche Faktoren die die Variable beeinflussen. Die a-Parameter sind die Regressionskoeffizienten. Das heißt, sie bestimmen die Bedeutung eines bestimmten Faktors. Index k bezeichnet die Gesamtzahl dieser gleichen Faktoren.

Analyseergebnisse Analyse

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

Einer der Hauptindikatoren ist das R-Quadrat. Es zeigt die Qualität des Modells an. In unserem Fall angegebenen Koeffizienten beträgt 0,705 oder etwa 70,5 %. Dies ist ein akzeptables Qualitätsniveau. Ein Verhältnis von weniger als 0,5 ist schlecht.

Ein weiterer wichtiger Indikator befindet sich in der Zelle am Schnittpunkt der Linie "Y-Schnittpunkt" und der Spalte "Koeffizienten". Hier wird angegeben, 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 gegebenen Wert gleich 58.04.

Der Wert am Schnittpunkt der Spalte „Variable X1“ und „Koeffizienten“ zeigt den Grad der Abhängigkeit von Y von X. In unserem Fall ist dies der Grad der Abhängigkeit der Anzahl der Ladenkunden von der Temperatur. Ein Koeffizient von 1,31 gilt als recht hohe Rate beeinflussen.

Wie Sie sehen können, ist es ganz einfach, eine Regressionsanalysetabelle mit Microsoft Excel zu erstellen. Aber nur eine geschulte Person kann mit den am Ausgang erhaltenen Daten arbeiten und deren Essenz 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 detailliert das Wesentliche des Problems. Unsere Experten werden versuchen, so schnell wie möglich zu antworten.

Hat Ihnen dieser Artikel geholfen?

Die Methode der linearen Regression ermöglicht es uns, eine gerade Linie zu beschreiben, die am besten zu einer Reihe geordneter Paare (x, y) passt. Die Gleichung für eine gerade Linie, bekannt als lineare Gleichung, ist unten angegeben:

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

x - unabhängige Variable,

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

b ist die Steigung der Geraden.

In der folgenden Abbildung wird dieses Konzept grafisch dargestellt:

Die obige Abbildung zeigt eine Linie, die durch die Gleichung ŷ =2+0,5x beschrieben wird. Das Segment auf der y-Achse ist der Schnittpunkt der Geraden mit der y-Achse; in unserem Fall ist a = 2. Die Steigung der Linie, b, das Verhältnis von Liniensteigung zu Linienlänge, hat einen Wert von 0,5. Eine positive Steigung bedeutet, dass die Linie von links nach rechts ansteigt. Wenn b = 0 ist, ist die Linie horizontal, was bedeutet, dass es keine Beziehung zwischen den abhängigen und unabhängigen Variablen gibt. Mit anderen Worten, eine Änderung des Werts von x wirkt sich nicht auf den Wert von y aus.

ŷ und y werden oft verwechselt. Der Graph zeigt 6 geordnete Punktepaare und eine Linie gemäß der gegebenen Gleichung

Diese Abbildung zeigt den Punkt, der dem geordneten Paar x = 2 und y = 4 entspricht. Beachten Sie, dass der erwartete Wert von y gemäß der Zeile at X= 2 ist ŷ. Wir können dies mit der folgenden Gleichung bestätigen:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Der y-Wert ist der tatsächliche Punkt und der ŷ-Wert ist der erwartete y-Wert unter Verwendung Lineargleichung für einen gegebenen Wert von x.

Der nächste Schritt besteht darin, eine lineare Gleichung zu bestimmen, die maximal einer Menge geordneter Paare entspricht, darüber haben wir in einem früheren Artikel gesprochen, in dem wir die Form der Gleichung mit der Methode bestimmt haben kleinsten Quadrate.

Verwenden von Excel zum Definieren der linearen Regression

Um das in Excel integrierte Regressionsanalyse-Tool zu verwenden, müssen Sie das Add-In aktivieren Analysepaket. Sie finden es, indem Sie auf die Registerkarte klicken Datei –> Optionen(2007+) im erscheinenden Dialog Optionenübertreffen gehen Sie zur Registerkarte Add-Ons. Auf dem Feld Kontrolle wählen Zusätzeübertreffen und klicken Gehen. Aktivieren Sie im angezeigten Fenster das Kontrollkästchen neben Analysepaket, klicken OK.

Auf der Registerkarte Daten in einer Gruppe Analyse Es erscheint eine neue Schaltfläche 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 Badezimmerbeispiel in die Spalten A und B eines leeren Blattes ein.

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

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

Klicken OK. Die folgende Abbildung zeigt die erzielten Ergebnisse:

Diese Ergebnisse stimmen mit denen überein, die wir durch unabhängige Berechnungen im vorherigen Artikel 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 die Nutzung recht 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. Unten sind konkrete Beispiele aus dem Bereich Wirtschaftswissenschaften.

Arten der Regression

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

• linear;
• parabolisch;
• Leistung;
• exponentiell;
• hyperbolisch;
• demonstrativ;
• logarithmisch.

Beispiel 1

Betrachten Sie das Problem, die Abhängigkeit der Anzahl der ausgeschiedenen Teammitglieder vom Durchschnittsgehalt in 6 Industrieunternehmen zu bestimmen.

Aufgabe. Bei sechs Unternehmen haben wir das durchschnittliche Monatsgehalt und die Anzahl der Mitarbeiter, die aufgrund von Austritten ausgetreten sind, analysiert eigener Wille. In tabellarischer Form haben wir:

Für das Problem der Bestimmung der Abhängigkeit der Anzahl der entlassenen Arbeitnehmer vom Durchschnittsgehalt in 6 Unternehmen hat das Regressionsmodell die Form der Gleichung Y = a0 + a1 × 1 + ... + akxk, wobei хi die sind Einflussgrößen, ai sind die Regressionskoeffizienten und k ist die Anzahl der Faktoren.

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

Nutzung der Funktionen der Tabellenkalkulation "Excel"

Der Regressionsanalyse in Excel muss die Anwendung integrierter Funktionen auf die verfügbaren Tabellendaten vorausgehen. Verwenden Sie für diese Zwecke jedoch besser das sehr nützliche Add-In „Analysis Toolkit“. Um es zu aktivieren, benötigen Sie:

• Gehen Sie auf der Registerkarte "Datei" zum Abschnitt "Optionen".
• Wählen Sie im sich öffnenden Fenster die Zeile "Add-ons" aus;
• Klicken Sie unten rechts neben der Zeile „Verwaltung“ auf die Schaltfläche „Los“.
• Aktivieren Sie das Kontrollkästchen neben dem Namen "Analysepaket" und bestätigen Sie Ihre Aktionen mit einem Klick auf "OK".

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

Lineare Regression in Excel

Jetzt, da wir 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 auf der angezeigten Registerkarte den Wertebereich für Y (die Anzahl der gekündigten Mitarbeiter) und für X (ihre Gehälter) ein.
• Wir bestätigen unsere Aktionen durch Drücken der Schaltfläche "Ok".

Als Ergebnis füllt das Programm automatisch ein neues Tabellenblatt mit Regressionsanalysedaten. Beachten Sie! Excel kann den für diesen Zweck bevorzugten Speicherort manuell festlegen. Beispielsweise könnte es sich um dasselbe Blatt handeln, in dem sich die Y- und X-Werte befinden, oder sogar Ein neues Buch, das speziell für die Speicherung solcher Daten entwickelt wurde.

Analyse der Regressionsergebnisse für R-Quadrat

In Excel sehen die bei der Verarbeitung der Daten des betrachteten Beispiels gewonnenen Daten so aus:

Zunächst sollten Sie auf den Wert des R-Quadrats achten. Es ist das Bestimmtheitsmaß. In diesem Beispiel ist R-Quadrat = 0,755 (75,5 %), d. h. die berechneten Parameter des Modells erklären die Beziehung zwischen den betrachteten Parametern zu 75,5 %. Je höher der Wert des Bestimmtheitsmaßes ist, desto geeigneter ist das gewählte Modell für eine bestimmte Aufgabe. Es wird angenommen, dass es die reale Situation mit einem R-Quadrat-Wert über 0,8 korrekt beschreibt. Wenn das R-Quadrat tcr ist, wird die Hypothese der Bedeutungslosigkeit des freien Terms der linearen Gleichung verworfen.

In dem betrachteten Problem für das freie Mitglied wurde unter Verwendung der Excel-Tools erhalten, dass t = 169,20903 und p = 2,89E-12, d. h. wir haben eine Nullwahrscheinlichkeit, dass die richtige Hypothese über die Bedeutungslosigkeit des freien Mitglieds sein wird abgelehnt. Für den Koeffizienten bei Unbekannt t=5,79405 und p=0,001158. Mit anderen Worten, die Wahrscheinlichkeit, dass die richtige Hypothese über die Geringfügigkeit des Koeffizienten für das Unbekannte verworfen wird, beträgt 0,12 %.

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

Das Problem der Zweckmäßigkeit des Kaufs eines Aktienpakets

Die multiple Regression in Excel wird mit demselben Datenanalysetool durchgeführt. Betrachten Sie ein spezifisches angewandtes Problem.

Die Geschäftsführung von NNN muss entscheiden, ob es ratsam ist, eine 20 %-Beteiligung an MMM SA zu erwerben. Die Kosten für das Paket (JV) betragen 70 Millionen US-Dollar. NNN-Spezialisten sammelten Daten über ähnliche Transaktionen. Es wurde beschlossen, den Wert des Aktienpakets nach folgenden Parametern, ausgedrückt in Millionen US-Dollar, zu bewerten:

• Kreditorenbuchhaltung (VK);
• Jahresumsatz (VO);
• Forderungen (VD);
• Kosten des Anlagevermögens (SOF).

Zusätzlich wird der Parameter Lohnrückstände des Unternehmens (V3 P) in Tausend US-Dollar verwendet.

Lösung mit Excel-Tabelle

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

• Rufen Sie das Fenster "Datenanalyse" auf;
• Wählen Sie den Abschnitt "Regression" aus.
• Geben Sie im Feld "Eingabeintervall Y" den Wertebereich der abhängigen Variablen aus Spalte G ein.
• Klicken Sie auf das Symbol mit dem roten Pfeil rechts neben dem Feld „Eingabeintervall X“ und wählen Sie auf dem Blatt einen Bereich aller Werte aus Spalten B,C, D, F.

Wählen Sie „Neues Arbeitsblatt“ und klicken Sie auf „Ok“.

Holen Sie sich die Regressionsanalyse für das gegebene Problem.

Prüfung der Ergebnisse und Schlussfolgerungen

„Wir erheben“ aus den oben in der Excel-Tabelle dargestellten gerundeten Daten die Regressionsgleichung:

SP \u003d 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, erhalten sie einen Wert von 64,72 Millionen US-Dollar. Das bedeutet, dass die Aktien von JSC MMM nicht gekauft werden sollten, da ihr Wert von 70 Millionen US-Dollar eher überbewertet ist.

Wie Sie sehen, war es durch die Verwendung der Excel-Tabelle und der Regressionsgleichung möglich, eine fundierte Entscheidung über die Durchführbarkeit einer sehr spezifischen Transaktion zu treffen.

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

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 verwendet wird. Grundsätzlich werden alle Aktionen der praktischen und Laborübungen in Excel durchgeführt, was die Arbeit erheblich erleichtert, indem bestimmte Aktionen detailliert erklärt werden. So wird eines der Analysewerkzeuge „Regression“ verwendet, um einen Graphen für eine Reihe von Beobachtungen auszuwählen, die die Methode der kleinsten Quadrate verwenden. Lassen Sie uns überlegen, was dieses Tool des Programms ist und welchen Nutzen es für die Benutzer hat. Nachfolgend finden Sie auch eine kurze, aber verständliche Anleitung zum Erstellen eines Regressionsmodells.

Hauptaufgaben und Arten der Regression

Regression ist eine Beziehung zwischen gegebenen Variablen, aufgrund derer es möglich ist, die Prognose des zukünftigen Verhaltens dieser Variablen zu bestimmen. Variablen sind verschiedene periodische Phänomene, einschließlich menschlichem Verhalten. Diese Excel-Analyse wird verwendet, um den Einfluss 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. Mithilfe der Regression in Excel können Sie den Grad des Einflusses jedes dieser Faktoren basierend auf den Ergebnissen bestehender Verkäufe bestimmen und die erhaltenen Daten dann anwenden, um die Verkäufe für einen anderen Monat oder für ein anderes Geschäft in der Nähe zu prognostizieren.

Typischerweise wird die Regression als einfache Gleichung dargestellt, die die Abhängigkeiten und die Stärke der Assoziation zwischen zwei Gruppen von Variablen aufzeigt, wobei eine Gruppe abhängig oder endogen und die andere unabhängig oder exogen ist. Wenn es eine Gruppe von miteinander verbundenen 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 Erstellen eines Regressionsmodells sind wie folgt:

1. Auswahl signifikanter unabhängiger Variablen (Х1, Х2, …, Xk).
2. Funktionstyp auswählen.
3. Konstruktion von Schätzungen für Koeffizienten.
4. Gebäude Vertrauensintervalle und Regressionsfunktionen.
5. Überprüfung der Signifikanz der berechneten Schätzungen und der konstruierten Regressionsgleichung.

Es gibt verschiedene Arten von Regressionsanalysen:

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

Es gibt zwei Arten von Regressionsgleichungen:

1. Linear, was eine streng lineare Beziehung zwischen Variablen darstellt.
2. Nichtlinear – Gleichungen, die Potenzen, Brüche und trigonometrische Funktionen enthalten können.

Modellbauanleitung

Um eine bestimmte Konstruktion in Excel auszuführen, müssen Sie die Anweisungen befolgen:

Für die weitere Berechnung sollte die Funktion "Linear()" verwendet werden, die Y-Werte, X-Werte, Konstante und Statistiken angibt. Danach bestimmen Sie die Punktemenge auf der Regressionsgerade mit der Funktion "Trend" - Y-Werte, X-Werte, Neue Werte, Konst. Berechnen Sie unter Verwendung der gegebenen Parameter den unbekannten Wert der Koeffizienten, basierend auf den gegebenen Bedingungen des Problems.

Zeigt den Einfluss einiger Werte (unabhängig, unabhängig) auf die abhängige Variable. Zum Beispiel, wie die Zahl der erwerbstätigen Bevölkerung von der Zahl der Unternehmen, Löhne und anderen Parametern abhängt. Oder: Wie wirken sich Auslandsinvestitionen, Energiepreise etc. auf die Höhe des BIP aus?

Das Ergebnis der Analyse ermöglicht Ihnen eine Priorisierung. Und basierend auf den Hauptfaktoren, um die Entwicklung vorrangiger Bereiche vorherzusagen, zu planen und Managemententscheidungen zu treffen.

Regression passiert:

linear (y = a + bx);

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

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

Potenz (y = a*x^b);

hyperbolisch (y = b/x + a);

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

exponentiell (y = a * b^x).

Betrachten Sie das Beispiel der Erstellung eines Regressionsmodells in Excel und der Interpretation der Ergebnisse. Nehmen wir eine lineare Regression.

Aufgabe. Bei 6 Unternehmen im Durchschnitt monatlich Lohn und die Zahl der Rentner. Es ist notwendig, die Abhängigkeit der Anzahl der Rentner vom Durchschnittsgehalt zu ermitteln.

Das lineare Regressionsmodell hat folgende Form:

Y \u003d ein 0 + ein 1 x 1 + ... + ein k x k.

Dabei sind a die Regressionskoeffizienten, x die Einflussgrößen und k die Anzahl der Faktoren.

In unserem Beispiel ist Y der Indikator für gekündigte Arbeitnehmer. Der Einflussfaktor ist der Lohn (x).

Excel verfügt über integrierte Funktionen, mit denen die Parameter eines linearen Regressionsmodells berechnet werden können. Aber das Analyse-ToolPak-Add-In macht es schneller.

Aktivieren Sie ein leistungsstarkes Analysetool:

1. Klicken Sie auf die Schaltfläche "Office" und wechseln Sie zur Registerkarte "Excel-Optionen". „Zusätze“.

2. Unten, unter der Dropdown-Liste, im Feld "Verwaltung" befindet sich die Aufschrift "Excel-Add-Ins" (falls nicht vorhanden, klicken Sie auf das Kontrollkästchen rechts und wählen Sie es aus). Und eine Go-Taste. Klicken.

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

Nach der Aktivierung ist das Add-on unter der Registerkarte Daten verfügbar.

Nun beschäftigen wir uns direkt mit der Regressionsanalyse.

1. Öffnen Sie das Menü des Datenanalyse-Tools. 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 Anfangsdaten geben wir den Bereich des beschriebenen Parameters (Y) und den ihn beeinflussenden Faktor (X) an. Der Rest kann abgeschlossen sein oder auch nicht.

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

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

R-Quadrat ist das Bestimmtheitsmaß. In unserem Beispiel sind es 0,755 oder 75,5 %. Das bedeutet, dass die berechneten Parameter des Modells die Beziehung zwischen den untersuchten Parametern zu 75,5 % erklären. Je höher das Bestimmtheitsmaß, desto besseres 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 nicht im Modell beschrieben sind, beeinflussen auch den Wert des analysierten Parameters.

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

Auch statistische Datenverarbeitungen können mit dem Add-in durchgeführt werden ANALYSE-PAKET(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.

Das in Abb. 63.

Analysetool « REGRESSION» wird verwendet, um ein Diagramm mit der Methode der kleinsten Quadrate an eine Reihe von Beobachtungen anzupassen. Die Regression wird verwendet, um die Auswirkung der Werte einer oder mehrerer unabhängiger Variablen auf eine einzelne abhängige Variable zu analysieren. Beispielsweise wird die sportliche Leistung eines Athleten von mehreren Faktoren beeinflusst, darunter Alter, Größe und Gewicht. Es ist möglich, den Grad des Einflusses jedes dieser drei Faktoren auf die Leistung eines Athleten zu berechnen und dann die erhaltenen Daten zu verwenden, um die Leistung eines anderen Athleten vorherzusagen.

Das Regressionswerkzeug verwendet die Funktion NÄCHSTES.

REGRESS-Dialogfeld

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

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

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

Ausgabebereich Geben Sie einen Verweis auf die obere linke Zelle des Ausgabebereichs ein. Ordnen Sie mindestens sieben Spalten für die Ergebnistabelle zu, darunter: Ergebnisse der Varianzanalyse, Koeffizienten, Standardfehler der Y-Berechnung, Standardabweichungen, Anzahl der Beobachtungen, Standardfehler für Koeffizienten.

Neues Arbeitsblatt Aktivieren Sie dieses Kontrollkästchen, um ein neues Arbeitsblatt in der Arbeitsmappe zu öffnen und die Analyseergebnisse ab Zelle A1 einzufügen. Geben Sie bei Bedarf einen Namen für das neue Blatt in das Feld gegenüber der entsprechenden Optionsfeldposition ein.

Neue Arbeitsmappe Aktivieren Sie dieses Kontrollkästchen, um eine neue Arbeitsmappe zu erstellen, in der die Ergebnisse einem neuen Blatt 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.

Darstellung der Residuen 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 Werte im Vergleich zu den beobachteten Werten darzustellen.

Normales Wahrscheinlichkeitsplot Aktivieren Sie das Kontrollkästchen, um die normale Wahrscheinlichkeit darzustellen.

Funktion NÄCHSTES

Um Berechnungen durchzuführen, wählen Sie mit dem Cursor die Zelle aus, in der der Durchschnittswert angezeigt werden soll, 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 NÄCHSTES berechnet Statistiken für eine Reihe mit der Methode der kleinsten Quadrate, um eine gerade Linie zu berechnen, die die verfügbaren Daten am besten annähert, und gibt dann ein Array zurück, das die resultierende gerade Linie beschreibt. Sie können die Funktion auch kombinieren NÄCHSTES 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 Matrixformel 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),

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

NÄCHSTES(bekannte_y-Werte; bekannte_x-Werte; const; Statistiken)

Bekannte_y-Werte – die Menge der y-Werte, die bereits für die Beziehung y=mx+b bekannt sind.

Wenn das Array von known_y eine Spalte hat, dann wird jede Spalte des Arrays von known_x als separate Variable interpretiert.

Wenn das Array von known_y eine Zeile hat, dann wird jede Zeile des Arrays von known_x als separate Variable interpretiert.

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

Das Array known_x kann einen oder mehrere Sätze von Variablen enthalten. Wenn nur eine Variable verwendet wird, können arrays_known_y_values ​​​​und known_x_values ​​eine beliebige Form haben - solange sie dieselbe 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 weggelassen wird, dann wird davon ausgegangen, dass dieses Array (1;2;3;...) die gleiche Größe wie array_known_y hat.

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

Wenn das Argument "const" TRUE ist oder weggelassen wird, dann wird die Konstante b normal ausgewertet.

Ist das Argument „const“ FALSE, dann wird der Wert von b mit 0 angenommen und die Werte von m so gewählt, dass die Beziehung y=mx erfüllt ist.

Statistics ist ein boolescher Wert, der angibt, ob zusätzliche Regressionsstatistiken zurückgegeben werden sollen.

Wenn die Statistik TRUE ist, gibt RGP 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 die Statistik FALSCH ist oder weggelassen wird, gibt RGP nur die Koeffizienten m und die Konstante b zurück.

Zusätzliche Regressionsstatistiken (Tabelle 17)

Wert	Beschreibung
se1,se2,...,sen	Standardfehlerwerte für die Koeffizienten m1,m2,...,mn.
seb	Standardwert Fehler für die Konstante b (seb = #N/A, wenn das Argument 'const' FALSCH ist).
r2	Bestimmungsfaktor. Die tatsächlichen Werte von y 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, d. h. es gibt keinen Unterschied zwischen den tatsächlichen und den geschätzten Werten von y. Andernfalls, wenn der Koeffizient des Determinismus 0 ist, macht es keinen Sinn, die Regressionsgleichung zu verwenden, um y-Werte vorherzusagen. Zum bekommen Weitere Informationen Möglichkeiten zur Berechnung von r2 finden Sie unter „Bemerkungen“ am Ende dieses Abschnitts.
Sey	Der Standardfehler für die y-Schätzung.
F	F-Statistik oder F-beobachteter Wert. Die F-Statistik wird verwendet, um zu bestimmen, ob eine 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 Konfidenzniveau des Modells zu bestimmen, müssen Sie die Werte in der Tabelle mit der von RGP zurückgegebenen F-Statistik vergleichen. Weitere Informationen zur Berechnung von df finden Sie unter „Bemerkungen“ am Ende dieses Abschnitts. Beispiel 4 unten zeigt die Verwendung von F und df.
ssreg	Regression Summe der Quadrate.
ssresid	Residuensumme der Quadrate. Weitere Informationen zum Berechnen von ssreg und ssresid finden Sie unter „Hinweise“ am Ende dieses Abschnitts.

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

Anmerkungen:

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

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

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

Die Geradengleichung hat die Form 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 mit den folgenden Formeln erhalten:

Steigung: INDEX(LINEST(bekannte_y's, bekannte_x's), 1)

Y-Achsenabschnitt: INDEX(LINEST(bekannte_y's, bekannte_x's), 2)

Die Genauigkeit der Annäherung unter Verwendung der von der RGP-Funktion berechneten geraden Linie hängt vom Grad der Datenstreuung ab. Je näher die Daten an einer geraden Linie liegen, desto genauer ist das von RGP verwendete Modell. Die RGP-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 mit den folgenden Formeln berechnet:

wobei x und y die Stichprobenmittelwerte sind, zum Beispiel x = AVERAGE(known_x's) und y = AVERAGE(known_y's).

Die Anpassungsfunktionen RGP und LGRFPRIBL können eine gerade oder exponentielle Kurve berechnen, die am besten zu den Daten passt. Sie beantworten jedoch nicht die Frage, welches der beiden Ergebnisse besser geeignet ist, das Problem zu lösen. Sie können auch die Funktion TREND(bekannte_y-Werte; bekannte_x-Werte) für eine gerade Linie oder die Funktion GROWTH(bekannte_y-Werte; bekannte_x-Werte) für eine Exponentialkurve berechnen. Diese Funktionen geben, wenn sie im new_x_values-Argument weggelassen werden, ein Array von berechneten y-Werten für die tatsächlichen x-Werte gemäß einer geraden Linie oder Kurve zurück. Anschließend können Sie die errechneten Werte mit den tatsächlichen Werten vergleichen. Sie können auch Diagramme für den visuellen Vergleich erstellen.

Beim Durchführen 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. Microsoft Excel berechnet dann die Gesamtsumme der Quadrate (stotal). Wenn const = TRUE oder dieses Argument nicht angegeben ist, ist die Gesamtsumme der Quadrate gleich der Summe der quadrierten Differenzen der realen y-Werte und der mittleren y-Werte. Wenn const = FALSE, ist die Summe der Quadrate gleich der Summe der Quadrate der realen y-Werte (ohne Subtraktion des Mittelwerts y vom Quotienten y). Danach kann die Regressionsquadratsumme wie folgt berechnet werden: ssreg = sges - ssresid. Je kleiner die Residualsumme der Quadrate ist, desto größer ist der Wert des Determinismuskoeffizienten r2, der angibt, wie gut die durch Regressionsanalyse erhaltene Gleichung die Beziehungen zwischen Variablen erklärt. Der Koeffizient r2 ist gleich ssreg/stotal.

In einigen Fällen haben eine oder mehrere X-Spalten (vorausgesetzt, Y- und X-Werte befinden sich in Spalten) keinen zusätzlichen Vorhersagewert in 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 "Kollinearität" genannt, da die redundanten Spalten von X als Summe mehrerer nicht redundanter Spalten dargestellt werden können. RGP prüft auf Kollinearität und entfernt alle redundanten X-Spalten aus dem Regressionsmodell, wenn es irgendwelche findet. Entfernte X-Spalten können in der RGP-Ausgabe durch einen Faktor von 0 und einen Wert von se von 0 identifiziert werden. Das Entfernen einer oder mehrerer Spalten als redundant ändert den Wert von df, da er von der Anzahl von X-Spalten abhängt, die tatsächlich für Vorhersagezwecke verwendet werden. Siehe Beispiel 4 unten für weitere Details zur Berechnung von df. Wenn sich df aufgrund der Entfernung redundanter Spalten ändert, ändern sich auch die Werte von sey und F. Es wird oft nicht empfohlen, Kollinearität zu verwenden. Es sollte jedoch verwendet werden, wenn einige X-Spalten 0 oder 1 enthalten, als Indikator dafür, ob die Testperson in einer separaten Gruppe ist. Wenn const = TRUE oder dieses Argument nicht angegeben ist, fügt RGP eine zusätzliche X-Spalte ein, um den Schnittpunkt zu simulieren. Wenn es eine Spalte mit den Werten 1 für Männer und 0 für Frauen gibt und es eine Spalte mit den Werten 1 für Frauen und 0 für Männer gibt, wird die letzte Spalte entfernt, da ihre Werte aus dem entnommen werden können 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, lautet wie folgt: Wenn es k bekannte_x Spalten gibt und const = TRUE oder nicht angegeben ist, dann ist df = n - k - 1. Wenn const = FALSE, dann ist df = n - k. In beiden Fällen erhöht das Entfernen der X-Spalten aufgrund der Kollinearität den Wert von df um 1.

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

Wenn Sie beispielsweise ein Array von Konstanten als Known_x_values-Argument eingeben, verwenden Sie ein Semikolon, um Werte in derselben Zeile zu trennen, und einen Doppelpunkt, um Zeilen zu trennen. Trennzeichen können je nach Einstellung im Fenster "Sprache und Standards" in der Systemsteuerung variieren.

Beachten Sie, 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 NÄCHSTES, unterscheidet sich vom Hauptalgorithmus der Funktionen NEIGUNG Und LINIENSEGMENT. Unterschiede zwischen Algorithmen können zu unterschiedlichen Ergebnissen für unsichere und kollineare Daten führen. Wenn beispielsweise die Datenpunkte des Arguments von known_y 0 und die Datenpunkte des Arguments von known_x 1 sind, dann:

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

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

Zusätzlich zum Berechnen von Statistiken für andere Regressionstypen kann RGP verwendet werden, um Bereiche für andere Regressionstypen zu berechnen, indem Funktionen der x- und y-Variablen als eine Reihe von x- und y-Variablen für RGP eingegeben werden. Zum Beispiel die folgende Formel:

RGP(y-Werte, x-Werte^COLUMN($A:$C))

arbeitet mit einer Spalte von Y-Werten und einer Spalte von 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 für Berechnungen anderer Regressionstypen geändert werden, in einigen Fällen sind jedoch Anpassungen der Ausgabewerte und anderer Statistiken erforderlich.

Mit dem MS Excel-Paket können Sie die meiste Arbeit beim Erstellen einer linearen Regressionsgleichung sehr schnell erledigen. Es ist wichtig zu verstehen, wie die Ergebnisse zu interpretieren sind.

Benötigt Add-on, um zu funktionieren Analysepaket, die im Menüpunkt aktiviert werden muss Dienst\Add-Ons

Klicken Sie in Excel 2007 zum Aktivieren des Analysepakets auf Gehe zu Block Excel-Optionen, indem Sie auf die Schaltfläche in der oberen linken Ecke und dann auf " Excel-Optionen» unten im Fenster:

Um ein Regressionsmodell zu erstellen, wählen Sie das Element aus Dienst\Datenanalyse\Regression. (In Excel 2007 befindet sich dieser Modus in der Daten/Datenanalyse/Regression). Es erscheint ein Dialogfeld, das ausgefüllt werden muss:

1) Eingabeintervall Y¾ enthält einen Link zu Zellen, die die Werte des resultierenden Attributs enthalten j. Werte müssen in einer Spalte stehen;

2) Eingabeintervall X¾ enthält einen Link zu Zellen, die die Werte der Faktoren enthalten. Werte müssen in Spalten sein;

3) Zeichen Stichworte gesetzt, wenn die ersten Zellen erklärenden Text (Datenbeschriftungen) enthalten;

4) Zuverlässigkeitsstufe¾ ist das Konfidenzniveau, das standardmäßig mit 95 % angenommen wird. Wenn dieser Wert nicht zu Ihnen passt, müssen Sie diese Funktion aktivieren und den erforderlichen Wert eingeben.

5) Zeichen Null konstant ist enthalten, wenn es notwendig ist, eine Gleichung zu konstruieren, in der die freie Variable ;

6) Ausgabeoptionen bestimmen, wo die Ergebnisse platziert werden sollen. Standard-Build-Modus Neues Arbeitsblatt;

7) Blockieren Überreste erlaubt Ihnen, die Ausgabe von Residuen und die Konstruktion ihrer Graphen einzubeziehen.

Das Ergebnis sind Informationen, die alle enthalten notwendige Informationen und in drei Blöcke gruppiert: Regressionsstatistik, Varianzanalyse , Kontoauszug. Betrachten wir sie genauer.

1. Regressionsstatistik:

mehrere R ist definiert durch die Formel ( Pearson-Korrelationskoeffizient);

R (Bestimmtheitsmaß);

Normalisiert R-Quadrat wird durch die Formel berechnet (wird verwendet für multiple Regression);

Standart Fehler S nach der Formel berechnet ;

Beobachtungen ¾ ist die Datenmenge N.

2. Varianzanalyse, Linie Rückfall:

Parameter df gleich M(Anzahl der Sätze von Faktoren X);

Parameter SS wird durch die Formel bestimmt;

Parameter MS wird durch die Formel bestimmt;

Statistiken F wird durch die Formel bestimmt;

Bedeutung F. Wenn die resultierende Zahl größer als ist, wird die Hypothese akzeptiert (kein linearer Zusammenhang), andernfalls wird die Hypothese akzeptiert (es besteht ein linearer Zusammenhang).

3. Varianzanalyse, Linie Rest:

Parameter df gleich ;

Parameter SS wird durch die Formel bestimmt ;

Parameter MS wird durch die Formel bestimmt.

4. Varianzanalyse, Linie Gesamt enthält die Summe der ersten beiden Spalten.

5. Varianzanalyse, Linie Y-Kreuzung enthält den Wert von Koeffizient , Standardfehler und T-Statistiken.

P-Wert ¾ ist der Wert der Signifikanzniveaus, die dem berechneten entsprechen T- Statistiker. Bestimmt durch den STUDENTEN ( T-Statistiken; ). Wenn P-Wert überschreitet , dann ist die entsprechende Variable statistisch nicht signifikant und kann aus dem Modell ausgeschlossen werden.

untere 95% Und Die besten 95 %¾ sind die unteren und oberen Grenzen der 95-Prozent-Konfidenzintervalle für die Koeffizienten der theoretischen linearen Regressionsgleichung. Wenn im Dateneingabeblock der Wert Vertrauensniveau standardmäßig belassen wurde, werden die letzten beiden Spalten die vorherigen duplizieren. Wenn der Benutzer einen benutzerdefinierten Konfidenzwert eingegeben hat, enthalten die letzten beiden Spalten die Unter- und Obergrenzenwerte für das angegebene Konfidenzniveau.

6. Varianzanalyse, die Zeilen enthalten die Werte der Koeffizienten, Standardfehler, T-Statistiker, P-Werte und Konfidenzintervalle für die entsprechenden .

7. Sperren Kontoauszug enthält die Werte der Vorhersage j(in unserer Notation ist es ) und Reste .