一个函数搞定普通排名和加权排名

admin

在Excel中,默认的排名函数是RANK。其实还有一个更厉害的函数SUMPRODUCT。这个更加灵活,今天就来聊一下。

根据得分,计算普通排名和加权排名。

1.普通排名,是根据成长分的高低进行排名。

使用公式:

=SUMPRODUCT((D2<$D$2:$D$13)*1)+1

我们先找出最大的值94,根据这个值来理解公式会变得更简单。

94<$D$2:$D$13

得到的结果全部都是FALSE,也就是所值都比94小。

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

逻辑值不能直接计算,这时通过*1,转换成数值,就可以运算。FALSE*1=0,TRUE*1=1。

{0;0;0;0;0;0;0;0;0;0;0;0}

嵌套SUMPRODUCT函数,就将这些值进行求和,得出来的结果是0,也就是最大值得出来的是0,再加1,就变成第1名。

同理,第2大的值,在所有值中,就小于1个,最终结果再加1就变成第2名。

{0;0;0;0;0;0;0;0;0;0;0;1}

依次类推。

讲这么多,是为了加权排名做铺垫。

2.加权排名,就是根据贡献分*30%+目标分*30%+成长分*40%的高低进行排名。

我们可以先通过辅助列计算出加权评分,在G列输入公式。

=B2*30%+C2*30%+D2*40%

对辅助列进行排名就可以。

=SUMPRODUCT((G2<$G$2:$G$13)*1)+1

如果不通过辅助列,公式就长一点,原理是一样的。

=SUMPRODUCT((B2*30%+C2*30%+D2*40%<$B$2:$B$13*30%+$C$2:$C$13*30%+$D$2:$D$13*40%)*1)+1

当然排名还有很多种情况,今天是周日就不讲太多了,剩下靠自己摸索。

来源:Excel不加班,微信号:Excelbujiaban,由中国Excel考试网【http://www.excel.cn/】整理发布,若需引用或转载,请注明来源!

向作者提问

  • 最新评论

验证码: 点击我更换图片
全部评论