- Sub 求和()
- Dim arr, d As Object, sh As Worksheet, ar
- Dim i As Integer, j As Byte, k As Byte
- Set d = CreateObject("scripting.dictionary")
- For Each sh In Worksheets '
- If sh.Name <> "汇总" Then '只要不是名为“汇总”的表
- arr = sh.Range("a1").CurrentRegion '选定区域
- For i = 2 To UBound(arr) '循环从数组第2行到数组的最后一行
- '字典key的顺序是按照写入顺序排列的
- 'arr(i, 1) & "|" & arr(i, 2)意为字典key的内容为“日期|用户名称”
- 'arr(i, 3)意为字典item的内容是“销售额”
- d(arr(i, 1) & "|" & arr(i, 2)) = d(arr(i, 1) & "|" & arr(i, 2)) + arr(i, 3)
- Next i
- Erase arr '清除数组
- End If
- Next sh
- ar = Sheets("汇总").Range("a2").CurrentRegion
- For j = 2 To UBound(ar, 1) '数组ar的行数
- For k = 2 To UBound(ar, 2) '数组ar的列数
- '依次为
- '字典key(第2列的顶行,第2行的最左列)=key(4月1日,江苏省)
- '对应的d.item(4月1日,江苏省)=1751,填入ar(2,2)
- '而ar(2,2),填入单元格B3
- ar(j, k) = d(ar(1, k) & "|" & ar(j, 1))
- Next k
- Next j
- Sheets("汇总").Range("a2").Resize(UBound(ar, 1), UBound(ar, 2)) = ar
- End Sub
复制代码 |