VLOOKUP 的人生巔峰:跨多表多文件查找!
?
作者:Mutou來源:部落窩教育發(fā)布時間:2023-08-04 09:20:47點擊:1019
平常接觸最多的是用VLOOKUP在單個表中查找,是最基礎(chǔ)的用法。今天說它的巔峰表現(xiàn):跨多表跨多文件(工作簿)查找。
前面說了跨多表匯總求和(有興趣的可看文末推薦),今天說說用Vlookup跨多表和跨多文件(工作簿)查詢。
1.從最簡單的跨單個表、跨單個文件開始
1)跨單個表
譬如:在sheet1表查詢 “成績表”中的成績。
公式:=VLOOKUP(A2,成績表!A:E,5,0)
2)跨單個文件查找
譬如,在兩個不同文件(工作簿)中進(jìn)行查詢。
公式:=VLOOKUP(A2,[1組.xlsx]Sheet1!$A$2:$E$10,5,0)
解析:
相比跨單個工作表查詢,區(qū)別是引用部分增加了用方括號括起來的文件名“[1組.xlsx]”。
注:
(1)跨文件查詢,被引用的文件必須打開,否則出錯。
(2)查詢結(jié)束,可以選擇性粘貼為值把查詢結(jié)果固定下來。
2.跨多表:被查對象在多個表中都存在
譬如:劉海鷗的成績分別記載在4張月成績表中,現(xiàn)在查詢他每月的成績。
公式:=VLOOKUP($B$1,INDIRECT($A3&"月成績!A:E"),5,0)
解析:相比跨單個工作表中查詢,用INDIRECT函數(shù)實現(xiàn)動態(tài)引用工作表。
3.跨多表:被查詢對象不確定在哪個工作表中
譬如:在6張不同組別的工作表中查詢不知具體是哪組的人員成績。
長但易理解的公式:
=IFERROR(VLOOKUP(A2,'1組'!A:E,5,0),
IFERROR(VLOOKUP(A2,'2組'!A:E,5,0),
IFERROR(VLOOKUP(A2,'3組'!A:E,5,0),
IFERROR(VLOOKUP(A2,'4組'!A:E,5,0),
IFERROR(VLOOKUP(A2,'5組'!A:E,5,0),
VLOOKUP(A2,'6組'!A:E,5,0))))))
解析:
一個一個的試著查。首先在1組中查,若結(jié)果是正常值則查詢結(jié)束,若結(jié)果是錯誤值(表示1組中沒有被查人員)則在2組中查,依此類推。
短但嵌套復(fù)雜的公式:
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT(ROW($1:$6)&"組!A:A"),A2),ROW($1:$6)&"組")&"!A:E"),5,0)
解析:
①COUNTIF(INDIRECT(ROW($1:$6)&"組!A:A"),A2),統(tǒng)計被查詢?nèi)嗽诿繌埞ぷ鞅碇械某霈F(xiàn)次數(shù),結(jié)果是一組0和1的數(shù)據(jù)。0表示工作表沒有被查人員,1表示有。
②LOOKUP(1,0/①,ROW($1:$6)&"組"),LOOKUP精確查找套路(點此可查看),返回包含被查人員的工作表名。
③INDIRECT(②&"!A:E"),引用含被查人員的工作表數(shù)據(jù)
④VLOOKUP(A2,③,5,0),查找總分
4.跨多個文件查找
兩種情況。
1)被查詢?nèi)藛T在每個文件中都有
譬如,4個文件中分別記錄了同一批人員不同月的成績。
4個文件
文件數(shù)據(jù)結(jié)構(gòu)
如查劉海鷗各月總分:
公式:
=VLOOKUP($B$1,INDIRECT("["&$A3&"月成績.xlsx]sheet1!A:E"),5,0)
2)被查詢?nèi)藛T不確定在哪個文件中
譬如,有6個文件,分別記錄了不同組別的人員成績。
6個文件
文件數(shù)據(jù)結(jié)構(gòu)
現(xiàn)在需要查詢?nèi)藛T的總分。
公式:
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT
("["&ROW($1:$6)&"組"&".xlsx]sheet1!a:a"),A2),"["&ROW($1:$6)&"組"&".xlsx]sheet1!")&"a:e"),5,0)
關(guān)于VLOOKUP跨多表跨多文件查找就說這么多,若還有不明白的歡迎留言,小窩為你解答。
另外VLOOKUP升級了用法,譬如第一參數(shù)支持?jǐn)?shù)組、第三參數(shù)支持?jǐn)?shù)組,如此可以一個公式查找多個條件返回多個值。有興趣了解的可以點此查看VLOOKUP四大參數(shù)最新用法。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育Excel精品好課任你選擇!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
用R1C1樣式完成結(jié)構(gòu)不同的表格跨多表求和
版權(quán)申明:
本文作者Mutou;部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!