Excel中如何快速进行并表查询?
Hi,大家好,我是胖斯基 谈到Excel,有一个话题是永远也避不开的,那就是“查询”。 查询的问题有很多种,比如:精确查询,模糊查询,条件查询,跨表查询等…… 而今天要分享的是一对多的并表查询什么意思呢? 比如:我要从各区域中查询订单号,并且将订单号的相关信息也一并查询出来,而此时的订单是分布在各区域,你要如何查询呢? 当然,最简单的办法就是查找(Ctrl+F),比如这里查询订单号:2018112307 注意:将默认的范围由工作表修改为“工作簿” 这种方法是很方便,但是如果要在Excel表中固定位置显示,则该方法失效。 So,你会想到用公式来查找,这样便可以在固定的位置返回所需要的数据。 没错,就是这样,就像下图所示一样 此时最常规的思路就是通过Vlookup来一个表一个表的查找,写出的公式就会像这样:iferror(vlookup(订单号,北京,,),iferror(vlookup(订单号,上海,,),iferror(vlookup(订单号,广州,,))) 乍一看,还行,结果能出来! 但是,一旦区域增多,那公式长度可想而知,久而久之,你连修改的勇气都没有了…… 其实从这个逻辑来看,核心是对区域表格逐一进行查询,为了避免公式长度无限长以及拓展性的需求,我们可以引入一个动态区域,什么意思呢? 无论你有多少区域,将其叠加在一起!! So,无论你有多少区域,无论后续是否增减,只要一个动态区域,即可解决查询问题。 即:我们将多个Sheet页签进行“并表”,这样就解决了一对多的查询问题。 那在Excel中如何进行并表查询呢? 1构建自定义动态区域通过Excel自带的【公式】-【名称管理器】来创建动态区域,如下图示 即:将Sheet页签的名字通过名称管理器来创建一个区域,其名称为QUYU 2利用动态区域来查询显示 由于订单号信息已知,So,要根据订单号来查询其他信息,此时利用上面设置的动态区域[img]http://spider.nosdn.127.net/27f05280c3df8559c8f8586661904dba.gif" /> 公式:=INDEX(QUYU,MATCH(1,--(COUNTIF(INDIRECT("'"&QUYU&"'!A2:A11"),C4)>0),0)) 内容有点长,我们给分段看一下: 1. INDIRECT函数构建动态区域 1. INDIRECT("'"&QUYU&"'!A2:A99") INDIRECT本质是构建新的区域范围,而之前已经定义了QUYU,所以这里构建的区域为:INDIRECT({"'北京'!A1:A99";"'上海'!A1:A99";"'广州'!A1:A99"}) 即:由现在的已有的页签,形成一个组合范围,而不用一个页签一个范围的去查。 2.COUNTIF查找计数 2.COUNTIF(INDIRECT("'"&QUYU&"'!A2:A99"),C4)>0 在上述范围的基础上,通过Countif函数,对多个区域进行查找 即:countif({"'北京'!A1:A99";"'上海'!A1:A99";"'广州'!A1:A99"},c4) 即:对北京、上海、广州页签的A1:A99范围查找,是否有C4,并判断其数量是否大于0,如果大于0,则范围TRUE(即C4存在该区域),否则范围False(即C4不存在该区域) 3.MATCH定位区域位置 3.MATCH(1,--(COUNTIF(INDIRECT("'"&QUYU&"'!A2:A99"),C4)>0),0) 用MATCH函数来查看Countif的结果,从而判断其出现在第几个区域范围; 如以订单号:2018112307为例,其通过第2个公式计算后,结果为:False;False;True 在通过“--”(负负得正)的方式,将文本字符串转化为数字,即为 0;0;1 So,MATCH函数,在范围内 0;0;1精确查找1,故结果为3 4. INDEX展现区域名称 4.INDEX(QUYU,MATCH(1,--(COUNTIF(INDIRECT("'"&QUYU&"'!A2:A99"),C4)>0),0)) 通过INDEX函数,对QUYU范围内,查找其第3个值,故结果为广州 So,此时你可以随意进行并表查询 此时,你仅仅只有北京、上海、广州3个区域,如果未来还有增加,则仅仅只需要修改数据范围【QUYU】即可,其他公式不变。这样,信息维护起来,则高效快捷 怎么样?如此并表查询处理,是不是快了很多呢? 思考:如果一个订单号由于信息录入错误,导致其出现在了2个页签,比如上海和广州区域,那此时如果用上述公式进行查询,会返回什么结果呢?
|
|
最新文章 |
图片主题 | ||||
|
||||
热门文章 |
推荐文章 | ||||
|
||||
相关文章 |
便民服务 | ||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||