绝了！Excel可以这样用 - 数据处理、计算与分析 - IT168文库(2)

第2篇 计算数据

这3个公式也能得出正确的结果，CHOOSE函数构建内存数组的效果同IF{1,0}，这 里不再详细讲解。

职场点睛：困扰很多用户的反向查找问题就这样解决了，强烈建议用户多尝试一下这 个函数套路，学会这一招，今后在工作中遇到类似问题无需每次再构建辅助列，一个公式 就能搞定。

4．解决方案2

使用IF{1,0}构建内存数组的方法很经典，但理解有一点难度。查找的问题并非一定要

F5 单元格录入如下公式： 通过 VLOOKUP 函数来解决，使用其他函数也能轻松搞定。在

MATCH 函数，作用是“定位”，在单元格区 公式解析：对于这个公式，里层是一个

域中搜索指定项，然后返回该项在单元格区域中的相对位置（返回值是一个数值）。公式 各参数解释如表5.2所示。

表5.2 MATCH函数3个参数的理解

参数顺序 第1参数 第2参数 第3参数 参数含义 要查找的值 查找的区域 查找模式 0 E5 如第3参数是0则区域第1列不需要排序；如是1需要 B2:B10 对区域的第1列升序排序；如是-1则需要对对区域的第 1列降序排序 为0时精确匹配，如为1或者-1则是模糊匹配 上例中的取值=INDEX(A2:A10,MATCH(E5,B2:B10,0)) =VLOOKUP(E5,IF({0,1},A2:A10,B2:B10),2,) =VLOOKUP(E5,CHOOSE({1,2},B2:B10,A2:A10),2,) =VLOOKUP(E5,CHOOSE({2,1},A2:A10,B2:B10),2,)

注 意 事 项 ，按 F9 键观 读者可以在编辑栏中选中公式的 MATCH 部分“ MATCH(E5,B2:B10,0)”察返回值，返回结果如图5.7所示。

图5.7 INDEX+MATCH函数运算过程

公式的外层是一个INDEX函数，我们可以把INDEX函数的第1参数理解为一个矩形

2 区域，函数的结果是返回矩形区域中的某个值，具体返回哪一个值则由该函数的第 、 3

·132 ·

第5章 海量数据分析的利器——初识查找引用函数

参数决定。第2、3参数告诉Excel，返回值在区域中的第几行和第几列。

本例中INDEX函数的第1参数A2:A10仅有一列，可以省略第3参数，根据第2参数 即MATCH函数计算的结果来决定返回值的结果。

读者可在E1单元格的数据有效性下拉列表中任意选择学生姓名，观察F1单元格公式 结果的变化。

5．函数套路——INDEX+MATCH解析

INDEX+MATCH函数组合相当于把原来VLOOKUP一个函数的工作分配给了两个函 数来完成。虽然没有能完成。

回到上一节中使用VLOOKUP精确查找的进销存案例，使用INDEX+MATCH函数组 合也能完成，公式如下： =INDEX($H$1:$H$15,MATCH(A2,$G$1:$G$15,0)) VLOOKUP那么惊艳，但这个函数组合能完成的工作比单一的

VLOOKUP函数要多很多，至少VLOOOKUP能做的工作，INDEX+MATCH函数组合都

这个公式的含义是先在 G 列的“货号”区域 $G$1:$G$13 中查找 A2 单元格中的货号， 然后在H列“销售汇总”区域$H$1:$H$13返回货号对应的销售额。

职场点睛：这个函数套路给我们的启示是：解决问题不要仅局限在一种方法上，多掌 握几个基础的函数对日常工作绝对是有帮助的。

5.2.2 IF{1,0}与INDEX+MATCH方法比较

两种方法都很经典，对于初学者，建议多尝试一下INDEX+MATCH函数的组合，思 路不要总局限在 VLOOKUP 函数上。

对于IF{1,0}，使用时要考虑是否需要使用数组公式（即按Ctrl+Shift+Enter三键结束）。

IF 函数的第 2 和第 3 参数仅是引用了工作 本节的示例是不需要使用数组公式结束的，因为 表中的单元格区域，并没有计算，如在引用的同时还有计算，就需要使用数组公式结束。

本节示例文件：《5.2 突破VLOOKUP函数反向查找的限制.xlsx》。

5.3 轻松应对多条件查找

本章前两节中讲解的示例都属于单条件查找，实际工作中更多的是需返回满足多个条 件的对应值。相对于单条件查找，多条件查找肯定不能简单应用VLOOKUP函数。

5.3.1 多条件查找的两种解决方案

先看一个多条件查找的经典案例，原始数据在

A:C 列， E2 单元格中设置了数据有效

性，取值为产品代码，F2单元格中取值为部门号，需要在G2单元格中设置公式，要求G2

·133·

第2篇 计算数据

单元格中返回值随E2、F2单元格取值的变动而变动，如图5.8所示。

图5.8 一个多条件查找的示例

1．问题分析

这里需要返回满足两个条件时对应的销售额，如果把这两个条件连接起来变成一个条 件，就能回到上两节讲解的单条件查找的问题中去了。如是多个条件方法也一样。

2．解决方案1

使用IF{1,0}构建内存数组，然后使用VLOOKUP函数，在G2单元格中输入如下公式， 并按 Ctrl+Shift+Enter 三键结束：

公式解析：公式中使用了连接符 & ，将两个查找条件 E2 和 F2 单元格连接起来，在 IF 函数构建的查找区域中，也使用连接符&将两个数据区域连接。将IF函数的返回结果放入 单元格区域中，如图5.9所示。

=VLOOKUP(E2&F2,IF({1,0},A2:A9&B2:B9,C2:C9),2,)

图5.9 IF{1,0}中使用连接符&的返回结果

使用连接符&把多条件查找的问题转换成了单条件查找，连接两个条件是关键的一步。 3．解决方案2

连接了两个条件，将多条件查找变成了单条件查找，后续的问题就简单了，INDEX+ MATCH 函数组合也可以解决，在 G2 单元格中录入如下公式，并按三键结束：

=INDEX(C2:C9,MATCH(E2&F2,A2:A9&B2:B9,)) ·134 ·

第5章 海量数据分析的利器——初识查找引用函数

公式解析：这个公式还是典型的INDEX+MATCH组合，公式没有悬念，需要注意的 是应按三键结束，给Excel传递一个信息，这里有多重运算。

5.3.2 特殊的多条件查找

多条件查找还有两种特殊的情况，展现的外观不同于之前介绍的例子，但实质上就是 多条件查找。

1．双向查找

查找的条件分别散布在行和列上，这种问题一般称其为“双向查找”。沿用上例的数 据，需要查找的条件分别在第1行和第E列，要求在F2单元格中写一个公式，向下向右 复制完成，如图5.10所示。

图5.10 双向查找的示例

1）问题分析

条件不在同一行中，实质上还是多条件，使用连接符&连接两个条件，将多条件变成 单条件是最常用的思路。

2）解决方案

在F2单元格中录入如下公式，按三键结束，并向下向右复制完成。 3）公式解析

=VLOOKUP($E2&F$1,IF({1,0},$A$2:$A$9&$B$2:$B$9,$C$2:$C$9),2,) 公式本身没有悬念，关键要注意 VLOOKUP 函数第 1 个参数中单元格的引用类型。对 于E2单元格，公式复制后引用的E列是不变的，引用的行会改变，所以对于E2单元格， 需要使用行相对列绝对的混合引用方式$E2；对于引用的F1单元格判断依此类推。

示例文件中给出了另两种思路的公式如下，注意使用INDEX+MATCH函数组合需要 按三键结束。

职场点睛：这个案例的启示是，要学会分析问题，将复杂的问题简单化并归纳到已知 的套路中去。函数问题如此思考，实际工作也是一样的。

·135·

=INDEX($C$2:$C$9,MATCH($I2&J$1,$A$2:$A$9&$B$2:$B$9,)) =LOOKUP(1,0/(($A$2:$A$9 …… 此处隐藏：1915字，全部文档内容请下载后查看。喜欢就下载吧 ……