Excel中的圆函数，工作表函数vs VBA [英] Round function in Excel, worksheet function vs VBA

问题描述

我有一个应用程序可以返回与大量值中的某些值最接近的匹配项（如我之前的问题），我选择了一个VBA解决方案。在使用上述申请的过程中，我观察到值为0.5的结果是不正确的。我一直在使用VBA Round函数，我发现返回0为0.5舍入到整数，而工作表循环函数返回1.奇怪的是，VBA循环函数返回2为1.5。我不得不用工作表函数代替VBA。

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 Round（）函数使用Banker的舍入，而电子表格单元格函数使用算术舍入。检查细节：

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:

PRB：圆功能不同的VBA 6和Excel电子表格

Microsoft提出的解决方法是编写一个自定义函数来获得所需的结果。

The workaround proposed by Microsoft is to write a custom function to get the desired results.

银行家的四舍五入始终向最接近的 数字加0.5，是会计的标准，这就是为什么Excel以这种方式工作。 算术四舍五入，直到下一个数字为止。

Banker's rounding always rounds 0.5 to the nearest even number and is standard in accounting, which is why Excel works that way. Arithmetic rounding rounds 0.5 up to the next number.

这篇关于Excel中的圆函数，工作表函数vs VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！