|
说起查找引用，想必大家也很熟悉了，Excel里也自带了许多查找引用的函数，今天就来给大家系统讲讲查找引用的相关公式。 “查找”是使用EXCEL过程中是非常普遍的工作，所以EXCEL设计了很多各式各样的“查询与引用类函数”
今天就来给大家分享几种常见的“查找引用”情况和一些经典的嵌套函数、数组函数。 1. 一对一查询 一个匹配条件，对应一个匹配值。
这是最常见、最简单的条件匹配，同时也是解法最多的一种。 我们给出两种“快准狠”的方案，供大家选择： 1）=VLOOKUP($E$2,$A$1:$B$16,2,0)，没什么可讲的了，VLOOKUP函数的基础用法。 2）=INDEX($B$2:$B$16,MATCH($E$2,$A$2:$A$16,0)) 这个就是经常说到的INDEX+MATCH的经典组合了。通过MATCH函数查到匹配条件在A列中的位置序号，返回给INDEX函数作为第二参数，引出B列的内容。 一对一查找，大家记这两个就好了，其它的函数解法拿来学习学习思路即可。 2. 一对多查询 在匹配条件列中存在，但不局限于唯一性，导致有多个匹配值存在。
这是一个经典用法“万金油”函数，这个函数的别称充分说明它的应用环境很广，同时也决定了它在写法上的变化很多。 我们列几个写法吧： 1）匹配条件对应字段2中的最大值 {=MAX(IF($A$2:$A$16=$E$2,$B$2:$B$16,""))}，此函数返回13。 2）匹配条件第二次出现时对应字段2的值 {=INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E$2,ROW($1:$15),99^9),2))}，此函数返回3。 3）列出所有匹配条件的值 {=IFERROR(INDEX($B$2:$B$16,SMALL(IF($A$2:$A$16=$E$2,ROW($1:$15),99^9),ROW(E1))),"")}，下拉填充单元格，最后形成的效果如下图：
通过上面3个案例，我们可以看到“万金油”函数的几个共同点： a、键入公式后，都需要按CTRL+SHIFT+ENTER三键结束的数组函数； b、都是围绕IF函数的判断，形成新的数列，再使用SMALL或者LARGE函数来确定我们需要的序号，即可返回给INDEX函数索引出对应的匹配值。 这就是“万金油”的精髓，一般人我不告诉他。 3. 多对一查找 多个匹配条件确定一个匹配值的情况
两列条件决定一个匹配值，我们可以使用下列函数来解决。 公式一： =SUMPRODUCT(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3),$C$2:$C$16) SUMPRODUCT是多条件匹配用的最多，而且写法最简单的写法，语法如下： =SUMPRODUCT((【条件1区域】比较符【条件1】)*(【条件2区域】比较符【条件2】)*（【条件n区域】比较符【条件n】）,【匹配值列】) 公式二： =LOOKUP(1,0/(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3)),($C$2:$C$16))，LOOKUP是利用了二分法运算原理做的函数思路，一定要注意条件需要用括号括起来（所有条件连乘之后，再用0除），语法如下： =LOOKUP(1,0/((【条件1区域】比较符【条件1】)*(【条件2区域】比较符【条件2】)*（【条件n区域】比较符【条件n】）)，【匹配值列】) 上面的例子是多对一查找的在一维数据上的匹配方式，还有一种特殊情况需要大家注意。 如果是下图所示的二维数据，又该如何操作呢？
横纵交叉点匹配值，也属于多条件查询，我们此时还是使用INDEX+MATCH的经典嵌套函数解决： =INDEX($B$19:$E$25,MATCH($H$18,$A$19:$A$25,0),MATCH($H$19,$B$18:$E$18,0))，用两个MATCH函数分别确定横纵匹配条件出现的序号，再返回给INDEX函数索引出横纵交叉点上的值。 4. 多对多查找 多个查找条件，匹配多个结果。
例如上图的案例，AN有两组对应值，我们需要匹配出所有的匹配值，可以使用下面的函数： {=IFERROR(INDEX($C$2:$C$16,SMALL(IF(($A$2:$A$16=$F$2)*($B$2:$B$16=$F$3),ROW($1:$15),9^9),ROW(F1))),"")} 这里还是用的万金油公式，鉴于篇幅原因，就不多解释了。 上面就是匹配查询问题的几种常见解题思路，一文写不尽整个EXCEL。 对于匹配查询的问题，还有很多的个案，只能具体问题具体分析，但是大部分个案都可以使用“万金油”解出，大家多多练习吧。 |
|
|
