我有一个使用数据透视表的计算。
我希望创建一个通用的数据透视表,可以在所有需要进行同样计算的时候使用。
我已经尝试过了,但是计算总是被数据透视表阻塞。
Sheets.Add.Name = "tdc_flux"
Sheets("flux phf a+1").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"flux phf a+1!R1C1:R1048576C16", Version:=6).CreatePivotTable _
TableDestination:="tdc_flux!R3C1", TableName:="Tableau croisé dynamique3", _
DefaultVersion:=6
Sheets("tdc_flux").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("Tableau croisé dynamique3")
.ColumnGrand = True
.HasAutoFormat = True
.DisplayErrorString = False
.DisplayNullString = True
.EnableDrilldown = True
.ErrorString = ""
.MergeLabels = False
.NullString = ""
.PageFieldOrder = 2
.PageFieldWrapCount = 0
.PreserveFormatting = True
.RowGrand = True
.SaveData = True
.PrintTitles = False
.RepeatItemsOnEachPrintedPage = True
.TotalsAnnotation = False
.CompactRowIndent = 1
.InGridDropZones = False
.DisplayFieldCaptions = True
.DisplayMemberPropertyTooltips = False
.DisplayContextTooltips = True
.ShowDrillIndicators = True
.PrintDrillIndicators = False
.AllowMultipleFilters = False
.SortUsingCustomLists = True
.FieldListSortAscending = False
.ShowValuesRow = False
.CalculatedMembersInFilters = False
.RowAxisLayout xlCompactRow
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("Tableau croisé dynamique3").RepeatAllLabels _
xlRepeatLabels
With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
"Concatner (ref +div)")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("Tableau croisé dynamique3").PivotFields( _
"Type de flux")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("Tableau croisé dynamique3").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique3").PivotFields(" En DICtrPr"), _
"Somme de En DICtrPr", xlSum
ActiveSheet.PivotTables("Tableau croisé dynamique3").AddDataField ActiveSheet. _
PivotTables("Tableau croisé dynamique3").PivotFields(" En DICtrPr"), _
"Somme de En DICtrPr", xlSum
这是代码中的一个阻塞部分。
"Somme" 意为英语中的 sum,"Tableau croisé dynamque" 是数据透视表的名称。