開始今天的教程前,我們先來了解一下什麼叫中國式排名?我就舉個最簡單的例子。
比如,你們班級一共 50 個人,期末考試有 49 個人考了 100 分,你考了 90 分,那麼按國際慣用的排名法則:49 個人并列第一,你第 50 名。如果按中國式排名:49 個人并列第一,你第 2 名。是不是瞬間和諧了許多?
既然中國式排名是中國特色,那麼,Excel 現有的 rank 函數顯然是按老美的規則來排名的,如果要統計中國式排名,就得用到些技巧。
本文教大家 4 種方法,由淺入深、一網打盡,總有一款适合你。
案例:
下表的高考總分,分别有兩個分數出現重複,請用中國式排名給這些學生排名次。
解決方案:
- 用 if 排名
- 用 vlookup 排名
- 用 sumproduct(sum 家族的函數都可以,比如 sum if,或者 sumif,本文以神級函數 sumproduct 舉例) countif 排名
- 用數據透視表排名
方案1:用 if 排名
1. 開始中國式排名前,我們首先來看一下普通排名,可以用 rank 函數,也可以用如下 sumproduct 公式:
=SUMPRODUCT((C2<$C$2:$C$15)*1) 1
公式釋義:
- sumproduct 作為神級函數,我在各種案例中多次、反複講解過,比較完整的可參見 Excel函數(四) – sumproduct函數計數、排名、求和等等
- 本公式中,C2<$C$2:$C$15:
- 用 C2,即當前行的總分與一整列的分數依次比較,判斷 C2 是否比别人小
- 根據判斷結果會得到一個由 true 和 false 組成的數組,true 相當于 1,false 相當于 0
- 用 F9 看一下計算結果(如下圖),就非常直觀容易理解
- SUMPRODUCT((C2<$C$2:$C$15)*1):sumproduct 是積求和函數,因此會用數組中的 1 和 0 依次與 1 相乘,最後求和,也就是說,有幾個總分比自己高的,就得出幾
- 1:如果有 5 個比自己高,那麼自己排名第 6,所以要 1
2. 現在開始中國式排名步驟,先按 C 列的高考分數由高到低排序
3. 在 E2 列輸入“1”,在 E3 列輸入以下公式,下拉即可:
=IF(C3=C2,E2,E2 1)
公式釋義:
- 如果上下兩行分數相等,則排名相同
- 如果分數不等,那麼不管上一行的分數有沒有重複值,排名 1
方案2:用 vlookup 排名
1. 按 C 列的高考分數由高到低排序
2. 将 C 列複制粘貼到旁邊的輔助列,比如 J --> 選中 J 列 --> 選擇菜單欄的 Data --> Remove Duplicates --> 在彈出的對話框中勾選“高考分數”--> 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
公式釋義:
- * 前面部分之前解釋過了,不贅述
- 重點來看這一段 COUNTIF($C$2:$C$15,$C$2:$C$15):Countif 統計數組中每個數的重複次數,即先用 C2 曆遍整個數組,得到重複次數;再用 C3 曆遍整個數組,得到重複次數……依次類推
- 1/COUNTIF($C$2:$C$15,$C$2:$C$15):
- 用 1 除以數組中每個數的重複次數,等到一組最大值為 1 的數組
- 當總分重複 n 次時,通過 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 也好理解
- 當第二個值為分數時,比如1/3(上圖中顯示為0.33333333……),表示重複了 3 次,而且 1/3 會在數組中出現三次,sumproduct 對它積求和即1*1/3 1*1/3 1*1/3=1,也就是不管重複幾次,最後結果都隻統計 1 次
- 因此實現了中國式排名的邏輯
- 1:比自己大的個數 1,即自己的排名
方案4:用數據透視表排名
1. 制作數據透視表:點擊表格中的任意單元格 --> 選擇菜單欄的 Insert --> PivotTable
2. 默認将數據透視表放入一個新 sheet --> 在右邊的對話框中将“姓名”拖動到 Rows 區域 --> 将“高考總分”拖動到 Values 區域,拖動兩次:一列用來顯示總分,另一列用來顯示排名
3. 回到數據透視表,随意選中第 2 個“高考總分”列的任意單元格 --> 右鍵單擊 --> 選擇 Show Values As --> Rank Largest to Smallest
4. 在彈出的對話框中點擊 OK
5. 現在 C 列已經變成了中國式排名了
6. 我們再對 C 列排下序:選中 C 列的任意單元格 --> 右鍵單擊 --> 選擇 Sort --> Sort Largest to Smallest
7. 然後把 C 列的标題改成“排名”,就完成了
,