为了避免所谓的银行家舍入(=中间值5总是舍入到最接近的偶数),您可以使用以下方法之一:
- (1)
WorkSheetFunction.Round
- (2) 用户定义函数。
银行家舍入是在金融和统计操作中使用的标准舍入形式,以通过始终将中间值舍入到单个方向来最小化多次舍入操作中的重大舍入误差。
(1) 使用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
WorksheetFunction.RoundUp(test1, 0)
(或者使用相同语法的RoundDown
)。 - barvobot