vba 如何将数字四舍五入到最接近的十位？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8587474/
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 roundup a number to the closest ten?
提问by Andrei Andre
Probably the title is not very suggestive.
可能标题不是很有暗示性。
Let me explain you with an example. I have:
让我用一个例子来解释你。我有：
12345.6
2345.1
12345.00000001
I want those numbers to be roundup to 12350.
How can I do this?
我希望这些数字被四舍五入到12350.
我怎样才能做到这一点？
If possible, I would rather use formulas instead of VBA.
如果可能，我宁愿使用公式而不是 VBA。
回答by brettdj
You could also use CEILINGwhich rounds up to an integer or desired multiple of significance
您还可以使用CEILINGwhich 四舍五入为整数或所需的重要倍数
ie=CEILING(A1,10)
rounds up to a multiple of 10
即=CEILING(A1,10)
四舍五入为 10 的倍数
12340.0001will become 12350
12340.0001会变成 12350
回答by chris neilsen
Use ROUNDbut with num_digits= -1
使用ROUND但与num_digits= -1
=ROUND(A1,-1)
Also applies to ROUNDUPand ROUNDDOWN
也适用于ROUNDUP和ROUNDDOWN
From Excel help:
从 Excel 帮助：
- If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.
- If num_digits is 0, then number is rounded to the nearest integer.
- If num_digits is less than 0, then number is rounded to the left of the decimal point.
- 如果 num_digits 大于 0（零），则将 number 舍入到指定的小数位数。
- 如果 num_digits 为 0，则将数字四舍五入为最接近的整数。
- 如果 num_digits 小于 0，则将数字四舍五入到小数点左侧。
EDIT:To get the numbers to always round up use =ROUNDUP(A1,-1)
编辑：为了让数字总是四舍五入使用=ROUNDUP(A1,-1)
回答by 7bluephoenix
You can use the function MROUND(<reference cell>, <round to multiple of digit needed>).
您可以使用该功能MROUND(<reference cell>, <round to multiple of digit needed>)。
Example:
例子：
For a value
A1 = 21round to multiple of 10 it would be written as=MROUND(A1,10)for which Result = 20For a value
Z4 = 55.1round to multiple of 10 it would be written as=MROUND(Z4,10)for which Result = 60
对于
A1 = 21四舍五入到 10 的倍数的值，它会写成=MROUND(A1,10)Result = 20对于
Z4 = 55.1舍入到 10 的倍数的值，它将被写为=MROUND(Z4,10)Result = 60
回答by KiwiSteve
the second argument in ROUNDUP, eg =ROUNDUP(12345.6789,3) refers to the negative of the base-10 column with that power of 10, that you want rounded up. eg 1000 = 10^3, so to round up to the next highest 1000, use ,-3)
ROUNDUP 中的第二个参数，例如 =ROUNDUP(12345.6789,3) 指的是底数为 10 的列的负数，该列具有 10 的幂，您想要向上取整。例如 1000 = 10^3，因此要四舍五入到下一个最高的 1000，请使用 ,-3)
=ROUNDUP(12345.6789,-4) = 20,000
=ROUNDUP(12345.6789,-3) = 13,000
=ROUNDUP(12345.6789,-2) = 12,400
=ROUNDUP(12345.6789,-1) = 12,350
=ROUNDUP(12345.6789,0) = 12,346
=ROUNDUP(12345.6789,1) = 12,345.7
=ROUNDUP(12345.6789,2) = 12,345.68
=ROUNDUP(12345.6789,3) = 12,345.679
So, to answer your question: if your value is in A1, use =ROUNDUP(A1,-1)
因此，要回答您的问题：如果您的值在 A1 中，请使用 =ROUNDUP(A1,-1)
