Excel多工作薄多表查找:Vlookup函数的高阶用法 | 您所在的位置:网站首页 › 两个excel表格找不同内容 › Excel多工作薄多表查找:Vlookup函数的高阶用法 |
编按:平常接触最多的是用VLOOKUP在单个表中查找,是最基础的用法。今天说它的巅峰表现:跨多表跨多文件(工作簿)查找。 前面说了跨多表汇总求和(有兴趣的可看文末推荐),今天说说用Vlookup跨多表和跨多文件(工作簿)查询。 1、从最简单的跨单个表、跨单个文件开始 1)跨单个表 譬如:在sheet1表查询 “成绩表”中的成绩。 公式:=VLOOKUP(A2,成绩表!A:E,5,0) 2)跨单个文件查找 譬如,在两个不同文件(工作簿)中进行查询。 公式:=VLOOKUP(A2,[1组.xlsx]Sheet1!$A$2:$E$10,5,0) 解析: 相比跨单个工作表查询,区别是引用部分增加了用方括号括起来的文件名“[1组.xlsx]”。 注: (1)跨文件查询,被引用的文件必须打开,否则出错。 (2)查询结束,可以选择性粘贴为值把查询结果固定下来。 2、跨多表:被查对象在多个表中都存在 譬如:刘海鸥的成绩分别记载在4张月成绩表中,现在查询他每月的成绩。 公式:=VLOOKUP($B$1,INDIRECT($A3&"月成绩!A:E"),5,0) 解析:相比跨单个工作表中查询,用INDIRECT函数实现动态引用工作表。 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组中查,若结果是正常值则查询结束,若结果是错误值(表示1组中没有被查人员)则在2组中查,依此类推。 短但嵌套复杂的公式: =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),统计被查询人在每张工作表中的出现次数,结果是一组0和1的数据。0表示工作表没有被查人员,1表示有。 ②LOOKUP(1,0/①,ROW($1:$6)&"组"),返回包含被查人员的工作表名。 ③INDIRECT(②&"!A:E"),引用含被查人员的工作表数据 ④VLOOKUP(A2,③,5,0),查找总分 4、跨多个文件查找 两种情况。 1)被查询人员在每个文件中都有 譬如,4个文件中分别记录了同一批人员不同月的成绩。 如查刘海鸥各月总分: 公式: =VLOOKUP($B$1,INDIRECT("["&$A3&"月成绩.xlsx]sheet1!A:E"),5,0) 2)被查询人员不确定在哪个文件中 譬如,有6个文件,分别记录了不同组别的人员成绩。 现在需要查询人员的总分。 公式: =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) 关于VLOOKUP跨多表跨多文件查找就说这么多,若还有不明白的欢迎留言,小窝为你解答。 另外VLOOKUP升级了用法,譬如第一参数支持数组、第三参数支持数组,如此可以一个公式查找多个条件返回多个值。 做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择! 学习交流请加微信hclhclsc进群领取资料 相关推荐: 再说两种不同情况的跨多表求和 用R1C1样式完成结构不同的表格跨多表求和 LOOKUP函数经典用法7例 与软件安装进度条相似的完成率图表 版权申明: 本文作者Mutou;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。 |
CopyRight 2018-2019 实验室设备网 版权所有 |