Excel-Formeln für eine Kovarianzmatrix -

Die Analyse-Funktion Kovarianz

Eingestellt: 28.05.2009
Letzte Änderung: 28.05.2009


Keywords: Analyse-Funktion Kovarianz Kovarianzmatrix
Betrifft:
  KOVAR
VARIANZEN BEREICH.VERSCHIEBEN


 

Einleitung

Mithilfe der Analyse-Funktion Kovarianz können Sie alle paarweisen Kovarianzen für die Variablen einer Datenmatrix bestimmen lassen. Als Ausgabe der Analyse-Funktion erhalten Sie eine sogenannte Kovarianzmatrix (auch: Varianz-Kovarianz-Matrix). Zur Berechnung der Kovarianzen benutzt das Tool die Tabellenfunktion KOVAR. Die Elemente in der Hauptdiagonalen der Kovarianzmatrix entsprechen der jeweiligen Varianz der Variablen. Für diese Elemente verwendet das Tool die Excel-Funktion VARIANZEN.1

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

 

Anleitung zur Analyse-Funktion Kovarianz

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

XLAMcovar.xls

XLAMcovar.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 Kovarianz 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 Kovarianzmatrix erstellt.


Abbildung 4 - Output der Analyse-Funktion Korrelation

 

Excel-Formeln für eine Kovarianzmatrix

Bei den folgenden Excel-Formeln für eine Kovarianzmatrix wird die Eigenschaft der Kovarianz ausgenutzt, dass die Kovarianz einer Variablen mit sich selbst gleich der Varianz der Variablen entspricht. Ferner werden Excel-Formeln mit Hilfe der Excel-Funktion KOVAR aufgestellt.

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


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

L2:=KOVAR(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 Kovarianz die Werte oberhalb der Hauptdiagonalen in der Kovarianzmatrix unbesetzt bleiben, so lässt sich schreiben:


Abbildung 6 - Excel-Formel für Kovarianzmatrix ohne Variablenindex

L2:=WENN(SPALTE(A:A)>ZEILE(A1);"";KOVAR(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 7 ein fortlaufender Variablenindex für die Datenbeschriftungen vor, so kann die Excel-Formel verkürzt werden zu:


Abbildung 7 - Excel-Formel für Kovarianzmatrix mit Variablenindex

Q2:=KOVAR(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 Kovarianz die Werte oberhalb der Hauptdiagonalen in der Kovarianzmatrix unbesetzt bleiben, so lässt sich schreiben:


Abbildung 8 - Excel-Formel für Kovarianzmatrix mit Variablenindex

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

Excel-Formel nach rechts und nach unten kopieren.

Dass die Analyse-Funktion Kovarianz die Excel-Funktion VARIANZEN für die Werte in der Hauptdiagonalen verwendet, können Sie sehen, wenn Sie in der Korrelationsmatrix der Analyse-Funktion die Zelle G2, H3 oder I4 aktivieren. In der Befehlszeile erscheint die Excel-Funktion VARIANZEN, während in den anderen Zellen der Kovarianzmatrix der Analyse-Funktion keine Excel-Funktion hinterlegt ist.

Auf Grund der unterschiedlichen Berechnung der Excel-Funktionen VARIANZEN und KOVAR sowie der Gleitkommarechnung können sich die Ergebnisse in der Hauptdiagonale leicht voneinander unterscheiden.2 So ergibt sich in Zelle I4 über die Excel-Funktion VARIANZEN ein Wert von 763,064239999999, während über die Excel-Funktion KOVAR in Zelle N4 und S4 ein Wert von 763,06424 ausgegeben wird.

Weitere dynamische Excel-Formeln für eine Kovarianzmatrix 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: VARP, Article ID: 826393, Revision: 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