Excel 三种方法实现按月汇总数据

学习一下 Excel 按月汇总数据，有三种方法实现：

1. 运用数据透视表轻松完成按月汇总；
2. 使用 SUMIFS 和 DATE 函数组合，设置条件进行求和；
3. 利用 SUMPRODUCT 函数，提取所需月份，将其视为 1，再与数值区域相乘，然后对所有乘积进行求和。

下面是具体方法示例，以 LibreOffice Calc 7.6 演示，部分细节与 MS Office Excel 有小的差异。

方法一、数据透视表

1. 将光标定位在数据源的任意单元格，然后单击【插入】——【数据透视表】——弹出对话框，单击确定即可；选择表格或区域，放置透视表的位置：新工作表；
2. 拖动“字段”窗口内【日期】到“行”，拖动【数量】到“值”，单击“确定”即可完成汇总数据；
3. 在汇总表的任一日期上点击右键选择【组合】——【按月】，即可完成按月度汇总。

这是最简单直观的方式。
右键组合的功能在 LibreOffice 上似乎没有，但是可以在透视表选中任一日期后点击主菜单【数据】——【分组及分级显示】——【组合 F12】，弹出对话框后设置按月组合完成汇总。
注意：如果数据源是跨年度的，需要同时选择“年、月”组合，否则会把所有年度相同月份数据计算在一起。

方法二、SUMIFS 函数

SUMIFS 函数是多条件求和函数，其语法为：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)，即：SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2，更多条件)
SUMIFS 使用难点在于如何设置条件，要计算某个月份的数据，同时这个月份值还可以变化，就需要用 DATE 日期拼接函数生成一个日期。如下图所示，在 G1 单元格输入 2，然后在 H4 单元格输入公式 =DATE(2024,G1,1)，得到结果就是 2024/2/1。

然后将条件 1 设置为大于等于计算月度的第一天，条件 2 设置为小于计算月度次月的第一天，选择求和的区域即可。
G2 单元格计算 2 月份总量的公式示例如下：
=SUMIFS($C$2:$C$18,$A$2:$A$18,">="&DATE(2024,G1,1),$A$2:$A$18,"<"&DATE(2024,G1+1,1))

为了能够拖拉自动填充数据——此例向右自动套用公式计算 3 月份，需要注意函数中有些单元格要使用绝对引用。

方法三、SUMPRODUCT 函数

SUMPRODUCT 函数是万能求和函数，可在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
语法：SUMPRODUCT(array1, [array2], [array3], …)
参数 array1 是必需的第一个数组参数，array2, array3 等可选。
注意：数组参数必须具有相同的维数，非数值数组条目将被视为零。

G6 单元格计算 2 月份总量的公式示例如下：
=SUMPRODUCT((MONTH($A$2:$A$18)=G5)*1,$C$2:$C$18)
这里嵌套了 MONTH 函数从日期数据组获取月份，并与 G5 单元格的月份数值进行比较，如果相同则返回 TRUE，然后将其乘以1，从而得到一个数据区域，然后这个区域与 $C$2:$C$18 区域对应的值进行相乘运算，最后对所有乘积相加求和。

延伸使用的示例 #

在工作薄内另建一个工作表来汇总数据。
让表格的最上方分别显示当前月份和日期、所属季度，为了展示动态变化，这里借助函数而非手工录入。
当日 在B2 单元格用 TODAY 函数并修改单元格格式为仅显示日期 =TODAY()
本月 在 A2 单元格用 MONTH 引用 B2 获得月份 =MONTH(B2)
季度 在 C2 单元格用 ROUNDUP 函数将月份除以3，向上取整到最接近的整数，得到季度数 =ROUNDUP(A2/3,0)

在日期下方制作各品类的相关汇总。

当月汇总
前面的方法三只是单条件求和，这里用 SUMPRODUCT 进行两个条件求和。公式原理即 =SUMPRODUCT((MONTH(日期数据列)=数字月份)*(条件数据区域1=条件1)*(条件数据区域N=条件N)*求和区域)
在 B5 单元格输入示例公式：
=SUMPRODUCT(MONTH($工作表1.A$2:A$18)=A$2,$工作表1.B$2:B$18=A5,$工作表1.C$2:C$18)
注意：A2 需要绝对引用，而 A5 是相对引用。
然后在 B5 单元格右下角下拉（快速）填充，完成其它行的自动汇总。

如果表格里包含不同年度的数据，就得按年月组合来判断取值。相较于传统的 YEAR 提取年、MONTH 提取月，直接用 TEXT 提取年月统计更方便，e 代表2位数的年，mm 代表2位数的月。TEXT 是文本格式，如果年月是数字格式，需要 –TEXT 转换成数字格式，只有统一格式才能比较。

示例：
A15 单元格根据 B2 数据提取年月 =TEXT(B2,"emm")
B15 单元格输入条件2
C15 单元格输入公式 =SUMPRODUCT(TEXT($工作表1.A$2:A$18,"emm")=A$15,$工作表1.B$2:B$18=B15,$工作表1.C$2:C$18) 回车

A16 单元格是手工输入的文本格式年月
B16 单元格输入公式
=SUMPRODUCT(TEXT($工作表1.A$2:A$18,"emm")=A$16,$工作表1.B$2:B$18="鸡",$工作表1.C$2:C$18) 回车
C16 单元格输入公式
=SUMPRODUCT(TEXT($工作表1.A$2:A$18,"emm")=A$16,$工作表1.B$2:B$18=B15,$工作表1.C$2:C$18) 回车

A17 单元格是手工输入的数字格式年月
B17 单元格输入公式
=SUMPRODUCT(--TEXT($工作表1.A$2:A$18,"emm")=A$17,$工作表1.B$2:B$18="鼠",$工作表1.C$2:C$18)
回车

在 LibreOffice Calc 里 ee 代表4位数的年，mm 代表2位数的月。

上月汇总
C5 输入
=SUMPRODUCT(MONTH($工作表1.A$2:A$18)=A$2-1,$工作表1.B$2:B$18=A5,$工作表1.C$2:C$18)
然后回车，下拉填充数据即可。

月环比增长率
在目标单元格 D5 中输入公式：
=IFERROR(TEXT((B5-C5)/C5,"增长0%(↑);下降0%(↓);持平(--)"),"")
然后回车。（因为表格只有本年度数据，1月份和1季度前面的数据都没有，如果不用 IFERROR 函数会报错）

本季度汇总
原理与月度是一样的，在目标单元格中输入：
=SUMPRODUCT(ROUNDUP(MONTH($工作表1.A$2:A$18)/3,0)=C$2,$工作表1.B$2:B$18=A5,$工作表1.C$2:C$18)
回车、下拉填充。

上季度汇总
只需在查询季度数位置减去 1 即可：(ROUNDUP(MONTH(A$2:A$18)/3,0)=C$2-1)

按区间统计
这里的区间日期都是手填的，日期>=开始日期，同时满足日期<=结束日期，就能按日期区间统计。
例：=SUMPRODUCT(($工作表1.$A$2:$A$18>=K$2)*($工作表1.$A$2:$A$18<=L$2)*($工作表1.$B$2:$B$18=M1)*$工作表1.$C$2:$C$18)
同理，也可以按月份区间统计，只需嵌套 MONTH 提取月份，开始月份、结束月份只输入月份数值不含年日。
跨年的月份区间统计：年月>=开始年月，同时满足年月<=结束年月，日期套 –TEXT 提取年月并转换成数值格式，便于跟单元格 K、L 年月的数值格式统一。
示例：
=SUMPRODUCT((--TEXT($工作表1.$A$2:$A$18,"eemm")>=K$5)*(--TEXT($工作表1.$A$2:$A$18,"eemm")<=L$5)*$工作表1.$C$2:$C$18)
单元格 K5 202310 L5 202403 均为数字格式

以下两种写法都能正确计算：
=SUMPRODUCT((MONTH($A$2:$A$18)=$G19)*($B$2:$B$18=G20),$C$2:$C$18)
=SUMPRODUCT((MONTH(A$2:A$18)=G19),B$2:B$18=G20,C$2:C$18)

—— CC BY-NC-ND 4.0