最近工作上经常用到排名,总觉得excel自带的rank排名特别鸡肋,实用性不强,下面分享几个我觉得比较好用的排名方法,作对比。
一、rank排名与sumproduct排名
没错,没看错,一开始我也没想到sumproduct能排序(哭了...)。
方法一:排名(占位)rank函数
方法二:排名(不占位)SUMPRODUCT(($E$4:$E$18>=E4)/COUNTIF($E$4:$E$18,$E$4:$E$18)) 使用了数组,完成后要按三键(ctrl+shift+enter)。
分子 (E4:E18)>=E4 逐个判断大于 E4 的得分有多少,大于等于 E4 为真,返回1,否则返回0; 分母 countif(E4:E18,E4:E18) 逐个判断某个得分出现的次数;
例:以朱晓雨得分85分(E7)来说,(E4:E18)>=E7 得出(1;1;1;1;1;0;0;0;0;0;0;0;0;0;0); countif(E4:E18,E4:E18) 得出 (1;1;1;2;2;1;1;2;2;1;2;2;2;2;1) ;然后sumproduct求和,公式为 1+1+1+1/2+1/2 得出的结果为4。
两者比较:从下方的结果可以看出,对于相同分数的处理,rank 会占位,得出两个第4名,就直接跳到第6名了,相较而言,sumproduct 的排序方法(按顺序不跳过)更适用于日常场景。
![5e4f18735ac55569c00aa1d1d7abb8d7.png](https://i-blog.csdnimg.cn/blog_migrate/bf7f3560b5b215b5d8d978a3de3b34fa.jpeg)
二、分组排名
方法三:分组排名(占位)SUMPRODUCT(($B$4:$B$18=B4)*($E$4:$E$18>E4))+1
(B4:B18)=B4 判断是不是属于同一分组,真值返回1,否则返回0; (E4:E18)>E4 判断有多少得分大于E4,真值返回1,否则返回0; +1 排名还需要包含自己;
例:以 A 部门的杜莎为例,((1;1;1;1;1;0;0;0;0;0;0;0;0;0;0)*(1;1;1;1;0;1;1;1;1;1;1;1;1;0;0))+1 计算公式为 (1+1+1+1)+1 得出结果为5。
方法四:分组排名(不占位) SUMPRODUCT(($B$4:$B$18=B4)*($E$4:$E$18>=E4)/COUNTIFS($B$4:$B$18,$B$4:$B$18,$E$4:$E$18,$E$4:$E$18)) 其实就是方法二与方法三的结合。
两者比较:方法四虽然复杂,但更适用于日常场景。
![3deeab7248e5b3933438b156d0a289e4.png](https://i-blog.csdnimg.cn/blog_migrate/66310006a4283ffb74b4f81760714e70.jpeg)
今天就分享这么多了,看看后面还有没有新发现吧。
---我只是需要一盏灯,一架钢琴,一支麦克风---