Excel VBA如何将双精度四舍五入为整数? [英] How Excel VBA rounds Doubles to Integers?

问题描述

我试图了解在VBA中声明错误类型的变量时可能发生的错误类型.

I am trying to understand the type of errors that could happen when a wrong type of variable is declared in VBA.

这是我正在使用的代码:

This is the code I am using:

Sub testTypes()

Dim test1 As Integer
test1 = 0.5

Debug.Print test1

End Sub

我试图使用双精度数字类型来查看VBA如何将它们四舍五入(向上或向下)以使其成为整数，因为数字以.5结尾

I tried to use Double number types on purpose to see how VBA will round them (up or down) to make them an Integer, given that the number ends on .5

我得到令人困惑的结果:

I got puzzling results:

5.567 --> 6
5.5 --> 6
4.5 --> 4
3.5 --> 4
2.5 --> 2
1.5 --> 2
0.5 --> 0

任何人都可以解释Excel如何确定它是否向上或向下取整吗?

Could anyone explain how Excel determines whether it will round up or down?

推荐答案

为了避免所谓的银行家舍入(=中点值5总是舍入到最接近的偶数)，可以使用

In order to avoid so called banker's rounding (= midpoint value 5 always rounds to the nearest even number ) you can use

• (1)WorkSheetFunction.Round
• (2)用户定义的函数.

Banker的舍入是用于金融和统计操作的标准舍入形式，目的是通过在单个方向上一致地舍入中点值来最大程度地减少多次舍入操作中的重大舍入误差.

Banker's rounding is a standard form of rounding used in financial and statistical operations in order to minimize significant rounding errors over multiple rounding operations by consistently rounding midpoint values in a single direction.

(1)使用WorksheetFunction Round()

(1) Example using the WorksheetFunction Round()

Sub RoundWithWorkSheetFunction()
' Purpose: avoid so called bankers' rounding in VBA (5 always rounds even)
With WorksheetFunction
    Debug.Print "WorksheetFunction.Round(3.5, 0)=" & .Round(3.5, 0), ":| VBA rounds to " & Round(3.5, 0)
    Debug.Print "WorksheetFunction.Round(4.5, 0)=" & .Round(4.5, 0), ":| VBA rounds to " & Round(4.5, 0)
End With

End Sub

(2)工作表功能的替代方法(避免四舍五入):

Function roundIt(ByVal d As Double, ByVal nDigits As Integer) As Double
' Purpose: avoid so called bankers' rounding in VBA (5 always rounds even)
If nDigits > 0 Then
   ' if continental european colon instead of point separartor
   ' roundIt= val(Replace(Format(d, "0." & String(nDigits, "0")), ",", "."))
     roundIt = Val(Format(d, "0." & String(nDigits, "0")))
Else
   ' if continental european colon instead of point separartor
   ' roundIt =  val(Replace(Format(d / (10 ^ nDigits), "0."), ",", "."))
   roundIt = Val(Format(d / (10 ^ nDigits), "0."))
End If
End Function

这篇关于Excel VBA如何将双精度四舍五入为整数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！