「函数」学习系列第6篇
今天介绍Excel函数中比较难搞但功能十分强大的函数——Lookup,它与vlookup函数属于一系列,但应用场景更丰富。
01查询最后一个符合条件的值
第1个例子是查询最后一个符合条件的值。比如A1:C13区域放置着股票的收盘价数据,现在要查找“银河生物”最后一天的收盘价。如果用vlookup函数,则会查找到“银河生物”第一天的收盘价(vlookup函数的用法参见这篇)。用Lookup函数编写的公式如下:
=LOOKUP(1,0/(B2:B13=E3),C2:C13)
公式解读:
1、第1个参数是查找值,这里赋值为1.
2、第2个参数是查找区域0/(B2:B13=E3),返回一个结果数组
{0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!}
3、第3个参数是结果区域。Lookup函数是在查找区域从下往上找小于或等于第1个参数值的最大值进行匹配。
02查询最后一行非空值
第2个例子是查询最后一行非空值,也就是定位数据区域的最后一行(13行)。这个动态获取最后非空行的功能,其他函数如counta,或者VBA的range.end方法都可以实现。来看看lookup函数怎么来实现:
=LOOKUP(1,0/(B2:B23<>''),B2:B23)
公式解读:
查找区域0/(B2:B23<>''),返回的数组中空行都返回错误值#DIV/0!,lookup函数在查找的时候会跳过错误值,这也是Lookup函数能有这么丰富应用场景的原因所在。
03双条件查找
以前写过用vlookup函数和if({1,0})结合进行双条件查询,Lookup函数也能实现这个功能,而且更强大。
公式解读:
第2个参数0/((A2:A13=E3)*(B2:B13=F3)),当条件1(日期=2016/1/2)和条件2(股票=河北宣工)都为真时,其值为1,否则为错误值#DIV/0!。
按照这个思路,Lookup函数可以轻松实现多条件查找,只要把第2个参数写成以下格式:
0/((条件1=值1)(条件2=值2)...(条件n=值n))
04模糊查找
Lookup函数还可以实现模糊查找的功能,但得结合Find函数一起使用。比如只根据股票名称的部分字符来查找对应的收盘价。
=LOOKUP(9^9,FIND(E3,B2:B13),C2:C13)
公式解读:
首先用Find函数返回是否包含“银河”这两个字的数组。如果包含,则返回“银河”这两个字符所在的位置,如果不包含则返回错误值#VALUE!。第1个参数设置为9^9,只是为了防错,只要数字足够大就行。
文件分享下载:
https://pan.baidu.com/s/1nvi3VEL