2016年4月15日 星期五

Excel-在資料清單中查詢同項目的最大值(陣列公式)

網友想要在一個 Excel 的資料清單中查詢同項目的最大值,該如何處理?
在下圖中A欄是項目的清單,B欄是對應的數值,例如:要在項目中找出A的最大值,或是在項目中找出B的第2小值。
Excel-在資料清單中查詢同項目的最大值(陣列公式)

【公式設計與解析】
選取儲存格A1:B25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、名稱。

(1) 查詢各項的數值最大值
儲存格E2:{=MAX(IF(項目=D2,數值,FALSE))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格E2,貼至儲存格E2:E6。
IF(項目=D2,數值,FALSE):在陣列公式中判斷項目陣列和儲存格D2相同者,傳回對應的數值陣列,否則傳回 FALSE
最後再透過 MAX 函數取得上述陣列中的最大值。

(2) 查詢各項的數值第2小值
儲存格F2:{=SMALL(IF(項目=D2,數值,FALSE),2)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格F2,貼至儲存格F2:F6。
原理同(1),最後利用 SMALL 函數取得傳回陣列的第2小值。

沒有留言:

張貼留言

好康東東