本帖最后由 爱吃蜂蜜的狼 于 2019-6-15 18:46 编辑
- Sub test()
- Set d = CreateObject("scripting.Dictionary")
- Dim ar1(), n%, a, i
- k = Sheet1.Range("a:a").Find("*", , , , , xlPrevious).Row
- Sheet1.Range("o1:x" & k) = ""
- arr = Sheet1.Range("b1:k" & k)
- For i = 1 To k
- d.Item(arr(i, 1)) = ""
- Next
- ar = d.Keys
- For Each a In ar
- n = n + 1
- ReDim Preserve ar1(1 To 10, 1 To n)
- For ii = 1 To k
- If arr(ii, 1) = a Then
- ar1(1, n) = a
- ar1(2, n) = ar1(2, n) + arr(ii, 2)
- ar1(3, n) = ar1(3, n) + arr(ii, 3)
- ar1(4, n) = ar1(4, n) + arr(ii, 4)
- ar1(5, n) = ar1(5, n) + arr(ii, 5)
- ar1(6, n) = ar1(6, n) + arr(ii, 6)
- ar1(7, n) = ar1(7, n) + arr(ii, 7)
- ar1(8, n) = ar1(8, n) + arr(ii, 8)
- ar1(9, n) = ar1(9, n) + arr(ii, 9)
- ar1(10, n) = ar1(10, n) + arr(ii, 10)
- y = y + 1
- End If
- Next
- If n <> 1 Then
- ar1(2, n) = ar1(2, n) / y
- ar1(3, n) = ar1(3, n) / y
- ar1(4, n) = ar1(4, n) / y
- ar1(5, n) = ar1(5, n) / y
- ar1(6, n) = ar1(6, n) / y
- ar1(7, n) = ar1(7, n) / y
- ar1(8, n) = ar1(8, n) / y
- ar1(9, n) = ar1(9, n) / y
- ar1(10, n) = ar1(10, n) / y
- End If
- y = 0
- Next
- ar2 = Application.Transpose(ar1)
- Sheet1.Range("o1").Resize(UBound(ar2), UBound(ar2, 2)) = ar2
- k1 = Sheet1.Range("p:p").Find("*", , , , , xlPrevious).Row
- Sheet1.Range("o" & k1 + 1) = "科目总平均"
- Sheet1.Range("o" & k1 + 1).Select
- For iii = 2 To UBound(ar2, 2)
- For i1 = 2 To k1
- m = m + ar2(i1, iii)
- Next
- x = x + 1
- Selection.Offset(, x) = m / (k1 - 1)
- m = 0
- Next
- Sheet1.Range("o2:o" & k1).Sort [o2], xlAscending
- End Sub
复制代码
|