|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
将“模板总表”中统计表格放到本文件第二个工作表的位置即可。
Sub Macro1数据汇总求和()
Dim arr, brr, crr, d1, d2, i&
Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
Set d3 = CreateObject("scripting.dictionary")
arr = Sheets(1).Range("a1").CurrentRegion
With Sheets(2)
.Range("c3:l9").ClearContents
.Range("c14:l18").ClearContents
brr = .Range("a1:l9")
crr = .Range("a12:l18")
End With
For i = 2 To UBound(arr)
s = arr(i, 1) & "|" & arr(i, 2) & "|" & arr(i, 4)
d1(s) = d1(s) + arr(i, 3)
Next
t = d1.items
For i1 = 2 To UBound(brr) - 1
For j = 3 To UBound(brr, 2)
s1 = brr(1, 1) & "|" & brr(i1 + 1, 1) & "|" & brr(2, j)
If d1.exists(s1) Then
n = n + 1
Sheets(2).Cells(i1 + 1, j) = t(n - 1)
End If
Next
Next
m = n
For i2 = 2 To UBound(crr) - 1
For j1 = 3 To UBound(crr, 2)
s2 = crr(1, 1) & "|" & crr(i2 + 1, 1) & "|" & crr(2, j1)
If d1.exists(s2) Then
m = m + 1
Sheets(2).Cells(i2 + 12, j1) = t(m - 1)
End If
Next
Next
End Sub |
评分
-
1
查看全部评分
-
|