如何使用 excel VBA round() 进行四舍五入？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16017501/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to round up with excel VBA round()?
提问by Ting Ping
I have the following data:
我有以下数据：
cell(1,1) = 2878.75
cell(1,2) = .10
cell(2,1) = ,529.13
However, when I tried to use round(cells(1,1).value*cells(1,2).value),2), the result does not match cell(2,1). I figured it has to do with the rounding issue, but I'm just wondering if it is possible to get round()to act normally. That is, for value > 0.5, round up. And for value < 0.5, round down?
但是，当我尝试使用 时round(cells(1,1).value*cells(1,2).value),2)，结果与 不匹配cell(2,1)。我认为这与舍入问题有关，但我只是想知道是否有可能round()正常行动。也就是说，对于value > 0.5，四舍五入。对于value < 0.5，向下舍入？
回答by Alex K.
VBA uses bankers roundingin an attempt to compensate for the bias in always rounding up or down on .5; you can instead;
VBA 使用银行家四舍五入来补偿总是在 0.5 上四舍五入的偏差；你可以代替；
WorksheetFunction.Round(cells(1,1).value * cells(1,2).value, 2)
回答by ana
Try this function, it's ok to round up a double
试试这个功能，四舍五入一个双就可以了
'---------------Start -------------
Function Round_Up(ByVal d As Double) As Integer
Dim result As Integer
result = Math.Round(d)
If result >= d Then
Round_Up = result
Else
Round_Up = result + 1
End If
End Function
'-----------------End----------------
回答by Steven Alpha
If you want to round up, use half adjusting. Add 0.5 to the number to be rounded up and use the INT() function.
如果要四舍五入，请使用半调整。将 0.5 添加到要四舍五入的数字并使用 INT() 函数。
answer = INT(x + 0.5)
答案 = INT(x + 0.5)
回答by ShamBhagwat
I am introducing Two custom library functions to be used in vba, which will serve the purpose of rounding the double value instead of using WorkSheetFunction.RoundDown and WorkSheetFunction.RoundUp
我正在介绍两个要在 vba 中使用的自定义库函数，它们将用于舍入双精度值而不是使用 WorkSheetFunction.RoundDown 和 WorkSheetFunction.RoundUp
Function RDown(Amount As Double, digits As Integer) As Double
RDown = Int((Amount + (1 / (10 ^ (digits + 1)))) * (10 ^ digits)) / (10 ^ digits)
End Function
Function RUp(Amount As Double, digits As Integer) As Double
RUp = RDown(Amount + (5 / (10 ^ (digits + 1))), digits)
End Function
Thus function Rdown(2878.75 * 31.1,2) will return 899529.12 and function RUp(2878.75 * 31.1,2) will return 899529.13 Whereas The function Rdown(2878.75 * 31.1,-3) will return 89000 and function RUp(2878.75 * 31.1,-3) will return 90000
因此函数 Rdown(2878.75 * 31.1,2) 将返回 899529.12 和函数 RUp(2878.75 * 31.1,2) 将返回 899529.13 而函数 Rdown(2878.75 * 31.1,-3) 将返回 88.170 和函数 RUp(2878.75 * 31.1,2) 3) 将返回 90000
回答by sous2817
Try the RoundUp function:
试试 RoundUp 函数：
Dim i As Double
i = Application.WorksheetFunction.RoundUp(Cells(1, 1).Value * Cells(1, 2).Value, 2)
回答by Granticus
I had a problem where I had to round up only and these answers didnt work for how I had to have my code run so I used a different method. The INT function rounds towards negative (4.2 goes to 4, -4.2 goes to -5) Therefore, I changed my function to negative, applied the INT function, then returned it to positive simply by multiplying it by -1 before and after
我遇到了一个问题，我必须只进行四舍五入，而这些答案对我必须运行代码的方式不起作用，所以我使用了不同的方法。INT 函数向负数舍入（4.2 变为 4，-4.2 变为 -5）因此，我将函数更改为负数，应用 INT 函数，然后通过前后乘以 -1 将其返回为正数
Count = -1 * (int(-1 * x))
回答by baziili
Used the function "RDown" and "RUp" from ShamBhagwat and created another function that will return the round part (without the need to give "digits" for input)
使用了 ShamBhagwat 的“RDown”和“RUp”函数，并创建了另一个返回圆形部分的函数（无需为输入提供“数字”）
Function RoundDown(a As Double, digits As Integer) As Double
RoundDown = Int((a + (1 / (10 ^ (digits + 1)))) * (10 ^ digits)) / (10 ^ digits)
End Function
Function RoundUp(a As Double, digits As Integer) As Double
RoundUp = RoundDown(a + (5 / (10 ^ (digits + 1))), digits)
End Function
Function RDownAuto(a As Double) As Double
Dim i As Integer
For i = 0 To 17
If Abs(a * 10) > WorksheetFunction.Power(10, -(i - 1)) Then
If a > 0 Then
RDownAuto = RoundDown(a, i)
Else
RDownAuto = RoundUp(a, i)
End If
Exit Function
End If
Next
End Function
the output will be:
输出将是：
RDownAuto(458.067)=458
RDownAuto(10.11)=10
RDownAuto(0.85)=0.8
RDownAuto(0.0052)=0.005
RDownAuto(-458.067)=-458
RDownAuto(-10.11)=-10
RDownAuto(-0.85)=-0.8
RDownAuto(-0.0052)=-0.005
回答by girhen
Here's one I made. It doesn't use a second variable, which I like.
这是我做的。它不使用我喜欢的第二个变量。
Points = Len(Cells(1, i)) * 1.2
If Round(Points) >= Points Then
Points = Round(Points)
Else: Points = Round(Points) + 1
End If
回答by Stephen Walsh
This worked for me
这对我有用
Function round_Up_To_Int(n As Double)
If Math.Round(n) = n Or Math.Round(n) = 0 Then
round_Up_To_Int = Math.Round(n)
Else: round_Up_To_Int = Math.Round(n + 0.5)
End If
End Function
回答by DrMarbuse
I find the following function sufficient:
我发现以下功能就足够了：
'
' Round Up to the given number of digits
'
Function RoundUp(x As Double, digits As Integer) As Double
If x = Round(x, digits) Then
RoundUp = x
Else
RoundUp = Round(x + 0.5 / (10 ^ digits), digits)
End If
End Function
