欧非资源网:安全、免费、专业放心的资源下载站! 最新软件|软件分类

您的位置:欧非资源网 > Excel专区 > Excel函数 > excel中VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

excel中VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

时间:2020-05-24 00:06作者:admin来源:未知人气:788我要评论(0)

VLOOKUP函数天阶用法:一条函数公式就能一次性完成对100张及以上的表格数据的引用,内容从第二大段开始。

坚持不易,有喜欢的朋友还请多多关注、帮我转发、收藏、评论、点赞,你们的认可就是我坚持的动力,先谢谢了!

▍一、利用INDIRECT函数十字相交查找、引用excel二维表数据,比VLOOKUP与MATCH函数的组合使用更简单,更方便。

如图1,这是同一个工作簿下的两张表格,右边表2引用左边表1的数据。此方法适用范围比较广:行列表头可以互换,顺序可以打乱。

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图1

先对表1的A1:G6数据区域定义名称,选择首行、最左列定义名称,如动图2:

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

动图2:定义名称

定义名称有规则,内容不能以数字开头,如果一定要用数字开头定义名称,会在数字前面加上下划线(_1Kg,英文输入状态,按住Shift+ - 符号),如图3:

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图3:数字定义名称

表1的定义名称完成,就可以在表2输入INDIRECT函数进行引用了。在表2的B2单元格输入公式=INDIRECT($A2) INDIRECT(B$1),两个函数之间空格隔开。

$A2表示A列绝对引用,不偏移;B$1表示第1行绝对引用,不会偏移。A列重量是数字开头,所以数字前面都要统一加“下划线”,如(_1Kg),中文内容没有问题。

=INDIRECT($A2) INDIRECT(B$1)公式的意思是 同时引用A2单元格里6Kg地址的内容与B1单元格里C区地址的内容,十字相交所得结果。因为6Kg和C区等都已经定义了名称,所以就变成了可以引用的地址内容。动图展示:图4

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

动图4:INDIRECT十字相交引用

▍二、利用INDIRECT和VLOOKUP和COUNTIF函数组合跨工作表一次性可引用100张表格

如图5:在“汇总表”中出现的人名是前面四张表格里随机抽取出来的,怎么用VLOOKUP一次性引用完成。(如果有100张工作表,1000个人名,而且还不知道他们是在哪个部门,一个一个找太麻烦了。)

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图5

▶思路解析:先求出部门,再VLOOKUP配合INDIRECT函数一次性引用。如图6

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图6

▶步骤一:先通过VBA代码自动提取所有工作表名称,解决手输的烦恼。

Sub a() For Each sh In Sheets k = k + 1 Cells(k, 1) = sh.Name Next End Sub 这是提取工作表名称代码,不用理解含义,复制保存好,用的时候直接粘贴,很方便,不用担心VBA很麻烦。

我新建一个工作表叫“提取各工作表名称”,用来放提取出的工作表名称。然后复制好VBA代码——右键点击工作表“提取各工作表名称”——点“查看代码”——出现了VBA编辑对话框——双击窗口左边的“提取各工作表名称”表——把VBA代码粘贴,点上方的“绿三角”运行,关掉VBA编辑窗口,OK。如图7和图8:

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图7

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图8

动图9展示:

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

动图9:VBA代码粘贴

▶步骤二:给提取出来的工作表名称新建定义名称,方便INDIRECT函数引用。

把A1:A4的四个工作表名称选中,定义名称为“部门”,如图10

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

动图10:定义名称

▶步骤三:用公式找出每个人所对应的部门表格

在D2单元格输入公式=LOOKUP(1,0/COUNTIF(INDIRECT(部门&"!A:D"),$A2),部门)。

函数解析:① INDIRECT(部门&"!A:D")表示引用定义名称“部门”表格A:D列地址的内容,定义的名称“部门”包含了财务部、销售部、 生产部 、研发部四张表格。

② COUNTIF(查找区域,查找值),COUNTIF(INDIRECT(部门&"!A:D"),$A2)就是A2小张在所有部门表格的A:D列查找,这部分函数结果就是{1;0;0;0},分别对应{"财务部";"销售部";"生产部";"研发部"}。

③ 0/COUNTIF(INDIRECT(部门&"!A:D"),$A2)表示0/{1;0;0;0}。因为0除以1为0,但是0除以0是错误的,数学计算是不成立的,所以最终结果为{0;#DIV/0!;#DIV/0!;#DIV/0!}。

④ LOOKUP是模糊查找函数,有一种向量形式,参数是LOOKUP(查找值,查找区域,结果区域)。=LOOKUP(1,0/COUNTIF(INDIRECT(部门&"!A:D"),$A2),部门)就是=LOOKUP(1,{0;#DIV/0!;#DIV/0!;#DIV/0!},{"财务部";"销售部";"生产部";"研发部"})。两个数组的位置是一 一对应的,因为0对应"财务部",其他都是错误值就不对应,所以得出A2小张是财务部。

如图11:思路解析图;如图12:动图展示

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图11:函数解析图

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图12:动图展示

注意:如果有一个人名在其他表格也有同名,那这个同名的可能会出现错误结果。这不是公式的问题,是给的信息太少的问题,因为就给了人名一个信息,没有给部门信息,就算手动一个一个找也会错,因为你不知道对方是要找哪个部门的人。

▶步骤四:用VLOOKUP和INDIRECT函数组合,一次性同时引用100张表格数据(请注意:前方高能)

在B2单元格输入=VLOOKUP($A2,INDIRECT($D2&"!A:Z"),MATCH(B$1,INDIRECT($D2&"!1:1"),0),0)。

函数解析:① MATCH(查找值,查找区域是单行或单列,查找类型),MATCH(B$1,INDIRECT($D2&"!1:1"),0)表示在“引用D2单元格里财务部表格第1行地址的内容”中精确查找B1工资,0是精确查找,反馈结果是数字2。(这是动态引用,不管前面任何一张表格怎么更换表头的顺序,插入列或减去列,都会自动匹配结果)。

② INDIRECT($D2&"!A:Z")表示“引用D2单元格里财务部表格A:Z列的内容”,写A:Z列是为了把所有表格的数据包含进去,怕遗漏数据。

③=VLOOKUP($A2,INDIRECT($D2&"!A:Z"),MATCH(B$1,INDIRECT($D2&"!1:1"),0),0)就是最终显示的结果,其他单元格就向左或向右的填充就可以了。

如图14:函数公式解析图

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图14:公式解析

如图15:

VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用

图15:一条函数同时引用100张表格


▍其实找人名对应部门表格的公式和最后引用的VLOOKUP公式是可以合并成一条的,但是如果合并成一条真的是太长太长,光括号都能把人看晕,所以还是分步写成两段公式比较清楚和容易理解。

excel中VLOOKUP函数天阶用法:你见过吗?同时一次性完成对100张表格的引用的下载地址:
  • 本地下载

  • 相关阅读 Excel有哪些常用的数学函数?​Excel取消表格中虚线的两种方法Excel最常见的「错误值」,这些含义你都知道吗?实现快速找出Excel表格中两列数据不同内容的3种方法!如何利用Excel一键提取身份证的这些重要信息,公式直接套用!Excel如何制作动态红绿灯,工作可不要亮红灯哦Excel身份证号大探索excel如何根据日期按月汇总计算公式Excel浪漫表白公式,发给心仪的她/他Excel表格如何自动求和

    文章评论
    发表评论

    热门文章 excel 两表数据快速对比,高手都是这样做,四种方法随你选.xlsm是什么文件格式,以及xlsm文件怎么打开的方法excel if函数如何多个条件并列excel中计算加权平均数的公式:用SUMPRODUCT和SUM函数计算加权平均

    最新文章 Excel有哪些常用的数学函数?​Excel取消表格中虚线的两种方法 Excel最常见的「错误值」,这些含义你都知道吗?实现快速找出Excel表格中两列数据不同内容的3种方法!如何利用Excel一键提取身份证的这些重要信息,公式直接套用!Excel如何制作动态红绿灯,工作可不要亮红灯哦

    人气排行 excel 两表数据快速对比,高手都是这样做,四种方法随你选.xlsm是什么文件格式,以及xlsm文件怎么打开的方法excel if函数如何多个条件并列excel中计算加权平均数的公式:用SUMPRODUCT和SUM函数计算加权平均excel中IF条件函数10大用法完整版,全会是高手,配合SUMIF,VLOOKUPexcel中COUNTIFS函数9种高级用法详解,条件统计重复值,告别加班涨工如何解除Excel VBA工程密码excel 如何根据身份证号码提取户籍所在省份地区函数公式

    盖楼回复X

    (您的评论需要经过审核才能显示)