SUMPRODUCT函数的排名新技能

来源：Excel应用之家

在以前的帖子中我向大家介绍过中国式排名技巧，大家可以使用MATCH函数或者FREQUENCY函数都可以实现。今天想大家介绍一个新的函数技巧--SUMPRODUCT函数，它也可以实现中国式排名。

01

中国式排名

如下图。

在单元格E2中输入“=SUMPRODUCT((D2<=$D$2:$D$17)/COUNTIF($D$2:$D$17,$D$2:$D$17))”并向下拖曳即可。

思路：

  • D2<=$D$2:$D$17部分进行判断，看有哪些数值大于单元格D2中的数值

  • COUNTIF($D$2:$D$17,$D$2:$D$17)部分利用了COUNTIF函数求不重复数字个数的技巧

  • 两部分相除，如果单元格区域D2:D17中有重复值时，SUMPRODUCT函数在求和时会只记录一次，因此就不占用名次了

总结：此技巧可以总结为下面的公式：

=SUMPRODUCT((条件1<=条件区域1）/（排名的区域))

02

按条件排名

在有些时候我们需要根据不同的组别来排名，比如说，以部门为单位进行排名。同样，SUMPRODUCT函数也可以胜任此任务！

在单元格E2中输入“=SUMPRODUCT(($C$2:$C$17=C2)*(D2<=$D$2:$D$17))”并向下拖曳即可。

思路：

  • $C$2:$C$17=C2部门对部门进行判断

  • D2<=$D$2:$D$17)部分判断有哪些数值大于单元格D2中的数值

  • 相乘的结果就是部门的排名

总结：此技巧可以总结为下面的公式：

=SUMPRODUCT((条件1=条件区域1)*(条件2=条件区域2)*...*)

赞 (0)

相关推荐