Excel 的排名函數 RANK 屬於美式排名,當出現兩人並列第一名時就不存在第二名。而我們常用另一種排名法,即忽略重複值進行排名,當兩人並列第一名時,第三高成績以第二名處理,而不是第三名,這屬於中式排名。


第 4 章\217.xlsx


1. 要對工作表中 10 人的成績進行中式排名。

開啟範例檔案中的資料檔案,在 D2 儲存格輸入以下陣列公式:
=SUM(IF(B$2:B$11>B2,1/COUNTIF(B$2:B$11,B$2:B$11)))+1


按下(Ctrl)+(Shift)+(Enter)複合鍵後,公式將算出第一個學生成績的排名。按兩下儲存格的填滿控點,將公式向下填滿,結果如圖 4.34 所示。
 

3.jpg

圖 4.34 中式排名


2. 公式說明
本例公式首先利用 IF 函數排除小於等於第一個成績的資料,僅僅對大於第一個成績的資料計算個數。而此個數是忽略重複值的。也就是說某個大於 B2 儲存格的值出現多次時,僅計算一次,只要統計出大於 B2 的資料個數,那麼 B2 在範圍忽略重複值的排名只需再加 1 即可。


3. 使用注意
① 本例公式是範例 216 中公式的變體,範例 216 的公式可以計算忽略重複值的資料個數,而本例公式則計算忽略重複值的排名。
② 本例公式僅適用於對範圍中的所有值執行中式排名, 對於常數陣列如“{1;5;7;5;4;1;2}”則無法完成排序。
③ 本例公式也可以改用如下方式:
=SUM(IF(B$2:B$11>=B2,1/COUNTIF(B$2:B$11,B$2:B$11)))


4. 範例延伸
思考:計算中式排名方式下第二名成績是多少。
提示:用 IF 函數和 MAX 函數,從小於最大值的資料中取最大值。


 

 

 

本文同步刊載於>>

Excel職場函數468招:超完整!新人工作就要用到的計算函數+公式範例集
 

內容涵蓋財務、人事、業務、銷售、倉儲、教育等行業的實例為主,以函數語法剖析為輔,示範 468 個函數的實用範例!
內容由淺入深,每篇範例都是獨立的應用,可依需求查詢及學習!
 


歡迎加入 PCuSER 密技爆料粉絲團

 

, , , , , , , ,

PCuSER 發表在 痞客邦 PIXNET 留言(0) 人氣()