|
楼主 |
发表于 2019-6-3 09:43
|
显示全部楼层
本帖最后由 gery_1 于 2019-6-4 10:35 编辑
Sub 年级总排名()
Dim Sht As Worksheet, Arr, i&, Brr(1 To 500, 1 To 10), n&
Dim bj$, ks, js, j&, r%, Arr1(), Crr
Application.ScreenUpdating = False
Sheet7.Activate
[a2:k5000].ClearContents
For Each Sht In Sheets
If Len(Sht.Name) < 5 Then
Arr = Sht.[a1:j41]
bj = Sht.Name
For j = 1 To UBound(Arr, 2) Step 5
For i = 2 To UBound(Arr) - 1
If Arr(i, j + 1) = "" Then Exit For
n = n + 1
Brr(n, 1) = bj
Brr(n, 2) = Arr(i, j): Brr(n, 3) = Arr(i, j + 1): Brr(n, 4) = Arr(i, j + 2): Brr(n, 5) = Arr(i, j + 3): Brr(n, 6) = Arr(i, j + 4)
Next
Next
End If
Next
[a2].Resize(n, 10) = Brr
[h2].Formula = "=sum(rc4:rc[-1])"
[h2].AutoFill [h2].Resize(n, 1)
[i2].Formula = "=rank(h2,$H$2:$H$" & n + 1 & ")"
[i2].AutoFill [i2].Resize(n, 1)
Crr = [a1].Resize(n + 1, 10)
For i = 2 To n + 1
If Crr(i - 1, 1) <> Crr(i, 1) Then
r = r + 1
ReDim Preserve Arr1(1 To r)
Arr1(r) = i
End If
Next
For i = 1 To r
If i <> r Then
js = Arr1(i + 1) - 1
Else
js = UBound(Crr)
End If
ks = Arr1(i)
ad = Cells(ks, 8).Resize(js - ks + 1, 1).Address
For j = ks To js
Cells(j, 10).Formula = "=rank(h" & j & "," & ad & ")"
Next
Next
Application.ScreenUpdating = True
End Sub
|
|