Regressionsrechnung

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. (PC-Welt)