來(lái)源：微信公眾號(hào)表妹的EXCEL
今天的推送內(nèi)容胡桃，來(lái)自于一位公眾號(hào)小伙伴的提問弄唧，通過(guò)解答，表妹覺得這個(gè)問題比較有典型性闷畸，值得有類似工作場(chǎng)景的同學(xué)借鑒，所以在這里和大家分享一下吞滞，好奇心爆棚的小伙伴，趕快一起來(lái)看一看吧~~
~~~~~~條件排名的分割線~~~~~~
問題描述
【問題】班級(jí)學(xué)生各科目成績(jī)數(shù)據(jù)（無(wú)序且成績(jī)有重復(fù)值）如下圖盾沫，現(xiàn)要求排序出各考試科目前5名的學(xué)生姓名和考試成績(jī)裁赠。
問題分析
【分析1】題目要求前5名，需要使用LARGE函數(shù)來(lái)處理
【分析2】由于成績(jī)數(shù)據(jù)存在重復(fù)值赴精，想要并列排序需要使用“先放大后縮小”的方法加以區(qū)分佩捞，放大使用乘數(shù)加個(gè)數(shù)，縮小使用MOD函數(shù)
【分析3】返回的姓名和成績(jī)可能存在并列關(guān)系蕾哟，所以不能使用常規(guī)的查找函數(shù)（VLOOKUP或者LOOKUP）一忱，需要使用INDEX函數(shù)（OFFSET函數(shù)也可以）
以上莲蜘，思路已理清，下面開始編寫公式解決問題~~
------------------------------------
問題解決
【步驟1】構(gòu)建排名區(qū)域帘营，添加科目單元格下拉列表
【步驟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))
成績(jī)=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))
兩公式原理相同票渠，以姓名列公式為例，從內(nèi)而外芬迄，公式含義如下：
公式含義解釋:
①.($A$2:$A$55=$F$1)*$C$2:$C$55：返回C列中考試科目為F1的成績(jī)
②.(...)*100+ROW($A$1:$A$54)：將考試成績(jī)放大后進(jìn)行區(qū)分（乘以100后加上行序號(hào)）问顷，實(shí)現(xiàn)相同成績(jī)可以并列排序
③.LARGE(...,ROW(A1))：對(duì)放大后的成績(jī)進(jìn)行從大到小的順序排序，下拉復(fù)制公式后禀梳，ROW(A1)變成ROW(A2)杜窄、ROW(A3)...，分別代表第一大算途，第二大塞耕，第三大...，也就是第一名嘴瓤、第二名扫外，第三名...
④.MOD(...,100)：對(duì)放大后的成績(jī)進(jìn)行縮小求余，所得余數(shù)即等于該成績(jī)所在位置編號(hào)
⑤.INDEX($B$2:$B$12,...)：根據(jù)第4步中返回的位置編號(hào)纱注，在B2:B12提取對(duì)應(yīng)的成績(jī)數(shù)據(jù)
-----------------------------------
【步驟3】設(shè)置條件格式畏浆，公式：=$A2=$F$1，突出顯示各科目數(shù)據(jù)
最終效果
【效果】選擇不同科目狞贱，函數(shù)自動(dòng)返回該科目中前5名的學(xué)生姓名和成績(jī)
清楚了函數(shù)公式的設(shè)計(jì)思路刻获，小伙伴們可以把上文中的科目條件替換成自己工作中的排名條件，也可以將LARGE函數(shù)換成SMALL函數(shù)從小到大進(jìn)行排名瞎嬉。只要掌握了核心思路蝎毡，千變?nèi)f化，唯我獨(dú)尊氧枣，勤奮好學(xué)的你趕快自己動(dòng)手試試吧~~
如果你覺得表妹的分享內(nèi)容很實(shí)用沐兵，歡迎分享給其他小伙伴呦，獨(dú)樂樂不如眾樂樂嘛便监！
關(guān)注微信公眾號(hào)“表妹的EXCEL”扎谎，每周一、三烧董、五獲取原創(chuàng)分享教程毁靶。加入“表妹的EXCELQQ群（345387282）”，和勤奮好學(xué)的小伙伴們一起快樂地學(xué)習(xí)EXCEL吧逊移！
本文已在版權(quán)印備案预吆，如需轉(zhuǎn)載請(qǐng)?jiān)L問版權(quán)印14257715
