如何构建可在TOP3/5/10终止的智能Excel排名系统？

核心规则回顾 #

• 仅成绩≥75的记录可参与排名
• 相同成绩赋予相同并列排名（例：99、98、98、97对应排名1、2、2、3）
• 排名终止逻辑：完成TOP3后，若剩余合格成绩不足以凑齐TOP5，则停止排名（仅保留TOP3）；完成TOP5后，若剩余合格成绩不足以凑齐TOP10，则停止排名（仅保留TOP5）；完成TOP10后直接终止

原公式问题分析 #

原公式依赖上方单元格值，且遇到3个及以上相同成绩时，无法正确触发终止规则，导致排名错误延续。以下提供两种适配所有行（含P1）的解决方案：

方案1：Excel 365/2021 动态数组公式（一键生成全列排名） #

在P1单元格输入以下公式，Excel会自动填充所有行的排名：

=LET(
    合格成绩,FILTER(N:N,N:N>=75),
    原始排名,RANK.EQ(合格成绩,合格成绩,0),
    唯一排名,UNIQUE(原始排名),
    终止阈值,IF(COUNTA(合格成绩)<5,3,IF(COUNTA(合格成绩)<10,5,10)),
    有效排名范围,TAKE(唯一排名,终止阈值),
    匹配结果,XLOOKUP(原始排名,有效排名范围,有效排名范围,""),
    IFERROR(XLOOKUP(N:N,合格成绩,匹配结果,""),"")
)

公式逻辑说明：

1. 先筛选出所有≥75的合格成绩
2. 给合格成绩生成基础并列排名
3. 根据合格总数量确定排名终止阈值（TOP3/5/10）
4. 截取有效排名范围，超出范围的排名返回空值
5. 最后将排名映射回原成绩列的对应位置

方案2：兼容旧版Excel的逐行公式（支持P1起始下拉） #

在P1单元格输入以下公式，然后下拉填充至所有行：

=IF(N1<75,"",
    LET(
        当前合格区域,$N$1:N1,
        合格成绩组,FILTER(当前合格区域,当前合格区域>=75),
        当前成绩排名,RANK.EQ(N1,合格成绩组,0),
        已出现唯一排名数,COUNTA(UNIQUE(RANK.EQ(合格成绩组,合格成绩组,0))),
        需终止,IF(已出现唯一排名数>3,IF(已出现唯一排名数>5,已出现唯一排名数>10,FALSE),FALSE),
        IF(需终止,"",
            IF(COUNTIF($N$1:N1,N1)>1,XLOOKUP(N1,$N$1:N1,$P$1:P1,""),已出现唯一排名数)
        )
    )
)

公式逻辑说明：

1. 先判断当前成绩是否达标，不达标直接返回空
2. 统计当前行及以上的合格成绩，计算当前成绩的排名
3. 根据已出现的唯一排名数量判断是否触发终止条件
4. 处理并列情况：若当前成绩已出现过，直接复用之前的排名；否则用唯一排名数作为当前排名
5. 触发终止条件时返回空值

示例验证 #

以成绩序列：98、97、97、83、78、74、74为例，生成的排名结果为：1、2、2、3、""、""、""，完全符合规则要求

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