问题场景
1)按日期将C列的金额累计汇总
2)按日期和"支出/收入"汇总每日金额
PS:直接输出结果,不需要公式
参数定义&函数说明
参数定义:
- Range格式定义汇总范围
- Long 格式定义参数。因为后续参数长度会超过32767,所以选取Long。
- Long(长整型)变量存储为带符号的 32 位(4 字节)数字,值范围从 -2,147,483,648 到 2,147,483,647。
- Integer(整形)Integer 变量存储为 16 位(2 字节)数字,值范围为 -32,768 至 32,767。
函数使用:
- WorksheetFunction.Round (expression, [ numdecimalplaces ])
- WorksheetFunction.SumIfs (Arg1、Arg2、Arg3…)
1)结果代码:每日累计汇总金额
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
2)结果代码:每日支出收入金额
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