分类汇总

ynymzzr · 发表于 2020-1-15 08:48

数据源为原始表格，表格中有的行数为0值，就是在数据源表格中删除0值的行，根据c列的品名进行分类后对各月的数据进行合计和累计。在数据源表格中得到结果表的内容。

microyip · 发表于 2020-1-15 09:03

不求学会，只求获得

ynymzzr · 发表于 2020-1-15 09:34

感谢楼上关注，虽然注册时间已有15年时间了，网站成了我每天有空就上来看一看的习惯，也使我从不会表格到基本会用和基本会套用一些，本来也想学一些，但无奈没有学过英语和对vb和编程一窍不通，只能是在网站上求助各位高手，同时十分感谢各位高手的帮助。

excel海 · 发表于 2020-1-15 11:31

获得可以找代工的

3190496160 · 发表于 2020-1-15 12:00

Sub test()
Set d = CreateObject("scripting.dictionary")
With Sheets("数据源")
rs = .Cells(Rows.Count, 3).End(xlUp).Row
ar = .Range("a1:bd" & rs)

ReDim br(1 To rs, 1 To UBound(ar, 2))
For i = 6 To rs
      m = Application.Sum(.Range(.Cells(i, 9), .Cells(i, "bd")))
      If m <> 0 Then
         n = n + 1
         For j = 1 To UBound(ar, 2)
            br(n, j) = ar(i, j)
         Next j
         If Trim(ar(i, 3)) <> "" Then
            d(Trim(ar(i, 3))) = ""
         End If
      End If
Next i
End With
Sheets("结果表").UsedRange.Offset(5).Clear
For Each k In d.keys
      m = 0
      t = t + 1
      ReDim cr(1 To n, 1 To UBound(br, 2))
      For i = 1 To n
         If Trim(br(i, 3)) = k Then
            m = m + 1
            cr(m, 2) = m
            For j = 3 To UBound(br, 2)
                  cr(m, j) = br(i, j)
            Next j
         End If
      Next i
      With Sheets("结果表")
         ws = .Cells(Rows.Count, 3).End(xlUp).Row + 1
         If ws = 5 Then
            ws = 6
         Else
            ws = ws
         End If
         y = .Cells(Rows.Count, 3).End(xlUp).Row
         .Cells(ws, 1).Resize(n, UBound(cr, 2)) = cr
         .Cells(ws + m, 3) = "合计"
         For j = 9 To UBound(cr, 2)
            .Cells(ws + m, j) = Application.Sum(Application.Index(cr, 0, j))
            If t = 1 Then
                  .Cells(ws + m + 1, j) = Application.Sum(Application.Index(cr, 0, j))
            Else
               Cells(ws + m + 1, j) = .Cells(y, j) + Application.Sum(Application.Index(cr, 0, j))
            End If
         Next j
         .Cells(ws + 1 + m, 3) = "累计"
      End With
Next k
End Sub