例题1:根据下表中的姓名,查找出定位
本例使用vlookup函数会更方便,大家都知道,在B11单元格输入:
=vlookup(a11,b1:d7,3,0)
但是如何用lookup函数计算呢?从而了解lookup函数的更高级方法:
向量型查找=lookup(lookup_value,lookup_vector,result_vector)
=lookup(查找的值,查找的范围,返回值的范围)
查找的值一般为数字,在此例中使用公式:
=LOOKUP(1,0/($B$2:$B$7=A11),$D$2:$D$7)
函数解读
公式一:B2:B7=A11
结果:{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
解读:lookup善长数字查找,将查找范围1,0转化
公式二:0/公式一
结果:{0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
解读:利用0/0等于错误值
公式三:lookup(1,公式二,D2:D7)
lookup函的两条工作思路:
(1)在参数2查找范围内查找参数1,如果找到相同的值,那么返回范围横向对应的值,显然本例找不到数据.
(2)在参数2查找范围内查找最接近并且小于参数1的值,输出返回范围横向对应的值,本例中0最接近并且小于1,所以输出结果为辅助
下表为诸葛亮的计算视图:
例题1:根据下表中的姓名,查找出编号
假如使得vlookup函数,则需要将原数据位置更换或使用数组
如果用lookup函数计算则非常方便
在此例中使用公式:
=LOOKUP(1,0/($B$2:$B$7=A11),$A$2:$A$7)
函数解读
公式一:B2:B7=A11
结果:{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
解读:lookup善长数字查找,将查找范围1,0转化
公式二:0/公式一
结果:{0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
解读:利用0/0等于错误值
公式三:lookup(1,公式二,A2:A7)
所以该例题 lookup中的参数一改成0及以上的数字都可以!
例题3:根据下表中的姓名及定位,查找出编号
假如使用vlookup函数,则需要使用数组公式:
=VLOOKUP(A12&B12,IF({1,0},B1:B8&D1:D8,A1:A8),2,0)
如果用lookup函数计算则更加方便
在此例中使用公式:
=LOOKUP(1,0/((B1:B8=A12)*(D1:D8=B12)),A1:A8)
函数解读
公式一:((B1:B8=A12)*(D1:D8=B12))
结果:{0;1;0;0;0;0;0;0}
这个时候参数2并不是唯一的数字且不是升序排列,故需用0除
公式二:0/公式一
结果:{0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}
这时参数2中唯一数字为0,并且符合小于参数1,故返回结果为A1
例题4:根据下表中学生成绩及成绩标准,将等级评分列算出
本例是可以是多种方法解决的,比如说IF函数的嵌套使用,或使用vlookup函数的模糊查询
先将分数等级进行可分析的表格重新定义,在G列输入满足该等级的最低分数
假如使用vlookup函数,因为匹配数据源是逆向的,所以使用
=VLOOKUP(B2,IF({1,0},$G$2:$G$7,$F$2:$F$7),2,1)
进行计算
如果用lookup函数计算就方便多了
在此例中使用公式:
=LOOKUP(B2,$G$2:$G$7,$F$2:$F$7)
函数解读
lookup函数进行模拟查找时
第2个参数需要进行升序排列
会在第2个参数列中查找小于等于并最接近参数1的数据
然后返回对应参数3所在列的值
这个参数2分数列小于等于84的值有
0,60,70,80
但是最近接于84的是80
所以对应参数3列的等级的良好
例题5:根据下表中学生成绩及成绩标准,将等级评分列算出
本例也是可以使用经典的vlookup函数进行求解的。便于理解,建立一个辅助列D
在D2输入公式:=B2&COUNTIF($B$2:B2,B2)
这个公式通过混合引用方式对物品列进行累计计数!和累计求和的原理一样。
然后在F2单元格输入公式:
=VLOOKUP(E2&COUNTIF($B$2:$B$10,E2),IF({1,0},$D$1:$D$10,$C$1:$C$10),2,0)
求出了最后一次进货的数量,对VLOOKUP函数不太懂的可以学习下小福的一小时从入门至精通微课录屏。
但是这个例子用lookup函数计算就方便多了
都不需要使用辅助列,在B14中使用公式:
=LOOKUP(1,0/($B$2:$B$10=A14),$C$2:$C$10)
直接一步到位,得到最终的结果:
函数解读
lookup函数进行模拟查找时
第2个参数需要进行升序排列
会在第2个参数列中查找小于等于并最接近参数1的数据
假如不是升序排列,Excel会默认为升序排列,如果有多个值都小于查找值,则会返回最后一个数值。
基于上述理论,对参数二进行计算之后,再使用lookup函数的解释如下:
最后一个小于1的数值0对应值为83,所以可乐返回结果为83
例题6:左边是某公式的流水数据,如何进行查找最后一条非空记录,如果数据进行新增,则查找的数据进行自动的变化求值?
1、查找最后一个非空文本
求最后一个非空物品,在物品列全都是文本型数据
在F2单元格输入:
=LOOKUP('々',B:B) 或
=LOOKUP('座',B:B)
々或座,在Excel中是编码比较大的字符,在现在查找匹配时,直接最后一个数据才能查找到
2、查找最后一个非空数字
求最后一个数量,在数量列全都数字型数据,
故需使用公式:
=LOOKUP(9E 307,C:C)
同样9E307被认为是接近Excel规范与限制允许键入最大数值的数,用它做查询值,可以返回一列或一行中的最后一个数值。
3、查找最后一个混合数据
在备注栏有数字又有文本,如何查找最后一条非空混合数据?
故需使用公式:
=LOOKUP(1,0/(D:D<>''),D:D)
这个公式类似于LOOKUP函数经典案例5
如果本例需要使用vlookup函数计算
则所有文本列查找最后一个文本:
=VLOOKUP('々',B:B,1)
所有数字列查找最后一个数字:
=VLOOKUP(9E 307,C:C,1)
函数解读
lookup函数进行模拟查找时
第2个参数需要进行升序排列
会在第2个参数列中查找小于等于并最接近参数1的数据
假如不是升序排列,Excel会默认为升序排列,如果有多个值都小于查找值,则会返回最后一个数值。
基于上述理论,对参数二进行计算之后,再使用lookup函数的解释如下:
最后一个参数省略,即返回对应参数二的结果,即最后一个非空值。
如何使用公式快速进行提取?
在B2单元格输入公式:
=LOOKUP(9^9,RIGHT(A2,ROW($1:$6))*1)
提取6个字符以内的数字,如果数字较大,更改6即可
公式一:RIGHT(A2,ROW($1:$6))
表示对A2单元格分别向右取1,2,3,4,5,6位数进行组成一个数组:
{'1';'21';'.21';'5.21';'你5.21';'欢你5.21'}
公式二:公式一*1
表示对生成的数组进行乘以1,屏蔽非字符数字
{1;21;0.21;5.21;#VALUE!;#VALUE!}
然后就是LOOKUP函数的向量应用,9的9次方是个很大的数,进行查找时会找到最后一个结果
如果数字在前方,例:666自学成才
则只需将RIGHT函数更改为LEFT即可
=LOOKUP(9^9,LEFT(A2,ROW($1:$6))*1)
如果数字在中间,如我喜欢666自学成才
则需要使用下面公式:
=LOOKUP(9^9,MID(A2,MATCH(1,MID(A2,ROW(1:6),1)^0,0),ROW(1:6))*1)
从A1:B5的原始数据库中,查找出杭州、长沙区域的数量
如果用IF函数,则需要使用IF 通配符进行解决
=VLOOKUP('*'&A9&'*',$A$1:$B$5,2,0)
需要在A9前后使用&连接通配符号*,用双引号进行连接
如果使用Lookup函数,处理起来也比较简单
在B9输入公式:
=LOOKUP(9^9,FIND(A9,$A$1:$A$5),$B$1:$B$5)
函数解读
以杭州查找为例,B9的计算过程如下:
首先对FIND(A9,$A$1:$A$5)进行计算
得到下面的效果
然后使用Lookup函数进行求解
所以得到的结果为:88
因此这个杭州所对应的原始数据中必需只有一个,否则也会出现意外的结果。
所以,你学会了吗?
有什么问题欢迎留言!
联系客服