使用vba过滤数据透视表 [英] Filtering pivot table with vba
问题描述
我有一张图表，它从数据透视表中挑选了每天数据的信息。
我正在尝试创建activeX按钮，以便过滤数据，如ROW LABEL，以查看我的数据在上周和上个月的行为。
所以，直到现在为止，它不起作用是：
Private Sub weekbtn1_Click（）
Dim i As Integer
如果weekbtn1 = True然后
i = 0
直到datavalue（日期） - i = 42005
使用ActiveSheet .PivotTables（Pivotcompsprice）。PivotFields（Date）
.PivotItems DateValue（Date） - i.Visible = False
i = i + 1
End with
Loop
i = 0
Do Until i = 7
With ActiveSheet.PivotTables（Pivotcompsprice）。PivotFields（Date）
.PivotItems Datevalue（date） - i .Visible = True
结束
循环
Else
结束如果
End Sub
我把这个42005因为是最后一个日期，我的数据是1/1/2015 ...我在想有可能过滤所有的数据a sfalse，然后将True设置为我想要的，但不起作用。
有人可以帮助我吗？
无法隐藏PivotField中的所有项目。您必须至少留下一个可见的。
如果您使用VBA来利用内置的日期过滤器功能，将会更快更容易即：
这是一个示例文件，我只是这样做：
通过更改标志，它的工作原理是，我们必须要从最旧的数据的前5天记录在案：
如果您指定该RelativeTo字段中的实际日期，它将根据Number参数是正/负值从前向/后退计数。这是今天从今天起的5天内写的：
...这是过去5天：
它将允许您指定是否需要日，周，季度，月或年。例如，这是最近的两周，从最近的记录返回：
我在这里使用一个Worksheet_Change事件来触发它，但是如果你喜欢，你可以把它连接到一个按钮，然后将它提供给你想要的参数。
Private Sub Worksheet_Change（ByVal Target As Range）
Dim bContinue As Boolean
如果不相交（目标，范围（间隔））没有，然后bContinue = True
如果不相交（目标，范围（数字））不是然后bContinue = True
如果不相交（目标，范围（RelativeTo））是没有，然后bContinue = True
如果bContinue Then Pivots_FilterPeriod [Interval]，[Number]，[RelativeTo]，Sheet1.PivotTables（1）.PivotFields（Date）
End Sub
I have a chart that picks the info from a pivot table that I have with daily data. I am trying to create activeX buttons so it can filter the data which is as ROW LABEL, to see how my data behaved in the last week and in the last month
So, what I have until now and it is not working is:
Private Sub weekbtn1_Click() Dim i As Integer
If weekbtn1 = True Then
i = 0
Do Until Datavalue(date) - i = 42005
With ActiveSheet.PivotTables("Pivotcompsprice").PivotFields("Date")
.PivotItems DateValue(Date) - i.Visible = False
i = i + 1
End With
Loop
i = 0
Do Until i = 7
With ActiveSheet.PivotTables("Pivotcompsprice").PivotFields("Date")
.PivotItems Datevalue(date) - i.Visible = True
End With
Loop
Else
End If
End Sub
I put this 42005 because it is the last date that I have the data which is 1/1/2015...I was thinking that it was possible to filter all the data as "false" and then Making True what I want, but it doesn't work!
Can someone help me?
It's not possible to hide all items in a PivotField. You always have to leave at least one visible.
It will be much faster and easier if you use VBA to leverage the inbuilt Date Filters functionality i.e. this:
Here's a sample file where I do just that: https://1drv.ms/x/s!Ah_zTnaUo4DzjhezQ3OTq9tq1APC
Note that this functionality is only available on RowFields or ColumnFields. So my code below won't work on PageFields.
Here's a generic routine that lets you choose what interval type and interval period you want to filter the pivot field on, as well as optionally select the date you want to count forward/back from.
Sub Pivots_FilterPeriod(sInterval As String, _
lNumber As Long, _
Optional vRelativeTo As Variant, _
Optional pf As PivotField)
'Programmer: Jeff Weir
'Contact: weir.jeff@gmail.com
'Description: Lets you programatically filter a Pivot RowField or ColumnField by specifying
' an interval type (e.g. days, weeks, months, quarters, years)
' as well as an interval count (e.g. 7, -7)
' If the optional vRelativeTo field is left blank, it counts back/foward from
' the youngest/oldest item depending on whether lNumber is positive/negative
' It leverages off the inbuilt DateFilters functionality, and as such does not
' work on RowFields.
Dim dteDateAdd As Date
Dim dteFrom As Date
Dim dteTo As Date
On Error GoTo errhandler
If pf Is Nothing Then
On Error Resume Next
Set pf = ActiveCell.PivotField
On Error GoTo errhandler
If pf Is Nothing Then GoTo errhandler
End If
With pf
If .DataType = xlDate _
And .Orientation <> xlPageField _
And .Orientation <> xlDataField Then
If IsMissing(vRelativeTo) Or vRelativeTo = "" Then
.AutoSort xlAscending, "Date"
If lNumber > 0 Then
vRelativeTo = .PivotItems(1)
Else
vRelativeTo = .PivotItems(.PivotItems.Count)
End If
End If
Select Case UCase(sInterval)
Case "D", "DD", "DDD", "DDDD", "DAY", "DAYS": sInterval = "d"
Case "W", "WW", "WWW", "WWWW", "WEEK", "WEEKS": sInterval = "ww"
Case "M", "MM", "MMM", "MMMM", "MONTH", "MONTHS": sInterval = "m"
Case "Q", "QQ", "QQQ", "QQQQ", "QUARTER", "QUARTERS": sInterval = "q"
Case "Y", "YY", "YYY", "YYYY", "YEAR", "YEARS": sInterval = "yyyy"
End Select
dteDateAdd = DateAdd(sInterval, lNumber, vRelativeTo)
If lNumber > 0 Then
dteDateAdd = dteDateAdd - 1
Else
dteDateAdd = dteDateAdd + 1
End If
If dteDateAdd < vRelativeTo Then
dteFrom = dteDateAdd
dteTo = vRelativeTo
Else
dteFrom = vRelativeTo
dteTo = dteDateAdd
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
.ClearAllFilters
.PivotFilters.Add2 _
Type:=xlDateBetween, _
Value1:=CStr(dteFrom), _
Value2:=CStr(dteTo)
End If
End With
errhandler:
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Here's some screenshots demonstrating how it looks in practice, using different parameters.
This shows how to filter on the last 5 days from the most recent data:
And by changing the sign, it works out that we must want the first 5 days from the oldest data on record:
If you specify an actual date in that RelativeTo field, it will count forward/back from there depending on whether the Number parameter is positive/negative. Here's the next 5 days from today's date as I write this:
...and here's the last 5 days:
It will let you specify whether you want days, weeks, quarters, months, or years. For instance, here's the last 2 weeks counting back from the most recent record:
I'm using a Worksheet_Change event here to trigger it, but you can hook it up to a button if you like, and feed it the parameters you want.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim bContinue As Boolean
If Not Intersect(Target, Range("Interval")) Is Nothing Then bContinue = True
If Not Intersect(Target, Range("Number")) Is Nothing Then bContinue = True
If Not Intersect(Target, Range("RelativeTo")) Is Nothing Then bContinue = True
If bContinue Then Pivots_FilterPeriod [Interval], [Number], [RelativeTo], Sheet1.PivotTables(1).PivotFields("Date")
End Sub
这篇关于使用vba过滤数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
