[已解决]多条件分类汇总

安全网 · 发表于 2014-12-21 14:09

怎样设置A列对应的E列数据进行汇总。A列对应的F列时间知改为行形式（重复的只取一次值）后附表

最佳答案

grf1973

2014-12-23 16:28

请看附件。

安全网 · 发表于 2014-12-21 15:24

重复的数据汇总后的要累加

grf1973 · 发表于 2014-12-23 16:18

搞个通用的，三张表都能用

Sub 汇总()
arr = Workbooks("数据工作簿.xls").Sheets(1).[a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
Set d1 = CreateObject("scripting.dictionary")
If ActiveSheet.Name = "姓名" Then c1 = 2: c2 = 12
If ActiveSheet.Name = "公积金" Then c1 = 2: c2 = 10
If ActiveSheet.Name = "部门" Then c1 = 5: c2 = 12
For i = 3 To UBound(arr)
d(arr(i, c1)) = "" '姓名（部门）去重
d1(arr(i, c1) & arr(i, 13)) = d1(arr(i, c1) & arr(i, 13)) + arr(i, c2) '姓名+月份（或部门+月份）为key,工资（或公积金）为item
Next
[a3:o100].ClearContents
[b3].Resize(d.Count, 1) = Application.Transpose(d.keys) 'B列姓名
Cells(3 + d.Count, 2) = "总计"
brr = [a1].CurrentRegion: mr = UBound(brr)
For i = 3 To mr - 1
brr(i, 1) = i - 2
For j = 4 To UBound(brr, 2)
brr(i, j) = d1(brr(i, 2) & brr(2, j))
If brr(i, j) > 0 Then
brr(i, 3) = brr(i, 3) + brr(i, j)
brr(mr, j) = brr(mr, j) + brr(i, j)
brr(mr, 3) = brr(mr, 3) + brr(i, j)
End If
Next
Next
[a1].Resize(mr, UBound(brr, 2)) = brr
End Sub