Excel无需辅助列汇总月度数据至年度：SUMIF等公式实现方案

核心公式（基础版） #

在Annual工作表的目标单元格（如数据区域的首个单元格）输入以下RC引用公式，然后批量填充：

=SUMPRODUCT((YEAR(Monthly!R1C:R1C[1000])=R1C)*Monthly!RC:RC[1000])

公式详解 #

• Monthly!R1C:R1C[1000]：引用Monthly工作表第1行（日期行），从当前列对应位置开始往后取1000列（可根据实际最大月度列数调整数值），覆盖所有可能生成的月度日期列。
• YEAR(...) = R1C：提取月度日期的年份，与Annual当前列的表头年份（R1C即当前列第1行的数值年份）匹配，筛选出对应年份的月度列。
• Monthly!RC:RC[1000]：引用Monthly工作表中当前行、对应列范围的数值，仅年份匹配的列会参与求和计算。

优化版（自动适配动态列范围） #

如果Monthly的月度列数每次生成差异较大，可使用INDEX+COUNTA自动定位最后一列，避免多余空列参与计算，提升效率：

=SUMPRODUCT(
    (YEAR(Monthly!R1C:INDEX(Monthly!R1C:R1C[10000],COUNTA(Monthly!R1C:R1C[10000])))=R1C)*
    Monthly!RC:INDEX(Monthly!RC:RC[10000],COUNTA(Monthly!R1C:R1C[10000]))
)

关键注意事项 #

• 确认Monthly工作表的日期列是标准日期格式，而非文本格式，否则YEAR函数无法正确提取年份。
• 批量填充兼容性：RC引用格式支持横向/纵向批量填充，无需手动调整公式适配不同单元格位置，完美适配Python生成的工作表场景。
• 无需辅助列：全程通过公式内部逻辑完成年份匹配与求和，符合要求的无辅助列方案，避免冗余计算。

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