Excel 2016中的数据透视表小计问题

If you using grouping, you might run into a pivot table subtotal problem in Excel 2016. There was a change in a recent update, so you might see this problem if you have an Office 365 subscription. I just learned about this issue, and will show you how to fix the problem if it affects your workbooks.

如果使用分组，则可能会在Excel 2016中遇到数据透视表小计问题。最近的更新已更改，因此如果您具有Office 365订阅，则可能会看到此问题。 我刚刚了解了此问题，并将向您展示如何解决影响您的工作簿的问题。

枢轴项分组问题 (Pivot Item Grouping Problem)

After you create a pivot table, you can group the pivot items in one or more of the pivot fields. This feature has been available since the beginning of pivot tables, as far as I know. Learn more about pivot table grouping on my Contextures website.

创建数据透视表后，您可以将一个或多个数据透视表字段中的数据透视表项分组。 据我所知，此功能自数据透视表开始以来就可用。 在我的Contextures网站上了解有关数据透视表分组的更多信息 。

分组编号和日期 (Grouping Numbers and Dates)

If the pivot fields contains numbers or dates, use the Grouping dialog box to group them.

如果数据透视表字段包含数字或日期，请使用“分组”对话框对它们进行分组。

分组文本项 (Grouping Text Items)

You can group text items too, but you'll have to do that manually – the Grouping dialog box isn't available for text items.

您也可以对文本项进行分组，但是您必须手动执行–分组对话框不适用于文本项。

In the screen shot below, I selected Bars and Cookies, then right-clicked on Bars, and clicked Group.

在下面的屏幕截图中，我选择了“条形图和Cookie”，然后右键单击“条形图”，然后单击“组”。

Excel automatically created Group1, and Bars and Cookies are listed under that heading. Also, a new field was created – Category2.

Excel自动创建了Group1，并且在该标题下列出了Bars和Cookies。 另外，创建了一个新字段– Category2。

数据透视表分组更改 (Pivot Table Grouping Change)

Thanks to Ian Brown, from Onion Reporting Software Ltd, who let me know about a recent change to grouping. The new behaviour might not affect your work, but Ian's clients had macros crashing, because of the change. And nobody wants unhappy clients.

感谢Onion Reporting Software Ltd的 Ian Brown，他让我知道最近对分组的更改。 新行为可能不会影响您的工作，但是由于更改，Ian的客户的宏崩溃了。 没有人想要不满意的客户。

Fortunately, Ian was able to fix the macros quickly, once he discovered what had happened, and his clients were able to get back to work.

幸运的是，一旦Ian发现了发生的事情，他就能够快速修复宏，并且他的客户能够重新开始工作。

Be prepared, in case you get mysterious reports of macro problems – it might be the same issue.

要做好准备，以防万一收到有关宏问题的神秘报道–可能是同一问题。

分组项目小计 (Grouped Item Subtotals)

So, what changed?

那么，发生了什么变化？

In the standalone version of Excel 2016 that I use, the new groups do not show subtotals. If I check the Field Settings for the new field, Subtotals are set to None.

在我使用的独立版Excel 2016中，新组不显示小计。 如果我检查新字段的“字段设置”，则“小计”将设置为“无”。

However, in recent builds, the subscription version of Excel 2016 has changed this behaviour. Now, if you group items, the new field settings default to Automatic Subtotals.

但是，在最新版本中，Excel 2016的订阅版本已更改了此行为。 现在，如果您将项目分组，则新的字段设置默认为“自动分类汇总”。

修复数据透视表宏 (Fix Your Pivot Table Macros)

If you have macros that build pivot tables, and group some of the items, your pivot tables will look different now. The grouped items will have subtotals, where they didn't before. That could cause problems further along in your code.

如果您具有用于构建数据透视表并将部分项分组的宏，则数据透视表现在看起来会有所不同。 分组的项目将具有小计，而以前没有。 这可能会导致代码中出现更多问题。

To avoid the problem, you can add code that turns off the Automatic subtotals, after you group the pivot items.

为避免此问题，可以在对数据透视项进行分组之后添加关闭“自动”分类汇总的代码。

There is a detailed explanation in this blog post that I wrote a couple of years ago. In the "too long, didn't read" version, use a line of code to turn Subtotals off:

我几年前写的这篇博客文章中有详细的解释 。 在“太长，没看完”版本中，使用以下代码行关闭小计：

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

获取样本工作簿 (Get the Sample Workbook)

To see the full code, and download the sample file, go to the Show and Hide Excel Pivot Items page on my website.

要查看完整代码并下载示例文件，请转到我网站上的“显示和隐藏Excel Pivot项目”页面 。

The zipped file is in xlsm format, and enable macros when you open the file, if you want to test the code.

压缩文件为xlsm格式，如果要测试代码，请在打开文件时启用宏。

视频：将数据透视表中的文本项分组 (Video: Grouping Text Items in a Pivot Table)

Watch this video to see the steps for creating groups from text items. Then, rename a group, or add more items. Later, you can ungroup one or more of the groups.

观看此视频，以了解从文本项创建组的步骤。 然后，重命名组，或添加更多项目。 以后，您可以取消一个或多个组的分组。

This video was recorded in a version of Excel that does NOT automatically create subtotals.

该视频记录在不会自动创建小计的Excel版本中。

演示地址

翻译自: https://contexturesblog.com/archives/2017/02/23/pivot-table-subtotal-problem-in-excel-2016/