Option Explicit
Sub test()
Dim arr
arr = [a1].CurrentRegion.Resize(, 2)
Call rank(arr, 1, UBound(arr, 1), 1, 2, False) 'true为美式排名
[a1].CurrentRegion.Resize(, 2) = arr
End Sub
Function rank(arr, first, last, key, col, order As Boolean)
Dim i As Long, j As Long, m As Long
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
If arr(i, key) = arr(i - 1, key) Then
arr(i, col) = arr(i - 1, col)
Else
arr(i, col) = m
End If
Next
End Function |