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