Korrelation von gefilterten Werten berechnen (VBA)

Julian, Mittwoch, 16.01.2019, 12:36 (vor 37 Tagen) @ Julian

Für die Nachwelt hier nochmal mein Code für eine Funktion, die die Korrelation zweier Listen berechnet und dabei berücksichtigt, ob diese Listen in unterschiedlichen Zeilen beginnen:

 
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
 
 
 
Dim o As Integer
Dim p As Integer
Dim l As Integer
 
 
'Zellennummer bestimmen

For o = 4 To R1.Cells.Count
If Not R1.Rows(o).Hidden Then
Exit For
End If
Next o
 
For p = 4 To R2.Cells.Count
If Not R2.Rows(p).Hidden Then
Exit For
End If
Next p
 
l = p - o
 
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 + l).Hidden Then
N = N + 1
Mu1 = Mu1 + R1.Cells(I)
Mu2 = Mu2 + R2.Cells(I + l)
S1 = S1 + R1(I) ^ 2
S2 = S2 + R2(I + l) ^ 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 + l).Hidden Then
Correl8 = Correl8 + (R1.Cells(I) - Mu1) * (R2.Cells(I + l) - Mu2)
End If
Next I
 
Correl8 = Correl8 / Sig1 / Sig2 / N
 
End Function
 

gesamter Thread:

 RSS-Feed dieser Diskussion

powered by my little forum