vlookup函数最高级的应用:多表多文件查找
发布时间:2023-03-17 06:42:50
关于vlookup函数的教程本站已发过入门+初级+进阶+高级的。在网上也可以搜到很多关于vlookup的教程,具体详见:vlookup函数 – vlookup函数的使用方法_vlookup函数的操作实例。但这些教程中都缺了vlookup的一个关键应用:跨多表多文件查找。今天本文将讲述了vlookup函数最高级的应用:多表多文件查找。
一、跨多工作表查找
【例】工资表模板中,每个部门一个表。
在查询表中,要求根据提供的姓名,从销售~综合5个工作表中查询该员工的基本工资。
分析:
如果,我们知道A1是销售部的,那么公式可以写为:
=VLOOKUP(A2,销售!A:G,7,0)
如果,我们知道A1可能在销售或财务表这2个表中,公式可以写为:
=IFERROR(VLOOKUP(A2,销售!A:G,7,0),VLOOKUP(A2,财务!A:G,7,0))
意思是,如果在销售表中查找不到(用iferror函数判断),则去财务表中再查找。
如果,我们知道A1可能在销售、财务或服务表中,公式可以再次改为:
=IFERROR(VLOOKUP(A2,销售!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),VLOOKUP(A2,!A:G,7,0)))
意思是从销售表开始查询,前面的查询不到就到后面的表中查找。
如果,有更多的表,如本例中5个表,那就一层层的套用下去。这也是我们今天提供的VLOOKUP多表查找
方法1:
=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息")))))
——————————————
如果你想简化一下公式,以适合在更多的表中查谒,再提供一个思路,只是公式简单了,理解起来却难了。这里你只需要学会怎么修改公式套用就可以了。
方法2:
=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)
你只需要修改以下部分,就可以直接套用
A2:查找的内容
{""}:大括号内是要查找的多个工作表名称,用逗号分隔
a:a :本例是姓名在各个表中的A列,如果在B列则为b:b
a:g :vlookup查找的区域
7:是vlookup第3个参数,相对应的列数。你懂的。
公式思路说明:
1、确定员工是在哪个表中。这里利用countif函数可以多表统计来分虽计算各个表中该员工存在的个数。
2、利用lookup(1,0/(数组),数组) 结构取得工作表的名称
3、利用indirec函数把字符串转换成单元格引用。
4、利用vlookup查找。
二、跨多文件查找
跨多个文件查找,估计你搜遍网络也找不到,这也是首次编写跨多文件查找公式。其实原理和跨多表查找一样,也是借助lookup等函数实现。
文件夹中有N个仓库产品表格,需要在“查询”文件完成查询
仓库表样式
在查询表中设置公式,根据产品名称从指定的文件中sheet1工作表查询入库单价
=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)。
补充:
vlookup函数的多文件查找,同样可以用iferror+vlookup的模式,公式虽然长,但容易理解且公式不容易出错。如果你有一定基础,倒可以试试第2种方法。
另外,如果工作表或excel文件有几十个或更多,就需要使用宏表函数Get.workbook来获取所有工作表的名称和用Files获取所有excel文件名称,然后应用到公式中。
猜你喜欢
- 当我们在使用excel做表格或是看文件时,有时表格的内容很多,我们不能在一页里看完,当我们看下面的页内容时上面的对应的行标或是列标就被隐藏了
- Word文本框在很多地方都运用的很广泛,但是你知道文本框的几个使用技巧么?今天就带大家一起学习文本框在word排版中的各种使用技巧的方法。具
- Excel表格如何改变表格颜色?下面是小编为大家精心整理的关于在Excel表格中如何改变表格颜色?希望能够帮助到你们。方法/步骤1打开Exc
- 平时我们只是在Word文档中排版,处理一些文字,大部分表格之类的工作都会在Excel中去完成。但有时候一些简单表格还是会用Word来做,今天
- 在excel使用中,有时候需要用到打钩方框,但是搜狗输入法只能输入“√”,不能输入打钩方框,那该怎么办啊。下面给大家分享excel表格内勾选
- 个人所得税的计算看起来比较复杂,似乎不用VBA宏编程而只用公式来计算是一件不可能的事。其实,Excel提供的函数公式不但可以计算个人所得税,
- word2017怎么加水印?这篇文章主要介绍了word2017添加水印教程,需要的朋友可以参考下现在的网络都需要版权,因为网络太大,大家都需
- 在Excel工作表中,要查找包含数据有效性的单元格,可以用下面的方法: 一、查找所有包含数据有效性的单元
- 在Excel中,如果密密麻麻的单元格背景颜色都一样,有时还真能让人看花眼,为了让其层次分明,增强视觉效果,可以给奇偶行设置不同的背景颜色,具
- 使用Excel条件格式可以帮助您直观地查看和分析数据、发现关键问题以及识别模式和趋势使用Excel条件格式可以帮助您直观地查看和分析数据、发
- 我们一般只会用键盘上的“Print Screen SysRq”键来截图,但有时不太方便,那么在Excel中怎么截图?下面小编就为大家介绍几种
- 通过文件——选项——保存,在保存工作簿命令下,可以设置工作簿自动保存的位置,以及保存自动恢复信息的时间间隔。如果忘记保存位置,可以通过该选项
- 当我们使用win10系统时,在某些情况下,我们会突然遇到无法连接到Internet的情况。选择网络诊断后,我们将提示此计算机缺少一个或多个网
- 今天跟大家分享通过自定义序列让数据按想要的方式排序的技巧。应用案例下图工作表A列中的组别“诗歌”“绘画”是交错排列的,接下来我们想让组别中所
- Excel2010中怎么设置货币数字格式?第1步,打开Excel2010工作簿窗口,选中需要设置货币数字格式的单元格。然后右键单击选中的单元
- 星际战甲是一款在电脑中运行的科幻题材的第三人称设计网游,让众多玩家对这款游戏非常感兴趣。如果您想在本机运行星际战甲这款游戏的话,很有可能是因
- word文档如何快速批量删除英文而只保留中文?下面小编就为大家介绍一下,喜欢的朋友可以跟着教程一起来学习,希望大家喜欢遇到中英文混排Word
- excel表格打印预览显示页面显示不全怎么办?excel表格打印的时候会先预览一下,预览的时候发现表格没有全部显示出来,该怎么设置全部显示出
- Excel怎么快速生成图表呢?很多用户对此还不是很清楚,小编这里就给大家带来有关Excel怎么快速生成图表的回答,希望能够帮助大家了解。 1
- 最近有Win10用户反映,运行telnet命令的时候,出现提示“telnet不是内部或外部命令”,用户不知道这是怎么回事,为此非常苦恼。其实