|
本帖最后由 dyzx 于 2016-7-3 09:25 编辑
请高手出马帮忙设计,能够自动统计总分、班排名和级排名,但在随意增减科目成绩后,又能自动统计出总分、班排名和级排名,又要同时写入表头(总分、班排名、级排名),多谢各位高手指教。
- Sub tj()
- arr = [a1].CurrentRegion
- r = UBound(arr)
- c = UBound(arr, 2)
- ReDim brr(3 To UBound(arr), 1 To 3)
- Set d = CreateObject("scripting.dictionary")
- Dim NjRng As Range '年级区域
-
- qs = 3 '起始
- For i = 3 To r
- For j = 4 To c
- brr(i, 1) = brr(i, 1) + arr(i, j)
- Next
- If (i > 3 And arr(i, 1) <> arr(i - 1, 1)) Or i = r Then
- js = IIf(i = r, r, i - 1) '结束
- d(arr(i - 1, 1)) = qs & "," & js
- qs = i '下一起始
- End If
- Next
- Cells(2, c + 1) = "总分": Cells(2, c + 2) = "班排名": Cells(2, c + 3) = "级排名"
- Cells(3, c + 1).Resize(i - 3) = brr
- Set NjRng = Range(Cells(3, c + 1), Cells(r, c + 1))
- For i = 3 To r
- qs = Split(d(arr(i, 1)), ",")(0)
- js = Split(d(arr(i, 1)), ",")(1)
- brr(i, 2) = Application.WorksheetFunction.Rank(brr(i, 1), Range(Cells(qs, c + 1), Cells(js, c + 1)))
- brr(i, 3) = Application.WorksheetFunction.Rank(brr(i, 1), NjRng)
- Next
- Cells(3, c + 1).Resize(i - 3, 3) = brr
- End Sub
复制代码
|
|