XLOOKUP函数的两大实用技巧,提升你的Excel技能

来源:本站整理 作者:网络笔记

最佳答案一、XLOOKUP一对多查找在数据处理中,常需要根据一个关键词找出所有符合条件的记录,这就是一对多查找。XLOOKUP在这类场景中展现出了独特的便捷性。需求场景根据产品名称,从数据中提取所有对应的库存记录。实用公式=LET(a,SCAN(0,A2:A6=D4,SUM)&D4,XLOOKUP(......

一、XLOOKUP一对多查找

在数据处理中,常需要根据一个关键词找出所有符合条件的记录,这就是一对多查找。XLOOKUP在这类场景中展现出了独特的便捷性。

  • 需求场景

根据产品名称,从数据中提取所有对应的库存记录。

  • 实用公式

=LET(a,SCAN(0,A2:A6=D4,SUM)&D4,XLOOKUP(UNIQUE(a),a,B2:B6))

  • 公式解析

该公式的核心是解决查找对象存在重复值的问题。首先,SCAN(0,A2:A6=D4,SUM)通过累加运算生成一组序号(例如{1;1;2;2;3});接着,将这组序号与查找对象(如“A”)组合,形成{“1A”;”1A”;”2A”;”2A”;”3A”}这样的唯一标识;再用UNIQUE函数去重,得到{“1A”;”2A”;”3A”}作为新的查找值;最后,XLOOKUP以B2:B6为返回区域,精准提取所有匹配的库存记录。

与传统VLOOKUP的一对多公式相比,XLOOKUP无需额外构造查找数据范围,仅通过生成新查找对象即可完成操作,步骤大幅简化。

二、XLOOKUP多行多列查找

当需要根据多个关键词查找多列数据时,多行多列查找就十分必要。虽然FILTER函数表现出色,但XLOOKUP结合“REDUCE+LAMBDA”组合,同样能高效实现这一需求。

  • 需求场景

根据多个序号,查找对应的“名称、盘点”等多列数据。

  • 初始尝试

曾尝试直接用数组形式书写查找对象和返回区域,公式为:

=XLOOKUP(F4:F6,A2:A6,CHOOSECOLS(A2:D6,XMATCH({“名称”,”盘点”},A1:D1)))

但结果仅返回名称列数据{“B”;”C”;”E”},未能实现目标。

  • 成功公式

借助“REDUCE+LAMBDA”优化后,公式如下:

=LET(a,{“名称”,”盘点

“},REDUCE(a,F4:F6,LAMBDA(X,Y,VSTACK(X,XLOOKUP(Y,A2:A6,CHOOSECOLS(B2:D6,XMATCH(a,B1:D1)))))))

  • 公式解析

公式的关键在于XLOOKUP(Y,A2:A6,CHOOSECOLS(B2:D6,XMATCH(a,B1:D1)))部分:CHOOSECOLS+XMATCH组合用于定位需要返回的列(如“名称”“盘点”),Y则是由REDUCE函数从F4:F6中依次传递的单个序号。

整个过程相当于将多值查找拆分为多次单值查找:每次查找出一行结果后,用VSTACK函数与上一步结果纵向拼接;当REDUCE传递完所有序号后,即得到多行多列的完整结果。

  • 与VLOOKUP对比

VLOOKUP结合VSTACK也能实现类似功能,公式为:

=LET(a,{“名称”,”盘点”},REDUCE(a,F4:F6,LAMBDA(X,Y,VSTACK

(X,VLOOKUP(Y,A2:D6,XMATCH(a,A1:D1),0)))))

可见,两种函数实现多行多列查找,都依赖“REDUCE+LAMBDA”的强大组合。

知识链接:“REDUCE+LAMBDA”的妙用

“REDUCE+LAMBDA”是Excel中拓展函数应用的重要工具,能将数组逐步运算为单一结果。

示例公式

=REDUCE(0,B1:K1,LAMBDA(X,Y,Y^2+X))

公式解析

REDUCE的作用是通过LAMBDA函数对数组逐个运算,返回累计结果。其中,第一参数0为初始值,第二参数B1:K1是待处理数组;LAMBDA(X,Y,Y^2+X)中,X代表初始值或上一步结果,Y代表数组中的每个值,Y^2+X是运算表达式。

计算过程为:先以0为起点,计算第一个值的平方加0(如1²+0=1);再用结果1计算第二个值(如2²+1=5);以此类推,最终得到累计结果385。

网络笔记 学习文库

我这一生没什么理想,能治愈我的,从来都不是时间,而是内心的那份释怀和明白!

热门分类

热门工具

联系客服QQ:+