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},数组内数据加和,即得第一位的排名。

阅读已结束,喜欢的话就点个赞吧
注册坚果云网盘
还有其他问题,可以咨询小坚果咨询小坚果
赞(1)
网站部分内容和图片来源于网络如有侵权请联系我们删除:好用的网盘坚果云 » SUMPRODUCT函数实现两种特殊但又常用的排名
分享到: 更多 (0)
SUMPRODUCT函数实现两种特殊但又常用的排名

本文链接:SUMPRODUCT函数实现两种特殊但又常用的排名https://content.jianguoyun.com/24207.html

评论 抢沙发

评论前必须登录!

 

坚果云 便捷、安全的网盘产品

免费试用下载客户端