如果一定要通过VBA,代码如下,但如果只做数据统计用,针对你这个我建议还是用数据透视表!- Sub xx()
- Dim n&, arr, d, brr, i%
- Set d = CreateObject("scripting.dictionary")
- With Sheet1
- n = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("B2:H" & n)
- For i = 1 To n - 1
- If d.Exists(arr(i, 1) & "|" & arr(i, 3)) Then
- d(arr(i, 1) & "|" & arr(i, 3)) = Split(d(arr(i, 1) & "|" & arr(i, 3)), "|")(0) + arr(i, 6) & "|" & Split(d(arr(i, 1) & "|" & arr(i, 3)), "|")(1) + arr(i, 7)
- Else
- d.Add arr(i, 1) & "|" & arr(i, 3), arr(i, 6) & "|" & arr(i, 7)
- End If
- Next
- End With
- x = 1
- With Sheet2
- For Each k In d.keys
- x = x + 1
- .Cells(x, 1) = Split(k, "|")(0)
- .Cells(x, 2) = Split(k, "|")(1)
- .Cells(x, 3) = Split(d(k), "|")(0)
- .Cells(x, 4) = Split(d(k), "|")(1)
- Next
- End With
- End Sub
复制代码 |