在日常的数据分析中，按条件分组排名是一个非常常见却又容易出错的操作，比如：按照班级+学科对学生成绩进行名次排序，或按学科整体进行成绩对比。传统做法可能需要借助多列辅助计算，还要频繁筛选分组，步骤繁琐、出错率高。今天分享一个无需辅助列，只用一个公式，即可实现“智能分组排名”的方法，简单高效，特别适合教学、销售、运营等场景使用。

数据背景说明

假设你正在处理一份学生成绩表，其中包含以下字段：

  • B列：班级

  • C列：学科

  • D列：成绩

你的目标是生成两种排名：

  • E列：在“同班级、同学科”内的学生名次

  • F列：在“同学科”内的整体学生名次

接下来，我们通过两个 SUMPRODUCT 公式，快速实现这两类排名需求。

一、按“班级 + 学科”组合条件进行排名

在 E2 单元格中输入以下公式：

=SUMPRODUCT(($B$2:$B$54=B2)*($C$2:$C$54=C2)*($D$2:$D$54>D2))+1

然后将公式下拉填充至对应区域。

原理解读：

  • ($B$2:$B$54=B2)：判断是否属于同一个班级

  • ($C$2:$C$54=C2)：判断是否为同一个学科

  • ($D$2:$D$54>D2)：比较成绩是否高于当前行

这三个条件相乘后，形成一个新的数值数组，SUMPRODUCT 负责统计满足“同组且成绩更高”的行数，再加 1 即为当前名次。

建议配合“按班级+学科排序”，可以更直观看到分组排名效果。

学生成绩怎么按班级和学科排名？这个公式太香了！-趣帮office教程网
班级 + 学科排名

二、按“学科”整体排名

在 F2 单元格中输入公式：

=SUMPRODUCT(($C$2:$C$55=C2)*($D$2:$D$55>D2))+1

此公式逻辑与上一种类似，只是去掉了班级字段，只按学科字段分组排名。

学生成绩怎么按班级和学科排名？这个公式太香了！-趣帮office教程网
按“学科”整体排名

公式拆解与逻辑说明

第一步：筛选分组内数据
通过 ($B$2:$B$54=B2)*($C$2:$C$54=C2) 判断当前行是否在相同班级和学科中，生成布尔值数组并转为数值形式。

第二步：比较成绩高低
用 ($D$2:$D$54>D2) 判断哪些同组数据的成绩高于当前行。

第三步：统计高分人数，生成排名
将上述两个数组相乘后，得到的数组中有几个值为 1，就代表当前行的成绩低于几位同组学生。加 1 后就是该行的名次。

注意事项

  • 所有公式中的引用范围应使用绝对引用（带 $ 符号），防止填充时引用错乱；

  • 确保成绩列为“数值格式”，避免文本型数字导致计算错误；

  • 若数据量非常大，推荐使用 COUNTIFS + SUMIFS 的组合方法提升计算效率；

  • 可结合筛选、排序、条件格式等功能，使排名结果更直观清晰。

总结

通过本文介绍的 SUMPRODUCT 函数技巧，我们可以快速在excel表格中完成分组排名，不依赖辅助列、逻辑清晰、效率高，是处理教育、销售、行政报表等数据时的高效利器。

>>免费下载：表格大师