【Power BI】Office 365/Excel 2016＋公式

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 = 区域列范围

区域 = 要筛选的区域

筛选器 = 针对产品和区域在表中进行筛选