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

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

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

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

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

图1

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

动图2：定义名称

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

图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

动图4：INDIRECT十字相交引用

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

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

图5

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

图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：

图7

图8

动图9展示：

动图9：VBA代码粘贴

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

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

动图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：动图展示

图11：函数解析图

图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：函数公式解析图

图14：公式解析

如图15：

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

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

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

• 本地下载