Excel-Formeln für eine Korrelationsmatrix -

Die Analyse-Funktion Korrelation

Eingestellt: 27.05.2009
Letzte Änderung: 27.05.2009


Keywords: Analyse-Funktion Korrelation Korrelationsmatrix Korrelationskoeffizient nach Bravais-Pearson
Betrifft:  KORREL
PEARSON BEREICH.VERSCHIEBEN


 

Einleitung

Mithilfe der Analyse-Funktion Korrelation können Sie alle paarweisen Korrelationen für die Variablen einer Datenmatrix bestimmen lassen. Als Ausgabe der Analyse-Funktion erhalten Sie eine sogenannte Korrelationsmatrix. Für die Bestimmung des Korrelationskoeffizienten nach Bravais-Pearson können die Excel-Funktionen KORREL und PEARSON angewendet werden.1

Nachfolgend wird eine kurze Anleitung zur Analyse-Funktion Korrelation gegeben und gezeigt wie eine Korrelationsmatrix mit Hilfe einer dynamischen Excel-Formel aufgestellt werden kann.

 

Anleitung zur Analyse-Funktion Korrelation

Die Durchführung der Analyse-Funktion Korrelation wird anhand eines Beispiels veranschaulicht, das Ihnen in nachstehender xls- und xlsx-Datei als Download zur Verfügung steht:

XLAMcorrel.xls

XLAMcorrel.xlsx

In diesem Beispiel liegen drei Variablen mit jeweils 10 Beobachtungen vor.


Abbildung 1 - Beispiel

Nach Installation des Add-Ins Analyse-Funktionen kann die Analyse-Funktion Korrelation in Excel 2003 in der Menüleiste über Extras / Analyse-Funktionen, in Excel 2007 in der Menüleiste über Daten im Ribbon Analyse mit Datenanalyse aufgerufen werden.


Abbildung 2 - Auswahl der Analyse-Funktion Korrelation

Markieren Sie die gewünschte Analyse-Funktion, und bestätigen Sie mit OK. Wählen Sie für dieses Beispiel in der Eingabe den Eingabebereich $B$1:$D$11 sowie die Option Geordnet nach Spalten und aktivieren Sie das Kontrollkästchen Beschriftungen in erster Zeile. Der Ausgabebereich soll in diesem Tabellenblatt ab Zelle $F$1 erfolgen.


Abbildung 3 - Eingabemaske der Analyse-Funktion Korrelation

Bestätigen Sie mit OK. Der Dialog wird daraufhin geschlossen und eine Korrelationsmatrix erstellt.


Abbildung 4 - Output der Analyse-Funktion Korrelation

 

Excel-Formeln für eine Korrelationsmatrix

Bei den nachfolgenden Excel-Formeln für eine Korrelationsmatrix wird die Eigenschaft des Korrelationskoeffizienten ausgenutzt, dass sich dieser nicht ändert, wenn die Variablen vertauscht werden. Ferner werden Excel-Formeln mit Hilfe der Excel-Funktion KORREL aufgestellt, welche durch die Excel-Funktion PEARSON ersetzt werden kann.2

Sofern die Datenbeschriftungen keinen fortlaufenden Variablenindex bilden, lässt sich eine Korrelationsmatrix wie in Abbildung 5 über folgende dynamische Excel-Formel bestimmen:


Abbildung 5 - Excel-Formel für Korrelationmatrix ohne Variablenindex

L2:=KORREL(BEREICH.VERSCHIEBEN($B$2:$B$11;;
ANZAHL2($K$1:$K1));B$2:B$11)

Excel-Formel nach rechts und nach unten kopieren.

Sollen wie in der Analyse-Funktion Korrelation die Werte oberhalb der Hauptdiagonalen in der Korrelationsmatrix unbesetzt bleiben, so lässt sich schreiben:

L2:=WENN(SPALTE(A:A)>ZEILE(A1);"";KORREL(BEREICH.VERSCHIEBEN(
$B$2:$B$11;;ANZAHL2($K$1:$K1));B$2:B$11))

Excel-Formel nach rechts und nach unten kopieren.

Liegt dagegen wie in Abbildung 6 ein fortlaufender Variablenindex für die Datenbeschriftungen vor, so kann die Excel-Formel verkürzt werden zu:


Abbildung 6 - Excel-Formel für Korrelationmatrix mit Variablenindex

Q2:=KORREL(BEREICH.VERSCHIEBEN($B$2:$B$11;;$P2-1);B$2:B$11)

Excel-Formel nach rechts und nach unten kopieren.

Sollen wiederum wie in der Analyse-Funktion Korrelation die Werte oberhalb der Hauptdiagonalen in der Korrelationsmatrix unbesetzt bleiben, so lässt sich schreiben:

Q2:=WENN(SPALTE(A:A)>ZEILE(A1);"";
KORREL(BEREICH.VERSCHIEBEN($B$2:$B$11;;$P2-1);B$2:B$11))

Excel-Formel nach rechts und nach unten kopieren.

Weitere dynamische Excel-Formeln für eine Korrelationsmatrix sind natürlich denkbar, insbesondere unter Verwendung von Namen.

 

1 Vgl. Excel-Hilfe oder Office-Online-Hilfe: Ausführen statistischer und technischer Analysen mit den Analyse-Funktionen.
2 Vgl. Microsoft (2007): Excel statistical functions: PEARSON, Artikel-ID: 828129, Version: 2.2.

 

Haben Sie Fragen oder Anregungen zu diesem Artikel?

Dann senden Sie einfach eine Nachricht an:

E-Mail:

 

Lesen Sie auch:
___________________________________________________________________

Excel-Formeln für die Analyse-Funktion Populationskenngrößen