網友問到:如下圖的 Excel 資料清單中,如何能找出不同料號的最低價廠商?

下圖中的基本資料有:廠商名稱、料號和單價,而單價有可能不同廠商相同單價。在本例中如有相同單價的廠商,則僅列出第一個廠商。

Excel-在資料清單中找出不同料號的最低價廠商(OFFSET,MIN,陣列公式)

 

【公式設計與解析】

(1) 為了便於說明,先設定儲存格範圍名稱:

選取儲存格A1:C24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:

廠商、料號、單價。

 

(2) 找出各料號的最低價

儲存格F2:{=MIN(IF(料號=E2,單價,FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」。

判斷在料號陣列中和儲存格E2相同者,使用 MIN 函數得到其中單價的最小值。

 

(3) 找出各料號的最低價在第幾列

儲存格G2:{=MAX((料號=E2)*(單價=F2)*ROW(廠商))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」。

(料號=E2)*(單價=F2):利用雙條件判斷「料號和儲存格E2相同『且』單價和儲存格F2相同者」,傳回 TRUE/FALSE 陣列。

將上式傳回的陣列再和ROW(廠商)相乘,會傳回符合的列號。但是,因為可能會傳回一個以上的列號,所以透過 MAX 函數只取其中一個。

 

(4) 找出各料號的最低價廠商

儲存格H2:=OFFSET($A$1,G2-1,0,1,1)

最後使用 OFFSET 函數在廠商儲存格陣列中根據第(3)式傳回的列號取得對應的內容。

複製儲存格H2,貼至儲存格H2:H6。

 

(5)整合以上公式

儲存格H2:

{=OFFSET($A$1,SUM((料號=E2)*(單價=MIN(IF(料號=E2,單價,FALSE)))*ROW
(廠商))-1,0,1,1)}

複製儲存格H2,貼至儲存格H2:H6。

arrow
arrow
    全站熱搜

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