|
- Sub Macro1()
- Dim arr, d, rng As Range, i&, j%, x&, y&
- Application.ScreenUpdating = False
- Set d = CreateObject("scripting.dictionary")
- For j = 1 To Sheets.Count
- Sheets(j).Activate
- y = Rows(1).Find("计算平均值").Column
- x = Cells(65536, y).End(xlUp).Row
- arr = Range(Cells(2, y), Cells(x, y))
- Range(Cells(1, y + 2), Cells(65536, "iv")).Clear
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = d(arr(i, 1)) + 1
- Next
- z = Application.Max(d.items) + 1
- Cells(2, y + 2).Resize(d.Count) = Application.Transpose(d.keys)
- Cells(2, y + 2).Resize(d.Count).Sort Cells(2, y + 2)
- Cells(2, y + 2).Resize(d.Count, z).Borders().LineStyle = xlContinuous
- arr = Cells(2, y + 2).CurrentRegion
- For i = 1 To UBound(arr)
- If rng Is Nothing Then
- Set rng = Cells(i + 1, y + 3).Resize(1, d(arr(i, 1)))
- Else
- Set rng = Union(rng, Cells(i + 1, y + 3).Resize(1, d(arr(i, 1))))
- End If
- Next
- If Not rng Is Nothing Then rng.Interior.ColorIndex = 6
- d.RemoveAll
- Set rng = Nothing
- Next
- Columns("N").NumberFormatLocal = "0.0"
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|