成績排名,原來有4種方法!你是不是只知道Rank函數(shù)?

成績排名,原來有4種方法!你是不是只知道Rank函數(shù)?

源文件如下，需要對(duì)表中的成績進(jìn)行排名。

中國式排名(例如，兩個(gè)人分?jǐn)?shù)一樣，并列第1，兩個(gè)第1，后面就是第2名)

非中國式排名(例如，兩個(gè)人分?jǐn)?shù)一樣，并列第1，兩個(gè)第1，后面就是第3名)

-01- 中國式排名

方法一：SUMPRODUCT+ COUNTIF 排名

=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1。 即在G2單元格輸入公式后，同時(shí)按ctrl+shift+enter，然后下拉即可完成。

函數(shù)解析

sumproduct有兩個(gè)英文單詞組成，sum是和，product是積，所以是乘積之和的意思。COUNTIF是計(jì)數(shù)函數(shù)。

函數(shù)解釋

= COUNTIF(B$2:B$7,B$2:B$7) 表示的是分?jǐn)?shù)出現(xiàn)的次數(shù)，和=COUNTIF($B$1：$B$7，B1)表達(dá)意思是一樣的。例如，92分出現(xiàn)的是2次，因此結(jié)果是2;其他分?jǐn)?shù)出現(xiàn)的是1次，因此返回的結(jié)果是1

在SUMPRODUCT中，數(shù)組公式 B$2:B$7>B2 表示的是由TRUE和FALSE組成的邏輯數(shù)組{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE｝轉(zhuǎn)化為1和0的數(shù)組{0;0;0;0;0;0｝。

因此G2單元格中的公式，=SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 是對(duì){0;0;0;0;0;0｝/{2;1;1;1;1;2｝+1求和， 即 0/2+0/1+0/1+0/1+0/1+0/2+1=1

因此G3單元格中的公式，=SUMPRODUCT((B$2:B$7>B3)/COUNTIF(B$2:B$7,B$2:B$7))+1 是對(duì){1;0;0;0;0;1｝/{2;1;1;1;1;2｝+1求和， 即 1/2+0/1+0/1+0/1+0/1+1/2+1=2

因此類推即可。

方法二：COUNTIF+ IF 排名

=SUM(–IF(B$2:B$7>B2,1/COUNTIF(B$2:B$7,B$2:B$7)))+1。同時(shí)按ctrl+shift+enter，然后下拉即可完成。

函數(shù)解釋

= COUNTIF(B$2:B$7,B$2:B$7) 表示的是分?jǐn)?shù)出現(xiàn)的次數(shù)，和=COUNTIF($B$1：$B$7，B1)表達(dá)意思是一樣的。例如，92分出現(xiàn)的是2次，因此結(jié)果是2;其他分?jǐn)?shù)出現(xiàn)的是1次，因此，返回的結(jié)果是1。

在if中，數(shù)組公式 B$2:B$7>B2 表示的是由TRUE和FALSE組成的邏輯數(shù)組{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE｝轉(zhuǎn)化為1和0的數(shù)組{0;0;0;0;0;0｝。

因此G2單元格中的公式，=SUM(–IF(B$2:B$7>B2,1/COUNTIF(B$2:B$7,B$2:B$7)))+1是對(duì)0+0+0+0+0+0+1=1

因此G3單元格中的公式，=SUM(–IF(B$2:B$7>B3,1/COUNTIF(B$2:B$7,B$2:B$7)))+1 即 1/2+0+0+0+0+1/2+1=2

類推即可。

方法三：MATCH + ROW + IF 函數(shù)

=SUM(–IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1))。同時(shí)按ctrl+shift+enter，然后下拉即可完成。

公式解讀

B$2:B$7>=B2，表示的是數(shù)組{92; 91; 89;80;82;92}>=92, 表示的是由TRUE和FALSE組成的邏輯數(shù)組{TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}

MATCH(B$2:B$7,B$2:B$7,)，表示的是數(shù)字第一次出現(xiàn)的行數(shù)，因此表示的數(shù)組為{1;2;3;4;5;1}

ROW($2:$7)-1 表示的是{2;3;4;5;6;7}-1= {1;2;3;4;5;6}

因此， IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1) 表示為

IF({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{1;2;3;4;5;1}={1;2;3;4;5;6}) =IF({TRUE;FALSE;FALSE;FALSE;FALSE;TRUE},{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE})

注意：true表示數(shù)值1，false表示數(shù)值為0

因此=SUM(–IF(B$2:B$7>=B2,MATCH(B$2:B$7,B$2:B$7,)=ROW($2:$7)-1)) = 1

-02- 非中國式排名——rank函數(shù)

可以發(fā)現(xiàn)這個(gè)函數(shù)最簡單，雷哥就不多解釋。

=RANK (B2, B:B)

各位朋友，你看懂了嗎?

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。