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

Korrelation von gefilterten Werten berechnen

Martin Asal @, Dienstag, 15.01.2019, 12:46 (vor 190 Tagen) @ Julian

Hallo Julian,

Man müsste jetzt vermutlich zwei Zähler bei der UDF einführen

nein, denn es müssen ja wohl von X und Y gleich viele Werte vorhanden sein. Dann muss nur der Startwert angepasst werden. Wenn ich das Richtig sehe, ungefähr so:

If Not R1.Rows(I).Hidden And Not R2.Rows(I+1).Hidden Then

Martin

Korrelation von gefilterten Werten berechnen

Julian, Dienstag, 15.01.2019, 13:34 (vor 190 Tagen) @ Martin Asal

Hallo Martin,

danke schonmal für die Antwort, das führt aber leider noch nicht ganz ans Ziel.
Den Autofilter setze ich automatisch mit einem Makro. I.d.R. sind alle Tabellen gleich aufgebaut, nur einmal hatte ich bisher das Problem, dass ein Datum in einer Tabelle fehlt und sich folglich dort alles verschiebt.
Die UDF soll also alles abdecken:
1. Den Fall, dass die Zellennummer übereinstimmt X A20:A40 & Y A20:A40
2. Den Fall, dass die Zellennummer nicht übereinstimmt X A19:A39 & Y A20:A40
==> In diesem Beispiel könnte z.B. in X der Wert A15 fehlen, weshalb alles etwas verschoben ist.


Ich hoffe, ich habe mich deutlich gemacht - am besten kannst du das in meinem angehängten Minimalbeispiel nachvollziehen.

Korrelation von gefilterten Werten berechnen

Julian, Dienstag, 15.01.2019, 13:48 (vor 190 Tagen) @ Julian

Ein Lösungsansatz wäre, die Werte, die nicht versteckt sind (ich habe eine Funktion in die Zellen daneben eingebaut, die "1" bei nicht ausgefilterten und 0 bei ausgeblendeten Zellen annimmt) als eine Art "Liste" (und nicht als "Werte in einer Anzahl von Zellen") abzugreifen, die ich direkt in KORREL() einspeise...

Korrelation von gefilterten Werten berechnen

Martin Asal @, Dienstag, 15.01.2019, 14:28 (vor 189 Tagen) @ Julian

Sorry aber ich schaue mir aus Prinzip keine Officedateien an, die im Netz rumschwirren. Nichts desto trotz: Die beiden Bereiche müssen ja die gleiche Anzahl von Zellen haben. Also brauchst du nur deren Startpunkte - und eben keine verschiedenen Zähler.

Martin

Korrelation von gefilterten Werten berechnen

Julian, Mittwoch, 16.01.2019, 09:23 (vor 189 Tagen) @ Martin Asal

Hm, das hilft tatsächlich... ich könnte die Zeilennummer der ersten nicht versteckten Zelle der Tabelle 1 und die Zeilennummer der ersten nicht versteckten Zelle der Tabelle 2 auswählen, voneinander abziehen und diese Variable "k" in die Schleife packen "I+k"!
Kannst du mir vielleicht einen Tipp geben, wie ich die Zeilennummer der ersten nicht versteckten Zelle abgreifen kann?

Korrelation von gefilterten Werten berechnen

Julian, Mittwoch, 16.01.2019, 09:45 (vor 189 Tagen) @ Julian

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 R1.Rows(o).Hidden Then
Exit For
End If
Next o
 
For p = 4 To R2.Cells.Count
If 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

Das hier funktioniert schonmal nicht :-(

Korrelation von gefilterten Werten berechnen

Martin Asal @, Mittwoch, 16.01.2019, 10:30 (vor 189 Tagen) @ Julian

Kannst du mir vielleicht einen Tipp geben, wie ich die Zeilennummer der ersten nicht versteckten Zelle abgreifen kann?

Du hast doch schon mit Rows.Hidden gearbeitet, also wo ist das Problem?

Martin

Korrelation von gefilterten Werten berechnen

Julian, Mittwoch, 16.01.2019, 10:57 (vor 189 Tagen) @ Martin Asal

Das Problem lag bei mir - ich habs gelöst! Danke für die Unterstützung :-)

Korrelation von gefilterten Werten berechnen

Julian, Mittwoch, 16.01.2019, 12:36 (vor 189 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
 
RSS-Feed dieser Diskussion
powered by my little forum