来源：微信公众号表妹的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吧！
本文已在版权印备案，如需转载请访问版权印14257715
网友评论