问题描述
我不确定我做错了什么(可能是)还是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).
预先感谢
推荐答案
这是带有浮点计算的正常行为，请参见 https://support.microsoft.com/en-us/kb/78113 和 https://support.microsoft.com/en-us/kb/214118 ，但是它很丑陋，因为它在数据透视表和普通excel工作表中的行为有所不同.
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"，请参见 https://support.office.com/zh-CN/article/Calculate-values-in-a-PivotTable-report-697406b6-ee20-4a39-acea- 8128b5e904b8#bmcreate_a_formula ，具有公式:
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总和中的正数和负数计算错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！