排名公式 excel怎么用（excel表格如何分组排名次）【百科全说】

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

如果单纯做一种排名，比如全校排名我们可能会想到：直接把所有人分数做降序然后从上到下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正好就是当前值的排名。

然后我们在对全校排名进行升序，结果如下：

可以看到结果也是正常的。我们来看看整个操作过程：

我结合我们这个例子给大家好好解释这个函数怎么用来做排序。

从这个例子中我们可以发现只有真正理解了一个函数才能用好这个函数。

排名公式 excel怎么用（excel表格如何分组排名次）

相关推荐