贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友提問:在 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 的頭像
vincent

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 訪客
  • 老師您好,

    聖誕快樂!新年進步!身體健康!

    不好意思, 又要請您幫忙, 想請教您有關EXCEL的問題!

    我想做一個自己股票清單, 如在 A 欄 下輸入數字 “5”, 電腦自動變為 “0005.HK”, 然後電腦去 “https://hk.finance.yahoo.com/quote/0005.HK” 找數據並把適當資料顯示在 B 與 C 欄中, 最後D 欄 會自動計算百分比.


    A欄 B欄 C欄 D欄
    代號 前收市價 現價 升跌 (%)
    0005.HK 63.700 63.650 -0.08%
    2800.HK 26.200 25.800 -1.53%

    代號 – 股票號碼為4位數與 ”.HK” 組成
    前收市價 – 在圖表之下可找到
    現價 – 即時股格, 在公司名稱之下可找到
    升跌 (%) – 計算公式為 “(現價 - 前收市價) / 前收市價” 之百分比

    謝謝您的幫忙!!

    Ralph
    ralph_lam@msn.com

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼