如何在VBA创建透视表时启用「添加到数据模型」选项？

要让透视表支持Distinct Count（去重计数），核心是创建透视表时将数据添加到数据模型。你只需要在创建PivotCache时添加IncludeInModel:=True参数，就能自动勾选「数据添加到数据模型」选项。

修改后的完整代码 #

Sub test()
    Dim testpath As String
    testpath = "C:\UserData\testfolder\testfile.xlsx"

    With Workbooks.Open(testpath)
        Dim ws As Worksheet
        Set ws = .Worksheets(2)
        
        Dim newSheet As Worksheet
        Set newSheet = .Worksheets.Add(Before:=ws)
        newSheet.Name = "PivotTableSheet"
        
        ' 创建关联数据模型的PivotCache
        Dim pivotCache As PivotCache
        Set pivotCache = .PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:=ws.UsedRange, _
            Version:=xlPivotTableVersion15, _
            IncludeInModel:=True ' 关键参数：将数据添加到数据模型
        )
        
        ' 创建透视表
        Dim pivotTable As PivotTable
        Set pivotTable = pivotCache.CreatePivotTable( _
            TableDestination:=newSheet.Cells(1, 1), _
            TableName:="PivotTable1", _
            DefaultVersion:=xlPivotTableVersion15 _
        )
        
        ' 示例：添加行字段和去重计数值字段
        With pivotTable
            ' 添加行字段（请根据实际数据列名修改）
            .PivotFields("类别").Orientation = xlRowField
            
            ' 添加去重计数值字段（请根据实际需要统计的列名修改）
            .AddDataField .PivotFields("ID"), "去重计数", xlDistinctCount
        End With
    End With
End Sub

关键说明 #

• IncludeInModel:=True：这是启用数据模型的核心参数，添加后数据会自动加入工作簿的数据模型，透视表就能支持去重计数功能。
• 确保Excel版本为2013及以上：xlPivotTableVersion15对应Excel 2013，Distinct Count功能从该版本开始支持。
• 添加值字段时指定xlDistinctCount：在AddDataField方法中，第三个参数设置为xlDistinctCount，即可实现目标列的去重计数。

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