我折腾到半夜,同事用这个Excel公式,3分钟完成1000条数据的跨表核查!
每天一点小技能
职场打怪不得怂
编按:在各种查询工作中,最难的就是跨表查询!而说到查询,大家首先想到的一定是VLOOKUP函数或LOOKUP函数,但是只有这两个函数却是远远不都的!那么,我们要怎么才能顺利实现跨表查询呢?
日常办公中,大家经常会遇到一个EXCEL表中有多个sheet表,所要查找的目标则分散在多个不同的sheet表中的情况。这时候,就需要进行跨表查找。
实现跨表查找的方法有很多,运用VLOOKUP函数或LOOKUP函数就是其中很关键的一门技巧,但只依靠它们却是远远不够的。在大多数工作中,一个完整的查找公式需要多个函数组合才能完成。
今天,春风就展示一下查找的最高级用法——跨表查询!学会了这个方法,大家也就可以进入EXCEL的中级水平了。
实例:
这是小明副食店所有商品的月销售额,老板小明为了方便分类,把不同品类的商品放在了不同的sheet表中。
到了月末盘点的时候,小明想在查询表中,根据提供的商品名称,从水果、蔬菜、肉类三个工作表中查询该商品的销售额。
如果当月录入的数据少,用“来回切换+肉眼观察”法即可。但是,如果数据过多,用“来回切换+肉眼观察”法就会耗时耗力,还容易眼花失误。
这时候就要用专业的“多表查找”技法了。它可以轻松实现在输入商品名称后即刻显示商品的月销售额,而不需要用鼠标在多个sheet表中来回切换。
接下来,一起看看如何实现这个操作吧!
第一部分:查询商品属于哪个品类
判断商品属于哪个品类的公式为:“=LOOKUP(1,0/COUNTIF(INDIRECT({"水果";"蔬菜";"肉类"}&"!a:a"),A2),{"水果";"蔬菜";"肉类"})”。在B2单元格输入后,往下拉即可。
注意:完成公式后,在A2单元格中输入待查找的商品名称,在B2单元格中就会自动显示其品类。
公式分析:
①“{""}”:大括号内是要查找的多个工作表名称,用分号分隔。为一维纵向数组,表示一列单元格数据的集合,关于数组具体用法见教程《不懂excel中的数组公式,怎么晋升高手?》。
②“a:a”:是商品名称在各个表中的A列。
③“COUNTIF(INDIRECT({"水果";"蔬菜";"肉类"}&"!a:a")”可以返回一个包含0、1、0的数组,其中非0数字1的位置的即是商品所在表的位置。
④利用LOOKUP(1,0/(数组),数组)结构取得工作表的名称。第一个参数“1”,是要查找的值;第二个参数“0/(数组)”是要查找的范围;第三个参数是要获得的值,即商品相对应的品类。其中,本例数组中共三个值,有两个值为0,被0除会显示“#DIV/0!”的错误。
【补充】思路剖析:
1.找到可以使用的函数
① 确定商品是在哪个sheet表中,应用COUNTIF()函数进行多表统计,分别计算各个表中该商品存在的个数。
② 利用INDIREC()函数把字符串转换成单元格引用。
③ 利用LOOKUP(1,0/(数组),数组)函数取得工作表的名称。
2.明确各函数的使用方式
① COUNTIF()函数
该函数的含义为在指定区域中按指定条件对单元格进行单条件计数。语法规则为COUNTIF(range,criteria)。其中,range为对非空单元格进行计数的区域,criteria为以数字、表达式或文本形式定义的条件。函数很常见,这里不多赘述。
② INDIREC()函数
INDIRECT()函数的含义为返回由文本字符串指定的引用。此函数立即对引用进行计算,并显示其内容。这个函数看起来很复杂,其实也简单。
③ INDIREC()函数引用方式的确认
在Excel中有两种引用方式。
第一种是直接引用,大部分情况下都是直接引用,如求苹果的月销售额,只需在C3单元格输入“=水果!B2”,就能直接引用B2单元格。直接引用区域方便快捷,也容易理解。
第二种是间接引用,现在已经将工作表的名称,即每个商品的品类都写在了B列。假如现在要引用每个表的月销售额。用&将工作表名称(品类)和月销售额所在的单元格连接起来,“=B2&"!B2"”这样就可以看到每个表格具体要引用的区域,不过这种是没法计算的。
这时,INDIRECT函数登场了,在D2单元格输入“=INDIRECT(B2&"!B2")”,这样D2单元格显示了苹果对应的月销售额。INDIRECT函数就是通过单元格间接引用对应表格,这样,大家就不需要通过鼠标一个个点击来选择引用区域了。
3.将函数正确组合到公式中,保证公式可以正确运转。
因为上面已有说明,这里就不再叙述了。
第二部分:查询该商品的月销售额
在B列有了商品的品类,查商品的月销售额,就简单多了,VLOOKUP函数与INDIRECT函数配合就可以轻松搞定。
在C2单元格输入公式“=VLOOKUP(A2,INDIRECT(B2&"!A:B"),2,FALSE)”,这样A2单元格的商品对应的月销售额就在C2单元格显示了。
下拉C2单元格至C4单元格,商品的月销售额就完成了。
公式分析:
“=VLOOKUP(A2,INDIRECT(B2&"!A:B"),2,FALSE)”中,第一个参数“A2”是要查找的值;第二个参数“INDIRECT(B2&"!A:B")”是要查找的范围;第三个参数“2”是结果数据所在列数,即第二列;第四个参数“FALSE”表示精确查找。
好了,跨表查找这个历史性的查询难题终于搞定了。大家掌握没?
上面的公式虽然看似很长,但只要逐步测试、验证,大家就能明白各个部分的意义。
还不明白?
那,那就再看一遍!
总结:掌握这个方法,我们需要了解以下两点。
① COUNTIF函数、INDIRECT函数、LOOKUP函数、VLOOKUP函数的用法。
② 数组公式的用法。
只有足够灵活地应用各个函数,才能成为EXCEL的高手。但是,要达到灵活使用的程度更少不了大家平时的多多练习。
最后,希望大家多多分享点赞支持春分哦!大家的每一次点赞和转发都是支持小篇坚持的动力。小伙伴们,还有别的什么想法,欢迎留言。
在线咨询Excel课程
Excel教程相关推荐
Lookup始终是哥!我用这个公式2分钟完成别人半天的查询工作量,同事看了都想学!
超实用!公司大神用Excel做了一张自动查询表,我跪着收藏了!
如果你连这些通配符的使用方法都还不会,就不要抱怨工资低了……
主讲老师: 滴答
Excel技术大神,资深培训师;
课程粉丝100万+;
开发有《Excel小白脱白系列课》
《Excel极速贯通班》。
原价299元
限时特价 99 元
少喝两杯咖啡,少吃两袋零食
就能习得受用一生的Excel职场技能!