1、引用的數(shù)字區(qū)域一定要從小到大排序。雜亂的數(shù)字是無法準確查找到的。如下面A列符合模糊查找的前題,B列則不符合。
2、模糊查找的原理是:給一定個數(shù),它會找到和它最接近,但比它小的那個數(shù)。詳見下圖說明。
最后看一個實例:
例:如下圖所示,要求根據(jù)上面的提成比率表,在提成表計算表中計算每個銷售額的提成比率和提成額。
公式:=VLOOKUP(A11,$A$3:$B$7,2)
公式說明:
1、上述公式省略了VLOOKUP最后一個參數(shù),相當于把第四個參數(shù)設置成1或TRUE。這表示VLOOKUP要進行數(shù)字的區(qū)間查找。
2、圖中公式中在查找5000時返回比率表0所對應的比率1%,原因是0和10000與5000最接近,但VLOOKUP只選比查找值小的那一個,所以公式會返回0所對應的比率1%。
五、VLOOKUP的多條件查找
VLOOKUP函數(shù)需要借用數(shù)組才能實現(xiàn)多條件查找。
例:要求根據(jù)部門和姓名查找C列的加班時間。
分析:不是讓VLOOKUP本身實現(xiàn)多條件查找,而是想辦法重構(gòu)一個數(shù)組。多個條件可以用&連接在一起,同樣兩列也可以連接成一列數(shù)據(jù),然后用IF函數(shù)進行組合。
公式:{=VLOOKUP(A9&B9,IF({1,0},A2:A5&B2:B5,C2:C5),2,0)}
公式剖析:
1、A9&B9 把兩個條件連接在一起。把他們做為一個整體進行查找。
2、A2:A5&B2:B5,和條件連接相對應,把部分和姓名列也連接在一起,作為一個待查找的整體。
3、IF({1,0},A2:A5&B2:B5,C2:C5) 用IF({1,0}把連接后的兩列與C列數(shù)據(jù)合并成一個兩列的內(nèi)存數(shù)組。按F9后可以查看的結(jié)果為:
{“銷售張一”,1;“銷售趙三”,5;“人事楊五”,3;“銷售趙三”,6}
4、完成了數(shù)組的重構(gòu)后,接下來就是VLOOKUP的基本查找功能了,另外公式中含有多個數(shù)據(jù)與多個數(shù)據(jù)運算(A2:A5&B2:B5),,所以必須以數(shù)組形式輸入,即按ctrl+shift后按ENTER結(jié)束輸入。
六、VLOOKUP的多項查找
VLOOKUP一般情況下只能查找一個,那么多項該怎么查找呢?
例3 要求把如圖表中所有張一的消費金額全列出來
分析:經(jīng)過前面的學習,我們也有這樣一個思路,我們在實現(xiàn)復雜的查找時,努力的方向是怎么重構(gòu)一個查找內(nèi)容和查找的區(qū)域。要想實現(xiàn)多項查找,我們可以對查找的內(nèi)容進行編號,第一個出現(xiàn)的是后面連接1,第二個出現(xiàn)的連接2。。。
公式:{=VLOOKUP(B$9&ROW(A1),IF({1,0},$B$2:$B$6&COUNTIF(INDIRECT(“b2:b”&ROW($2:$6)),B$9),$C$2:$C$6),2,)}
公式剖析:
1、B$9&ROW(A1) 連接序號,公式向下復制時會變成B$9連接1,2,3
2、給所有的張一進行編號。要想生成編號,就需要生成一個不斷擴充的區(qū)域(INDIRECT(“b2:b”&ROW($2:$6)),然后在這個逐行擴充的區(qū)域內(nèi)統(tǒng)計“張一”的個數(shù),在連接上$B$2:$B$6后就可以對所有的張一進行編號了。
3、IF({1,0}把編號后的B列和C組重構(gòu)成一個兩列數(shù)組
通過以上的講解,需要知道,VLOOKUP函數(shù)的基本用法是固定的,要實現(xiàn)高級查找,就需要借助其他函數(shù)來重構(gòu)查找內(nèi)容和查找數(shù)組。
評論