vlookup函數(shù)最高級的應(yīng)用:多表多文件查找
一、跨多工作表查找
【例】工資表模板中,每個部門一個表。
在查詢表中,要求根據(jù)提供的姓名,從銷售~綜合5個工作表中查詢該員工的基本工資。
分析:
如果,我們知道A1是銷售部的,那么公式可以寫為:
=VLOOKUP(A2,銷售!A:G,7,0)
如果,我們知道A1可能在銷售或財(cái)務(wù)表這2個表中,公式可以寫為:
=IFERROR(VLOOKUP(A2,銷售!A:G,7,0),VLOOKUP(A2,財(cái)務(wù)!A:G,7,0))
意思是,如果在銷售表中查找不到(用iferror函數(shù)判斷),則去財(cái)務(wù)表中再查找。
如果,我們知道A1可能在銷售、財(cái)務(wù)或服務(wù)表中,公式可以再次改為:
=IFERROR(VLOOKUP(A2,銷售!A:G,7,0),IFERROR(VLOOKUP(A2,財(cái)務(wù)!A:G,7,0),VLOOKUP(A2,!A:G,7,0)))
意思是從銷售表開始查詢,前面的查詢不到就到后面的表中查找。
如果,有更多的表,如本例中5個表,那就一層層的套用下去。這也是我們今天提供的VLOOKUP多表查找
方法1:
=IFERROR(VLOOKUP(A2,服務(wù)!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,綜合!A:G,7,0),IFERROR(VLOOKUP(A2,財(cái)務(wù)!A:G,7,0),IFERROR(VLOOKUP(A2,銷售!A:G,7,0),"無此人信息")))))
------------------------------------------
如果你想簡化一下公式,以適合在更多的表中查謁,再提供一個思路,只是公式簡單了,理解起來卻難了。這里你只需要學(xué)會怎么修改公式套用就可以了。
方法2:
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售";"服務(wù)";"人事";"綜合";"財(cái)務(wù)"}&"!a:a"),A2),{"銷售";"服務(wù)";"人事";"綜合";"財(cái)務(wù)"})&"!a:g"),7,0)
你只需要修改以下部分,就可以直接套用
A2:查找的內(nèi)容
{""}:大括號內(nèi)是要查找的多個工作表名稱,用逗號分隔
a:a :本例是姓名在各個表中的A列,如果在B列則為b:b
a:g :vlookup查找的區(qū)域
7:是vlookup第3個參數(shù),相對應(yīng)的列數(shù)。你懂的。
公式思路說明:
1、確定員工是在哪個表中。這里利用countif函數(shù)可以多表統(tǒng)計(jì)來分雖計(jì)算各個表中該員工存在的個數(shù)。
2、利用lookup(1,0/(數(shù)組),數(shù)組) 結(jié)構(gòu)取得工作表的名稱
3、利用indirec函數(shù)把字符串轉(zhuǎn)換成單元格引用。
4、利用vlookup查找。
二、跨多文件查找
跨多個文件查找,估計(jì)你搜遍網(wǎng)絡(luò)也找不到,這也是首次編寫跨多文件查找公式。其實(shí)原理和跨多表查找一樣,也是借助lookup等函數(shù)實(shí)現(xiàn)。
文件夾中有N個倉庫產(chǎn)品表格,需要在“查詢”文件完成查詢
倉庫表樣式
在查詢表中設(shè)置公式,根據(jù)產(chǎn)品名稱從指定的文件中sheet1工作表查詢?nèi)霂靻蝺r
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"倉庫1";"倉庫2";"倉庫3"}&".xlsx]sheet1!a:a"),A2),"["&{"倉庫1";"倉庫2";"倉庫3"}&".xlsx]sheet1")&"!a:b"),2,0)。
補(bǔ)充:
vlookup函數(shù)的多文件查找,同樣可以用iferror+vlookup的模式,公式雖然長,但容易理解且公式不容易出錯。如果你有一定基礎(chǔ),倒可以試試第2種方法。
另外,如果工作表或excel文件有幾十個或更多,就需要使用宏表函數(shù)Get.workbook來獲取所有工作表的名稱和用Files獲取所有excel文件名稱,然后應(yīng)用到公式中。
繼續(xù)查找其他問題的答案?
-
Excel 2019如何使用復(fù)制、粘貼命令插入Word數(shù)據(jù)?(視頻講解)
2021-01-257次播放
-
Excel 2019如何制作數(shù)據(jù)透視表?(視頻講解)
2021-01-2227次播放
-
Excel 2019如何設(shè)置相對引用?(視頻講解)
2021-01-2210次播放
-
2021-01-2212次播放
-
Excel 2019如何使用命令復(fù)制公式?(視頻講解)
2021-01-226次播放
-
2021-01-223次播放
-
2021-01-222次播放
-
Excel 2019如何利用開始選項(xiàng)卡進(jìn)入篩選模式?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置指定數(shù)據(jù)的篩選?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置自定義排序?(視頻講解)
2021-01-222次播放
點(diǎn)擊加載更多評論>>