一区二区三区日韩精品-日韩经典一区二区三区-五月激情综合丁香婷婷-欧美精品中文字幕专区

分享

圖例細說vlookup函數(shù)(想學不會都難)

 L羅樂 2017-11-18

一、 vlookup基本用法
    

目標:根據(jù)品名找到對應(yīng)的價格;

注意事項:品名必須位于價格的前面(左側(cè)),品名與價格一一對應(yīng) ;


公式解讀:

=VLOOKUP(D2,A2:B5,2,0) ——根據(jù)(查找品名)提取(數(shù)據(jù)區(qū))的,第二列單價數(shù)據(jù),采用精確匹配0; 
=VLOOKUP(查找內(nèi)容,查找區(qū)域,需要的數(shù)據(jù)在查找區(qū)域的列數(shù),準確查找)           
上面的例子含義為:根據(jù)d2單元格的內(nèi)容(稿紙),在A2:B5這個區(qū)域里找對應(yīng)的價格,需要滿足兩個條件,品名在這個區(qū)域的第一列,并且每個品名只有一個;要找的數(shù)據(jù)(價格)在這個區(qū)域的第二列,按照品名準確查找。




這個例子要求根據(jù)工號找對應(yīng)的籍貫,公式為=VLOOKUP(H14,C14:F17,3,0),這個公式里四個參數(shù)的含義根據(jù)前面的解釋自己理解一下,如果明白了,再看后面的內(nèi)容,這一點很重要!


了解了vlookup四個參數(shù)的基本含義后,就可以使用這個函數(shù)來為我們解決一些數(shù)據(jù)引用的問題了,但是在遇到需要下拉公式的時候,還有一個地方需要注意,就是范圍的絕對引用(引用的概念可以看這個帖子【excel基礎(chǔ):相對引用與絕對引用】)



看上面這個圖,KT003的籍貫可以正確找到,但是公式拉下來,KT001的籍貫就錯誤了,觀察KT001這里的公式可以看到,公式發(fā)生了變化,=VLOOKUP(H15,C15:F18,3,0),范圍從C14:F17變成了C15:F18,而KT001已經(jīng)不在這個范圍了,所以就找不到。


為了解決這樣的錯誤,我們需要加工一下公式,改成=VLOOKUP(H14,$C$14:$F$17,3,0),或者=VLOOKUP(H14,C:F,3,0),朋友們可以自己試試,理解一下固定范圍的含義。


通過以上的敘述,對于同一個表格中的vlookup使用相信應(yīng)該明白了其中的竅門,但是工作中往往會發(fā)生不在同一個表中引用的情況,比如明細表是全部的數(shù)據(jù),而結(jié)果表里只需要引用一部分數(shù)據(jù),這就是下面要說明的情況。
常見錯誤總結(jié):
1、范圍未鎖定:常見于公式需要下拉或者橫拉的情況,配合引用方式靈活處理即可;
2、查找內(nèi)容和數(shù)據(jù)源格式不對,常見于數(shù)據(jù)源為其他程序?qū)С龅膬?nèi)容,數(shù)字類型為文本或者單元格內(nèi)有空格等情況,需要仔細核查;
3、vlookup第四參數(shù)使用錯誤:vlookup的第四參數(shù)是可以省略的,但是逗號不能省略,例如將=VLOOKUP(H14,C14:F17,3,0)寫成=VLOOKUP(H14,C14:F17,3,),是可以的,但是=VLOOKUP(H14,C14:F17,3),這樣就不對了,省略最后一個逗號和第四參數(shù)寫成1是一樣的效果,就是模糊匹配。關(guān)于精確匹配和模糊匹配不在這里解釋,可以自己試試效果。這個概念將在其他帖子專門說明。


二、vlookup跨表引用
 
如上圖,就是一個跨sheet的引用,公式為:=VLOOKUP(A2,明細表!C:F,4,0),具體操作方法看下面的動畫

通常情況下不建議使用跨文件的引用,最好能將數(shù)據(jù)源與結(jié)果放在同一個文件的不同sheet,如果一定要跨文件的話,方法也是一樣的。


三、vlookup連續(xù)使用(第三參數(shù)不固定)
掌握了跨表引用的方法后提高工作效率是一定的,但是有時候還是覺得不方便,比如下面的這個情況:

要引用的不是一列內(nèi)容了,雖然都可以用vlookup,但是每列都要重新寫公式,如果列數(shù)更多的話也是挺麻煩的一件事,這里就需要我們對vlookup的第三參數(shù)來做一個加工了。

思路是這樣的,對于年齡的引用,=VLOOKUP(I2,C1:F13,2,0),第三參數(shù)是2,這個容易明白,如果想通過一個公式橫著拉過去可以引用三列的值,只要我們可以讓這個2拉的時候可以變成3,4,就行了,因此,公式里套用一個函數(shù)就可以實現(xiàn)這個目的。

j2的公式修改為:=VLOOKUP($I2,$C$1:$F$13,COLUMN(B1),0),然后橫拉下拉就可以了,為了確保第一參數(shù)的準確性,和第二參數(shù)范圍的固定,我們分別做了混合引用和絕對引用,這個之前已經(jīng)說過了。


這個方法使用有個前提就是引用的列和數(shù)據(jù)源的列順序位置都是一樣的,如果只是引用數(shù)據(jù)源的一部分列或者列的順序不一樣就不能這樣做了,比如下面的這個情況:

公式為:=VLOOKUP($I2,$C$1:$F$13,MATCH(J$1,$C$1:$F$1,0),0),這個就完全可以實現(xiàn)一個公式靈活匹配了,關(guān)于match可以看看【【函數(shù)學堂】MATCH函數(shù)面面觀(上)、【函數(shù)學堂】MATCH函數(shù)面面觀(下)】,靈活運用vlookup和match需要多練習。通過上面這三個討論,對于單個條件的引用基本都可以解決了,但是實際當中很多時候是兩個條件甚至三個條件一起作為引用條件的,這就是下面要說的問題。


四、vlookup合并條件引用 

要求找到業(yè)務(wù)員在指定客戶的銷售額,找就是兩個條件的引用實例,對于初學者而言,建議使用輔助列的方法來處理,具體如下:

這里加了一個輔助列,輔助列用的=B2&C2下拉得到,目的就是把兩個條件合并為一個(&在這里是合并兩個單元格的內(nèi)容),然后在vlookup里把第一個參數(shù)也用&連起來,公式如圖。如果要求不能用輔助列的話,那么只能用到數(shù)組公式了,這個公式初學者不容易理解,能夠照著套用就行了

=VLOOKUP(E2&F2,IF({1,0},A2:A7&B2:B7,C2:C7),2,0),輸入或復(fù)制公式后按著ctrl、shift按回車,會自動出來大括號,原理就是用if把合并后的條件列,與銷售額列看做兩列的一個范圍,所以vlookup的第三參數(shù)改為2。



這里的if用choose也行,=VLOOKUP(E2&F2,CHOOSE({1,2},A2:A7&B2:B7,C2:C7),2,0),也是要三鍵結(jié)束的數(shù)組公式。
關(guān)于上面的這兩種用法,需要理解if和choose的作用,這里不做深究,不過這個用法通常還可以用在反向引用,下面說說這個問題。


五、vlookup反向引用

例如上面這個例子,根據(jù)工號來找姓名,就是反向,所謂反向的意思就是查找的條件列在要找的數(shù)據(jù)列右側(cè),這個直接用vlookup是不行的,解決這類問題就得用vlookup if或者vlookup choose來完成,不過不用三鍵結(jié)束哦,因為是單條件的,以下寫出來四種公式,對照理解以下其中的異同,也可以幫助理解這種用法的原理。就上面這個問題,四個公式得到的效果完全一樣:
=VLOOKUP(G2,IF({1,0},$B$2:$B$5,$A$2:$A$5),2,0)對照=VLOOKUP(G2,IF({0,1},$A$2:$A$5,$B$2:$B$5),2,0)理解,看看差別在哪,
=VLOOKUP(G2,CHOOSE({2,1},$A$2:$A$5,$B$2:$B$5),2,0)對照=VLOOKUP(G2,CHOOSE({1,2},$B$2:$B$5,$A$2:$A$5),2,0)理解。


六、vlookup排除錯誤引用
通過以上的敘述,相信對于vlookup應(yīng)該有了比較全面的了解,可是美中不足的是當找不到所要查找的數(shù)據(jù)是,往往會得到,有沒有辦法讓找不到數(shù)據(jù)的時候不要出現(xiàn)亂碼(其實是錯誤代碼),回答是肯定的,只要我們借助一個函數(shù)就可以了,這個函數(shù)就是iferror??梢蕴子眠@個格式來處理公式=iferror(vlookup(),'需要提示的文字'),例如,=IFERROR(VLOOKUP(E12,B12:C15,2,0),'')這個公式的意思就是當找不到對應(yīng)數(shù)據(jù)的時候顯示空白,兩個'(英文狀態(tài)的雙引號)之間沒有內(nèi)容,或者可以這樣寫,=IFERROR(VLOOKUP(E12,B12:C15,2,0),'無此人'),當找不到對應(yīng)姓名的時候顯示無此人等等。
需要說明一點,iferror這個函數(shù)在Excel2003里沒有,如果用2003要達到這個效果需要一個比較長一點的公式,格式如下:
=if(iserror(vlookup(),“”,vlookup()),例如:=IF(ISERROR(VLOOKUP(G2,B1:C5,2,0)),'',VLOOKUP(G2,B1:C5,2,0))。


七、vlookup多范圍引用
當我們掌握了vlookup排除錯誤顯示的方法后,可以來探討一個多范圍引用的例子,如下圖所示:
  
兩個班的數(shù)據(jù)是分開存放的,但是我們現(xiàn)在要找的名單可能在其中的任何一個里面,用=VLOOKUP(A2,'1班'!A:D,4,0)只能找到存在于1班的成績,怎么樣才能完全找到所有的成績呢?換個思路來想想,也就是說當在1班找不到的時候,就去找2班,結(jié)合iferror函數(shù)可以實現(xiàn):=IFERROR(VLOOKUP(A2,'1班'!A:D,4,0),VLOOKUP(A2,'2班'!A:D,4,0))用這個公式找到的結(jié)果如下:

再完善一下: =IFERROR(IFERROR(VLOOKUP(A2,'1班'!A:D,4,0),VLOOKUP(A2,'2班'!A:D,4,0)),'姓名有誤請核實')。至于這個公式的意思如果前面的內(nèi)容你都明白了相信不難理解。
最終一句話:一定要動手練習,動腦思考,當你明白的時候發(fā)現(xiàn)真的很容易。


    本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間,所有內(nèi)容均由用戶發(fā)布,不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式、誘導購買等信息,謹防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,請點擊一鍵舉報。
    轉(zhuǎn)藏 分享 獻花(0

    0條評論

    發(fā)表

    請遵守用戶 評論公約

    類似文章 更多

    国产视频福利一区二区| 国产白丝粉嫩av在线免费观看| 国产免费观看一区二区| 亚洲一区二区三区有码| 日韩精品一区二区毛片| 99久热只有精品视频免费看| 在线亚洲成人中文字幕高清| 国产亚洲视频香蕉一区| 麻豆一区二区三区精品视频| 欧美一本在线免费观看| 国产人妻熟女高跟丝袜| 五月综合激情婷婷丁香| 亚洲一级二级三级精品| 欧美黑人在线精品极品| 五月激情五月天综合网| 日韩国产精品激情一区| 国产亚洲精品久久久优势| 欧美日韩视频中文字幕| 日韩欧美好看的剧情片免费| 国产欧美一区二区色综合| 日本一本在线免费福利| 欧美日韩三区在线观看| 激情三级在线观看视频| 久久本道综合色狠狠五月| 99秋霞在线观看视频| 中文字幕久久精品亚洲乱码| 欧美大黄片在线免费观看| 精品一区二区三区免费看| 亚洲国产av一二三区| 免费观看一区二区三区黄片| 亚洲最新的黄色录像在线| 亚洲一区二区三在线播放| 国产高清一区二区不卡| 欧美日韩成人在线一区| 国产精品一区二区三区黄色片| 欧美日韩国产福利在线观看| 在线中文字幕亚洲欧美一区 | 一本色道久久综合狠狠躁| 亚洲欧美日韩精品永久| 日本妇女高清一区二区三区| 日本三区不卡高清更新二区|