第二个问题,自定义函数,附代码
y2Na8snN.rar
(10.01 KB, 下载次数: 8)
Function Hsort(rng As Range, n)
Dim i%, M%, t%, k%
Dim S, Hmax
Dim A(), B(), C()
Application.Volatile
With rng
M = .Count
Hmax = Application.Max(rng)
For i = 1 To M
If .Cells(i) = Hmax Then k = i: Exit For
Next
ReDim A(1 To k - 1), B(1 To M - k), C(1 To M)
For i = 1 To k - 1
A(i) = .Cells(k - i)
Next
For i = 1 To M - k
B(i) = .Cells(i + k)
Next
C(1) = Hmax '最大值只能为1个,根据你给的数据,好像没有重复的
End With '如果有的话,自定义函数就不能用了哦
'---------------------------------------------------------------------------------
t = IIf((k - 1) < (M - k), k - 1, M - k) '公共部分,t为重复的部分
For i = 1 To t
If A(i) < B(i) Then
S = A(i)
A(i) = B(i)
B(i) = S
End If
Next
For i = 1 To t - 1
If A(i + 1) > B(i) Then
S = A(i + 1)
A(i + 1) = B(i)
B(i) = S
End If
Next
For i = 1 To t
C(i * 2) = A(i)
C(i * 2 + 1) = B(i) '公共部分已经搞定
Next
'---------------------------------------------------------------------------------
For i = 1 To t
A(i) = 0
B(i) = 0
Next
For i = 2 * t + 2 To M '非公共部分
If k - 1 > t Then
C(i) = Application.Large(A, i - 2 * t - 1)
Else
C(i) = Application.Large(B, i - 2 * t - 1)
End If
If C(i) = 0 Then C(i) = ""
Next
'---------------------------------------------------------------------------------
If n > M Then
Hsort = "-结束-"
Else
Hsort = C(n)
End If
End Function |