SUMPRODUCT分组排名公式

之前有一篇文章介绍,在那一篇中的公式“=SUMPRODUCT(($C$2:$C$14=C2)*($D$2:$D$14>=D2))”出现了bug——如果在某组中出现并列第一的情况,结果显示为并列“第二”。

如下图:

今天再给一个更严密的公式。

公式实现

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

此公式修正了并列第一却出现并列“第二”的bug。如下图:

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

本文链接:SUMPRODUCT分组排名公式https://content.jianguoyun.com/24009.html

评论 抢沙发

评论前必须登录!

 

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

免费试用下载客户端