|
- Sub test1()
- Dim r%, i%
- Dim arr, brr(1 To 10000, 1 To 10)
- Dim reg As New RegExp
- Dim d As Object
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- With reg
- .Global = False
- .Pattern = "^\d\.\d{1,2}$"
- End With
- m = 0
- For Each ws In Worksheets
- If reg.test(ws.Name) Then
- With ws
- arr = .Range("a2:j41")
- For j = 1 To 6 Step 5
- For i = 1 To IIf(j = 1, 40, 19)
- If Len(arr(i, j + 1)) <> 0 Then
- m = m + 1
- brr(m, 1) = ws.Name
- brr(m, 3) = arr(i, j + 1)
- brr(m, 4) = arr(i, j + 2)
- brr(m, 5) = arr(i, j + 3)
- brr(m, 6) = arr(i, j + 4)
- End If '
- Next
- Next
- End With
- End If
- Next
- For i = 1 To m
- For j = 4 To 7
- brr(i, 8) = brr(i, 8) + brr(i, j)
- Next
- d(brr(i, 8)) = d(brr(i, 8)) + 1
- If Not d1.exists(brr(i, 1)) Then
- Set d1(brr(i, 1)) = CreateObject("scripting.dictionary")
- End If
- d1(brr(i, 1))(brr(i, 8)) = d1(brr(i, 1))(brr(i, 8)) + 1
- Next
- nn = 1
- kk = d.keys
- For k = 0 To UBound(kk)
- mm = Application.Large(kk, k + 1)
- ss = d(mm)
- d(mm) = nn
- nn = nn + ss
- Next
- For Each aa In d1.keys
- nn = 1
- kk = d1(aa).keys
- For k = 0 To UBound(kk)
- mm = Application.Large(kk, k + 1)
- ss = d1(aa)(mm)
- d1(aa)(mm) = nn
- nn = nn + ss
- Next
- Next
- For i = 1 To m
- brr(i, 9) = d(brr(i, 8))
- brr(i, 10) = d1(brr(i, 1))(brr(i, 8))
- Next
- With Worksheets("年级总排名")
- .UsedRange.Offset(1, 0).ClearContents
- .Range("a2").Resize(m, UBound(brr, 2)) = brr
- With .Range("a1:j" & m + 1)
- With .Font
- .Size = 10
- .Name = "宋体"
- End With
- .Borders.LineStyle = xlContinuous
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- End With
- End With
- End Sub
复制代码 |
评分
-
1
查看全部评分
-
|