|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub 汇总()
- Dim arr, brr, i, j, r, d
- Set d = CreateObject("scripting.dictionary")
- With Sheet1
- r = .Cells(Rows.Count, 1).End(xlUp).Row
- arr = .Range("a1:e" & r)
- End With
- For i = 2 To UBound(arr)
- sa = arr(i, 1)
- sb = arr(i, 2)
- If Not d.exists(sa) Then
- Set d(sa) = CreateObject("scripting.dictionary")
- End If
- d(sa)(sb) = d(sa)(sb) & "," & i
- Next
- ReDim brr(1 To UBound(arr), 1 To 5)
- For Each ka In d.keys
- For Each kb In d(ka).keys
- n = n + 1
- xa = Split(d(ka)(kb), ",")
- For i = 1 To UBound(xa)
- brr(n, 1) = ka
- brr(n, 2) = kb
- brr(n, 3) = brr(n, 3) + arr(xa(i), 3)
- brr(n, 4) = brr(n, 4) + arr(xa(i), 4)
- brr(n, 5) = brr(n, 5) + arr(xa(i), 5)
- Next
- Next
- Next
- [j10].Resize(n, 5) = brr
- End Sub
复制代码 |
|