数据透视表和数据交叉表_隐藏数据透视表小计

数据透视表和数据交叉表

Sometimes the Excel macro recorder creates code that gets you off to a good start. Other times, it's not so helpful.

有时，Excel宏记录器会创建使您入门的代码。 其他时候，它不是那么有用。

This week, I was working on a pivot table macro, and wanted to turn off all the Row Field subtotals. Fortunately, there is a handy command for this on the Ribbon, on the Design tab, under PivotTable Tools. One click, and the subtotals disappear, or reappear.

本周，我正在处理数据透视表宏，并希望关闭所有“行字段”小计 。 幸运的是，功能区上的“数据透视表工具”下的“设计”选项卡上有一个方便的命令。 一键，小计消失或重新出现。

Let's get the code for that, which I'm sure will be equally clean and simple. Ha!

让我们获取该代码，我相信它同样干净简单。 哈！

记录步骤 (Record the Steps)

Full of optimism, developed over my years of working with Excel, I turned on the macro recorder. Then, I recorded the steps while I used the Do Not Show Subtotals command. I turned off the recorder, and opened the Visual Basic Editor.

在使用Excel多年的过程中，我充满了乐观，因此打开了宏记录器。 然后，当我使用“不显示小计”命令时，记录了这些步骤。 我关闭了记录器，并打开了Visual Basic编辑器。

In the screen shot below, you can see the code from my macro recording. Yikes! It didn't look anything like a simple click of the button had been recorded.

在下面的屏幕截图中，您可以看到我的宏录制中的代码。 kes！ 看起来好像没有任何记录，只需单击按钮即可。

Instead of just one line of code, a line had been recorded for each field in the source data, to turn off the subtotals individually.

在源数据中为每个字段记录了一行代码，而不仅仅是一行代码，以分别关闭小计。

I needed flexible code, for a variety of pivot table layouts, so this wasn't going to be much help.

我需要灵活的代码，用于各种数据透视表布局，因此这不会有太大帮助。

代码的作用 (What the Code Does)

To see what the macro recorder created, here is one line of the code, for the Category field:

若要查看宏记录器创建的内容，以下是“类别”字段的一行代码：

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals = _
    Array(True, False, False, False, _
      False, False, False, False, _
      False, False, False, False)

Each of the items in the Array represents one of the Subtotal options that you see when you right-click on a pivot field, and choose Field Settings.

数组中的每个项目代表右键单击枢轴字段并选择“字段设置”时看到的小计选项之一。

自动小计 (Automatic Subtotals)

The first item in the Array is for the Automatic and None settings

数组中的第一项是“自动”和“无”设置

• if you click Automatic, that item in the Array is True
  如果单击“自动”，则数组中的该项为True
• if you click None, the first item in the Array is False
  如果您单击无，则数组中的第一项为False

The other items in the Array represent the Custom subtotal functions, in the order that they are listed in the Field Settings window.

数组中的其他项代表“自定义”小计功能，按照它们在“字段设置”窗口中列出的顺序。

• Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp
  总和，计数，平均值，最大值，最小值，乘积，计数数，StdDev，StdDevp，Var，Varp

自动是老板 (Automatic is the Boss)

In the manual Field Settings, the Automatic setting is the boss. In the screen shot below, I selected several Custom subtotal functions.

在手动“字段设置”中，“自动”设置为首选项。 在下面的屏幕截图中，我选择了几个“自定义”小计功能。

But, as soon as I click Automatic, those Custom subtotals are ignored – only the Automatic subtotal will be shown when I click OK.

但是，当我单击“自动”时，这些“自定义”小计将被忽略–单击“确定”时，仅显示“自动”小计。

The same thing happens in the VBA code:

VBA代码中发生了相同的事情：

• If the first item is True, it doesn't matter if any of the other items are set to True – they will be ignored.
  如果第一个项目为True，则其他任何项目都设置为True都没关系–它​​们将被忽略。

Here is another version of the code, for the Category field, with the first 3 items changed to True:

这是代码的另一个版本，在类别字段中，前3项更改为True：

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals = _
    Array(True, True, True, True, _
      False, False, False, False, _
      False, False, False, False)

If I run that code, only the Automatic subtotals will appear.

如果运行该代码，则只会显示“自动”小计。

However, if I change the first item to False, the Custom subtotals for Sum, Count and Average are shown, when I run the code, because Automatic subtotals are turned off.

但是，如果我将第一项更改为False，则在运行代码时，将显示“总计”，“计数”和“平均值”的“自定义”小计，因为“自动”小计已关闭。

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals = _
    Array(False, True, True, True, _
      False, False, False, False, _
      False, False, False, False)

Here is the pivot table with the custom subtotals.

这是带有自定义小计的数据透视表。

只是自动设置 (Just the Automatic Setting)

Since I'm only interested in turning subtotals off, I can focus on the Automatic setting, and ignore all the others.

由于我只对关闭小计感兴趣，因此我可以专注于“自动”设置，而忽略所有其他设置。

If you look in the VBA help for the PivotField.Subtotals property, you'll see that you can use an index number, instead of an Array.

如果在VBA帮助中查找PivotField.Subtotals属性，将会看到可以使用索引号而不是数组。

The index number for Automatic is 1, so I can turn subtotals on with this line of code:

自动的索引号为1，因此我可以使用以下代码行来汇总小计：

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals(1) = True

Or, turn Automatic subtotals off by setting index 1 to False:

或者，通过将索引1设置为False来关闭“自动分类汇总”：

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals(1) = False

创建灵活的代码 (Create Flexible Code)

To make the code more flexible, I didn't want to name specific pivot fields – I wanted the code to turn off subtotals on all the fields. So, I created variables for pivot table and pivot field.

为了使代码更灵活，我不想命名特定的透视字段-我希望代码关闭所有字段的小计。 因此，我为数据透视表和数据透视表字段创建了变量。

The code loops through all the pivot tables on the active sheet. In each pivot table, the code loops through all the pivot fields, and sets the Subtotals(1) to Automatic (to turn off any other Subtotals), then sets Subtotals(1) to False, to turn subtotals off.

该代码循环遍历活动工作表上的所有数据透视表。 在每个数据透视表中，代码循环遍历所有数据透视表字段，并将小计(1)设置为自动(以关闭任何其他小计)，然后将小计(1)设置为False，以关闭小计。

NOTE: To save time in a large pivot table, you could change ".PivotFields" to ".RowFields" or ".ColumnFields"

注意：为了节省大型透视表中的时间，可以将“ .PivotFields”更改为“ .RowFields”或“ .ColumnFields”

Sub NoSubtotals()
'pivot table tutorial by contextures.com
'turns off subtotals in pivot table

Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
  pt.ManualUpdate = True
  For Each pf In pt.PivotFields
    'First, set index 1 (Automatic) to True,
    'so all other values are set to False
    pf.Subtotals(1) = True
    pf.Subtotals(1) = False
  Next pf
  pt.ManualUpdate = False
Next pt

End Sub

下载样本文件 (Download the Sample File)

To test the NoSubtotals code, you can download the sample file from the Show and Hide Pivot Table Items page on my Contextures website. Use the data from the FoodSales sheet.

若要测试NoSubtotals代码，可以从Contextures网站上的“显示和隐藏数据透视表项”页面下载示例文件。 使用FoodSales工作表中的数据。

Paste the NoSubtotals sample code onto a regular module, and save the workbook as macro enabled.

将NoSubtotals示例代码粘贴到常规模块上，然后将工作簿保存为启用宏。

_______________

_______________

翻译自: https://contexturesblog.com/archives/2015/02/12/hide-pivot-table-subtotals/

数据透视表和数据交叉表