Excel子数组SUMPRODUCT计算优化：公式与数据表重构咨询

需求与痛点明确 #

要在FACT_TABLE每行的DICEMBRE列右侧，计算**各月份招募人数 × （当月至DICEMBRE的cost×days累加值）**的总和。原方案用VLOOKUP+OFFSET嵌套，不仅公式冗长，还因OFFSET是易失性函数导致计算效率低下，现提供两种优化方案，均无需数组公式（无需Ctrl+Shift+Enter）。

原表结构回顾 #

• COSTO_DUMMY：主键为「AP编码+月份」，存储对应cost值
• GG_TARGET：主键为「编号+月份」，存储对应days值
• FACT_TABLE：首行是AP编码，次行是月份（GENNAIO~DICEMBRE），数据行是对应AP编码、月份的招募人数

方案1：新增辅助表简化公式（不重构原表） #

步骤1：创建月份数字映射表 #

在空白区域（如Sheet2的A1:B12）建立月份与数字的对应关系：

步骤2：创建累计cost×days辅助表 #

新建ACCUMULATED_COST_DAYS表，结构如下：

注：假设共有12个AP编码，每个对应12个月份，总数据行144行，可根据实际情况调整范围。SUMPRODUCT通过月份数字筛选出≥当前月份的行，直接累加cost×days的乘积。

步骤3：FACT_TABLE中写入最终公式 #

在FACT_TABLE的DICEMBRE列右侧单元格（如N2）输入：

=SUMPRODUCT($B2:$M2, XLOOKUP($A2&$B$1:$M$1, ACCUMULATED_COST_DAYS!$A:$A, ACCUMULATED_COST_DAYS!$F:$F))

下拉填充即可。该公式通过XLOOKUP快速匹配每个「AP+月份」对应的累计值，再用SUMPRODUCT完成招募人数与累计值的乘积求和，完全替代冗余的VLOOKUP+OFFSET组合。

方案2：重构原表为长表结构（更直观高效） #

如果允许调整FACT_TABLE的结构，转成长表格式能大幅降低公式复杂度：

步骤1：转换FACT_TABLE为长表 #

用Excel的「数据」→「从表格/区域」功能，将原FACT_TABLE转成三列结构：

步骤2：计算单月贡献值 #

新增两列：

• 累计cost×days：=SUMPRODUCT(--(XLOOKUP(B2, Sheet2!$A$1:$A$12, Sheet2!$B$1:$B$12) <= 12), --(XLOOKUP(B2, Sheet2!$A$1:$A$12, Sheet2!$B$1:$B$12) >= XLOOKUP(B2, Sheet2!$A$1:$A$12, Sheet2!$B$1:$B$12)), XLOOKUP(A2&B2, COSTO_DUMMY!$A:$A, COSTO_DUMMY!$B:$B), XLOOKUP(A2&B2, GG_TARGET!$A:$A, GG_TARGET!$B:$B))
• 单月贡献：=C2*D2

步骤3：汇总每个AP编码的总结果 #

用SUMIF按AP编码汇总单月贡献值：

=SUMIF(A:A, "0050001643", E:E)

最后可将汇总结果转回原FACT_TABLE的行结构。

为什么原方案低效？ #

• OFFSET是易失性函数，每次工作表有任何变动都会重新计算，大幅拖慢效率；
• VLOOKUP未使用排序+近似匹配时为线性查找，数据量大时速度极慢，换成XLOOKUP或INDEX+MATCH可实现更高效的查找逻辑。

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