如何将Excel多列权重计分辅助公式合并为单单元格嵌套公式

不需要设置任何辅助列，直接在A-F列后首行目标单元格输入对应版本公式，下拉即可完成全量计算，完全覆盖原有分步逻辑。

Excel 365/2021及以上版本（支持LET函数） #

公式逻辑清晰、计算效率更高，输入后直接回车即可生效：

=LET(
  base_weights, Controls!$H$2:$H$7,
  scores, A2:F2,
  valid_mask, ISNUMBER(scores)*(scores>=1)*(scores<=5),
  valid_count, SUM(valid_mask),
  IF(valid_count=6, Q2,
    invalid_weight_total, SUMPRODUCT(base_weights*(1-valid_mask)),
    add_per_col, invalid_weight_total/valid_count,
    adj_weights, (base_weights + add_per_col)*valid_mask,
    SUMPRODUCT(scores, adj_weights)
  )
)

公式各段完全对应原有辅助列逻辑：

• valid_mask：生成与A-F列一一对应的有效性标记，值为1代表是1-5之间的有效评分，0代表N/A、空白、0或超出评分范围的无效值，替代原有N/A转0的预处理步骤
• valid_count：统计当前行有效评分列数量，替代原H列COUNTIF逻辑
• 分支判断：有效列数为6（全列有效）时直接返回Q2原始得分，与原有规则一致
• invalid_weight_total：汇总所有无效列对应的原始权重总和，替代原G列SUMIF逻辑
• add_per_col：计算每个有效列需要平均追加的权重值，替代原I列逻辑
• adj_weights：计算每列调整后的最终权重，无效列权重置为0，替代原J-O列共6列的权重调整计算
• 最终SUMPRODUCT计算有效评分与对应调整后权重的乘积和，得到最终得分，替代原最终列求和逻辑

Excel 2019及更早版本（无LET函数支持） #

使用嵌套SUMPRODUCT实现同等逻辑，输入公式后需按Ctrl+Shift+Enter组合键作为数组公式生效：

=IF(SUMPRODUCT(--(ISNUMBER(A2:F2)),--(A2:F2>=1),--(A2:F2<=5))=6,Q2,
 SUMPRODUCT(
  IF(ISNUMBER(A2:F2),IF(A2:F2>=1,IF(A2:F2<=5,A2:F2,0),0),0),
  (Controls!$H$2:$H$7 + (1-SUMPRODUCT(Controls!$H$2:$H$7,IF(ISNUMBER(A2:F2),IF(A2:F2>=1,IF(A2:F2<=5,1,0),0),0)))/SUMPRODUCT(--(ISNUMBER(A2:F2)),--(A2:F2>=1),--(A2:F2<=5)))
  *IF(ISNUMBER(A2:F2),IF(A2:F2>=1,IF(A2:F2<=5,1,0),0),0)
 )
)

使用说明 #

• 公式自动识别N/A、空白、0值、超出1-5评分区间的异常值为无效列，无需提前做数据清洗
• 原始权重直接引用Controls!H2:H7区域，后续调整固定权重时无需修改公式结构
• 公式下拉即可覆盖全部729行数据，无中间计算列占用表格空间

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