Excel中如何按月份分组多年支出并计算12个月移动平均值？

问题根源 #

你之前分组日期时只选择了「月」，Excel会自动忽略年份，把所有同月份的记录合并在一起，导致无法按年月维度统计，也没法计算跨年度的移动平均。

方法一：优化数据透视表（适合习惯用透视表的场景） #

1. 先把数据源转为结构化表格 #

选中数据区域，按 Ctrl+T 勾选「我的表格有标题」，确定后表格会自动扩展，新增数据时只需往下填，后续刷新透视表就能同步。

2. 创建带年月分组的透视表 #

• 插入数据透视表，将字段按以下配置：
  • 行区域：Date
  • 筛选器：Type（用来快速切换特定支出类型）
  • 值区域：Amount（设置为「求和」）
• 右键点击行区域的任意日期单元格 → 选择「组合」，在弹出的对话框里同时勾选「年」和「月」，点击确定。此时行标签会显示「2023年5月」这类唯一的年月标识，不会再合并不同年份的同月份数据。

3. 添加12个月移动平均 #

因为透视表的计算字段无法直接引用动态范围，建议把透视表结果转为普通区域后计算：

• 选中透视表所有数据（含标题），按 Ctrl+C → 右键选择「粘贴值」，转为普通单元格。
• 在月度总额列旁边插入新列，标题设为「过去12个月移动平均」。
• 在第一个有数据的单元格（比如C3）输入公式：

  =IF(ROW()-2<12,AVERAGE(B$3:B3),AVERAGE(OFFSET(B3,-11,0,12,1)))
  

  下拉填充即可。公式逻辑：如果当前行之前的记录不足12条，就计算已有记录的平均值；够12条时，取当前行及往前11行的12个数据求平均。

方法二：用动态数组公式（Excel 365/2021，适合持续新增数据的场景） #

这种方法无需透视表，自动适配新增数据：

1. 结构化数据源（同方法一，Ctrl+T 转为表格，命名为 Expenses） #

2. 生成唯一年月序列 #

在空白单元格（比如A2）输入：

=SORT(UNIQUE(TEXT(Expenses[Date],"yyyy-mm")))

这会自动生成所有不重复的年月，并按时间升序排列，新增数据后刷新即可更新。

3. 计算指定类型的月度总额 #

在B2输入（以统计TypeA为例）：

=SUMIFS(Expenses[Amount],Expenses[Type],"TypeA",TEXT(Expenses[Date],"yyyy-mm"),A2#)

A2# 表示引用A2生成的动态数组，公式会自动填充所有年月的对应总额。

4. 计算12个月移动平均 #

在C2输入：

=BYROW(B2#,LAMBDA(x,LET(pos,MATCH(x,B2#,0),AVERAGE(TAKE(B2#,pos,-MIN(12,pos))))))

这个公式会遍历每个月度总额，自动计算从当前行往前最多12条记录的平均值，不足12条时取所有已有记录的均值，新增数据后自动更新结果。

内容的提问来源于stack exchange，提问作者wilkas