×

实用EXECL办公函数 - 【中国式排名】

admin admin 发表于2020-05-06 19:53:35 浏览64 评论0

抢沙发发表评论

上次分享了 RANK 排名函数,也提到了 RANK 函数其实是不符合中国人的排名习惯的。但是 Execl 中并没有直接可用于中国式排名的函数。

例如:下图可以看到,因为出现 2 个第 1 名,因此没有第 2 名;以此类推没有第 5、7 名。



RANK 函数的特征就是:相同数值在排名中具有相同的名次,且会占据名次的数字位置。

但中国人的排名习惯则是即使有并列的第 1 名,之后仍有第 2 名。也就是名次并列但不占名次,这就是所谓的【中国式排名】。
如下图:


能够实现中国式排名的公式很多,但小编比较喜欢最简单好记的方式,这里就只分享我常用的公式吧。


我所用的公式就是:以 G6 单元格为例。
=SUMPRODUCT(($D$4:$D$20>=$D6)/COUNTIF($D$4:$D$20,$D$4:$D$20))


接下来对公式做一个简单的解析:

SUMPRODUCT是多条件求和函数【多条件排名也会用到】

公式中的“($D$4:$D$20>=$D6)”是返回一个数组:

在 G6 返回的逻辑值则是:

{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

公式中的“/COUNTIF($D$4:$D$20,$D$4:$D$20)

可表述为“*1/COUNTIF($D$4:$D$20,$D$4:$D$20)

在 G6 返回的结果则是:

{0.5,0.5,1,0.5,0.5,0.5,0.5,1,1,1,1,1,1,1,1,1,1}

($D$4:$D$20>=$D6)/COUNTIF($D$4:$D$20,$D$4:$D$20)

这个公式计算出来的结果是 0.5,0.5,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0。最后用 SUMPRODUCT函数对上述结果求和, G6 单元格就会得到结果 2。

当然还有种方式是利用辅助列和 VLOOKUP 函数来实现。

1、我们可以把 D  列订单量复制到表格中的空白列后去重【打开数据选项-删除重复值】

2、对去重后的数据列利用 RANK 函数进行排名。

3、最后利用 VLOOKUP 函数对 D 列的订单量进行精确查找,也可以完成中国式排名。

这种方式比较适合于对数组求和理解不够透彻的时候。

嘿嘿,文章结束,下次再分享多条件排名。


本篇文章来源于微信公众号: Execl 实用小知识

群贤毕至

访客