Excel | 同時(shí)滿足兩個(gè)區(qū)間的返回值問(wèn)題

老師自己的書(shū)，獲評(píng)國(guó)家十三五規(guī)劃教材：

　　該書(shū)共分為4個(gè)部分，從數(shù)據(jù)采集與整理，到數(shù)據(jù)統(tǒng)計(jì)分析，再到數(shù)據(jù)可視化，并輔以常用函數(shù)與公式的使用詳解，基本涵蓋了Excel使用中的數(shù)據(jù)輸入與規(guī)范、數(shù)據(jù)查找統(tǒng)計(jì)、數(shù)據(jù)條件輸出與分析等重要操作實(shí)用技能。
　　該書(shū)可作為行業(yè)白領(lǐng)數(shù)據(jù)處理與分析的參考用書(shū)?............","author":["韓春玲"],"publisher":"電子工業(yè)出版社"},"appuin":"3208869061","isNewCpsKOL":0}">

問(wèn)題

粉絲朋友的問(wèn)題：

"想在excel中實(shí)現(xiàn)雙區(qū)間查找，然后返回所對(duì)應(yīng)的值，如區(qū)間A在300-400， 區(qū)間B90-100， 那么返回81，請(qǐng)問(wèn)如何可以實(shí)現(xiàn)?" 如下圖：

解決方法

初解：

因粉絲朋友沒(méi)有給我返回值區(qū)域，所以韓老師自己寫了一個(gè)數(shù)據(jù)，返回該數(shù)據(jù)對(duì)應(yīng)的區(qū)間A與區(qū)間B的交叉值，如下：

使用的公式如下：

再解：

但韓老師寫的同一個(gè)數(shù)值對(duì)應(yīng)兩個(gè)區(qū)間交叉點(diǎn)的值，不滿足粉絲朋友的需求，所以有再問(wèn)：

“但是我的表格是兩個(gè)區(qū)間A和B， 有兩個(gè)不同的參數(shù)，如分別對(duì)應(yīng)不同的區(qū)間300-400， 區(qū)間90-100， 首先定義在不同的區(qū)間，然后再返回交叉點(diǎn)的值。”

韓老師提供的解決方法，如下圖：

使用的公式如下：

=INDEX($D$4:$I$11,MATCH(LOOKUP($B$16,$B$4:$B$11),$B$4:$B$11,0),MATCH(LOOKUP($C$16,$D$2:$I$2),$D$2:$I$2,0))

其實(shí)，兩個(gè)數(shù)，每個(gè)數(shù)各自對(duì)應(yīng)一個(gè)區(qū)間反而容易解決。

三解：

此時(shí)，朋友又遇到一個(gè)問(wèn)題：

”如果當(dāng)數(shù)值分別為95 和 720， 超出了表中的范圍，這時(shí)候仍會(huì)顯示結(jié)果，沒(méi)有提示有誤，這種情況有解嗎？“

韓老師更新公式為：

如果數(shù)據(jù)超出區(qū)間范圍，效果如下：

如果數(shù)據(jù)在區(qū)間范圍內(nèi)，效果如下：

公式解析

其實(shí)完成這一系列問(wèn)題的關(guān)鍵點(diǎn)是：

利用了LOOKUP函數(shù)返回區(qū)間下限值的性質(zhì)。比如，如果查找數(shù)據(jù)85，在區(qū)間80-90之間查找，沒(méi)有85這個(gè)確切的值，那么LOOKUP函數(shù)返回區(qū)間下限80。

LOOKUP 函數(shù)用法請(qǐng)參考：

LOOKUP($B$16,$B$4:$B$11)：在$B$4:$B$11中查找$B$16，返回最接近$B$16的比$B$16小的值。

MATCH(LOOKUP($C$17,$B$4:$B$12),$B$4:$B$12,0)：返回$B$16所在區(qū)間的行。

INDEX($D$4:$I$11,MATCH(LOOKUP($B$16,$B$4:$B$11),$B$4:$B$11,0),MATCH(LOOKUP($C$16,$D$2:$I$2),$D$2:$I$2,0)))：返回$B$16所在區(qū)間行與$C$16所有區(qū)間列交叉處的值。

INDEX+MATCH 函數(shù)用法請(qǐng)參考：