學不完．教不停．用不盡

有網友問到：在 Excel 中有一個資料清單，如下圖，如果想要在每種相同商品中找出最高／最低報價及其廠商，該如何處理？

在下圖中，有甲、乙、丙、丁、戊等不同廠商，分別有A、B、C、D、E等不同商品，現在要來找出各種商品的最高／最低報價和所屬廠商。

 

【公式設計與解析】

選取儲存格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。