遇到一个头疼的 Excel 精度问题

[messiah163]

遇到一个 Excel 数值精度问题，求大佬们解析

今天在处理 Excel 文件时发现了一个有趣的现象。看下面两张图：

1. Excel 界面显示的数据：

A 列	B 列
4239.26	224789.77
4239.26	220550.51
128753.34	91797.17
90000	1797.17
1797.17	-1.63709E-11
4239.26	224789.77
4239.26	220550.51
128753.34	91797.17
91797.17	0

如图 1： [](图 1：减出来不等于 0)

如图 2: [](图 2:减出来等于 0 了)

1. 但当我查看这个 Excel 文件的 XML 源码时：

	<row r="2" spans="1:2" ht="18"><c r="A2" s="1"><v>4239.26</v></c><c r="B2" s="1"><v>224789.77</v></c></row>
	<row r="3" spans="1:2" ht="18"><c r="A3" s="1"><v>4239.26</v></c><c r="B3" s="1"><f>B2-A3</f><v>220550.50999999998</v></c></row>
	<row r="4" spans="1:2" ht="18"><c r="A4" s="1"><v>128753.34</v></c><c r="B4" s="1"><f>B3-A4</f><v>91797.169999999984</v></c></row>
	<row r="5" spans="1:2" ht="18"><c r="A5" s="1"><v>90000</v></c><c r="B5" s="1"><f>B4-A5</f><v>1797.1699999999837</v></c></row>
	<row r="6" spans="1:2" ht="18"><c r="A6" s="1"><v>1797.17</v></c><c r="B6" s="1"><f t="shared" ref="B6" si="0">B5-A6</f><v>-1.6370904631912708E-11</v></c></row>
	<row r="7" spans="1:2" ht="18"><c r="A7" s="1"/><c r="B7" s="1"/></row>
	<row r="8" spans="1:2" ht="18"><c r="A8" s="1"/><c r="B8" s="1"/></row>
	<row r="9" spans="1:2" ht="18"><c r="A9" s="1"><v>4239.26</v></c><c r="B9" s="1"><v>224789.77</v></c></row>
	<row r="10" spans="1:2" ht="18"><c r="A10" s="1"><v>4239.26</v></c><c r="B10" s="1"><f>B9-A10</f><v>220550.50999999998</v></c></row>
	<row r="11" spans="1:2" ht="18"><c r="A11" s="1"><v>128753.34</v></c><c r="B11" s="1"><f>B10-A11</f><v>91797.169999999984</v></c></row>
	<row r="12" spans="1:2" ht="18"><c r="A12" s="1"><v>91797.17</v></c><c r="B12" s="1"><f>B11-A12</f><v>0</v></c></row>
	<row r="13" spans="1:2" ht="18"><c r="A13" s="1"/><c r="B13" s="1"/></row>
	<row r="14" spans="1:2" ht="18"><c r="A14" s="1"/><c r="B14" s="1"/></row>
	<row r="15" spans="1:2" ht="18"><c r="A15" s="1"/><c r="B15" s="1"/></row>
	<row r="16" spans="1:2" ht="18"><c r="A16" s="1"/><c r="B16" s="1"/></row>

如图 3: [](图 3 、xml 细节)

问题在于：

1. Excel 界面上显示的数值看起来很"干净"，但 XML 中却带着一串额外的小数位
2. 特别是 91797.17 这个数，在 XML 中实际是 91797.169999999984
3. 最后的-1.63709E-11 ，在 XML 中是更精确的-1.6370904631012788E-11

这引发了几个问题：

1. Excel 为什么要在 XML 中保存这么高的精度？
2. 这些额外的精度是从哪里来的？是计算过程中产生的？
3. 为什么界面显示和 XML 存储的精度不一致？
4. 这种精度差异会在实际应用中造成什么影响？

欢迎各位大佬一起讨论。我个人猜测这可能跟 Excel 内部的数值表示方式有关，但具体细节还请懂行的来解释下。 Excel 文件： 文件链接： https://file.uhsea.com/2502/a971d24a402a2e08fd5873d3b0788ae925.xlsx

[Rickkkkkkk]

原因是，数字都是二进制存储，而十进制下有限的小数到了二进制下可能是无限循环的。

解法是：如果是钱，最好是用整数（分）来做加减乘除

[jr55475f112iz2tu]

我觉得你需要先告诉大家，这份 excel 是怎么得来的

如果它是某个软件/线上环境输出的结果，那可能原因需要从那个软件/线上环境去找，而不是从 excel 找
如果是其他场景，再分析

[RobertLyu]

我当时做工资表，就是两个人差一分钱，但是求和还对得上。直接 =ROUND() 函数。

后来发现这个函数也不够用，因为是 0.5 5 循环，就很麻烦，所以就手动把数字输进去了。

[tool2dx]

“在 XML 中实际是 91797.169999999984”

你如果写代码，会发现程序里铺天盖地都是这种浮点数表示法。

我一般保存时，都会处理成 91797.1699999999 + 0.0000000001 （基于 double 精度表示），这样一处理，小数点后面都是 0 了。

[hunterster]

这种情况下是否可以保存为字符串形式？

[messiah163]

@Rickkkkkkk 感谢答复，这个问题并不容易复现，我们大部分保留 2 位小数的加减函数都没有出现精度问题，如果您有兴趣可以看下这份 Excel 附件：文件链接： https://file.uhsea.com/2502/a971d24a402a2e08fd5873d3b0788ae925.xlsx
应该是别的原因导致的这种问题

[messiah163]

@czfy 感谢您的答复，这个文件是别人传输给我，我在上面添加数字的，每个月会添加一条 A 列开支，B 列是备用金结余，但是这个月发现不是 0 了，就很奇怪，如果您有兴趣的话，可以研究下这份附件：文件链接： https://file.uhsea.com/2502/a971d24a402a2e08fd5873d3b0788ae925.xlsx

[messiah163]

@RobertLyu 对，这个问题确实可以通过 round 掩饰过去，但是原因还是不清楚，我们打大部分表格都是正常的（应该是除了这份，其他目前都没有出现精度误差），我想这个肯定是有啥特别的原因导致的，说数字都是二进制存储，那别的正常精度的都是这么存储的，为啥一直是正常的，所以我比较困惑

[messiah163]

@tool2dx 感谢您的答复，这个 xml 的精度问题并不全部都是，我拆了几个 Excel 包都是正常精度的，目前，确实只有这个 xml 出现了异常，

[RobertLyu]

@messiah163 可以试试 =ROUND() 参数保留到小数点后 2 位试试？

或者就是老办法，直接吧数字输进去。

[tool2dx]

@messiah163 你在 v2 上面搜"浮点精度"，不是你一个人遇到，很多人都遇到过。

解决方案只能是 excel 保存时处理，你说 xml 大部分情况不会触发，这只是和浮点计算有关系。用代码计算的话，乘除很容易就会触发了。

excel 设计初衷应该不是单纯计算金额，对中间计算步骤的把控，没那么严格。

[messiah163]

@RobertLyu round 可以的，但是只是掩盖了这个问题，我拆包后，还是能看到很多小数点的

[messiah163]

@tool2dx 你可以把手头任意一个 excel 拆包，看看里面的数字，大部分都是正常的没有出现浮点（...99999 或...000001 这种），我发这个贴文是想知道为啥会触发这个浮点数，目前看下来大部分 Excel 都是正常的没有出现浮点问题。这个异常的原因还是没有定位到
拆包方法是，将 Excel 的后缀名.xlsx 改成 zip ，然后解压缩，找到 xl 目录下的 worksheets 目录里面会有具体的 sheet 的 xml 文件，这个就是 Excel 数据存储的文件

[tool2dx]

@messiah163 我试了一下，很容易就复现了。

<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="3" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="52.8"
ss:DefaultRowHeight="15.600000000000001">

[messiah163]

说明下：
1 、Excel 的拆包方法是，将 Excel 的后缀名.xlsx 改成 zip ，然后解压缩，找到 xl 目录下的 worksheets 目录里面会有具体的 sheet 的 xml 文件，这个就是 Excel 数据存储的文件
2 、OP 发这个贴的问题主要是希望能探究下这个数字变成浮点的原因，round 可以让结果看上去没问题，但是实际上只是掩盖了这个问题，能解决是有效的，但是绕开了发生的原因，我的目的是探究出现问题的原因
3 、OP 的数字是手动输入的，只有加减 B1-A2 后面都是直接拖拽填充的，没有乘除法运算
A 列
4239.26
4239.26
128753.34
91234.56
562.61

B 列第一个单元格 224789.77
第二个单元格=B1-A2 后面都是直接拖拽填充的

[messiah163]

@tool2dx 感谢答复，你这个是 excel 拆包的文件吗？我咋看着不像啊？大佬方便的话：VX 可以交流下 im_zmx