前二篇文章提到了在 Excel 中的排名問題:

Excel-使用RANK.EQ和RANK.AVG處理排名問題

Excel-重覆名次不跳過

如果現在要根據分組的人員分數,來求各組人員的名次,該如何處理?

Excel-計算分組的名次(SUMPRODUCT)

 

【公式設計與解析】

這個問題無法使用 RANK 函數來直接求取名次,但可以使用 SUMPRODUCT 函數來模擬名次。

儲存格D2:=SUMPRODUCT(($B$2:$B$21=B2)*($C$2:$C$21>C2))+1

公式中使用兩個條件:

$B$2:$B$21=B2:判斷在『組別』陣列中是否和儲存格B2相同,傳回 TRUE/FALSE 陣列。

$C$2:$C$21>C2:判斷在『分數』陣列中是否大於儲存格C2,傳回 TRUE/FALSE 陣列。

公式中的『*』運算,相當於執行兩個條件的邏輯 AND 運算。計算過程中會將 TRUE/FALSE 轉換為 1/0。

最後記得要將 SUMPRODUCT 函數傳回值再加 1,意思是例如大於自己的有 2 個,自己是第 3 名。該公式的結果相當於使用 RANK.EQ 函數的運算結果。

arrow
arrow
    全站熱搜

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