数据透视表 字段交叉_删除数据透视表的计算字段的宏

数据透视表 字段交叉

Have you ever recorded a macro to remove pivot table calculated fields? Just turn on the recorder, right-click on the field and hide it, and turn off the recorder. Then, if you try to run that macro later, Kaboom! You get an error message, "Run-time error '1004': Unable to set the Orientation property of the PivotField class".

您是否曾经录制过宏以删除数据透视表计算字段？ 只需打开记录器，右键单击该字段并将其隐藏，然后关闭记录器即可。 然后，如果您稍后尝试运行该宏，那么Kaboom！ 您收到错误消息，“运行时错误'1004”：无法设置PivotField类的Orientation属性。

Good news – you can download my sample file that has a macro that actually removes those pesky calculated fields, without creating an error message. The video shows how it works.

好消息–您可以下载我的示例文件，其中包含一个宏，该宏实际上删除了那些讨厌的计算字段，而不会产生错误消息。 视频显示了它的工作原理。

删除计算字段 (Remove the Calculated Fields)

I ran into this old familiar problem on the weekend, while recording a demo video for my Pivot Power Premium add-in. One of the new features lets you select cells in multiple value fields, then click a button to remove them. It worked perfectly before, but wouldn't remove the values in this pivot table.

周末，我在为Pivot Power Premium加载项录制演示视频时遇到了这个熟悉的老问题。 其中一项新功能使您可以在多个值字段中选择单元格，然后单击按钮将其删除。 之前它工作得很好，但是不会删除此数据透视表中的值。

Ah, those were calculated fields, and change their orientation to xlHidden doesn't work. I had to change the code, so it would work on those fields too. And that's not as easy as you'd hope.

啊，这些是计算字段，将其方向更改为xlHidden不起作用。 我必须更改代码，因此它也可以在那些字段上工作。 这并不像您希望的那么容易。

删除并重新添加计算字段 (Delete and Re-Add Calculated Fields)

In the past, I used a macro that deleted each Calculated Field, then re-added it to the pivot table. That was okay if only one pivot table used the pivot cache. But, if you had multiple tables on that cache, it didn't put the calculated fields back in those other pivot tables. Oops!

过去，我使用一个宏来删除每个计算字段，然后将其重新添加到数据透视表中。 如果只有一个数据透视表使用该数据透视表缓存，那就可以了。 但是，如果您在该缓存上有多个表，则不会将计算出的字段放回那些其他数据透视表中。 糟糕！

The same code was on my pivot table blog, and in one of the comments there, Rory Archibald mentioned using the Visible property for an item in the Values field. He posted a sample that hid a specific field, and I finally adapted that code, so it hides all the calculated fields. Thanks Rory!

相同的代码在我的数据透视表博客上，并且在其中的注释之一中， Rory Archibald提到了在Values字段中为项目使用Visible属性。 他发布了一个隐藏特定字段的示例，最后我修改了该代码，因此它隐藏了所有计算出的字段。 谢谢罗里！

Except the last one, of course, if you don't have any non-calculated fields in the Values area. If that Values field disappears, the trick doesn't work.

当然，除了最后一个字段外，如果“值”区域中没有任何未计算的字段。 如果“值”字段消失，则该技巧无效。

视频：宏删除数据透视表的计算的字段 (Video: Macro to Remove Pivot Table Calculated Fields)

In this video, you can see the error that occurs when you try to remove a calculated field with a recorded macro. Then, see the code that actually works!

在此视频中，您可以看到尝试删除带有记录的宏的计算字段时发生的错误。 然后，查看实际有效的代码！

演示地址

下载样本文件和代码 (Download the Sample File and Code)

To follow along with the video, you can download the sample file with the macro to remove pivot table calculated fields. It's on the Pivot Table Calculated Fields page on my Contextures site.

要与视频一起播放，您可以下载带有宏的示例文件，以删除数据透视表的计算字段。 在我的Contextures网站上的“ 数据透视表计算字段”页面上。

The file is in xlsm format, and you'll have to enable macros when you open the file.

该文件为xlsm格式，打开文件时必须启用宏。

翻译自: https://contexturesblog.com/archives/2016/05/10/macro-to-remove-pivot-table-calculated-fields/

数据透视表 字段交叉