透视表二级菜单_清除数据透视表下拉菜单中的旧项目

透视表二级菜单

There is a page on the Contextures website that describes how to clear old items in pivot table drop downs. Someone ran into a problem with that code, and here's how we fixed it.

Contextures网站上有一个页面，描述如何清除数据透视表下拉列表中的旧项目 。 有人在使用该代码时遇到了问题，这是我们解决该问题的方法。

清除旧项目代码有问题 (Problem with Clear Old Items Code)

This week, Laurence emailed me about a problem he was having with those instructions to clear old items. Keep reading, to see how we solved the problem.

这周，劳伦斯(Laurence)向我发送了电子邮件，告知他在清理旧物品时遇到了问题。 继续阅读，看看我们如何解决问题。

数据透视表中的旧项目 (Old Items in the Pivot Table)

Those old items can appear if you change the pivot table source data – for example, you might remove a few obsolete products, or change a sales rep's name.

如果您更改数据透视表的源数据，这些旧项目可能会出现-例如，您可能会删除一些过时的产品或更改销售代表的名称。

When you refresh the pivot table, the new data can appear, but the old names still show up in the drop down lists, that you use for filtering.

刷新数据透视表时，可以显示新数据，但旧名称仍会显示在用于过滤的下拉列表中。

手动清除旧项目 (Manually Clear the Old Items)

One way to clear the old items is to do the steps manually:

清除旧项目的一种方法是手动执行以下步骤：

1. Remove pivot field(s) from the pivot table layout
   从数据透视表布局中删除数据域
2. Refresh the pivot table
   刷新数据透视表
3. Put the pivot field(s) back in the pivot table layout
   将数据透视表字段放回数据透视表布局中

更改数据透视表设置 (Change a Pivot Table Setting)

In Excel 2007 or Excel 2010, you can change a pivot table setting, to prevent old items from appearing.

在Excel 2007或Excel 2010中，可以更改数据透视表设置，以防止出现旧项目。

1. Right-click any cell in the pivot table, and click PivotTable options
   用鼠标右键单击数据透视表中的任何单元格，然后单击数据透视表选项
2. In the PivotTable Options dialog box, click the Data tab
   在“数据透视表选项”对话框中，单击“数据”选项卡
3. In the Retain Items section, select None from the drop down list.
   在“保留项目”部分中，从下拉列表中选择“无”。
4. Click OK, then refresh the pivot table.
   单击确定，然后刷新数据透视表。

使用宏清除旧项目 (Use a Macro to Clear Old Items)

If you're using Excel 2002 or Excel 2003, there isn't a Retain Items setting in the Options dialog box, but you can prevent old pivot items, with a bit of programming.

如果您使用的是Excel 2002或Excel 2003，则“选项”对话框中没有“保留项目”设置，但是您可以通过一些编程来防止旧的枢轴项目 。

Things weren't so easy, in earlier versions of Excel, so you'll need a longer piece of code to remove the old items from the pivot table. Laurence, who emailed me about the problem, is still using Excel 2000.

在早期版本的Excel中，事情并不是那么容易，所以您需要更长的代码才能从数据透视表中删除旧项目。 通过电子邮件向我发送有关该问题的劳伦斯仍在使用Excel 2000。

I'm sure he's not the only one, so here's the issue that he encountered, and the simple solution.

我确定他不是唯一的人，所以这是他遇到的问题以及简单的解决方案。

代码问题 (Problems With the Code)

The code loops through all the worksheets, and all the pivot tables on each sheet. Laurence has 3 pivot tables on one worksheet, and was running the code to clear old pivot items in Excel 2000 and Excel 97. However, the code was hanging, and stopping somewhere in the second pivot table update.

该代码循环遍历所有工作表以及每张工作表上的所有数据透视表。 Laurence在一个工作表上有3个数据透视表，并正在运行代码以清除Excel 2000和Excel 97中的旧数据透视表项 。 但是，代码已挂起，并在第二个数据透视表更新的某处停止。

He uploaded his sample file, in case I wanted to take a look at it, so I stepped through the code to see what was happening. The pivot data source wasn't too big, and the pivot tables only had a few fields, but Excel seemed to go into overload in the second pivot table.

他上载了示例文件，以防万一我想看一下它，因此我逐步查看了代码以了解发生了什么。 数据透视表数据源不是很大，数据透视表只有几个字段，但是Excel似乎在第二个数据透视表中处于重载状态。

At the end of each pivot table loop, there were 2 lines:

在每个数据透视表循环的末尾，有2行：

    pt.ManualUpdate = False
    pt.RefreshTable

That's a belt and suspenders approach, because the pivot table should refresh when ManualUpdate is set to False.

这是一条皮带和吊带的方法，因为当ManualUpdate设置为False时，数据透视表应该刷新。

删除一行代码 (Remove a Line of Code)

In one of my tests, I removed the pt.RefreshTable line, and the code ran smoothly. It removed all the old items, and the pivot tables all refreshed, without the additional line of code.

在我的测试之一中，我删除了pt.RefreshTable行，并且代码运行顺利。 它删除了所有旧项目，并且刷新了数据透视表，而没有附加的代码行。

I was using Excel 2010, and Laurence confirmed that the same solution worked in his file, using Excel 2000. I hope that helps you, if you ever encounter a similar pivot table update problem.

我正在使用Excel 2010，并且Laurence确认使用Excel 2000可以在他的文件中使用相同的解决方案。如果您遇到类似的数据透视表更新问题，希望对您有所帮助。

I've updated the code sample on the Contextures website, to alert future readers. _________________

我已经在Contextures网站上更新了代码示例，以提醒未来的读者。 _________________

翻译自: https://contexturesblog.com/archives/2011/04/22/clear-old-items-in-pivot-table-drop-downs/

透视表二级菜单