2016年7月20日 星期三

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

網友問到:如下圖的 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)式傳回的列號取得對應的內容。

(5)整合以上公式
儲存格H2:
{=OFFSET($A$1,SUM((料號=E2)*(單價=MIN(IF(料號=E2,單價,FALSE)))*ROW
(廠商))-1,0,1,1)}

沒有留言:

張貼留言

好康東東