不用数据透视表，让大神头疼的组内排名问题用这3个函数更好

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

分享到:

收藏收藏人气：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列中显示综合测评分在部门中的排名值。