Excel 365含NA()的数值评分公式优化方案咨询

核心思路 #

利用LOOKUP函数的区间匹配特性，结合MIN和IFNA处理NA值，实现逻辑清晰、易维护的评分计算。

公式写法 #

假设目标数值数组为A:A，最终公式如下：

=LET(
    min_val, MIN(A:A),
    threshold_15, min_val*1.15,
    threshold_50, min_val*1.5,
    LOOKUP(IFNA(A:A, 0), {0, threshold_15, threshold_50}, {1, 3, 2})
)

逻辑拆解 #

1. 用MIN(A:A)提取数组中的最小值，定义为min_val，作为评分基准
2. 计算两个阈值：
   • threshold_15：最小值的115%（对应15%范围上限）
   • threshold_50：最小值的150%（对应50%范围上限）
3. IFNA(A:A, 0)将所有#N/A值替换为0，确保LOOKUP能匹配到最低评分区间
4. LOOKUP的区间映射规则：
   • 数值≤threshold_15（最小值15%范围内）→ 返回3分
   • threshold_15 < 数值 ≤threshold_50（最小值15%-50%范围内）→ 返回2分
   • 数值>threshold_50或为#N/A（已转0）→ 返回1分

方案优势 #

• 比LET+IF嵌套更直观：区间匹配逻辑一目了然，避免多层嵌套的混乱
• 比LET+SWITCH更简洁：仅通过一组映射数组实现多区间判断，代码量更少
• 易维护：修改评分规则时，只需调整阈值系数或分数映射数组即可，无需重构整个判断逻辑

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