excel用宏如何筛选日期_Excel筛选器的日期范围2种方式

excel用宏如何筛选日期

In Excel, you can use the drop down arrows in the table headings, to sort or filter the data. In this example, we'll filter for dates that fall within a specific date range. The video shows two ways to accomplish that task.

在Excel中，您可以使用表格标题中的下拉箭头来对数据进行排序或过滤。 在此示例中，我们将过滤掉特定日期范围内的日期。 视频显示了完成任务的两种方法。

使用日期过滤器 (Use a Date Filter)

The first way to filter for a date range, is to use one of the Date Filter options. There are dynamic date range options, such as This Week, or Last Year.

过滤日期范围的第一种方法是使用“日期过滤器”选项之一。 有动态的日期范围选项，例如“本周”或“去年”。

For this example, I'll use one of the static date range options – Between. Unfortunately, you can't click on a cell, to use its contents as the date, but there's a drop down list. You can select an item from that drop down, or type the dates in the boxes.

在此示例中，我将使用静态日期范围选项之一-之间 。 不幸的是，您不能单击单元格以将其内容用作日期，但是有一个下拉列表。 您可以从下拉菜单中选择一个项目，或在框中输入日期。

使用条件格式 (Use Conditional Formatting)

The second option that I used in the video was a Filter by Font Color. Thanks to UniMord for suggesting this technique!

我在视频中使用的第二个选项是“按字体颜色过滤”。 感谢UniMord提出这项技术！

First, I created a temporary worksheet formula, using the AND function. It compared the date in cell B4 to the start date, and to the end date.

首先，我使用AND函数创建了一个临时工作表公式。 它将单元格B4中的日期与开始日期和结束日期进行了比较。

=AND(B4>=$G$2,B4<=$H$2)

= AND(B4> = $ G $ 2，B4 <= $ H $ 2)

Once the worksheet formula was working correctly, I copied it, using Ctrl+C.

工作表公式正常工作后，我使用Ctrl + C复制了它。

添加条件格式 (Add Conditional Formatting)

Next, I selected the Date column in the table, and added conditional formatting to all the date cells.

接下来，我选择了表中的“日期”列，并向所有日期单元格添加了条件格式 。

For the Formula, I used Ctrl+V to paste in the worksheet formula that I had copied in the previous step.

对于公式，我使用Ctrl + V粘贴了在上一步中复制的工作表公式。

For the formatting, on the Font tab, I selected Black – Text 1 – 5% lighter.

对于格式设置，在“字体”选项卡上，我选择了“黑色–文本1 –浅5％”。

Excel will detect the difference, but on the worksheet, the dates should all look the same. That way, if you want to print the sheet, you won't need to worry about the font colour.

Excel将检测到差异，但在工作表上，日期应该都看起来相同。 这样，如果您要打印纸张，则无需担心字体颜色。

按颜色过滤 (Filter by Colour)

Now, with the conditional formatting applied, click the drop down arrow in the Date column heading.

现在，应用条件格式后，单击“日期”列标题中的下拉箭头。

Click on Filter by Color, then click the black color under Filter by Font Color.

单击“按颜色过滤”，然后单击“按字体颜色过滤”下的黑色。

The table is filtered to show only the rows in the selected date range.

表格被过滤以仅显示所选日期范围内的行。

And it's easy to change the date range. Just type new dates on the worksheet, then apply the Filter by Color again.

而且很容易更改日期范围。 只需在工作表上键入新日期，然后再次应用“按颜色过滤”即可。

NOTE: You can clear the cells where the worksheet formulas were entered. They were just used for testing.

注意：您可以清除输入工作表公式的单元格。 它们只是用于测试。

获取工作簿 (Get the Workbook)

To download the AutoFilter for Date Range workbook, go to the AutoFilter page on my Contextures website. The zipped file is in xlsx format, and does not contain macros.

要下载“日期范围自动筛选”工作簿，请转到Contextures网站上的“自动筛选”页面 。 压缩文件为xlsx格式，不包含宏。

And you can find more Conditional Formatting examples on my Contextures site too!

您也可以在我的Contextures网站上找到更多条件格式设置示例 ！

视频：过滤日期范围 (Video: Filter for Date Range)

Here's the video that shows the steps for two ways to filter for a date range in Excel. Use a Date filter, or use Filter by Color, after applying Conditional Formatting. The timeline is below the video

以下视频显示了在Excel中过滤日期范围的两种方法的步骤。 在应用条件格式后，请使用“日期”过滤器或“按颜色过滤”。 时间线在视频下方

演示地址

Video Timeline:

视频时间轴：

• 0:00 Intro
  0:00简介
• 0:17 Use the Date Filter
  0:17使用日期过滤器
• 2:01 Another Way to Filter
  2:01另一种过滤方式
• 2:42 Create a Formula
  2:42创建公式
• 4:03 Add Conditional Formatting
  4:03添加条件格式
• 6:21 Get the Workbook
  6:21获取工作簿

翻译自: https://contexturesblog.com/archives/2019/09/05/excel-filter-for-date-range-2-ways/

excel用宏如何筛选日期