长期浪迹于Excel相关的各大论坛网站,见识过形形色色的问题和回答。不得不说XLOOKUP确实是近两年来的绝对网红,分享几个网友的实际问题和解决方案,每一个都会体现出XLOOKUP的某个独特之处,值得反复咀嚼体会。 案例一,匹配下一行内容 读题:找到对应的日期,返回它下一行的金额。 公式:=XLOOKUP(D2,$A$2:$A$11,$B$3:$B$12) 解读:注意XLOOUP第二参数和第三参数的位置关系。 Excel笔记:XLOOKUP对第二参数和第三参数的位置没有限制,个数相同即可。 案例二,拆分内容 读题:需求是拆分单元格内容,并放到对应单元格。转换思路,在首行查询对应的内容,如有则放置对应内容。 公式:=XLOOKUP("*"&B$1&"*",$A2,B$1,"",2) 解读:以B1单元格为例,搭配通配符后查询,只要包含“心脏病”即查询成功,返回A2中的“心脏病”,否则为空。 Excel笔记:XLOOKUP搭配通配符实用时第五参数记得设置为2 案例三,返回查找地址 读题:返回2A的起始位置和结束位置。注意,A,B列连接起来才是”2A”,且需要返回单元格地址。 公式:="C"&XLOOKUP("2A",A1:A9&B1:B9,ROW(A1:A9),,,{1,-1}) 解读: A列连接B列作为第二参数,对应查找值“2A”; 第三参数ROW返回对应的行号; 第六参数1表示正向查找,从上往下查,找到起始位置;-1表示逆向查找,从下往上查,找到结束位置。数组{1,-1}集合在一个公式完成输出。 Excel笔记:XLOOKUP第六参数设置为-1可逆向查询。 案例四,按日期查询价格,找不到就取小于该日期的平均值 公式:=XLOOKUP(D2,A:A,B:B,AVERAGEIF($A$2:$A$11,"<"&D2,$B$2:$B$11)) 解读:在A列查找日期,如果找到返回B列对应的值,找不到则执行第四参数。 第四参数通过AVERAGEIF函数求取小于该日期的平均值。 =AVERAGEIF($A$2:$A$11,"<"&D2,$B$2:$B$11) Excel笔记:XLOOKUP找不到查找值时可以执行第四参数,如第四参数未做设置,则返回#N/A. |
|