第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列为税前薪资，C列为扣除税前费用3,500后的应税额，在D列计算个人所
5.18 所示。 得税，不考虑缴纳社保等其他事项的影响，如图
图5.18 个人所得税计算表
1）问题分析
个调税 = 应税额 * 税率–速算扣除数
需要先求出税率和速算扣除数。这还是一个查找问题，只是查找返回值要根据查找值 （应税额）的分段区间，需要应用模糊查找。
2）解决方案1
直接输入公式比较麻烦，我们可以先找一个税率表，加工之后作为辅助区域，定义为 名称后使用。如图5.19所示，这是一个网上下载的最新税率表，其中B列的“分段”数字 是自行添加的，读者可以观察一下B列“分段”数字与A列“含税级距”之间的关系。
将表中 B2 ： D8 区域定义为名称“税率”，用于简化公式录入。
图5.19 个人所得税税率表
做好准备工作后，可以在“个人所得税计算表”的复制：
D2单元格输入如下公式并向下
3）公式解析
这个公式使用了两个VLOOKUP函数，第1个函数取得“税率”，第2个函数返回“速
F9 键来分析一下公式的分段运算结果。 算扣除数”。如对于计算结果不理解的话，可以用
对于D2单元格中的公式，在编辑栏中选取“VLOOKUP(C2,税率,2,1)”，按F9键， 返回结果是25。选择后一段VLOOKUP函数，按F9键，可以发现运算结果为1005。由此
速算扣除数是 1005。 可见，对于员工 A ，应税额 21 500 对应的税率为 25%，
·141·
=C2 * VLOOKUP(C2,税率,2,1)/100-VLOOKUP(C2,税率,3,1)