如何在Excel中使用高级过滤器 - 公式的标准范围示例

该教程显示了如何在Excel中使用高级过滤器，并提供了许多非平凡的标准范围示例来创建一个案例敏感的过滤器，查找两列之间的匹配和差异，匹配较小列表的提取记录等等。

在上一篇文章中，我们讨论了Excel Advanced Filter的不同方面，以及如何使用它和或逻辑过滤行。现在您知道了基础知识，让我们来看看更复杂的标准范围示例，可能对您的工作有帮助。

• 如何设置基于公式的标准范围
• 高级过滤标准范围示例
  • 案例敏感过滤器的文本值
  • 列高于或低于平均列的滤波器值
  • 用空白 /非蓝色单元格过滤行
  • 提取顶部 /底部N记录
  • 滤镜匹配和2列之间的差异
  • 匹配或不匹配列表中的项目的过滤行
  • 过滤周末和工作日

设置基于公式的标准范围

由于本教程中讨论的大多数标准范围示例将包括各种公式，因此让我们从定义正确设置它们的基本规则开始。相信我，这个小的理论将为您节省大量时间，并节省对您的复杂标准进行故障排除的头痛，其中包括基于公式的多个条件。

• 您在标准范围内使用的公式必须评估为True或False 。
• 标准范围应至少包含2个单元格：配方奶细胞和标头单元。
• 基于公式的标准的标题小区应与任何表（列表范围）标题的空白或不同。
• 对于要在列表范围内评估每一行的公式，请使用诸如a1之类的相对参考的数据参考最高单元格。
• 对于仅针对特定单元格或范围进行评估的公式，请参考该单元格或范围使用绝对参考（例如$ a $ 1）。
• 在公式中引用列表范围时，请务必使用绝对的单元格引用。
• 在提供多种条件时，请输入同一行上的所有标准，与AN和操作员一起加入它们，并将每个标准放在单独的行上，以与OR操作员一起加入它们。

Excel高级过滤标准范围示例

以下示例将教您如何在Excel中创建自己的过滤器，以处理无法使用常规Excel Autofter执行的更复杂的任务。

案例敏感过滤器的文本值

除了Excel Autofter外，Advanced Filter工具本质上是对大小写的不敏感的，这意味着它在过滤文本值时不会区分大写和小写字符。但是，您可以通过使用高级过滤器标准中的精确函数轻松地执行对案例敏感的搜索。

例如，要过滤包含香蕉的行，忽略香蕉和香蕉，请在标准范围内输入以下公式：

=EXACT(B5, "Banana")

其中b是包含项目名称的列，而第5行是第一个数据行。

然后，通过单击“数据”选项卡上的“高级”按钮应用Excel高级过滤器，然后配置列表范围和标准范围，如下面的屏幕截图所示。请注意，标准范围包括2个单元格 -媒体电池和配方奶单元。

笔记。上面的图像以及本教程中的所有进一步屏幕截图仅出于清晰度而显示标准范围单元中的公式。在您的真实工作表中，公式单元格应返回对还是错，具体取决于数据的第一行是否匹配标准：

列高于或低于平均列的滤波器值

在过滤数字值时，您通常只想仅显示列中平均值以上或低于平均值的单元格。例如：

要滤波以高于平均水平的行滤波行，请在标准范围内使用以下公式：

=F5>AVERAGE($F$5:$F$50)

要滤除以下平均水平的行，请使用以下公式：

=F5<average></average>

请注意，我们使用相对引用来指带有数据（F5）的顶级电池，并且绝对引用以定义您要计算平均值的整个范围，不包括列标题（$ f $ 5：$ f $ 50）。

以下屏幕截图显示了上述平均公式：

那些熟悉Excel号码过滤器的人可能会想知道，为什么有人会不愿意使用高级过滤器，而内置号码过滤器已经具有高于平均水平和低于平均水平的选项？是的，但是内置的Excel过滤器不能与逻辑一起使用！

因此，为了进一步以此示例，让我们过滤行，该行子（列F）或9月销售（E列）高于平均水平。为此，通过在单独的行上输入每个条件来设置使用或逻辑的标准范围。结果，您将获得E或F列中具有以上平均值的项目列表：

用空白或非空白的过滤行

众所周知，Excel Filter具有过滤空白单元的内置选项。通过在“自动滤波器”菜单中选择或取消选择（空白）复选框，您只能在一个或多个列中显示那些具有空单元或非空单元的行。问题在于，空白的内置Excel滤波器只能与逻辑一起使用。

如果您想用或逻辑过滤空白或非空白单元格，或将空白 /非空白条件与其他一些标准一起使用，请使用以下公式之一设置高级滤波器标准范围：

过滤器空白：

top_cell =“”

滤波器非空白：

top_cell “”

用或逻辑过滤空白单元格

要过滤A或B列或两列中具有空白单元格的行，请以这种方式配置高级滤波器标准范围：

• =A6=""
• =B6=""

其中6是最高数据。

用或井以及逻辑过滤非空白的单元格

为了进一步了解Excel的高级过滤器如何使用多个标准工作，让我们在样本表中使用以下条件过滤行：

• 区域（A列）或项目（B列）应非空白，并且
• 亚类（C列）应大于900。

换句话说，我们想显示符合以下条件的行：

（小计> 900和区域=非蓝兰）或（小计> 900 ， item =非蓝兰）

如您所知，在Excel高级滤波器标准范围内，与逻辑相连的条件应在同一行中输入，并且与OR逻辑相连的条件 - 在不同的行上：

因为此示例中的一个标准用一个公式（非空白）表示，另一个标准包括一个比较操作员（亚电> 900），请让我提醒您：

• 由比较操作员形成的标准应具有完全等于桌子标题的标准，例如上面屏幕截图中的次数标准。
• 基于公式的标准应具有空白的标题单元格或不匹配任何桌子标题的标题，例如上面屏幕截图中的非空白标准。

如何提取顶部/底部N记录

您可能知道，Build-In-In Excel号码过滤器可以选择显示前10个或底部10个项目。但是，如果您需要过滤前3个或底部5个值怎么办？在这种情况下，具有以下公式的Excel高级过滤器派上用场：

提取顶级n个项目：

top_cell > =大（范围，n）

提取底部N项目：

top_cell range ，n）

例如，要过滤前3个小计，请使用此公式创建标准范围：

=F5>=LARGE($F$5:$F$50,3)

要提取底部3个小计，请使用此公式：

=F5>=SMALL($F$5:$F$50,3)

其中F5是最高的单元格，该单元格中的数据列（不包括列标题）。

以下屏幕截图显示了行动中的前三名公式：

笔记。如果列表范围包含几行，其值与落入顶部/底部n列表中的值相同，则将显示所有这些行，如下面的屏幕截图所示：

过滤匹配和两列之间的差异

我们以前的文章之一解释了比较Excel中两列并找到匹配和差异的多种方法。除了Excel公式外，有条件的格式规则和上述教程中涵盖的重复卸载工具还可以使用Excel的高级过滤器来提取在两个或更多列中具有相同或不同值的行。为此，在标准范围内输入以下简单公式之一：

• 匹配的过滤器（重复）2列： =B5=C5
• 在2列中的差异（唯一值）的过滤器： =B5C5

其中B5和C5是您要比较的两列中具有数据的最佳单元格。

笔记。高级过滤器工具只能在同一行中搜索匹配和差异。要查找A列中的所有值，但B列中没有任何地方，请使用此公式。

根据列表中的匹配项过滤行

假设您有一张数百或数千行的大桌子，并且您收到了一个较短的列表，其中仅包含在给定时刻相关的项目。问题是 - 您如何找到表中或不在较小列表中的所有条目？

符合列表中项目的过滤行

要使用以下countif公式在较小列表中查找源表中也存在的所有项目：

countif（ list_to_match ， top_data_cell）

假设较小的列表在d2：d7的范围内，并且要将表的项目与该列表进行比较，则在第10行开始中，该公式如下（请注意，绝对和相对引用的使用）：

=COUNTIF($D$2:$D$7,B10)

当然，您不仅只有一个标准过滤桌子。

例如，要过滤与列表匹配的行，但仅对于北部地区，请在同一行中输入两个条件，以便它们可以使用逻辑：

• 区域： ="=North"
• 匹配项目： =COUNTIF($D$2:$D$7,B10)

正如您在下面的屏幕截图中看到的那样，表中只有两个记录符合这两个条件：

笔记。在此示例中，我们使用文本值的确切匹配标准： <em>="=North</em> "仅找到与指定文本完全等于的单元格。如果您仅像北一样进入区域标准（没有同等标志和双引号），Microsoft Excel将找到所有以指定文本（例如东北或西北）开头的项目。有关更多信息，请参阅Excel Advanced Filter有关文本值。

不匹配列表中项目的过滤行

要查找表中不在较小列表中的所有项目，请检查我们的Countif公式的结果是否等于零：

countif（ list_to_match ， top_data_cell） = 0

例如，要过滤列表中确实出现的北区项目，请使用以下标准：

• 区域： ="=North"
• 非匹配项： =COUNTIF($D$2:$D$7,B10)=0

笔记：

• 如果要匹配的列表位于不同的工作表中，请确保在公式中包含表名称，例如=COUNTIF(Sheet2!$A$2:$A$7,B10) 。
• 如果要将结果提取到其他表格，请从目标表启动高级过滤器，如如何将过滤的行提取到另一个工作表中所述。

周末和工作日的过滤器

到目前为止，我们的高级过滤条件范围示例主要与数字和文本值有关。现在，是时候给那些在约会的人那里提供一些线索了。

内置的Excel日期过滤器提供了多种涵盖许多方案的选项。很多，但不是全部！例如，如果您获得了日期清单并要求过滤工作日和周末，那么您将如何处理？

如您所知，Microsoft Excel提供了一个特殊的工作日功能，该功能返回一周中与给定日期相对应的日期。正是我们将在Excel高级滤波器标准范围内使用的功能。

如何在Excel中过滤周末

请记住，在工作日的时间里，有1个代表周日和6个代表星期六，周末过滤的公式如下：

或（工作日（日期）= 7，工作日（日期）= 1）

在此示例中，我们正在以第5行开始过滤B列中的日期，因此我们的周末公式采用以下形状：

=OR(WEEKDAY(B5)=7, WEEKDAY(B5)=1)

如何在Excel中过滤工作日

要过滤工作日，请修改上述公式，以便将1（星期日）和7（星期六）遗漏：

和（工作日（日期） 7，工作日（日期） 1）

对于我们的示例表，以下公式将有效：

=AND(WEEKDAY(B5)7, WEEKDAY(B5)1)

此外，您可以添加更多条件来过滤空白单元格： =B5""

要以其他方式过滤工作表中的日期，只需找到相关的日期函数，然后随时在高级过滤条件范围内使用它。

好吧，这就是您在Excel中使用高级滤波器使用复杂标准的方式。当然，您的选择不仅限于本教程中讨论的示例，我们的目标只是为您提供一些鼓舞人心的想法，这些想法将使您走上正确的轨道。记住，练习的精通之路是用练习铺成的，您可能需要使用以下链接下载我们的示例，并扩展或反向工程师，以更好地理解。我感谢您阅读，并希望下周在我们的博客上见到您！

练习工作簿

Excel高级过滤器示例（.xlsx文件）

以上是如何在Excel中使用高级过滤器 - 公式的标准范围示例的详细内容。更多信息请关注PHP中文网其他相关文章！