二维码 购物车
部落窝在线教育欢迎您!

快速实现组内排名的三个函数有countifs和……

 

作者:赋春风来源:部落窝教育发布时间:2021-04-01 10:32:38点击:243

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

在日常工作中,需要对数据进行排名的情况有很多,不管是专业的财务、会计等,还是公司小组领导,甚至是看似八竿子打不到一起的学校老师,都会遇到。

而在Excel的众多排名问题中,最常见又最能难住大家的,就不得不提,让小白犯怂,让大神翻白眼的“组内排名”问题了。今天,小E和大家介绍的就是快速解决组内排名问题的三个函数公式!

 

相信很多朋友在接触Excel时,都会遇到一个数据集里有很多组,一个组里有很多数据,需要获得该数据在其所属的组中的排名值的情况,这就不可避免的接触到了按条件排名。

 

Excel众多的函数中,有按条件求和的SUMIF函数,有按条件求平均值的AVERAGEIF函数,也有按条件计数的COUNTIF函数……那么,按条件求组内排名,又该怎么做呢?

 

举个例子,某个武侠公司共有四个部门,分别是IT部、人事部、财务部、销售部,需要对员工在所在部门内部的综合评分进行排名。以综合评分值的高低,作为新一年每个部门的后备干部选拔的参考依据,这就需要获得数据在其所属组中的排名。

 

 

今天春风就分别通过三个公式来介绍三种组内排名的方法!希望小伙伴们在学习方法的同时,也能够回顾一些重要的函数的用法。

方法一:COUNTIFS()函数法

 

F2单元格输入公式“=COUNTIFS($D$2:$D$21,D2,$E$2:$E$21,">"&E2)+1”,按Enter键结束公式输入,下拉至F21单元格,此时F列中显示员工在各自所在部门中综合评分的排名值。

 

 

函数解析:

COUNTIFS()函数中的条件可以应用于跨区域的多个单元格,同时计算符合所有条件的次数。COUNTIFS()函数的语法为(条件区域1,条件1,条件区域2,条件2,…)

 

参数说明:

条件区域1为第一个需要计算其中满足某个条件的单元格数目的单元格区域,条件1为第一个区域中将被计算在内的条件,其形式可以为数字、表达式或文本。同理,条件区域2、条件2依次类推。最终结果为多个区域中满足所有条件的单元格个数。

 

F2单元格为例,使用COUNTIFS()函数统计出在$D$2$D$21中是“IT部”(与D2单元格一致)的单元格,再在其中查找综合评分大于自身综合评分的总个数,然后再加上1,即可获得员工郭靖的综合评分在IT部中的排名。

 

方法二:INDEX()函数法

 

F2单元格输入公式“=INDEX(FREQUENCY(($D$2:$D$21=D2)*$E$2:$E$21,E2),2)+1”,按Enter键结束公式输入,下拉至F21单元格,此时F列中显示员工综合评分在所在部门中的排名值。

 

 

函数解析:

INDEX()函数是返回表或区域中的值或值的引用,INDEX()函数有两种形式,数组形式和引用形式。数组形式通常返回数值或数值数组;引用形式通常返回引用。本例中为数组形式。

 

F2单元格为例,在公式中:

($D$2:$D$21=D2)*$E$2:$E$21”用于判断是否属于当前部门,如果是,则返回对应的总分值,否则返回0,这样可以得到一个数组。

FREQUENCY(($D$2:$D$21=D2)*$E$2:$E$21,E2)”表示以当前E2单元格的数值作为分段点,使用FREQUENCY()函数获得小于或等于E2单元格和大于E2单元格值的数据分布频率,这时将获得包含这两个频率值的数组。FREQUENCY()函数用于计算值在值范围内出现的频率,然后返回数值的垂直数组。

最后使用INDEX()函数从这个数组中提取第2个数组,即大于E2单元格值的总分个数,再加上1,即可获得E2单元格数值在所属部门中的排名。

 

方法三:SUMPRODUCT()函数法

 

F2单元格输入公式“=SUMPRODUCT(($D$2:$D$21=D2)*($E$2:$E$21>E2))+1”,按Enter键结束公式输入,下拉至F21单元格,此时F列中显示综合测评分在部门中的排名值。

 

 

函数解析:

SUMPRODUCT()函数是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。

 

F2单元格为例,在公式中,"$D$2:$D$21=D2"用于判断是否属于当前部门,"$E$2:$E$21>E2"用于判断总分是否大于自身总分值。使用SUMPRODUCT()函数将数组对应的值相乘并累加就可以得出大于自身总分的人数,然后将其加上1,即可获得员工在所在部门的排名值。

 

总结:

好了,我们分别用COUNTIFS()函数、INDEX()函数、SUMPRODUCT()函数对小组内部的数据进行了排名。这三个公式虽然很长,但都是写在单元格上的,只要逐步测试,验证,最后就能明白各个部分的意义,使用哪个公式都可以。相信大家理解了公式的原理以后,结合具体问题去自己套用是完全没问题的。

只有够灵活应用各个函数,才能成为EXCEL的高手,但这需要长时间的练习和应用。希望大家多多分享点赞,支持春风哦!你的每一次点赞和转发都是支持笔者坚持的动力。小伙伴们,还有别的什么想法,欢迎到公众号中留言。

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

在excel中,按条件进行排名,竟是如此简单!

多条件查找排名第一人的方案等你来完善!

排名公式大杂烩:不管是常规排名还是特殊排名都在这里

数据透视表送温暖来了:嘿,鼠标拖两下一次搞定业绩统计和排名!

版权申明:

本文作者赋春风;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。