Excel VBA 停止自动舍入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7270641/
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
Excel VBA stop Auto Rounding
提问by S..
I have price column in which the prices are displayed in 3 decimals and 4 decimals places, for example 123.456 or 123.4357. So irrespective of the number of decimal places i want the value to be with only two decimals. So i am selecting the column and in the VBA i am using Selection.NumberFormat = "0.00" Which is resulting me the rounded value i.e when i format 123.456 and 123.4357 to 0.00 i am getting 123.46 and 123.44 but i want it to be 123.46 and 123.43.
我有价格列，其中价格以 3 位小数和 4 位小数显示，例如 123.456 或 123.4357。因此，无论小数位数如何，我都希望该值只有两位小数。所以我选择列，在 VBA 中我使用 Selection.NumberFormat = "0.00" 这导致我四舍五入的值，即当我将 123.456 和 123.4357 格式化为 0.00 我得到 123.46 和 123.44 但我希望它是 123.46 和123.43。
So just wondering is there a way we can just trim the values instead of rounding. Please give me some sample examples.
所以只是想知道有没有一种方法可以只修剪值而不是四舍五入。请给我一些示例。
Thank you in advance.
先感谢您。
回答by rajah9
Excel has a built-in function, trunc, that should do the trick for you.
Excel 有一个内置函数 trunc，它应该可以为您解决问题。
This is what I placed in a1:b2.
这就是我放在 a1:b2 中的内容。
123.456 =trunc(A1,2)
123.4357 =trunc(A2,2)
This will display
这将显示
123.456 123.45
123.4357 123.43
回答by joshua9k
You could treat the result as a string and extract the number of characters you need as in:
您可以将结果视为字符串并提取所需的字符数，如下所示：
dim a as double
dim s as string
a = 123.4357
s = MID(a,1,FIND(".",a))&MID(a,FIND(".",a)+1,2)
dim a as double
将 s 作为字符串
a = 123.4357
s = MID(a,1,FIND(".",a))&MID(a,FIND(".",a)+1,2)
回答by Alex K.
I don't believe there is a built in mask that will truncate without rounding, you could instead use another column containing =TRUNC(A1, 2).
我不相信有一个内置掩码会在不舍入的情况下截断，您可以改用另一列包含=TRUNC(A1, 2).
