如何获取Excel OLAP数据透视表的可用筛选值？

我之前处理过OLAP多维数据集连接的Excel透视表，确实普通透视表的Items集合在OLAP场景下完全用不了，得换个思路——直接访问字段对应的Cube成员列表。

核心思路：利用CubeField.Members获取所有可用值 #

OLAP透视表的字段本质是多维数据集里的维度成员，所以我们要通过PivotField.CubeField拿到对应的Cube字段，再遍历它的Members集合，就能得到所有可筛选的有效值了。

完整VBA代码示例 #

Sub GetOLAPAvailableValuesAndFilter()
    Dim pt As PivotTable
    Dim targetField As PivotField
    Dim cubeField As CubeField
    Dim availableValues() As String
    Dim member As CubeMember
    Dim i As Integer
    Dim tempArray As Variant
    Dim filteredArray() As String
    Dim j As Integer
    
    ' 初始化变量（替换成你的透视表和字段名）
    Set pt = ActiveChart.PivotLayout.PivotTable
    Set targetField = pt.PivotFields(fieldOrder_two) ' 你的目标字段
    tempArray = Array("要筛选的值1", "要筛选的值2") ' 你的待筛选数组
    
    ' 获取对应Cube字段的所有可用成员
    Set cubeField = targetField.CubeField
    ReDim availableValues(1 To cubeField.Members.Count)
    For i = 1 To cubeField.Members.Count
        ' 这里用Caption是显示名称，若需底层唯一标识符可改用Name
        availableValues(i) = cubeField.Members(i).Caption
    Next i
    
    ' 检查待筛选值是否存在，同时处理大小写（统一转大写对比）
    j = 0
    ReDim filteredArray(1 To UBound(tempArray) + 1)
    For i = LBound(tempArray) To UBound(tempArray)
        If UBound(Filter(availableValues, UCase(tempArray(i)), True, vbTextCompare)) > -1 Then
            j = j + 1
            filteredArray(j) = tempArray(i)
        End If
    Next i
    ReDim Preserve filteredArray(1 To j)
    
    ' 仅当存在有效筛选值时才设置
    If j > 0 Then
        targetField.VisibleItemsList = filteredArray
    Else
        MsgBox "没有匹配的有效筛选值！"
    End If
End Sub

关键细节说明 #

• CubeMember的Caption vs Name：Caption是透视表中显示的名称，Name是多维数据集里的唯一标识符（通常带维度路径，比如[维度].[层级].[值]），如果你的筛选用的是完整标识符，就用Name而非Caption。
• 大小写检查：代码里用UCase统一转大写，再用Filter函数忽略大小写匹配，你也可以改成精确遍历对比，满足更严格的校验需求。
• 为什么不用错误处理：这种提前校验的方式，不仅能避免报错，还能让你灵活处理无效值（比如记录无效项、提示用户等），比单纯的On Error Resume Next要可控得多。

内容的提问来源于stack exchange，提问作者smartini