Sie befinden sich hier: Startseite > Excel > Excel lernen - kostenlose Tutorials und Vorlagen > Korrelation 

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

Ausgangsdaten der KorrelationAls 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.

Diagrammauswahl im ExcelIm 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:

Darstellung einer Korrelation im Excel Diagramm

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

Excel Funktion Anstieg RegressionsgeradeFü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.

 

Berechnung der Y-Werte der RegressionsgeradenDurch 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:

Berechnung Korrelationskoeffizient mit MS 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ü.

Einfügen der Regressionsgerade im Exceldiagramm

Eigenschaftenfenster Regressionsgerade im ExcelDaraufhin ö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:

Darstellung von Korrelation und Regressionsgerade im Excel Diagramm

Das Tutorial zur Anwendung von Microsoft Office hat Ihnen weitergeholfen?

Ich freue mich über Ihre Verlinkung. Kopieren Sie dazu einfach den folgenden Code an eine geeignete Stelle in Ihrer Internetseite. Vielen Dank!


Letzte Aktualisierung: 14.02.2014
Jetzt den Spiegel testen und Prämie sichern!
officecoach24.de - Hilfe für Word, Excel, Powerpoint und Visio

Suche

Zuletzt erstellt & aktualisiert

Weiter empfehlen

Sie finden diese Seite empfehlenswert? Sagen Sie es bitte weiter und unterstützen Sie somit die Erweiterung des Inhalts:

Werbung


Please enable / Bitte aktiviere JavaScript!
Veuillez activer / Por favor activa el Javascript![ ? ]