二維碼 購物車
部落窩在線教育歡迎您!

高手交流:如何不排序提取排名前三的學(xué)生姓名?

?

作者:龔春光來源:部落窩教育發(fā)布時(shí)間:2018-11-23 11:17:47點(diǎn)擊:8074

分享到:
0
收藏    收藏人氣:2人
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。
編按:

這是高手的交流區(qū),小白免進(jìn)!本期題目是不提前排序,直接采用函數(shù)提取成績前三的學(xué)生姓名。難點(diǎn)在于怎么排除同分?jǐn)?shù)的干擾。

 

Excel高手擂臺(tái),部落窩教育

 

統(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

http://zuijiahehuoren.cn/ewebeditor/uploadfile/20180914164059384001.png

相關(guān)推薦:

排名教程1中國式排名《統(tǒng)計(jì)中國式排名的三種方法,一定有一個(gè)適合你

排名教程2多因素排名《看世界杯學(xué)做多因素排名統(tǒng)計(jì)表