Google Sheets替代嵌套IF的加权平均计算脚本方案咨询

前提约定 #

先明确4个得分的跨表引用示例，你可以按需替换为实际单元格地址：

• Manager得分：Manager!B2（对应Manager工作表的B2单元格）
• Colleague1得分：Colleague1!B2
• Colleague2得分：Colleague2!B2
• Colleague3得分：Colleague3!B2

核心实现公式 #

无需嵌套IF，使用Google Sheets原生数组函数即可实现全部规则，直接粘贴到结果单元格即可：

=LET(
  // 定义变量：Manager得分、同事得分数组
  mgr_val, Manager!B2,
  col_vals, {Colleague1!B2, Colleague2!B2, Colleague3!B2},
  // 判断Manager得分是否为有效数值
  mgr_is_valid, ISNUMBER(mgr_val),
  // 过滤出同事中的有效得分
  valid_col_vals, FILTER(col_vals, ISNUMBER(col_vals)),
  // 核心逻辑分支
  IF(
    NOT(mgr_is_valid),
    // Manager无效时：所有有效得分算术平均
    AVERAGE(FILTER({mgr_val, col_vals}, ISNUMBER({mgr_val, col_vals}))),
    // Manager有效时：固定占50%权重，同事侧50%权重由有效得分均分
    mgr_val * 0.5 + AVERAGE(valid_col_vals) * 0.5
  )
)

低版本兼容方案 #

如果你的Google Sheets版本不支持LET函数，可使用以下简化版公式：

=IF(
  ISNUMBER(Manager!B2),
  Manager!B2*0.5 + AVERAGE(FILTER({Colleague1!B2, Colleague2!B2, Colleague3!B2}, ISNUMBER({Colleague1!B2, Colleague2!B2, Colleague3!B2})))*0.5,
  AVERAGE(FILTER({Manager!B2, Colleague1!B2, Colleague2!B2, Colleague3!B2}, ISNUMBER({Manager!B2, Colleague1!B2, Colleague2!B2, Colleague3!B2})))
)

可选优化 #

如果需要处理所有得分均无效的场景，可在公式外层嵌套IFERROR自定义返回内容，示例：
=IFERROR(原有公式, "无有效得分")

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