为什么Grand Total在Excel Pivot Table＃DIV / 0中！ （在此计算字段上除以零）？

Here is my data:

这是我的数据:

I created a pivot table from the data and created a calculated field called "% Error" defined like this:

我从数据创建了一个数据透视表,并创建了一个名为“%Error”的计算字段,定义如下:

I set the option to "Classic Pivot Table Layout" and dragged the values so that they would be next to the items instead of across the top as shown:

我将选项设置为“经典数据透视表布局”并拖动值,使它们位于项目旁边而不是顶部,如图所示:

The totals at the bottom for month 1, 2, and 3 are correct. The totals for each Item are correct. The grand total at the bottom shows the "% Error" with a divide by zero error. I am expecting it to be (40-30)/30=33.33%. If I ship any quantity of Item B in any month it calculates correctly. What do I have to do to get the correct value? Is this a feature or a bug?

第1,第2和第3个月底部的总数是正确的。每个项目的总数是正确的。底部的总计显示“%Error”,除以零错误。我期待它是(40-30)/30 = 33.33%。如果我在任何月份运送任何数量的物品B,它会正确计算。我需要做些什么才能获得正确的价值?这是一个功能还是一个bug?

1 个解决方案

#1

So I can tell you what the problem is. I tried, but I cannot find a fix for it.

所以我可以告诉你问题是什么。我试过,但我找不到它的修复方法。

Basically you are looking at the final, Grand Total for Total Sum of Percent Error as still having the calculated field in it, however this is not the case.

基本上你正在查看最终的总百分比误差百分比,因为它仍然有计算字段,但事实并非如此。

For Month's 1, 2, and 3, you have the calculated field formula that you inserted is taking the values in Total Sum of Forecast and Total Sum of Shipped and is applying the calculation on the values in the those two cells above it to give you the Total Sum of % Error.

对于月份的1,2和3,您插入的计算字段公式是采用预测总和和已发货总和中的值,并对其上方的两个单元格中的值应用计算,以便为您提供%误差总和。

What happens in the Grand Total column is that it is updated by more than one formula, your calculated field formula, and the Sum of the Grand Totals for the Sum of % Error for A and the Sum of % Errors for B. If you click on the List Formulas button, at least in Excel 2016, you get a new sheet that says this.

在Grand Total列中发生的情况是,它由多个公式,计算字段公式以及A的%误差总和和B的%误差总和的总计总和更新。如果单击在“列表公式”按钮上,至少在Excel 2016中,您将获得一个说明此内容的新工作表。

Calculated Field        
Solve Order             Field               Formula
       1               % Error              =(Forecast-Shipped)/Shipped

Calculated Item     
Solve Order              Item               Formula


Note:                    When a cell is updated by more than one formula,   
                         the value is set by the formula with the last solve order. 

                         To change formula solve orders,    
                         use the Solve Order command on the PivotTable command bar.

The only problem is that you do not get the option to change the Solve Order for this, so the sum of the fields in the Grand Total column override the calculation of (40-30)/30

唯一的问题是您没有为此更改Solve Order的选项,因此Grand Total列中的字段总和将覆盖(40-30)/ 30的计算

Your option is that in the Design Tab, you can select Grand Totals -> For Columns Only, which will give you the totals at the bottom and then manually put the formulas into the Grand Total Column that is now hidden. Sorry I can't come up with a more elegant solution.

您可以选择在“设计”选项卡中选择“总计” - >“仅用于列”,这将为您提供底部的总计,然后手动将公式放入现在隐藏的“总计”列中。对不起,我无法想出更优雅的解决方案。

If you are really ambitious about getting this to work with Pivot Tables, many years ago I used to create them through VBA and not using the built in GUI, and you get much more control using the objects and methods than you do with the commands that are exposed to you in the user interface. Unfortunately most all of the knowledge on how to do that has been lost to time for me.

如果你真的雄心勃勃地想要使用数据透视表,多年前我曾经通过VBA创建它们而不是使用内置的GUI,你使用对象和方法比使用命令更多地控制在用户界面中向您显示。不幸的是,大多数关于如何做到这一点的知识对我来说已经失去了时间。