EXCEL有两个函数可以实现排名,通常使用的是Rank,但是很多人都不知道SUMPRODUCT也可以实现排名,并且排名更符合中国人的规则。以下表为例:

先使用Rank进行排名,在H2输入公式=RANK(G2,$G$2:$G$11,0)

按ENTER键就显示出第一个产品的排名

下拉复制到其他单元格,可以看到所有产品都按降序排了名这里说明一下,公式里最后这个0代表是降序,越大的排名越在前面,如果改成1就会被成升序,越大的排名越在后面

使用SUMPRODUCT函数来排名:在I2输入公式=SUMPRODUCT(($G$2:$G$11>G2)/COUNTIF($G$2:$G$11,$G$2:$G$11))+1

按ENTER键就显示出第一个产品的排名

下拉复制到其他单元格,可以看到所有产品都按降序排了名,并且排名结果与Rank结果是一致的

但是如果当排名里面有相同排名时,就会产生差别了,如下图所示,当第二名有两个相同时,Rank产生的结果就不会有第三名,后面直接是第四名。但是如果用SUMPRODUCT产生的结果就会按顺序排下来

SUMPRODUCT排名公式原理:公式里的条件$G$2:$G$11>G2如果成立,就返回(1,1/1)=1,公式=SUMPRODUCT(($G$2:$G$11>G2)/COUNTIF($G$2:$G$11,$G$2:$G$11))就会返回以1和0位数组元素的数组,和值为大于当前值的个数,即相对排名,+1为修正值