本文介绍了在Excel中使用数据模型时遇到的一个数据透视表刷新错误，错误涉及函数PCMinorObjectCollection。问题源于更改源数据表标题大小写。通过检查数据透视表字段列表，发现更改的字段被重复列出。解决方法是将标题改回原始大小写并刷新数据透视表，或者在更改标题大小写时使用两步过程以避免错误。
数据透视表怎么刷新数据
A simple data change can cause a strange pivot table refresh error, if you added the data to the Data Model. The problem is easy to fix, once you see what's causing it, but the error message, shown below, isn't much help! I'd never heard of function PCMinorObjectCollection, had you?
如果将数据添加到数据模型中，则简单的数据更改可能会导致奇怪的数据透视表刷新错误。 一旦发现问题的根源，就很容易解决该问题，但是下面显示的错误消息并没有多大帮助！ 我从未听说过函数PCMinorObjectCollection，对吗？
奇怪的错误信息 (Strange Error Message)
I learned about this problem from UniMord, who explained how to reproduce the error in a few simple steps. Hopefully, you'll never run into this problem, but thanks to UniMord for sharing his information, in case you need it!
我从UniMord了解了这个问题，他通过几个简单的步骤解释了如何重现该错误。 希望您永远不会遇到这个问题，但是感谢UniMord在需要时分享他的信息！
Here's an unreadable screen shot of the strange error – it's one of those really wide messages, and included references that I didn't recognize, such as function PCMinorObjectCollection. What is that?
这是一个奇怪的错误，它的屏幕截图不可读-这是真正的错误消息之一，其中包括我不认识的引用，例如函数PCMinorObjectCollection。 那是什么？
Here's an edited version of that message, with some of the text moved, so you can read all of it.
这是该消息的编辑版本，其中一些文本已移动，因此您可以阅读所有消息。
And here's a text version of the message, in case anyone is searching for help with this error message:
这是该消息的文本版本，以防有人在寻求有关此错误消息的帮助：
- We couldn't get data from the Data Model. Here's the error message we got: 我们无法从数据模型中获取数据。 这是我们收到的错误消息：
- An unexpected error occurred (file 'pcminorobjcoll.inl', line 109, function PCMinorObjectCollection < class PCProperty, class NameHashSupport > ::SetNameAndUpdateCollection') 发生意外错误(文件'pcminorobjcoll.inl'，行109，函数PCMinorObjectCollection <类PCProperty，类NameHashSupport> :: SetNameAndUpdateCollection')
是什么导致错误？ (What Caused the Error?)
Fortunately, UniMord didn't just send me a problem, and reproduction steps – he had figured out what caused the error, and how to fix it.
幸运的是，UniMord不仅给我发送了一个问题，而且给了我更多的解决方法–他已经弄清楚了导致该错误的原因以及解决方法。
UniMord had done one simple thing that caused the problem – he changed one of the source data headings from upper case to proper case. That's it! Making that one simple change apparently caused one of the Data Model circuits to overheat, or something, and it had a little meltdown.
UniMord做了一件简单的事情就引起了问题-他将源数据标题之一从大写改为了大写。 而已！ 进行一次简单的更改显然会导致数据模型电路之一过热或发生某种情况，并且电路融化了一点。
Or, to misquote HAL, the computer in 2001: A Space Odyssey, "I'm sorry, UniMord. I'm afraid I can't do that."
或者， 错误地引用 2001年的HAL电脑 ：《太空漫游》，“对不起，UniMord。恐怕我做不到。”
如何重现问题 (How to Reproduce the Problem)
If you'd like to reproduce this pivot table refresh error, with Data Model data, you can follow these steps.
如果您想使用数据模型数据重现此数据透视表刷新错误，则可以按照以下步骤操作。
- Create a simple list on a worksheet, like the one shown below. It has 3 columns – Num, Month, DAY 在工作表上创建一个简单的列表，如下图所示。 它具有3列-Num，Month，DAY
Format the list as a named Excel table.
将列表格式化为一个命名的Excel表 。
Create a pivot table from the table, and check the box to Add to Data Model
从表中创建数据透视表，然后选中“ 添加到数据模型 ”框
- Add a couple of fields to the pivot table layout – I put DAY in the Rows area, and Num in the Values area, as Sum of Num 在数据透视表布局中添加几个字段–我将DAY放在Rows区域中，将Num放在Values区域中，作为Num之和
进行零钱 (Make a Small Change)
Next, you'll make a small change that causes the error when refreshing.
接下来，您将做一个小的更改，刷新时会导致错误。
- In the named table, change the case of one of the headings – I changed DAY to Day 在命名表中，更改标题之一的大小写–我将DAY更改为Day
- Right-click on a pivot table cell, and refresh the pivot table 右键单击数据透视表单元格，然后刷新数据透视表
- The first refresh should be OK 第一次刷新应该可以
- Next, refresh the pivot table again 接下来，再次刷新数据透视表
- This time you should see the error message that mentions the function, PCMinorObjectCollection 这次，您应该看到提到该函数PCMinorObjectCollection的错误消息
检查数据透视表字段列表 (Check the PivotTable Field List)
That error message is really confusing, and it doesn't give you any clues as to what is wrong, or needs to be fixed.
该错误消息确实令人迷惑，并且它不给您任何有关错误或需要修复的线索。
While troubleshooting the error, UniMord noticed that the changed field was listed twice in the PivotTable Field List.
在对错误进行故障排除时，UniMord注意到在“数据透视表字段列表”中两次列出了更改的字段。
- the old name (DAY) is still there 旧名称(DAY)仍然存在
- the new name is also listed, with a number added (Day 1) 新名称也会列出，并增加一个数字(第1天)
解决问题 (Fix the Problem)
There might be other ways to fix this problem, but here's what I did.
可能还有其他方法可以解决此问题，但这是我所做的。
- In the source data table, change the heading back to its original case – I changed Day back to DAY 在源数据表中，将标题改回其原始大小写–我将Day改回DAY
- Then, refresh the pivot table 然后，刷新数据透视表
- The extra field should disappear from the PivotTable Field List 多余的字段应从数据透视表字段列表中消失
正确更改标题大小写 (Change a Heading Case With No Error)
Next, you'll change the heading case again, but in a two-step process, to avoid getting an error message.
接下来，您将再次更改标题的大小写，但是要分两个步骤进行，以避免收到错误消息。
NOTE: Use this method if you need to make any future "case" changes to the data headings.
注意 ：如果您以后需要对数据标题进行任何“大小写”更改，请使用此方法。
- In the source data table, change the heading case – I changed DAY to Day 在源数据表中，更改标题大小写–我将DAY更改为Day
- On the Excel Ribbon's Data tab, click the Manage Data Model command 在“ Excel功能区”的“数据”选项卡上，单击“管理数据模型”命令
- In the Power Pivot for Excel window, on the Home tab, click the Design View command 在Power Pivot for Excel窗口的“主页”选项卡上，单击“设计视图”命令
- In the diagram, right-click on the field name that you changed, and click Rename 在图中，右键单击您更改的字段名称，然后单击“重命名”。
- Type the field name, the way that you entered it in the source data table – I changed DAY to Day 输入字段名称，即您在源数据表中输入字段的方式–我将DAY更改为Day
If the Excel worksheet is visible in the background, you might see an alert at the top of the sheet:
如果Excel工作表在后台可见，则您可能会在工作表顶部看到警报：
- DATA MODEL CHANGED Select the workbook to get these changes 数据模型已更改选择工作簿以进行这些更改
- Close the Power Pivot Window, and the revised field name should appear in the pivot table, and in the PivotTable Field List. 关闭“ Power Pivot窗口”，修改后的字段名称应出现在数据透视表和“ PivotTable字段列表”中。
- Refresh the pivot table, and no error message should appear. 刷新数据透视表，并且不会出现错误消息。
更多数据透视表信息 (More Pivot Table Info)
For more pivot table information, you can go to these pages on my Contextures website.
有关更多数据透视表的信息，您可以转到我的Contextures网站上的这些页面。
翻译自: https://contexturesblog.com/archives/2020/03/26/excel-pivot-table-refresh-error-with-data-model/
数据透视表怎么刷新数据
扫一扫
1240
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
