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 es mehrere Bereiche von x-Werten gibt, wobei die abhängigen y-Werte eine Funktion der unabhängigen x-Werte sind. Die m-Werte sind Koeffizienten, die jedem x-Wert entspricht, und b ist ein Konstantenwert. Beachten Sie, dass y, x und m Vektoren sein können. Die Matrix, die die Funktion LINIENT zurückgibt, ist {mn,mn-1,...,m1,b}. LINIENT 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.

  • Ist Statistik WAHR, gibt LINIENT die zusätzliche Regressionsstatistik zurück. Als Ergebnis lautet das zurückgegebene Array {mn,mn-1,...,m1;b;sen,sen-1,...,se1,seb;r²,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 die Linie, (x1,y1) und (x2,y2); die Steigung gleich (y2 - y1)/(x2 - x1).

  Y-Abfangen (b):
  Der y-Abfang einer Zeile, häufig als b geschrieben, ist der Wert y an der Stelle, an der die Linie die y-Achse durchquert.

  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(LINIENT(known_y;known_x)1)

  Y-Abfangen:
  =INDEX(LINIENT(known_y;known_x)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 Summe der Quadrate ist, desto größer ist der Wert des Bestimmtheitskoeffizienten r^2.Dies ist ein Indikator dafür, wie gut die sich aus der Regressionsanalyse ergebende Formel die Beziehung zwischen den Variablen erklärt. Der Wert von r² entspricht ssreg/sstotal.

• In einigen Fällen hat eine oder mehrere X-Spalten (davon aus, dass sich Y- und X-Spalten in Spalten befinden) möglicherweise keinen zusätzlichen Vorhersagewert in Anwesenheit der anderen X-Spalten. Mit anderen Worten, das Entfernen einer oder mehrere x-Spalten kann zu vorhergesagten Y-Werten führen, die genauso genau sind. In diesem Fall sollten diese redundanten X-Spalten im Regressionsmodell weggelassen werden. Dieses Phänomen wird als "Collinearität" bezeichnet, da jede redundante X-Spalte als Summe von Vielfachen der nicht redundanten X-Spalten ausgedrückt werden kann. Die Funktion LINIENT überprüft die Collinearität und entfernt alle redundanten X-Spalten aus dem Regressionsmodell, wenn diese identifiziert werden. Entfernte X-Spalten können in der ZEILENT-Ausgabe als 0 Koeffizienten zusätzlich zu den 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 zu Vorhersagezwecken verwendet werden. Details 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. Die Collinearität sollte in der Praxis relativ selten sein. Ein Fall, in dem es wahrscheinlicher ist, ist jedoch, dass einige X-Spalten nur 0 und 1 Werte als Indikatoren für die Frage enthalten, ob ein Thema in einem Experiment Mitglied einer bestimmten Gruppe ist oder nicht. Wenn const = WAHR oder nicht angegeben ist, fügt die Funktion LINIENT effektiv eine zusätzliche X-Spalte mit allen 1 Werten ein, um den Intercept zu modellieren. Wenn Sie über eine Spalte mit einer 1 für jedes Subjekt verfügen, wenn männlich oder 0, und wenn nicht, und sie auch eine Spalte mit einer 1 für jedes Thema haben, wenn sie weiblich ist, oder 0, wenn nicht, ist diese zweite Spalte redundant, da Einträge in dieser Spalte durch Subtrahieren des Eintrags in der Spalte "Männlicher Indikator" aus dem Eintrag in der zusätzlichen Spalte aller 1 Werte, die von der Funktion LINIENT hinzugefügt werden, erhalten werden können.

• 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 Funktion LINIENT zurückgegebene F-Testwert unterscheidet sich vom F-Testwert, der von der Funktion FTEST zurückgegeben wird. LINIENT gibt die F-Statistik zurück, während FTEST die Wahrscheinlichkeit zurückgibt.

Beispiele