excel排名_Excel | SUMPRODUCT函数实现中国式排名和分组排名

今天给大家讲利用SUMPRODUCT函数实现两种常用的排名：名次连续的中国式排名和组内排名。

第一种排名：名次不间断的中国式排名

RANK和RANK.EQ排名次，但这两个排名函数的结果是：如果有相同的数值会出现相同的排名，再继续排下去，会出现名次“间断”的情况，如下图，两个第三名后，会直接出现第五名，而没有第四名，这种“不连续”名次，成为美式排名，在我们国家不受欢迎。

美式排名名次间断

如何实现名次不间断的“中式排名”？

【方法实现】

可以借助SUMPRODUCT 函数。

在D2中输入公式：

=SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))，确定，公式向下填充，即可实现。

如下图所示：

中式不间断排名

【函数解释】

• ($B$2:$B$7>=B2)：

将B2:B7单元格数值依次与B2对比，如果大于等于B2，返回TRUE，否则返回FALSE，所以此部分返回数值：{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}，即：{1;0;0;0;0;0}

• COUNTIF($B$2:$B$7,$B$2:$B$7)：

在B2:B7单元格区域依次计算B2:B7每个单元格数值出现的个数，返回数组：{1;1;2;2;1;1}；

• SUMPRODUCT(($B$2:$B$7>=B2)/COUNTIF($B$2:$B$7,$B$2:$B$7))：

把上述两个数组对应数值相乘，再求和，即得名次1。

再对该公式进行进一步解释：

当向下填充到D5单元格，公式变化为：=SUMPRODUCT(($B$2:$B$7>=B5)/COUNTIF($B$2:$B$7,$B$2:$B$7))

($B$2:$B$7>=B4),返回值是：{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}，即：{1;1;1;1;0;0}

COUNTIF($B$2:$B$7,$B$2:$B$7),返回值是：{1;1;2;2;1;1}

总公式SUMPRODUCT(($B$2:$B$7>=B5)/COUNTIF($B$2:$B$7,$B$2:$B$7))，即是：SUMPRODUCT({1;1;0.5;0.5;0;0})，即得名次3。

第二种排名：分组排名

如下图所示样表，员工分A、B、C要求不改变现有排序的情况下，计算出每位员工在自己组内的排名：

计算组内排名

【方法实现】

在E2单元格输入公式：

=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14))，确定，然后公式向下填充，即可得组内排名。

实现组内排名

【公式解析】

• $C$2:$C$14=C2：

在C2:C14区域的 每一个单元格与C2相比较，如果相等返回TRUE，否则返回FALSE。本部分返回数组：

{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}(数组一)

• $D$2:$D$14>=D2：

在D2:D14区域的 每一个单元格与D2相比较，如果大于或等于D2返回TRUE，否则返回FALSE。本部分返回数组：

{TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE ;FALSE }(数组二)

• COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14)：

查找C列D列从第2行到第14行每一行出现的次数。本部分得数组：

{2;1;1;2;1;1;1;1;1;1;1;1;1}(数组三)

• =SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2)/COUNTIFS($C$2:$C$14,$C$2:$C$14,$D$2:$D$14,$D$2:$D$14))：

数组一*数组二/数组三，得到的数组{0.5;0;0;0.5;0;0;0;0;0;0;0;0;0}，数组内数据加和，即得第一位的排名。