LOOKUP函數(shù)用法全解(下)——LOOKUP函數(shù)的二分法原理
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2018-07-07 15:05:40點(diǎn)擊:40754
小編有話說:昨天給大家推送的教程講解了LOOKUP的五種用法,估計(jì)有很多小伙伴都沒看懂,今天給大家細(xì)致的講解一下LOOKUP的二分法原理,了解原理后,再返回去看昨天的教程,相信你會對LOOKUP有別樣的理解。
昨天咱們學(xué)習(xí)了LOOKUP函數(shù)的各種套路,也多次提到了LOOKUP函數(shù)的查找是根據(jù)二分法來進(jìn)行的,那么到底什么是二分法,今天就來聊聊這個問題。
還是用昨天的例子:按序號查找成績,序號是升序排列的,公式=LOOKUP(J2,A2:D19)的結(jié)果正確。
一、二分法查找原理
二分法查找是把查找范圍中的數(shù)據(jù)按照個數(shù)一分為二找到位于中間位置的一個數(shù)據(jù),中間值,然后用我們的查找值和中間值做比較。當(dāng)中間值等于查找值時,直接去獲取結(jié)果;當(dāng)中間值小于查找值時,則向下繼續(xù)進(jìn)行二分法查找比較(也就是在不含中間值在內(nèi)的下方的那一半數(shù)據(jù)中繼續(xù)進(jìn)行二分法查找);當(dāng)中間值大于查找值時,則向上繼續(xù)進(jìn)行二分法查找比較(也就是在不含中間值在內(nèi)的上方的那一半數(shù)據(jù)中繼續(xù)進(jìn)行二分法查找)。如果如此二分到最后一個數(shù)據(jù)都未找到等于查找值的數(shù)據(jù):最后一個數(shù)據(jù)小于查找值的,那就以最后一個數(shù)據(jù)的位置去獲取結(jié)果值;最后一個數(shù)據(jù)大于查找值的,那就再向上找一個位置最靠近最后一個數(shù)據(jù)的小于或等于查找值的數(shù)據(jù),再以這個數(shù)據(jù)的位置去獲取結(jié)果。
只是這樣解釋估計(jì)很多朋友都會犯迷糊,我們結(jié)合上面的例子,具體看看如何通過序號5,找到語文成績79的。
第一次比較:查找范圍A2~A19一共18個數(shù)據(jù),中間位置是18÷2=9,即中間值是A10單元格的9。顯然查找值5小于9,所以繼續(xù)向上在A2~A9中查找;
提示:如果查找范圍數(shù)據(jù)個數(shù)是單數(shù),中間位置就是(個數(shù)+1)÷2,例如11行的話,中間位置就是(11+1)÷2=6;如果數(shù)據(jù)個數(shù)是偶數(shù),中間位置就是(個數(shù))÷2。
第二次比較:只有8個數(shù)據(jù),中間位置是8÷2=4,中間值是A5單元格的4,查找值5大于4,所以繼續(xù)向下在A6~A9中找。注意此時下面只有四個數(shù),第一次查找時直接將9下面的數(shù)據(jù)都排除了。
第三次比較:4個數(shù)據(jù),中間值是A7的6,查找值5小于6,所以向上找。此時只剩下一個數(shù)據(jù)A6單元格的5,與查找值一致,因此就得到5所對應(yīng)的D列數(shù)據(jù)79。
僅僅通過這樣一個例子要想明白二分法是很困難的,我們再看一個例子。將上表中的數(shù)據(jù)按照成績降序排列,還是按序號5查找語文成績,公式不做修改。因?yàn)樾蛱栠@一列的順序亂了,不是升序排列,結(jié)果就出現(xiàn)了錯誤,實(shí)際是79,公式得到的是94,這是怎么回事呢?還是通過二分法來看看吧。
第一次查找:中間值(第9個數(shù)據(jù))是18,查找值5小于18,因此向上在A2~A9中找;
第二次查找:上面的8個數(shù)據(jù),中間值(第4個數(shù)據(jù))是8,查找值5小于8,繼續(xù)向上在A2~A4中找;
第三次查找:上面的3個數(shù)據(jù),中間值是1,查找值5大于1,向下找:
第四次查找:現(xiàn)在只有A4單元格一個數(shù)據(jù)7,查找值5小于7,因此以7為參考,向上找一個位置最靠近7,同時數(shù)值小于5或者等于5的數(shù)據(jù),即A3單元格的1,由此獲得對應(yīng)的語文值就是94。
通過這兩個例子,我想大家對于二分法應(yīng)該有了一定的認(rèn)識,關(guān)于這個原理,在函數(shù)說明里只有一句話的介紹:
在實(shí)際應(yīng)用中,我們可以不用去糾結(jié)二分法到底是怎么回事,中間位置是什么,該往下還是往上找,這都是函數(shù)的工作,我們只需要記住一點(diǎn):數(shù)據(jù)一定要升序排列,如果不能升序排列,那么就按照LOOKUP的精確查找套路去設(shè)計(jì)公式。
二、LOOKUP實(shí)現(xiàn)數(shù)據(jù)的四舍五入
二分法原理就介紹這么多,接下來需要解決之前遺留的兩個問題。
在5月12日的文章中,我們用LOOKUP解決了一個四舍五入的問題,結(jié)果大家紛紛留言要個解釋:
那么引起大家熱議的這個公式到底是什么呢?看下圖:
原來這個公式是用LOOKUP函數(shù)將一個數(shù)字百位以下全部舍去,實(shí)現(xiàn)了百分位取整。
在了解過二分法原理以后,是時候讓LOOKUP還大家一個解釋了。首先說明ROW(A:A)*100這部分。它其實(shí)就是得到了一組數(shù)字。為了讓大家看明白,把A:A范圍給小一點(diǎn),我們用=ROW(A1:A31)*100作說明:
雖然單元格中只能看到一個100,實(shí)際上是31個數(shù)字,我們可以用f9功能鍵來看看具體內(nèi)容:
ROW函數(shù)用于獲取單元格的行號,ROW(A1:A31)*100就是用A1到A31單元格的行號分別乘以100,得到一組百位取整的數(shù)據(jù){100;200;300;……3000;3100}。
然后LOOKUP上場了。它要在上述得到的已經(jīng)百位取整的數(shù)組中查找A1。因?yàn)閿?shù)組是按升序排列的,所以查找A1的實(shí)質(zhì)就是在數(shù)組中找小于等于A1的最大值。
以2517.32為例,唯有2500是小于它的最大值,因此結(jié)果就是2500。有興趣的朋友可以自己用二分法原理去試試,看看對不對。
其他數(shù)字的查找方式都是如此。這個公式之所以巧妙,就是把一個四舍五入的問題變成了查找引用的問題,真是妙!
三、LOOKUP進(jìn)行數(shù)據(jù)提取
在5月31日的文章中,我們又利用LOOKUP進(jìn)行數(shù)據(jù)的提取,因此有了一個5000字的約會:
這次又是什么問題呢?原題如圖所示提取學(xué)號:
咱們用上圖的第一個數(shù)據(jù)來解釋。在N1單元格中輸入“10張勇a”,然后在O1單元格中輸入公式=-LOOKUP(1,-LEFT(N1,ROW(1:9)))提取學(xué)號。
LOOKUP不是引用函數(shù)嗎,怎么又可以提取數(shù)字了?
LEFT函數(shù)的第二個參數(shù)使用了一個數(shù)組,ROW(1:9)相當(dāng)于{1;2;3;4;5;6;7;8;9}。LEFT是把第一參數(shù)指定的數(shù)據(jù)從左邊開始提取,提取的長度由第二參數(shù)來確定。 LEFT按照數(shù)組{1;2;3;4;5;6;7;8;9}提取,得到9個結(jié)果:
也就是從左邊開始提取1位,2位……一直到9位。因?yàn)?/span>LEFT提取的結(jié)果都是文本類型,在LEFT前面加上負(fù)號,就可以把其中的文本型數(shù)字轉(zhuǎn)為數(shù)值,文字變成錯誤值:
錯誤值被LOOKUP忽略,現(xiàn)在就變成了在{-1; -10}中找1。1比這組數(shù)據(jù)都大,根據(jù)二分法查找原理,二分后只能向下找,直到最后一位小于1的數(shù)據(jù)。因此,我們可以簡單理解成:當(dāng)查找值大于查找范圍中所有數(shù)據(jù)(不管是否是升序排列)時,LOOKUP的實(shí)質(zhì)就是在找最后一個數(shù)據(jù)。
其實(shí)將公式中的1變成0也是可以的,因?yàn)?/span>0也比所有的負(fù)數(shù)大:
當(dāng)前最后一個數(shù)是-10,所以我們在LOOKUP前面再加個負(fù)號,就變成10了,也就是我們需要提取的數(shù)字。
對于初學(xué)者來說,以上兩個案例中LOOKUP的用法過于高級,即便是通過這些介紹,估計(jì)也是一知半解,其實(shí)學(xué)習(xí)函數(shù)是有一個過程的,從不認(rèn)識到了解,從了解再到掌握,這里需要大量的練習(xí)和思考。只要大家保持積極樂觀的心態(tài),能夠體會到學(xué)習(xí)函數(shù)的樂趣的時候,就離成功不遠(yuǎn)了。
本文配套的練習(xí)課件請加入QQ群:316492581下載。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
相關(guān)推薦:
《excel量筒式柱形圖繪制案例教程》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請收好!