這個主題曾經發表在另外一個部落格中，不過有讀者詢問過一些進階的處理方法，所以整理了文章內容，重新發布到這個專門發表Excel相關文章的部落格中。
Excel中預設提供的排名函數RANK.EQ，只能針對一個欄位的內容值來進行排名。若要針對多個欄位進行排名，RANK.EQ就力有未逮了。網路上有些文章建議可以用巨集處理多欄位的排名，但是其實Excel本身所提供的函數已經強大到足以達成這樣的需求，以下以實例來說明如何做到多欄位的成績排名。
 |
| 原始資料 |
如畫面所顯示，這張成績單共有5個人的成績記錄，其中三個人的總分相同，都是260分。若是按照總分排名，這三個人會並列第一，而剩下的兩個人則會分居第四、第五名。
 |
| RANK.EQ |
若採用Excel內建的排名函數RANK.EQ比較總分欄位，三個總分為260分的人會並列第一，250分與170分的人則分居第四、第五名。該函數的語法如下：RANK.EQ(number,ref,[order])，所以如範例畫面中一樣，鍵入RANK.EQ(F2,$F$2:$F$6)，就能得到王小明的成績排名，該計算式的意思是看看F2在F2到F6的範圍內排第幾名，至於其他人的排名如法炮製即可取得。
 |
| COUNTIF |
若不採用RANK.EQ也可以用COUNTIF函數來進行排名，得到的結果會是一樣的。
該函數的語法如下：COUNTIF(range, criteria)，所以如範例畫面中一樣，鍵入COUNTIF($F$2:$F$6,">"&F2)+1，就能得到王小明的成績排名。該計算式的意思是看看F2到F6的範圍內有多少個比F2更大的值，取得該值後再加一就是王小明的名次，至於其他人的排名如法炮製即可取得。
另外，補充一下，COUNTIF的條件參數必須輸入字串，所以「">"&F2」才是正確的，若輸入「>F2」會出現錯誤訊息。
 |
| 總分相同時比英文成績 |
在現實生活中，總分相同時往往仍會要分出一個高下（升學主義作祟？大家都第一名不是皆大歡喜嗎😆），一般常見到的作法就是再用一科或數科主科的成績來進行比較，這時無論RANK.EQ或COUNTIF都派不上用場了。若要做到多欄位比較排名，則要搭配另一個功能超強的函數SUMPRODUCT，才有辦法實作出上述功能。
該函數的語法如下：SUMPRODUCT(array1, [array2], [array3], ...)，說明中的功能描述是「傳回指定陣列中所有對應元素乘積的總和」。舉個例子，Excel中有兩個陣列如下：
 |
| SUMPRODUCT用法 |
若鍵入SUMPRODUCT(A2:B4,C2:D4)即可得到兩個陣列中所有對應元素乘積的加總，也就是3x2 + 4x7 + 8x6 + 6x7 + 1x5 + 9x3 (156)。所以如範例畫面中一樣，鍵入(COUNTIF($F$2:$F$6,">"&F2)+1)+SUMPRODUCT(--($F$2:$F$6=F2),--($B$2:$B$6>B2))，就能得到王小明的成績排名。
補充一下，--($F$2:$F$6=F2)前面的「--」作用是進行Boolean值的轉換，將TRUE轉成1，FALSE轉成0。
該計算式的的說明如下：
先用COUNTIF($F$2:$F$6,">"&F2)+1取得F2的所在名次，然後再用SUMPRODUCT(--($F$2:$F$6=F2),--($B$2:$B$6>B2))找出到底有幾個總分與王小明相同，但是英文成績比王小明高的人，將兩者相加之後就是王小明的名次了，至於其他人的排名也是如法炮製即可取得。
之前有網友詢問，若英文成績也相同，還想用國文成績來比較該怎麼做。其實也就是一樣的作法，再多加一個SUMPRODUCT進去就搞定了。
一樣先用COUNTIF($F$2:$F$6,">"&F2)+1取得F2的所在名次，然後再用SUMPRODUCT(--($F$2:$F$6=F2),--($B$2:$B$6>B2))找出到底有幾個總分與王小明相同，但是英文成績比王小明高的人。但是因為要比較國文的名次，所以還要用SUMPRODUCT(--($F$2:$F$6=F2),--($B$2:$B$6=B2),--($C$2:$C$6>C2))找出到底有幾個總分和英文成績與王小明相同，但是國文成績比王小明高的人，最後將三者相加就是王小明的名次。至於其他人的排名同樣也是如法炮製即可取得。
 |
| 總分與英文成績相同時比國文成績 |
留言
張貼留言