Forum des Access-Tutorials

Korrelation von gefilterten Werten berechnen (VBA)

Julian , Dienstag, 15.01.2019, 10:47 (vor 190 Tagen)

Hallo zusammen!

In meinem Excel-Tool filtere ich Werte nach dem Datum und berechne anschließend die Korrelation der übrig gebliebenen Daten. 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: Wenn der erste Bereich X von Werten und der zweite Bereich Y dürfen nicht in verschiedenen Zeilen starten. 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.

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?

Viele Grüße,
Julian

Eine Beispieldatei habe ich hochgeladen

Die UDF kommt aus folgendem Forum und hat folgenden Code:

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