透视表计数宏

huangapple go评论63阅读模式
英文:

Pivot Table Count Macro

问题

I'm here to provide you with the translation of your content:

我正在尝试创建一个包含数据透视表的宏,但我无法使其正常工作...当我录制宏时,一切都正常,但当我再次执行它时,它无法产生预期的结果...

这是原始数据的示例:

透视表计数宏

这是我需要的结果:

透视表计数宏

我需要宏按级别具有多列,并计算每个级别的数量,但我无法使用宏来复制这个功能...

这是我尝试过的代码:

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”用作行和计数...

(Note: I've removed the HTML character codes like " for clarity in the translation.)

英文:

I´m trying to do a Macro that includes a Pivot Table, but I´m unable to make it work... When I record the macro all works OK, but when I execute it again, it does not deliver the expected results...

This is an Example of Original Data

透视表计数宏

This is what I need

透视表计数宏

I need the macro to have multiple Colums by Level, and Count each Level
But I´m unable to reproduce that with a macro...

Here is the code I tried:

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

But this is what I get instead...
This is what I get...

Seems I cannot use "Level" as Row and Ccount at the same time...

答案1

得分: 0

这对我有效 - 似乎关键在于添加字段的顺序...

英文:

This works for me - it seems to matter what order you add the fields in...

Sub Tester()
    Dim wb As Workbook, pc As PivotCache, pt As PivotTable
    Dim wsData As Worksheet, wsPT As Worksheet, rngData As Range
    
    Set wsData = ActiveSheet
    Set rngData = wsData.Range("A1").CurrentRegion 'the data for the PT
    Set wb = wsData.Parent     'parent workbook
    
    Set wsPT = wb.Worksheets.Add(after:=wsData)
    
    'create the pivot cache
    Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
          SourceData:=rngData, Version:=xlPivotTableVersion15)
    'create the pivot table
    Set pt = pc.CreatePivotTable(TableDestination:=wsPT.Range("A3"))
    
    With pt.PivotFields("Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    pt.AddDataField pt.PivotFields("Level"), _
            "Cuenta de Level", xlCount
    
    With pt.PivotFields("Level")
        .Orientation = xlColumnField
        .Position = 1
    End With
    
End Sub

huangapple
  • 本文由 发表于 2023年5月11日 04:26:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/76222315.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定