2017年9月17日 星期日

Excel-找出間隔欄位資料的最大值(COLUMN,OFFSET,MATCH,陣列公式)

參考下圖,網友問到如何在 Excel 的工作表中找出間隔欄位資料的最大值?
例如:在蘋果那一列找出最大的公斤數(500),並且求得最大公斤數對應的價格(24)。
Excel-找出間隔欄位資料的最大值(COLUMN,OFFSET,MATCH,陣列公式)

【公式計與解析】
1. 找出最大公斤數
儲存格C8:{=MAX((MOD(COLUMN(C3:K3),2)=1)*(C3:K3))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加「{}」。
複製儲存格C8,貼至儲存格C8:C9。
(1) MOD(COLUMN(C3:K3),2)=1
利用 COLUMN 函數來取得儲存格C3:K3範圍中每一個儲存格的欄數。再利用 MOD 函數其每個欄數除以 2 的餘數,若餘數為『1』,表示為 C, E, G, I, K 欄。
(2) (MOD(COLUMN(C3:K3),2)=1)*(C3:K3))
取得 C, E, G, I, K 欄的公斤數。
(3) MAX((MOD(COLUMN(C3:K3),2)=1)*(C3:K3))
將取得 C, E, G, I, K 欄的公斤數,利用 MAX 函數求得最大值。

2. 找出最大公斤數對應的價格
儲存格B8:=OFFSET(B3,0,MATCH(C8,C3:K3,0)-1)
複製儲存格B8,貼至儲存格B8:BC9。
(1) MATCH(C8,C3:K3,0)
利用 MATCH 函數求得儲存格C8的內容在儲存格C3:K3範圍裡的位置,傳回一個數值。
(2) OFFSET(B3,0,MATCH(C8,C3:K3,0)-1)
利用 OFFSET 函數代入第(1)式傳回的數值,找出對應的儲存格內容。

沒有留言:

張貼留言

檢視其他文章

好康東東