RGP-Funktion

Beschreibung

Die Funktion RGP berechnet die Statistik für eine Linie nach der Methode der kleinsten Quadrate, um eine gerade Linie zu berechnen, die am besten an die Daten angepasst ist, und gibt dann eine Matrix zurück, die die Linie beschreibt. Sie können RGP auch mit anderen Funktionen kombinieren, um die Statistiken für andere Modelltypen zu berechnen, die lineare unbekannte Parameter aufweisen, einschließlich polynomischer, logarithmischer und exponentieller Reihen sowie Potenzen. Da diese Funktion eine Matrix von Werten zurückgibt, muss die Formel als Matrixformel eingegeben werden. Anweisungen dazu sind nach den Beispielen in diesem Artikel angegeben.

Die Gleichung einer solchen Geraden lautet:

y = mx + b

– oder –

y = m1x1 + m2x2 + ... + b

, wenn mehrere Bereiche von x-Werten vorhanden sind, wobei die abhängigen y-Werte eine Funktion der unabhängigen x-Werte sind. Die m-Werte sind Koeffizienten, die jedem x-Wert entsprechen, und b ist ein konstanter Wert. Beachten Sie, dass y, x und m Vektoren sein können. Das Array, das die LINEST-Funktion zurückgibt, ist {mn,mn-1,...,m1,b}. LINEST kann auch zusätzliche Regressionsstatistiken zurückgeben.

Syntax

RGP(Y_Werte;[X_Werte];[Konstante];[Stats])

Die Syntax der Funktion RGP weist die folgenden Argumente auf:

Syntax

• Y_Werte    Erforderlich. Die y-Werte, die Ihnen bereits aus der Beziehung y = mx + b bekannt sind.

  • Besteht der Bereich der Y_Werte aus nur einer Spalte, wird jede Spalte für X_Werte als eigenständige Variable interpretiert.

  • Besteht der Bereich der Y_Werte aus nur einer Zeile, wird jede Zeile für X_Werte als eigenständige Variable interpretiert.

• X_Werte    Optional. Die x-Werte, die Ihnen möglicherweise bereits aus der Beziehung y = mx + b bekannt sind.

  • Der Bereich der X_Werte kann eine oder mehrere Variablengruppen umfassen. Wird nur eine Variable verwendet, können Y_Werte und X_Werte Bereiche beliebiger Form sein, solange sie dieselben Dimensionen haben. Werden mehrere Variablen verwendet, muss Y_Werte ein Vektor sein (das heißt ein Bereich, der aus nur einer Zeile oder nur einer Spalte besteht).

  • Fehlt die Angabe X_Werte, wird an ihrer Stelle die Matrix {1.2.3...} angenommen, die genauso viele Elemente wie Y_Werte enthält.

• Konstante    Optional. Ein Wahrheitswert, der angibt, ob die Konstante b den Wert 0 annehmen soll.

  • Ist Konstante mit WAHR belegt oder nicht angegeben, wird b normal berechnet.

  • Ist Konstante mit FALSCH belegt, wird b gleich 0 festgelegt, und die m-Werte werden so angepasst, dass sie zu der Beziehung y = mx passen.

• Stats    Optional. Ein Wahrheitswert, der angibt, ob zusätzliche Regressionskenngrößen zurückgegeben werden sollen.

  • Wenn stats den Wert TRUE hat, gibt LINEST die zusätzlichen Regressionsstatistiken zurück. Daher ist das zurückgegebene Array {mn,mn-1,...,m1,b; sen,sen-1,...,se1,seb; r^2,sey; F,df; ssreg,ssresid}.

  • Ist Stats mit FALSCH belegt oder nicht angegeben, gibt RGP nur die m-Koeffizienten sowie die Konstante b zurück.

    Die folgenden Regressionskenngrößen (-statistiken) können zusätzlich ermittelt werden:

Die folgende Abbildung zeigt, in welcher Reihenfolge die zusätzlichen Regressionskenngrößen zurückgegeben werden.

Hinweise

• Jede Gerade lässt sich durch ihre Steigung und die jeweilige Anfangsordinate (y-Achsenabschnitt) beschreiben:

  Steigung (m):
  Um die Steigung einer Linie zu finden, die häufig als m geschrieben wird, nehmen Sie zwei Punkte auf der Linie, (x1,y1) und (x2,y2); Die Steigung ist gleich (y2 - y1)/(x2 - x1).

  Y-Intercept (b):
  Der y-Abschnitt einer Linie, die häufig als b geschrieben wird, ist der Wert von y an dem Punkt, an dem die Linie die y-Achse kreuzt.

  Eine Gerade wird durch die Gleichung y = mx + b beschrieben. Sobald Ihnen die Werte von m und b bekannt sind, können Sie alle Punkte der Geraden berechnen, indem Sie den jeweiligen y- oder x-Wert in die Gleichung einsetzen. Sie können dafür auch die TREND-Funktion verwenden.

• Wenn nur eine unabhängige x-Variable vorliegt, können Sie die Steigung und den y-Achsenabschnitt direkt mithilfe der folgenden Formeln ermitteln:

  Steigung:
  =INDEX(LINEST(known_y's;known_x's);1)

  Y-Intercept:
  =INDEX(LINEST(known_y;known_x's);2)

• Die Genauigkeit einer von der RGP-Funktion berechneten Geraden hängt davon ab, wie sehr die betreffenden Daten gestreut sind. Je linearer sich die Daten verhalten, desto genauer ist das von RGP ermittelte Modell. RGP verwendet die Methode der kleinsten Quadrate, um die für die jeweiligen Daten beste Anpassung zu ermitteln. Wenn nur eine unabhängige x-Variable vorliegt, werden m und b entsprechend der folgenden Formeln berechnet:

  

  

  wobei x und y Beispielmöglichkeiten darstellen, d. h. x = MITTELWERT(X_Werte) und y = MITTELWERT(Y_Werte).

• Die Regressionsfunktionen RGP (lineare Regression) und RKP (exponentielle Regression) können die Koeffizienten der an die von Ihnen bereitgestellten Daten optimal angepassten Geraden beziehungsweise Exponentialkurve berechnen. Sie müssen dennoch entscheiden, welches der beiden Ergebnisse Ihren Daten eher entspricht. Bei einer Geraden können Sie TREND(Y_Werte;X_Werte) und bei einer Exponentialkurve VARIATION(Y_Werte;X_Werte) berechnen. Werden diese Funktionen ohne das Argument Neue_x_Werte verwendet, geben sie eine Matrix mit y-Werten zurück, die an den x-Werten Ihrer tatsächlichen Datenpunkte als Vorhersagewerte auf der Geraden oder Exponentialkurve liegen. Diese Vorhersagewerte können Sie mit den tatsächlichen Werten vergleichen. Um eine bessere Vergleichsmöglichkeit zu haben, kann es sinnvoll sein, die Werte in Diagrammen darzustellen.

• Bei der Regressionsanalyse berechnet Excel für jeden Punkt das Quadrat der Differenz zwischen dem für diesen Punkt erwarteten y-Wert und dem entsprechenden tatsächlichen y-Wert. Die Summe dieser quadrierten Differenzen wird als Residual-Quadratsumme (ssresid) bezeichnet. Anschließend berechnet Excel die Gesamtsumme der Abweichungsquadrate (sstotal). Ist das Argument Konstante mit WAHR belegt oder nicht angegeben, entspricht die Gesamtsumme der Abweichungsquadrate der Summe der quadratischen Differenzen zwischen den tatsächlichen y-Werten und dem Mittelwert der y-Werte. Wenn das Argument Konstante mit FALSCH belegt ist, entspricht die Gesamtsumme der Abweichungsquadrate den Quadraten der tatsächlichen y-Werte (ohne Subtraktion der Mittelwerte aller y-Werte von jedem einzelnen y-Wert). Anschließend kann die Regressions-Quadratsumme (ssreg) anhand der folgenden Gleichung berechnet werden: ssreg = sstotal - ssresid. Je kleiner die Restsumme der Quadrate im Vergleich zur Gesamtsumme der Quadrate ist, desto größer ist der Wert des Bestimmungskoeffizienten^r2, der ein Indikator dafür ist, wie gut die aus der Regressionsanalyse resultierende Gleichung die Beziehung zwischen den Variablen erklärt. Der Wert von r² entspricht ssreg/sstotal.

• In einigen Fällen kann eine oder mehrere der X-Spalten (davon ausgehen, dass Y- und X-Spalten in Spalten enthalten sind) möglicherweise keinen zusätzlichen Vorhersagewert im Vorhandensein der anderen X-Spalten aufweisen. Anders ausgedrückt: Das Entfernen einer oder mehrerer X-Spalten kann zu vorhergesagten Y-Werten führen, die genauso genau sind. In diesem Fall sollten diese redundanten X-Spalten aus dem Regressionsmodell weggelassen werden. Dieses Phänomen wird als "Kollinearität" bezeichnet, da jede redundante X-Spalte als Summe von Vielfachen der nicht redundanten X-Spalten ausgedrückt werden kann. Die LINEST-Funktion überprüft die Kollinearität und entfernt alle redundanten X-Spalten aus dem Regressionsmodell, wenn sie identifiziert werden. Entfernte X-Spalten können in der LINEST-Ausgabe als 0 Koeffizienten zusätzlich zu 0 se-Werten erkannt werden. Wenn eine oder mehrere Spalten als redundant entfernt werden, ist df betroffen, da df von der Anzahl der X-Spalten abhängt, die tatsächlich für Vorhersagezwecke verwendet werden. Ausführliche Informationen zur Berechnung von df finden Sie unter Beispiel 4. Wenn df geändert wird, weil redundante X-Spalten entfernt werden, sind auch die Werte sey und F betroffen. Kollinearität sollte in der Praxis relativ selten sein. Ein Fall, in dem die Wahrscheinlichkeit höher ist, ist jedoch, wenn einige X-Spalten nur 0- und 1-Werte als Indikatoren dafür enthalten, ob ein Subjekt in einem Experiment Mitglied einer bestimmten Gruppe ist oder nicht. Wenn const = TRUE oder ausgelassen wird, fügt die LINEST-Funktion effektiv eine zusätzliche X-Spalte aller 1 Werte ein, um den Intercept zu modellieren. Wenn Sie über eine Spalte mit einer 1 für jedes Thema verfügen, falls männlich, oder 0, wenn nicht, und Sie auch eine Spalte mit einer 1 für jeden Betreff haben, wenn weiblich, oder 0, wenn nicht, ist diese zweite Spalte redundant, da die Einträge darin aus dem Subtrahieren des Eintrags in der Spalte "männlicher Indikator" vom Eintrag in der zusätzlichen Spalte aller 1-Werte abgerufen werden können, die von der LINEST-Funktion hinzugefügt werden.

• Der Wert "df" wird folgendermaßen berechnet, wenn keine X-Spalten aufgrund von Kollinearität aus dem Modell entfernt werden: Wenn k Spalten für X_Werte vorhanden sind und Konstante mit WAHR belegt oder nicht angegeben ist, gilt df = n – k – 1. Wenn Konstante mit FALSCH belegt ist, gilt df = n - k. In beiden Fällen wird der Wert "df" um die Anzahl der aufgrund von Kollinearität entfernten Spalten erhöht.

• Wird eine Matrixkonstante (wie zum Beispiel X_Werte) als Argument eingegeben, müssen Sie Punkte verwenden, um Werte innerhalb derselben Zeile zu trennen, und Semikola, um die Zeilen zu trennen. Die Trennzeichen können je nach den Ländereinstellungen unterschiedlich sein.

• Beachten Sie, dass mithilfe einer Regressionsgleichung vorhergesagte y-Werte sind möglicherweise ungültig, wenn diese außerhalb des Bereiches der y-Werte liegen, die Sie zur Ermittlung der Gleichung verwendet haben.

• Der zugrunde liegende Algorithmus in der RGP-Funktion unterscheidet sich vom zugrunde liegenden Algorithmus der Funktionen STEIGUNG und ACHSENABSCHNITT. Bei unbestimmten und kollinearen Daten kann der Unterschied zwischen diesen Algorithmen zu unterschiedlichen Ergebnissen führen. Wenn beispielsweise die Datenpunkte des Arguments Y_Werte den Wert 0 und die Datenpunkte des Arguments X_Werte den Wert 1 aufweisen, geschieht Folgendes:

  • RGP gibt einen Wert 0 zurück. Der Algorithmus der Funktion RGP soll vernünftige Ergebnisse für kollineare Daten zurückgeben, und in diesem Fall wird mindestens ein Ergebnis ermittelt.

  • STEIGUNG und ACHSENABSCHNITT geben den Fehlerwert #DIV/0! zurück. Der Algorithmus der Funktionen STEIGUNG und ACHSENABSCHNITT soll ausschließlich ein einziges Ergebnis ermitteln, und in diesem Fall sind mehrere Ergebnisse möglich.

• Neben der Verwendung von RKP zum Berechnen von Statistiken für andere Regressionstypen können Sie RGP zum Berechnen eines Bereichs von Regressionstypen verwenden, indem Sie Funktionen der x- und y-Variablen als x- und y-Reihen für RGP eingeben. Beispielsweise wird die folgende Formel:

  =RGP(Y_Werte; X_Werte^SPALTE($A:$C))

  verwendet, wenn Sie über eine Spalte von y-Werten und eine Spalte von x-Werte verfügen, um die kubische (Polynom der Ordnung 3) Annäherung in folgender Form zu berechnen:

  y = m1*x + m2*x^2 + m3*x^3 + b

  Sie können diese Formel anpassen, um andere Regressionstypen zu berechnen. In einigen Fällen ist dafür die Anpassung der Ausgabewerte und anderer Statistiken erforderlich.

• Der von der LINEST-Funktion zurückgegebene F-Test-Wert unterscheidet sich vom F-Test-Wert, der von der FTEST-Funktion zurückgegeben wird. LINEST gibt die F-Statistik zurück, während FTEST die Wahrscheinlichkeit zurückgibt.

Beispiele