在dsmch老师后面加一段即可- Sub Macro1()
- Dim arr, brr, d, i&, s&, zf$
- Set d = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion
- ReDim brr(1 To UBound(arr), 1 To 6)
- For i = 2 To UBound(arr)
- zf = arr(i, 2) & "," & arr(i, 5) & "," & arr(i, 6) & "," & arr(i, 8)
- If Not d.exists(zf) Then
- s = s + 1
- d(zf) = s
- brr(s, 1) = arr(i, 2)
- brr(s, 2) = arr(i, 5)
- brr(s, 3) = arr(i, 6)
- brr(s, 4) = arr(i, 7)
- brr(s, 5) = arr(i, 8)
- brr(s, 6) = arr(i, 1)
- Else
- n = d(zf)
- brr(n, 4) = brr(n, 4) + arr(i, 7)
- End If
- Next
- With Sheet2
- .Range("a2:g1000").Clear
- .Range("a2").Resize(s, 6) = brr
- .Range("a2").Resize(s, 6).Sort key1:=.[a2]
- arr = .[a1].CurrentRegion
- For i = 2 To UBound(arr)
- arr(i, 7) = arr(i, 4)
- If arr(i, 1) = arr(i - 1, 1) Then
- .Cells(i - 1, 7).Resize(2, 1).Merge
- arr(i - 1, 7) = Application.WorksheetFunction.SumIf(.[a2].Resize(s, 1), arr(i, 1), .[d2].Resize(s, 1))
- End If
- Next
- .[a1].CurrentRegion = arr
- End With
- End Sub
复制代码 |