網友提問:參考下圖,在 Excel 的資料表中,如何查得最低價廠商的名稱?

注意:其中有些價格內容並不是數字(必須排除)。

Excel-查詢數列最小值對應的欄位名稱(MIN,MATCH,OFFSET)

 

【公式設計與解析】

本例要用到陣列公式。

1. 計算最低價

儲存格F2:{=MIN(IF(ISNUMBER(B2:E2),B2:E2,""))}

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

複製儲存格F2,貼至儲存格F2:F5。

(1) ISNUMBER(B2:E2)

由於價格中有些儲存格的內容不是數字,所以利用 ISNUMBER 函數檢查儲存格B2:E2中那些是數字。結果會傳回 TRUE/FALSE

(2) IF(ISNUMBER(B2:E2),B2:E2,"")

在陣列公式中判斷如果儲存格B2:E2中是數字者,傳回對應的儲存格內容,否則,傳回空白(空字串)。

(3) MIN(IF(ISNUMBER(B2:E2),B2:E2,""))

最後,利用 MIN 函數求得是數字的儲存格內容的最小值。

 

2. 查詢最低價的廠商名稱

儲存格G2:

{=OFFSET($A$1,0,MATCH(MIN(IF(ISNUMBER(B2:E2),B2:E2,"")),B2:E2,0))}

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

複製儲存格G2,貼至儲存格G2:G5。

(1) MIN(IF(ISNUMBER(B2:E2),B2:E2,"")),B2:E2,0))

計算廠商中的最低價為多少。

(2) MATCH(MIN(IF(ISNUMBER(B2:E2),B2:E2,"")),B2:E2,0)

利用 MATCH 函數傳回第(1)式中的最低價為儲存格B2:E2的第幾個(傳回一個數字)。

(3) OFFSET($A$1,0,MATCH(MIN(IF(ISNUMBER(B2:E2),B2:E2,"")),B2:E2,0))

將第(2)式傳回的位置代入 OFFSET 函數求得對應儲存格(B1:E1)的內容(相對儲存格A1的位置)。

學不完.教不停.用不盡文章列表

    文章標籤

    MIN MATCH OFFSET

    全站熱搜

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