大家好,今天和大家分享“Lookup函数的常规用法',这个函数确实是一个难的函数,但同时也是使用频率较高的函数,早在6年前,我见到QQ群里,论坛上,谁会用这个函数,我就把谁当作函数高手。许多朋友想学,但是又学不会,下面跟我来学下,看你读完我这文章之后,是否学会了没有?
一、参数讲解
1、两个参数的形式 =lookup(lookup_value,array)
第1参数:查找值
第2参数:数据源,返回第2参数最后一列
要求第2参数的第1列要升序排序,否则会报错
当然你通过其它方法处理,也可以不排序
2、三个参数的形式 =lookup(lookup_value,lookup_vector,result_vector)
第1参数查找值
第2参数:查找值所在的数据源,一行、一列的一维引用,或者是一维数组
第3参数:一行、一列的一维引用,或者是一维数组,根据第2参数的定位,返回定位到这个结果,
3个参数的形式有的人也叫做向量查找
第2参数也要求升序排序
3、二分法原理
有的朋友有一种爱学习,爱钻研,确实是一种好的习惯,但同时这也有个缺点,弄懂要花时间和精力,如空调可以制冷,也可以制热,你购买了一个空调,你也去研究它的原理,没有这个必要,会用就可以了,同理,lookup函数也有一个二分法应用,大家没有必要研究这个原理,会用就行了,当然我这个说法也许是错的,不能说我的就是全对的,大家因人而异吧,但是大家必须要记住这一点,如果lookup函数第1参数查找值大于第2参数的第1列最大值,且不能等于,也就是查找值大于第2参数第1列所有值,就定位第2参数第1列最后一个值的位置,如果只有2个参数,就返回定位这个值,如果第3个参数,那么就返回第3参数这个值;第2参数也不用升序排序;错误值不参入
二、案例讲解
1、根据销售额算提成
要求:小于10000的没有提成;大于等于10000且小于30000的提成为0.01;大于等于30000且小于50000的提成为0.02;大于等于50000且小于100000提成为0.04;大于等于100000提成为0.05
I、公式截图
II、公式
=LOOKUP(A2,$D$1:$E$5)*A2
III、公式解释
第1参数查找值
第2参数:数据源D1:E5,第2参数的第1列,也就是D列进行了升序排序,返回第2参数的最后一列,也就是E列
提到提成率之后再乘以本身销售额
2、查找A列最后一个数值
I、公式截图
II、公式
=LOOKUP(9E 307,A:A)
III、公式解释
查找值9E 307是Excel最大的数值
根据二分法原理,当查找值大于第2参数最大值,这里是2个参数,那么就返回最后一个数值
3、查找A列最后一个文本值
I、公式截图
II、公式
=LOOKUP('々',A:A)
III、公式解释
“々”这个排序是排序所有汉字之后
“々”这个是怎样打出来,如果台式电脑:按住Alt键不放,然后依次敲小数字键盘上的,不是主键盘上的数字41385;如果笔记本还要多加个Fn,有的笔记本也不要加;如果你确实还不出来,来个死的=char(41385)就是返回“々'
查找值“々”大于任意一个汉字,所以会定位到最后一个文本
4、查找A列最后一个值
I、公式截图
II、公式
=LOOKUP(1,0/(A:A<>''),A:A)
III、公式解释
大家一看就会提问,为什么lookup函数查找值是1,和第2参数毛线关系都没有,是的,这是你初看,也理解你,因为你的函数功力不够,导致你的眼力不够,昨天朋友圈刷屏了的话:要有鹰的一样眼光,像儿狼一样的精神,像熊一样的胆量,像豹一样的速度”
(A:A<>'')返回的是true和Falses构建的一维数组,另0/true=0; 0/false报错,也就是说第2参数是由0和错误值组成,且错误值不参入,那么只剩下0了,查找值是1,现在大家知道1和0有没有关系,有,当查找值1大于第2参数最大值0,返回最后一个0位置的值,返回“曹丽”
5、反向查找
I、公式截图
II、公式
=LOOKUP(1,0/(B1:B4=D2),A1:A4)
III、公式解释
这个原理有案例4一样
6、双条件查找
I、公式截图
II、公式
=LOOKUP(1,0/((A1:A5=E2)*(B1:B5=F2)),C1:C5)
III、公式解释
如果第2参数两个条件都满足,那么就返回1,否则就返回0,因为两个条件中间用的是乘号
0/0报错;0/1=0,这样把第2参数构建了0和报错组成的一维数组
其它的我就不作解释了
7、应用于提取数字
I、公式截图
II、公式
=-LOOKUP(1,-RIGHT(A1,ROW($1:$9)))
III、公式解释
lookup函数第2参数用一个函数Right,Right函数两个参数,第1参数要处理的数据;第2参数从那个位置开始提取,这里是数组用法,从右边提取一个是3,提取2个是23,提取3个是123,提取4个饭123,这里我就不说下去了,由于Right是文本函数,提取数字是文本,加一个负号,把文本双引号去掉了,变成了数值型数字,不过是负数
lookup函数第2参数最大值是0,其它都是负数,所以lookup函数不能用0,一定要用1,也就是第1参数查找值大于第2参数最大值,且不能等于
为什么前面还要加个负号,因为我刚才得到值学是负数,所以还要加个负号还原成正数
8、实现模糊查找
要求:搬运工显示搬运工,叉车司机显示叉车司机,清洁工,厨师,帮厨,电工,保安,门卫,食堂都显示后勤
I、公式截图
II、公式
=LOOKUP(FIND(A2,'搬运工叉车司机清洁工厨师帮厨电工保安门卫食堂'),{1,4,8},{'搬运工','叉车司机','后勤'})
III、公式解释
find这个函数3个参数,第1参数:查找值,第2参数:从那一串中找;第3参数从那个位置开始,如果不写就是从第1个位置开始
这里巧妙根据题构建了find的第2参数:'搬运工叉车司机清洁工厨师帮厨电工保安门卫食堂',搬运工在第1位置,叉车司机在第4个位置;其它全部在第8个位置,及后面,也就是find只得到这3个值1,4,8,所以构建lookup第2参数为常量数组{1,4,8},lookup函数第3参数为常量数组'叉车司机','后勤'}
三、新班开班通知
2017-10-22函数初级开新班
1、上课时间:每周星期天晚上8点到10点上课
2、上课方式:网络在线听课和视频回放两种方式
3、上课教室:YY房间141797
4、学费:100
5、报名方式:直接加”佛山小老鼠微信:18664243619“,发红包100元
联系客服