2016年2月20日 星期六

Excel-6種計算名次的方法(RANK,SUMPRODUCT,COUNTIF)

開學之初而已,就有同仁問到在 Excel 中,關於名次計算的問題。參考下圖,一般在計算名次時,可能會有這樣的狀況:
1. 名次重覆跳過:例如有 2 個第 4 名,所以沒有第 5 名。
2. 名次重覆不跳過:例如有 2 個第 4 名,原來的第 6 名成為第 5 名。
3. 名次不重覆:例如有 2 個第 4 名,會隨機產生一個第 4 名,另一個為第 5 名。
Excel-6種計算名次的方法(RANK,SUMPRODUCT,COUNTIF)

【公式設計與解析】
依上圖,選取儲存格A1:A24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。使用相同方法,將E欄中有資料的儲存格,定義名稱:輔助欄。

1. 名次重覆跳過
儲存格B2:=RANK(A2,數值)
使用 RANK 函數是最通用的計算排名工具。再提供以下三種方式,都能達到相同的排名結果。
(1) 儲存格B2:=COUNTIF(數值,">"&A2)+1
使用 COUNTIF 函數計算在數值陣列中,大於儲存格A2的有幾個,然後再加 1。
(2) 儲存格B2:=SUMPRODUCT(--(數值>A2))+1
在 SUMPRODUCT 函數中找出數值是否大於儲存格A2的 TRUE/FALSE 陣列,利用『--』運算,將 TRUE/FALSE 陣列轉換為 1/0 陣列,再由 SUMPRODUCT 函數執行『乘積和』運算。最後再加 1,即為所求。
(3) 儲存格B2:{=SUM(IF(數值>A2,1,0))+1}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
SUM(IF(數值>A2,1,0)) 的計算原理同 SUMPRODUCT(--(數值>A2))。

2. 名次重覆不跳過
儲存格C2:=SUMPRODUCT((數值>A2)*(1/COUNTIF(數值,數值)))+1
1/COUNTIF(數值,數值):藉由 COUNTIF 函數,找出每個名次出現的次數,1/COUNTIF 的用意是將計算後的次數加以倒數,例如:某一名次有 3 人,則 1/COUNTIF 為 0.333333,某一名次有 4 人,則 1/COUNTIF 為 0.25。透過SUMPRODUCT 函數加總運算後,結果均會為 1。
(數值>A2)*(1/COUNTIF(數值,數值)):找出大於儲存格A2者,再運算 1/COUNTIF 的部分。其中『*』運算,相當於執行邏輯 AND 運算。

3. 名次不重覆
儲存格E2:=A2+RAND()
儲存格D2:=RANK(E2,輔助欄)
藉由輔助欄位將A欄中的每個儲存格加上一個亂數(介於0和1之間的數),然後再置入 RANK 函數中加以排序,如此得到的結果,會是隨機讓同名次者,分出大小。

沒有留言:

張貼留言

好康東東