VBA舍入遇到的一个奇怪问题

三流高手

您的问题解决了吗？
我这有一个方法，可以解决您遇到的问题。

baomaboy

三流高手 发表于 2012-10-1 15:30
据您所知，有没有解决的方法呢？
我有一个方法，如有兴趣，可以和您探讨一下。

http://club.excelhome.net/thread-617843-1-1.html

三流高手

以下是我在网上看到的一篇关于四舍六入五留双的文章，与大家分享：
经常看到有人提出 Round 函数的问题，很多人都认为这个函数就是四舍五入算法，其实是错误的。
在 VB, VBScript, C#, J#, T-SQL 中 Round 函数都是采用 Banker's rounding（银行家舍入）算法，即四舍六入五取偶。详细的说是这样：四舍六入五考虑, 五后非零就进一, 五后皆零看奇偶, 五前为偶应舍去, 五前为奇要进一. 事实上这也是 IEEE 规定的舍入标准。因此所有符合 IEEE 标准的语言都是采用这一算法的。
虽然“四舍五入”是我国最早提出的算法，值得我们自豪，但不能因此就认为它始终是先进的。毕竟它已经有近二千年历史了（大约一千七百多年前，天文学家杨伟就已明确提出了“四舍五入法”）。
四舍五入算法逢五就要进位，带来的问题就是结果偏大，尤其是在大量的数据统计中。4舍6入没有什么特别，就是5 比较特殊，如果5舍就会偏小，如果5入就会偏大，Banker 舍入法是取最接近的偶数，这样就比四舍五入准确性高，我们看下面的例子。
             原始数据       四舍五入      Banker 舍入
             1.1249         1.12              1.12
             1.1248         1.12              1.12
             1.1252         1.13              1.13
             1.1250         1.13              1.12
             1.1354         1.14              1.14
             1.1351         1.14              1.14
             1.1352         1.14              1.14
             1.1353         1.14              1.14
             1.1361         1.14              1.14
             1.1360         1.14              1.14
----------------------------------------------------------------
平均值 1.1313         1.134            1.133
这组原始数据它们的平均值是1.1313；如果我们按四舍五入保留两位小数，它们的平均值是1.134，误差是+0.0027；如果我们采用 Banker 舍入，结果是1.1320，误差是+0.0017。可以看出四舍五入的误差要大得多。而在金融计算和统计中是精度是非常重要的，这也是 Banker 舍入的名称的由来，银行家是不喜欢四舍五入的。
四舍五入是我上小学时数学课程的内容，大多数中国人都坚信舍入小数时应该使用这个算法。所幸据说现在的小学数学课程已经将 Banker's rounding 正式写入课本。希望以后的孩子大概不会再有现在的问题了。
关于编程中 Round 函数和 Banker 舍入的信息，可以参考微软的 Knowledge Base：
Q194983 PRB: Round Function Different in VBA 6 and Excel Spreadsheet
Q196652 HOWTO: Implement Custom Rounding Procedures
---------------------------------------------------------------------------------------------------------
另外，我们还可能碰到这样的问题
? MATH.ROUND(3.225,2)     结果是 3.22
? MATH.ROUND(1.225,2)     结果是 1.23
这是在.net中的使用 Round 函数的两个例子，我们知道.net中的Round 函数使用的就是Banker's rounding（银行家舍入）算法。按前面的理解，五前为偶应舍去，3.225 保留2位小数确实是3.22，1.225同样应该符合 五前为偶应舍去 的原则,可是为什么结果是1.23呢？
其实，这是由于数据类型精度的问题导致的，这是由于浮点数(double或float)的不精确造成的，1.225 如果作为浮点数常量，在机器内部存储时，可能是 1.2250000000000000000000001，按照 五后非零就进一 原则，就被舍入为 1.23 了。如果用 System.Decimal 数据类型，就没有这个问题了，例如：
在.Net中 ？Math.Round(CDec(1.225), 2)   结果就是 1.22 了，所以出现这种情况，并不是Round函数有错，而是我们在编程时应该注意数据精度的问题。
但是在VBScript中并没有Decimal数据类型，这时我们可以用Currency来处理。例如：
VBScript中    Round(1.225,2)             结果就是 1.23      
                     Round(CCur(1.225),2)   结果就是 1.22
但是值得注意的是VBScript中的Currency类型只能处理4位小数，其取值范围是 922,337,203,685,477.5808 到 922,337,203,685,477.5807。

三流高手

chenzyyysl 发表于 2012-5-10 09:26
采用下面的进行测试
Sub xxx()
   With Sheets(1)

您测试的四舍六入五留双的代码已有更新，解决了已知的bug，有兴趣再看看。

http://club.excelhome.net/forum.php?mod=viewthread&tid=800269

三流高手

四舍六入的问题还这么多啊，学习了

baomaboy

三流高手 发表于 2012-10-1 22:05
您给出的链接里的工具不错，但看不到源代码，学习不了。
另外，我主要想和您探讨一下怎么改进下列代码， ...

就我所知，使用vba的round还不如使用工作表函数的round改造。

三流高手

baomaboy 发表于 2012-10-1 18:08
http://club.excelhome.net/thread-617843-1-1.html

您给出的链接里的工具不错，但看不到源代码，学习不了。
另外，我主要想和您探讨一下怎么改进下列代码，使它可用（避免已知的错误！）
Function Cround(x, mm As Long)
    Cround = VBA.Round(Val(x), mm)
End Function

chenzyyysl

大假回来看到这个贴子还在讨论，我已经在20楼给出了我的解决方法及源码，如果精度要求更高的可以略改一下参数，或者把精度要求做到参数里，应当能解决实际的问题了。

wj2368

chenzyyysl 发表于 2012-10-9 08:18
大假回来看到这个贴子还在讨论，我已经在20楼给出了我的解决方法及源码，如果精度要求更高的可以略改一下参 ...

你的函数也不正确，你算算4.085是多少

1. Function round55(num1, num2)
   
2.      num3 = Int(num1 * 10 ^ num2)
   
3.    If Abs(num1 * 10 ^ num2 - num3 - 0.5) < 0.0000000001 Then
   
4.      If (-1) ^ num3 = -1 Then num3 = num3 + 1
   
5.    Else
   
6.      num3 = Int(num1 * 10 ^ num2 + 0.5)
   
7.    End If
   
8.    round55 = num3 / 10 ^ num2
   
9. End Function

复制代码

用的截取方法

1. Function roundA(num1, num2)
   
2.      num1 = Left(num1, num2 + InStr(num1, ".") + 1)
   
3.    If Val(Right(num1, 1)) >= 5 Then
   
4.      roundA = (Val(Left(num1, num2 + InStr(num1, "."))) * 10 ^ num2 + 1) / 10 ^ num2
   
5.    Else
   
6.      roundA = (Val(Left(num1, num2 + InStr(num1, "."))) * 10 ^ num2) / 10 ^ num2
   
7.    End If
   
8. End Function
   

复制代码

chenzyyysl

没看到有什么问题呀，=round55(4.085,2) 结果是4.08