根据条件求排名

来源：微信公众号表妹的EXCEL

今天的推送内容，来自于一位公众号小伙伴的提问，通过解答，表妹觉得这个问题比较有典型性，值得有类似工作场景的同学借鉴，所以在这里和大家分享一下，好奇心爆棚的小伙伴，赶快一起来看一看吧~~

~~~~~~条件排名的分割线~~~~~~

问题描述

【问题】班级学生各科目成绩数据（无序且成绩有重复值）如下图，现要求排序出各考试科目前5名的学生姓名和考试成绩。

------------------------------------

问题分析

【分析1】题目要求前5名，需要使用LARGE函数来处理

【分析2】由于成绩数据存在重复值，想要并列排序需要使用“先放大后缩小”的方法加以区分，放大使用乘数加个数，缩小使用MOD函数

【分析3】返回的姓名和成绩可能存在并列关系，所以不能使用常规的查找函数（VLOOKUP或者LOOKUP），需要使用INDEX函数（OFFSET函数也可以）

以上，思路已理清，下面开始编写公式解决问题~~

------------------------------------

问题解决

【步骤1】构建排名区域，添加科目单元格下拉列表

-----------------------------------

【步骤2】编写公式

姓名=INDEX($B$2:$B$55,MOD(LARGE((($A$2:$A$55=$F$1)*$C$2:$C$55)*100+ROW($A$1:$A$54),ROW(A1)),100))

成绩=INDEX($C$2:$C$55,MOD(LARGE((($A$2:$A$55=$F$1)*$C$2:$C$55)*100+ROW($A$1:$A$54),ROW(A1)),100))

两公式原理相同，以姓名列公式为例，从内而外，公式含义如下：

公式含义解释:

①.($A$2:$A$55=$F$1)*$C$2:$C$55：返回C列中考试科目为F1的成绩

②.(...)*100+ROW($A$1:$A$54)：将考试成绩放大后进行区分（乘以100后加上行序号），实现相同成绩可以并列排序

③.LARGE(...,ROW(A1))：对放大后的成绩进行从大到小的顺序排序，下拉复制公式后，ROW(A1)变成ROW(A2)、ROW(A3)...，分别代表第一大，第二大，第三大...，也就是第一名、第二名，第三名...

④.MOD(...,100)：对放大后的成绩进行缩小求余，所得余数即等于该成绩所在位置编号

⑤.INDEX($B$2:$B$12,...)：根据第4步中返回的位置编号，在B2:B12提取对应的成绩数据

-----------------------------------

【步骤3】设置条件格式，公式：=$A2=$F$1，突出显示各科目数据

------------------------------------

最终效果

【效果】选择不同科目，函数自动返回该科目中前5名的学生姓名和成绩

-------------------------------------

清楚了函数公式的设计思路，小伙伴们可以把上文中的科目条件替换成自己工作中的排名条件，也可以将LARGE函数换成SMALL函数从小到大进行排名。只要掌握了核心思路，千变万化，唯我独尊，勤奋好学的你赶快自己动手试试吧~~

如果你觉得表妹的分享内容很实用，欢迎分享给其他小伙伴呦，独乐乐不如众乐乐嘛！

关注微信公众号“表妹的EXCEL”，每周一、三、五获取原创分享教程。加入“表妹的EXCELQQ群（345387282）”，和勤奋好学的小伙伴们一起快乐地学习EXCEL吧！