編按：

在前三個(gè)回合的較量中，LOOKUP處于下風(fēng)。第四回合的比賽題目是“區(qū)間查詢”，簡單來說，就是判斷某個(gè)數(shù)值屬于哪個(gè)區(qū)間哪個(gè)等級。雖說VLOOKUP和LOOKUP都將利用二分法原理完成區(qū)間查詢，但二分法是LOOKUP唯一的、根本的內(nèi)功心法，LOOKUP能否憑此守得云開見月明呢？

“年少萬兜鍪，坐斷東南戰(zhàn)未休?！?span>VLOOKUP和LOOKUP的故事還在繼續(xù)，刀鋒未休！連續(xù)吃癟的LOOKUP試圖挽住頹勢，“二分法”千呼萬喚始出來，精彩馬上開始！

ROUND 04  區(qū)間查詢

在數(shù)值查詢中，我們經(jīng)常需要查找數(shù)值所對應(yīng)的區(qū)間。一個(gè)經(jīng)典的問題就是學(xué)生成績等級評定，0-60（不含60）為不及格，60-75（不含75）為及格，75-85（不含85）為良好，85以上為優(yōu)秀。面對這種問題，你是否還在用IF函數(shù)反復(fù)嵌套？

圖3.1：區(qū)間查詢——IF函數(shù)嵌套

太OUT了?。?！快來看看VLOOKUP和LOOKUP是怎么做的吧？

在使用這兩個(gè)函數(shù)之前，我們必須按下圖所示，對各區(qū)間及對應(yīng)值進(jìn)行升序排列：

1.將數(shù)值區(qū)間的分界值按升序依次填入連續(xù)的單元格，即從最小值到最大值，自上而下填入同一列單元格中。

2.各區(qū)間分界值采用區(qū)間下界值，例如，“及格”區(qū)間數(shù)值60，“優(yōu)秀”區(qū)間數(shù)值85。

3.如果最小的數(shù)值區(qū)間無下界，也必須賦予一個(gè)足夠小的數(shù)值，例如-8^∧8，否則公式可能會(huì)報(bào)錯(cuò)。

圖3.2：區(qū)間查詢——查找范圍

接下來就是VLOOKUP和LOOKUP展現(xiàn)戰(zhàn)斗力的時(shí)候了！?。?/span>

VLOOKUP："一"字之差，難得模糊

VLOOKUP函數(shù)的解決之道和其基礎(chǔ)用法非常類似，只需將最后一個(gè)參數(shù)更改為1或TRUE即可。

=VLOOKUP(B2,$E$2:$F$5,2,1)

圖3.3：區(qū)間查詢——VLOOKUP"一"字之差

公式說明

最末參數(shù)是0的時(shí)候，VLOOKUP精確查找，采用遍歷法原理將查找值與查找區(qū)域首列值從上到下逐一比較。但最末參數(shù)是1的時(shí)候，VLOOKUP模糊查找，采用二分法原理將查找值與查找區(qū)域首列的二分位值（中間值）進(jìn)行比較。模糊查找要得到正確結(jié)果，查找區(qū)域首列必須升序排列。記住這一點(diǎn)，你就能輕松駕馭區(qū)間查找。否則，VLOOKUP區(qū)間查找公式將會(huì)帶來災(zāi)難?。?！

圖3.4：區(qū)間查詢——查找范圍須升序排列

LOOKUP：基礎(chǔ)用法，簡單粗暴

同樣的問題，LOOKUP是怎么做的呢？基礎(chǔ)用法，直接上?。?！就是這么簡單粗暴！

圖3.5：區(qū)間查詢——粗暴的LOOKUP

公式說明：

LOOKUP函數(shù)只采用二分法查找，所以只要做好了升序排列和區(qū)間設(shè)置，按平?；居梅ㄝ斎牍郊纯伞?/span>

區(qū)間查找，不論是VLOOKUP還是LOOKUP的查找都采用的是二分法查找。這里以LOOKUP函數(shù)為例，說說二分法。

LOOKUP的二分法

LOOKUP總是將查詢范圍視為一組從小到大排列的有序數(shù)組，通過將目標(biāo)值與查詢區(qū)域的二分位值進(jìn)行比較，從而確定目標(biāo)值出現(xiàn)在查詢區(qū)域的上半?yún)^(qū)細(xì)分范圍(目標(biāo)值小于二分位值)還是下半?yún)^(qū)細(xì)分范圍(目標(biāo)值大于二分位值)。然后繼續(xù)將目標(biāo)值與細(xì)分范圍的二分位值進(jìn)行比較，循環(huán)往復(fù)，直到細(xì)分范圍不可再分，返回最后一個(gè)小于或等于目標(biāo)值的二分位對應(yīng)的結(jié)果。

特別說明：

1.二分位值的確定。如果查詢范圍的數(shù)據(jù)個(gè)數(shù)為奇數(shù)，二分位值取中間項(xiàng)；若為偶數(shù)，則取中間兩項(xiàng)中項(xiàng)序較小的一項(xiàng)；若僅一項(xiàng)，則視該唯一項(xiàng)為二分位值。例如，5個(gè)數(shù)據(jù)，二分值取第3項(xiàng)，6個(gè)數(shù)據(jù)，二分值也取第3項(xiàng)。

2.連續(xù)相等取末原則。如果二分位值與目標(biāo)值完全相等，則停止二分法查詢，而采用連續(xù)相等取末原則返回?cái)?shù)據(jù)。即，如果從二分位值開始向下存在一個(gè)連續(xù)等于目標(biāo)值的區(qū)域，則返回這個(gè)連續(xù)區(qū)域最后一個(gè)數(shù)據(jù)對應(yīng)的值；如果從二分位值開始向下不存在連續(xù)等于目標(biāo)值的區(qū)域，則返回二分值對應(yīng)的值。

3.如果目標(biāo)值大于所有二分位值，則返回查詢范圍的最后一個(gè)值；如果目標(biāo)值小于所有二分位值，則返回#N/A錯(cuò)誤值。

文字生澀，結(jié)合實(shí)例圖解會(huì)更加容易！

圖3.6：區(qū)間查詢——LOOKUP的二分法

C2：=LOOKUP(B2,$E$2:$E$7,$F$2:$F$7)

目標(biāo)單元格B2先與第一個(gè)二分位E4比較，2<3；轉(zhuǎn)而與E4的上半?yún)^(qū)E2:E3的二分位E2比較，2>1；繼續(xù)與E2的下半?yún)^(qū)二分位E3進(jìn)行比較，2<3。至此，細(xì)分范圍不可再分，最后一個(gè)小于或等于目標(biāo)值的二分位為E2，所以返回E2對應(yīng)的結(jié)果F2，即"A"。

C3：=LOOKUP(B3,$E$2:$E$7,$F$2:$F$7)

目標(biāo)單元格B3先與第一個(gè)二分位E4比較，3=3；此時(shí)停止二分法查詢，轉(zhuǎn)為連續(xù)相等取末查找。自當(dāng)前二分位E4開始，E4、E5連續(xù)等于3，因此返回E5對應(yīng)的結(jié)果F5，即"D"。圖中的E3與E4相鄰且等于3，但其在E4上方，故不參與第二階段的匹配；E7也等于3，但E2:E7區(qū)域不是連續(xù)等于3的區(qū)域，所以E7不參與匹配。只有E5滿足條件，位于連續(xù)相等區(qū)域的最末?。?！劃重點(diǎn)，LOOKUP這一特性經(jīng)常與其忽略錯(cuò)誤值的特性聯(lián)用，形成經(jīng)典的LOOKUP(1,0/(條件)...)結(jié)構(gòu)！這一用法我們后續(xù)會(huì)詳細(xì)介紹。

C4：=LOOKUP(B4,$E$2:$E$7,$F$2:$F$7)

目標(biāo)單元格B4先與第一個(gè)二分位E4比較，4>3；轉(zhuǎn)而與E4的下半?yún)^(qū)E5:E7的二分位E6比較，4>2；繼續(xù)與E6的下半?yún)^(qū)二分位E7進(jìn)行比較，4>3。至此，細(xì)分范圍不可再分，最后一個(gè)小于或等于目標(biāo)值的二分位為E7，所以返回E7對應(yīng)的結(jié)果F7，即"F"。

第四回合，VLOOKUP和LOOKUP都能很好地解決區(qū)間查詢問題，但LOOKUP保持了它一貫的用法，所以這一局LOOKUP小勝！

結(jié)束語

二分法是查詢函數(shù)中最難的知識(shí)點(diǎn)之一，盡管為了講好這一知識(shí)點(diǎn)，小花已經(jīng)費(fèi)勁心思，但可能很多小伙伴還是無法完全理解。“紙上得來終覺淺，絕知此事要躬行”，希望小伙伴們多多練習(xí)，在實(shí)操中尋找答案！天下英雄誰敵手，戰(zhàn)不止?fàn)幉恍?，敬請期待?/span>

本文配套的練習(xí)課件請加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

VLOOKUP與LOOKUP的比較①《VLOOKUP&LOOKUP雙雄戰(zhàn)（一）：VLOOKU的漂亮開局》

VLOOKUP與LOOKUP的比較②《VLOOKUP&LOOKUP雙雄戰(zhàn)(二)：LOOKUP劍走偏鋒》

LOOKUP二分法講解《 LOOKUP函數(shù)用法全解（下）——LOOKUP函數(shù)的二分法原理》