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
Man müsste jetzt vermutlich zwei Zähler bei der UDF einführen und die Zwischenergebnisse getrennt berechnen, aber da stoße ich bereits an meine Grenzen in VBA und mathematischem Verständnis. Kann mir jemand weiterhelfen?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