Microsoft Excel Pivot总和中的正数和负数计算错误 [英] Microsoft Excel Pivot miscalculation in Sum for positive and negative numbers

问题描述

我不确定我做错了什么(可能是)还是Microsoft Excel中有问题.

I am not sure whether I am doing something wrong (probably yes) or there is a problem in Microsoft Excel.

场景: 我创建一个如下表

Scenario: I create a table as below

+------+-------+
| Name | Value |
+------+-------+
| Alex | 1.55  |
| Alex | -1.56 |
| Alex | 0.01  |
+------+-------+

当我尝试获取枢轴中的Value之和时，结果如下:

When I try to get sum of Value in a pivot the result is as below:

+-------------+--------------+
| Row Labels  | Sum of Value |
| Alex        | -8.67362E-18 |
| (blank)     |              |
| Grand Total | -8.67362E-18 |
+-------------+--------------+

我希望[值的总和]显示0，但它显示的是非零值(-8.67362E-18).

I expect the [Sum of Value] to display 0 but it displays a non zero value(-8.67362E-18).

预先感谢

推荐答案

这是带有浮点计算的正常行为，请参见

While this is normal behavior with floating point calculation, see https://support.microsoft.com/en-us/kb/78113 and https://support.microsoft.com/en-us/kb/214118, it is ugly however since it behaves different in pivot tables and normal excel sheets.

表格:

B5中的公式是

= SUM(B2:B4)

=SUM(B2:B4)

如您所见，结果为0.

数据透视表:

如您所见，正常的计算总和为不0.并且不，数据透视表将不遵守显示的精度"设置.

As you see, normal calculated sum is not 0. And no, pivot tables will not respect setting "Precision as Displayed".

我做了什么?

添加了一个计算字段"Feld1"，请参见

Added a calculated field "Feld1", see https://support.office.com/en-us/article/Calculate-values-in-a-PivotTable-report-697406b6-ee20-4a39-acea-8128b5e904b8#bmcreate_a_formula, with formula:

= ROUND(值，8)

=ROUND(Value, 8)

并将其用于求和.

这篇关于Microsoft Excel Pivot总和中的正数和负数计算错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！