38岁的VLOOKUP要光荣退休了?这个新函数好用到爆哭!! |
您所在的位置:网站首页 › 在文末输入公式是什么意思啊 › 38岁的VLOOKUP要光荣退休了?这个新函数好用到爆哭!! |
Vlookup 可以说是 Excel 使用最广泛的函数之一。 不知不觉,它已经陪我们度过了 38 个春秋,它让千千万万人免于加班脱发之苦,然鹅…… 早在 2019 年,微软官方就放了个大招,发布 Xlookup 函数,但是只有 Office 365 版本才可以使用;不过现在下载新版 WPS 版,也可以免费使用 Xlookup 函数了。 和 Vlookup 相比,只是字母从 V 变成了 X,功能却强大了好几倍!
接下来,我们先了解一下「什么是 Xlookup?」,再通过 6 个常见的需求,看看 Xlookup 函数到底厉害在哪儿~ PS.本文演示版本:WPS Office 最新版;练习文件获取方式见文末。 普通查找 Vlookup 我们都再熟悉不过啦,主要就是查询匹配数据。
Xlookup 的用法也是一样滴,But 它还可以看做是 Lookup 家族的合体版!! Lookup 家族:Vlookup、Hlookup、Lookup、Index、Match。(可见合体版有多强大……) 举个栗子! 这里我们要根据「姓名」,查询每个人的「在职状态」。
用 Xlookup 来实现,简单的很~
在 J4 单元格中输入公式: =XLOOKUP(I4,C4:C8,E4:E8) 公式是啥意思呢?解释一下: =XLOOKUP(要查找的值,查找的区域,返回的区域)
对应上图一起来看,是不是很好理解。
Xlookup 函数的优势就在于,在确定要查找的区域、返回的区域时,我们可以直接选中一整列,比如这里就选中了 C 列、E 列数据~
这样公式只需 3 个参数就搞定啦!
But 用 Vlookup,我们需要写 4 个参数……
而且!要一次性选中整个区域,再来挨个数数,看返回的区域在整个区域中排第几位……
公式如下: =VLOOKUP(I4,C3:E19,3,0) 解释一下就是: =VLOOKUP(要查找的数值,查找的区域,查找返回列,查找模式)
这样一比较,你是不是发现 Xlookup 函数在普通查找中表现更优秀? 多条件查找 写到这里隔壁小 E 跑过来吐槽:
切,不就是少了一个参数,值得把 Xlookup 吹上天吗?你个喜新厌旧的坏人!
那我们再来看一个案例,还是根据「姓名」,查询「在职状态」。
但是……眼神好的同学一定发现啦,数据中有两个同名的「小叶」,直接查找一定会出错!
所以,要同时根据「部门」和「姓名」,来查找在职状态~
先用 Xlookup 函数,把公式写出来给你们看:
什么意思呢?思路是这样的:
❶ 把查询结果中,「部门 I 列」和「姓名 J 列」合并,一起作为「要查找的值」: =XLOOKUP(I5&J5 ❷ 选择「查找区域」时,也把「部门 B 列」和「姓名 C 列」合并起来查找: =XLOOKUP(I5&J5,B4:B12&C4:C12 ❸ 最后,选择「返回的区域」为: =XLOOKUP(I5&J5,B4:B12&C4:C12,E4:E12) 我们会发现,Xlookup 函数居然可以直接用&符号,把列合并起来!这也太方便了吧! 但同样的思路,用 Vlookup 就复杂了……
光说这公式,我就先晕辽: =VLOOKUP(I5&J5,IF({1,0},B4:B12&C4:C12,E4:E12),2,0) 参数 1,用&符号,把部门和姓名合并在了一起。参数 2,用 IF 函数,构建查找列 B4:B12&C4:C12 和返回列 E4:E12。参数 3,返回第 2 列。参数 4,精确查找。参数 2 的写法,实在是看不懂啊。这里还构建了一个动态区域: IF({1,0},B4:B12&C4:C12,E4:E12) 这段公式相当于构建了下面的数据: 看不明白吧?看不明白就对了! 这不就说明了,Xlookup 更好用嘛! 未找到值 当我们在查找一个不存在的值,表格通常会返回一个错误乱码。 这时,在 Xlookup 中,可以添加一个较为特殊的参数[未找到值],它的作用是在我们查找不存在的值时,能够返回指定内容。 来看看这个案例,查询「丽丽」所在的「入职日期」。 用 Xlookup 函数查询很简单,公式如下: =XLOOKUP(I5,C4:C12,F4:F12) 但是结果……是这样滴…… 这说明公司里并没有这名员工,所以入职日期这一栏才会显示#N/A。 如果我们在[未找到值]填入一个信息,比如「查无此人」(需要用英文双引号框起来),那么单元格就会显示设定好的内容: 而 Vlookup,不用说,它并没有这样的功能。 反向查找 前面 3 个案例,相信大家已经感受到 Xlookup 的简单、好用了,但是离上天,还差点儿。 再来看这个需求,要根据「姓名」查询「部门」。 非常简单嘛,和按「姓名」查找「在职状态」没两样啊?! fine,咱先不说 Xlookup,没有对比就没有伤害,先看 Vlookup。 用过 Vlookup 同学都知道,它有一个通病:只能从左往右查找。 也就是说这里需要「姓名」在左边,「部门」在右边,才方便查找。 而表格中「部门」在左边,所以查找起来会很麻烦。 公式如下: =VLOOKUP(I5,IF({1,0},C4:C12,B4:B12),2,0)▲ 左右滑动查看 我天,又是 IF({1,0})的方法,再见。 再看 Xlookup,一如既往地简单优雅: =XLOOKUP(I5,C4:C12,B4:B12)
Xlookup 在选择时,只需要分别选择查找列和返回列就行,所以根本不存在左右的问题~ 再说了,这里查找「小叶」的部门时,因为有两个小叶(重名)。 而 Vlookup 默认只能查找到第 1 条记录,也就是「生产部」。 如果我想查找在「客服部」的「小叶」,要怎么写呢? 给 Xlookup 加个参数「0,-1」就可以了: =XLOOKUP(I5,C4:C12,B4:B12,,0,-1)
「0,-1」这个参数并不难,来解释一下: 0 表示匹配模式为精确匹配。-1 表示从下往上查找;如果输入 1,表示从上往下查找;输入 2,表示升序排序的二进制文件搜索;输入-2:表示降序排序的二进制文件搜索。(后两种搜索模式一般不用)所以填写 -1,就能找到最下面位于「客服部」的「小叶」~ 一对多查找 现在我们要根据姓名,把员工的全部信息都查找出来,共计 4 列,所以返回值也有 4 个。 如果用 Vlookup 函数,为了解决返回列变化的问题,需要结合 Column 函数来写公式。 =VLOOKUP($I5,$C$4:$G$12,COLUMN(B1),0) 公式填写好之后,向右拖动填充即可。 但……如果你不会 Column 函数,解决这个问题最好的方法,就是赶紧关掉 Excel,眼不见为净。 在这个问题上,Xlookup 的处理方式会更高级。 简简单单一个公式就搞定: =XLOOKUP(I5,C4:C12,D4:G12) 注意!返回数组须框选所有区域,输入公式后按下【Ctrl+Shift+Enter】 才能得出正确结果。 奥秘就在第 3 个参数「D3:G12」上。 参数 3,选择返回列的时候,把所有需要返回的列,一次性都选上。 聪明的 Xlookup 同学,会根据返回列的列数,自动填充相邻的数据列~ 高效又简单,大家快给我夸夸! 模糊查找 这里我们要根据 G 列的「绩效」,算出 I 列的「绩效评比」结果。 评比规则如下: 我猜,很多人遇到这种问题,都会写长长的 IF 函数吧? =IF(G4>=100,"A+",IF(G4>=90,"A",IF(G4>=80,"B",IF(G4>=70,"C",IF(G4>=60,"D","E"))))) 这种情况其实可以用 Vlookup 解决~ 在一些绩效、提成的计算上,用 Vlookup 模糊查找,可以避免反复地写 IF 函数嵌套。 在 I4 列输入公式: =VLOOKUP(G4,$L$4:$M$9,2,1) 糟糕,好像翻车了…… 小伙伴们注意啦!!! 在使用这种模糊查找方法时,「绩效」列的数字必须从小到大排序,否则查询就会出错。 正确操作: 但是!没错,但是来了—— Xlookup 的解决方法,更加简单易懂,改一下参数就好。 =XLOOKUP(G4,$L$4:$L$9,$M$4:$M$9,,-1) 前 3 个参数和 Vlookup 道理一样,重点是第 4 个参数:设置查询匹配的模式。 参数 4 有下面几种用法: 0 表示精确匹配,若未找到所查找内容返回#N/A。 2 表示可使用通配符匹配 。 1 表示精确匹配,若未找到所查找内容返回较大项。 -1 表示精确匹配,若未找到所查找内容返回较小项。 这样一来,不管「绩效」列的数字按什么顺序排,都不会影响我们查找结果! 又比 Vlookup 函数节省了好几分钟呢~写在最后怎么样,看完这篇文章,是不是感觉 Xlookup 比 Vlookup 强大了好几倍呢? 如果你有不同的见解,也欢迎积极留言交流哦~ 如果这篇文章对你有帮助,请帮忙「点赞」「在看」「转发」。 这对我很重要,能给我更多动力,持续分享优质的内容。 秋叶家福利来袭——《3 天 Excel 集训体验营》 专为职场职场人准备,全部基于职场真实表格案例设计,超实用 Excel 技巧集合教学。 从此不愿再为表格而加班,那就行动起来!3 天搞定表格难题! 《秋叶 Excel 3 天集训营》 想提升,趁现在! 不再被加班支配,不再为表格发愁!免费赠送【35 个常用函数说明】哦! 赶紧扫码预约吧! 👇👇👇 *广告 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |