贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

讀者問到 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 函數運算。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 謝小益
  • 老師您好:
    請問如果E$2:E$11中有合併儲存格時,會出現Div/0!的錯誤訊息,
    即分母不可為0的錯誤,那請問該如何解決呢?謝謝

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼