2017年11月28日 星期二

Excel-對符合條件者排序(SUMPRODUCT)

(網友提問)當你在 Excel 的工作表中對一個成績清冊的某個欄位排序時,如果想要只對某些符合條件的資料來排序,該如何處理?
如下圖,若本例設定只顯示英文超過 88 者的排名,並且標示前三名。
Excel-對符合條件者排序(SUMPRODUCT)

【公式設計與解析】
先選取儲存格B1:E26,按Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:國文、英文、數學、總分。
1. 計算原排名
儲存格F2:=RANK(E2,總分)
複製儲存格F2,貼至儲存格F2:F26。
2. 計算符合條件的排名
儲存格E2:=IF(C2>85,SUMPRODUCT((英文>88)*(總分>E2))+1,"")
利用雙條件:(英文>88)*(總分>E2),讓 SUMPRODUCT 函數計算由第一個儲存格(E2)開始至每個儲存格的範圍中,大於本身儲存格的個數。(模擬 RANK 功能)
3. 將前3名標示星號
若要將前3名標示星號,則在設定好上述公式後,再透過設定格式化的條件來處理。
勾選「只顯示圖示」。(表示不顯示數值)
設定 >=4 時不顯示圖示,否則顯示星號。
Excel-對符合條件者排序(SUMPRODUCT)

沒有留言:

張貼留言

檢視其他文章

好康東東