Gruppe
UDF
Problem
Die benutzerdefinierte Funktion in Zelle A17 berechnet den Merian der Zahlenreihe in A1:A16 ohneBerücksichtigung der ausgeblendetenZellen.
StandardModule: Modul1
Function MyMedian(rng As Range) As Double
Dim rngAct As Range
Dim arr() As Double
Dim dAct As Double
Dim iCountA As Integer, iCountB As Integer, iCount As Integer
For Each rngAct In rng.Cells
If Rows(rngAct.Row).Hidden = False Then
iCountA = iCountA + 1
ReDim Preserve arr(1 To iCountA)
arr(iCountA) = rngAct.Value
End If
Next rngAct
iCount = iCountA
For iCountA = 1 To UBound(arr)
For iCountB = iCountA + 1 To iCount
If arr(iCountA) > arr(iCountB) Then
dAct = arr(iCountA)
arr(iCountA) = arr(iCountB)
arr(iCountB) = dAct
End If
Next iCountB
Next iCountA
dAct = 0
For iCountA = 1 To iCount
If iCount Mod 2 = 0 Then
dAct = dAct + arr(iCountA)
If iCountA > 2 Then dAct = dAct - arr(iCountA - 2)
If iCountA > iCount / 2 Then
MyMedian = dAct / 2
Exit Function
End If
Else
dAct = arr(iCountA)
If iCountA > iCount / 2 Then
MyMedian = dAct
Exit Function
End If
End If
Next iCountA
End Function