高手交流:如何不排序提取排名前三的學(xué)生姓名?
?
作者:龔春光來源:部落窩教育發(fā)布時(shí)間:2018-11-23 11:17:47點(diǎn)擊:8074
這是高手的交流區(qū),小白免進(jìn)!本期題目是不提前排序,直接采用函數(shù)提取成績前三的學(xué)生姓名。難點(diǎn)在于怎么排除同分?jǐn)?shù)的干擾。
統(tǒng)計(jì)前三名學(xué)生姓名是很多老師每次考試后必須要做的工作。統(tǒng)計(jì)成績時(shí)最麻煩的就是碰到相同分?jǐn)?shù)的,如果同樣是100分到底算誰第一名呢?這不今天我們的學(xué)員LXP老師就遇到這樣問題了,她希望返回班級(jí)所有學(xué)生中前三名學(xué)生名稱,如果遇到相同的分?jǐn)?shù)按照先后排列順序返回。
如下表:A列是學(xué)生姓名,B列是學(xué)生成績信息,現(xiàn)在需要在F列返回前三名的學(xué)生姓名。
如果沒有相同分?jǐn)?shù),可以通過以下函數(shù)公式完成學(xué)生姓名提?。?/span>
=INDEX($A$2:$A$68,MATCH(LARGE($B$2:$B$68,ROW(A1)),$B$2:$B$68,0))
但正如下圖所展示的那樣,但存在相同分?jǐn)?shù)的時(shí)候,這個(gè)公式就不管用了,出現(xiàn)了重名:
按照LXP老師的要求相同分?jǐn)?shù)按排列順序先后記名次,我有兩種解決方法:
【方法一】
函數(shù)公式:=INDEX($A$2:$A$67,MATCH(LARGE($B$2:$B$67,ROW(A1)),INDEX(((COUNTIFS($F$1:F1,$A$2:$A$67)>0)*-100+1)*($B$2:$B$67),),0),0)
函數(shù)思路:
INDEX(((COUNTIFS($F$1:F1,$A$2:$A$67)>0)*-100+1)*($B$2:$B$67),)這部分函數(shù)用于將B2:B67區(qū)域中的分?jǐn)?shù)進(jìn)行重新構(gòu)建。譬如,成績99的第一名學(xué)生姓名返回后,他的分?jǐn)?shù)就會(huì)被乘以-99,就不會(huì)再以99分的分?jǐn)?shù)去爭(zhēng)搶第二名。
【方法二】
首先我們先通過函數(shù)公式=LARGE($B$2:$B$68,ROW(A1))統(tǒng)計(jì)前三名分?jǐn)?shù)。
然后通過以下函數(shù)公式完成前三名學(xué)生姓名查詢:
=OFFSET(A$1,SMALL(IF($B$2:$B$68=F2,ROW($1:$67)),COUNTIF($F$2:F2,F2)),)(數(shù)組公式,按ctrl+shift+enter三鍵完成)
函數(shù)思路:
用IF函數(shù)把所有等于某個(gè)分?jǐn)?shù)的行號(hào)找出,然后通過COUNTIF($F$2:F2,F2)統(tǒng)計(jì)F2:F4中該分?jǐn)?shù)出現(xiàn)的次數(shù),并由此確定small函數(shù)返回第幾小的行號(hào),這樣就避免了分?jǐn)?shù)相同,姓名相同。
不知對(duì)于這樣不排序提取前三名姓名的需求,你還有哪些做法?歡迎留言分享。
本文配套的練習(xí)課件請(qǐng)加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號(hào),可隨時(shí)隨地學(xué)習(xí)Excel:
相關(guān)推薦:
排名教程1中國式排名《統(tǒng)計(jì)中國式排名的三種方法,一定有一個(gè)適合你》
排名教程2多因素排名《看世界杯學(xué)做多因素排名統(tǒng)計(jì)表》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!