1. 软件Microsoft OfficeExcel如何在Excel 2016中使用XLOOKUP函数

格雷格·哈维(Greg Harvey)

Windows和Mac上适用于Office 365订阅者的Excel 2016现在支持新的XLOOKUP功能,被吹捧为非常流行(但还没有实现)的垂直查找功能VLOOKUP(不知道XLOOKUP中的X是什么)的更简单,更通用的替代品代表;也许广泛吗?)。

对于尚未熟悉VLOOKUP(在SUM和AVERAGE之后排在第三位的最常用函数)的用户,此函数从上到下在指定查找表的最左列中逐行垂直搜索,直到在VLOOKUP中找到一个值为止。由与您查找的偏移量匹配或超过的偏移量编号指定的查找列。尽管在查找工作表中数据表的长列表或数据表的列中的特定项目时非常有用,但VLOOKUP函数具有一些限制,这些限制是XLOOKUP不能与这个新的查找函数共享的:

  • 默认为在查找范围内查找与您的查找值完全匹配的对象 可以在表中垂直(按行)和水平(按列)搜索,从而消除了按列水平搜索时使用HLOOKUP函数的需要 可以向左或向右搜索,以便查找表中的查找范围不必位于指定为返回范围的查找范围左侧的一列中,以便该功能起作用 使用完全匹配默认值时,即使未按特定顺序对查找范围内的值进行排序,也可以使用 可以使用可选的搜索模式参数从查找数组范围的底部到顶部进行搜索

XLOOKUP函数有五个可能的参数,使用以下语法,其中前三个是必需参数,后两个是可选参数:

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

必需的lookup_value参数指定您要搜索的值或项目。必需的look_up array参数指定要搜索此查找值的单元格范围,而return_array参数指定包含要在Excel找到精确匹配时返回的值的单元格范围。

*在XLOOKUP函数中指定lookup_array和return_array参数时,请记住,两个范围的长度必须相等,否则Excel将返回#VALUE!错误的公式。这更是您在定义这些参数时使用指定数据表的范围名称或列名称的原因,而不是指出它们或键入其单元格引用。

可选的match_mode参数可以包含以下四个值之一:

  • 0表示完全匹配(默认值,与未指定match_mode参数时相同) -1表示完全匹配或下一个较小的值 1表示完全匹配或下一个更大的值 2部分匹配,使用在lookup_value参数中加入到单元格引用的通配符

可选的search_mode参数可以包含以下四个值之一:

  • 1表示从头到尾,即从上到下搜索(默认值,与未指定search_mode参数时相同) -1搜索从后到先,即从下到上 2以升序进行二进制搜索 -2以降序搜索二进制

理解新XLOOKUP函数的功能和多功能性的最佳方法是在Excel工作表中对其进行实际操作。在下图中,我有一个工作表,其中包含按国家/地区排列的简单的2019年销售数据表。若要使用XLOOKUP根据您在工作表的单元格D4中输入的国家,从单元格E4中的此表中返回总销售额,请执行以下步骤:

  1. 将单元格光标放置在工作表的单元格E4中 单击“公式”选项卡上的“查找和引用”选项,然后单击下拉菜单底部附近的XLOOKUP,以打开其“函数参数”对话框。 单击工作表中的单元格D4,以将其单元格引用输入到Lookup_value参数文本框中。 按Tab键选择Lookup_array参数文本框,然后单击单元格A4并在按住Shift的同时按Ctrl-向下箭头以选择A4:A8作为要搜索的范围(因为范围A3:B8被定义为Excel数据表, Table1 [Country]出现在文本框中,而不是范围A4:A8)。 按Tab键选择Return_array参数文本框,然后单击单元格B4并在按住Shift的同时按Ctrl-向下箭头以选择B4:B8作为包含要基于搜索结果返回的值的范围(显示为表1 [总销售]在文本框中。

单击“确定”以在单元格E4中输入XLOOKUP公式。

XLOOKUP公式创建

Excel将XLOOKUP公式输入到工作表的单元格E4中,并返回4900,因为哥斯达黎加当前已输入查找单元格D4,正如您在2019年销售表中所看到的,这确实是该国家/地区的总销售额。

由于XLOOKUP的功能从右到左以及从左到右,因此您也可以使用此功能根据特定的销售数据从该销售表中返回国家/地区。下图显示了如何执行此操作。这次,您在单元格D4中创建XLOOKUP公式,并将在单元格E4中输入的值(在本例中为11,000)指定为lookup_value参数。

此外,您输入-1作为match_mode参数,以覆盖该函数的完全匹配默认值,以便Excel返回与在查找单元格E4中输入的销售值完全匹配的国家或与总销售额较低的国家(墨西哥与在这种情况下为$ 10,000,因为此表中没有任何国家/地区的销售总额为$ 11,000。如果不为此公式指定match_mode参数,Excel将返回#NA作为结果,因为此销售表中没有与$ 11,000完全匹配的内容。

D4中的XLOOKUP公式

因为XLOOKUP函数和按行垂直搜索一样适合按列水平搜索,所以您可以使用它来创建公式并执行双向查找(而无需创建将INDEX和MATCH函数结合在一起的公式以往)。下图包含从2019年4月到12月的零件编号从AB-100到AB-103的生产计划表,向您展示了如何完成此工作。

嵌套的XLOOKUP函数

在单元格B12中,我创建了以下公式:

= XLOOKUP(part_lookup,$ A $ 3:$ A $ 6,XLOOKUP(date_lookup,$ B $ 2:$ J $ 2,$ B $ 3:$ J $ 6))

该公式首先定义一个XLOOKUP函数,该函数按行垂直搜索与生产表的单元格区域$ A $ 3:$ A $ 6中名为part_lookup的单元格(在本例中为B10单元格)中所做的零件条目的精确匹配。 。但是请注意,此原始LOOKUP函数的return_array参数本身是第二个XLOOKUP函数。

第二个嵌套的XLOOKUP函数按列水平搜索单元格区域$ B $ 2:$ J $ 2,以查找与名为date_lookup的单元格(在本例中为B11单元格)中的日期条目完全匹配的情况。此第二个嵌套XLOOKUP函数的return_array参数为$ B $ 3:$ J $ 6,即表中所有生产值的单元格范围。

该公式的工作方式是Excel首先通过执行水平搜索来计算第二个嵌套XLOOKUP函数的结果,在这种情况下,该返回将返回Jun-19列的单元格区域D3:D6中的数组(值: 438、153、306和779)作为结果。此结果又成为原始XLOOKUP函数的return_array参数,该函数按行进行垂直搜索,以与单元格B11中命名的零件号条目(称为part_lookup)完全匹配。因为在此示例中,此part_lookup单元格包含AB-102,所以该公式仅从第二个下一个XLOOKUP函数的结果返回Jun-19生产值306。

你有它!首先介绍XLOOKUP,它是一种功能强大,用途广泛且相当易于使用的新查询功能,它不仅可以执行VLOOKUP和HLOOKUP函数执行的单值查询,而且还可以通过组合两个值查询来执行双向查询。 INDEX和MATCH也起作用。

*不幸的是,XLOOKUP函数与仅支持VLOOKUP和HLOOKUP函数的Microsoft Excel的早期版本不向后兼容,或者与尚未将其作为其查找函数之一的当前版本兼容,例如Excel 2019和Excel Online。这意味着,如果与正在使用不包含此新查找功能的Excel版本的同事或客户共享包含XLOOKUP公式的工作簿,则所有这些公式都将返回#NAME?他们打开其工作表时的错误值。