Office 365/Excel 2016+ 公式
本单元介绍了 Office 365/Excel 2016+ 中的 3 个新公式,它们是 XLOOKUP()、FILTER() 和 LET()。 要了解应用程序中的新特性和功能,还可加入 Microsoft Office 预览体验计划(本模块末尾参考资料部分的链接)。
XLOOKUP()
XLOOKUP() 是 VLOOKUP() 功能更强大的新版本。 它更简单、速度更快,而且更灵活。
XLOOKUP() 优于 VLOOKUP() 的原因如下:
搜索列和行结合了 VLOOKUP() 和 HLOOKUP(),实现更全面的搜索。
左侧的搜索列替换了 INDEX() MATCH() 模式,让你能够使用最适合搜索的组合。
该公式更加强大,原因是它在添加/删除列时不会“中断”。
XLOOKUP() 包含一个带有 3 个必需参数的语法。 该函数默认执行完全匹配。
XLOOKUP() 具有以下功能:
基于其他列中的值从给定列返回值
如果未找到任何结果,则返回其他值
从顶部或底部进行搜索
XLOOKUP() 有 6 个参数,其中最后 3 个是可选参数:
lookup_value:用于定义要查找的值的参数
lookup_array:用于指定在其中查找值的列的数组参数
return_array:用于定义要从中返回值的列的数组参数
if_not_found:如果未找到匹配项,则返回此可选值
match_mode:可选参数,用于指定完全匹配、先搜索上方/下方或通配符搜索
search_mode:可选参数,用于指定从顶部或底部进行搜索
在前面的数据集示例中,请注意显示返回结果的右侧黑框中的 XLOOKUP() 公式。 这三个示例回答了以下问题:
按 ID 查找产品:公式演示查找产品 ID = 109 的产品,其中产品结果位于“产品 ID”列右侧的列中。
按 ZIP 查找城市:示例公式演示了查找 ZIP = 21658 的城市,这些结果位于 ZIP 列左侧的列中。
按城市查找最后一个产品:此公式演示使用可选参数:“未找到结果”(如果没有找到结果,则返回它)、0(完全匹配)和 -1(表示从数据表的底部到顶部进行搜索)。
FILTER()
FILTER() 是新的数组函数。 将该公式添加到单个单元格会返回表的部分内容,其他值将溢出到结果中的其他单元格。 FILTER() 会返回多行数据,并使用 and/or 逻辑来允许多个条件。
FILTER() 具有以下功能:
返回一个或多个查找值的多个匹配结果
无需 [refresh]{.underline} 即可筛选数据
可嵌套在其他 Excel 函数内
以下详细信息说明了 FILTER() 中包含的 3 个参数:
array:用于指定要筛选的列和行范围的参数
include:用于提供筛选规则条件的参数
if_empty:如果没有符合条件的行,则返回可选参数值
上一个数据集示例显示了返回的结果,还在黑框中显示了 FILTER() 公式。 请注意,它使用的是表而不是范围。 建议尽可能使用表。 前面的示例对 SalesTable 表进行了筛选,其中“区域 = 西部”,并在结果中返回了所有匹配的行。
此示例还是使用上述数据集,但对表应用了 3 个筛选器。 该公式根据以下条件筛选表。 必须满足所有条件才能包含行。
产品 = Palma UM-01
区域 = 西部
收入 = 超过 1,215.00 美元
此公式使用乘法函数,因为逻辑比较将导致出现 0(表示 false)或 1(表示 true)。 如果所有条件均为 TRUE,则 1 * 1 * 1 = 1。 但如果任一条件为 0(表示 false),则整个逻辑为 false。
AND 条件使用星号 (*),OR 条件使用加号 (+)。
LET()
LET() 函数让你能够很灵活地进行复杂计算,并且更轻松地理解公式的不同部分。 它不仅能够存储使用变量的计算和值,还可利用 Excel 的本机公式语法。
变量用于向值或计算分配名称。 这些变量用于重新调用语法,无需你反复重写公式。 可在函数中定义多达 126 个不同的变量,但至少必须有 3 个组成部分(变量、变量值和计算)。 还可利用其他数组函数,例如 LET() 函数中的 FILTER()。 以下示例基于前面的 FILTER() 示例,但现在分配了变量。
在上一个屏幕截图中,数字 1 到 4 是变量和定义。 最后一个语句是使用变量的计算。
ProductRange = 产品列范围
产品 = 要筛选的产品
RegionRange = 区域列范围
区域 = 要筛选的区域
筛选器 = 针对产品和区域在表中进行筛选