Option Explicit
Sub test()
Dim arr, i, j, k
arr = Range("a1:k" & Cells(Rows.Count, "a").End(xlUp).Row)
For i = 1 To UBound(arr, 1)
If arr(i, 1) = "班级" Then
i = i + 1
For j = i To UBound(arr, 1)
If arr(j, 1) = "片区" Then
For k = i To j: arr(k, 11) = k: Next
Call dsort(arr, i, j, 1, UBound(arr, 2), 8, False)
Call rank(arr, i, j, 8, 9, True)
Call dsort(arr, i, j, 1, UBound(arr, 2), 11, True)
i = j: Exit For
End If
Next
End If
Next
[a1].Resize(UBound(arr, 1), UBound(arr, 2) - 1) = arr
End Sub
Function dsort(arr, first, last, left, right, key, order)
Dim i, j, k, t
For i = first To last - 1
For j = i + 1 To last
If arr(i, key) < arr(j, key) Xor order Then
For k = left To right
t = arr(i, k): arr(i, k) = arr(j, k): arr(j, k) = t
Next
End If
Next j, i
End Function
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 |