|
试试的我这个。
- Sub test()
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- A = Sheets(1).Range("a1").CurrentRegion
- B = A: n = 1
- '统计并转换
- For i = 2 To UBound(A)
- x1 = A(i, 1) & A(i, 2) & A(i, 3) & A(i, 6) '产生单位+代码+名称+接收单位为key
- x2 = A(i, 1) '产生单位为key
- If Not d1.exists(x1) Then
- n = n + 1
- d1(x1) = n
- For j = 1 To UBound(A, 2)
- If j <> 4 Then B(n, j) = A(i, j) Else B(n, j) = 0
- Next
- End If
- B(d1(x1), 4) = B(d1(x1), 4) + A(i, 4)
- d2(x2) = d2(x2) & "," & A(i, 4) & "-" & A(i, 5) ',30-个,28-支,0.8-吨。。。。。
- Next i
-
- Set dd = CreateObject("scripting.dictionary") '计算分计量单位的汇总值
- For Each x2 In d2.keys
- xrr = Split(d2(x2), ",")
- For i = 1 To UBound(xrr)
- x = xrr(i): sz = Val(x): dw = Split(x, "-")(1) '数值、单位
- xx = x2 & dw: dd(xx) = CStr(Val(dd(xx)) + sz) & dw
- Next
- d2(x2) = Join(dd.items, "+")
- dd.RemoveAll
- Next
- '输出
- With Sheets(2)
- .Select
- .Cells.Clear
- .Range("A1").Resize(n, UBound(B, 2)) = B
- .Range("A2").Resize(n - 1, UBound(B, 2)).Sort key1:=.[a2] '按产生单位排序
- B = .Range("A1").Resize(n, UBound(B, 2))
-
- .[H1] = "汇总"
- s = 2
- For i = 3 To n
- If B(i, 1) <> B(i - 1, 1) Or i = n Then
- e = IIf(i = n, n, i - 1)
- .Cells(s, "H").Resize(e - s + 1).Merge '合并H列单元格
- .Cells(s, "H") = d2(B(s, 1))
- s = i
- End If
- Next
- End With
- End Sub
复制代码 |
|