|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
Sub tt()
Dim sh As Worksheet, ar, d As Object, i&, r&, c&, j&, s$
ThisWorkbook.Activate
Set sh = Sheet1
With sh
If .AutoFilterMode Then .AutoFilterMode = False
r = .Cells(.Rows.Count, "a").End(xlUp).Row
c = .Cells(1, .Columns.Count).End(xlToLeft).Column
If r < 2 Then Exit Sub
ar = .Range("a1").Resize(r, c)
Set d = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
For j = 4 To UBound(ar, 2)
s = Split(ar(i, 2), " ")(0) & "," & ar(1, j)
d(s) = d(s) + Val(ar(i, j))
Next
End If
Next
If d.Count = 0 Then Exit Sub
End With
Set sh = Sheet2
Erase ar
With sh
If .AutoFilterMode Then .AutoFilterMode = False
.UsedRange.Offset(1, 1).ClearContents
ar = .Range("a1").CurrentRegion
For i = 2 To UBound(ar)
If ar(i, 1) <> "" Then
For j = 2 To UBound(ar, 2)
s = ar(i, 1) & "," & ar(1, j) & "累计数量"
If d.exists(s) Then ar(i, j) = d(s)
Next
End If
Next
.Range("a1").CurrentRegion = ar
Set d = Nothing
End With
End Sub
|
|