我一直在 VBA 中使用普通的数据透视表,但我最近使用我非常喜欢的数据模型在数据透视表上发现了一些功能——主要是"非重复计数"。我在普通的数据透视表中有一些代码,可以过滤表中的记录"喜欢"字符串,并且效果很好。如何使用数据模型将此代码转换为数据透视表?
With ActiveSheet.PivotTables("Metrics").PivotFields("Reference number")
.Orientation = xlPageField
.Position = 1
.EnableMultiplePageItems = True
For i = 1 To .PivotItems.Count
If .PivotItems(i).Name Like "*oran*" Then
.PivotItems(i).Visible = False
End If
Next i
End With
以下是我在录制宏并选择要在数据模型下手动显示的项目时创建的代码:
ActiveSheet.PivotTables("Metrics").PivotFields("[RawData].[Status].[Status]"). _
VisibleItemsList = Array("[RawData].[Status].&[apple_434]", _
"[RawData].[Status].&[banana_689]", _
"[RawData].[Status].&[orange_1346]", _
"[RawData].[Status].&[orange_1454]")
这是我前进的方向,但我在访问 VisibleItemsList 数组时遇到了一些问题:
With ActiveSheet.PivotTables("Metrics").PivotFields("[RawData].[Status].[Status]")
For i = 0 To UBound(.VisibleItemsList)
If i Like "*oran*" Then
i = ""
Debug.Print i
End If
Next i
End With
输出 i 是数字 0,1,2,3,4 --不是文本,并且数字似乎与筛选器列表中的项目数不对应。我不知道如何访问这些项目,所以我可以使用代码显示或隐藏我想要的项目。老实说,我已经很久没有使用数组了。
我最终使用切片器来过滤数据。
Dim sC As SlicerCache
Dim sL As SlicerCacheLevel
Dim aArray() As Variant
'create a slicer cache. I just used a recorded macro to get the cache code
ActiveWorkbook.SlicerCaches.Add2(ActiveSheet.PivotTables("Metrics"), _
"[RawData].[Status]").Slicers.Add ActiveSheet, "[RawData].[Status].[Status]", _
"Status", "Status", 141.75, 424.5, 144, 198.75
Set sC = ActiveWorkbook.SlicerCaches("Slicer_Status")
Set sL = sC.SlicerCacheLevels(1) 'this will start with the first item in the slicer
With sL
For i = 1 To .Count
If sL.SlicerItems.Item(i).Name Like "*oran*" Then
GoTo nextiteration 'this will skip over anything
'like oran when saving to the array
End If
ReDim Preserve aArray(0 To i) As Variant
aArray(i) = sL.SlicerItems.Item(i).Name
nextiteration:
Next i
sC.VisibleSlicerItemsList = aArray 'this set the visible items
'= to the array you just created
'ActiveSheet.Shapes("Status").Visible = False
'to hide this slicer, uncomment the line above
End With
Paul te Braak的这篇文章提供了大部分解决方案。我还使用本教程来帮助将项目保存到数组。当我需要使用动态数组时,这个 stackoverflow 答案也帮助了我。感谢-GregGalloway和-jeffreyweir:在浏览您提供的链接时,我想到了使用切片器搜索解决方案的想法。
我知道这是一个老问题,但我最近为此提出了一个很好的解决方案:
<your other code goes here>
dim v as Variant, xlPT as Excel.PivotTable, str as String
str = vbNullString
set xlPT = xlBook.PivotTables("MyPivot")
with xlPT
for each v in .PivotFields("[table].[field]").PivotItems
select case Right(Left(v.Name, Len(v.Name) - 1), 4) 'my variables are 4-char strings
' But you can manipulate this piece to match your need
case "1234", "4312", "4321", "7462", "etc."
str = str & v.Name & ";"
end select
next v
.CubeFields("[table].[field]").PivotFields(1).VisibleItemsList = Split(str, ";")
end with
<the rest of your code goes here>
这里的关键知识是 CubeField
对象包含一个只有 1 个成员的 PivotFields
集合,我们可以在其中使用VisibleItemsList
。
希望这对将来的某人有所帮助。快乐的索英