excel筛选空白条件_Excel筛选器的空白：鲨鱼周2011-CSDN博客

excel筛选空白条件

Last year, we celebrated the Discovery Channel's Shark Week, by using the LARGE and FLOOR functions.

去年，我们通过使用LARGE和FLOOR功能庆祝了发现频道的鲨鱼周。

This year, we'll pay tribute to the three known species of sharks that are filter feeders, by declaring this Excel Advanced Filter Week.

今年，我们将宣布本“ Excel高级过滤周”，向这三种已知的鲨鱼(过滤器)致敬。

Yes, we'll have three fun-filled, action-packed days of Excel filtering fabulousness – one day for each filter feeding shark. Please hold your applause until all three articles have been posted.

是的，我们将有3个充满乐趣，充满动作的Excel过滤神话般的日子-每个过滤器喂食鲨鱼一天。在发布所有三篇文章之前，请保持掌声。

高级过滤条件范围 (Advanced Filter Criteria Range)

We'll kick off the week's celebrations by filtering rows with missing data (blank cells) to a different worksheet. When you're using an Advanced Filter, usually you would enter a heading, and one or more criteria, in a criteria range, like the one shown below.

我们将通过将缺少数据的行(空白单元格)过滤到其他工作表来开始本周的庆祝活动。使用高级过滤器时，通常会在一个条件范围内输入标题和一个或多个条件，如下所示。

In this example, you would be filtering the customer order list for any orders with Cookies as the product.

在此示例中，您将过滤任何以Cookies为产品的订单的客户订单列表。

However, if you want to filter orders with a blank cell for Product, you can't just leave the criteria range blank. A blank criteria cell is interpreted as "No criteria", so all the records would pass through the filter. That might be fine for a shark, but not for an Excel report.

但是，如果您要使用产品的空白单元格过滤订单，则不能仅将条件范围保留为空白。空白条件单元格被解释为“无条件”，因此所有记录都将通过过滤器。这对鲨鱼来说可能很好，但对Excel报表则不行。

在高级过滤器中过滤空白 (Filter for Blanks in Advanced Filter)

Instead of leaving the criteria cell blank, you can use a formula, to check for empty cells. In this example, the first product data is in cell C2, so the formula is:

您可以使用公式来检查空白单元格，而不是将条件单元格保留为空白。在此示例中，第一个产品数据在单元格C2中，因此公式为：

=C2=""

= C2 =“”

The two double quote marks represent an empty string, so if C2 is not blank, the formula result is FALSE. Only the records that calculate to TRUE would pass through the filter.

两个双引号表示一个空字符串，因此，如果C2不为空，则公式结果为FALSE。只有计算为TRUE的记录才会通过过滤器。

删除条件范围标题 (Remove the Criteria Range Heading)

If you're using a formula in an Advanced Filter criteria range, the heading can't match any of the source data headings.

如果您在“高级过滤器”条件范围内使用公式，则标题不能与任何源数据标题匹配。

You can either clear the heading cell in the criteria range, or type a different heading.

您可以清除条件范围内的标题单元格，或键入其他标题。

I usually clear the heading cell, because that's quick and easy!

我通常会清除标题单元格，因为这很容易！

运行高级过滤器 (Run the Advanced Filter)

After you set up the criteria range, you can run the Advanced Filter. Remember, if you want the results on a different worksheet, select that destination sheet before you run the filter.

设置条件范围后，您可以运行高级过滤器。请记住，如果要将结果放在其他工作表上，请在运行过滤器之前选择该目标表。

In this example, the filter is started from the Blank Orders sheet, and the list and criteria range are on the Orders sheet.

在此示例中，过滤器从“空白订单”表启动，并且列表和条件范围在“订单”表上。

下载高级过滤器空白工作手册 (Download the Advanced Filter Blanks Workbook)

To see the sample data, and test the filter, you can download the Advanced Filter for Blanks sample workbook. The file is in Excel 2007 format, and is zipped.

要查看样本数据并测试过滤器，您可以下载“ 空白的高级过滤器”样本工作簿。该文件为Excel 2007格式，并且已压缩。