|
- Sub Macro1()
- Dim arr, brr, crr, d, d2, rng As Range, i&
- Set d = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- arr = Range("a1").CurrentRegion
- [r4:aa17].ClearContents
- brr = [q3:aa17]
- ReDim crr(1 To UBound(brr) - 1, 1 To UBound(brr, 2) - 1)
- 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
- For i = 2 To UBound(brr)
- n = Int(d(brr(i, 1)) * 0.92)
- x = Split(d2(brr(i, 1)))
- y1 = Val(x(1)): y2 = Val(x(UBound(x)))
- For j = 2 To UBound(brr, 2)
- l = Application.Match(brr(1, j), [a1:m1], 0)
- Set rng = Range(Cells(y1, l), Cells(y2, l))
- s = 0
- For k = 1 To n
- s = s + Application.Large(rng, k)
- Next
- crr(i - 1, j - 1) = s / n
- Next
- Next
- Range("r4").Resize(UBound(crr), UBound(crr, 2)) = crr
- End Sub
复制代码 |
|