美式足球竞猜管理工具：多条件筛选获胜者的公式优化问询

需求说明 #

• 仅从拥有最高正确猜题数（单元格范围C3:C18）的玩家中筛选
• 在筛选出的玩家群体里，找到其决胜选择（B3:B18）与最终总分（B20，值为27）最接近的玩家
• 在C22单元格显示对应玩家姓名（A3:A18）；若存在多个玩家并列最接近，则显示?

现有公式的问题 #

当前使用的公式仅能遍历全范围找出决胜选择最接近目标值的玩家，未纳入「最高正确猜题数」的筛选条件，无法满足需求：

=INDEX(A3:A18,MATCH(MIN(ABS(B3:B18-B20)), ABS(B3:B18-B20),0))

解决方案公式 #

1. 基础筛选公式（单个最优结果） #

先锁定最高正确猜题数的玩家群体，再在该群体内计算决胜选择与目标值的差值绝对值，找到最小值对应的姓名。Excel 2019及以后版本直接回车即可，旧版本需按Ctrl+Shift+Enter触发数组计算：

=INDEX(A3:A18,MATCH(MIN(IF(C3:C18=MAX(C3:C18),ABS(B3:B18-B20))),IF(C3:C18=MAX(C3:C18),ABS(B3:B18-B20)),0))

2. 含并列判断的完整公式 #

如果需要处理「多个玩家并列最接近」的情况，先统计最小差值在目标群体中的出现次数，若次数大于1则返回?，否则返回对应姓名：

=IF(COUNTIFS(C3:C18,MAX(C3:C18),ABS(B3:B18-B20),MIN(IF(C3:C18=MAX(C3:C18),ABS(B3:B18-B20))))>1,"?",INDEX(A3:A18,MATCH(MIN(IF(C3:C18=MAX(C3:C18),ABS(B3:B18-B20))),IF(C3:C18=MAX(C3:C18),ABS(B3:B18-B20)),0)))

案例验证 #

本次案例中，最高正确猜题数为12，对应3名玩家。使用上述公式可正确筛选出决胜选择最接近27的K's FB Lab；若这3名玩家中有多个决胜选择与27的差值相同，公式会返回?。

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