Excel 2209：如何列出缺考次数最多的Top3学生姓名

数据结构说明 #

假设你的数据布局是：

• A列：班级（ABC/XYZ）
• B列：学生姓名
• C列：考试成绩（空白=缺考）
• F2：指定要查询的目标班级
• F3:F5：已算出的Top3缺考次数

一、优化缺考次数公式（可选，更简洁直观） #

如果你想简化之前的次数计算，F3单元格可以替换为这个动态数组公式（输入后直接溢出Top3次数，无需下拉）：

=TAKE(SORT(UNIQUE(HSTACK(FILTER(B:B,A:A=F2),COUNTIFS(B:B,FILTER(B:B,A:A=F2),C:C,""))),2,-1),3,2)

二、获取Top3学生姓名的公式 #

在G3单元格输入以下公式，下拉到G5，就能对应F3:F5的次数，取出Top3缺考学生姓名：

=INDEX(SORT(UNIQUE(HSTACK(FILTER(B:B,A:A=F2),COUNTIFS(B:B,FILTER(B:B,A:A=F2),C:C,""))),2,-1),ROWS(B$2:B2),1)

公式拆解：

1. FILTER(B:B,A:A=F2)：先筛选出目标班级的所有学生
2. COUNTIFS(...)：统计每个学生的缺考次数
3. HSTACK：把学生姓名和对应缺考次数合并成一个二维数组
4. UNIQUE：去掉重复的学生记录（避免同一学生多次统计）
5. SORT(...,2,-1)：按缺考次数降序排序
6. INDEX：根据行号依次提取Top1、Top2、Top3的姓名

特殊情况处理：班级有同名学生 #

如果班级里有重名的学生，需要结合唯一标识（比如学号，假设D列是学号）来区分，公式调整为：

=INDEX(SORT(UNIQUE(HSTACK(FILTER(D:D,A:A=F2),FILTER(B:B,A:A=F2),COUNTIFS(D:D,FILTER(D:D,A:A=F2),C:C,""))),3,-1),ROWS(B$2:B2),2)

这个公式会用学号确保唯一性，最终提取姓名列（第2列）。

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