主页 > Excel专区 > Excel函数 >

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

Excel函数 2020-05-24

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公式是可以合并成一条的,但是如果合并成一条真的是太长太长,光括号都能把人看晕,所以还是分步写成两段公式比较清楚和容易理解。


标签: vlookup函数

九游地址网 Copyright © 2016-2020 modle8.com. Some Rights Reserved. 备案号:闽ICP备16003269号