拉波尔特Excel | SUMPRODUCT函数实现两种特殊但又常

阅读 208 2022-01-06 13:13

清晨六点,与您相约

今天给大家讲利用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},数组内数据加和,即得第一位的排名。

热门文章

问答 热点 前沿 观点 视觉 谣言粉碎机 果然 方式

©2022-01-06 13:13 足球直播cc/瑞安/足球直播cc 版权所有;

违法和不良信息举报邮箱:    举报电话:110    网上有害信息举报专区    信息举报专区