excel自动筛选_自动更改Excel筛选器标题

excel自动筛选

There was a question about Excel Advanced Filter criteria on the Tech Republic blog recently, and I posted an answer. A couple of weeks later, a Tech Republic mug and flag were delivered to my door, as a reward for answering.

最近在Tech Republic博客上有一个关于Excel Advanced Filter标准的问题，我发布了答案。 几周后，我收到了一个Tech Republic马克杯和一面旗帜，作为对我回答的奖励。

The real joy is in solving a problem, but it's fun to get a major award, even if it's not a fancy leg lamp that I can put in the front window.

真正的乐趣在于解决问题，但是即使不是我可以放在前窗上的花哨的台灯，获得大奖也很有趣。

Keep reading, to see what problem the blogger was having with Excel Advanced Filters, and download a workbook with my suggested solution.

继续阅读，以了解博客作者使用Excel Advanced Filters遇到的问题，并下载带有我建议的解决方案的工作簿。

设置高级过滤器 (Set Up an Advanced Filter)

To use an Excel Advanced Filter, you create a criteria range, with headings that match the ones used in the original table. Then, under one or more of the headings, you enter the filter criteria.

要使用Excel高级筛选器 ，请创建一个条件范围，其标题与原始表中使用的标题相匹配。 然后，在一个或多个标题下，输入过滤条件。

For example, in the screenshot below, the criteria would extract all the records where the quantity ordered is 20 and the product is juice. With an Advanced Filter, you can even extract the data to a different location, all in one step.

例如，在下面的屏幕截图中，条件将提取所有记录，其中订购的数量为20，产品为果汁。 使用高级过滤器，您甚至可以一步将数据提取到另一个位置。

相同的标题 (Identical Headings)

In most cases, when you set up an Advanced Filter criteria range, each heading must be identical to a heading in the source data table.

在大多数情况下，设置高级过滤器条件范围时，每个标题都必须与源数据表中的标题相同。

An easy way to make them identical is to link from the criteria headings to the table headings. In the screenshot below, cell F1 is linked to cell B1.

使它们相同的一种简单方法是将条件标题链接到表标题。 在下面的屏幕截图中，单元格F1链接到单元格B1。

=B1

= B1

不同的标题 (Different Headings)

However, there's one situation in which the criteria range headings must NOT match the table headings -- if you use a formula in the criteria row. In the example below, we'd like to extract the records where the number ordered is different than the number shipped.

但是，在一种情况下，条件范围标题必须与表标题不匹配-如果您在条件行中使用公式。 在下面的示例中，我们希望提取记录，其中订购的编号与发货的编号不同。

In the criteria range, there's a formula in cell G2, to compare the quantity ordered and quantity shipped.

在标准范围内，单元格G2中有一个公式，用于比较订购的数量和装运的数量。

=C2<>D2

= C2 <> D2

For this filter to work, the heading in cell G1 has to be removed, or changed to something different than any of the table headings.

为了使此过滤器起作用，必须删除单元格G1中的标题，或将其更改为与任何表标题不同的标题。

Another option would be to leave the link to the table heading, and add a space character or underscore.

另一种选择是保留指向表标题的链接，并添加空格字符或下划线。

=C1 & " "

= C1＆“”

创建可调整的标准标题 (Create Adjustable Criteria Headings)

This was the problem that the Tech Republic blogger encountered -- remembering to manually change the heading, or remove it, when using a formula in the criteria range. The question posed included this restriction:

这是Tech Republic博客作者遇到的问题-使用条件范围内的公式时，请记住手动更改或删除标题。 提出的问题包括以下限制：

Remember, you don't want to force users to remember that in this particular case... they have to do something special like delete header text! Working with the list and criteria ranges, already in place, how would you get the desired results?

记住，您不想强迫用户记住这种特殊情况 ……他们必须做一些特别的事情，例如删除标题文本！ 使用已经存在的列表和条件范围，您将如何获得期望的结果？

To make the heading adjust automatically, you can use an IF formula to test what's in the cell below.

要使标题自动调整，您可以使用IF公式来测试下面的单元格中的内容。

=C1 & IF(ISLOGICAL(G2), "_" , "" )

= C1＆IF(ISLOGICAL(G2)，“ _”，“”)

If cell G2 contains TRUE or FALSE, then it has a criteria formula, and an underscore is added to the heading.

如果单元格G2包含TRUE或FALSE，则它具有条件公式，并且在标题上添加了下划线。

下载高级过滤器工作簿 (Download the Advanced Filter Workbook)

To see the data and the criteria range heading formulas, you can download the Advanced Filter Criteria Headings sample file. It's in Excel 2003 format, and zipped.

要查看数据和条件范围标题公式，可以下载Advanced Filter Criteria Headings示例文件 。 它采用Excel 2003格式，并已压缩。

The file contains a macro, that lets you run the advanced filter by clicking the Filter button on the worksheet. Enable macros if you want to use that feature.

该文件包含一个宏，可让您通过单击工作表上的“筛选器”按钮来运行高级筛选器。 如果要使用该功能，请启用宏。

观看高级过滤器标准视频 (Watch the Advanced Filter Criteria Video)

To see the steps for applying an Advanced Filter, with regular criteria or a formula in the criteria range, please watch this short Excel video tutorial.

要查看使用常规条件或条件范围内的公式应用高级过滤器的步骤，请观看此简短的Excel视频教程。

演示地址

翻译自: https://contexturesblog.com/archives/2010/11/22/automatically-change-excel-filter-heading/

excel自动筛选