韩老师自己的书,纳入国家十三五与十四五规划教材: 【问题】 在Excel2019及以前的版本中,如果用VLOOKUP实现逆向查找、多条件查找、从下向上查找以及如果查找不到则返回特定值等功能,是比较麻烦的,而在Excel2021版本中新增的XLOOKUP函数可以轻而易举的实现这些功能。 【函数简介】 功能:按行查找表或区域中的项。 语法:=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])。 中文语法:=XLOOKUP(要搜索的值,要搜索的数组或区域, 要返回的数组或区域,未找到返回值返回的指定文本, 匹配类型,搜索模式)。
其中后两种搜索模式的执行,依赖于返回值所有数据或区域的排序方式。 【实现方法】 1)基本查找 在G3单元格输入公式“=XLOOKUP(F3,B3:B15,D3:D15)”,按Enter键,完成运算,即可即可查找到F3单元格内指定姓名得分,如图B-1所示。 2)逆向查找 在G3单元格输入公式“=XLOOKUP(F3,B3:B15,A3:A15)”,按Enter键,完成运算,即可即可查找到F3单元格内指定姓名所属部门,如图B-2所示。 查找值所有的“姓名”列,在信息表中,位于返回值“所属部门”的右侧,这种返回值位于查找值左侧的查找方式称为逆向查找。 3)查找错误 在G3单元格输入公式“=XLOOKUP(F3,B3:B15,C3:C15,"查无此人")”,按Enter键,完成运算,如图B-3所示。 在信息表“姓名”列中,没有查找值“徐五”,指定返回值为“查无此人”。 4)模糊查找 在E2单元格输入公式“=XLOOKUP("G"&"*",A2:A8,B2:B8,,2)”,按Enter键,完成运算,即可查找出开头为“G”型号系列的销量,如图B-4所示。 公式中的"G"&"*",表示以“G”开头的型号系列。公式第5个参数为“2”,即按通配符进行数据匹配。 5)区间查找 对成绩划分等级,划分等级的标准是:85分及以上为优秀、70到84分为良好,60到69分为合格,60分以下为不合格。 在C2单元格输入公式“=XLOOKUP(B2,{0,60,70,85},{"不合格","合格","良好","优秀"},,-1)”,按Enter键,完成运算,并将公式向下填充,即得所有成绩对应的等级,如图B-5所示。 公式中的第5个参数为“-1”,即按完全匹配,如果没有找到成绩对应的等级,则返回下一个较小成绩对应的等级。 6)从下向上查找 在E2单元格输入公式“=XLOOKUP(D2,$A$2:$A$18,$B$2:$B$18,,,-1)”,按Enter键,完成运算,并将公式向下填充,即得所有商品对应的最大进货数量,如图B-6所示。 公式中的第6个参数为“-1”,从最后一项开始执行自下而上搜索。 特别注意:此时的数据表中的“进货数量“一定是按照自小而的升序排列的。 7)多条件查找 在G2单元格输入公式“=XLOOKUP(E2&F2,A2:A13&B2:B13,C2:C13)”,按Enter键,完成运算,即得指定仓库指定商品的进货数量,如图B-7所示。 8)多行多列查找 在C18单元格输入公式“=XLOOKUP(B18,$C$3:$C$15,$D$3:$G$15)”,按Enter键,完成运算,即得指定姓名的各项信息,如图B-8所示。 |
|
来自: bitterfleabane > 《待分类》