'只要>倒10名就结束,这样倒10名就可以不存在
'支持并列排名,看AA列倒排名情况,,,
Option Explicit
Sub test()
Dim arr, i, j, k, kk, m
arr = Range("a1:m" & Cells(Rows.Count, "a").End(xlUp).Row + 1)
Call bsort(arr, 2, UBound(arr, 1) - 1, 1, UBound(arr, 2), 2) '班级升序
m = 1
For i = 2 To UBound(arr, 1) - 1
For j = i To UBound(arr, 1) - 1
If arr(j, 2) <> arr(j + 1, 2) Then
Call bsort(arr, i, j, 1, UBound(arr, 2), 3) '总分升序
Call rank(arr, i, j, 3, 13, True) '倒数排名
For k = i To UBound(arr, 1) - 1
If arr(k, 13) > 10 Then Exit For
m = m + 1
For kk = 1 To UBound(arr, 2)
arr(m, kk) = arr(k, kk)
Next kk, k
i = j: Exit For
End If
Next j, i
With [o2]
.Resize(Rows.Count - 1, UBound(arr, 2)).ClearContents
.Resize(m, UBound(arr, 2)) = arr
End With
End Sub
Function rank(arr, first, last, key, col, order)
Dim i, j, m
m = 1: arr(first, col) = 1
For i = first + 1 To last
If order Then
m = m + 1
Else
If arr(i, key) <> arr(i - 1, key) Then m = m + 1
End If
arr(i, col) = IIf(arr(i, key) = arr(i - 1, key), arr(i - 1, col), m)
Next
End Function
Function bsort(arr, first, last, left, right, key)
Dim i, j, k, t
For i = first To last - 1
For j = first To last + first - 1 - i
If arr(j, key) > arr(j + 1, key) Then
For k = left To right
t = arr(j, k): arr(j, k) = arr(j + 1, k): arr(j + 1, k) = t
Next
End If
Next j, i
End Function |