VBA和透视表:获取两个数据字段
VBA和透视表:获取两个数据字段
提问于 2013-10-25 16:46:40
我正在尝试使用vba从一个标题为DATA的工作表中创建一个数据透视表。由多个专栏组成，其中三个专栏分别称为NOM、NOM_MOVE和COMMENT receptively。
我希望数据透视表具有以下字段:列标签字段=求和(值)，行标签字段=注释，值字段=名称总和，NOM_MOVE总和
然而，我写的代码不能工作，也不能给出正确的字段(即使是在不同的形式下使用pivottablewizard和cache/createpivottable。
我在分配正确字段时遇到问题的代码是：
Sub CreatePivot()
' Create the 5 summary pivot tables using the TRADE_LIST sheet as data
Dim pivot1 As Worksheet
Dim datasheet As Worksheet
Dim dataCache As PivotCache
Dim PVT As PivotTable
Dim LastRow As Long
Dim LastCol As Long
Dim dataSource As Range
Dim PVTdest As Range
Set datasheet = Sheets("DATA")
Set pivot1 = Sheets("PIVOT")
' (1) Source data from the TRADE_LIST sheet and create a pivot cache from source data to use for each pivot table
LastRow = datasheet.Cells(1, 1).End(xlDown).Row
LastCol = datasheet.Cells(1, 1).End(xlToRight).Column
Set dataSource = datasheet.Cells(1, 1).Resize(LastRow, LastCol)
Set dataCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=dataSource)
' (2) Create a pivot table
Set PVTdest = pivot1.Cells(2, 2)
Set PVT = dataCache.CreatePivotTable(tabledestination:=PVTdest, tablename:="Comment_Sums")..。
任何帮助都非常感谢
回答 1
Stack Overflow用户
发布于 2013-10-25 17:31:13
我对你的代码做了一些修改，所以试试这个：
Sub CreatePivot()
' Create the 5 summary pivot tables using the TRADE_LIST sheet as data
Dim LastRow, LastCol As Long
Dim dataSource, destination As String
Dim wb As Workbook
Dim data_sheet, pivot1 As Worksheet
Set wb = ThisWorkbook
Set data_sheet = wb.Sheets("DATA") 'in your code, you forgot to include WB
Set pivot1 = wb.Sheets("PIVOT")
' (1) Source data from the TRADE_LIST sheet and create a pivot cache from source data _
to use for each pivot table
'i changed this part to cover all data
LastRow = data_sheet.Cells(data_sheet.Rows.Count, 1).End(xlUp).Row
LastCol = data_sheet.Cells(1, data_sheet.Columns.Count).End(xlToLeft).Column
'PivotCache creation requires arguments in string format, no need to use Set
dataSource = data_sheet.Name & "!" & data_sheet.Cells(1, 1).Resize(LastRow, LastCol).Address(ReferenceStyle:=xlR1C1)
destination = pivot1.Name & "!" & pivot1.Range("A1").Address(ReferenceStyle:=xlR1C1)
'here we use the datasource and destination
wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
dataSource, Version:=xlPivotTableVersion12).CreatePivotTable _
TableDestination:=destination, TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion12
'after creating pivot, add the Row Field
With pivot1.PivotTables("PivotTable1").PivotFields("Comment")
.Orientation = xlRowField
.Position = 1
End With
'then add the column fields
With pivot1
.PivotTables("PivotTable1").AddDataField .PivotTables( _
"PivotTable1").PivotFields("NOM"), "Sum of NOM", xlSum
.PivotTables("PivotTable1").AddDataField .PivotTables( _
"PivotTable1").PivotFields("NOM_MOVE"), "Sum of NOM_MOVE", xlSum
End With
End Sub希望这能让你开始吧。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/19585221
复制相关文章
相似问题