全文约1600字
大家好,我是古老师。在生产计划(PMC)的日常工作中,“查找与引用”是一项非常频繁的操作。例如,我们经常需要通过生产任务号来查找相关的生产信息,依据采购订单号获取相应的详情,或者根据物料编码查询库存现存量等。可以说,VLOOKUP函数陪伴我从一名计划员成长为计划经理。
随着新函数如XLOOKUP以及动态数组功能的引入,传统的查找和引用方法在处理现代数据时可能显得有些不足,尤其是在需要一次性返回多列数据的情况下。那么,如何利用这些新工具实现更高效的批量数据返回呢?今天,我就来分享一下不同函数在批量返回数据时的效果和应用技巧。
批量返回首先介绍的是XLOOKUP函数,它能够用于单行单条件的查找,并且可以返回多列数据。由于XLOOKUP本身支持动态数组的功能,因此我们只需输入以下公式即可实现这一操作:
=XLOOKUP(A10,A2:A6,B2:E6)
公式解释:
查找值为A10单元格中的内容,
在A2到A6的区域中寻找匹配项,
一旦找到匹配项,则返回对应行在B2到E6区域中的所有列数据。
这样,只要满足查找条件,XLOOKUP就能一次性返回与之对应的多列信息
多查找条件上面的测试仅展示了基于单个条件返回多列数据的情况,例如查找A10中的值。如果需要根据两个或更多条件来同时返回多列数据,我们可能会尝试使用如下公式:
=XLOOKUP(A10:A11,A2:A6,B2:E6)
然而,这种写法的意图是通过A10和A11两个条件来查找,并返回对应行的多列数据。遗憾的是,XLOOKUP并不直接支持这样的多条件查找方式;上述公式实际上并不会按照预期工作,它只能处理单个查找值与返回区域的映射,因此只返回了单列的数据。
堆叠递归为了实现全动态一键查找多条件,并返回多列数据,我们可以使用REDUCE函数结合LAMBDA函数的组合。这种方法允许我们对多个查找条件进行迭代处理,并将结果堆叠起来。下面是具体的公式:
=DROP(REDUCE("",A10:A11,LAMBDA(X,Y,VSTACK(X,XLOOKUP(Y,A2:A6,B2:E6)))),1)
公式解释:
REDUCE("", A10:A11, ...): 使用REDUCE函数来累积处理A10到A11范围内的每个查找条件。初始值设置为空字符串""
LAMBDA(x, y, ...): 定义一个匿名函数(即LAMBDA),它接受两个参数:累积的结果x和当前处理的查找条件y。
VSTACK(x, XLOOKUP(y, A2:A6, B2:E6)): 对于每一个查找条件y,使用XLOOKUP在其对应的查找范围内(A2:A6)搜索,并从返回区域(B2:E6)获取相应的多列数据。然后,使用VSTACK将这些结果垂直堆叠到累积的结果x上。
DROP(..., 1): 最后,使用DROP函数移除最开始的空行(由REDUCE的初始值造成),从而得到最终的结果。
这个公式能够根据多个查找条件动态地查找并返回多列数据,非常适合用于需要灵活、动态的数据查询场景
传统函数上述使用REDUCE函数的组合对于许多人来说可能较为复杂。实际上,我们也可以采用经典的INDEX结合MATCH的方法来实现多条件查找并返回多列数据。由于我们需要返回的是固定列(如第1、第2列等),可以利用SEQUENCE函数生成一组列号,从而简化操作。下面是具体的动态数组公式:
=INDEX(B2:E6,MATCH(A10:A11,A2:A6,0),SEQUENCE(,4))
公式解释如下:
MATCH(A10:A11, A2:A6, 0): 在A2到A6的区域中查找A10和A11中的值,并返回它们各自匹配行的位置。这里0表示精确匹配。
SEQUENCE(, 4): 生成一个水平数组,包含从1到4的数字,用于指定要返回的列数。因为B2:E6有四列,所以这里设置为4。
INDEX(B2:E6, ..., ...): 使用INDEX函数根据MATCH找到的行位置以及SEQUENCE生成的列号,从B2:E6区域中提取相应的数据。
这个公式能够根据多个查找条件返回对应的多列数据,而且通过使用SEQUENCE,我们可以轻松调整需要返回的列数。这种方法不仅保持了经典INDEX与MATCH组合的简洁性,还借助现代Excel&QWPS的动态数组功能实现了更加灵活的数据处理。
最后总结综上所述,在生产计划(PMC)的日常工作中,“查找与引用”操作是不可或缺的一部分。从早期依赖VLOOKUP函数,到如今利用XLOOKUP、REDUCE结合LAMBDA以及INDEX与MATCH等现代Excel函数,我们见证了数据处理方式的巨大变革。这些新工具不仅提升了工作效率,还为解决复杂的多条件查找和批量返回问题提供了更优解。
此外,对于那些希望保持传统方法简洁性的同时享受现代Excel功能带来的便利的用户来说,采用INDEX与MATCH组合加上SEQUENCE函数是一种理想的选择。这种方式既保留了经典函数易于理解的优点,又充分利用了动态数组的优势,实现了固定列数的数据批量返回。这不仅简化了操作流程,还增强了公式的可读性和维护性。