|
发表于 2014-4-15 22:48
|
显示全部楼层
本楼为最佳答案
- Sub Macro1()
- Dim arr, brr, d, d2, rng As Range, i&
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- arr = Range("a1").CurrentRegion
- For i = 2 To UBound(arr)
- d(arr(i, 3)) = d(arr(i, 3)) + 1
- d2(arr(i, 3)) = d2(arr(i, 3)) & " " & i
- Next
- ReDim brr(1 To d.Count, 1 To 11)
- a = d.keys: b = d.items: b2 = d2.items
- For i = 0 To d.Count - 1
- brr(i + 1, 1) = a(i)
- n = Int(b(i) * 0.92)
- x = Split(b2(i))
- y1 = Val(x(1)): y2 = Val(x(UBound(x)))
- For j = 4 To 13
- Set rng = Range(Cells(y1, j), Cells(y2, j))
- s = 0
- For k = 1 To n
- s = s + Application.Large(rng, k)
- Next
- brr(i + 1, j - 2) = s / n
- Next
- Next
- Range("o18").Resize(UBound(brr), 11) = brr
- End Sub
复制代码 |
|