如何在Excel中分组排名（excel并列排名公式）

啊风搞机 • 2022-09-19 12:28:57 • 软件

教导主任给了我这次全校学生的期中考试成绩表，让我把每位同学所在班级排名名次以及在全校排名名次都列出来。

如果单纯做一种排名，比如全校排名我们可能会想到：直接把所有人分数做降序然后从上到下1，2，3……递增下去，但是即使这样也有问题就是分数一样的人排名不一样了。幸运的是Excel提供了排名函数「RANK」。

「RANK」作用：返回一列数字的数字排位，语法；

「RANK」语法：RANK(需要排名的数字,排名数字列表,升序或降序)；

对于全校排名只需要在第一个学生全校排名列输入公式：「=RANK(C2,C$2:C$12)」然后向下填充即可。

然后班级可以先以班级排序，保证同一个班级都在一起，然后以每个班级为单位使用一次RANK函数，比如本例中在D2单元格中输入「=RANK(C2,C$2:C$5)」。

然后每个班级都做一次。这样最终也能完成，不说有多少个班级就要写多少次RANK函数，还有个致命的缺陷就是，如果我们把所有数据按全校排名升序排序，我们会发现班级排名里很多数据都显示了#N/A。显然班级排序的写法还是有局限性的。

这要怎么办呢，还有什么函数能达到排名的效果呢？

这儿还真有个比较特别的函数「SUMPRODUCT」，说它特殊是因为如果你在所有函数列表里面看到这个函数，那么你会看到这样的解释：属于数学和三角函数分类下，作用是返回对应的数组元素的乘积和。这实在无法让人把它和排名联想到一起，但是如果你点击这个函数看到详细解释：SUMPRODUCT函数返回对应范围或数组的个数之和，默认操作是乘法，但也可以进行加减除运算，在继续看下去就会发现，咦这个函数有点意思。

我们来看看官网给的其中一个示例。这个例子是什么意思呢？官网给出的解释是：本示例使用 SUMPRODUCT 返回给定项和大小的总销售额。我们看公式：「=SUMPRODUCT((B2:B7=B10)*(C2:C7=C10)*D2:D7」，首先「(B2:B7=B10)」我们可以解读出在B2:B7单元格区域查找出值为B10单元格值的行，也就是Item列为Y的行，然后「(C2:C7=C10)」可以解读出在在C2:C7单元格区域查找出值为C10单元格值的行，也就是Size列为M的行，而「D2:D7」我们只能看出来是拿到前面筛选出结果的D列值，至于拿到值以后怎么操作我们并无法看出来，但是结合前面筛选出来的记录是第3行和第6行，对于D列值分别为21和41，在看看D10单元格中的公式最后结果是62我们可以推测出这个公式是返回指定Item列和Size列记录的Sold列和。

之所以在这里解释这么多，是为了来说明我们怎么灵活的使用这函数来实现官网没介绍的功能。

通过上面的解释我们可以推断出「(B2:B7=B10)*(C2:C7=C10)」是筛选作用，「D2:D7」是求和作用。那么「=SUMPRODUCT((B2:B7=B10)*(C2:C7=C10)」是否返回的就是筛选出来的数据条数呢？我们再把思维打开点，这里可以写多个筛选条件，我们是否可以想成一个对应班级筛选，一个对应分数筛选，再把思维打开点，筛选可以写成「(C2:C7=C10)」也就可以写出「(C2:C7>C10)」，再想想分数，大于指定的分数的个数是不是就可以转换为排名呢。

最后我们得到公式：「=SUMPRODUCT((A$2:A$12=A2)*(C$2:C$12>C2))+1」。加1是因为大于当前值的个数加1正好就是当前值的排名。