如何在excel中對學(xué)生成績(jì)進(jìn)行排名？
我用中國式排名為例來(lái)說(shuō)明吧，我們先來(lái)了解一下什么叫中國式排名？我就舉個(gè)最簡(jiǎn)單的例子。
比如，你們班級一共 50 個(gè)人，期末考試有 49 個(gè)人考了 100 分，你考了 90 分，那么按國際慣用的排名法則：49 個(gè)人并列第一，你第 50 名。如果按中國式排名：49 個(gè)人并列第一，你第 2 名。是不是瞬間和諧了許多？
既然中國式排名是中國特色，那么，Excel 現有的 rank 函數顯然是按老美的規則來(lái)排名的，如果要統計中國式排名，就得用到些技巧。
本文教大家 4 種方法，由淺入深、一網(wǎng)打盡，總有一款適合你。
案例：
下表的高考總分，分別有兩個(gè)分數出現重復，請用中國式排名給這些學(xué)生排名次。
解決方案：
用 if 排名用 vlookup 排名用 sumproduct（sum 家族的函數都可以，比如 sum+if，或者 sumif，本文以神級函數 sumproduct 舉例）+ countif 排名用數據透視表排名方案1：用 if 排名
1. 開(kāi)始中國式排名前，我們首先來(lái)看一下普通排名，可以用 rank 函數，也可以用如下 sumproduct 公式：
=SUMPRODUCT((C2$C$2:$C$15)*1)+1
公式釋義：
sumproduct 作為神級函數，我在各種案例中多次、反復講解過(guò)，比較完整的可參見(jiàn) Excel函數(四) – sumproduct函數計數、排名、求和等等本公式中，C2$C$2:$C$15：用 C2，即當前行的總分與一整列的分數依次比較，判斷 C2 是否比別人小根據判斷結果會(huì )得到一個(gè)由 true 和 false 組成的數組，true 相當于 1，false 相當于 0用 F9 看一下計算結果（如下圖），就非常直觀(guān)容易理解
SUMPRODUCT((C2$C$2:$C$15)*1)：sumproduct 是積求和函數，因此會(huì )用數組中的 1 和 0 依次與 1 相乘，最后求和，也就是說(shuō)，有幾個(gè)總分比自己高的，就得出幾+1：如果有 5 個(gè)比自己高，那么自己排名第 6，所以要 +12. 現在開(kāi)始中國式排名步驟，先按 C 列的高考分數由高到低排序
3. 在 E2 列輸入“1”，在 E3 列輸入以下公式，下拉即可：
=IF(C3=C2,E2,E2+1)
公式釋義：
如果上下兩行分數相等，則排名相同如果分數不等，那么不管上一行的分數有沒(méi)有重復值，排名 +1
方案2：用 vlookup 排名
1. 按 C 列的高考分數由高到低排序
2. 將 C 列復制粘貼到旁邊的輔助列，比如 J -- 選中 J 列 -- 選擇菜單欄的 Data -- Remove Duplicates -- 在彈出的對話(huà)框中勾選“高考分數”-- OK
3. 現在 J 列是去除重復項的分數
4. 在 K 列用 rank 函數對 J 列排名，公式如下：
=RANK(J2,$J$2:$J$12)
5. 在 F2 輸入以下公式，下拉即可，目的是用 vlookup 函數去查找 C 列的分數所對應的 K 列去重后的排名，即中國式排名：
=vlookup(C2,J:K,2,0)
方案3：用 sumproduct + countif 排名
1. 在 E2 單元格輸入以下公式，下拉即可：
=SUMPRODUCT((C2$C$2:$C$15)*(1/COUNTIF($C$2:$C$15,$C$2:$C$15)))+1
公式釋義：
* 前面部分之前解釋過(guò)了，不贅述重點(diǎn)來(lái)看這一段 COUNTIF($C$2:$C$15,$C$2:$C$15)：Countif 統計數組中每個(gè)數的重復次數，即先用 C2 歷遍整個(gè)數組，得到重復次數；再用 C3 歷遍整個(gè)數組，得到重復次數……依次類(lèi)推1/COUNTIF($C$2:$C$15,$C$2:$C$15)：用 1 除以數組中每個(gè)數的重復次數，等到一組最大值為 1 的數組當總分重復 n 次時(shí)，通過(guò) 1/n，把重復次數拆分成了 n 等分下圖是用 F9 查看這段公式后顯示的值，便于大家理解
SUMPRODUCT((C2$C$2:$C$15)*(1/COUNTIF($C$2:$C$15,$C$2:$C$15)))：就是用一組 1 和 0 組成的數組，與一組 1 和小數組成的數組積求和0 * 任何數為 0，忽略不計；1 * 1 也好理解當第二個(gè)值為分數時(shí)，比如1/3（上圖中顯示為0.33333333……），表示重復了 3 次，而且 1/3 會(huì )在數組中出現三次，sumproduct 對它積求和即1*1/3+1*1/3+1*1/3=1，也就是不管重復幾次，最后結果都只統計 1 次因此實(shí)現了中國式排名的邏輯
+1：比自己大的個(gè)數 +1，即自己的排名方案4：用數據透視表排名
1. 制作數據透視表：點(diǎn)擊表格中的任意單元格 -- 選擇菜單欄的 Insert -- PivotTable
2. 默認將數據透視表放入一個(gè)新 sheet -- 在右邊的對話(huà)框中將“姓名”拖動(dòng)到 Rows 區域 -- 將“高考總分”拖動(dòng)到 Values 區域，拖動(dòng)兩次：一列用來(lái)顯示總分，另一列用來(lái)顯示排名
3. 回到數據透視表，隨意選中第 2 個(gè)“高考總分”列的任意單元格 -- 右鍵單擊 -- 選擇 Show Values As -- Rank Largest to Smallest
4. 在彈出的對話(huà)框中點(diǎn)擊 OK
5. 現在 C 列已經(jīng)變成了中國式排名了
6. 我們再對 C 列排下序：選中 C 列的任意單元格 -- 右鍵單擊 -- 選擇 Sort -- Sort Largest to Smallest
7. 然后把 C 列的標題改成“排名”，就完成了
