vba 数据透视表不显示小计
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24170895/
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
PivotTable Do not show subtotals
提问by ingalcala
I am converting a table into Pivot and then to a CSV file. I want to remove the subtotals of the pivot table. So far: this is my progress.
我正在将表转换为 Pivot，然后转换为 CSV 文件。我想删除数据透视表的小计。到目前为止：这是我的进步。
MACRO CreatePivot:
宏创建枢轴：
Sub CreatePivot()
' Creates a PivotTable report from the table on Sheet1
' by using the PivotTableWizard method with the PivotFields
' method to specify the fields in the PivotTable.
Dim objTable As PivotTable, objField As PivotField
' Select the sheet and first cell of the table that contains the data.
ActiveWorkbook.Sheets("Employees").Select
Range("A1").Select
' Create the PivotTable object based on the Employee data on Sheet1.
Set objTable = Sheet1.PivotTableWizard
' Specify row and column fields.
Set objField = objTable.PivotFields("Supplier")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Part #")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Tracking #")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Packing/Inv#")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("PO#")
objField.Orientation = xlRowField
Set objField = objTable.PivotFields("Ship Date")
objField.Orientation = xlRowField
' Specify a data field with its summary
' function and format.
Set objField = objTable.PivotFields("Qty")
objField.Orientation = xlDataField
objField.Function = xlSum
' Specify a page field.
Set objField = objTable.PivotFields("Carrier")
objField.Orientation = xlPageField
' Prompt the user whether to delete the PivotTable.
Application.DisplayAlerts = False
If MsgBox("Delete the PivotTable?", vbYesNo) = vbYes Then
ActiveSheet.Delete
End If
Application.DisplayAlerts = True
End Sub
Also I just added MACRO DesigningPivotTable:
另外我刚刚添加了 MACRO DesigningPivotTable：
Sub DesigningPivotTable()
Dim PT As PivotTable
Set PT = ActiveSheet.PivotTables(1)
PT.TableRange1.Select
With PT
.NullString = 0
.RepeatAllLabels Repeat:=xlRepeatLabels
.ColumnGrand = False
.RowGrand = 0
.PivotFields("Order #").Subtotals(1) = True
.PivotFields("Order #").Subtotals(1) = False
End With
End Sub
According to Excel 2013 Pivot Table Data Crunching; you can turn on the first subtotal, and this method automatically disables all the other subtotals, hence: it is not working for me:
根据 Excel 2013 Pivot Table Data Crunching；您可以打开第一个小计，此方法会自动禁用所有其他小计，因此：它对我不起作用：
.PivotFields("Order #").Subtotals(1) = True
.PivotFields("Order #").Subtotals(1) = False
回答by ingalcala
All, thanks to some more research I found the solution
所有，多亏了更多的研究，我找到了解决方案
Sub PivotTableLayout2b()
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField
Set PvtTbl = ActiveSheet.PivotTables(1)
'hide Subtotals for all fields in the PivotTable .
With PvtTbl
For Each pvtFld In .PivotFields
pvtFld.Subtotals(1) = True
pvtFld.Subtotals(1) = False
Next pvtFld
End With
End Sub
回答by robotik
.Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
surely sets all possible subtotals to false, in one go.
肯定会一次性将所有可能的小计设置为 false。
回答by Bojan Goldy
.PivotFields("Pivot Field Name").Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
it is not better is it just to make it clear
不是更好是只是说清楚
回答by Danhadnagy
I think it's easier.
我认为这更容易。
ActiveSheet.PivotTables("Supplier").RowGrand = False
ActiveSheet.PivotTables("Supplier").ColumnGrand = False
回答by dra_red
I found I did not need to set the subtotal to 'true' first. Setting it directly to false was fine. Example below:
我发现我不需要先将小计设置为“true”。将其直接设置为 false 很好。下面的例子：
With PvtTbl
For Each pvtFld In .PivotFields
pvtFld.Subtotals(1) = False
Next pvtFld
End With
