透视表计数宏

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

Pivot Table Count Macro

问题

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

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

这是原始数据的示例：

透视表计数宏

这是我需要的结果：

透视表计数宏

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

这是我尝试过的代码：

  1. NomHoja = ActiveSheet.Name
  2. Range("A1:B11").Select
  3. Sheets.Add
  4. NomHojaTD = ActiveSheet.Name
  5. ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  6.     "'" & NomHoja & "'!R1C1:R11C2", Version:=xlPivotTableVersion15).CreatePivotTable _
  7.     TableDestination:= "'" & NomHojaTD & "'!R3C1", TableName:="Tabla dinámica3", _
  8.     DefaultVersion:=xlPivotTableVersion15
  9. Sheets(NomHojaTD).Select
  10. Cells(3, 1).Select
  11. With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Type ")
  12.     .Orientation = xlRowField
  13.     .Position = 1
  14. End With
  15. With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Level")
  16.     .Orientation = xlColumnField
  17.     .Position = 1
  18. End With
  19. ActiveSheet.PivotTables("Tabla dinámica3").AddDataField ActiveSheet.PivotTables _
  20.     ("Tabla dinámica3").PivotFields("Level"), "Cuenta de Level", _
  21.     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:

  1. NomHoja = ActiveSheet.Name
  2.     Range("A1:B11").Select
  3.     Sheets.Add
  4. NomHojaTD = ActiveSheet.Name
  5.     ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
  6.         "'" & NomHoja & "'!R1C1:R11C2", Version:=xlPivotTableVersion15).CreatePivotTable _
  7.         TableDestination:="'" & NomHojaTD & "'!R3C1", TableName:="Tabla dinámica3", _
  8.         DefaultVersion:=xlPivotTableVersion15
  9.     Sheets(NomHojaTD).Select
  10.     Cells(3, 1).Select
  11.     With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Type ")
  12.         .Orientation = xlRowField
  13.         .Position = 1
  14.     End With
  15.     With ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("Level")
  16.         .Orientation = xlColumnField
  17.         .Position = 1
  18.     End With
  19.     ActiveSheet.PivotTables("Tabla dinámica3").AddDataField ActiveSheet.PivotTables _
  20.         ("Tabla dinámica3").PivotFields("Level"), "Cuenta de Level", _
  21.         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...

  1. Sub Tester()
  2.     Dim wb As Workbook, pc As PivotCache, pt As PivotTable
  3.     Dim wsData As Worksheet, wsPT As Worksheet, rngData As Range
  4.     
  5.     Set wsData = ActiveSheet
  6.     Set rngData = wsData.Range("A1").CurrentRegion 'the data for the PT
  7.     Set wb = wsData.Parent     'parent workbook
  8.     
  9.     Set wsPT = wb.Worksheets.Add(after:=wsData)
  10.     
  11.     'create the pivot cache
  12.     Set pc = wb.PivotCaches.Create(SourceType:=xlDatabase, _
  13.           SourceData:=rngData, Version:=xlPivotTableVersion15)
  14.     'create the pivot table
  15.     Set pt = pc.CreatePivotTable(TableDestination:=wsPT.Range("A3"))
  16.     
  17.     With pt.PivotFields("Type")
  18.         .Orientation = xlRowField
  19.         .Position = 1
  20.     End With
  21.     
  22.     pt.AddDataField pt.PivotFields("Level"), _
  23.             "Cuenta de Level", xlCount
  24.     
  25.     With pt.PivotFields("Level")
  26.         .Orientation = xlColumnField
  27.         .Position = 1
  28.     End With
  29.     
  30. End Sub

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

发表评论

匿名网友

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

确定