Korrelation und Regressionsgerade mit Excel ermitteln

Dieser Artikel beschreibt ein Beispiel, wie man mit Microsoft Excel die Korrelation zweier Datenreihen bestimmen kann. Weiterhin wird ausgeführt, wie man zum Korrelationskoeffizient kommt und sich die Regressionsgerade mit Funktion im Diagramm in Excel anzeigen lassen kann.

Das Beispiel des Benzinverbrauchs eines Pkw´s in Korrelation zur Geschwindigkeit selbst ist dabei für die Praxis wahrscheinlich eher weniger sinnvoll. Es dient lediglich dem Zweck anhand der Beispieldaten die Bearbeitung der Korrelation in Excel aufzuzeigen. Der Benzinverbrauch wird in der Praxis kaum linear zur Geschwindigkeit korrelieren.

Das komplette Korrelation mit Excel Beispiel downloaden.

Ausgangsinformationen und Visualisierung der Korrelation

Als Ausgangsinformationen dient im Excel immer eine Tabelle verschiedener Werte. Für die Korrelation zweier Werte benötigt man einen  Ausgangswert und einen davon (scheinbar) abhängigen Wert. Die Korrelation gibt an, wie stark der Wert von der Ausgangsgröße abhängt.

Im Excel erstellt man zuerst die Tabelle mit den Daten. Das Ergebnis einer Dateneingabe im Excel sollte bekannt sein. Das Ergebnis zeigt das linke Bild.  Um die Abhängigkeit bereits besser einschätzen zu können, wird mit diesen Daten als erstes ein Diagramm in Excel erzeugt. Dazu markiert man zuerst die Ausgangsdaten mit der Maus vollständig. Insofern die Überschriften mit markiert werden, erkennt Excel dies und wird entsprechende Beschriftungen im Diagramm einfügen. Im Microsoft Excel 2010 erfolgt dies über den Reiter "Einfügen". Für die Anzeige der Korrelation in einem Excel-Diagramm ist es ratsam aus den Punktdiagrammen das Diagramm "Punkte nur mit Datenpunkten" zu wählen.

Im rechten Bild ist ein Ausschnitt des Excel Reiters Einfügen zu erkennen. Beim Klicken mit der Maus auf die Schaltfläche Punktdiagramme öffnet sich die zu sehende Auswahl. Klickt man auf das "Punkte nur mit Datenpunkten" Diagramm, so erhält man die folgende Darstellung:

Wie man bereits erkennen kann, korreliert der Benzinverbrauch mit der Geschwindigkeit des Autos. Wie hoch die Korrelation genau ist und wie man die Regressionsgerade im Diagramm anzeigt, wird im Folgenden näher beschrieben.

Berechnung der Korrelation im Excel

Für die Berechnung einer, der einfachheithalber linearen Korrelation, benötigt man die Mittelwerte der beiden Datenreihen. Weiterhin kann man mit der Excel Funktion =steigung(Y-Werte;X-Werte) die Steigung der linearen Korrelation anzeigen lassen.

Durch den Anstieg der Regressionsgeraden und die Mittelwerte der beiden Datenreihen kann man mit den Ausgangswerten der Geschwindigkeit die Werte errechnen, die die Regressionsgerade an den zu berechnenden Stellen hat. Die Werte weichen von den tatsächlichen Werten ab, veranschaulichen jedoch gut, dass die Regressionsgerade nur näherungsweise die tatsächlichen Werte wiedergibt. Die theoretischen Werte kann man mit der rechts stehenden Funktion berechnen. Dabei bedeuten:

• $D$16 - Anstieg der Regressionsgeraden
• $D$14 - Mittelwert der Geschwindigkeiten
• $D$15 - Mittelwert der Verbräuche

Mit dem $ Zeichen wird im Microsoft Excel lediglich signalisiert, dass beim nach unten Ausfüllen diese Zellen nicht verändert werden.

Zuletzt soll noch der Korrelationskoeffizient mit Excel berechnet werden und die Regressionsgerade im Excel Diagramm dargestellt werden. Dazu nutzt man die Funktion =korrel(Matrix1;Matrix2) von Excel:

Mit diesem Korrelationskoeffizienten kann man die rechnerische Korrelation der beiden Datenreihen einschätzen. Das Ergebnis von ~0,97 läßt auf eine starke Korrelation schließen.

Darstellung Regressionsgerade im Diagramm

Zur Darstellung der Regressionsgerade im Excel Diagramm klickt man mit der rechten Maustaste auf einen Punkt der Datenreihe und wählt "Trendlinie einfügen" im Auswahlmenü.

Daraufhin öffnet sich das Eigenschaftenfenster der Trendlinie bzw. Regressionsgeraden. Unter anderem kann man in diesem Fenster Farbe, Dicke, Schatten etc. einstellen. Die wichtigsten Einstellungen sind jedoch der Regressionstyp sowie die Formel der Trendlinie. Wie bereits erwähnt begnügen wir uns mit der linearen Regressionsgerade. Exponentielle, logarithmische oder polynomische Regressionsfunktionen bieten eine bessere Annäherung, können jedoch auch vom Verlauf der bisher gesammelten Daten abweichen. Welcher Typ gewählt werden muß, hängt von der jeweiligen Situation und der visuellen Einschätzung der Korrelation ab.

Es wird nur noch ein Haken bei "Formel im Diagramm anzeigen" und "Bestimmtheitsmaß im Diagramm darstellen" gesetzt. Daraufhin erhält man folgende Ansicht des bereits oben dargestellten Excel Diagramms: