Excel-Bug: Falsches Bestimmtheitsmaß im Punkt(XY)-Diagramm

Eingestellt: 21.08.2010
Letzte Änderung: 21.08.2010


 

Kurzbeschreibung

Wenn Sie in einem Punkt(XY)-Diagramm eine Trendlinie eingefügt haben, dann können Sie im Dialog 'Trendlinie formatieren' neben der Formel (bzw. Gleichung)  auch das Bestimmtheitsmaß im Diagramm darstellen. Aktivieren Sie im gleichen Dialog zusätzlich noch die Option 'Schnittpunkt', so berechnet Excel das Bestimmtheitsmaß im Punkt(XY)-Diagramm falsch.[1]

Unter Verwendung der Excel-Option 'Schnittpunkt' wird bei der Regression die Bedingung aufgenommen, dass für die Regressionskonstante ein fest vorgegebener Wert gelten soll. Dies schließt den in der Praxis oft verwendeten Fall der homogenen Regression mit ein, bei der  im linearen Modell die Regressionskonstante den Wert null annimmt.

 

Einleitung

Die folgenden Betrachtungen beschränken sich der Einfachheit halber auf das lineare Regressionsmodell, wobei zwischen inhomogener und homogener Regression unterschieden wird. Dabei wird das übliche lineare Modell, welches an keine zusätzliche Bedingung geknüpft ist, als inhomogene Regression bezeichnet. Demgegenüber soll bei einer homogenen Regression die Bedingung erfüllt sein, dass der Wert der Regressionskonstante null ist. Nach einer tabellarischen Übersicht einiger wichtiger Formeln zur Berechnung des Bestimmtheitsmaßes und einem einführenden Beispiel wird eine Fehlersuche unternommen.

Bei dieser Fehlersuche wird zunächst die Excel-Funktion RGP vorgestellt und auf den Bug in der Excel-Funktion RGP in den Versionen Excel 2002 und früher aufmerksam gemacht.[2] Anschließend wird gezeigt, dass sich eine intuitive Erläuterung für den Bug in der Excel-Funktion RGP auf den Excel-Bug im Punkt(XY)-Diagramm übertragen lässt. Eine Abhilfe und ein Resümee schließen diesen Artikel. Als Testumgebung dienen die Versionen Excel 2000, Excel 2002, Excel 2003, Excel 2007 und Excel 2010.

 

Formeln

Das Bestimmtheitsmaß wird überlicherweise über die Variation der Residuen bzw. die der Regresswerte definiert. Dabei ist es zum Verständnis der beiden Excel-Bugs lediglich wichtig zu erkennen, dass das Ergebnis einer Variation nach Formel #1 , #2 und #3 in Abbildung 1 nie negativ werden kann. Über die Gleichung #4 folgt in Verbindung mit den Formeln #5 und #6, dass das Bestimmtheitsmaß somit nur Werte im abgeschlossenen Intervall zwischen null und eins annehmen kann.

Abbildung 1: Formeln zur Berechnung des Bestimmtheitsmaßes.

 

Beispiel

Die populärsten Fälle des Excel-Bugs im Punkt(XY)-Diagramm sind jene, bei dem es für den Anwender ersichtlich ist, dass das Bestimmtheitsmaß falsch berechnet wird. Dies ist beispielsweise dann offensichtlich, wenn das Bestimmtheitsmaß einen negativen Wert annimmt. Nachstehendes Beispiel zeigt einen solchen, wenngleich extremen Fall für eine homogene Regression. Gegeben sind fünf Beobachtungen, welche Sie aus Abbildung 2 entnehmen können.

Abbildung 2: Beobachtungen für das Beispiel.

Erstellen Sie für diese ein Punkt(XY)-Diagramm und führen Sie nach einem Rechtsklick auf die Datenreihe den Befehl 'Trendlinie hinzufügen' aus. Wählen Sie im Dialog 'Trendlinie formatieren' (bzw. 'Trendlinie hinzufügen') anschließend den Regressionstyp 'Linear' und aktivieren Sie die Optionen 'Schnittpunkt=0', 'Formel im Diagramm anzeigen' (bzw. 'Gleichung im Diagramm darstellen') und 'Bestimmtheitsmaß im Diagramm darstellen' und drücken Sie 'Schließen' (bzw. 'OK') (vgl. Abbildung 3).

Abbildung 3: Dialog 'Trendlinie formatieren' in Excel 2010.

Im Punkt(XY)-Diagramm werden nun die Regressionsgerade, die Regressionsgleichung und ein negatives Bestimmtheitsmaß ausgegeben (vgl. Abbildung 4).

Abbildung 4: Negatives Bestimmtheitsmaß in Excel 2010.

 

Fehlersuche

Um eine mögliche Fehlerursache für diesen Excel-Bug auszumachen, wird die Excel-Funktion RGP herangezogen und soll im Folgenden näher betrachtet werden. Die Syntax dieser Funktion lautet:

RGP(Y_Werte;X_Werte;Konstante;Stats)

Werden bei einer linearen Regression das Argument 'Konstante' mit WAHR (bzw. FALSCH) und das Argument 'Stats' mit WAHR belegt, liefert die Excel-Funktion RGP eine 5x2-Matrix mit den Maßen für eine inhomogene (bzw. homogene) Regression.

Abbildung 5: Matrizen der Excel-Funktion RGP für eine inhomogene und homogene Regression.

An dieser Stelle sei lediglich erläutert, dass jeweils in der ersten Zeile die Werte der Regressionsparameter m1 und b (bzw. 0), in der dritten Zeile der ersten Spalte das Bestimmtheitsmaß (R2), in der vierten Zeile der ersten Spalte die F-Statistik (F) sowie in der fünften Zeile die Variation der Regresswerte (SSReg) und die Variation der Residuen (SSResid) stehen. Der Index [i] (bzw. [h]) wird verwendet, wenn es sich um eine inhomogene (bzw. homogene) Regression handelt.

Werden diese Maße mit Hilfe der Excel-Funktion RGP in Excel 2002 für eine inhomogene und eine homogene Regression berechnet, so ergeben sich für das betrachtete Beispiel folgende Ergebnisse:


Formeln in der Tabelle:
A11:B15: {=RGP(y;x;WAHR;WAHR)}
D11:E15: { =RGP(y;x;FALSCH;WAHR)}
wobei für den Bereich B2:B6 (bzw. C2:C6) der Name x (bzw. y) definiert wurde.

Abbildung 6: Ergebnisse der Excel-Funktion RGP in Excel 2002.

Während die Ergebnisse der Excel-Funktion RGP für die inhomogene Regression korrekt sind, ergeben sich bei der homogenen Regression in Excel 2002 (und vermutlich auch in früheren Versionen) falsche Ergebnisse für das Bestimmtheitsmaß als auch für die F-Statistik und die Variation der Regresswerte (vgl. Abbildung 6). Geht man nun der Frage nach wie es möglich ist, dass das Bestimmtheitsmaß einen negativen Wert annimmt, fällt Formel #5 ins Auge:

Unter der Annahme, dass das Bestimmtheitsmaß R2[h] auch in Excel laut Formel #5 berechnet wird, kann dieses Maß fälschlicherweise lediglich dann einen negativen Wert annehmen, falls der Wert für die Variation der Residuen SSResid[h] größer ist als der Wert der Variation von Y SSTotal[h]. Wird weiter angenommen, dass die Variation der Residuen korrekt berechnet wird, müsste folglich die Berechnung der Variation von Y SSTotal[h] fehlerhaft sein. Formt man Formel #5 nach der Variation von Y SSTotal[h] um und setzt die Werte für das Beispiel ein, ergibt sich:

Diesen Wert erhält man ebenfalls, wenn die Variation von Y SSTotal[h] über die Beziehung #4 berechnet wird, so dass in Excel vermutlich die Berechnung des Bestimmtheitsmaßes auf das Ergebnis der Variation von Y SSTotal[h] (bzw. von SSResid[h] und SSReg[h] ) aufbaut oder der gleiche Fehler ein weiteres Mal begangen wird.

Durch scharfes Hinsehen erkennen Sie in Abbildung 6, dass aber gerade der Wert der SSTotal[h] der Summe aus der Variation der Residuen SSResid[i] und der der Regressionswerte SSReg[i] einer inhomogenen Regression entspricht. Eine mögliche Ursache für den Bug in der Excel-Funktion RGP ist also, dass bei der Berechnung des Bestimmtheitsmaßes für eine homogene Regression die Werte der Variation der Residuen und der Variation der Regresswerte (bzw. die  Werte der Variation von Y) einer inhomogenen Regression verwendet werden. Bekräftigt wird dies, dass sich dadurch auch die fehlerhaften Werte für die Variation der Regresswerte und der F-Statistik erklären lassen würden wie Abbildung 7 zeigt. Darüber hinaus kann diese Vermutung sowohl auf andere Beispiele als auch auf weitere, in Excel verfügbare Regressionstypen aufrecht erhalten werden.[3]


Formeln in der Tabelle:
A11:B15: {=RGP(y;x;WAHR;WAHR)}
D11:E15: { =RGP(y;x;FALSCH;WAHR)}
D17: =1-E15/(A15+B15)
bzw.
D17: =1-E15/(D15+E15)
D18: =E14*(1-E15/(A15+B15))/(1-(1-E15/(A15+B15)))
bzw.
D18: =E14*D13/(1-D13)
D19: =(A15+B15)-E15

Abbildung 7: Mögliche Fehlerursache des Bugs in der Excel-Funktion RGP in Excel 2002.

Nun ist es lediglich ein kleiner Schritt, um zum Excel-Bug im Punkt(XY)-Diagramm zu gelangen. Vergleichen Sie bitte den Wert in Zelle D13 in Abbildung 6  oder 7 mit dem Wert des Bestimmtheitsmaßes im Diagramm in Abbildung 4.  Fällt Ihnen etwas auf? Das Ergebnis des Bestimmtheitsmaßes bei der Excel-Funktion RGP in Excel 2002 stimmt mit dem Ergebnis im Punkt(XY)-Diagramm überein.[4] Dies ist auch für andere Beispiele und Modelle der Fall. Es kann daher vermutet werden, dass die Ursache für den Bug im Punkt(XY)-Diagramm die gleiche ist wie bei der Excel-Funktion RGP. Microsoft selbst bestätigt diese Vermutung im Knowledge-Base-Artikel You will receive an incorrect R-squared value in the chart tool in Excel 2003.

 

Workaround

Für die Versionen ab Excel 2003 kann der Excel-Bug im Punkt(XY)-Diagramm relativ leicht umgangen werden. Mit Hilfe der Excel-Funktion RGP kann zunächst der Wert für das Bestimmtheitsmaß einer inhomogenen Regression ausgegeben werden. Um nicht die gesamte Matrix der Excel-Funktion RGP berechnen zu müssen, kann folgende Excel-Formel verwendet werden:

=INDEX(RGP(y;x;FALSCH;WAHR);3;1)

Anschließend können Sie bspw. den Wert des Bestimmtheitsmaß für eine homogene Regression in das Diagramm über eine Dummy-Datenreihe oder über ein Textfeld aufnehmen.

Für die Versionen vor Excel 2003 gestaltet sich eine Abhilfe etwas schwieriger, da der Bug in der Excel-Funktion RGP für diese nicht behoben wurde und somit auf den Wert des Bestimmtheitsmaß der Excel-Funktion RGP für eine homogene Regression nicht zurückgegriffen werden kann. Es kann jedoch das Ergebnis der Variation der Residuen SSResid[h] der Excel-Funktion RGP verwendet werden. Weiter entspricht die Variation von Y SSTotal[h] im Falle der homogenen Regression gerade der Summe der quadrierten Beobachtungen von Y, so dass laut Formel #5 geschrieben werden kann:

=1-INDEX(RGP(y;x;FALSCH;WAHR);5;2)/QUADRATESUMME(y)

Diese Excel-Formel kann natürlich auch in den neueren Excel-Versionen verwendet werden.[5]

Die korrekten Werte für das Bestimmtheitsmaß, die F-Statistik und der Variation der Regresswerte für das betrachtete Beispiel können Sie der nachstehenden Abbildung entnehmen.


Formeln in der Tabelle:
A11:B15: {=RGP(y;x;WAHR;WAHR)}
D11:E15: { =RGP(y;x;FALSCH;WAHR)}
D18: =INDEX(RGP(y;x;FALSCH;WAHR);3;1)
D19: =1-INDEX(RGP(y;x;FALSCH;WAHR);5;2)/QUADRATESUMME(y)

Abbildung 8: Ergebnisse der Excel-Funktion RGP in Excel 2010.

Werden andere Modelle betrachtet und/oder soll die Regressionskonstante einen anderen Wert als null annehmen, so sind die in diesen Workaround aufgezeigten Excel-Formeln entsprechend des zugrunde liegenden Modells anzupassen.[6]

 

Resümee

Die fehlerhafte Berechnung des Bestimmtheitsmaßes im Punkt(XY)-Diagramm bei Aktivierung der Option 'Schnittpunkt' ist  unabhängig vom gewähltem Regressionsmodell und vom gewählten Wert bei der Option 'Schnittpunkt'. Sie ist seit (mindestens) Excel 2000 vorhanden und findet sich auch noch in Excel 2010 wieder. Praxisrelevanz hat dieser Bug insbesondere bei einer homogenen Regression, wenn im Punkt(XY)-Diagramm neben der Gleichung das Bestimmtheitsmaß als Kriterium für die Güte der berechneten Regressionsgerade herangezogen wird. Der ausgegebene Wert ist lediglich dann korrekt, wenn das Bestimmtheitsmaß eins bzw. der Wert der Variation der Residuen null ist. Erkennbar ist dieser Bug vornehmlich in den Fällen, bei denen das Bestimmtheitsmaß einen negativen Wert annimmt. Problematisch sind jedoch gerade die Fälle, bei denen das Bestimmtheitsmaß einen (falschen) nichtnegativen Wert annimmt, da dieser sich nur schwer als falsch identifizieren lässt und ggf. als Grundlage weiterer Berechnungen und Entscheidungen dient.

 

[1] Grundsätzlich sollte eine Trendlinie ausschließlich in einem Punkt(XY)-Diagramm eingesetzt werden. Hintergrund ist, dass andere Diagrammtypen wie bspw. ein Liniendiagramm nicht zwei Größenachsen, sondern eine Größen- und eine Rubrikenachse besitzen.

[2] Auf die Analyse-Funktion 'Regression' wird in diesem Artrikel bewusst nicht eingegangen.

[3] Getestet wurden eine lineare und 'exponentielle' Regression sowie eine polynomische Regression 2. und 3. Grades mit fest vorgegebener Regressionskonstante.

[4] Genauer gesagt können sich die Ergebnisse des Bestimmtheitsmaßes in der Excel-Funktion RGP und im Punkt(XY)-Diagramm in der letzten ausgewiesenen Nachkommastelle unterscheiden.

[5] Weitere Informationen zur Excel-Funktion RGP erhalten Sie auch im Microsoft Knowledge-Base-Artikel Beschreibung der Funktion RGP in Excel 2003 und späteren Versionen von Excel.

[6] An dieser Stelle sei ausdrücklich darauf hingewiesen, dass die Excel-Funktion RGP bei bestimmten Datenkonstellationen und/oder Modellen falsche Ergebnisse liefert.

 

Haben Sie Fragen oder Anregungen zu diesem Artikel?

Dann senden Sie einfach eine Nachricht an:

E-Mail:

 

Lesen Sie auch:
___________________________________________________________________

Aktualisierungsfehler bei den Funktionen SPALTE und ZEILE – Bug in Excel?