1)按日期将C列的金额累计汇总
2)按日期和"支出/收入"汇总每日金额
PS:直接输出结果,不需要公式
参数定义:
函数使用:
Sub 每日累计汇总金额() Application.ScreenUpdating = False Dim sumRange As Range Dim criteriaRange_date As Range Dim e As Long, f As Long, g As Long '定义Range Set sumRange = Sheets("Sheet1").Range("C2:C31") '金额 Set criteriaRange_date = Sheets("Sheet1").Range("A2:A31") '日期 Sheets("Sheet1").Select e = Range("K1048573").End(xlUp).Row ' 初始行数 - 1 f = Range("J1048573").End(xlUp).Row '结束行数 '更新 For g = e + 1 To f '累计 Sheets("Sheet1").Cells(g, 11) = WorksheetFunction.Round(WorksheetFunction.SumIfs(sumRange, criteriaRange_date, "<=" & Cells(g, 10)), 2) Next g End Sub
Sub 每日支出收入金额() Application.ScreenUpdating = False Dim sumRange As Range Dim criteriaRange As Range Dim criteriaRange_date As Range Dim e As Long, f As Long, g As Long '定义Range Set sumRange = Sheets("Sheet1").Range("C2:C31") '金额 Set criteriaRange = Sheets("Sheet1").Range("D2:D31") '分类 Set criteriaRange_date = Sheets("Sheet1").Range("A2:A31") '日期 Sheets("Sheet1").Select e = Range("O1048573").End(xlUp).Row ' 初始行数 - 1 f = Range("N1048573").End(xlUp).Row '结束行数 '更新 For g = e + 1 To f '累计 Sheets("Sheet1").Cells(g, 15) = WorksheetFunction.Round(WorksheetFunction.SumIfs(sumRange, criteriaRange, Cells(g, 14), criteriaRange_date, "=" & Cells(g, 13)), 2) Next g End Sub
联系客服