When you're analyzing data in an Excel pivot table, you might want to see the detail behind one of the numbers. To extract the data, you can double-click a data cell and a new worksheet is created, with the related records.
在分析Excel数据透视表中的数据时,您可能希望查看其中一个数字后面的详细信息。 要提取数据,您可以双击一个数据单元格,并创建一个带有相关记录的新工作表。
This is a nice feature, but you'll end up with extra sheets in your workbook, and will need to clean things up occasionally.
这是一个不错的功能,但最终会在工作簿中增加多余的工作表,并且偶尔需要清理。
过滤源数据 (Filter the Source Data)
If the pivot table source data is in the same workbook, you can use the following macro, written by Héctor Miguel Orozco Díaz. It filters the source data, based on the pivot items connected to the double-clicked cell.
如果数据透视表源数据在同一工作簿中,则可以使用HéctorMiguel OrozcoDíaz编写的以下宏。 它根据连接到双击单元格的数据透视表项过滤源数据。
For example, if you double-click the cell circled in screenshot below:
例如,如果双击下面屏幕截图中圈出的单元格:
the source data is filtered for Class_A, Month_3, Store_1, Code_A cost.
源数据将按Class_A,Month_3,Store_1和Code_A成本进行过滤。
This lets you focus on the detail records, without creating new worksheets.
这使您可以专注于明细记录,而无需创建新的工作表。
下载样本文件 (Download the Sample File)
Héctor's code is shown here, and you can download the sample file to filter a pivot table's source data.
此处显示了 Héctor的代码 ,您可以下载示例文件以过滤数据透视表的源数据 。
There is also a sample file with a shorter version of the code. ________________
还有一个示例文件,其中包含较短版本的代码 。 ________________
翻译自: https://contexturesblog.com/archives/2009/01/20/filter-pivot-table-source-data-in-excel/