当前位置：首页 > news >正文

mysql 与 sqlite 数学运算精度问题

news 来源：原创 2025/9/14 5:04:52

在 Excel 中，浮点运算得到的结果可能不准确

https://learn.microsoft.com/zh-cn/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

本文讨论 Microsoft Excel 如何存储和计算浮点数。由于存在舍入或数据截断，这可能会影响某些数字或公式的结果。

Microsoft Excel 围绕 IEEE 754 规范进行设计，以确定它如何存储和计算浮点数。 IEEE 是电气与电子工程师协会，一个确定计算机软件和硬件标准的国际机构。 754 规范是一种广为采用的规范，用于描述如何在二进制计算机中存储浮点数。它很受欢迎，因为它允许浮点数存储在合理的空间和计算速度相对较快。 754 标准用于当今几乎所有实现浮点数学的基于 PC 的微处理器的浮点运算单元和数字数据处理器，包括 Intel、Motorola、Sun 和 MIPS 处理器。

存储数字时，对应的二进制数可以表示每个数字或小数。例如，分数 1/10 在十进制记数制中可以表示为 0.1。但是，以二进制格式的相同数字将成为以下重复的二进制小数：

0001100110011100110011（等）

此操作可以无限重复。此数字不能以有限的（有限）空间量表示。因此，存储此数字时，此数字按大约 -2.8E-17 舍入。

但是，IEEE 754 规范有一些限制，可分为三大类：

最大/最小限制
Precision
重复二进制数
更多信息
最大/最小限制
所有计算机都有可处理的最大值和最小数。由于存储该数字的内存位数是有限的，因此可以存储的最大或最小数也是有限的。对于 Excel，可以存储的最大数为 1.79769313486232E+308，可以存储的最小正数为 2.2250738585072E-308。

我们遵循 IEEE 754 的案例
下溢：当生成的数字太小，无法表示时会发生下溢。在 IEEE 和 Excel 中，结果为 0（除了 IEEE 的概念为 -0，Excel 没有）。
溢出：当数字太大无法表示时会发生溢出。 Excel 针对这种情况使用自己的特殊表示形式 (#NUM!)。
我们不符合 IEEE 754 的情况
非规范化数字：非规范化数字由指数 0 表示。在这种情况下，整个数字存储在尾数中，而尾数没有隐式前导 1。如此一来，你损失了精度，而数字越小，损失的精度就越多。此范围小端的数字精度只有一位数。

示例：规范化数字具有隐式前导 1。例如，如果尾数表示 0011001，则由于隐式前导 1，规范化数字将变为 10011001。非规范化数字没有隐式前导数字，因此在我们的0011001示例中，非规范化数字保持不变。在这种情况下，规范化数有八个有效数字 (10011001)，而非规范化数有五个有效数字 (11001)，前导 0 无效。

非规范化数基本上是一种允许存储小于正常下限的数字的解决方法。 Microsoft不实现此规范的可选部分，因为非规范化数字具有可变数量的有效数字。这可能导致计算中出现重大错误。

正/负无穷大：除以 0 时出现无穷大。 Excel 不支持无限，而是提供 #DIV/0！这些案例中的错误。

非数字 (NaN) ：NaN 用于表示无效运算（如无穷大/无穷大、无穷大-无穷大或 -1 的平方根）。 NaN 允许程序跳过无效运算继续运行。 Excel 会立即生成错误，例如 #NUM! 或 #DIV/0!。

Precision
浮点数以二进制形式存储在 65 位范围内的三个部分：符号、指数和尾数。

符号指数尾数
1 个符号位 11 位指数 1 个隐式位 + 52 位小数
符号存储数字（正或负）的符号，指数存储数字的 2 次升幂或降幂（2 的最大/最小幂分别为 +1,023 和 -1,022) ，而尾数存储实际数字。尾数的有限存储区域限制两个相邻浮点数的接近程度（即精度）。

尾数和指数都存储为单独的组件。因此，精度可能会有所不同，具体取决于所操作的数字（尾数）的大小。就 Excel 而言，尽管它可以存储从 1.79769313486232E308 到 2.2250738585072E-308 之间的数字，但存储精度只能达到 15 位数。此限制是严格遵循 IEEE 754 规范的直接结果，不是 Excel 的限制。在其他电子表格程序中也可以找到此精度级别。

浮点数以以下形式表示，其中指数是二进制指数：

X = 小数 * 2^（指数 - 偏量）

小数是数字的规范化小数部分，由于对指数进行调整以便前导位始终为 1，因此进行了规范化。这样一来，它就不必存储了，你又得到一位精度。这就是为什么存在隐含位的原因。这类似于科学记数法，其中操作指数使小数点左侧有一位数；除二进制外，始终可以操作指数，使第一位为 1，因为只有 1 和 0。

偏量是用于避免必须存储负指数的偏量值。单精度数字的偏量为 127，双精度数字的偏量为 1,023（十进制）。 Excel 采用双精度存储数字。

使用非常大数的示例
将以下内容输入到新工作簿中：

adoc

复制
A1: 1.2E+200
B1: 1E+100
C1: =A1+B1
单元格 C1 的结果值为 1.2E+200，与单元格 A1 的值相同。事实上，如果使用 IF 函数比较单元格 A1 和 C1，例如 IF(A1=C1)，则结果将为 TRUE。这是由 IEEE 规范仅存储 15 个有效精度数字导致的。为了能够存储上述计算，Excel 的精度至少需要达到 100 位数。

使用非常小数的示例
将以下内容输入到新工作簿中：

adoc

复制
A1: 0.000123456789012345
B1: 1
C1: =A1+B1
单元格 C1 的结果值为 1.00012345678901，而不是 1.000123456789012345。这是由 IEEE 规范仅存储 15 个有效精度数字导致的。为了能够存储上述计算，Excel 至少需要 19 位数的精度。

更正精度错误
Excel 提供两种补偿舍入误差的基本方法：ROUND 函数和以显示精度为准或将精度设为所显示的精度工作簿选项。

方法 1：ROUND 函数
以下示例使用上一数据，使用 ROUND 函数强制将数字分为五位数。这样，就能够成功将结果与另一个值进行比较。

adoc

复制
A1: 1.2E+200
B1: 1E+100
C1: =ROUND(A1+B1,5)
结果是 1.2E+200。

D1: =IF(C1=1.2E+200, TRUE, FALSE)

其结果值为 TRUE。

方法 2：以显示精度为准
在某些情况下，可以使用“以显示精度为准”选项防止舍入误差影响你的工作。此选项强制工作表中每个数字的值为显示值。要打开此选项，请按照下列步骤操作：

在“文件”菜单上，单击“选项”，然后单击“高级”类别。
在“计算此工作簿时”部分，选择需要的工作簿，然后选中“将精度设为所显示的精度”复选框。
例如，如果选择显示两位小数位的数字格式，然后打开“以显示精度为准”选项，则保存工作簿时，所有超出两位小数位的精度将丢失。此选项会影响活动工作簿，包括所有工作表。无法撤消此选项并恢复丢失的数据。建议在启用此选项之前保存工作簿。

重复结果接近于零的二进制数和计算
影响以二进制格式存储浮点数的另一个令人困惑的问题是，某些数字在十进制基 10 中是无限的、重复的二进制数字。最常见的示例是值 0.1 及其变体。尽管这些数字可以完全以 10 为底表示，但二进制格式的相同数字在存储在 mantissa 中时会变为以下重复二进制数：

000110011001100110011（等等）

IEEE 754 规范对任意数字都没有任何特殊限制。它将可存储的内容存储在尾数中，并截断其余部分。这会导致存储时出现大约 -2.8E-17 或 0.0000000000000028 的错误。

即使是常见的十进制分数（如十进制 0.0001）也不能完全以二进制形式表示。（0.0001 是重复的二进制小数，周期为 104 位）。这类似于为什么分数 1/3 不能完全用小数表示（重复 0.33333333333333333333333333333333）的原因。

例如，请考虑Microsoft Visual Basic for Applications 中的以下示例：

复制
Sub Main()
MySum = 0
For I% = 1 To 10000
MySum = MySum + 0.0001
Next I%
Debug.Print MySum
End Sub
这将打印 0.999999999999996 作为输出。以二进制表示 0.0001 的小误差将传播到总和。

示例：添加负数
将以下内容输入到新工作簿中：

A1: =(43.1-43.2)+1

右键单击“单元格 A1”，然后单击“设置单元格格式”。在“数字”选项卡上，单击“类别”下的“科学记数”。将“小数位数”设置为 15。

Excel 不显示 0.9，而是显示 0.899999999999999。由于先计算 (43.1-43.2)，因此将临时存储 -0.1，并且存储 -0.1 带来的误差将引入计算。

值达到零时的示例
在 Excel 95 或更早版本中，将以下内容输入到新建工作簿中：

A1: =1.333+1.225-1.333-1.225

右键单击“单元格 A1”，然后单击“设置单元格格式”。在“数字”选项卡上，单击“类别”下的“科学记数”。将“小数位数”设置为 15。

Excel 95 不显示 0，而是显示 -2.22044604925031E-16。

然而，Excel 97 推出了一种尝试更正此问题的优化措施。如果加减运算的结果等于或非常接近零，Excel 97 及更高版本将补偿因操作数与二进制相互转换而带来的任何误差。以上在 Excel 97 及更高版本中执行的示例以科学记数法正确显示 0 或 0.000000000000000E+00。

有关浮点数和 IEEE 754 规范的详细信息，请参阅以下万维网网站：

https://www.ieee.org
https://steve.hollasch.net/cgindex/coding/ieeefloat.htmlb

相关文章：