|
- Sub 查询()
- Set d = CreateObject("scripting.dictionary")
- Set d1 = CreateObject("scripting.dictionary")
- w = Array("所涉及", "上", "", "中", "", "下", "")
- For k = 1 To 2
- arr = Sheets(k).[a1].CurrentRegion
- For i = 2 To UBound(arr)
- d(arr(i, 4)) = d(arr(i, 4)) + 1
- d1(arr(i, 4) & arr(i, 1)) = d1(arr(i, 4) & arr(i, 1)) + 1
- Next
- Next
- With Sheets(3)
- .[a:g].Clear
- .[a1].Resize(1, 7) = w
- .[a2].Resize(d.Count) = Application.Transpose(d.keys)
- brr = .[a1].Resize(d.Count + 1, UBound(w) + 1)
- For i = 2 To UBound(brr)
- For j = 2 To UBound(brr, 2) - 1 Step 2
- brr(i, j) = d1(brr(i, 1) & brr(1, j))
- brr(i, j + 1) = Format(brr(i, j) / d(brr(i, 1)), "0.00%")
- Next
- Next
- .[a1].Resize(UBound(brr), UBound(brr, 2)) = brr
- End With
- End Sub
复制代码 |
|