Excel Rgp Funktion Beispiel Essay

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

    Steigung (m):
    Die Steigung einer Geraden (häufig als m bezeichnet) lässt sich aus zwei Punkten der Geraden, (x1,y1) und (x2,y2), gemäß der Beziehung (y2 - y1)/(x2 - x1) berechnen.

    y-Achsenabschnitt (b):
    Der y-Achsenabschnitt (häufig als b bezeichnet) ist der y-Wert des Punkts, in dem die Gerade die y-Achse schneidet.

    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(RGP(Y_Werte;X_Werte);1)

    y-Achsenabschnitt:
    =INDEX(RGP(Y_Werte;X_Werte);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 Residual-Quadratsumme im Vergleich zur Gesamtsumme der Abweichungsquadrate ist, desto größer ist der Wert des Bestimmtheitsmaßes (r2), das angibt, wie gut die aus der Regressionsanalyse resultierende Gleichung die zwischen den Variablen bestehende Beziehung beschreibt. Der Wert r2 ist gleich ssreg/sstotal.

  • In einigen Fällen enthalten eine oder mehrere der X-Spalten (wenn sich y-Werte und x-Werte in Spalten befinden) keine zusätzlichen berechenbaren Werte, wenn andere X-Spalten vorhanden sind. Das Entfernen von X-Spalten führt möglicherweise zur Anzeige von berechneten y-Werten, die gleichermaßen genau sind. In diesem Fall sollten diese nicht erforderlichen X-Spalten nicht im Regressionsmodell angegebenen werden. Diese Phänomen wird als “Kollinearität" bezeichnet, da jede nicht erforderliche X-Spalte als eine Summe von Vielfachen der erforderlichen X-Spalten formuliert werden kann. Die Funktion RGP überprüft die Spalten auf Kollinearität und entfernt alle nicht erforderlichen X-Spalten aus dem Regressionsmodell, wenn solche ermittelt werden. Entfernte X-Spalten können in der RGP-Ausgabe anhand der Koeffizienten 0 zusätzlich zu den Werten 0 se erkannt werden. Das Entfernen von einer oder mehreren Spalten hat Auswirkungen auf den Freiheitsgrad (df), da dieser von der Anzahl der zur Berechnung verwendeten X-Spalten abhängig ist. Weitere Einzelheiten zur Berechnung von df finden Sie in Beispiel 4. Wird df geändert, da nicht erforderliche X-Spalten gelöscht wurden, wirkt sich dies auch auf die Werte von sey und F aus. Kollinearität sollte in der Praxis nur selten vorkommen. Sie tritt jedoch häufiger auf, wenn einige X-Spalten nur die Werte 0 und 1 enthalten, mit denen angegeben wird, ob ein Objekt in einem Experiment ein Mitglied einer speziellen Gruppe ist oder nicht. Wenn Konstante mit WAHR belegt oder nicht angegeben ist, fügt die RGP-Funktion automatisch eine zusätzliche X-Spalte für alle Werte 1 ein, um den Schnittpunkt zu modellieren. Wenn in einer Spalte durch den Wert 1 angegeben wird, dass das Objekt männlich ist, und durch den Wert 0, dass es nicht männlich ist, und in einer weiteren Spalte durch den Wert 1 angegeben wird, dass das Objekt weiblich ist, und durch den Wert 0, dass es nicht weiblich ist, ist die letzte Spalte nicht erforderlich. Die darin enthaltenen Einträge können berechnet werden, indem der Eintrag in der Spalte, in der angegeben wird, dass ein Objekt männlich ist, von dem Eintrag in der zusätzlichen von der Funktion RGP hinzugefügten Spalte für die Werte 1 subtrahiert wird.

  • 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.

  • Formeln, die als Ergebnis eine Matrix zurückgeben, müssen als Matrixformeln eingegeben werden.

    Hinweis:  In Excel Online können keine Matrixformeln erstellt werden.

  • 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 RKB 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.

  • Die Funktionen RGP und FTEST geben unterschiedliche F-Testwerte zurück: RGP gibt die F-Statistik zurück, FTEST die Wahrscheinlichkeit.

  • Die Regressionsrechnung ist ein statistisches Verfahren, das die Abhängigkeit einer Datenreihe (Y-Werte) von einer zweiten Datenreihe (X-Werte) analysiert und einen funktionalen Zusammenhang zwischen beiden Größen herstellt. Aus den daraus erlangten Kenntnissen sollen zukünftige Prognosen bzw. Trends abgeleitet werden können. Excel stellt eine Gruppe von Funktionen zur Verfügung, die sich dieser Thematik annehmen und sich sehr gut ergänzen und teilweise auch substituieren lassen.

    Die meisten Funktionen gehen standardmäßig von einem linearen Zusammenhang zwischen abhängiger und unabhängiger Größe aus. Es werden folglich lineare Trends unterstellt. Abb. 3.7 zeigt in Spalte A die unabhängige Größe und in Spalte B die abhängigen Messwerte, die in einem Punkt (XY)-Diagramm dargestellt werden. Die Gerade stellt den linearen Trend dieser Messwerte dar.

    Die Funktion STEIGUNG(Y_Werte;X_Werte) gibt die Steigung der Trendgeraden zurück. ACHSENABSCHNITT(Y_Werte;X_Werte) liefert den Y-Wert, an dem die Gerade die Y-Achse schneidet. Die Funktion RGP(Y_Werte; X_Werte) liefert ein Array aus zwei Werten in obiger Form, die ebenfalls die Steigung und den Y-Achsenschnittpunkt bestimmen. Darüber hinaus besitzt sie noch zwei weitere optionale Parameter, über die weitere statistische Kennzahlen der Trendfunktion abfragbar sind.

    Die Funktion TREND berechnet aus vorgegebenen X-Werten einzelne Y-Werte der Trendfunktion, was natürlich auch über Steigung und Y-Achsenschnittpunkt ableitbar wäre: =TREND(x)=STEIGUNG*x+ACHSENABSCHNITT Die Funktion SCHÄTZER(x; Y_Werte; X_Werte) macht genau das Gleiche wie TREND, nur dass die Parameter in einer etwas anderen Reihenfolge verlangt werden. TREND hat aber noch ein paar spezielle Tricks auf Lager, die Sie mit SCHÄTZER nicht machen können, bleiben Sie deshalb lieber gleich bei TREND. Die Funktionen BESTIMMTHEITSMASS(Y_Werte; X_Werte), KORREL(Matrix1; Matrix2) und PEARSON(Matrix1; Matrix2) drücken aus, wie stark die Y-Werte überhaupt von den X-Werten abhängig sind. Die beiden Letztgenannten liefern stets ein identisches Ergebnis. Werden beide quadriert, ergibt sich das BESTIMMTHEITSMASS. Bei rein zufälligen Y-Werten, die absolut nichts mit den X-Werten zu tun haben, liefern alle drei Funktionen einen Wert nahe 0. Bei vollständiger Abhängigkeit liefern sie den Wert +1. KORREL und PEARSON können auch eine negative Korrelation von bis zu -1 darstellen. Da die Funktion BESTIMMTHEITSMASS deren Quadrat ist, liegt ihr Ergebnis im Bereich von 0 und 1.

    Es besteht übrigens auch ein Zusammenhang zwischen diesen Funktionen und der im vorherigen Abschnitt erwähnten Kovarianz und der Standardabweichung: =KORREL(WerteA;WerteB)*STABWN(WerteA)*STABWN(WerteB) =KOVAR(WerteA;WerteB) STFEHLERYX(Y_Werte;X_Werte) ist ein weiteres Maß zur Bestimmung der Abhängigkeit zwischen Y-Werten und X-Werten. Je stärker die Abhängigkeit der Y-Werte ist, desto kleiner ist das Ergebnis dieser Funktion. Im Extremfall einer vollkommenen Abhängigkeit liefert sie #DIV/0!. =STFEHLERYX({5.10.15};{1.2.3})=#DIV/0! Der Zusammenhang zwischen Y-Werten und X-Werten muss nicht immer linear sein. Statt einer linearen Funktion y=a+b*x ist auch eine polynomische Funktion y=a + b*x + c*x^2+d*x^3+… darstellbar. Werden die x-Werte in den Funktionen RGP und Trend mit den Potenzen der Polynomfunktion ^{1.2.3…} verquickt, liefern sie die richtigen Koeffizienten bzw. y-Werte des Funktionsgraphen, wie Abb. 3.8. Die Messwerte werden hier durch eine polynomische Trendfunktion 3. Ordnung angenähert. Neu dazu gesellt sich die Funktion POTENZREIHE, mit der man zwar nicht unmittelbar Regressionsrechnungen durchführt, die aber zusammen mit RGP und TREND ein sich schön ergänzendes Dreigestirn bildet. TREND ermittelt ja Y-Werte aus vorhandenen X/Y-Werten. POTENZREIHE kann dieselben Y-Werte aus den Koeffizienten herleiten, die RGP als Ergebnis liefert. Dass wir uns hier schon sehr nahe am Bereich der Finanzmathematik befinden, demonstriert der Zusammenhang POTENZREIHE(1/(1+5%);1;1;Zahlungsreihe) =NBW(5%;Zahlungsreihe) Mit beiden Funktionen kann gleichermaßen der Barwert einer Zahlungsreihe gebildet werden. Dies soweit nur als kleiner Appetithappen auf Ausführungen im Vertiefungsteil dieses Buchs.

    Noch mal zurück zur Trendrechnung. Handelt es sich weder um einen linearen noch um einen polynomischen Trend, sondern um einen exponentiellen Zusammenhang zwischen unabhängiger und abhängiger Größe, werden die Funktionen RGP und TREND durch die Funktionen RKP und VARIATION ersetzt, die ansonsten gleichermaßen zu handhaben sind. Sie beschreiben Wachstumsfunktionen. RGP und RKP liefern jeweils Koeffizienten der Trendfunktion, TREND und VARIATION einzelne Y-Werte oder eine ganze Reihe von Y-Werten. Die Formeln =VARIATION({ 1 . 1,1 . 1,21 . 1,331 };{ 0 . 1 . 2 . 3 };4) = 1,4641 =RKP({ 1 . 1,1 . 1,21 . 1,331 };{ 0 . 1 . 2 . 3 }) = {1,1.1} ... sind so zu interpretieren: Ein Taler wächst in jedem Zeitabschnitt um den Faktor 1,1 (er wird mit 10% verzinst). Nach vier Perioden ist er auf 1,4641 Taler angewachsen. (ph)

    One thought on “Excel Rgp Funktion Beispiel Essay

    Leave a Reply

    Your email address will not be published. Required fields are marked *