2016年12月14日 星期三

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

讀者問到 Excel 關於排序的問題:在下圖中有一個成績表,想要製作排名,規則如下:
1. 根據淨桿排名次。
2. 數值小者,名次小。
3. 數值相同者,名次相同。
4. 無資料者不列入排序。
該如何設計公式?
Excel-名次不重覆且數值小者排名小(SUMPRODUCT,COUNTIF)

【公式設計與解析】
儲存格F2:=IF(E2<>"",SUMPRODUCT((E$2:E$11E$2:E$11)))+1,"")
複製儲存格F2,貼至儲存格F2:F11。

(1) 1/COUNTIF(E$2:E$11,E$2:E$11)
在 SUMPRODUCT 函數中計算陣列(E2:E11)中每個數值的重覆次數之倒數。如下圖。
Excel-名次不重覆且數值小者排名小(SUMPRODUCT,COUNTIF)

(2) SUMPRODUCT((E$2:E$11
因題意是要『數值小者,名次小』,因此在 SUMPRODUCT 函數中加入一個條件:E$2:E$11

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

沒有留言:

張貼留言

檢視其他文章

好康東東