求Excel同行多组不同单元格乘积求和的高效公式

问题分析 #

你当前手动编写(B3*C3)+(D3*E3)+(F3*G3)+...的方式效率极低，SUMPRODUCT未生效大概率是因为未正确配对列区域或空值引发运算错误。以下是针对不同Excel版本的最优解决方案，自动处理空值/0值：

方案1：全版本通用（含旧版Excel） #

使用SUMPRODUCT结合列位置判断，自动配对每两列的乘积并求和，同时用N()函数将空值转为0避免错误：

=SUMPRODUCT(N(B3:Y3)*N(C3:Z3)*(MOD(COLUMN(B3:Y3)-COLUMN(B3),2)=0))

• 逻辑：MOD(COLUMN(...)-COLUMN(B3),2)=0 筛选出B、D、F...等起始列，与右侧相邻列（C、E、G...）配对相乘
• N()函数：将空值、文本转为0，避免空值引发的#VALUE!错误
• 范围调整：将B3:Y3和C3:Z3替换为你的实际数据范围（确保起始列是每组的第一列，结束列是每组的第二列）

如果组与组之间有间隔，可手动指定列对：

=SUMPRODUCT(N(B3)*N(C3), N(D3)*N(E3), N(F3)*N(G3), N(H3)*N(I3))

方案2：Excel 365/2021专属（动态数组） #

利用BYCOL+LAMBDA实现全自动分组计算，无需手动调整范围：

=SUM(BYCOL(CHOOSECOLS(B3:Z3,SEQUENCE(ROUNDUP(COLUMNS(B3:Z3)/2,0)*2,1,2)),LAMBDA(x,PRODUCT(x))))

• 逻辑：CHOOSECOLS按每两列一组提取数据，BYCOL对每组执行乘积计算，最后SUM汇总结果
• 优势：不管有多少组，只要是连续的两列一组，公式自动适配；空值会被PRODUCT视为0，不影响结果

方案3：数组公式（旧版Excel需按Ctrl+Shift+Enter） #

=SUM(IF(MOD(COLUMN(B3:Z3)-COLUMN(B3),2)=0,B3:Z3*OFFSET(B3:Z3,0,1),0))

• 逻辑：判断列位置为偶数列索引（从B列开始计数）时，与右侧列相乘，否则取0，最后求和
• 注意：旧版Excel输入后需按Ctrl+Shift+Enter触发数组运算，新版直接回车即可

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