|
直接修改代码也可以- Sub 生成排名()
- Dim arr1, Arr12(), Arr13()
- With Sheets("资料")
- row1 = .Range("A" & Rows.Count).End(xlUp).Row
- .Range("h3:h" & row1).ClearContents
- arr1 = .Range("B3:E" & row1)
- ReDim Arr11(1 To UBound(arr1), 1 To 2)
- For i = 1 To UBound(arr1)
- If arr1(i, 4) = "缺考" Then arr1(i, 4) = 0
- Next i
- Set D1 = CreateObject("Scripting.Dictionary")
- Set D2 = CreateObject("Scripting.Dictionary")
- For i = 1 To UBound(arr1)
- If Not D1.EXISTS(arr1(i, 1)) Then
- m1 = m1 + 1
- D1(arr1(i, 1)) = m1
- ReDim Preserve Arr12(1 To 2, 1 To m1)
- Arr12(1, m1) = arr1(i, 1)
- Arr12(2, m1) = m1
- End If
- Arr11(i, 2) = Format(D1(arr1(i, 1)), "00")
- Next i
- For j = 1 To m1
- For i = 1 To UBound(arr1)
- If Arr12(1, j) = arr1(i, 1) Then
- If Not D2.EXISTS(Val(arr1(i, 4))) Then
- M2 = M2 + 1
- D2(Val(arr1(i, 4))) = M2
- ReDim Preserve Arr13(1 To 2, 1 To M2)
- Arr13(1, M2) = Val(arr1(i, 4))
- Arr13(2, M2) = M2
- If M2 > 1 Then
- For k1 = 1 To M2 - 1
- For k2 = k1 + 1 To M2
- If Val(Arr13(1, k1)) < Val(Arr13(1, k2)) Then
- t = Arr13(1, k1)
- Arr13(1, k1) = Arr13(1, k2)
- Arr13(1, k2) = t
- End If
- Next k2
- Next k1
- End If
- End If
- End If
- Next i
- For i = 1 To UBound(arr1)
- If Arr12(1, j) = arr1(i, 1) Then
- For K3 = 1 To M2
- If Arr13(1, K3) = Val(arr1(i, 4)) Then
- Arr11(i, 1) = Arr13(2, K3)
- Exit For
- End If
- Next K3
- End If
- Next i
- Erase Arr13
- D2.RemoveAll
- M2 = 0
- Next j
- .Range("F3").Resize(UBound(Arr11), 1) = Arr11
- End With
- End Sub
复制代码 |
评分
-
查看全部评分
|