|
Sub test()
Dim O(2) As Object, Ar, s$, a(), b()
Application.ScreenUpdating = False
Set O(0) = CreateObject("htmlfile"): Set O(1) = O(0).parentWindow
Sheets("数据源").Select
Ar = Range("a2:c" & Cells(Rows.Count, 1).End(3).Row)
Set O(2) = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Ar)
If Not O(2).exists(Ar(i, 3)) Then
ReDim a(0)
Else
a = O(2)(Ar(i, 3))
ReDim Preserve a(UBound(a) + 1)
End If
a(UBound(a)) = i: O(2)(Ar(i, 3)) = a
Next
k = O(2).keys: s = "[""" & Join(k, """,""") & """]"
O(1).execScript "a=" & s & ";function sortAr(m,n){return m-n};a.sort(sortAr)"
k = Split(O(1).eval("a"), ",")
Sheets("最前三名").[a3:c1000] = ""
r = Sheets("最前三名").Cells(Sheets("最前三名").Rows.Count, 1).End(3).Row + 1
ReDim a(1 To 100, 1 To UBound(Ar, 2))
For i = 0 To 2
For j = 0 To UBound(O(2)(Val(k(i))))
n = n + 1
For x = 1 To UBound(Ar, 2)
a(n, x) = Ar(O(2)(Val(k(i)))(j), x)
Next
Next
Next
Sheets("最前三名").Range("a" & r).Resize(n, 3) = a
Sheets("最后三名").[a3:c1000] = ""
r = Sheets("最后三名").Cells(Sheets("最后三名").Rows.Count, 1).End(3).Row + 1
ReDim b(1 To 100, 1 To UBound(Ar, 2))
For i = UBound(k) To UBound(k) - 2 Step -1
For j = 0 To UBound(O(2)(Val(k(i))))
m = m + 1
For x = 1 To UBound(Ar, 2)
b(m, x) = Ar(O(2)(Val(k(i)))(j), x)
Next
Next
Next
Sheets("最后三名").Range("a" & r).Resize(m, 3) = b
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub |
评分
-
1
查看全部评分
-
|