英文:
Strange issue in Excel. Different results after 10th decimal place when multiplying same numbers in the same excel sheet
问题
Strange issue in Excel. 在同一个 Excel 表格中，相同的数字相乘后，小数点第十位之后出现不同的结果。已附上 Excel 表格截图和 Excel 表格链接。为什么会出现这种差异。
Can you please help. Thanks.
英文:
Strange issue in Excel. Different results after 10th decimal place when multiplying same numbers in the same excel sheet. Attached is the excel sheet screenshot and also the link to the excel sheet itself. Why the difference.
Can you please help. Thanks
答案1
得分: 3
以下是翻译好的部分：
差异的原因是列H中的两个数字（153095.044...）实际上并不相同。
这里有两个关键要理解的事情：
- Excel最多显示15个有效数字。（四舍五入取决于数字的来源；用户输入的数字只是被截断，也就是第16位和之后的有效数字显示为零。）
- Excel最多使用17个有效数字来存储数字。
由此得出的一个推论是，两个数字在原始的Excel文件（以及Excel的内存中）可能是不同的，但在工作表中以最大显示精度显示时会看起来相同。
这正是发生在你的情况中的情况。查看原始的XML，这两个数字如下（省略了一些中间元素）：
<row r="26" ... >
<c r="H26" s="4">
<v>153095.04486992653</v>
</c>
</row>
<row r="27" ...>
<c r="H27" s="6">
<v>153095.04486992667</v>
</c>
</row>
你可以清楚地看到这两个数字（包括17位数字）是不同的。这就是为什么在列I中的乘法会产生不同结果的原因。然而，对于15位有效数字来说，这些数字是相同的，这就是为什么它们在工作表的列H中显示为相同的原因。
你可以在这里阅读更多关于Excel中数字精度的信息，但这篇文章不涵盖上述第二点。关于这一点，你可以阅读这个答案。
如果将来第二个链接消失了，我将引用其中的关键部分：
与大多数在线文档（包括微软的文档）相反，数字并没有“存储为15个有效的十进制数字位数”。
相反，数字在内存中以二进制形式表示，称为64位二进制浮点数。由于大多数十进制小数（例如0.999）不能在二进制形式中精确表示，它们是由连续53个2的幂的和来近似表示的。
我们在Excel中只看到最多15位有效数字，是因为Excel会将数字的显示和格式化任意限制到那么多位数字。
但事实上，64位二进制浮点数标准规定，为了在十进制和二进制表示之间进行精确的转换，需要17位有效数字，四舍五入。
因此，Excel通常将最多17位有效数字写入XML文件中。
如果你需要更高的精度，那么不幸的是，Excel可能不适合你的用例。
英文:
The reason for the discrepancy is that the two numbers in column H (153095.044...) are not really the same.
There are two key things to understand here:
- Excel displays numbers using a maximum of 15 significant figures. (Rounding depends on the number's source; user-entered numbers are simply truncated, meaning that the 16th and subsequent significant figures are displayed as zeros.)
- Excel stores numbers using up to 17 significant figures.
A corollary of these is that two numbers may be different in a raw Excel file (and Excel's memory) but appear identical when displayed in a worksheet, even at maximum display precision.
This is exactly what is happening in your case. Looking at the raw XML, the two numbers are as follows (with some intermediate elements omitted):
<row r="26" ... >
<c r="H26" s="4">
<v>153095.04486992653</v>
</c>
</row>
<row r="27" ...>
<c r="H27" s="6">
<v>153095.04486992667</v>
</c>
</row>
You can clearly see that these two numbers (comprising 17 digits) are different. This is why your multiplications in column I produce different results. However, to 15 sig fig the numbers are identical, which is why they display identically in column H in your worksheet.
You can read more about numeric precision in Excel here, but this article doesn't cover point #2 above. For that, you could read this answer.
In case this second link disappears in future, I will quote the key parts:
> Contrary to most online documentation (including MSFT's), numbers are
> not "stored with 15 significant decimal digits of precision".
>
> Instead, numbers are represented in a binary form in memory, called
> 64-bit binary floating-point. And since most decimal fractions (e.g.
> 0.999) cannot be represented exactly in that binary form, they are approximated by the sum of 53 consecutive powers of 2.
>
> The reason that we see only up to 15 significant digits in Excel is
> because Excel arbitrary limits the display and formatting of numbers
> to that many digits.
>
> But in fact, the 64BFP standard states that it requires 17 significant
> digits, rounded, in order to convert between all decimal and binary
> representations with no loss of precision.
>
> So, Excel usually writes up to 17 significant digits into XML files.
If you require greater precision then unfortunately Excel may not be suitable for your use case.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论