数据透视表日期怎么选范围_筛选数据透视表中的日期范围

数据透视表日期怎么选范围

In a pivot table, you might want to see all the orders that were shipped on a specific date. To do that, you'd move the Ship Date field to the Page area, and select a date from the drop down list.

在数据透视表中，您可能希望查看特定日期发货的所有订单。 为此，您需要将“发货日期”字段移至“页面”区域，然后从下拉列表中选择一个日期。

Sometimes though, you'd like to show the orders shipped in a date range, instead of just a single date. For example, you might like to show orders with ship dates in the upcoming week, so you can do some planning.

不过有时，您想显示的是在日期范围内而不是单个日期内发送的订单。 例如，您可能希望在下周显示带有发货日期的订单，因此您可以进行一些计划。

To accomplish this, you could manually hide all the dates in the Ship Date page field, except the dates for next week. However, that might take quite a while if there are lots of dates.

为此，您可以在“发货日期”页面字段中手动隐藏所有日期，但下周的日期除外。 但是，如果有很多日期，则可能要花一些时间。

Another option is to add a new field to the source data, to test the ship dates, then add it to the pivot table, as a filter. This might slow things down a bit, if your source data table is very large.

另一种选择是在源数据中添加一个新字段，以测试发货日期，然后将其作为过滤器添加到数据透视表中。 如果您的源数据表很大，这可能会使速度变慢。

We'll look at both options in Excel 2003 -- the steps are slightly different in Excel 2007.

我们将介绍Excel 2003中的两个选项-步骤与Excel 2007中的稍有不同。

手动隐藏日期 (Manually Hide the Dates)

In Excel 2003, there are no check boxes beside the items in a Page field's drop down list, to allow you to select multiple items.

在Excel 2003中，“页面”字段的下拉列表中的项目旁边没有复选框，以允许您选择多个项目。

To hide some of the items in a pivot table's Page field, temporarily move the field to the Row area, and select the items there, then drag the date field back to the Page area.

要隐藏数据透视表的“页面”字段中的某些项目，请将其暂时移至“行”区域，然后在其中选择项目，然后将日期字段拖回到“页面”区域。

Or, without moving the field in the Page area, you can change the field's settings.

或者，无需在页面区域中移动该字段，就可以更改该字段的设置。

1. Double-click on the Ship Date field button.
   双击发运日期字段按钮。
2. In the PivotTable Field dialog box, click on dates in the Hide Items list.
   在“数据透视表字段”对话框中，单击“隐藏项目”列表中的日期。
3. Click OK to close the dialog box.
   单击“确定”关闭对话框。

将新字段添加到源数据 (Add a New Field to the Source Data)

Manually hiding the dates might work well if you only need to do this occasionally, and the list of dates isn't too long. Otherwise, the best solution might be to add a column to the pivot table's source data.

如果只需要偶尔执行一次，则手动隐藏日期可能会很好用，并且日期列表不会太长。 否则，最好的解决方案可能是在数据透视表的源数据中添加一列。

In this case, we'll add a column named ShipSoon, and use a formula to test if the ship date is within the next 10 days.

在这种情况下，我们将添加一个名为ShipSoon的列，并使用公式测试发货日期是否在接下来的10天内。

1. In the source data table, add a column with the heading ShipSoon. In this example, the new column is to the right of column A.
   在源数据表中，添加一个标题为ShipSoon的列。 在此示例中，新列在列A的右侧。
2. In the first data row of the new column, enter a formula that checks the ShipDate in that row. Our first ship date is in cell A2, and the formula will test the date, to see if it's within 10 days of today's date.
   在新列的第一数据行中，输入一个公式来检查该行中的ShipDate。 我们的第一个发货日期在A2单元格中，公式将测试该日期，以查看是否在今天日期的10天内。

=AND(A2>TODAY(),A2<=TODAY()+10)

= AND(A2> TODAY()，A2 <= TODAY()+ 10)

• Copy the formula down to the last row of data.
  将公式向下复制到数据的最后一行。

Each row will show TRUE or FALSE as the result of the formula.

每行将显示TRUE或FALSE作为公式的结果。

更新数据透视表 (Update the Pivot Table)

Next, you'll update the pivot table, and add the new field.

接下来，您将更新数据透视表，并添加新字段。

1. Refresh the pivot table, and add the ShipSoon field to the Page area.
   刷新数据透视表，然后将ShipSoon字段添加到Page区域。
2. From the ShipSoon Page field dropdown list, select TRUE.
   从“ ShipSoon页面”字段下拉列表中，选择“ TRUE”。

刷新数据透视表 (Refresh the Pivot Table)

Remember to refresh the pivot table each day to see the current calculations for the ShipSoon field.

请记住每天刷新数据透视表，以查看ShipSoon字段的当前计算。

• To manually refresh the pivot table, right-click on a cell in the pivot table, then click on Refresh Data.
  要手动刷新数据透视表，请右键单击数据透视表中的单元格，然后单击“刷新数据”。

Or, you can set the pivot table to automatically refresh when you open the Excel file.

或者，您可以将数据透视表设置为在打开Excel文件时自动刷新。

1. Right-click on a cell in the pivot table
   右键单击数据透视表中的单元格
2. Click Table Options
   单击表格选项
3. In the Data Options section, add a check mark to Refresh on Open
   在“数据选项”部分中，将一个复选标记添加到“打开时刷新”
4. Click OK to close the dialog box.
   单击“确定”关闭对话框。

翻译自: https://contexturesblog.com/archives/2009/06/08/filter-a-pivot-table-for-a-date-range/

数据透视表日期怎么选范围