Xlookup函数新用法,居然能计算快递费,太强大了!
今天我们来学习下,如何做近似的区间匹配,这个也是一个粉丝提问的问题,感觉非常的典型,就写个文章来讲讲
如下图所示,我们需要根据右侧的费用表,来进行快递费用的匹配,其实对于这样的问题,我们利用Xlookup就行了,来看下我的解决方法
首先我们需要对数据源来做整理,取每个区间的最大值来对应的这个区间,构建一个新的表格,新表格如下图所示
最后一个数字可以设置一个永远也达不到的数字,在这里写的是100000,大家可以根据自己的需要来设置
公式:=XLOOKUP(I4,$B$3:$F$3,$B$4:$F$13,,1)
第一参数:I4,结果表中的重量
第二参数:$B$3:$F$3,查询表中的辅助数据
第三参数:$B$4:$F$13,查询表中的运费区域
第四参数:省略
第五参数:1,表示近似匹配
这个函数的关键点就是第五参数,近似匹配,设置为1就表示会找到【下一个比较大的结果】
如下图所示,我们查找的数字是1.5,表头中是没有1.5的,所以就会返回下一个较大的项,在当前的表头中,下一个较大的项是2,所以函数就会返回表头2对应的这一列数字,就是图中的黄色列
公式:=XLOOKUP(H4,$A$4:$A$13,XLOOKUP(I4,$B$3:$F$3,$B$4:$F$13,,1))
第一参数:H4,省份名称
第二参数:$A$4:$A$13,查找表中的省份列
第三参数:XLOOKUP(I4,$B$3:$F$3,$B$4:$F$13,,1)
这个就是Xlookup的常规用法,将我们上一步找到的数字对应的列,放入了当前Xlookup的第三参数中。
上面是获取了每个区间对应的价格,但是如果超过了3KG,每1gk是需要加1的,为了满足这个条件我们还需要使用IF函数来做条件判断
公式:=IF(I4>3,ROUND(I4,0)-3+XLOOKUP(H4,$A$4:$A$13,XLOOKUP(I4,$B$3:$F$3,$B$4:$F$13,,1)),XLOOKUP(H4,$A$4:$A$13,XLOOKUP(I4,$B$3:$F$3,$B$4:$F$13,,1)))
这个公式虽然很长,但是理解起来并不复杂,判断重量是否大于3,如果大于3就使用ROUND对重量四舍五入,结果减去3,再加上Xlookup,如果小于3就直接返回Xlookup
如果你想要提高工作效率,不想再求同事帮你解决各种Excel问题,可以了解下我的专栏,WPS用户也能使用,讲解了函数、图表、透视表、数据看板等常用功能,带你快速成为Excel高手
LOOKUP函数,典型用法二三例
这个函数有两个特点:
一:要求查询区域必须升序进行排序。如果没有经过排序,LOOKUP函数也会认为排在数据区域最后的内容,是该区域中最大的。
二:当查找不到具体的查询值时,会以比查询值小、并且最接近查询值的内容进行匹配。
另外,还能识别查询值是文本格式还是数值格式,再以相同类别的内容进行匹配。
1、查询A列中的最后一个文本
模式化公式为:
=LOOKUP(\”々\”,A:A )
\”々\”通常被看做是一个编码较大的字符,它的输入方法为<Alt+41385>组合键。
如果感觉每次写这个符号有点费事儿,也可以写成:
=LOOKUP(\”座\”,A:A )
一般情况下,第一参数写成“座”也可以返回一列或一行中的最后一个文本。
下图中,B列的部门是一些合并单元格,在C列使用LOOKUP就能填充完整。
=LOOKUP(\”座\”,B$2:B2)
第2参数使用了动态扩展的技巧,仅锁定起始单元格的地址,当公式下拉时,LOOKUP函数的查询区域不断扩大。
公式相当于是从B2开始,到公式所在行这个区域内,查找最后一个文本。
2、查询A列中的最后一个数值
模式化公式为:
=LOOKUP(9E307,A:A)
9E307被认为是接近Excel规范与限制允许键入最大数值的数,用它做查询值,可以返回一列或一行中的最后一个数值。
如果A列中的数据既有文本也有数值,想得到最后一个单元格内容,咱们可以写成这样:
=LOOKUP(1,0/(A:A<>\”\”),A:A)
3、逆向查询
下面这个表中,A:C列是员工基础信息表,分别是部门、姓名和职务。
现在要根据E5单元格中的员工姓名,在这个信息表中查询属于哪个部门,也就是咱们常说的逆向查询,就可以使用LOOKUP函数了。
F5单元格输入以下公式:
=LOOKUP(1,0/(B2:B10=E5),A2:A10)
得出的结果是“销售部”。
上面这个公式就是LOOKUP函数最典型用法。可以归纳为:
=LOOKUP(1,0/(条件区域=指定条件),目标区域或数组)
公式中的0/(条件区域=指定条件)部分,先使用等式对比条件是否符合,如果符合就返回逻辑值TRUE,否则返回FALSE。最终得到一个内存数组结果。
再使用0除以这个内存数组,0除以TRUE结果是0,0除以FALSE结果是错误值。
接下来使用1作为查询值,在内存数组中进行查找,由于找不到1,就用最后一个0进行匹配,并返回第三参数中同一位置的元素。
如果是多个条件,模式化的写法为:
=LOOKUP(1,0/(条件1)/(条件2)/(条件N),目标区域或数组)
4、查询产品类别
如下面这个图中所示,A列是产品名称,D列是类型对照表。
如果产品名称中包含对照表中的关键字,就显示出该内容。
B2单元格输入以下公式,向下复制。
=LOOKUP(1,-FIND(D$2:D$7,A2),D$2:D$7)
简单说说公式各部分的含义:
“FIND(D$2:D$7,A2)”部分:
首先用FIND函数,以D$2:D$7单元格中的类别关键字作为查询,在A2单元格中分别查询这些字符出现的位置,得到一个由错误值和数值组成的内存数组。
加上负号后,内存数组中的数值变成负数,错误值部分的结果不变。
接下来使用1作为查询值,在内存数组中进行查找,由于找不到具体的查找值,同时LOOKUP认为数组中最后一个数值一定是所有数值中最大的,因此以最后一个负数与之匹配,并返回第三参数中同一位置的元素。
好了今天咱们的分享就是这些吧,祝各位一天好心情~~
图文制作:祝洪忠
本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com
文章为作者独立观点不代本网立场,未经允许不得转载。