Excel 中强大的查询函数:XLOOKUP
消息来源:baojiabao.com 作者: 发布时间:2024-05-13
原文标题:《比 Vlookup 更逆天!这个强大的查询函数,看完我就跪了!》
我们今天来讲讲 XLOOKUP,一个强大的查询函数!
在说 XLOOKUP 函数之前,咱们先看看下面这个案例,根据姓名查找对应的性别。
这个问题太常见了!首先想到的就是用 VLOOKUP 函数。
=VLOOKUP(E2,A2:C7,2,0)
在查找区域 A2:C7 的首列找到 E2 单元格的值「王五」,返回查找区域 A2:C7 第 2 列与之对应的值「男」。
初识 XLOOKUP 函数
再来看看 XLOOKUP 函数的用法:
=XLOOKUP(查找的值,查找范围,结果范围)
公式就可以这样写:
=XLOOKUP(E2,A2:A7,B2:B7)
在查找范围 A2:A7 中找到 E2 单元格的值「王五」,返回 B2:B7 对应的值「男」。
如果姓名这列不是在前面,用 VLOOKUP 函数似乎就不太合适了。因为 VLOOKUP 函数的规则是在查找区域的首列查找。
这种情况下,我们通常会用 INDEX 函数和 MATCH 函数组合写公式:
=INDEX(A2:A7,MATCH(E2,B2:B7,0))
MATCH 函数找出 E2 的值「王五」在 B2:B7 中是第几行,得到结果 3,然后用 INDEX 函数将 A2:A7 的第 3 行的值引用出来,得到结果「男」。
而 XLOOKUP 函数就不一样了,它不会像 VLOOKUP 那样受位置的影响,依然照用不误:
=XLOOKUP(E2,B2:B7,A2:A7)
在查找区域 B2:B7 中找到 E2 单元格的值「王五」,返回 A2:A7 对应的值「男」。
XLOOKUP 函数的第 4 参数
经常有小伙伴提这样的问题,如何让 VLOOKUP 查找不到的数据返回为空值?
如下图,VLOOKUP 函数在查找区域 A2:B7 的首列没有找到单元格的值「孙二」, 就会返回错误值#N / A。
=VLOOKUP(D2,A2:B7,2,0)
通常我们都会在 VLOOKUP 函数外层嵌套 IFERROR 函数,或者用 IFNA 函数来容错。
=IFNA(VLOOKUP(D2,A2:B7,2,0),"")
而 XLOOKUP 函数有它专属的参数:它的第 4 个参数专门负责容错。
=XLOOKUP(查找值,查找范围,返回范围,[容错])
这个参数是非必需参数,当你碰到上面这种问题,才有必要把它请出来。
公式就可以写成:
=XLOOKUP(D2,A2:A7,B2:B7,"")
在查找范围 A2:A7 中找 E2 单元格的值「孙二」,如果有找到就返回 B2:B7 对应的值,如果没有找到,则返回第 4 参数指定的内容「""」。
当然第 4 参数的设定并非只可以是字符串,数值。也可以嵌套其它的公式返回结果。
XLOOKUP 函数的第 5 参数
以下是评定的规则:
小于 60 分不合格;
大于等于 60 小于 70 为合格;
大于等于 70 小于 80 为良好;
大于等于 80 为优秀。
先为每个等级设置分数的下限,如下图 A 列,然后在 E2 单元格写入公式:
=XLOOKUP(D2,A2:A5,B2:B5,,-1)
XLOOKUP 函数的第 5 参数是匹配类型。
=XLOOKUP(查找值,查找范围,返回范围,[容错], [匹配类型])
当第 5 参数的值为-1 时,表示如果「查找值」没有在「查找范围」中,就返回下一个较小的值。
如上面的公式中,D2 单元格的值是 75,没有在查找区域 A2:A7 中,就找比 75 小的值,即 70。再返回 B2:B5 对应的等级「良好」。
如果把 A 列的分数下限改成上限,公式就可以这样写:
=XLOOKUP(D2,A2:A5,B2:B5,,1)
当第 5 参数为 1 时,表示如果「查找值」没有在「查找范围」中,就返回下一个较大的值。
如公式中,D2 单元格的值是 75,没有在查找区域 A2:A7 中,就找比 75 大的值,即 79。再返回 B2:B5 对应的等级「良好」。
写到最后
关于 VLOOKUP、XLOOKUP 和 LOOKUP 有哪些区别,以下 Tips 供大家参考:
❶ VLOOKUP 函数必须在查找区域的首列查找,而 XLOOKUP 函数不受这种位置限制;
❷ VLOOKUP 函数需要其它函数嵌套来容错,而 XLOOKUP 函数有自己的参数做容错处理,更方便;
❸ LOOKUP 函数在多值判断时,需要升序排序,而 XLOOKUP 函数可以不用排序。
本文来自微信公众号:秋叶 Excel (ID:excel100),作者:赵骄阳,编辑:竺兰
2023-02-12 18:46:04相关文章
- 美国法院裁定阿里须为Squishmallows玩具侵权案答辩
2023-12-28 19:59:34
- 小米汽车传员工3700人 雷军称小米汽车不可能卖9万9
2023-12-28 19:41:57
- 国家新闻出版署:认真研究《网络游戏管理办法(草桉徵求意见稿)》关切 实行前进一步完善
2023-12-28 19:14:56
- 天猫新规可以无条件申请“仅退款”了?淘宝天猫又离狗多多零元购近了一步
2023-12-28 18:57:55
- 印度以打击金融犯罪为由逮捕了两名 vivo 高管
2023-12-26 16:49:01
- 在国外微信收不到国内信息?微信和WeChat将被拆分
2023-12-15 10:40:15
- 苹果iPhone15 系列手机发布最新消息 预计上市发布时间9月
2023-08-06 23:21:02
- 华为将发布鸿蒙HarmonyOS4操作系统 功能五大升级支持设备清单
2023-08-06 23:17:37
- 整治自媒体网红账号 400万粉丝网红发布擦边视频被无限期封禁
2023-07-12 09:56:09
- 网传微信文件传输助手是真人是真的吗?微信官方回应
2023-06-27 15:53:32
- 电信移动送手机成了“信用购”?你上了运营商的贷款套路了吗?
2023-06-12 17:18:55
- 中国电信广东地区崩了无信号 客服回应已在核实处理
2023-06-08 15:39:04
- 消息称小米新能源汽车价格表正讨论定价区间:双版本不同配置,高配或超 35 万元
2023-03-06 12:56:03
- 华为因制裁被传或分拆剥离手机业务? 内部人士回应:可能性不大.
2023-03-05 23:26:41
- OPPO正式发布安第斯智能云,让终端更智能
2023-02-24 16:02:27
- 华为与OPPO签订全球专利交叉许可协议 包括5G蜂窝通信专利
2023-02-24 16:02:26
- 老蛙将推MINI镜头新品:目前未知具体规格 官宣将于12月20日发布
2023-02-24 16:02:26
- 首发全新35mm定制光学系统 努比亚Z50性能同样强悍
2023-02-24 16:02:25
- Redmi K60屏幕细节曝光:全系标配2K护眼柔性直屏+5000mAh大容量电池
2023-02-24 16:02:25
- OPPO Find N2今天发 合金金属折叠屏更轻了
2023-02-24 16:02:24