贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到:如下圖的 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。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • mir
  • 請問如何在第一欄(條件)輸入文字或數字,後面欄位出現對應數字或文字(結果)?
    例如,A1輸入1,B1出現100,C1出現50,D1出現music,E1出現很好
    謝謝...
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼