So liegst Du mit der Funktion Bestimmtheitsmass im Trend

Mit der Funktion BESTIMMTHEITSMASS begebe ich mich schon wieder ins Reich der statistischen Funktionen von Excel.

Zeigen will ich, wie mit dem Bestimmtheitsmaß beurteilt werden kann, wie gut eine Regressionsgerade eine Punktewolke abbildet. Dabei wird standardmäßig von einem linearen Trend ausgegangen.

Das Bestimmtheitsmaß im Diagramm

Für die anschauliche Darstellung dessen, was ich mitteilen will, verwende ich die Wertetabelle aus meinem Beitrag zu den Funktionen ACHSENABSCHNITT und STEIGUNG:

Ich markiere den Bereich A5:B9 und erstelle über Einfügen / Diagramme / Linie / Linie mit Datenpunkten ein Diagramm. Das Diagramm formatiere ich ein wenig um, so dass es etwa so aussieht:

Nun will ich eine Trendlinie einfügen. Dazu klicke ich die Linie mit Datenpunkten mit rechts an und wähle “Trendlinie einfügen”.

Aus der zur Verfügung stehenden Auswahl  an Trend- bzw. Regressionstypen entscheide ich mich für den Typ “Linear”, das erscheint aufgrund der gegebenen Streuung der Ist-Werte sinnvoll.

Der lienare Trend wird als Linie eingefügt. Über Trendlinie formatieren entscheide ich mich für einen gestrichelten Strichtyp und gebe der Linie die Farbe blau.

Dann rufe ich die Trendlinienoptionen auf und setze einen Haken bei “Bestimmtheitsma´im Diagramm darstellen”. Der Eintrag erscheint jetzt auf der Zeichungsfläche. Ich verschiebe das Objekt ein wenig in einen gut lesbaren Bereich.

Das Bestimmtheitsmaß wird in der Grafik mit 0,9709 ausgewiesen. Das ist ein sehr hoher Wert, der auf eine große Übereinstimmung der Trend- mit der Ist-Linie hin deutet. Der Wert 1,0 wäre der höchstmögliche und damit der Idealwert.

Die Funktion BESTIMMTHEITSMASS

Zur Kontrolle des Grafik-Wertes verwende ich jetzt die Excel-Funktion BESTIMMTHEITSMASS. Die Syntax dafür lautet:

=BESTIMMTHEITSMASS(Y_Werte;X_Werte)

Setze ich n un die Werte aus meiner Wertetabelle in die Formel ein, für die Y_Werte den Bereich B5:B9, für die X_Werte den Bereich A5:A9, erhalte ich die Gleichung

=BESTIMMTHEITSMASS(B5:B9;A5:A9)

Das Ergebnis ist 0,97087379 (gerundet 0,9709) und entspricht damit dem Wert in der Grafik.

Berechnung des Bestimmtheitsmaßes mit einer Formel

Das Ergebnis kann nun noch auf einem weiteren Wege geprüft werden. Es gilt, dass das Bestimmtheitsmaß das Quadrat des Pearsonschen Korrelationskoeffizienten r ist. Dieser errechnet sich nach der Formel:

   (Quelle: Excel-Hilfe)

Dazu baue ich unter Verwendung der Ausgamgstabelle eine Rechentabelle auf:

Anmerkung: Die Symbole für x-quer und y-quer habe ich in MS Word so “hergestellt”, in Excel 2007 ist das etwas schwierig mit dem Formeleditor: Einfügen / Formel / Neue Formel einfügen / Strukturen / Akzent / Unter- und Überstreichungen / Überstreichung wählen, “x” bzw. “y” eintragen.

Jetzt habe ich nahezu alle Werte zusammen, die ich zum Berechnen von r benötige.

Die Formel für r lautet nun:

=L11/WURZEL(O11)

Als Ergebnis erhalte ich 0,985329278, das ist der Pearsonsche Korrelationskoeffizient.

Das Bestimmtheitsmaß schließlich ist das Quadrat von r, ich erhalte

=0,97087379

Damit werden die Werte aus dem Diagramm und aus der Funktion BESTIMMTHEITSMASS bestätigt.

Fazit

Sollte sich nach allen drei Methoden ein kleiner Wert für das Bestimmtheitsmaß ergeben, ist der lineare Trend wenig oder gar nicht geeignet, die Punktewolke zu repräsentieren. Dann ist ein anderer Trendtyp zu suchen.