数据透视表 字段交叉_组数据透视表报表过滤器字段

数据透视表 字段交叉

Welcome back! The Contextures Blog was out of commission for a couple of weeks, and it's nice to be up and running again. A few of the shingles blew off during the reconstruction, so if you notice anything missing or broken, please let me know!

欢迎回来！ Contextures博客已经停产了几个星期，很高兴再次启动并运行。 在重建过程中，一些木瓦会炸掉，因此，如果您发现任何缺失或损坏的东西，请告诉我！

通过报表过滤器字段关注数据 (Focus on Data with Report Filter Fields)

Now that we're back in business, let's take a look at the Report Filter fields in a pivot table. In older versions of Excel, these were called Page Fields, and they help you focus on specific data in an Excel pivot table.

现在我们重新开始工作，让我们看一下数据透视表中的“报告过滤器”字段。 在较早版本的Excel中，这些称为“页面字段”，它们可帮助您专注于Excel数据透视表中的特定数据。

Drop fields into the Report Filter area of the pivot table layout. Then, select one or more items from that pivot table field, to see the summarized data for the selected items.

将字段拖放到数据透视表布局的“报告过滤器”区域中。 然后，从该数据透视表字段中选择一个或多个项目，以查看所选项目的汇总数据。

In this example, the Order Date field is in the Report Filter area, and you can select a specific date, to see its orders.

在此示例中，“订单日期”字段位于“报表过滤器”区域中，您可以选择一个特定日期以查看其订单。

在“日期”字段中分组日期 (Group Dates in the Date Field)

If you put a date field in the Report Filter area, there might be a long list of dates in the dropdown list. Instead of seeing the individual dates, you might prefer to group them, by year or month.

如果将日期字段放在“报表过滤器”区域中，则下拉列表中可能会有一长串日期。 您可能不希望看到各个日期，而是希望按年或月对它们进行分组。

However, if you right-click on the Report Filter field, there isn't a command that lets you group the data. Are you doomed to a miserable existence of scrolling through the date list?

但是，如果您在“报表过滤器”字段上单击鼠标右键，则没有命令可以对数据进行分组 。 您是否注定要在日期列表中滚动浏览？

日期分组解决方法 (The Date Grouping Workaround)

Fortunately, there is a workaround that solves the Report Filter grouping problem. It's not pretty, but it works!

幸运的是，有一种解决方法可以解决“报表过滤器”分组问题。 它不漂亮，但是可以用！

To enable the grouping command, you'll temporarily move the Report Filter field to the Row Labels area. In the screen shot below, the OrderDate field is being dragged to the Row Labels area.

要启用分组命令，您需要将“报表过滤器”字段临时移至“行标签”区域。 在下面的屏幕快照中，OrderDate字段被拖到“行标签”区域。

Then, right-click on the field in the pivot table, and click Group. Select the Grouping options that you want, and click OK.

然后，右键单击数据透视表中的字段，然后单击“组”。 选择所需的分组选项，然后单击确定。

返回报告过滤器区域 (Back to the Report Filters Area)

Move the grouped fields back to the Report Filter area. In this example, the OrderDate field was grouped by Year and Month, and that created a new field – Years.

将分组的字段移回到“报表过滤器”区域。 在此示例中，OrderDate字段按Year和Month分组，并创建了一个新字段– Years。

Both the Years field and the OrderDate field are dragged back to the Report Filter area.

Years字段和OrderDate字段都被拖回到Report Filter区域。

Now, the pivot table can be filtered by year and/or month.

现在，可以按年份和/或月份筛选数据透视表。

Maybe in the next version of Excel you'll be able to group the fields, without moving them from the Report Filter area.

也许在下一版本的Excel中，您将能够对字段进行分组，而无需将它们从“报表过滤器”区域中移出。

翻译自: https://contexturesblog.com/archives/2011/11/29/group-pivot-table-report-filter-fields/

数据透视表 字段交叉