|
发表于 2015-12-7 13:39
|
显示全部楼层
本楼为最佳答案
- Sub 统计()
- arr = Sheets(1).Range("a1").CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 9)
- Set d = CreateObject("scripting.dictionary")
- For i = 3 To UBound(arr)
- x = arr(i, 5) '年级key
- If Len(x) Then
- If Not d.exists(x) Then
- n = n + 1
- d(x) = n
- brr(n, 1) = arr(i, 5)
- brr(n, 2) = arr(i, 6)
- brr(n, 3) = arr(i, 7)
- End If
- brr(d(x), 4) = brr(d(x), 4) + 1
- If Len(arr(i, 26)) = 0 Then arr(i, 26) = "未参加测试"
- x1 = x & arr(i, 26)
- d(x1) = d(x1) + 1
- End If
- Next
- With Sheets("数据统计")
- .[a2:g300].ClearContents
- .[a2].Resize(n, 9) = brr
- crr = .[a1].CurrentRegion
- For j = 5 To 9
- x = Replace(crr(1, j), "人数", "")
- For i = 2 To UBound(crr)
- y = crr(i, 1) & x
- crr(i, j) = d(y)
- Next
- Next
- .[a1].CurrentRegion = crr
- End With
- End Sub
复制代码 |
|