在之前的文章《Excel函數(shù)學(xué)習(xí)之LOOKUP函數(shù)的5種用法》，我們了解了LOOKUP函數(shù)的5種用法，估計(jì)有很多小伙伴都沒(méi)看懂，今天給大家細(xì)致的講解一下LOOKUP的二分法原理，了解原理后，再返回去看昨天的教程，相信你會(huì)對(duì)LOOKUP有別樣的理解。

手機(jī)如何做表格：點(diǎn)擊查看

在之前的文章中，咱們學(xué)習(xí)了LOOKUP函數(shù)的各種套路，也多次提到了LOOKUP函數(shù)的查找是根據(jù)二分法來(lái)進(jìn)行的，那么到底什么是二分法，今天就來(lái)聊聊這個(gè)問(wèn)題。

還是用昨天的例子：按序號(hào)查找成績(jī)，序號(hào)是升序排列的，公式=LOOKUP(J2,A2:D19)的結(jié)果正確。

一、二分法查找原理

二分法查找是把查找范圍中的數(shù)據(jù)按照個(gè)數(shù)一分為二找到位于中間位置的一個(gè)數(shù)據(jù)，中間值，然后用我們的查找值和中間值做比較。當(dāng)中間值等于查找值時(shí)，直接去獲取結(jié)果；當(dāng)中間值小于查找值時(shí)，則向下繼續(xù)進(jìn)行二分法查找比較（也就是在不含中間值在內(nèi)的下方的那一半數(shù)據(jù)中繼續(xù)進(jìn)行二分法查找）；當(dāng)中間值大于查找值時(shí)，則向上繼續(xù)進(jìn)行二分法查找比較（也就是在不含中間值在內(nèi)的上方的那一半數(shù)據(jù)中繼續(xù)進(jìn)行二分法查找）。如果如此二分到最后一個(gè)數(shù)據(jù)都未找到等于查找值的數(shù)據(jù)：最后一個(gè)數(shù)據(jù)小于查找值的，那就以最后一個(gè)數(shù)據(jù)的位置去獲取結(jié)果值；最后一個(gè)數(shù)據(jù)大于查找值的，那就再向上找一個(gè)位置最靠近最后一個(gè)數(shù)據(jù)的小于或等于查找值的數(shù)據(jù)，再以這個(gè)數(shù)據(jù)的位置去獲取結(jié)果。

只是這樣解釋估計(jì)很多朋友都會(huì)犯迷糊，我們結(jié)合上面的例子，具體看看如何通過(guò)序號(hào)5，找到語(yǔ)文成績(jī)79的。

第一次比較：查找范圍A2~A19一共18個(gè)數(shù)據(jù)，中間位置是18÷2=9，即中間值是A10單元格的9。顯然查找值5小于9，所以繼續(xù)向上在A2~A9中查找；

提示：如果查找范圍數(shù)據(jù)個(gè)數(shù)是單數(shù)，中間位置就是（個(gè)數(shù)+1）÷2，例如11行的話，中間位置就是（11+1）÷2=6；如果數(shù)據(jù)個(gè)數(shù)是偶數(shù)，中間位置就是（個(gè)數(shù)）÷2。

第二次比較：只有8個(gè)數(shù)據(jù)，中間位置是8÷2=4，中間值是A5單元格的4，查找值5大于4，所以繼續(xù)向下在A6~A9中找。注意此時(shí)下面只有四個(gè)數(shù)，第一次查找時(shí)直接將9下面的數(shù)據(jù)都排除了。

第三次比較：4個(gè)數(shù)據(jù)，中間值是A7的6，查找值5小于6，所以向上找。此時(shí)只剩下一個(gè)數(shù)據(jù)A6單元格的5，與查找值一致，因此就得到5所對(duì)應(yīng)的D列數(shù)據(jù)79。

僅僅通過(guò)這樣一個(gè)例子要想明白二分法是很困難的，我們?cè)倏匆粋€(gè)例子。將上表中的數(shù)據(jù)按照成績(jī)降序排列，還是按序號(hào)5查找語(yǔ)文成績(jī)，公式不做修改。因?yàn)樾蛱?hào)這一列的順序亂了，不是升序排列，結(jié)果就出現(xiàn)了錯(cuò)誤，實(shí)際是79，公式得到的是94，這是怎么回事呢？還是通過(guò)二分法來(lái)看看吧。

第一次查找：中間值（第9個(gè)數(shù)據(jù)）是18，查找值5小于18，因此向上在A2~A9中找；

第二次查找：上面的8個(gè)數(shù)據(jù)，中間值（第4個(gè)數(shù)據(jù)）是8，查找值5小于8，繼續(xù)向上在A2~A4中找；

第三次查找：上面的3個(gè)數(shù)據(jù)，中間值是1，查找值5大于1，向下找：

第四次查找：現(xiàn)在只有A4單元格一個(gè)數(shù)據(jù)7，查找值5小于7，因此以7為參考，向上找一個(gè)位置最靠近7，同時(shí)數(shù)值小于5或者等于5的數(shù)據(jù)，即A3單元格的1，由此獲得對(duì)應(yīng)的語(yǔ)文值就是94。

通過(guò)這兩個(gè)例子，我想大家對(duì)于二分法應(yīng)該有了一定的認(rèn)識(shí)，關(guān)于這個(gè)原理，在函數(shù)說(shuō)明里只有一句話的介紹：

在實(shí)際應(yīng)用中，我們可以不用去糾結(jié)二分法到底是怎么回事，中間位置是什么，該往下還是往上找，這都是函數(shù)的工作，我們只需要記住一點(diǎn)：數(shù)據(jù)一定要升序排列，如果不能升序排列，那么就按照LOOKUP的精確查找套路去設(shè)計(jì)公式。

二、LOOKUP實(shí)現(xiàn)數(shù)據(jù)的四舍五入

二分法原理就介紹這么多，接下來(lái)需要解決之前遺留的兩個(gè)問(wèn)題。

在5月12日的文章中，我們用LOOKUP解決了一個(gè)四舍五入的問(wèn)題，結(jié)果大家紛紛留言要個(gè)解釋：

那么引起大家熱議的這個(gè)公式到底是什么呢？看下圖：

原來(lái)這個(gè)公式是用LOOKUP函數(shù)將一個(gè)數(shù)字百位以下全部舍去，實(shí)現(xiàn)了百分位取整。

在了解過(guò)二分法原理以后，是時(shí)候讓LOOKUP還大家一個(gè)解釋了。首先說(shuō)明ROW（A:A）*100這部分。它其實(shí)就是得到了一組數(shù)字。為了讓大家看明白，把A:A范圍給小一點(diǎn)，我們用=ROW(A1:A31)*100作說(shuō)明：

雖然單元格中只能看到一個(gè)100，實(shí)際上是31個(gè)數(shù)字，我們可以用f9功能鍵來(lái)看看具體內(nèi)容：

ROW函數(shù)用于獲取單元格的行號(hào)，ROW(A1:A31)*100就是用A1到A31單元格的行號(hào)分別乘以100，得到一組百位取整的數(shù)據(jù){100;200;300;……3000;3100}。

然后LOOKUP上場(chǎng)了。它要在上述得到的已經(jīng)百位取整的數(shù)組中查找A1。因?yàn)閿?shù)組是按升序排列的，所以查找A1的實(shí)質(zhì)就是在數(shù)組中找小于等于A1的最大值。

以2517.32為例，唯有2500是小于它的最大值，因此結(jié)果就是2500。有興趣的朋友可以自己用二分法原理去試試，看看對(duì)不對(duì)。

其他數(shù)字的查找方式都是如此。這個(gè)公式之所以巧妙，就是把一個(gè)四舍五入的問(wèn)題變成了查找引用的問(wèn)題，真是妙！

三、LOOKUP進(jìn)行數(shù)據(jù)提取

我們又利用LOOKUP進(jìn)行數(shù)據(jù)的提取，因此有了一個(gè)5000字的約會(huì)：

這次又是什么問(wèn)題呢？原題如圖所示提取學(xué)號(hào)：

咱們用上圖的第一個(gè)數(shù)據(jù)來(lái)解釋。在N1單元格中輸入“10張勇a”，然后在O1單元格中輸入公式=-LOOKUP(1,-LEFT(N1,ROW(1:9)))提取學(xué)號(hào)。

LOOKUP不是引用函數(shù)嗎，怎么又可以提取數(shù)字了？

LEFT函數(shù)的第二個(gè)參數(shù)使用了一個(gè)數(shù)組，ROW(1:9)相當(dāng)于{1;2;3;4;5;6;7;8;9}。LEFT是把第一參數(shù)指定的數(shù)據(jù)從左邊開(kāi)始提取，提取的長(zhǎng)度由第二參數(shù)來(lái)確定。 LEFT按照數(shù)組{1;2;3;4;5;6;7;8;9}提取，得到9個(gè)結(jié)果：

也就是從左邊開(kāi)始提取1位，2位……一直到9位。因?yàn)長(zhǎng)EFT提取的結(jié)果都是文本類型，在LEFT前面加上負(fù)號(hào)，就可以把其中的文本型數(shù)字轉(zhuǎn)為數(shù)值，文字變成錯(cuò)誤值：

錯(cuò)誤值被LOOKUP忽略，現(xiàn)在就變成了在{-1; -10}中找1。1比這組數(shù)據(jù)都大，根據(jù)二分法查找原理，二分后只能向下找，直到最后一位小于1的數(shù)據(jù)。因此，我們可以簡(jiǎn)單理解成：當(dāng)查找值大于查找范圍中所有數(shù)據(jù)（不管是否是升序排列）時(shí)，LOOKUP的實(shí)質(zhì)就是在找最后一個(gè)數(shù)據(jù)。

其實(shí)將公式中的1變成0也是可以的，因?yàn)?也比所有的負(fù)數(shù)大：

當(dāng)前最后一個(gè)數(shù)是-10，所以我們?cè)贚OOKUP前面再加個(gè)負(fù)號(hào)，就變成10了，也就是我們需要提取的數(shù)字。

對(duì)于初學(xué)者來(lái)說(shuō)，以上兩個(gè)案例中LOOKUP的用法過(guò)于高級(jí)，即便是通過(guò)這些介紹，估計(jì)也是一知半解，其實(shí)學(xué)習(xí)函數(shù)是有一個(gè)過(guò)程的，從不認(rèn)識(shí)到了解，從了解再到掌握，這里需要大量的練習(xí)和思考。只要大家保持積極樂(lè)觀的心態(tài)，能夠體會(huì)到學(xué)習(xí)函數(shù)的樂(lè)趣的時(shí)候，就離成功不遠(yuǎn)了。

相關(guān)學(xué)習(xí)推薦：excel教程