如何在 Excel 中使用新的 XLOOKUP() 动态数组函数

关于 Excel 中的 XLOOKUP()

XLOOKUP() 是几个新的动态数组函数之一。如果您曾经使用 Ctrl + Shift + Enter 输入过表达式,那么您已经熟悉 Excel 过去如何处理动态数组。由于新的动态数组功能,这些类型的表达式更容易创建和维护,因为您可以像往常一样输入表达式 – 只需简单的 Enter。结果溢出到下面的单元格中,尽可能多地填充以完成表达式的计算。这就是所谓的溢出范围。如果您看到溢出错误,则表示实现该功能所需的范围不可用。这意味着您可以使用一个函数返回结果值的多列(或行)。

XLOOKUP() 按行返回表或区域中的数据。您可能想要返回产品的价格或客户的电话号码。使用 XLOOKUP(),您可以根据相应单元格中的搜索词快速检索信息。   

以下是 XLOOKUP 的一些升级:

  • XLOOKUP() 支持垂直和水平查找。
  • XLOOKUP() 向左和向右搜索,因此不再重新排列列。
  • XLOOKUP() 支持相对引用,因此您可以插入和删除列(或行),函数将相应更新。
  • XLOOKUP() 默认为精确匹配,这是首选的默认值;较旧的查找函数默认为最近的匹配项。
  • XLOOKUP() 的新匹配模式允许更灵活的搜索。

现在让我们来看看这个函数的语法:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

前三个参数是必需的:

  • lookup_value:搜索词。
  • lookup_array:搜索范围(或源数据)。
  • return_array:返回范围(或结果)。
  • if_not_found:未找到有效匹配时返回的文本。如果省略,函数返回#N/A。
  • match_mode:指定匹配类型。有关适当的值和解释,请参见表 A。
  • search_mode:指定搜索模式。有关适当的值和解释,请参见表 B。

表A

价值 解释
0 找到完全匹配;如果没有找到,则返回 #N/A。这是参数的默认值。
-1 找到完全匹配;如果未找到匹配项,则返回下一个较小的项目。
1 找到完全匹配;如果未找到匹配项,则返回下一个较大的项目。
2 允许通配符匹配:*、? 和 ~。

表B

价值 解释
1 从第一项开始搜索。这是此参数的默认值。
-1 从最后一项开始搜索。
2 搜索要求 lookup_array 按升序排序。如果不是,该函数返回无效结果,而不是错误。
-2 搜索要求 lookup_array 按降序排序。如果不是,该函数返回无效结果,而不是错误。

这是很多信息,但其中大部分类似于旧的查找功能。现在,让我们继续看几个例子。

XLOOKUP() 和 VLOOKUP() 的快速比较

XLOOKUP() 可用于返回单个值,类似于 VLOOKUP(),但它使用不同的参数。让我们使用图 A 中的数据集比较这两个函数。具体来说,我们将使用人员值作为搜索词 (K1) 返回员工 ID 和日期。

图一

excelxlookup-a

  我们将使用查找函数根据搜索词返回值。

首先,让我们回顾一下 ID 函数:

K3: =VLOOKUP($K$1,Table1[[人员]:[ID]],2)

K4: =XLOOKUP($K$1,Table1[Personnel],Table1[ID])

这两个函数都使用 K1、Luke 中的值作为搜索词。最重要的是,K3 中的 VLOOKUP() 函数返回了错误的值,而 K4 中的 XLOOKUP() 函数返回了正确的值。VLOOKUP() 需要一个排序的数据集,但 XLOOKUP() 不需要。XLOOKUP() 返回匹配的第一个值——默认设置。

L3 和 L4 中的函数尝试使用函数返回基于查找值 Luke 的日期

L3: =VLOOKUP($K$1,Table1[[人员]:[ID]],-1)

L4: =XLOOKUP($K$1,Table1[Personnel],Table1[Date])

您可能期望 L3 中的错误值,因为 VLOOKUP() 不支持在查找值左侧进行搜索;该函数根本不理解参数-1。XLOOKUP() 可以,并且您不是使用负值,而是引用实际列,并且该函数不介意数据集未排序。在这个简单的示例中,对数据进行排序无关紧要,但有时您必须处理数据集的顺序,因此这种新行为是一个很好的升级。

原创文章,作者:校长,如若转载,请注明出处:https://www.yundongfang.com/Yun77161.html

(0)
打赏 微信扫一扫不于多少! 微信扫一扫不于多少! 支付宝扫一扫礼轻情意重 支付宝扫一扫礼轻情意重
上一篇 2021年10月13日 下午11:44
下一篇 2021年10月25日 上午9:35