如何在Excel中基于打开量、点击量双维度构建动态评分体系

原评分规则参考 #

实现逻辑 #

• 优先校验打开量门槛：打开量未达到1分最低要求时，无论点击量多高都返回空值
• 打开量达标后，分别匹配打开量可支持的最高评分、点击量可支持的最高评分
• 取两个最高评分的最小值作为最终结果，自动实现点击量不足降档的规则

公式方案 #

适用Excel 365/2021及以上版本（更简洁） #

使用XLOOKUP实现，公式逻辑简单易维护：

=IF(OR(F2="",F2<$Y$2),"",MIN(XLOOKUP(F2,$Y$2:$Y$6,$X$2:$X$6,,-1),XLOOKUP(G2,$Z$2:$Z$6,$X$2:$X$6,,-1)))

适用所有Excel版本（兼容旧版） #

使用LOOKUP实现兼容性方案：

=IF(OR(F2="",F2<$Y$2),"",MIN(LOOKUP(F2,$Y$2:$Y$6,$X$2:$X$6),LOOKUP(G2,$Z$2:$Z$6,$X$2:$X$6)))

校验说明 #

用给出的示例测试完全匹配规则：

• 13次打开+0次点击：打开量最高支持1分，点击量最高支持1分，最终得1分
• 4次打开+20次点击：打开量未达最低门槛，返回空值
• 14次打开+2次点击：打开量最高支持2分，点击量最高支持2分，最终得2分
• 14次打开+20次点击：打开量最高支持3分，点击量最高支持3分，最终得3分

注意事项 #

• 请确保参数表中的$Y$2:$Y$6（打开量阈值序列）、$Z$2:$Z$6（点击量阈值序列）为升序排列
• $X$2:$X$6对应的评分需要和阈值一一对应，按1到5升序排列即可

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