在VBA中比较双精度浮点数的精度问题比较

Question

在VBA中比较双精度浮点数的精度问题比较

15

我在Excel VBA中比较两个双精度浮点数时遇到了问题。

假设我有以下代码：

Dim a as double
Dim b as double
a = 0.15
b = 0.01

对 b 进行几次操作后，b 现在等于 0.6。

然而，由于双精度数据类型的不精确性，让我感到头痛，因为

if a = b then
 //this will never trigger
end if

你知道如何去掉 double 类型的尾数不精确性吗？

- Eric

10个回答

9

如果你打算这样做……

（关于IT技术的具体内容，需要您提供）

Dim a as double  
 Dim b as double  
 a = 0.15  
 b = 0.01

你需要在IF语句中添加 round 函数，像这样...

  If Round(a,2) = Round(b,2) Then   
     //code inside block will now trigger.
  End If

同时也可以参考Microsoft的其他参考资料。

- Anonymous Type

5

这是我写的一个简单函数:

Function dblCheckTheSame(number1 As Double, number2 As Double, Optional Digits As Integer = 12) As Boolean

If (number1 - number2) ^ 2 < (10 ^ -Digits) ^ 2 Then
    dblCheckTheSame = True
Else
    dblCheckTheSame = False
End If

End Function

使用以下命令进行调用：

MsgBox dblCheckTheSame(1.2345, 1.23456789)
MsgBox dblCheckTheSame(1.2345, 1.23456789, 4)
MsgBox dblCheckTheSame(1.2345678900001, 1.2345678900002)
MsgBox dblCheckTheSame(1.2345678900001, 1.2345678900002, 14)

- user3706920

5

比较浮点数的相等性并不明智。

一些十进制值映射到多个浮点表示。因此，一个0.6并不总是等于另一个0.6。

如果我们将一个减去另一个，可能会得到类似于0.00000000051的结果。

现在我们可以将相等定义为具有小于某个误差范围的差异。

- Gamecat

3

晚回答，但我很惊讶竟然还没有发布解决方案来解决文章中提到的问题（目前被接受的答案），即：

四舍五入检查绝对容差（例如，如果保留4位小数，则为0.0001单位），这在比较多个数量级上的不同值时是无用的（因此不仅仅是与0比较）
相对容差与被比较的数字之一成比例，目前的答案中没有提到，但在非零比较中表现良好（但在与零比较时会出现放大问题）。

为了解决这个问题，我从Python中获得了灵感：PEP 485 -- A Function for testing approximate equality，并在一个标准模块中实现了以下代码。

'@NoIndent: Don't want to lose our description annotations
'@Folder("Tests.Utils")

Option Explicit
Option Private Module

'Based on Python's math.isclose https://github.com/python/cpython/blob/17f94e28882e1e2b331ace93f42e8615383dee59/Modules/mathmodule.c#L2962-L3003
'math.isclose -> boolean
'    a: double
'    b: double
'    relTol: double = 1e-09
'        maximum difference for being considered "close", relative to the
'        magnitude of the input values, e.g. abs(a - b)/(a OR b) < relTol
'    absTol: double = 0.0
'        maximum difference for being considered "close", regardless of the
'        magnitude of the input values, e.g. abs(a - b) < absTol
'Determine whether two floating point numbers are close in value.
'Return True if a is close in value to b, and False otherwise.
'For the values to be considered close, the difference between them
'must be smaller than at least one of the tolerances.
'-inf, inf and NaN behave similarly to the IEEE 754 Standard.  That
'is, NaN is not close to anything, even itself.  inf and -inf are
'only close to themselves.
'@Description("Determine whether two floating point numbers are close in value, accounting for special values in IEEE 754")
Public Function IsClose(ByVal a As Double, ByVal b As Double, _
                        Optional ByVal relTol As Double = 0.000000001, _
                        Optional ByVal absTol As Double = 0 _
                        ) As Boolean
                        
    If relTol < 0# Or absTol < 0# Then
        Err.Raise 5, Description:="tolerances must be non-negative"
    ElseIf a = b Then
        'Short circuit exact equality -- needed to catch two infinities of
        ' the same sign. And perhaps speeds things up a bit sometimes.
        IsClose = True
    ElseIf IsInfinity(a) Or IsInfinity(b) Then
        'This catches the case of two infinities of opposite sign, or
        ' one infinity and one finite number. Two infinities of opposite
        ' sign would otherwise have an infinite relative tolerance.
        'Two infinities of the same sign are caught by the equality check
        ' above.
        IsClose = False
    Else
        'Now do the regular computation on finite arguments. Here an
        ' infinite tolerance will always result in the function returning True,
        ' since an infinite difference will be <= to the infinite tolerance.
        'NaN has already been filtered out in the equality checks earlier.

        On Error Resume Next 'This is to suppress overflow errors as we deal with infinity.
        Dim diff As Double: diff = Abs(b - a)
        
        If diff <= absTol Then
            IsClose = True
        ElseIf diff <= CDbl(Abs(relTol * b)) Then
            IsClose = True
        ElseIf diff <= CDbl(Abs(relTol * a)) Then
            IsClose = True
        End If
        On Error GoTo 0
    End If
End Function

'@Description "Checks if Number is IEEE754 +/- inf, won't raise an error"
Public Function IsInfinity(ByVal Number As Double) As Boolean
    On Error Resume Next                         'in case of NaN
    IsInfinity = Abs(Number) = PosInf
    On Error GoTo 0
End Function

'@Description "IEEE754 -inf"
Public Property Get NegInf() As Double
    On Error Resume Next
    NegInf = -1 / 0
    On Error GoTo 0
End Property

'@Description "IEEE754 +inf"
Public Property Get PosInf() As Double
    On Error Resume Next
    PosInf = 1 / 0
    On Error GoTo 0
End Property

'@Description "IEEE754 signaling NaN (sNaN)"
Public Property Get NaN() As Double
    On Error Resume Next
    NaN = 0 / 0
    On Error GoTo 0
End Property

'@Description "IEEE754 quiet NaN (qNaN)"
Public Property Get QNaN() As Double
    QNaN = -NaN
End Property

_{更新以纳入来自Code Review的Cristian Buse的宝贵反馈}

示例

IsClose函数可用于检查绝对差异：

assert(IsClose(0, 0.0001233, absTol:= 0.001)) 'same to 3 d.p.?

...或者相对差异：

assert(IsClose(1234.5, 1234.6, relTol:= 0.0001)) '0.01% relative difference?

...但通常情况下，您需要同时指定两个公差，只要满足其中一个公差，就认为这些数字是接近的。它对+-无穷大有特殊处理，只有自身才被认为是接近的，而NaN则与任何东西都不接近（详见PEP中的完整理由，或者我的代码审查帖子，我很乐意听取对这段代码的反馈意见 :)）

- Greedo

2

正如所指出的那样，许多十进制数字不能精确地表示为传统浮点类型。根据您问题空间的性质，您可能最好使用Decimal VBA类型，该类型可以表示带有完美精度的十进制数（基数为10），直到某个小数点。例如，这经常用于表示货币，其中通常需要2位小数精度。

Dim a as Decimal
Dim b as Decimal
a = 0.15
b = 0.01

- C. Dragon 76

2

VBA中是否有十进制(decimal)数据类型？我认为您需要声明为Variant，然后通过Cdec(myVar)强制转换为十进制(decimal)。 - Mark Nold

我们正在谈论VBA。在那里，Decimal不是VariableType。因此，此代码无法编译。 - Gener4tor

1

货币数据类型可能是一个不错的选择。它可以处理相对较大的数字，并具有固定的四位精度。

- DJ

1

根据您的情况和数据，如果您对默认显示的精度水平感到满意，可以尝试将数字的字符串转换进行比较作为一种非常简单的编码解决方案。

if cstr(a) = cstr(b)

这将包括默认情况下显示的尽可能多的精度，通常足以认为数字相等。

对于非常大的数据集来说，这样做效率低下，但对于我来说，在将数据存储在VBA数组中后，用于调节不匹配但实际上是相同的导入数据时非常有用。

- Michael

0

绕道处理？？不确定这是否能解决所有情况，但我在使用VBA比较舍入的双精度值时遇到了问题。当我比较看起来舍入后相同的数字时，VBA会在if-then比较语句中触发错误。我的解决方法是先将双精度转换为字符串，然后再将字符串转换为双精度，然后进行比较。

模拟示例我没有记录导致本帖提到的错误的确切数字，而且我的示例中的金额目前并没有触发该问题，它们只是代表了这种类型的问题。

 Sub Test_Rounded_Numbers()

      Dim Num1 As Double

      Dim Num2 As Double

      Let Num1 = 123.123456789

      Let Num2 = 123.123467891

      Let Num1 = Round(Num1, 4) '123.1235


      Let Num2 = Round(Num2, 4) '123.1235

      If Num1 = Num2 Then

           MsgBox "Correct Match, " & Num1 & " does equal " & Num2
      Else
           MsgBox "Inccorrect Match, " & Num1 & " does not equal " & Num2
      End If

      'Here it would say that "Inccorrect Match, 123.1235 does not equal 123.1235."

 End Sub

 Sub Fixed_Double_Value_Type_Compare_Issue()

      Dim Num1 As Double

      Dim Num2 As Double

      Let Num1 = 123.123456789

      Let Num2 = 123.123467891

      Let Num1 = Round(Num1, 4) '123.1235


      Let Num2 = Round(Num2, 4) '123.1235

      'Add CDbl(CStr(Double_Value))
      'By doing this step the numbers
      'would trigger if they matched
      '100% of the time

      If CDbl(CStr(Num1)) = CDbl(CStr(Num2)) Then

           MsgBox "Correct Match"
      Else
           MsgBox "Inccorrect Match"

      End If

      'Now it says Here it would say that "Correct Match, 123.1235 does equal 123.1235."
 End Sub

- Josh Anstead

-1

尽可能使用单精度值。转换为双精度值会产生随机错误。

Public Sub Test()
Dim D01 As Double
Dim D02 As Double
Dim S01 As Single
Dim S02 As Single
S01 = 45.678 / 12
S02 = 45.678
D01 = S01
D02 = S02
Debug.Print S01 * 12
Debug.Print S02
Debug.Print D01 * 12
Debug.Print D02
End Sub

    45,678 
    45,678 
    45,67799949646 
    45,6780014038086

- Elimar

这个措辞有点误导，错误并不是随机的，但单独使用解决方案将会四舍五入到一个合理的有效数字以进行比较。我认为最好还是使用其他方法来控制舍入。 - Greedo

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

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

- Rob Walker · Accepted Answer

你不能将浮点数值进行相等比较。参考这篇文章 "比较浮点数" 来讨论如何处理内在误差问题。

如果你未事先确定浮点数的绝对范围，那么它并不像简单地与恒定的误差边界进行比较那样简单。