Excel Filter 函数

过滤器功能是 Excel 中常用的功能之一。用户通常使用自动筛选器来筛选数据，而在更复杂的数据问题中使用高级筛选器。尽管这些方法快速而强大，但它们不是动态的。简单来说，当原始数据发生任何变化时，这些方法不会自动更新。因此，您需要删除筛选器，更改数据，然后再次应用筛选器。这样会非常耗时。

为了防止这种缺点，Excel 推出了期待已久的替代方案，即 Excel 365 中的 FILTER 函数。与以前的方法不同，Excel FILTER() 函数会在每次工作表更改时自动重新计算数据，从而简化您的工作，因为现在您只需设置一次筛选器！

什么是 FILTER 函数？

Excel FILTER 函数用于根据给定的条件筛选范围或数组值。此函数返回一个输出，其中包含原始范围的值数组，该数组会自动溢出到 Excel 单元格范围，从您输入 FILTER 函数的 Excel 单元格开始。

FILTER 函数生成动态输出。当原始数据中的值发生变化或原始数据数组的大小发生变化时，FILTER 函数会自动更新输出。此外，此函数的结果将“溢出”到 Excel 工作表中的多个单元格。Filter 函数属于动态数组函数组。

FILTER 函数的主要作用是通过提供一个或多个逻辑测试（或条件）来从给定的数据集中提取匹配的值。逻辑测试使用“include”参数应用，它可以包含各种类型的公式条件。例如，FILTER 可以匹配特定员工表中的数据，并提取所有销售额低于 350000 印度卢比（或任何其他指定阈值）的员工姓名。

注意：FILTER() 函数仅适用于最新的 Excel 版本，即 Office 365 订阅和 Excel 2021。因此，与 Excel 2019、Excel 2016 和所有其他早期版本不同，此函数不支持早期版本。

语法

参数

• Array（必需） - 此参数表示您希望在 Excel 工作表中筛选的单元格范围或值数组。
• Include（必需） - 此参数表示您希望应用于数组的条件。它可以作为布尔数组（TRUE 和 FALSE 值）提供。
  例如，(B2:B6)>2 是一个布尔条件，我们在此指定 FILTER 函数仅提取范围 B2:B6 中值大于 2 的数据。
• If_empty（可选） - 此参数表示当不满足任何条件时返回的值。通常包含自定义用户消息，例如“未找到值”，但您也可以提供其他值。如果您不传递任何值给此参数，它将默认取空字符串 ("") 并返回空值。

Excel FILTER 函数要点

以下是一些重要要点，将帮助您在 Excel 工作表中有效应用 FILTER 函数

• FILTER 函数会分析条件，并根据条件自动将输出水平或垂直溢出，具体取决于您的源数据在 Excel 工作表中的排列方式。因此，请务必在单元格下方和右侧留出足够的空单元格；否则，Excel 将会报错 #SPILL。
• FILTER 函数生成动态输出，这意味着当原始数据中的值发生变化，或者原始数据数组的大小发生变化时，FILTER 函数将自动更新输出。但是，当向原始数据添加新值时，为“array”参数提供的范围不会更新。但是，如果您想自动调整数组参数的大小，则必须将其转换为 Excel 表格或创建动态命名范围。

示例 1：从下面的 Excel 表中筛选出学生姓名，其中学生分数大于 50。

请按照以下步骤解决上述问题

步骤 1：选择一个单元格

选择您要放置筛选数据的一个单元格。务必在所选单元格的右侧和下方留出足够的空单元格，因为根据您的原始数据，它可能会产生多个值。

如您在下图所示，我们在此选择了 E5 单元格。

步骤 2：输入公式，即 =Filter(

在选择空白单元格（E5）后，只需键入公式：= Filter(

它将看起来与下图相似

步骤 3：输入要排序的单元格范围或数组

下一步是键入（或选择）您要筛选的单元格或数组的范围。虽然您也可以用鼠标指针指向并拖动来选择原始单元格组，而不是键入。

在此示例中，原始单元格位于 B4 到 9。因此，我们的公式变为：=FILTER (B4: C9。它将看起来与下图相似

步骤 4：输入条件

我们将进入下一个参数（条件），所以我们先输入一个逗号（,）。在这里，我们需要筛选出分数大于 50 的学生姓名。所以我们的条件是 Score（单元格范围）> 50。

在此示例中，分数单元格位于 C4 到 C9。因此，我们的公式变为：=FILTER (B4: C9, C4:C9 > 50。它将看起来与下图相似

步骤 4：填写第三个参数的值（尽管此步骤是可选的）

在此参数中，我们将输入自定义用户消息，例如“抱歉！找不到记录”，但您也可以提供其他值。如果您不传递任何值给此参数，它将默认取空字符串 ("") 并返回空值。

因此，我们的公式变为：=FILTER (B4: C9, C4:C9 > 50, "抱歉！找不到记录")。它将看起来与下图相似

步骤 5：按 Enter 键，Excel 将给出筛选后的结果

按 Enter 键后，您将看到筛选后的记录。

在我们的例子中，有三个字段的分数大于 50。并且我们得到了相同的三个字段。

示例 2：使用两个条件（如下所述）在 Excel 中筛选多个列。

这是我们基本 Filter() 函数的扩展。在此示例中，我们将学习如何对两个不同的列应用两个条件来筛选数据。

请按照以下步骤解决上述问题

步骤 1：选择一个单元格

选择您要放置筛选数据的一个单元格。如您在下图所示，我们在此选择了 F7 单元格。

步骤 2：输入公式并输入单元格范围

下一步是键入 Filter 函数并选择您要筛选的单元格范围。在我们的例子中，原始单元格位于 B3 到 D12。因此，我们的公式变为：=FILTER (B3: D12。

它将看起来与下图相似

步骤 3：应用第一个条件

首先，我们将设置条件来指定组。为此，我们在 G4 单元格中输入了目标组名（criteria1）。因此，我们将检查范围 C3:C12 中的任何值是否等于 G4。公式如下

=FILTER(B3:D12, (C3:C12=G4),

步骤 4：应用第二个条件并使用 AND (*) 运算符组合它们

在第二个条件中，我们将指定 G5 单元格中的最小获胜次数（criteria2）。再次，我们将检查 D3:D12 之间的值是否等于 G5。然后使用 AND 运算符组合条件 1 和 2，我们将使用 AND 的逻辑表达式，即 '*'。公式如下

=FILTER(B3:D12, (C3:C12=G4)*(D3:D12= G5), "无结果")

注意：对于“AND”或“*”逻辑运算符，只有当两个条件都必须为 TRUE 时，才会提取数据条目。如果任何条目的条件为 FALSE，它们将被筛选掉。

步骤 5：按 Enter 键获取输出

Excel 将为您筛选条目，并获取来自 A 组且得分超过 2 次的候选人列表。

它将看起来与下图相似

示例 3：Excel FILTER 函数与多个条件（使用 AND & OR 逻辑）

您还可以实现 Filter 函数来获取具有多个条件的数据。通常，当我们想在公式中应用多个条件时，我们会使用 OR 和 AND 条件。与上一个示例不同，我们使用了 AND 的逻辑表达式（*），但在许多情况下，您还需要添加（使用 + 逻辑表达式）。

对于“AND”或“*”逻辑运算符，只有当两个条件都必须为 TRUE 时，才会提取数据条目。如果任何条目的条件为 FALSE，它们将被筛选掉。

对于“OR”或“+”逻辑运算符，如果两个条件都不满足且显示为 FALSE，则结果数组将返回 0。如果指定的条件之一为 TRUE，它将提取该条目的数据。

请按照以下步骤解决上述问题

步骤 1：选择一个单元格

选择您要放置筛选数据的一个单元格。如您在下图所示，我们在此选择了 F8 单元格。

步骤 2：输入公式并输入单元格范围

接下来，我们将键入 Filter 函数并输入范围。在我们的例子中，原始单元格位于 B3 到 D12。因此，我们的公式变为：=FILTER (B3: D12。

它将看起来与下图相似

步骤 3：应用第一个条件

首先，我们将设置条件来指定组。为此，我们在 G4 单元格中输入了目标组名（criteria1）。因此，我们将检查范围 C3: C12 中的任何值是否等于 G4。公式如下

=FILTER(B3:D12,((C3:C12=G4)

步骤 4：应用第二个条件，并使用 '+'（OR）运算符组合第一个和第二个条件。

接下来，我们将指定 C 组的条件。我们将检查原始范围中 C3:C12 之间的任何值是否等于 G5。

这次公式不会在此结束，因为我们将使用 '+'（OR）运算符组合 criteria1 和 criteria2。只要有一个条件为 TRUE，它就会返回该条目。

公式将如下

=FILTER(B3:D12,((C3:C12=G4)+(C3:C12=G5)

步骤 4： 应用第三个条件，并使用 '*'（AND）运算符将它们与其余条件组合。

在第三个条件中，我们将指定 G6 单元格中的最小获胜次数（criteria3）。再次，我们将检查 D3:D12 之间的值是否等于 2。然后使用 AND 运算符将条件 3 与其他两个条件组合，我们将使用 AND 的逻辑表达式，即 '*'。公式如下

=FILTER(B3:D12,((C3:C12=G4)+(C3:C12=G5)) *(D3:D12=2),"无结果")

步骤 5：按 Enter 键获取输出

Excel 将根据指定的条件筛选条目，并为您获取输出。

它将看起来与下图相似

Excel 中 FILTER 函数不起作用

很多时候，在使用 Excel FILTER 公式时，您可能会遇到以下错误之一。让我们看看错误的原因以及如何解决它们

#CALC! 错误

如果您省略了可选的 `if_empty` 参数，并且函数因不满足条件而无法获取任何输出，则会出现此错误。此错误的主要原因是 Excel 工作表不支持空数组。

要修复 #CALC! 错误，请确保在定义 Excel FILTER 函数时，始终在可选的 `if_empty` 参数中指定一个值。

#VALUE 错误

如果您在 array 和 include 参数中输入了不兼容的尺寸，您可能会遇到此错误。

为防止此类错误，请务必在 FILTER 参数中输入有效值。

#N/A、#VALUE 等。

在使用 FILTER 函数时，您可能会遇到这些不同类型的错误。这些错误可能发生的主要原因是您在 `include` 参数中输入了无法转换为布尔值（TRUE 或 FALSE）的无效值。

请始终记住，在 include 参数中包含一个可以转换为布尔值的条件。

#NAME 错误

当您在不支持 FILTER 函数的旧版本中使用 FILTER 函数时，会出现 #NAME 错误。请务必仅在最新的 Excel 版本，即 Office 365 和 Excel 2021 中使用此函数。

如果您不小心拼错了函数名，您也可能在新的 Excel 版本中遇到此错误。

#SPILL 错误

如果溢出区域的一个或多个单元格中有值且不为空，则可能发生此错误。

为防止此错误，您只需要清除或删除溢出区域中的数据。

#REF! 错误

当您在不同工作簿之间使用 FILTER 函数，并且主工作簿已关闭时，会出现 #REF! 错误。

恭喜！现在您已经简要了解了 FILTER 函数。继续使用 Excel FILTER 函数筛选您的数据。