通过多个条件筛选VBA [英] Filter by multiple conditons pivot VBA

问题描述

我在枢轴表上有四个过滤器，week_of_year（1,2至52），日期（1/1/2012至2/7/2016），年份（2012年至2016年），城市（纽约州到圣弗朗西斯科）

  Sub datefilter（）
 Dim PvtTbl As PivotTable 
 Set PvtTbl = Worksheets（test）。数据透视表（PivotTable3）
 Dim pf As PivotField 
 Dim pf1 As PivotField 
 Dim PI As PivotItem 
 Set pf = 
 Worksheets（test）。PivotTables PivotTable3）PivotFields（week_of_year）
设置pf1 = 
工作表（test）。数据透视表（PivotTable3）PivotFields（year）
设置pf2 = 
工作表（test）。数据透视表（PivotTable3）。PivotFields（Date）
设置pf3 = 
工作表（test）。数据透视表（PivotTable3）PivotFields city）
 
 PvtTbl.ClearAllFilters 
 
对于每个PI在pf.PivotItems 
如果PI.Name =7然后
 PI.Visible = True 
 Else 
 PI.Visible = False 
结束如果
下一个
 
对于每个PI在pf1.PivotItems 
如果PI.Name =2014或PI.Name =2015然后
 PI.Visible = True 
 Else 
 PI.Visible = False 
结束如果
下一个
 
对于日期，城市，选择多个项目作为过滤器。 
 End Sub 
  

这个工作可以永远运行，可以循环每个记录。尝试找到一种更有效的方式来做到这一点。任何一个可以使用currentpage或pivotfield.add来实现同样的事情？感谢!!!感谢一堆！

特殊要求：数据透视表链接到数据库。所以当新日期闪烁时，需要从过滤器中选择更多的日期。例如，如果2/9/16进入，还需要选择数据< = 2/9/15并且仅选择2015年的数据，忽略2012,2013,2014。

解决方案

最有效的解决方案是使用动态设置存储过程底层数据库中的过程将让您在数据库结束时过滤数据。该SP可以在电子表格中的列表上绘制。请参见 http：/ /datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

如果不可能，我的文章在 http ：//dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ 具有可以做你想要的代码，正如我已经在您的其他主题关于此主题

阅读该线程，您将学习如何大幅度减少循环访问PivotItem并更改其状态所需的时间。 / p>

另外请注意，在遍历数据透视图之前您要设置PT的项目。 .ManualUpdate为TRUE，而您进行更改，然后将其设置为FALSE，以避免数据透视表在每次更改后刷新。

I have four filters on a pivot table,week_of_year(1,2 to 52),date(1/1/2012 to 2/7/2016),year(2012 to 2016),city(newyork to san fransisco)

Sub datefilter()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("test").PivotTables("PivotTable3")
Dim pf As PivotField
Dim pf1 As PivotField
Dim PI As PivotItem
Set pf =              
Worksheets("test").PivotTables("PivotTable3").PivotFields("week_of_year")
Set pf1 =       
Worksheets("test").PivotTables("PivotTable3").PivotFields("year")
Set pf2 =       
Worksheets("test").PivotTables("PivotTable3").PivotFields("Date")
Set pf3 =       
Worksheets("test").PivotTables("PivotTable3").PivotFields("city")

PvtTbl.ClearAllFilters

For Each PI In pf.PivotItems
If PI.Name = "7" Then
PI.Visible = True
Else
PI.Visible = False
End If
Next

For Each PI In pf1.PivotItems
If PI.Name = "2014" Or PI.Name = "2015" Then
PI.Visible = True
Else
PI.Visible = False
End If
Next

''Same for the Date,city, have multiple items selected as filter.
End Sub

This works but take forever to run, cuz its looping each record. Try to find a more efficient way to do it. Any one can use currentpage or pivotfield.add to achieve the same thing? Appreciate!!!Thanks a bunch!

Special request: the pivot table is linked to database. So when new date flashes in, need to select more date from the filter. For example, if 2/9/16 gets in,also need to select data <= 2/9/15 and only select data for 2015, neglect 2012,2013,2014. Looping each record is too cumbersome and takes forever to run.

解决方案

The most efficient solution is to set up a Stored Procedure with dynamic procedures in the underlying database that will let you filter your data at the Database end. That SP could then draw on a list in the spreadsheet. See http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

If that is not possible, my article at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/ has code that will do what you want, as I've already outlined at your other thread on this topic

Read that thread, and you will learn how to drastically reduce the amount of time it takes to loop through PivotItems and change their status.

Also, note that before you iterate through PivotItems you want to set PT. .ManualUpdate to TRUE while you make your changes, and set it back to FALSE afterwards to avoid the PivotTable refreshing after each and every change.

这篇关于通过多个条件筛选VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！