- Sub Macro1()
- Dim arr, ar, d, m%, i&, j%, k%, l%, s&
- Set d = CreateObject("scripting.dictionary")
- n = Range("o65536").End(xlUp).Row
- [u:ae].NumberFormatLocal = "@"
- For m = 11 To 15 Step 4
- ll = IIf(m = 11, 4, 2) '频率在数组中的列
- arr = Cells(1, m).Resize(n, 5)
- ReDim ar(1 To UBound(arr), 1 To UBound(arr, 2))
- For i = 2 To UBound(arr)
- If Not d.exists(arr(i, ll)) Then
- d(arr(i, ll)) = i
- Else
- d(arr(i, ll)) = d(arr(i, ll)) & "," & i
- End If
- Next
- s = 0
- For j = 1 To 3 '前3名
- x = Application.Large(d.keys, j)
- y = Split(d(x), ",")
- For k = 0 To UBound(y)
- s = s + 1
- For l = 1 To UBound(arr, 2)
- ar(s, l) = arr(y(k), l)
- Next
- Next
- Next
- lie = IIf(m = 11, 21, 27)
- Cells(1, m).Resize(1, 5).Copy Cells(1, lie)
- Cells(2, lie).Resize(s, UBound(ar, 2)) = ar
- d.RemoveAll
- Next
- End Sub
复制代码 |