Vlookup和lookup兩函數(shù)只相差一個(gè)字,但境遇卻是天壤之別,Vlookup廣受歡迎大行其道,lookup卻鮮有人問津。之所以出現(xiàn)這樣的情況主要是由于 Vlookup函數(shù)的易用性及Lookup這個(gè)心機(jī)婊的小怪癖,導(dǎo)致lookup函數(shù)一直被Vlookup函數(shù)壓制,遲遲不能被大眾所接受。其實(shí)Lookup函數(shù)在很多方面比Vlookup更優(yōu)秀呢,下面我們將由易到難的講解lookup函數(shù)和它那些經(jīng)典的瞬間。
Lookup是查找引用公式的一種,主要功能是從單行(單列)區(qū)域或者從一個(gè)數(shù)組中查找某個(gè)值,并返回相應(yīng)的值。它有兩種用法:
下例中我們就分別采用了兩種方式來計(jì)算排名第3的銷售員和銷量。 注意:lookup是個(gè)有強(qiáng)迫癥的處女座,房子太亂了它是不會(huì)認(rèn)真工作的。所以查找數(shù)據(jù)區(qū)域必須是按升序排列的才能得出正確的結(jié)果,如果不按升序排列是得不到正確值的,如果真的得到正確值,那恭喜你可以去買張彩票了。 下圖公式?jīng)]變,只是調(diào)整了一下數(shù)據(jù)行的順序,就得出莫名其妙的結(jié)果,如下:
有些同學(xué)受不了lookup臭脾氣(非要查找數(shù)據(jù)按升序排列)。覺得這些功能明明用Vlookup就可以實(shí)現(xiàn),為什么要了解lookup函數(shù)呢,那是因?yàn)閘ookup函數(shù)可以實(shí)現(xiàn)一些vlookup實(shí)現(xiàn)不了的功能。譬如vlookup沒法直接逆序查找(即從右到左的查找)但lookup可以,而且,通過構(gòu)建一個(gè)自定義向量,我們可以實(shí)現(xiàn)精確查找??聪旅娴睦樱?br> 公式的計(jì)算原理如下:我們要找殺敵最多的武將,正常的做法是首先用MAX函數(shù)找出最大殺敵數(shù),然后在殺敵數(shù)列中查找這個(gè)值,然后,返回武將列中對(duì)應(yīng)位置的武將。但是因?yàn)闅硵?shù)列中的數(shù)據(jù)并不是升序排列的,因此,我們沒法取得正確值。 這個(gè)時(shí)候我們就需要做些變通了,我們需要構(gòu)建一個(gè)查找向量,在本例中我們用公式“0/(C3:C14=MAX(C3:C14))”構(gòu)建一個(gè)向量。這個(gè)公式的意思是,如果單元格值等于最大值,結(jié)果就為TRUE,TRUE作為被除數(shù)的時(shí)候會(huì)被轉(zhuǎn)化為1,這樣1/true就等于0,如果單元格值不為最大值,結(jié)果就是FALSE,F(xiàn)ALSE作為被除數(shù)的時(shí)候會(huì)被轉(zhuǎn)化為0,這樣0/false就會(huì)顯示錯(cuò)誤值,這樣就構(gòu)建了一個(gè)由0和錯(cuò)誤值構(gòu)成的向量數(shù)組,如下: Lookup在向量中查找的時(shí)候會(huì)忽略掉錯(cuò)誤值,這樣整個(gè)向量中就只有一個(gè)0值是有效的,就不存在排序的概念了,我們通過在向量中查找0值,最后找到殺敵最多的武將就是趙云了。 第二個(gè)公式的原理也是一樣,用公式“0/(C3:C14=1000)/(D3:D14=800)”,創(chuàng)建一個(gè)向量,將同時(shí)滿足殺敵數(shù)等于1000,自損數(shù)等于800的賦值為0,將不能同時(shí)滿足條件的賦上錯(cuò)誤值,構(gòu)建一個(gè)由0和錯(cuò)誤值構(gòu)成的數(shù)組,如下: 最后Lookup會(huì)忽略錯(cuò)誤值,找到殺敵一千自損八百的武將為呂布。 這兩個(gè)公式就是lookup函數(shù)的典型用法,歸納起來就是“=Lookup(0,0/(條件1)/(條件2)/(條件*),目標(biāo)區(qū)域)”,可以用單條件也可以用多條件。
除了可以逆序查找之外,Lookup相比Vlookup有優(yōu)勢的地方是Lookup相比Vlookup查詢效率更高,Vlookup查詢數(shù)據(jù)采用遍歷法,而lookup采用二分法查詢數(shù)據(jù)。 怎么更解這個(gè)呢?譬如說,現(xiàn)在有一個(gè)從1-100的數(shù)列,要找到100。Vlookup從1開始找一直找到100,找100次;而Lookpu會(huì)從數(shù)據(jù)中間開始找,先找51,51比100小,再接著找75,還是小再找87...,反正就是每次從剩下的數(shù)據(jù)中間開始找,lookup通過7次就可以找到100??雌饋砗孟褚矝]什么,但是,當(dāng)你表格里有幾百上千個(gè)Vlookup函數(shù)的時(shí)候再看看吧,那是卡的想哭啊。 剛才講到二分法了,其實(shí)利用Lookup函數(shù)的二分法,我們可以實(shí)現(xiàn)一些特殊的功能哦,例如: 公式的計(jì)算原理是:座是一個(gè)編碼較大的字符,當(dāng)我們輸入公式“=Lookup("座",A:A)的時(shí)候會(huì)返回A列最后一個(gè)文本,這是因?yàn)長ookup是采用二分法查找數(shù)據(jù),并且默認(rèn)數(shù)據(jù)是按升序排列的,越往后越大。所以,如果查找值大于數(shù)據(jù)列中的所有數(shù)據(jù),Lookup會(huì)一直向后查找直到找到最后一個(gè)值。 如果要查詢一列中最后一個(gè)數(shù)值是多少,我們可以用公式“=Lookup(2^16,A:A)”,公式原理也是一樣,2^16也是一個(gè)特別大的數(shù)。 在本例中我們就是利用Lookup返回選區(qū)中最后一個(gè)值的特性,為每個(gè)單元格建立一個(gè)可變選區(qū),選區(qū)的最后一個(gè)單元格為當(dāng)前單元格的上一格,這樣就實(shí)現(xiàn)了自動(dòng)填充的效果。
利用LOOKUP以上特性與其他函數(shù)進(jìn)行結(jié)合可以實(shí)現(xiàn)很多特殊的目的,例如,我們有一張表格提供了不同級(jí)別武將的帶兵數(shù)。然后有一個(gè)武將的介紹表格,包含武將的名字和級(jí)別,如何將武將的帶后數(shù)添加到武將的介紹表格?這時(shí)候我們就可以通過Lookup和Find函數(shù)來解決,如下: 公式的計(jì)算原理如下:首先用公式“0/FIND($F$3:$F$6,B3,1)”建立一個(gè)向量數(shù)組,F(xiàn)ind函數(shù)會(huì)在B3單元格的文本中依次查找是否包含F(xiàn)3:F6的內(nèi)容,如果有會(huì)返回位置值,否則近回錯(cuò)誤值,再用0/除以位置值和錯(cuò)誤值,得到一個(gè)由0和錯(cuò)誤值構(gòu)成的數(shù)組。再通過查找0值找到包含對(duì)應(yīng)武將的帶兵數(shù)。后面再加一個(gè)IFERROR進(jìn)行錯(cuò)誤判斷,如果Lookup沒有找到對(duì)應(yīng)的武將級(jí)別,會(huì)返回錯(cuò)誤值,也就是說如果計(jì)算出錯(cuò),說明當(dāng)前武將屬于其他級(jí)別武將,我們可以看到像吳國都督周瑜,在這個(gè)文本中不包含級(jí)別表中的任何級(jí)別,這時(shí)Lookup就會(huì)出錯(cuò),周瑜屬于其他級(jí)別武將,我們用IFERROR將其他級(jí)別數(shù)武將帶兵數(shù)賦予他。 利用LOOKUP和FIND組合我們還可以為簡稱找到對(duì)應(yīng)的全稱,如圖: 公式很長,看起來很費(fèi)勁的樣子,但知道公式的原理后,其實(shí)就很好理解了,最好是自己親自動(dòng)手操作一遍,這些技巧就都是你的了。
|
|