VBA 和数据透视表：获取两个数据字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19585221/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA and Pivot Tables: getting two data fields
提问by user2915209
I'm trying to use vba to create a pivot table from a data from a sheet entitled DATA. Is organised in a number of columns, three of which are called NOM, NOM_MOVE and COMMENT receptively.
我正在尝试使用 vba 从名为 DATA 的工作表中的数据创建数据透视表。被组织在许多列中，其中三个被称为 NOM、NOM_MOVE 和 COMMENT。
I want the pivot table to have the following field: Col labels field = Sum(Values), Row labels field = COMMENT, Values field = sum of NOM, sum of NOM_MOVE
我希望数据透视表具有以下字段：Col labels field = Sum(Values), Row labels field = COMMENT, Values field = sum of NOM, sum of NOM_MOVE
However, the code I have written doesn't work or give the right fields (even in different forms using pivottablewizard and cache/createpivottable.
但是，我编写的代码不起作用或提供了正确的字段（即使在使用 pivottablewizard 和 cache/createpivottable.
My code up until where I am having trouble assigning the right fields is:
在我无法分配正确字段之前，我的代码是：
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")
...
...
Any help much appreciated
非常感谢任何帮助
回答by L42
I made some revisions to your code so try this:
我对你的代码做了一些修改，所以试试这个：
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
Hope this get's you started.
希望这让你开始。
