Führen Regressionsanalyse in Excel 2007
Craig Stinson ist seit 1981 ein Industrie Journalist gewesen und dient als Redakteur von PC Magazine und Autor der mehreren Ausgaben des meistverkauften Ausführen von Microsoft Windows®. Zusätzlich wird ein Co-Autor zu sein, auf Ausführen von Microsoft Excel, ist er der Co-Autor von Microsoft Windows NT Workstation, Version 4. Er hat auch Musik-Rezensionen geschrieben für so bekannte Publikationen wie Billboard, der Boston Globe und den Christian Science Monitor .
In diesem Artikel
Das Verständnis lineare und exponentielle Regression
Excel enthält mehrere Array-Funktionen zum Durchführen einer linearen Regression (RGP tendenz FORECAST, SLOPE und STFEHLERYX) und zur Durchführung einer exponentiellen Regression (LOGEST und Wachstum). Sie geben diese Funktionen als Matrixformeln, und sie produzieren Array Ergebnisse. Sie können jede dieser Funktionen mit einem oder mehreren unabhängigen Variablen verwenden. Die folgende Liste definiert die verschiedenen Arten der Regression:
Exponentialregression Erzeugt eine exponentielle Kurve, die am besten eine Reihe von Daten paßt, die Sie vermuten, dass mit der Zeit nicht linear ändern. Zum Beispiel wird eine Reihe von Messungen des Bevölkerungswachstums fast immer besser durch eine exponentielle Kurve dargestellt werden als durch eine Linie.
Multiple Regression ist die Analyse von mehr als einem Satz von Daten, die oft ein realistischeres Projektion erzeugt. Sie können sowohl linear als auch exponentielle multiple Regressionsanalysen durchzuführen. Angenommen, Sie den entsprechenden Preis für ein Haus in Ihrer Nähe projizieren wollen, basierend auf Quadratmeterzahl, Anzahl der Bäder, Losgröße und Alter. Unter Verwendung einer Mehrfachregressionsformel, können Sie einen Preis schätzen, auf Basis einer Datenbank von Informationen aus bestehenden Häusern gesammelt.
Regredieren in die Zukunft?
Der Begriff der Regression mag seltsam klingen, weil der Begriff in der Regel mit Bewegung verbunden ist, rückwärts, während sie in der Welt der Statistiken, Regression oft die Zukunft vorherzusagen verwendet wird. Einfach ausgedrückt, Regression ist ein statistisches Verfahren, die einen mathematischen Ausdruck findet, die eine Reihe von Daten am besten beschreibt.
Oft Unternehmen versuchen, die Zukunft mit Umsatz und Prozent-of-Sales auf Geschichte basiert Projektionen zu prognostizieren. Eine einfache Prozent-of-Sales-Technik identifiziert Aktive und passive, die zusammen mit einem Umsatz variieren, bestimmt den Anteil der einzelnen, und weist diese Prozentsatz. Obwohl die Verwendung von Prognose Prozent-of-Sales für langsames oder stetig kurzfristiges Wachstum oft ausreichend ist, verliert die Technik Genauigkeit Wachstum beschleunigt.
Regressionsanalyse verwendet komplexere Gleichungen größere Mengen von Daten zu analysieren, und übersetzt sie in Koordinaten auf einer Linie oder Kurve. In der nicht allzu fernen Vergangenheit, Regressionsanalyse nicht weit wegen der großen Menge an Berechnungen beteiligt verwendet wurde. Da Tabellenkalkulationsanwendungen wie Excel, begann bietet integrierte in Regressionsfunktionen hat sich die Verwendung von Regressionsanalyse weiter verbreitet.
Die Berechnung der linearen Regression
Die Gleichung y = mx + b beschreibt algebraisch eine gerade Linie für einen Satz von Daten mit einer unabhängigen Variablen, wobei x die unabhängige Variable, y ist die abhängige Variable, m die Steigung der Linie stellt, und b stellt den y-Achsenabschnitt. Wenn eine Zeile eine Anzahl von unabhängigen Variablen, die in einer Mehrfachregressionsanalyse, um ein erwartetes Ergebnis darstellt, nimmt die Gleichung der Regressionslinie die Form
in welcher Y die abhängige Variable ist, sind X 1 bis X n n unabhängigen Variablen, m 1 bis m n die Koeffizienten jeder unabhängigen Variablen sind, und b eine Konstante ist.
Die Funktion RGP
Die Funktion RGP verwendet diese allgemeinere Gleichung die Werte von m 1 bis m n und der Wert von b zurückzukehren. eine bekannte Menge von Werten für y und eine bekannte Menge von Werten für jede unabhängige Variable angegeben. Diese Funktion nimmt die Form = RGP (Y_Werte, X_Werte, const, Statistiken).
Das Argument der known_y ist der Satz von y -Werten Sie bereits kennen. Dieses Argument kann eine einzige Spalte, eine einzelne Reihe oder ein rechteckiger Bereich von Zellen sein. Wenn Y_Werte eine Spalte ist, jede Spalte in der Argumente der known_x ist eine unabhängige Variable betrachtet. Und falls Y_Werte eine einzelne Zeile ist, wobei jede Zeile in dem Argument der known_x eine unabhängige Variable betrachtet. Wenn Y_Werte ein rechteckiger Bereich ist, können Sie nur eine unabhängige Variable verwenden; X_Werte sollte in diesem Fall ein Bereich von der gleichen Größe und Form wie Y_Werte sein. Wenn Sie die X_Werte Argument weglassen, verwendet Excel die Reihenfolge 1, 2, 3, 4, und so weiter.
Die konst und Statistiken Argumente sind optional. Wenn entweder enthalten ist, muss es eine logische Konstant entweder WAHR oder FALSCH sein. (Sie können 1 für TRUE ersetzen und 0 für falsch.) Die Standardeinstellungen für const und Statistiken sind TRUE und FALSE sind. Wenn Sie const auf FALSCH gesetzt, b Excel Kräfte (das letzte Glied in der linearen Gleichung) 0 zu sein, wenn Sie Statistiken auf TRUE gesetzt, kehrte das Array von RGP enthält die folgenden Validierungsstatistik:
SE1 bis Sen
Abbildung 1 zeigt ein einfaches Beispiel für die Verwendung von RGP mit einer unabhängigen Variablen. Die Einträge in der Spalte B dieses Arbeitsblatt darstellen monatliche Produktnachfrage für ein kleines Unternehmen. Die Zahlen in der Spalte A stehen für die Monate in der Zeit. Angenommen, Sie die Steigung und y-Schnittpunkt der Regressionsgeraden berechnet werden sollen, die am besten die Beziehung zwischen der Nachfrage und den Monaten beschreiben. Mit anderen Worten, wollen Sie den Trend der Daten zu beschreiben. Um dies zu tun, wählen Sie den Bereich F6: G6, gibt die Formel = RGP (B2: B19, A2: A19). und drücken Sie Strg + Umschalt + Enter. Die resultierende Zahl in Zelle F6 ist 20,613, die Steigung der Regressionslinie; Die Zahl in Zelle G6 ist 4002,065, der Y-Achsenabschnitt der Leitung.
Abbildung 1 Die RGP-Funktion berechnet die Steigung und die y-Intercept einer Regressionsgeraden.
Die RGP und LOGEST Funktionen geben nur die y-Achsen-Koordinaten zur Berechnung von Linien und Kurven verwendet. Der Unterschied zwischen ihnen ist, dass RGP Projekte einer geraden Linie und LOGEST eine exponentielle Kurve projiziert. Sie müssen vorsichtig sein, um die entsprechende Funktion für die Analyse zur Hand passen. Die Funktion RGP könnte besser geeignet sein für Umsatzprognosen und die RKP-Funktion könnte besser geeignet für statistische Auswertungen oder Bevölkerungsentwicklung sein.
aus in einer echten (estate) Regressions Anwendung
Diese Anwendung verwendet die Funktion RGP die Statistik in dem Eingangsdatenbereich zu analysieren und eine Reihe von Ergebnissen auf der Grundlage ähnliche Statistiken im What You Want Bereich generiert. Die RGP-Array wird tatsächlich in ausgeblendeten Zeilen unter dem sichtbaren Bereich des Arbeitsblatts angeordnet ist, wie unten gezeigt. Die erste Reihe von Werten in der RGP Datenarray wird von der geschätzten Preis Formel verwendet, um einen Schätzwert zu extrapolieren.
In der Regel in diesem Arbeitsbuch, Zeilen- und Spaltenüberschriften sind versteckt, Zeilen 25 bis 37 ausgeblendet sind, Blattschutz eingeschaltet ist, und die Zellen werden mit Einträgen nur in den dafür vorgesehenen Eingabebereiche erlaubt verriegelt. Immobilien-Tipp: Da die Notiz auf dem Arbeitsblatt schon sagt, können Sie börsennotierten Wohnungspreis nutzen zu einem geschätzten Preis zu kommen, aber die tatsächlichen Verkaufspreise sind realistischer, wenn man sich bekommen kann.
Die Funktion TREND
RGP gibt eine mathematische Beschreibung der geraden Linie, die am besten passt Daten bekannt. TREND findet Punkte, die entlang dieser Linie und die fallen in die unbekannte Kategorie liegen. Sie können die Nummern von TREND zurück verwenden, um eine Trendlinie-eine gerade Linie zu zeichnen, den Sinn der tatsächlichen Daten erleichtern. Sie können auch TREND Extrapolation verwenden. machen oder intelligente Vermutungen über, basierend Zukunft Daten über die Tendenzen von bekannten Daten ausgestellt. (Sei vorsichtig. Obwohl Sie TREND können die gerade Linie zu zeichnen, die die bekannten Daten am besten passt, TREND kann Ihnen nicht sagen, ob diese Linie ein guter Indikator für die Zukunft. Validierungsstatistik zurück von RGP können Sie diese Beurteilung vorzunehmen helfen. ) die Trend-Funktion nimmt die Form = Trend (Y_Werte, X_Werte, Neue_x_Werte, const).
Die ersten beiden Argumente stellen die bekannten Werte der abhängigen und unabhängigen Variablen. Wie in RGP wird die Y_Werte Argument eine einzige Spalte, eine einzelne Reihe oder ein rechteckiger Bereich. Die X_Werte Argument folgt auch das Muster für RGP beschrieben. Die dritte und vierte Argumente sind optional. Wenn Sie Neue_x_Werte weglassen, hält die Funktion TREND Neue_x_Werte zu X_Werte identisch sein. Wenn Sie const sind, muss der Wert dieses Argument WAHR oder FALSCH sein (oder 1 oder 0). Wenn const TRUE ist, b TREND Kräfte 0 zu sein.
Um die Trendlinie Datenpunkte zu berechnen, die Ihre bekannten Daten am besten passen, lassen Sie einfach das dritte und vierte Argument von dieser Funktion. Die Ergebnisse Array wird die gleiche Größe wie die Reichweite des known_x sein. In Abbildung 2 haben wir TREND den Wert von jedem Punkt auf der Regressionslinie zu finden, verwendet, der die Daten aus dem Beispiel in Abbildung 1, diese Werte erstellen gesetzt beschreibt, wählten wir den Bereich C2: C19 und eingegebenen = Trend (B2: B19 , A2: A19) als Matrixformel mit Strg + Shift + Enter.
Für die Extrapolation aus den vorhandenen Daten, müssen Sie einen Bereich für Neue_x_Werte liefern. Sie können so viele oder so wenige Zellen für Neue_x_Werte liefern, wie Sie wollen. Das Ergebnis Array wird die gleiche Größe wie die Reichweite des new_x sein. In 3 haben wir TREND Nachfrage nach dem 19. zu berechnen, 20. und 21. Monat. Um bei diesen Werten ankommen, wir getippt 19 bis 21 in A21: A23, ausgewählt C21: C23 und eingegebenen = Trend (B2: B19, A2: A19, A21: A23) als Matrixformel durch Drücken von Strg + Umschalt + Eingabe.
Die Berechnung Exponentialregression
Im Gegensatz zu der linearen Regression, die Werte entlang einer geraden Linie Plots, beschreibt eine exponentielle Regression Kurve, die durch die Anordnung von Werten Berechnung benötigt es plotten. Die Gleichung, die eine exponentielle Regressionskurve beschreibt, ist wie folgt:
y = b * m1x1 M2X2 * *. * mnxn
Wenn Sie nur eine unabhängige Variable, dann wird die Gleichung wie folgt:
Die RKP-Funktion
Die RKP-Funktion funktioniert wie RGP, außer dass Sie es verwenden, um Daten zu analysieren, die nicht linear ist, und es gibt die Koordinaten einer exponentiellen Kurve statt einer geraden Linie. LOGEST kehrt Koeffizientenwerte für jede unabhängige Variable und einen Wert für die Konstante b. Diese Funktion nimmt die Form = RKP (Y_Werte, X_Werte, const, Statistiken).
LOGEST akzeptiert die gleichen Argumente wie die Funktion RGP und gibt ein Ergebnis-Array in der gleichen Art und Weise. Wenn Sie das optionale Statistiken Argument auf TRUE gesetzt, gibt die Funktion auch Validierungsstatistik.
Anmerkung: Die RGP und LOGEST Funktionen geben nur die y-Achse für die Berechnung der Linien und Kurven verwendet Koordinaten. Der Unterschied zwischen ihnen ist, dass RGP Projekte einer geraden Linie und LOGEST eine exponentielle Kurve projiziert. Sie müssen vorsichtig sein, um die entsprechende Funktion für die Analyse zur Hand passen. Die Funktion RGP könnte besser geeignet sein für Umsatzprognosen und die RKP-Funktion könnte besser geeignet für Anwendungen wie statistische Analysen oder Bevölkerungsentwicklung sein.
Die Wachstumsfunktion
Wo die RKP-Funktion eine mathematische Beschreibung der exponentiellen Regressionskurve liefert, die am besten eine Reihe von bekannten Daten passt, findet die GROWTH Funktionspunkte, die entlang dieser Kurve liegen. Die Wachstumsfunktion wirkt wie sein lineares Gegenstück, Trend, und nimmt die Form = GROWTH (Y_Werte, X_Werte, Neue_x_Werte, const).