本帖最后由 3190496160 于 2020-2-13 17:56 编辑
Sub 排名次()
Set d = CreateObject("scripting.dictionary")
Set dc = CreateObject("scripting.dictionary")
With Sheets("总成绩册")
ws = .Cells(Rows.Count, 1).End(xlUp).Row
ar = .Range("a1:i" & ws)
.Range("a3:i" & ws) = Empty
For i = 3 To UBound(ar)
If Trim(ar(i, 1)) <> "" And Trim(ar(i, 2)) <> "" Then
d(Trim(ar(i, 1))) = ""
End If
Next i
For Each k In d.keys
n = 0
dc.RemoveAll
ReDim br(1 To UBound(ar), 1 To UBound(ar, 2))
For i = 3 To UBound(ar)
If Trim(ar(i, 1)) = k Then
n = n + 1
dc(Trim(ar(i, 2))) = ""
For j = 1 To UBound(ar, 2)
br(n, j) = ar(i, j)
Next j
End If
Next i
For i = 1 To n
For s = i + 1 To n
If br(i, 7) < br(s, 7) Then
For j = 1 To UBound(br, 2)
k1 = br(i, j)
br(i, j) = br(s, j)
br(s, j) = k1
Next j
End If
Next s
br(i, 9) = i
Next i
For Each kc In dc.keys
m = 0
ReDim brr(1 To n, 1 To UBound(br, 2))
For i = 1 To n
If Trim(br(i, 2)) = kc Then
m = m + 1
For j = 1 To UBound(br, 2)
brr(m, j) = br(i, j)
Next j
End If
Next i
For i = 1 To m
For s = i + 1 To m
If brr(i, 7) < brr(s, 7) Then
For j = 1 To UBound(brr, 2)
k2 = brr(i, j)
brr(i, j) = brr(s, j)
brr(s, j) = k2
Next j
End If
Next s
brr(i, 8) = i
Next i
rs = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(rs, 1).Resize(m, UBound(brr, 2)) = brr
Next kc
Next k
End With
MsgBox "OK!"
End Sub
|