LOOKUP函數(shù)用法全解(上)——LOOKUP函數(shù)的5種用法
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2018-07-06 11:49:55點擊:365244
小編有話說:過去大家使用VLOOKUP函數(shù)的時候,經(jīng)常都會遇到一些麻煩事,那就是VLOOKUP查找只找到滿足條件的第一個值就不找了,并且還只能從左往右查找,簡直是太多限制了。今天給大家介紹LOOKUP函數(shù),可以完美的解決這兩個問題,太好用了!
LOOKUP函數(shù)非常強大,有引用函數(shù)之王的稱號。要想完全掌握它,必須了解它的5種常見用法和它的二分法查找原理。由于相關(guān)知識點比較多,所以教程將分成上下兩篇。今天我們首先通過五個例子來了解這個函數(shù)的5種常見用法。明天我們再來說二分法查找原理和之前文章中遺留的兩個LOOKUP問題。
一、常規(guī)引用
格式:LOOKUP(查找值,查找區(qū)域)
例1:根據(jù)姓名查找語文成績,公式為=LOOKUP(H2,C2:D19)
例2:根據(jù)姓名查找英語成績,公式為:=LOOKUP(H2,C2:F19)
通過這兩個例子我們可以發(fā)現(xiàn),LOOKUP在進行查找時公式的結(jié)構(gòu)非常簡單,查找值和要找的結(jié)果分別位于查找區(qū)域的首列和末列。
但是僅僅了解這個用法是遠遠不夠的,如果我們再試一個數(shù)據(jù)的話,有可能就會發(fā)現(xiàn)問題:
當查找姓名變成趙永福的時候,結(jié)果就不對了,這是因為LOOKUP函數(shù)使用的是二分法查找,也就是模糊匹配,關(guān)于這一點,我們將在明天的教程《LOOKUP函數(shù)用法全解(下)——LOOKUP的二分法查找原理》中詳細解釋。
因此,在使用LOOKUP進行常規(guī)查找的時候,有一個非常重要的步驟,就是按照查找內(nèi)容(姓名所在的c列)升序排序。
當我們排序以后,公式的結(jié)果立刻變成了正確的,是不是很神奇!
這又引出了一個新的問題,如果數(shù)據(jù)不能排序的話,LOOKUP函數(shù)還能用嗎?
肯定能用啊,下面來看看LOOKUP函數(shù)的第二種用法。
二、精確查找的套路
格式:=LOOKUP(1,0/(查找范圍=查找值),結(jié)果范圍)
在I2單元格輸入公式:
=LOOKUP(1,0/(C2:C19=H2),D2:D19),回車,可看到正確結(jié)果。
關(guān)于這個套路的1和0/到底是什么意思,也是提問率最高的問題之一,在未講解二分法原理之前,簡單來說一下公式的意思。1就是要查找的值,但是條件變了,不是直接查找姓名,而是根據(jù)姓名得到的一組邏輯值:
注意這里只有一個TRUE,也就是我們要找的姓名。
接下來用0除以這些邏輯值,在進行計算的時候邏輯值TRUE代表1,FALSE代表0,當分母為0也就是FALSE的時候,計算結(jié)果是錯誤值:
因此,LOOKUP的工作就變成了在一組數(shù)據(jù)中找1。由于這組數(shù)據(jù)只有一個0,其他都是錯誤值,二分法使得LOOKUP只能找到不大于查找值的最后一個數(shù)字,因此只能找到0,最后根據(jù)0的行位置(第2行)得到第三參數(shù)對應(yīng)位置的數(shù)據(jù),即D2就是我們需要的結(jié)果。
這部分內(nèi)容算是函數(shù)學(xué)習(xí)中比較有難度的知識點了,初學(xué)者可能理解困難,這不要緊,隨著學(xué)習(xí)的深入,當對數(shù)組和邏輯值這兩大要點掌握比較熟練的時候,這些內(nèi)容就很好理解了。目前如果不能完全理解,記住這個套路就行:=LOOKUP(1,0/(查找范圍=查找值),結(jié)果范圍)。同時這個套路還能延伸出多條件精確查找用法:
=LOOKUP(1,0/((查找范圍1=查找值1)* (查找范圍2=查找值2)* (查找范圍3=查找值3)),結(jié)果范圍)
就是在每個查找范圍內(nèi)找到要找的值,得到的邏輯值相乘后同時符合多個條件的位置就是1,原理與單條件的一樣。
三、反向查找的套路
與我們熟知的VLOOKUP不同,使用LOOKUP函數(shù)進行反向查找時非常簡單,公式結(jié)構(gòu)為:
=LOOKUP(查找值,查找列,結(jié)果列),下面這個例子是按照姓名排序后再反向查找的效果:
如果數(shù)據(jù)不能排序的話,使用精確查找的套路:=LOOKUP(1,0/(C2:C19=H8),B2:B19)
四、按區(qū)間查找的套路
根據(jù)學(xué)生的總分給出相應(yīng)的評語。50分以下的為“很差”,50-100分的為“差”,100-150分的為“一般”,150-200分的為“較好”,200-250分的為“優(yōu)秀”,250分及以上的為“能手”。
這里用的公式為:
=LOOKUP(G2,{0,50,100,150,200,250;"很差","差","一般","較好","優(yōu)秀","能手"})
按照評語的要求分成了六個等級,如果用if函數(shù)去做就很啰嗦,使用LOOKUP處理這類問題非常方便,公式結(jié)構(gòu)也很簡單:
=LOOKUP(分數(shù)值,{下限1, 下限2……;評語1,評語2……})
在寫這個公式的時候注意兩點:
1.LOOKUP的第二參數(shù)使用了常量數(shù)組,這里的大括號是手動輸入的,括號內(nèi)用一個分號分開,左邊是每個等級的下限,例如50分以下這個表述里下限就是0,50-100的下限就是50,以此類推,每個數(shù)字之間用逗號分開;分號右邊是對應(yīng)的評語,評語應(yīng)當使用引號,同時用逗號分開(公式里的所有符號都是英文狀態(tài)下的);
2.數(shù)字區(qū)間應(yīng)當遵循升序的排列順序,否則結(jié)果就會錯誤。
五、關(guān)于數(shù)據(jù)排序的重要性
當我們按照學(xué)號查找姓名的時候,發(fā)現(xiàn)會出現(xiàn)錯誤,學(xué)號也是按升序排列的啊,怎么會錯?
這是一種最常見的錯誤,這里的學(xué)號升序排列只是我們感覺如此而已,實際上升序的效果是這樣的:
在使用LOOKUP的時候,如果不使用精確查找的套路,切記一定要排序才能保證公式結(jié)果的正確性。
小結(jié)
1、今天一共分享了五種LOOKUP的使用套路,分別是常規(guī)查找、精確查找、多條件查找,反向查找還有按區(qū)間查找,初學(xué)者掌握這些套路學(xué)會去套用解決問題就可以了;
2、LOOKUP函數(shù)的查找原理與我們之前學(xué)過的VLOOKUP不同,VLOOKUP函數(shù)的查找方式叫做遍歷法,找到滿足條件的第一個值就會停止查找,而LOOKUP函數(shù)使用的是二分法原理進行查找,要找到滿足條件的最后一個值才會停止查找,這一點在函數(shù)的說明文檔里也提到了;
我們可以通過一個簡單的測試來驗證這一點:
3、對數(shù)據(jù)源按升序排列這一點很重要:
如果不能排序,那么一定要使用這個套路:=LOOKUP(1,0/(查找范圍=查找值),結(jié)果范圍),這也是二分法的特性決定的;
4、LOOKUP這個函數(shù)很強大,同時也很難以理解,要想徹底弄清楚這個函數(shù),必須了解二分法原理。明天的教程我們就來聊聊什么是二分法原理,順便再把前一段時間遺留的兩個問題(LOOKUP解決四舍五入的問題和進行數(shù)據(jù)提取的問題)做個解釋。
本文配套的練習(xí)課件請加入QQ群:316492581下載。
Excel教程微信公眾號,每日更新。
如果您因工作所需使用到Excel,不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。
相關(guān)推薦:
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!