學不完．教不停．用不盡

開學之初而已，就有同仁問到在 Excel 中，關於名次計算的問題。參考下圖，一般在計算名次時，可能會有這樣的狀況：

１. 名次重覆跳過：例如有 2 個第 4 名，所以沒有第 5 名。

2. 名次重覆不跳過：例如有 2 個第 4 名，原來的第 6 名成為第 5 名。

3. 名次不重覆：例如有 2 個第 4 名，會隨機產生一個第 4 名，另一個為第 5 名。

 

【公式設計與解析】

依上圖，選取儲存格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 函數中加以排序，如此得到的結果，會是隨機讓同名次者，分出大小。