Excel如何参考year、month列分组计算value列的平均值

以下方法均可实现和R语言中group_by(year, month) %>% summarise(avg = mean(value))完全一致的聚合效果，你可以根据自己的Excel版本和使用习惯选择：

方法1：数据透视表（最推荐，零公式、易维护） #

这是Excel做分组聚合效率最高的方案，操作步骤：

• 选中包含表头在内的全部原始数据区域
• 点击顶部菜单栏「插入」选项卡，选择「数据透视表」，根据提示选择透视表存放位置（建议选新工作表，避免覆盖原始数据）
• 在右侧弹出的字段配置面板中操作：
  • 将year字段拖动到「行」区域
  • 将month字段拖动到「行」区域，放在year字段下方，自动形成年-月的层级分组结构
  • 将value字段拖动到「值」区域，点击值字段的下拉按钮，选择「值字段设置」，将汇总方式修改为「平均值」后确认即可
• 后续如果原始数据有更新，只需要右键透视表选择「刷新」就能同步更新计算结果。

方法2：动态数组公式（适用于Excel 365/2021及以上版本，结果自动联动） #

如果需要聚合结果和原始数据实时联动，可以直接用函数实现：

• 在空白区域先输入表头：year、month、avg_value
• 在year表头下方的单元格输入以下公式，会自动提取所有不重复的年-月组合：

=UNIQUE(A2:B15)

注：A2:B15是示例数据中year、month两列的数值范围，你可以根据自己实际数据的行数调整。

• 在avg_value列的首个数据行输入以下匹配求平均的公式，会自动向下溢出填充所有分组的平均值：

=AVERAGEIFS(C:C,A:A,INDEX(UNIQUE(A2:B15),,1),B:B,INDEX(UNIQUE(A2:B15),,2))

注：C:C是value列所在列，A:A是year列、B:B是month列，可根据实际列位置调整。如果你的Excel版本不支持自动溢出，输入公式后按Ctrl+Shift+Enter确认数组公式，再下拉填充即可。

方法3：分类汇总（适合需要同时保留明细数据和分组结果的场景） #

• 先对原始数据做双关键字排序：选中全部数据，点击「数据」选项卡下的「排序」，主要关键字选择year，次要关键字选择month，排序依据都选「数值」，次序选「升序」后确认
• 保持数据选中状态，点击「数据」选项卡下的「分类汇总」：第一次分类汇总的分类字段选year，汇总方式选「平均值」，选定汇总项勾选value，点击确认
• 再次点击「分类汇总」，分类字段选month，汇总方式选「平均值」，选定汇总项勾选value，取消勾选「替换当前分类汇总」选项后确认，即可生成按年、月分层显示的明细+平均值汇总结果。

用你提供的示例数据计算，最终正确的分组平均值结果如下，你可以操作后对照校验：

year	month	avg_value
2018	6	84
2018	7	98
2019	1	23
2019	2	35.5
2019	3	53
2020	1	14
2020	3	34
2020	4	89

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