讀者問到 Excel 關於排序的問題:在下圖中有一個成績表,想要製作排名,規則如下:

1. 根據淨桿排名次。

2. 數值小者,名次小。

3. 數值相同者,名次相同。

4. 無資料者不列入排序。

該如何設計公式?

Excel-名次不重覆且數值小者排名小(SUMPRODUCT,COUNTIF)

 

【公式設計與解析】

儲存格F2:=IF(E2<>"",SUMPRODUCT((E$2:E$11<E2)*(1/COUNTIF(E$2:E$11,
E$2:E$11)))+1,"")

複製儲存格F2,貼至儲存格F2:F11。

 

(1) 1/COUNTIF(E$2:E$11,E$2:E$11)

SUMPRODUCT 函數中計算陣列(E2:E11)中每個數值的重覆次數之倒數。如下圖。

數值相同者(例如:73),則其公式結果有三個0.333333333,即此三個數加總後會得到『1』。也就是三個 73,只被列入排名一次,可以達到名次不重覆的效果。

Excel-名次不重覆且數值小者排名小(SUMPRODUCT,COUNTIF)

 

(2) SUMPRODUCT((E$2:E$11<E2)*(第(1)式))+1

因題意是要『數值小者,名次小』,因此在 SUMPRODUCT 函數中加入一個條件:E$2:E$11<E2,即在每個儲存格計算小於儲存格本身的個數再加上 1,如此可以得到儲存格E2的排名結果。

 

(3) IF(E2<>"",第(2)式,"")

因題意要『無資料者不列入排序』,因此只把非空白的儲存格列入計算。即若E2<>"",再執行 SUMPRODUCT 函數運算。

arrow
arrow
    全站熱搜

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