Korrelation von gefilterten Werten berechnen
15.01.2019 11:11:17
gefilterten
In meinem Excel-Tool filtere ich eine Tabelle nach dem Datum und berechne anschließend die Korrelation der übrig gebliebenen Werte. Dafür habe ich eine UDF gesucht, die ausgefilterte ("hidden") Zellen ignoriert und nur die nicht ausgefilterten Zellen in die Berechnung miteinbezieht.
Das hat gut funktioniert, aber kürzlich bin ich auf ein Problem gestoßen: Der erste Bereich X von Werten und der zweite Bereich Y dürfen nicht in verschiedenen Zeilen starten, sonst kommt kein sinnvolles Ergebnis raus. Zur Veranschaulichung:
X-Werte in A20:A40
Y-Werte in B21:B41
berechnet einen falschen Korrelationskoeffizienten, das liegt an der UDF, in der die for-Schleife bei 20 anfängt und bis 40 geht.
Die UDF mit Link zur Quelle:
https://www.mrexcel.com/forum/excel-questions/15169-correl-function-filtered-data.html
Function Correl8(R1 As Range, R2 As Range) As Double
' correlation coefficient function that ignores hidden values
Dim Sig1 As Double
Dim Sig2 As Double
Dim S1 As Double
Dim S2 As Double
Dim Mu1 As Double
Dim Mu2 As Double
Dim N As Integer
Dim I As Integer
Sig1 = 0: Sig2 = 0: Mu1 = 0: Mu2 = 0: S1 = 0: S2 = 0
N = 0
For I = 1 To R1.Cells.Count
If Not R1.Rows(I).Hidden And Not R2.Rows(I).Hidden Then
N = N + 1
Mu1 = Mu1 + R1.Cells(I)
Mu2 = Mu2 + R2.Cells(I)
S1 = S1 + R1(I) ^ 2
S2 = S2 + R2(I) ^ 2
End If
Next I
Sig1 = Sqr((N * S1 - Mu1 ^ 2)) / N
Sig2 = Sqr((N * S2 - Mu2 ^ 2)) / N
Mu1 = Mu1 / N
Mu2 = Mu2 / N
Correl8 = 0
For I = 1 To R1.Cells.Count
If Not R1.Rows(I).Hidden And Not R2.Rows(I).Hidden Then
Correl8 = Correl8 + (R1.Cells(I) - Mu1) * (R2.Cells(I) - Mu2)
End If
Next I
Correl8 = Correl8 / Sig1 / Sig2 / N
End Function
Ein Minimalbeispiel, in dem der Fehler nochmal ersichtlich wird:
https://www.herber.de/bbs/user/126768.xlsm
Viele Grüße und danke im Voraus
Julian