为什么VBA和Excel在判断两个单元格是否相等时会出现分歧？

Question

为什么VBA和Excel在判断两个单元格是否相等时会出现分歧？

6

我正在尝试比较表格中的两个单元格：

列“MR”是使用公式=ABS([@Value]-A1)计算的，以确定列“Value”的移动范围。 “Value”列中的值未四舍五入。 “MR”列中突出显示的单元格（B3和B4）相等。我可以在单元格中输入公式=B3=B4，Excel会说B3等于B4。

但是当我在VBA中进行比较时，VBA会说B4大于B3。我可以选择单元格B3并将以下内容输入即时窗口? selection.value = selection.offset(1).value。该语句计算结果为false。

我尝试从公式中删除绝对值，以为这可能与此有关，但VBA仍然表示它们不相等。

我尝试添加另一行，其中Value=1.78，因此MR=0.18。有趣的是，新行（B5）中的MR确实等于B3，但不等于B4。

我尝试将A4的小数位数增加以匹配其他值，现在VBA说它们是相等的。但是当我再次将绝对值添加到公式中时，VBA又说它们不相等。我再次删除了绝对值，现在VBA又说它们不相等。

为什么VBA告诉我这些单元格不相等，而Excel却说它们相等？如何通过VBA可靠地处理这种情况？

- Joku

4

VBA问题将由有限的浮点精度引起。Excel公式比VBA更好地解决此问题。 - chris neilsen

3

请参考此链接了解 Excel 与 VBA 在浮点数计算上的差异。 - chris neilsen

@chrisneilsen 尽管您提供的链接中的问题也是由浮点数引起的，但问题和答案的性质是不同的。更值得注意的是，链接问题中的答案并没有回答我的问题。虽然您在答案中提供的论文和评论中提供的文章都很有启发性，但都没有帮助我找到如何正确比较浮点数相等的答案。 - Joku

抱歉，但依我之见，“如何正确比较浮点数是否相等”的答案确实在链接的重复问题（实际上是在链接的论文）中，答案是“不要比较”。我看到您接受了Excel Hero的答案（这很好）。该答案中包含的所有内容也包含在Goldberg的论文中（Excel Hero也将您链接到了该论文）。Excel Hero提供的“解决方案”是“不要比较相等，而是比较接近相等”，并且正确地指出这不是一种适合所有情况的解决方案（Goldberg的论文中也提到了这一点）。 - chris neilsen

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Excel Hero · Accepted Answer

问题在于IEEE 754浮点运算标准本质上是不精确的，几乎所有编程语言都会因此受到影响。

IEEE 754是一个非常复杂的话题，当你研究它几个月并且认为你完全明白时，其实你只是在自欺欺人！准确的浮点数值比较是困难且容易出错的。在尝试比较浮点数之前，请仔细思考！

Excel程序通过在应用程序侧进行欺骗来解决这个问题。另一方面，VBA忠实地遵循IEEE 754规范双精度(binary64)。

双精度值使用64位内存表示。这64位被分成三个不同的字段，用于二进制科学计数法：

1.符号位（1位，表示值的正负） 2.指数（11位，由+1023偏置） 3.尾数（53位，52位存储+1位暗示）

在这个系统中，尾数利用了所有二进制数字以1开头的事实，因此1不存储在位模式中。它是暗示的，将尾数精度提高到53位。

数学计算如下：存储值=符号值*2^未偏置指数*尾数 请注意，符号位的存储值为1表示负符号值（-1），而0表示正符号值（+1）。公式为符号值=(-1)^(符号位)。

问题总是归结为同一件事。

大多数实数在这个系统中无法精确表示，这会引入小的舍入误差并像杂草一样传播。

将该系统视为一个间隔均匀的点网格可能有所帮助。该系统只能表示点值，不能表示点之间的任何实数。分配给浮点数的所有值都将舍入为点值之一（通常是最接近的点，但有些模式强制向上舍入到下一个最高点或向下舍入）。对浮点数值进行任何计算几乎肯定会导致结果值需要舍入。

显而易见，在这个网格中，相邻可表示点值之间有无限个实数；并且这些实数都被四舍五入到离散的网格点。

更糟糕的是，随着网格远离真正的零点（在两个方向上），每个二次幂处的间隙大小会加倍。例如，在范围为2到4的值之间的网格点之间的间隙长度是在范围为1到2的值之间的间隙长度的两倍。当表示具有足够大数量级的值时，网格间隙长度变得巨大，但靠近真正的零点时，它微不足道。

以下是您的示例数字...

以下二进制数表示 1.24:

符号位 = 0

指数 = 01111111111

尾数 = 0011110101110000101000111101011100001010001111010111

完整的64位十六进制模式为：3FF3D70A3D70A3D7。

精度仅来自53位尾数，从二进制到精确的十进制值为： 0.2399999999999999911182158029987476766109466552734375

在这种情况下，由于与尾数相关联的隐藏位，暗示了前导整数1，因此完整的十进制值为：

1.2399999999999999911182158029987476766109466552734375

现在请注意，这并不精确等于1.24，这就是整个问题所在。

让我们来看看1.42：

符号位 = 0

阶码 = 01111111111

尾数 = 0110101110000101000111101011100001010001111010111000

完整的64位十六进制模式为：3FF6B851EB851EB8。

加上隐含的1，完整的十进制数值被存储为：

1.4199999999999999289457264239899814128875732421875000

再次强调，不是精确的1.42。

现在，让我们来看一下1.6:

符号位=0

指数=01111111111

尾数=1001100110011001100110011001100110011001100110011010

完整的64位十六进制模式为：3FF999999999999A。

请注意，在这种情况下，截断并四舍五入了重复的二进制小数部分，当尾数位用完时？显然，以二进制base2表示的1.6永远不可能像以十进制base10表示的1/3那样精确（0.33333333333333333333333... ≠ 1/3）。

带有隐含的1，完整的十进制值存储为：

1.6000000000000000888178419700125232338905334472656250

不完全是 1.6，但比其他值更接近！

现在让我们减去完整的存储双精度表示：

1.60 - 1.42 = 0.18000000000000015987

1.42 - 1.24 = 0.17999999999999993782

因此，您可以看到它们根本不相等。

解决这个问题的通常方法是threshold testing，基本上是检查两个值是否足够接近...这取决于您和您的要求。请注意，有效的阈值测试比表面上看起来要难得多。

这是一个函数，可以帮助您开始比较两个双精度数字。它可以很好地处理许多情况，但并非所有情况都可以。

Function Roughly(a#, b#, Optional within# = 0.00001) As Boolean
    Dim d#, x#, y#, z#
    
    Const TINY# = 1.17549435E-38    'SINGLE_MIN
    
    If a = b Then Roughly = True: Exit Function
            
    x = Abs(a): y = Abs(b): d = Abs(a - b)
    
    If a <> 0# Then
        If b <> 0# Then
            z = x + y
            If z > TINY Then
                Roughly = d / z < within
                Exit Function
            End If
        End If
    End If
    
    Roughly = d < within * TINY
End Function

这里的思路是，如果两个Double在一定范围内大致相同，则函数返回True：

MsgBox Roughly(3.14159, 3.141591)           '<---dispays True

Within margin默认值为0.00001，但您可以传递所需的任何余量。

虽然我们知道：

MsgBox 1.60 - 1.42 = 1.42 - 1.24            '<---dispays False

考虑一下它的实用性：

MsgBox Roughly(1.60 - 1.42, 1.42 - 1.24)    '<---dispays True

@chris neilsen分享了一个有关Excel和IEEE 754的有趣Microsoft页面。

请阅读David Goldberg的开创性著作计算机科学家应该了解的浮点运算。它改变了我对浮点数的理解。