Excel多条件评分：基于运动员性别与完成时间的公式实现

问题概述 #

现有表格包含Time（C列，完成时间/秒）和Gender（D列，性别：Male/Female）两列，需根据以下规则计算分数，且不能使用辅助表：

• 男性评分规则：
  • ≤23.7224秒 →10分
  • 23.7224~24.6348秒 →9分
  • 24.6348~25.5472秒 →8分
  • 25.5472~26.4596秒 →7分
  • 以此类推，每超出0.9124秒降1分，最低1分
• 女性评分规则：
  • ≤23.868秒 →10分
  • 23.868~24.786秒 →9分
  • 24.786~25.704秒 →8分
  • 以此类推，每超出0.918秒降1分，最低1分

原尝试的嵌套IF公式因仅覆盖部分区间、逻辑繁琐导致结果错误，以下是两种正确实现方案：

方案1：完整嵌套IF公式 #

覆盖1-10分全区间，逻辑清晰（适配所有Excel版本）：

=IF(D2="Male",
    IF(C2<=23.7224,10,
        IF(C2<=24.6348,9,
            IF(C2<=25.5472,8,
                IF(C2<=26.4596,7,
                    IF(C2<=27.372,6,
                        IF(C2<=28.2844,5,
                            IF(C2<=29.1968,4,
                                IF(C2<=30.1092,3,
                                    IF(C2<=31.0216,2,1))))))))),
    IF(D2="Female",
        IF(C2<=23.868,10,
            IF(C2<=24.786,9,
                IF(C2<=25.704,8,
                    IF(C2<=26.622,7,
                        IF(C2<=27.54,6,
                            IF(C2<=28.458,5,
                                IF(C2<=29.376,4,
                                    IF(C2<=30.294,3,
                                        IF(C2<=31.212,2,1))))))))))

方案2：简洁LOOKUP公式（推荐） #

利用LOOKUP的区间匹配特性，无需嵌套多层IF，公式更简洁易维护：

=LOOKUP(C2,
    IF(D2="Male",
        {0,23.7224,24.6348,25.5472,26.4596,27.372,28.2844,29.1968,30.1092,31.0216},
        {0,23.868,24.786,25.704,26.622,27.54,28.458,29.376,30.294,31.212}),
    {1,2,3,4,5,6,7,8,9,10})

公式说明： #

• 第一个数组是男女对应的时间阈值（升序排列），0作为最低阈值确保所有超时情况都能匹配到1分
• 第二个数组是对应阈值的分数，LOOKUP会自动匹配小于等于目标时间的最大阈值，返回对应分数

验证示例 #

• 男性时间24.5秒：落在23.7224<24.5≤24.6348区间，返回9分
• 女性时间25.5秒：落在24.786<25.5≤25.704区间，返回8分

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