数据透视图的数据不准确_使用一个过滤器更改所有数据透视图

数据透视图的数据不准确

Slicers were introduced in Excel 2010, and they make it easy to change multiple pivot tables with a single click. In earlier versions, you can use programming to change the report filters in multiples pivot tables.

切片器是Excel 2010中引入的，它们使单击一次更改多个数据透视表变得容易。 在早期版本中，您可以使用编程来更改多个数据透视表中的报表过滤器 。

AlexJ has shared a nice technique for using a single Report Filter to update multiple pivot tables. This technique uses slicers, but they are stored on a different worksheet, so they don’t take up room on the dashboard.

AlexJ分享了一种使用单个报表筛选器更新多个数据透视表的好方法。 该技术使用切片器，但是它们存储在不同的工作表中，因此不会占用仪表板上的空间。

单一报告过滤器 (Single Report Filter)

Here is a screen shot of AlexJ’s dashboard, with two filters at the top of the sheet. The pivot charts are all connected to the same Date slicer, so when you select a date, all the charts change.

这是AlexJ的仪表板的屏幕截图，表格顶部有两个过滤器。 数据透视图都连接到相同的日期切片器，因此，选择日期时，所有图表都会更改。

You can use more than one filter with this technique, and I’ve added a Market filter too. The third chart is not connected to the Market filter – it always shows the results for all markets.

您可以通过此技术使用多个过滤器，并且我还添加了Market过滤器。 第三个图表未连接到市场过滤器–它始终显示所有市场的结果。

Note – If you’re using more than one master filter, AlexJ warns us to leave a few blank rows between them. Otherwise, you’ll see a message that pivot tables can’t overlap one another.

注 –如果您使用多个主过滤器，AlexJ警告我们在它们之间留一些空白行。 否则，您会看到一则消息，即数据透视表不能相互重叠。

显示详细信息功能 (Show Details Feature)

Just remember that there is a problem with the Show Details feature, when you use slicers. If you don’t have the slicer fields in the pivot tables, you might see all the data, instead of the filtered data, when you double-click on a value cell.

请记住，使用切片器时，“显示详细信息”功能存在问题 。 如果数据透视表中没有切片器字段，则在双击值单元格时，可能会看到所有数据，而不是过滤后的数据。

With this technique though, the pivot tables are stored on a separate sheet, which could be hidden from the users, so that reduces the problem.

但是，通过这种技术，数据透视表存储在单独的工作表中，可以对用户隐藏，从而减少了问题。

观看视频 (Watch the Video)

To see the steps for creating the pivot charts, and connecting them through a slicer, watch this short video.

要查看创建数据透视图并通过切片器连接它们的步骤，请观看此简短视频。

演示地址

下载样本文件 (Download the Sample File)

To see how AlexJ’s technique works, you can visit my Contextures website, and download the sample file. On the Sample Excel Files page, go to the Pivot Tables section, and look for PT0031 - Change All Pivot Charts With One Filter.

要了解AlexJ的技术如何工作，您可以访问我的Contextures网站，并下载示例文件。 在“示例Excel文件”页面上，转到“数据透视表”部分，然后查找“ PT0031-使用一个过滤器更改所有数据透视图” 。

The technique will work in Excel 2010, and later versions, where slicers are available.

该技术将在可用切片器的Excel 2010和更高版本中使用。

翻译自: https://contexturesblog.com/archives/2012/12/13/change-all-pivot-charts-with-one-filter/

数据透视图的数据不准确