|
本帖最后由 ykcbf1100 于 2024-4-12 11:00 编辑
参与一下。。。
- Sub ykcbf() '//2024.4.12
- Set d = CreateObject("Scripting.Dictionary")
- r = Cells(Rows.Count, 1).End(3).row
- Set rng = [a2].Resize(r - 1, 3)
- zrr = rng.Value
- rng.Sort Key1:=[b2], Order1:=1, Key2:=[c2], Order2:=2, Header:=2
- arr = rng.Value
- ReDim brr(1 To UBound(arr), 1 To 4)
- m = 1
- brr(m, 1) = "班级": brr(m, 2) = "姓名": brr(m, 3) = "班级排名": brr(m, 4) = "总分"
- For i = 1 To UBound(arr)
- s = arr(i, 2)
- If Not d.exists(s) Then
- m = m + 3
- d(s) = m
- For x = 1 To 3
- brr(m - 3 + x, 1) = s
- brr(m - 3 + x, 2) = arr(i + x - 1, 1)
- brr(m - 3 + x, 3) = x
- brr(m - 3 + x, 4) = arr(i + x - 1, 3)
- Next
- End If
- Next
- [a2].Resize(r - 1, 3) = zrr
- [g:h] = ""
- [g1:j1].Interior.Color = 49407
- With [g1].Resize(m, 4)
- .Value = brr
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter '//列居中
- .VerticalAlignment = xlCenter
- End With
- zr = Array(7)
- hb 2, 7, 7, zr
- Set d = Nothing
- MsgBox "OK!"
- End Sub
复制代码
|
|