EXCEL中l(wèi)ookup函數(shù)的經(jīng)典查找方式

1、主講老師：盧子老師講座主題：解析函數(shù)的經(jīng)典查找方式學(xué)習(xí)是需要技巧和經(jīng)驗的。感謝盧子老師為大家分享和交流他的實戰(zhàn)經(jīng)驗。下面是本期講座的全部內(nèi)容。本期講座包括兩部分內(nèi)容：第一，函數(shù)用法介紹；第二，通過實例講解函數(shù)經(jīng)典的條件查找解法，通用公式基本可以寫為：條件查找數(shù)組或區(qū)域或條件查找數(shù)組或區(qū)域）第一部分：函數(shù)用法介紹函數(shù)和函數(shù)是中最常用的兩個查找函數(shù)。函數(shù)能做到的函數(shù)同樣可以做到，而且可以做得更好。函數(shù)有兩種語法形式：向量和數(shù)組。本期就向量形式的展開交流和探討。向量形式的語法為：其中的參數(shù)意義如下:為所要查找的數(shù)值?？梢詾閿?shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。_為只包含一行或一列的區(qū)域。的數(shù)值可

2、以為文本、數(shù)字或邏輯值。的數(shù)值必須按升序排序：L否則，不能返回正確的結(jié)果。文本不區(qū)分大小寫。只包含一行或一列的區(qū)域，其大小必須與相同。比如其中的尺寸要與相同，且如果對應(yīng)列中的位置是的話，那么返回的將是的值。函數(shù)說明：第一，如果函數(shù)找不到，則查找中小于或等于的最大數(shù)值。這就是為何返回最后一個滿足條件的值的原理。第二，如果小于中的最小值，函數(shù)返回錯誤值。利用這個特性，我們可以用條件引用區(qū)域這樣一個通用公式來作查找引用。第二部分:函數(shù)實例運用運用一：模糊查找模糊查找的核心是第二個參數(shù)排序必須是升序，否則會導(dǎo)致查找值錯誤。下圖所示的表是按升序排序的，表沒有排序。BCHJ4番號俗稱番號俗稱540401H

3、12640411H276640402H12640412H276740403H12640571H142840411H27640572H142940412H27640401H1261040571H14240402H1261140572H14240403H1261240981H27643010R1021340983H27693657R1021443010R10293667H2161593657R1029366BH2761693667H21640981H2761793668H27640983H27618表1升序)表2沒有排序）2223番號俗稱番號俗稱24404034040325405724057226

4、9365793657分別在表和表下面對應(yīng)的單元格輸入公式。表的數(shù)據(jù)源是按升序排序的，根據(jù)函數(shù)用法：要查找的數(shù)據(jù)查找范圍結(jié)果，在單元格設(shè)置公式：，然后下拉得到正確結(jié)果。表的數(shù)據(jù)源是沒有排序的，在單元格輸入公式：$然后下拉，發(fā)現(xiàn)單元格得到的結(jié)果是2顯然不對。通過表的源數(shù)據(jù)可以看到單元格對應(yīng)的值應(yīng)該為單元格的值。為什么會出錯呢這就印證了第一部分的用法介紹中所講到的：的數(shù)值必須按升序排序：否則，不能返回正確的結(jié)果。文本不區(qū)分大小寫。模糊查找，數(shù)據(jù)源一定要以升序先進(jìn)行排序，否則就會出錯。在數(shù)據(jù)源沒有排序的情況下，如何才能查找到正確結(jié)果函數(shù)有一個經(jīng)典的條件查找解法，可以很好的解決此問題。條件查找數(shù)組或區(qū)域

5、或在第一部分有提到，通用公式基本可以寫為:條件查找數(shù)組或區(qū)域）公式中的、1等數(shù)字的含義是什么首先，條件是一組邏輯判斷的值或邏輯運算得到的由和組成或者與非組成的數(shù)組，因而：條件的作用是用于構(gòu)建一個由或者錯誤組成的值。比如數(shù)據(jù)源中能查找到對應(yīng)值就是，沒有就是l形式如：，查找到就0沒有就是錯誤值。如果于函數(shù)找不到即：，則它與的最大值即：匹配。中小于或等也就是說，要在一個由和組成的數(shù)組中查找，肯定找不到，因而將返回小于或等于的最大值也就是匹配。用大于的數(shù)來查找0肯定能查到最后一個滿足條件的。以上的原理，被俗稱為“以大欺小法”。這種技巧在函數(shù)上的運用是很常見的。利用上面的原理，不管有沒有排序，只要使用上

6、面的“以大欺小法”都能得到正確結(jié)果。比如上面實例中，在單元格輸入公式：，就可以了。運用二：精確查找第一，查找的數(shù)據(jù)沒有對應(yīng)值，可以利用函數(shù)屏蔽錯誤值。C51A=LCOKUP(1,0/(B5二$B$42:$B$45),$C$42:$C$45)BCDEFGHI41番號俗稱424J4O1H126434J412H2764493657R102454J572H14246表m4748查找的數(shù)據(jù)沒有對應(yīng)力S利用SNAQSER尺。尺涵數(shù)屏蔽錯誤值4950番號俗稱番號俗稱514J4O1H126.K40401H126524J402，#N/A5404025393657R102V93657R10254#N/A如上圖所示

7、，表是數(shù)據(jù)源，在下面左邊根據(jù)“番號”查找“俗稱”。單擊單元格，輸入公式，然后下拉可以看到下面的和單元格出現(xiàn)錯誤值。這種情況可以利用函數(shù)屏蔽錯誤值。只要在公式外面嵌套個（這樣的形式就可以把錯誤值屏蔽。在單元格，輸入這樣的公式：2下拉，就可以屏蔽錯誤值Y。將錯誤值屏蔽了，表格就好看多了。上面公式中，,是顯示空的意思，錯誤就顯示空，沒有就查找。第二，借助錯誤值來判定產(chǎn)品是否存在。下圖所示根據(jù)左邊的數(shù)據(jù)源，來判定右邊對應(yīng)的數(shù)據(jù)是否在番號列中。H62H-ThT%gx)OKUP(l,”362二$B$E2:$B$T4)/否一是)BCDEFGH61番號俗稱番號判定情況6240401H12640401|是I63

8、40402H12640402是6440403H12640403是6540411H27640404否6640412H27640405否6740571H142409S1是6840572H14240933是6940981H27640905否7040983H2769B877否7143010R1029B87B否7293657R10298879否7393667H216988SO否7493668H27698831否75表4只需要嵌套一個函數(shù)就可以做到，如果沒有存在就錯誤，有存在就這樣的形式。在單元格輸入公式：否是，下拉就即可得出結(jié)果?！皥D啥”網(wǎng)友問：與函數(shù)的區(qū)別。只屏蔽錯誤，屏蔽所有錯誤。Ayr-第三，函數(shù)

9、多條件查找。E112bA二LlTEJPQ,V(SI112=$B5112：5Bfl22(SJ112Bc.VCEFGIJ、_111俗稱ir單號H單數(shù)O完成情況俗稱訂單號訂單數(shù)尾數(shù)112R10210C429150CN3R1021J0430E0301113R10210C430300C0OKH27E門口51n11410C51775C0OKR1021J042911510C52275C123N3H12E0601116H?610C523120C0OKH12E0603117H12610C6J280C0OK118H1261W100C0OK如上圖所示，根據(jù)“俗稱”和“訂單號”來查找“訂單數(shù)”和“尾數(shù)”，可以套用這樣

10、的公式：條件。引用區(qū)域，用或?qū)⒏鱾€條件連接起來，就是和的意思。此題有兩種方法：第一，在單元格輸入公式：,復(fù)制公式就可以得到結(jié)果。第二，另外也可以使用這個公式：第四，含某個字符查找。G128/妥二LOOKUP(1,0/(FIND($F12&$H31),B$128:B$131)BCEFGIJK127俗稱番號字段俗稱128MC48897R1R102.129LT-R48906HH142130R102936571314nnH14240572按照上圖所示，根據(jù)左邊的數(shù)據(jù)源，來對含有某個字符進(jìn)行查找。單擊單元格，輸入公式：，就可以得到結(jié)果。函數(shù)與函數(shù)對比：第一，在多條件查找方面，就能看出函數(shù)好用。用多條件查

11、找，最簡單的方法就是借用輔助列。第二，函數(shù)對于反向查找是需要嵌套其余函數(shù)才能實現(xiàn)，而函數(shù)沒有正反之分，因此在這方面函數(shù)會更加容易實現(xiàn)。第三，在查找字符方面，可以使用號類通配符。是不支持通配符的，但可以使用查找字符，數(shù)據(jù)源區(qū)域的形式代替。本講座有關(guān)的源文件請在論壇下載:講座主題：函數(shù)運用下在第期的技術(shù)講座中，已經(jīng)學(xué)習(xí)了函數(shù)的用法，也分析了一些基礎(chǔ)例子。無言老師在本期講座通過幾個實例來幫助大家更深入的了解函數(shù)的使用。函數(shù)運用一：函數(shù)第三個參數(shù)返回的列數(shù)可以通過函數(shù)定位查找的返回所需的列數(shù)，和嵌合使用。下圖所示的1單元格區(qū)域是源數(shù)據(jù)。需要查找的數(shù)據(jù)表列標(biāo)題，元數(shù)據(jù)表列標(biāo)題范圍，這是精確查找定位第一次

12、出現(xiàn)的列位置。自3匚DE7E11鼾姓名性盤部門蚓出生年月地址電法-1把張9會鞋舊罔仍上在楣京芹二知”二磋MOJO硼出】強(qiáng)思業(yè)務(wù)鞋舊游旺上茬T虱可法弼二樓哪加000：工1313粕J-市籍艮聯(lián)閆朗上新龍建桀三座ioeojo-ooo6151313袞,蘇鷹閆朗上花莖1憾中央詞*三磋iC20J0-U05Ie-1223莪四業(yè)務(wù)鞋因定脹二標(biāo)趣瓷號醋iC20)2：J0-C04-T11?歸J庫吟p-海不合:護(hù)-1?用工把ifownn-nni請使用函數(shù)解出下圖所示的單元格中編號所對應(yīng)的其余單元格的值。單擊-單元格右下角的向下三角形，可以更換選擇其余的編號。C24：-01452ABCDE2324編號1452.姓名2

13、5性別部門26職稱出生年月27地址28電話在單元格輸入公式：即可得到答案。同樣的方法可以求出其余單元格的值，只是把定位的單元格地址更改一下就好了。函數(shù)運用二：原工作表存在通配符的查找。如下圖所示，原工作表列中存在”通配符。AECDEFG219一月二月三月四月五月六月22D電視砌3,6004,2004,8004,5003酬221電冰瑞2,4002,6002.5503,0003,8004J000099J&湄士知QRnribnnn4omrjnriannn通過上圖的源數(shù)據(jù)，要對下圖的C、列對應(yīng)的數(shù)據(jù)進(jìn)行查找，該如何設(shè)計公式呢單擊單元格，輸入以下公式：，然后向右和向下拉即可查找相應(yīng)出相應(yīng)的數(shù)據(jù)。無言老師

14、提到，還可以使用下面這樣的公式，也能實現(xiàn)。通過這個案例，可以看到和的替換作用，號替換的可以為某個文本之前或之后的所有字符，號代替的只是一個字符。通常來說，通配符用的比較多的還是號。函數(shù)運用三：反向查找。函數(shù)通常只能從左往右的垂直方向有序查找。如果需要用到逆序，反向查找就需要使用或其中一個函數(shù)嵌套使用。這兩個函數(shù)在函數(shù)的使用通常是這樣的形式：查找內(nèi)容的列返回內(nèi)容的列和查找內(nèi)容的列返回內(nèi)容的列返回內(nèi)容的列返回內(nèi)容的列）返回內(nèi)容的列需要幾列就寫入幾列】。下圖所示的是5單元格區(qū)域為源數(shù)據(jù)，為了演示需要，將其中的部分行區(qū)域隱藏了。ABCDEF258職工編號姓名性別年齡職稱工讀2592003001王1男3

15、5高級工程師58992602003002王2男28高級工程師48902612003003王3女24高級工程師49002622003004王4男18工程師65002632003005王5男35技師36002832003025王25男79高級工程師26542842003026王2日男28助理工程師68422852003027王27男27技師36542862003028王2W男38助理工程師48652872003029王29女30高級工程師32582882003030王3口男23工程師6548HIJ287姓名年齡工衰283王3289王12290王19291王1T292王1呂如下圖所示，已知姓名列數(shù)據(jù)，使用函數(shù)查找年齡和工資列的數(shù)據(jù)。QC9要完成此題，有兩種方法可以實現(xiàn):第一，使用嵌套實現(xiàn)。單擊年齡下方的單元格，輸入公式:，然后下拉。單擊工資下方的單元格，輸入公式:提示:，然后下拉。函數(shù)在這里只能用到個條件，因此使用有一定局限性。下面我們就使用另外一種方法來實現(xiàn)，即借助函數(shù)。其實函數(shù)可以做到的同樣能做到，而且比更靈活。-第二二二，函數(shù)和的嵌套使用的公式:同樣在年齡下方的單元格，輸入公式:然后下拉。F9，俗稱“抹黑”，查看公