網友提問:在 Excel 的工作表中,有A值和B值二個數值清單,如果給予A值一段範圍,要找出符合的A值中,所對應的B值之最大值,該如何處理?

參考下圖,給予A值範圍(例如:40~60),找出符合的A值所對應的B值,要求取B值中的最大值。

Excel-指定清單數值範圍傳回另一欄對應數值的最大值

 

【公式設計與解析】

選取儲存格A1:B101(共有100筆資料),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A值、B值。

儲存格D4:{=MAX(IF((A值>=E1)*(A值<=G1),B值,""))}

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

(1) (A值>=E1)*(A值<=G1)

在陣列公式中設定二個條件:A值>=E1、A值<=G1,其中「*」運算子,相當於執行邏輯 AND 運算。

(2) IF((A值>=E1)*(A值<=G1),B值,"")

在陣列公式中,如果符二個條件者,傳回對應的B值,否則,傳回空字串。

(3) =MAX(IF((A值>=E1)*(A值<=G1),B值,""))

在陣列公式中,使用 MAX 函數取得第(2)式傳回B值中的最大值。

 

【延伸學習】

儲存格I1:{=IFERROR(OFFSET($A$1,SMALL(IF((A值>=$E$1)*(A值<=$G$1),
ROW(A值),""),ROW(1:1))-1,0),"")}

儲存格J1:{=IFERROR(OFFSET($B$1,SMALL(IF((A值>=$E$1)*(A值<=$G$1),
ROW(A值),""),ROW(1:1))-1,0),"")}

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

複製儲存格I1:J1,往下各列貼上。

文章標籤

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