|
- Sub test2()
- Dim r%, i%
- Dim arr, brr
- Dim ws As Worksheet
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- For Each ws In Worksheets
- If ws.Name Like "*年级" Then
- d.RemoveAll
- d1.RemoveAll
- With ws
- r = .Cells(.Rows.Count, 2).End(xlUp).Row
- .Range("k3:m" & r).ClearContents
- arr = .Range("a3:m" & r)
- For i = 1 To UBound(arr)
- bj = Mid(arr(i, 5), 2)
- For j = 6 To 10
- arr(i, 11) = arr(i, 11) + arr(i, j)
- Next
- If Not d.exists(bj) Then
- Set d(bj) = CreateObject("scripting.dictionary")
- End If
- d(bj)(arr(i, 11)) = d(bj)(arr(i, 11)) + 1
- d1(arr(i, 11)) = d1(arr(i, 11)) + 1
- Next
- For Each aa In d.keys
- nn = 1
- kk = d(aa).keys
- For k = 0 To UBound(kk)
- mm = Application.Large(kk, k + 1)
- ss = d(aa)(mm)
- d(aa)(mm) = nn
- nn = ss + nn
- Next
- Next
- nn = 1
- kk = d1.keys
- For k = 0 To UBound(kk)
- mm = Application.Large(kk, k + 1)
- ss = d1(mm)
- d1(mm) = nn
- nn = ss + nn
- Next
- For i = 1 To UBound(arr)
- bj = Mid(arr(i, 5), 2)
- arr(i, 12) = d(bj)(arr(i, 11))
- arr(i, 13) = d1(arr(i, 11))
- Next
- .Range("a3").Resize(UBound(arr), UBound(arr, 2)) = arr
- End With
- End If
- Next
- Application.ScreenUpdating = True
- MsgBox "成绩统计完毕!"
- End Sub
复制代码 |
|