Pivot总和中的正数和负数计算错误

Pivot总和中的正数和负数计算错误

本文介绍了Microsoft Excel Pivot总和中的正数和负数计算错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我不确定我做错了什么(可能是)还是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总和中的正数和负数计算错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-21 07:55