學不完．教不停．用不盡

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

參考下圖，給予A值範圍（例如：40～60），找出符合的A值所對應的B值，要求取B值中的最大值。

 

【公式設計與解析】

選取儲存格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，往下各列貼上。