Korrelationsmatrix als benutzerdefinierte Funktion (UDF)

Diese kurze Fallstudie soll folgende Punkte demonstrieren:

  •  Es handelt sich um eine benutzerdefinierte Funktion, die nicht nur einen Einzelwert liefert, sondern ein Array bzw., auf dem Tabellenblatt ausgegeben, einen Bereich.
  • Zur Ermittlung des Ergebnisses wird eine eingebaute Excel-Funktion herangezogen (WorksheetFunction)
  • Die Funktion hat einen Parameter vom Typ Range. Hierbei handelt es sich um eine Klasse aus dem Excel-Objektmodell.

Eine Korrelationsmatrix ist eine Tabelle, welche Korrelationskoeffizienten enthält. Der Korrelations­koeffizient r ist eine Masszahl im Bereich [-1, 1], die ausdrückt, wie stark der Zusammenhang zwi­schen zwei statistischen Merkmalen ist. Die linke Tabelle im folgenden Bild zeigt die Werte der Merk­male x1, x2 und x3 für 15 Individuen. Der Korrelationskoeffizient der beiden Merkmale x1 und x2 hat gerundet den Wert 0,87. Man ersieht daraus, dass die beiden Merkmale stark positiv korreliert sind. Einem hohen Wert bei x1 entspricht ein hoher Wert bei x2. Die Merkmale x1 und x2 sind dagegen mit r = -0,47 negativ korreliert. Einem hohen Wert bei x1 entspricht tendenziell ein niedriger Wert bei x2, aber dieser Zusammenhang ist nur schwach ausgeprägt.

 

Die Tabelle auf der rechten Seite, die Korrelationsmatrix, zeigt die Korrelationen zwischen sämt­lichen Merkmalen. Da der Korrelationskoeffizient zwischen x1 und x2 dem Korrelationskoeffizienten zwischen x2 und x1 gleich ist, ist die Korrelationsmatrix symmetrisch bezüglich der Hauptdiagonalen. Der Inhalt dieser Tabelle, ohne Zeilen- und Spaltenbeschriftungen, ist übrigens schon das Ergebnis unserer benutzerdefinierten Funktion.

Wir betrachten nun den Code dieser Funktion. Der Inputparameter rng ist vom Typ Range. Es handelt sich um den inneren Bereich der Wertetabelle (linke Tabelle im Bild oben), also ohne Beschriftungen. Das Array r vom Typ Double nimmt die Korrelationen auf. Hat die Wertetabelle n Merkmale, so sind insgesamt n * n Korrelationen in der Korrelationsmatrix enthalten. Diese werden in einer Doppelschleife ermittelt und in das Array r eingestellt. Dabei erfolgt die Ermittlung der Einzel­korre­la­tionen mit Hilfe der WorksheetFunction Correl. Diese nimmt als Parameter jeweils zwei Spalten aus der Wertetabelle entgegen.

 

'ermittelt die Korrelationsmatrix; ohne Beschriftungen

'Input: Bereich ohne Überschrift

'------------------------------------------------------

Public Function KorrMatr(ByVal rng As Excel.Range) As Double()

   Dim i As Integer, j As Integer

   Dim n As Integer               'Anzahl der Attribute

   Dim c1 As Range, c2 As Range

   n = rng.Columns.Count

   Dim r() As Double

   ReDim r(1 To n, 1 To n)

   For i = 1 To n

       For j = 1 To n

           Set c1 = rng.Columns(i)

           Set c2 = rng.Columns(j)

           r(i, j) = Application.WorksheetFunction.Correl(c1, c2)

       Next j

   Next i

   KorrMatr = r

End Function

 

Beachten Sie, dass die beim Anwenden der Funktion aus dem Tabellenblatt heraus vor dem Aufruf einen Bereich von n * n Zellen markieren müssen (s. Bild unten). Haben Sie dann den Parameter Rng eingegeben, so müssen Sie beim Drücken der Eingabetaste die Tasten <Strg> und <Shift> gleichzeitig drücken.