VBA数据透视表宏执行异常：无法同时用Level做列字段与计数

问题描述 #

• 录制数据透视表宏时运行正常，手动执行宏却无法得到预期效果
• 需求：按Level字段生成多列，统计每个Type下各Level的数量
• 核心问题：无法同时将Level用作列字段和计数字段

数据说明 #

• 原始数据：包含两列（Type、Level），Type列有A/B/C等类别，Level列有1/2/3等层级，共10行有效数据
• 预期效果：数据透视表以Type为行字段，各Level值为列字段，单元格展示对应Type下该Level的记录数量
• 实际执行结果：无法正确生成列字段与计数字段的组合，统计结果不符合预期

尝试的代码 #

NomHoja = ActiveSheet.Name
Range("A1:B11").Select
Sheets.Add
NomHojaTD = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "'" & NomHoja & "'!R1C1:R11C2", Version:=xlPivotTableVersion15).CreatePivotTable _
    TableDestination:="' & NomHojaTD & "'!R3C1", TableName:="Tabla dinámica3", _
    DefaultVersion:=xlPivotTableVersion15
Sheets(NomHojaTD).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Type ")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Level")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.PivotTables("Tabla dinámica3").AddDataField ActiveSheet.PivotTables _
    ("Tabla dinámica3").PivotFields("Level"), "Cuenta de Level", _
    xlCount

解决方案 #

问题出在同时将Level用作列字段和计数字段，导致数据透视表逻辑冲突。只需将计数字段替换为任意非空字段（比如Type）即可，因为xlCount统计的是非空单元格数量，只要对应行有数据，结果就等同于统计Level的数量。

修改后的代码 #

NomHoja = ActiveSheet.Name
' 避免使用Select，直接引用对象更稳定
Dim srcRange As Range
Set srcRange = ThisWorkbook.Sheets(NomHoja).Range("A1:B11")

Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Sheets.Add
NomHojaTD = newSheet.Name

Dim pivotCache As PivotCache
Set pivotCache = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:=srcRange, _
    Version:=xlPivotTableVersion15)

Dim pivotTable As PivotTable
Set pivotTable = pivotCache.CreatePivotTable( _
    TableDestination:=newSheet.Range("R3C1"), _
    TableName:="Tabla dinámica3", _
    DefaultVersion:=xlPivotTableVersion15)

' 设置行字段
With pivotTable.PivotFields("Type")
    .Orientation = xlRowField
    .Position = 1
End With

' 设置列字段
With pivotTable.PivotFields("Level")
    .Orientation = xlColumnField
    .Position = 1
End With

' 改用Type作为计数字段，统计非空数量
pivotTable.AddDataField _
    Field:=pivotTable.PivotFields("Type"), _
    Caption:="Cuenta de Level", _
    Function:=xlCount

说明 #

1. 移除了不必要的Select操作，直接引用对象让代码更稳定、高效
2. 将计数字段从Level改为Type，避免字段角色冲突
3. 因为Type列每行都有非空值，xlCount统计的结果就是对应Type+Level组合的记录数，和预期需求一致

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