基于非连续单元格数值判断求和对应权重的Excel公式求助

需求说明 #

制作成绩追踪文档时，每项作业对应L列（L5至L12）的不同权重；学生未参加测试时，对应单元格（B31、F31、J31、N31、R31、V31、Z31、AD31）会填入NA。需要实现：判断这些非连续单元格是否为数值，若是则累加对应L列的权重。原SUMPRODUCT公式无法排除NA值，需优化。

优化后的SUMPRODUCT公式 #

基础版（仅排除NA，保留所有数值，包括0分）

=SUMPRODUCT(--ISNUMBER(CHOOSE({1,2,3,4,5,6,7,8},B31,F31,J31,N31,R31,V31,Z31,AD31)),CHOOSE({1,2,3,4,5,6,7,8},L5,L6,L7,L8,L9,L10,L11,L12))

• 逻辑：用ISNUMBER判断目标单元格是否为有效数值，--将布尔结果（TRUE/FALSE）转换为1/0；再与对应权重数组相乘，最终求和时NA对应的权重会被0抵消，不计入总和。

进阶版（排除NA且排除0分，仅统计大于0的数值）

如果需要把0分也视为无效成绩，可在判断中加入>0条件：

=SUMPRODUCT(--(ISNUMBER(CHOOSE({1,2,3,4,5,6,7,8},B31,F31,J31,N31,R31,V31,Z31,AD31))*(CHOOSE({1,2,3,4,5,6,7,8},B31,F31,J31,N31,R31,V31,Z31,AD31)>0)),CHOOSE({1,2,3,4,5,6,7,8},L5,L6,L7,L8,L9,L10,L11,L12))

适用于Excel 365的替代方案 #

如果使用Excel 365及以上版本，可借助HSTACK+BYROW实现更简洁的逻辑：

=SUM(BYROW(HSTACK(B31,F31,J31,N31,R31,V31,Z31,AD31,L5:L12),LAMBDA(r,IF(ISNUMBER(INDEX(r,1)),INDEX(r,2),0))))

• 逻辑：用HSTACK将成绩单元格和对应权重两两配对，BYROW遍历每一对，判断成绩是否为数值，是则取权重，否则取0，最后用SUM累加结果。

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