有網友問到:在 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。

arrow
arrow
    全站熱搜

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