2016年2月17日 星期三

Excel-在資料清單查詢相同項目的最大值和最小值(OFFSET,MAX,MIN)

有網友問到:在 Excel 中有一個資料清單,如下圖,如果想要在每種相同商品中找出最高/最低報價及其廠商,該如何處理?
在下圖中,有甲、乙、丙、丁、戊等不同廠商,分別有A、B、C、D、E等不同商品,現在要來找出各種商品的最高/最低報價和所屬廠商。
Excel-在資料清單查詢相同項目的最大值和最小值(OFFSET,MAX,MIN,陣列公式)

【公式設計與解析】
選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:廠商、商品、報價。
1. 最高報價的報價/儲存格H2:
{=MAX(IF(商品=E2,報價,FALSE))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
在陣列公式中,判斷商品陣列和儲存格E2相同者,傳回其報價。再透過 MAX 函數傳回最大值。例如:儲存格E2為『A』,則會傳回 A 中報價的最大值。

2. 最高報價的廠商/儲存格G2:
{=OFFSET($A$1,MAX(IF((商品=E2)*(報價=H2),ROW(廠商),FALSE))-1,)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
IF((商品=E2)*(報價=H2),ROW(廠商),FALSE):符合雙條件『商品=E2』和『報價=H2』者,傳回廠商所在列號。
再透過 OFFSET 函數取出列號對應的儲存格內容。

同理:
3. 最低報價的報價/儲存格H3:
{=MIN(IF(商品=E3,報價,FALSE))}
4. 最低報價的廠商/儲存格G3:
{=OFFSET($A$1,MIN(IF((商品=E3)*(報價=H3),ROW(廠商),FALSE))-1,)}

最後,複製儲存格H2:G3,貼至儲存格H2:H11。

沒有留言:

張貼留言

好康東東