|
谈到Excel，有一个话题是永远也避不开的，那就是“查询”。 查询的问题有很多种，比如：精确查询，模糊查询，条件查询，跨表查询等……  而今天要分享的是一对多的并表查询 什么意思呢？ 比如：我要从各区域中查询订单号，并且将订单号的相关信息也一并查询出来，而此时的订单是分布在各区域，你要如何查询呢？  当然，最简单的办法就是查找（Ctrl+F），比如这里查询订单号：2018112307  注意：将默认的范围由工作表修改为“工作簿” 这种方法是很方便，但是如果要在Excel表中固定位置显示，则该方法失效。 So，你会想到用公式来查找，这样便可以在固定的位置返回所需要的数据。 没错，就是这样，就像下图所示一样  此时最常规的思路就是通过Vlookup来一个表一个表的查找，写出的公式就会像这样：iferror（vlookup（订单号,北京，，），iferror（vlookup（订单号,上海，，），iferror（vlookup（订单号,广州，，））） 乍一看，还行，结果能出来！ 但是，一旦区域增多，那公式长度可想而知，久而久之，你连修改的勇气都没有了…… 其实从这个逻辑来看，核心是对区域表格逐一进行查询，为了避免公式长度无限长以及拓展性的需求，我们可以引入一个动态区域，什么意思呢？  无论你有多少区域，将其叠加在一起！！ So，无论你有多少区域，无论后续是否增减，只要一个动态区域，即可解决查询问题。 即：我们将多个Sheet页签进行“并表”，这样就解决了一对多的查询问题。 那在Excel中如何进行并表查询呢？ 1 构建自定义动态区域 通过Excel自带的【公式】-【名称管理器】来创建动态区域，如下图示  即：将Sheet页签的名字通过名称管理器来创建一个区域，其名称为QUYU 2 利用动态区域来查询显示 由于订单号信息已知，So，要根据订单号来查询其他信息，此时利用上面设置的动态区域  播放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，此时你可以随意进行并表查询 播放GIF此时，你仅仅只有北京、上海、广州3个区域，如果未来还有增加，则仅仅只需要修改数据范围【QUYU】即可，其他公式不变。这样，信息维护起来，则高效快捷 怎么样？如此并表查询处理，是不是快了很多呢？ 思考：如果一个订单号由于信息录入错误，导致其出现在了2个页签，比如上海和广州区域，那此时如果用上述公式进行查询，会返回什么结果呢？ |
|
|
