|
- Sub 班级平均分统计New()
- arr = Sheet1.[a1].CurrentRegion
- Set d = CreateObject("scripting.dictionary") '各组的班级情况
- Set d1 = CreateObject("scripting.dictionary") '各班的人数
- Set d2 = CreateObject("scripting.dictionary") '各班+学科的总分
- For i = 3 To UBound(arr)
- zb = arr(i, 3) '组别
- bj = arr(i, 4) '班级
- If Not d.exists(zb) Then '字典d:各组的班级情况
- d(zb) = "," & bj & ","
- Else
- If InStr(d(zb), "," & bj & ",") = 0 Then d(zb) = d(zb) & bj & ","
- End If
- If arr(i, 13) > 0 Then
- d1(bj) = d1(bj) + 1 '总分不为0,班级人数+1
- For j = 5 To 12
- xk = arr(2, j) '学科
- d2(bj & xk) = d2(bj & xk) + arr(i, j)
- Next
- End If
- Next
-
- For Each zb In d.keys
- Worksheets.Add after:=Sheets(Sheets.Count)
- With ActiveSheet
- .Name = zb '& "班级平均分统计"
- Sheet2.[a14:l16].Copy .Cells(1, 1)
- x = Mid(d(zb), 2, Len(d(zb)) - 2) '去掉字符串首尾的逗号
- xrr = Split(x, ",")
- .[a4].Resize(UBound(xrr) + 1) = Application.Transpose(xrr) '把该组的班级填到表中
- arr = .[a1].CurrentRegion
- For i = 4 To UBound(arr)
- bj = arr(i, 1)
- For j = 2 To 9
- xk = Replace(arr(3, j), " ", "") '学科(去掉表头中可能有的空格)
- arr(i, j) = Format(d2(bj & xk) / d1(bj), "0.00") '均分=班级+学科的总分/班级人数
- s = s + Val(arr(i, j)) '总分
- Next
- arr(i, j) = s: s = 0
- Next
- .[a1].CurrentRegion = arr
- End With
- Next
-
- End Sub
复制代码 |
|