vba 根据下拉列表更改数据透视表过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17868204/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Changing pivot table filter based on a drop down
提问by SAM244776
I have drop down which has a list of countries. I need the pivot table to be filtered based on the country selected on the drop down. I am using this code.
我有下拉列表，其中包含国家/地区列表。我需要根据下拉列表中选择的国家/地区过滤数据透视表。我正在使用此代码。
Sub PivotChange(ByVal Target As Range)
If Not Application.Intersect(Target, Sheets("Summary").Range("D7")) Is Nothing Then
Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country"). _
ClearAllFilters
Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country").CurrentPage _
= Sheets("Summary").Range("D7").Value
End If
End Sub
But currently its not doing anything. Can anyone help me where should I add this code so that it will be triggered by the event.
但目前它没有做任何事情。任何人都可以帮助我在哪里添加此代码，以便它由事件触发。
Thanks
谢谢
回答by xlViki
Although it might be too late for you, this might help someone else. The CurrentPage property is valid only for Page fields. Use the below code for the purpose:
尽管对您来说可能为时已晚，但这可能对其他人有所帮助。CurrentPage 属性仅对页面字段有效。为此目的使用以下代码：
Sub PivotChange(ByVal Target As Range)
If Not Application.Intersect(Target, Sheets("Summary").Range("D7")) Is Nothing Then
Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country"). _
ClearAllFilters
Sheets("Data_4PivotChart").PivotTables("PivotTable7").PivotFields("Country").PivotFilters.Add _
Type:=xlCaptionEquals, Value1:=Sheets("Summary").Range("D7").Value
End If
End Sub
回答by Marc Andrew Johnston
I had this same problem and I have an easier work-around without VBA macros. I had a source list of mobile devices sold per region per company per department per manager and various other field info on the mobile devices which I had to report on using multiple pivot tables like a Rubik's cube. The source list had various sales dates, but the the client only wanted the sales from last week, which means I had to manually select the week to be reported from the filter drop-down box for each pivot table which was painful and time-consuming before I could select refresh all.
我遇到了同样的问题，没有 VBA 宏，我有一个更简单的解决方法。我有每个地区每个公司每个部门每个经理销售的移动设备的源列表以及移动设备上的各种其他字段信息，我必须使用多个数据透视表（如魔方）进行报告。源列表有不同的销售日期，但客户只想要上周的销售，这意味着我必须从每个数据透视表的过滤器下拉框中手动选择要报告的周，这既痛苦又耗时在我可以选择全部刷新之前。
Here is the solution: create a column in your source list and enter whatever criteria you wish to use - in my case I used a MAX function with a TRUE/FALSE evaluation criteria to find out if the record fell into the latest week. So you will have a list of TRUE/FALSE flags in this column. Then in your pivot tables filter select TRUE and your pivot tables will automatically filter to the true condition.
这是解决方案：在您的源列表中创建一个列并输入您希望使用的任何标准 - 在我的例子中，我使用了一个带有 TRUE/FALSE 评估标准的 MAX 函数来确定记录是否属于最近一周。因此，您将在此列中有一个 TRUE/FALSE 标志列表。然后在您的数据透视表过滤器中选择 TRUE，您的数据透视表将自动过滤到真实条件。
回答by Stepan1010
Are you familiar with the "Report Filter" feature of pivot tables? It is a drop down that will filter your pivot table. You don't need to have a custom drop down. You can read about it here:
您熟悉数据透视表的“报表过滤器”功能吗？这是一个下拉列表，可以过滤您的数据透视表。您不需要自定义下拉菜单。你可以在这里读到它：
Also Here:
也在这里：
http://www.contextures.com/Excel-Pivot-Table-Report-Filters.html
http://www.contextures.com/Excel-Pivot-Table-Report-Filters.html
EDIT:
编辑：
To control multiple pivot tables you can use a slicer:
要控制多个数据透视表，您可以使用切片器：
EDIT AGAIN:
再次编辑：
That is only if your pivot tables share the same data source.
只有当您的数据透视表共享相同的数据源时。
Using single slicer to control two pivot tables with different data source in Excel
使用单个切片器在Excel中控制具有不同数据源的两个数据透视表
FOURTH EDIT:
第四次编辑：
You need to set up a worksheet change event on that cell with the drop down in it. The event will run your pivot table filter macro.
您需要在该单元格上设置一个工作表更改事件，并在其中设置下拉菜单。该事件将运行您的数据透视表过滤器宏。
