參考下圖,網友問到如何在 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)式傳回的數值,找出對應的儲存格內容。

    文章標籤

    Excel COLUMN OFFSET MATCH

    全站熱搜

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