主题:【分享】EXCEL采用VB实现指定修约间隔及指定有效数字四舍六入五留双修约方法

浏览 |回复40 电梯直达
夜豹
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
该帖子已被新官人设置为精华; 奖励积分记录: 夜豹(10分)
      我们科学技术及生产生活中测试和计算的数值修约方法,是按照《GB/T 8170-2008 数值修约规则与极限数值的表示和判定》给出的规则进行修约,就是常说的“四舍六入五留双”,这种修约规则,在其他国家的比如国际标准《ISO 80000-1:2009 Quantities and units - Part 1 General》、美标《ASTM E29-2013 使用试验数据中的有效数字以确保规范一致性的标准规程》、英标《BS EN ISO 80000-1:2013 Quantities and units. General》  、澳标《AS 2706:2003 数值-修约和继续值的解释》、日标《JIS Z 8401:1999 (2005)数値の丸め方》等尽管与我国的GB/T 8170有一定的差异,但是在“四舍六入五留双”规则上,修约方法也是相同的。
      EXCEL电子表格采设定小数位数时,用的是“四舍五入”的修约方法,之所以不采用这种“四舍六入五成双”的修约方法,主要是EXCEL表格取舍的小数位数是有限的(小数点后16位),用“五留双”方法在极端情况下会出现浮点误差,要消除运算的浮点误差就只有无限增加小数点位数,但是这样一来就会造成软件运算量大增,这是微软工程师也无法克服的。微软为了省事或是说避免纠纷,干脆就简单的“四舍五入”修约了,因此不要指望微软来解决这个修约的问题。通常我们用EXCEL实现“四舍六入五成双”的修约,可以用函数组合公式修约实现,但是用函数组合的公式存在过于复杂,修改不灵活不方便的缺点,我给大家介绍使用VB代码实现“四舍六入五成双”的终极方法。
      这个VB代码其实不是我原创,是excelhome论坛上一位ID叫“三流高手”的大神编写,最早在2012年发表的,据我所知他应该是国内采用VB代码完美解决这个修约方法的第一人了。我只是把大神的帖子的内容加上说明后介绍过来。VB代码见以下附件,这附件是一个压缩包,解压后就是本例的excel文档,文档里面一共是用VB有两个自定义函数,bround(实现指定修约间隔修约),cround(实现指定有效数字位数修约),代码及其简单但是有效,大家先看到自定义函数的形式和修约的效果,见下图,是符合了GB/T 8170-2008要求的,大家可以在excel文档里拟修约数值里面修改要修约的数值,自己验证。

      EXCEL文档查看代码的方法是右键点击工作薄,点“查看代码(V)”打开VB编辑器


      在代码编辑器左边的工程VBAproject栏里,双击模块1,就可以打开模块看到“三流高手”编辑的代码及说明。


      如果是自己原有的EXCEL表格想使用这个自定义函数的代码也简单,就是在自己原有的excel文档,右键点击工作薄,点“查看代码(V)”打开VB编辑器,点击“插入”—“模块”,建立一个空白的模块,

      双击打开模块的编辑窗口,把附件里面的代码复制过去,保持为“EXCEL使用宏的工作薄”,就可以在原有的EXCEL表格里面使用这两个自定义函数了。图中代码是两个自定义函数合在一起的,横线上部分是bround的代码,横线下部分是cround的代码,自己可以根据需要的函数分别复制上部分或是下部分。还有这两个自定义公式是可以根据自己的爱好,在代码里面把函数名称bround 或是cround改成自己喜欢的名称的,而且定义的指定位数n,可以用EXCEL自带的函数设置成自己需要的位数。

      最后要说明的是,如果需要修约的数值比较多,极端情况下这两个VB编制的自定义公式还是会出现浮点误差的问题,对这个问题在excelhome论坛的“三流高手”大神也给出另外一个VB函数尽可能的解决浮点误差的,只是这两个公式基本可以满足日常使用了就不介绍了。
这个帖子是应答“沧海青城”的讨论在EXCEL中,四舍六入五留双怎么破帖子的,相比起复杂的组合公式,对于使用宏的EXCEL带来的警告不便,我觉得实在是不值一提
      刚刚发现可以用压缩包形式可以上传带宏的excel文档,2019年6月30日更改附件了,大家下载了就可以亲自验证了。顺便把原来的帖子编辑了一下,原来的帖子叙事比较乱,大家不好看明白,。
该帖子作者被版主 沧海青城10积分, 2经验,加分理由:话题
为您推荐
沧海青城
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
之所以之前一直不愿意用宏,也确实和宏会被误“杀”有关系。对于了解的人,宏操作起来确实很简单,但被隔离或当病毒清理后,不清楚的人就很难再把宏调用出来。
个人觉得对于类似实用性的操作资料,也尽可能的让使用者简单直接的使用为好,这就是我为什么一直想用公式解决的原因。
myoldid
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
valorb
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
生活所迫
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
水质分析时,统计对象涉及很多项目,每个项目的数据,第几位需修约能自动判断吗?
夜豹
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
原文由 生活所迫(yzhlai) 发表:
水质分析时,统计对象涉及很多项目,每个项目的数据,第几位需修约能自动判断吗?
对于不同项目测试数据的第几位修约不能自动判定,都是要自己设定修约到小数点后第几位,或是保留多少个有效数字。
该帖子作者被版主 沧海青城2积分, 2经验,加分理由:讨论
夜豹
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
原文由 沧海青城(lgt228) 发表:
之所以之前一直不愿意用宏,也确实和宏会被误“杀”有关系。对于了解的人,宏操作起来确实很简单,但被隔离或当病毒清理后,不清楚的人就很难再把宏调用出来。
个人觉得对于类似实用性的操作资料,也尽可能的让使用者简单直接的使用为好,这就是我为什么一直想用公式解决的原因。
对于实用性操作资料要上传给不特定的人使用,确实是会有人因为宏不了解造成误杀懵逼。这样的话如果修约位数固定的话用公式修约也是个办法。我使用宏是公司里面用,跟大家说明了知道是什么回事了才行
该帖子作者被版主 沧海青城2积分, 2经验,加分理由:讨论
deer
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
原文由沧海青城(lgt228)发表:之所以之前一直不愿意用宏,也确实和宏会被误“杀”有关系。对于了解的人,宏操作起来确实很简单,但被隔离或当病毒清理后,不清楚的人就很难再把宏调用出来。
个人觉得对于类似实用性的操作资料,也尽可能的让使用者简单直接的使用为好,这就是我为什么一直想用公式解决的原因。
是呀,希望简单点
沧海青城
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
原文由 夜豹(liangking) 发表:
对于实用性操作资料要上传给不特定的人使用,确实是会有人因为宏不了解造成误杀懵逼。这样的话如果修约位数固定的话用公式修约也是个办法。我使用宏是公司里面用,跟大家说明了知道是什么回事了才行
是的,不过都挺好,为大家服务好就行
之宣
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
拉菲
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵