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

第5章 海量数据分析的利器——初识查找引用函数 如果这个公式不是放在B:D列，写法就要改变一下，COLUMN函数要加上参数，公 式如下：

图5.13列示了两种不同的公式写法，COLUMN函数能否省略参数要看函数所在单元 格的位置，这点与 ROW 函数的用法类似。

=VLOOKUP($A$9,$B$2:$E$6,COLUMN(B1),)

图5.13 COLUMN函数两种写法比较

4）解决方案2

既然这种多列查找还是一种多条件查找，自然可以试试多条件查找的几种经典解决方 案，比如 INDEX+MATCH 函数组合。在 B9 单元中录入如下公式，并向右复制完成。

=INDEX($C$2:$E$6,MATCH($A$9,$B$2:$B$6,),MATCH(B8,$C$1:$E$1,))

5 ）公式解析

这里使用了两次MATCH函数，分别定位两个条件（行方向的姓名，列方向的要查找 项目）在INDEX函数第1个参数中的位置。

6 ）解决方案 3

有兴趣的读者还可以试试IF{1,0}的解决方案，公式如下，需按三键结束，这里不再 讲解。

=VLOOKUP($A9&B8,IF({1,0},$B2:$B6&C1,C2:C6),2,) 本节示例文件：《5.3 轻松应对多条件查找.xlsx》。

5.4 查找满足条件的最后一个值

本章前 3 节介绍的都是精确查找，满足条件的返回值都只有一个。如满足条件的值有 多个，将会是什么情况呢？在精确查找模式下，不管是单条件还是多条件查找，不论是用 VLOOKUP函数、VLOOKUP{1,0}或者INDEX+MATCH函数组合，返回结果都仅是满足 条件的第一个值。

5.4.1 LOOKUP函数应用于模糊查找

可能查找需要返回的值并非第一个，这样就需要使用其他函数组合来完成，本节介绍 使用LOOKUP函数返回满足条件的最后一个值，请看下面的例子。

·137·

第2篇 计算数据 如图5.14所示，A列为升序排列的成交时间，B列为货号，C列存放单笔成交单价， 需提取当天各个货号的最后一笔成交单价，即黄色标注部分。

1．问题分析

这个问题如用VLOOKUP函数，只能返回各个货号的第一笔成交单价，如图5.14中 红色字体标注。

VLOOKUP公式：=VLOOKUP(E2,$B$2:$C$10,2,0)

图5.14 VLOOKUP函数返回了第一笔成交单价

2．解决方案

在查找货号的情况下，因相同的货号不止一个，精确查找无法解决。换一种思路，可 以试一下模糊查找，在F2单元格输入如下公式，向下复制，如图5.15所示。

=LOOKUP(1,0/($B$2:$B$10=E2),$C$2:$C$10)

图5.15 LOOKUP函数查找最后一笔成交单价

3．公式解析

这个公式使用了 LOOKUP 函数， LOOKUP 函数不同于 VLOOKUP 函数的地方是，其 本身就是一个模糊查找的函数，不像VLOOKUP函数通过第4参数来控制查找模式。

在讲解VLOOKUP函数时曾经提到，在模糊查找模式下，数据区域的第一列需要按升 序排列，使用 LOOKUP 函数时自然对被查找区域数据也有类似的要求。但是否一定要对 查找区域先排序呢？那样不是很麻烦吗？答案是否定的，这里使用了LOOKUP(1,0)的函数 套路。

·138 ·

第5章 海量数据分析的利器——初识查找引用函数 4．函数套路——LOOKUP(1,0)解析

如果直接使用模糊查找，数据区域第一列需要按升序排列。如将查找值与被查找区域 的第一列都转化为如 1 或 0 之类的数值（例如查找 1 ，查找区域中都是 0 ），就可以规避预 先排序的问题。这个公式可以归纳为以下套路：

=LOOKUP(1,0 / (条件区域=条件),返回值区域) 第 2 个参数使用了“ 0 / ( 条件区域 = 条件 ) ”，其中“条件区域 = 条件”判断结果非 TRUE 即FALSE，用0除以判断的结果TRUE或者FALSE，得到的值为0或者#DIV/0!。 选取LOOKUP函数第2参数部分，即： 按F9键后得到结果为{0;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!}。把 这一段的计算结果放入单元格，如图 5.16 所示，可以发现在 B 列的货号中，对应 E2 单元 取值“A”的行返回值为0，其他行返回值为“#DIV/0!”。这一段公式的实质是构建了一 个辅助列，作为LOOKUP函数的第2个参数。

0/($B$2:$B$10=E2)

图5.16 公式第2参数运算结果

公式分解到这一步，就是一个LOOKUP函数了，这个LOOKUP函数使用向量形式有 3个参数，在第2个参数区域（0/($B$2:$B$10=E2)）中查找第1个参数（1），然后返回第 3个参数区域（$C$2:$C$10）中相同位置的值。

0 值，肯定找不到“ 1 第 2 参数中只有 0 和错误值，错误值不参与查找，其他都是 ”， 这时模糊查找发挥作用了。Excel默认辅助列中的值都已按升序排列，如找到0值后会认

为下面还有更大的值，继续查找，直到定位到最后一个0值为止。这样查找就能返回E2 单元格中货号 A 对应的最后一笔成交单价 87 。

对于货号B和C的查找，读者可以按照上述方法自行测试一下，加深对于LOOKUP(1,0) 函数套路的理解。

5.4.2 用模糊查找做精确查找的工作

LOOKUP是用于模糊查找的函数，但应用这个函数可以完成精确查找的工作，不论是

单条件还是多条件，也不管是不是所谓的反向查找都可以完成，但其前提是要用 LOOKUP(1,0)的函数套路。

·139·

第2篇 计算数据 在满足条件的值只有一个的情况下，使用LOOKUP(1,0)函数套路能达到使用 VLOOKUP{1,0}与INDEX+MATCH函数组合同等的效果。

沿用5.3节多列查找的例子，在B9单元格中录入如下公式，向右复制完成，不需要按 三键结束。

LOOKUP函数的第2参数“0/($B2:$B6&C1=$A9&B8)”相当于构建了一个仅有0和 #DIV/0!的内存数组（辅助列），将运算结果放在单元格中再次理解一下，如图5.17所示。

=LOOKUP(1,0/($B2:$B6&C1=$A9&B8),C2:C6)

图5.17 LOOKUP函数运行结果分析

在辅助列中查找1，只能找到一个0（其他都是错误值），因满足条件的仅有一个值， 模糊查找变成了精确查找。

关于多条件查找的其他示例读者也可以自行测试，在满足查找条件的返回值仅有一个 的情况下，使用LOOKUP(1,0)函数套路可以起到精确查找的效果。

本节示例文件：《 5.4 查找满足条件的最后一个值 .xlsx 》。

5.5 巧妙利用函数的模糊查找

上一节中讲到了LOOKUP函数的模糊查找，介绍了LOOKUP(1,0)函数套路查找满足 条件的最后一个值。其实不仅是LOOKUP函数，VLOOKUP和MATCH函数也可运用于 模糊查找， MATCH 函数还有两种模糊查找模式。

5.5.1 典型的区间查找案例

模糊查找最典型的应用是区间查找，下面举两个例子。 1．个人所得税计算

很多用户计算个人所得税时使用的是IF函数，因个人所得税是7级超额累进税率，用 IF函数公式写起来很麻烦，而且有错也不容易查核。这里介绍使用VLOOKUP函数的模糊 查找功能轻松搞定这个问题。

之前介绍过， VLOOKUP 函数第 4 个参数如使用 1 或者 TRUE ，是模糊查找模式。模 糊查找就是在查找区域的第1列中没有找到查找值时，并不简单地返回“#N/A”，而是根

·140 ·

第5章 海量数据分析的利器——初识查找引用函数 据模糊查找的原理返回比查找值小的最大值。

函数的帮助文件很难理解，我们还是通过实例来认识理解函数。

实例中B …… 此处隐藏：2431字，全部文档内容请下载后查看。喜欢就下载吧 ……