vba Excel中的舍入函数，工作表函数与VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/265926/
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
Round function in Excel, worksheet function vs VBA
提问by Vaibhav Garg
I had an application for returning closest matches to certain values in a large cluster of values( as in my earlier question) and I chose a VBA solution. While in the course of using the said application, I observed that the results for the value of 0.5 were not correct. I had been using the VBA Round function which I found to be returning 0 for 0.5 rounded to integer whereas the worksheet round function returns 1. Strangely, the VBA round function returns 2 for 1.5. I had to substitute the worksheet function in place of the VBA one.
我有一个应用程序，用于返回与大量值中的某些值最接近的匹配项（如我之前的问题），我选择了 VBA 解决方案。在使用上述应用程序的过程中，我观察到值 0.5 的结果不正确。我一直在使用 VBA Round 函数，我发现它返回 0 表示 0.5 舍入为整数，而工作表舍入函数返回 1。奇怪的是，VBA 舍入函数返回 2 表示 1.5。我不得不用工作表函数代替 VBA 函数。
Am I missing something?
我错过了什么吗？
采纳答案by Panos
It's a known issue. The VBA Round() function uses Banker's rounding while the spreadsheet cell function uses arithmetic rounding. Check the details here:
这是一个已知问题。VBA Round() 函数使用银行家的舍入，而电子表格单元格函数使用算术舍入。在此处查看详细信息：
PRB: Round Function different in VBA 6 and Excel Spreadsheet
PRB：VBA 6 和 Excel 电子表格中的舍入函数不同
The workaround proposed by Microsoft is to write a custom function to get the desired results.
Microsoft 提出的解决方法是编写自定义函数以获得所需的结果。
Banker's roundingalways rounds 0.5 to the nearest evennumber and is standard in accounting, which is why Excel works that way. Arithmetic roundingrounds 0.5 up to the next number.
银行家的四舍五入总是将 0.5舍入到最接近的偶数，这在会计中是标准的，这就是 Excel 以这种方式工作的原因。算术四舍五入将0.5舍入到下一个数字。
回答by BoboTheCodeMonkey
You can blame Microsoft for this one: http://support.microsoft.com/kb/194983
你可以为此责怪微软：http: //support.microsoft.com/kb/194983
Here is a list of ways around it: http://www.excelforum.com/excel-programming/401104-worksheet-rounding-vs-vba-rounding.html
以下是解决方法的列表：http: //www.excelforum.com/excel-programming/401104-worksheet-rounding-vs-vba-rounding.html
回答by Lance Roberts
Question answered completely hereand copied here (by permission of the author :):
问题在这里完全回答并复制到这里（经作者许可:)：
Be careful, the VBA Round function uses Banker's rounding, where it rounds .5 to an even number, like so:
请注意，VBA Round 函数使用 Banker 的舍入，将 0.5 舍入为偶数，如下所示：
Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)
Whereas the Excel Worksheet Function Round, always rounds .5 up.
而 Excel 工作表函数舍入，始终向上舍入 0.5。
I've done some tests and it looks like .5 up rounding (symmetric rounding) is also used by cell formatting, and also for Column Width rounding (when using the General Number format). The 'Precision as displayed' flag doesn't appear to do any rounding itself, it just uses the rounded result of the cell format.
我做了一些测试，看起来 0.5 向上舍入（对称舍入）也用于单元格格式，也用于列宽舍入（当使用通用数字格式时）。“显示的精度”标志本身似乎没有进行任何舍入，它只是使用单元格格式的舍入结果。
I tried to implement the SymArith function from Microsoft in VBA for my rounding, but found that Fix has an error when you try to give it a number like 58.55; the function giving a result of 58.5 instead of 58.6. I then finally discovered that you can use the Excel Worksheet Round function, like so:
我尝试在 VBA 中实现 Microsoft 的 SymArith 函数进行四舍五入，但是当您尝试给它一个像 58.55 这样的数字时发现 Fix 有错误；函数给出的结果是 58.5 而不是 58.6。然后我终于发现您可以使用 Excel Worksheet Round 函数，如下所示：
Application.Round(58.55, 1)
Application.Round(58.55, 1)
This will allow you to do normal rounding in VBA, though it may not be as quick as some custom function. I realize that this has come full circle from the question, but wanted to include it for completeness.
这将允许您在 VBA 中进行正常的舍入，尽管它可能不如某些自定义函数那么快。我意识到这已经从问题中完整地循环了一遍，但为了完整起见，我想将其包括在内。
