Excel xlookup使用指南
xlookup语法
=XLOOKUP(查找值,查找范围,结果范围,[容错值],[匹配方式],[查询模式]
# 只有前三个是必须的,后面均可省略。
1) 单条件查询
如下图所示,B:D列是数据明细,需要根据F列姓名查询相关电话号码。
公式如下:
G2输入公式▼ =XLOOKUP(F2,B:B,D:D)
F2是查找值,B列是查找范围,D列是结果范围,公式的意思也就是在B列查找F2,找到后返回D列对应的结果。
2) 容错查询
如下图所示,B:D列是数据明细,需要根据F列姓名查询相关电话号码,但和上一个案例所不同的是,如果查无结果,需要返回指定值:查无结果。
公式如下:
G2输入公式▼ =XLOOKUP(F2,B:B,D:D,”查无”)
XLOOKUP的第4参数可以指定容错值,当查无结果时避免返回错误值#N/A,省去了外围再嵌套一个IFERROR函数。
3) 模糊条件查询
如下图所示,A:B列是数据明细,需要根据F列姓名的简称查询相关特长。这是一个模糊查询的示例,比如查找星光,对应的结果为看见星光。
公式如下:
E2输入公式▼ =XLOOKUP(““&D2&”“,A:A,B:B,”查无”,2)
XLOOKUP的查找值是"*"&D2&"*",*是通配符,可以代替0到多个字符串,"*"&D2&"*"也就指包含D2的字符串。
但和VLOOKUP所不同的是,XLOOKUP默认不支持通配符匹配,只有将第5参数设置为常数2时,才支持通配符匹配。
XLOOKUP的第5参数可以指定匹配方式。
4) 区间查询
如下图所示,F:G列是评分标准,60以下不及格,80以下及格等,需要根据该评分标准,对C列的成绩计算评级。
公式如下:
D2输入公式▼=XLOOKUP(C2,F2:F5,G2:G5,””,-1)
XLOOKUP第5参数为-1,指定了匹配方式是’精确匹配或下一个较小的项’,比如查找84,找不到精确匹配,则寻找比它小的项,也就是80,然后取其对应结果:’良好’。
这儿的XLOOKUP等同于LOOKUP函数▼
=LOOKUP(C2,F:G)
但和LOOKUP所不同的是,XLOOKUP函数不要求查找区域首列数据升序排列,即便把F:G列的数据打乱了,也不妨碍它寻找’精确匹配或下一个较小的项’的计算规则▼
除此之外,XLOOKUP还支持’精确匹配或下一个较大的项’的计算规则▼=XLOOKUP(C2,F2:F5,G2:G5,””,1)第5参数指定值为1,比如查找80,找不到精确匹配,则寻找比它大的项,也就是90。
5) 查询符合条件的最后一个结果
如下图所示,A:C列是数据明细,其中日期字段升序排列。需要根据E列姓名查询相关销售额,但和前面案例所不同的是,它需要查找每个人最后一次销售额,也就是符合条件的最后一条记录。
公式如下:
F2输入公式▼ =XLOOKUP(E2,B:B,C:C,”查无”,0,-1)
XLOOKUP的第6参数可以指定查询方式,默认是从前往后找,找到即止;此外也可以从后往前找, 找到即止;如果数据源有排序,还可以执行二分法查找。
本例是寻找符合查询条件的最后一条记录,需要从后往前找, 找到即止,也就是将第6参数设置为-1。
6) 二分法查询
如下图所示,A:C列是数据源,其中姓名列有升序排序,现在需要根据E列姓名查询相关电话号码。
公式如下:
F2输入公式▼ =XLOOKUP(E2,A:A,C:C,”查无”,0,2)
第6参数指定值为2,查找方式是升序排序情况下的二分法查找。
公式如下:
F2输入公式▼ =XLOOKUP(E2,A:A,C:C,”查无”,0,2)
第6参数指定值为2,查找方式是升序排序情况下的二分法查找。
这里也可以使用公式:
=XLOOKUP(E2,A:A,C:C,”查无”)
两者相比有何不同呢?
主要是查询方式的区别。后者是从前往后找,虽然说找到即止,但效率也不是很高。后者是二分法查找,效率非常高。
比如查询看见星光,前者要从第1行开始遍历,找到第10行才找到结果,它需要找10次。而后者折半查找,只需要找3次就可以了。数据量越大后者的效率优势就越高——不过后者要求查询范围需排序处理。
7) 横向查询
如下图所示,A:D列是数据明细,需要根据F1指定的科目查询对应的成绩。
公式如下:
F2输入公式▼ =XLOOKUP(F1,B1:D1,B2:D2)
当查询范围是一个横向区域时,XLOOKUP也就可以像HLOOKUP一样,实现横向数据查询。
8) 多列数据查询
如下图所示,A:D列是数据明细,需要根据F列的姓名,查询对应的特长、电话和得分等多列数据。
公式如下:
G2输入公式▼=XLOOKUP(F2,A:
当结果范围是一个多行多列的区域时,XLOOKUP可以根据查询范围的行列特性,返回一个多行或多列的结果区域。本例中查找范围是单列(A列),结果范围是B:D列,因此返回B:D列多列结果。
9) 交叉表查询
如下图所示,A:D列是数据明细,需要根据F列的姓名,查询对应的电话、特长和得分等多列数据。和上面的案例所不同的是,结果表的字段排列顺序和数据源不一致,也就是通常所说的交叉表查询了。
公式如下:
G2输入公式▼=XLOOKUP(F2,A2:A11,XLOOKUP(G1,B1:D1,B2:D11))
公式使用了两个XLOOKUP函数。
先说XLOOKUP(G1,B1:D1,B2:D
上面解释过,当结果范围是一个多行多列的区域时,XLOOKUP可以根据查询范围的行列特性,返回一个多行或多列的结果区域。本例中查找范围是单行(B1:D1),结果范围是B2:D11,因此返回一个多行单列数据。
比如查找G1的值为’电话’,则返回C2:C11。以此作为第2个XLOOKUP的结果范围。
10) 多条件查询
如下图所示,A:C列是数据明细,需要根据E列的年和F列的姓名,查询对应的得分。
公式如下:
G2输入公式▼=XLOOKUP(E2&F2,A11&B2:B11,C2:C11)
XLOOKUP支持数组运算,本例中查找值为E2&F2,查找范围是年字段&姓名字段,即A11&B2:B11▼
11) 区域查询
如下图所示,A:B列是数据明细,A列日期升序排列。需要查询E1单元格指定开始日期和E2单元格指定结束日期之间的金额合计。
公式如下:
E3输入公式▼ =SUM(XLOOKUP(E1,A:A,B:B):XLOOKUP(E2,A:A,B:B))
和VLOOKUP不同,和INDEX函数相同,XLOOKUP返回的不是一个单纯的值,而是一单元格引用;因此XLOOKUP(E1,A:A,B:B)返回的是B4单元格的引用,XLOOKUP(E2,A:A,B:B)返回B8单元格的引用,B4:B8也就是目标金额区域,最后使用SUM函数求和即可。
12) 动态表查询
如下图所示,一张工作簿包含了2017年、2018年、2019年等多张工作表,现在需要根据B1单元格指定的工作表名称,在其中查询A列相关人名的得分。
公式如下:
B4输入公式▼=XLOOKUP(A4,INDIRECT(B1&”!A:A”),INDIRECT(B1&”!B:B”))
公式使用INDIRECT函数根据B1单元格指定的工作表名称构建引用范围,其中查找范围是指定表的A列,结果范围是指定表的B列。
相关文章
- pycharm界面颜色设置_Excel护眼色打印
- MS SQL SERVER导出表结构到Excel
- 【说站】Excel如何快速删除空行?WPS删除excel空白行
- poi导出excel动态表头并合并
- excel旭日图_旭日图怎么画
- Excel图表学习:漏斗图2
- Excel技术:如何在一个工作表中筛选并获取另一工作表中的数据
- Excel VBA 通过字典进行数据计数/求和
- 导出excel详解编程语言
- MySQL查询结果导出至Excel:一步到位(mysql查询结果导出excel)
- 轻松实现Excel导入MySQL数据库(excel导入mysql数据库)
- 快速安装Excel:Linux用户指南(linux安装excel)
- 极速提升业务效率:利用Excel快速导入Oracle数据库(excel数据导入oracle)
- 轻松操作:oracle快速导入Excel数据(oracle导入excel数据)
- 步骤简单!轻松实现SQL Server导入Excel操作(sqlserver导入excel)
- 将Excel表格数据导入MySQL数据库(excel到mysql)
- MSSQL导入Excel数据的精彩实战(mssql读excel)
- Oracle将数据输出到Excel中(oracle写excel)
- Excel中抓取Oracle数据的有效方法(excel抓oracle)
- 从Excel快速导入Oracle数据库(excel到oracle)
- js读取本地excel文档数据的代码