vba 将数字四舍五入到最接近的 5 或 10 或 X
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/326476/
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
Rounding a number to the nearest 5 or 10 or X
提问by matt wilkie
Given numbers like 499, 73433, 2348 what VBA can I use to round to the nearest 5 or 10? or an arbitrary number?
给定像 499、73433、2348 这样的数字，我可以使用什么 VBA 来四舍五入到最接近的 5 或 10？还是任意数字？
By 5:
由 5:
499 -> 500
2348 -> 2350
7343 -> 7345
By 10:
到 10:
499 -> 500
2348 -> 2350
7343 -> 7340
etc.
等等。
采纳答案by matt wilkie
Integrated Answer
综合答案
X = 1234 'number to round
N = 5 'rounding factor
round(X/N)*N 'result is 1235
For floating point to integer, 1234.564 to 1235, (this is VB specific, most other languages simply truncate) do:
对于浮点到整数，1234.564 到 1235，（这是 VB 特定的，大多数其他语言只是截断）执行：
int(1234.564) 'result is 1235
Beware:VB uses Bankers Rounding, to the nearest even number, which can be surprising if you're not aware of it:
注意：VB 使用Bankers Rounding到最接近的偶数，如果您不知道，这可能会令人惊讶：
msgbox round(1.5) 'result to 2
msgbox round(2.5) 'yes, result to 2 too
Thank you everyone.
谢谢大家。
回答by Vilx-
It's simple math. Given a number X and a rounding factor N, the formula would be:
这是简单的数学。给定一个数字 X 和一个舍入因子 N，公式为：
round(X / N)*N
轮（X / N）* N
回答by Alnitak
To round to the nearest X (without being VBA specific)
四舍五入到最近的 X（不特定于 VBA）
N = X * int(N / X + 0.5)
N = X * int(N / X + 0.5)
Where int(...) returns the next lowest whole number.
其中 int(...) 返回下一个最小的整数。
If your available rounding function already rounds to the nearestwhole number then omit the addition of 0.5
如果您可用的舍入函数已经舍入到最接近的整数，则省略 0.5 的加法
回答by Bob
In VB, math.round has additional arguments to specify number of decimal places and rounding method. Math.Round(10.665, 2, MidpointRounding.AwayFromZero)will return 10.67 . If the number is a decimal or single data type, math.round returns a decimal data type. If it is double, it returns double data type. That might be important if option strict is on.
在 VB 中， math.round 有额外的参数来指定小数位数和舍入方法。 Math.Round(10.665, 2, MidpointRounding.AwayFromZero)将返回 10.67 。如果数字是小数或单一数据类型，则 math.round 返回小数数据类型。如果是double，则返回double数据类型。如果启用了选项严格，这可能很重要。
The result of (10.665).ToString("n2") rounds away from zero to give "10.67". without additional arguments math.round returns 10.66, which could lead to unwanted discrepancies.
(10.665).ToString("n2") 的结果从零舍入为 "10.67"。没有附加参数 math.round 返回 10.66，这可能会导致不必要的差异。
回答by Joey
'Example: Round 499 to nearest 5. You would use the ROUND() FUNCTION.
'示例：将 499 舍入到最接近的 5。您将使用 ROUND() 函数。
a = inputbox("number to be rounded")
b = inputbox("Round to nearest _______ ")
strc = Round(A/B)
strd = strc*B
msgbox( a & ", Rounded to the nearest " & b & ", is" & vbnewline & strd)
回答by James Berard
Here is our solution:
这是我们的解决方案：
Public Enum RoundingDirection
Nearest
Up
Down
End Enum
Public Shared Function GetRoundedNumber(ByVal number As Decimal, ByVal multiplier As Decimal, ByVal direction As RoundingDirection) As Decimal
Dim nearestValue As Decimal = (CInt(number / multiplier) * multiplier)
Select Case direction
Case RoundingDirection.Nearest
Return nearestValue
Case RoundingDirection.Up
If nearestValue >= number Then
Return nearestValue
Else
Return nearestValue + multiplier
End If
Case RoundingDirection.Down
If nearestValue <= number Then
Return nearestValue
Else
Return nearestValue - multiplier
End If
End Select
End Function
Usage:
用法：
dim decTotal as Decimal = GetRoundedNumber(CDec(499), CDec(0.05), RoundingDirection.Up)
回答by Raymond Martineau
For a strict Visual Basic approach, you can convert the floating-point value to an integer to round to said integer. VB is one of the rare languages that rounds on type conversion (most others simply truncate.)
对于严格的 Visual Basic 方法，您可以将浮点值转换为整数以四舍五入到所述整数。VB 是在类型转换上进行四舍五入的罕见语言之一（大多数其他语言只是截断。）
Multiples of 5 or x can be done simply by dividing before and multiplying after the round.
5 或 x 的倍数可以简单地通过在回合之前进行除法和在回合之后相乘来完成。
If you want to round and keep decimal places, Math.round(n, d) would work.
如果你想四舍五入并保留小数位， Math.round(n, d) 会起作用。
回答by ana
Try this function
试试这个功能
--------------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 Fredou
something like that?
类似的东西？
'nearest
n = 5
'n = 10
'value
v = 496
'v = 499
'v = 2348
'v = 7343
'mod
m = (v \ n) * n
'diff between mod and the val
i = v-m
if i >= (n/2) then
msgbox m+n
else
msgbox m
end if
回答by Osama Al-Maadeed
Simply ROUND(x/5)*5 should do the job.
只需 ROUND(x/5)*5 就可以完成这项工作。
