仪器信息网APP

选仪器、听讲座、看资讯

立即体验

当前位置：仪器社区 >实验室建设/管理 > 实验室建设 > 帖子详情

在Excel中实现四舍六入五成双修约的几种方法及问题

Ins_d30d8932

2019/02/21

实验室建设

数值修约规则是指在进行具体的数字运算前或计算出结果后，通过省略原数值的最后若干位数字，调整保留的末位数字，使最后所得到的值最接近原数值的过程。指导数字修约的具体规则被称为数值修约规则。
进行数值修约时应首先确定“修约间隔”（修约值的最小数值单位）和“进舍规则”。一经确定，修约值即为“修约间隔”的整数倍。然后指定表达方式，即选择根据“修约间隔”保留到指定位数。最后我们需要注意的是应该一步到位修约，而不允许连续修约。
目前广泛使用的数值修约规则主要有四舍五入和四舍六入五成双。
四舍五入往往是人们习惯采用的一种数值修约规则，其具体使用方法是：在需要保留数字的位次后一位，逢四及以下就舍，逢五及以上就进。Excel这款广泛使用的软件在处理数据时默认采用的就是四舍五入修约规则。
当然四舍五入修约规则，逢五就进，必定会造成结果的系统性偏高，误差偏大。为了避免这样的状况出现，尽量减小因修约而产生的误差，在医药工业等科技领域中，测定和计算各种数值时就需要用到更为科学的修约规则。
《中国药典》2015年版四部凡例中明确说明“试验结果在运算过程中，可比规定的有效数字多保留一位，而后根据有效数字的修约规则进舍至规定有效位。计算所得的最后数值或测定读数值均可按修约规则进舍至规定的有效位，取此数值与标准中规定的限度数值比较，以判断是否符合规定的限度。”这里的修约规则遵循中国国家标准文件GB/T 8170—2008《数值修约规则与极限数值的表示和判定》，即四舍六入五成双。
四舍六入五成双修约规则与四舍五入修约规则有些许差异，当被修约的数字小于或等于四时，就直接舍去；当被修约的数字大于或等于六时，则向前进一位；当被修约的数字等于五时，需要看五前面的数字，如果是偶数就将五舍去，如果是奇数则进一位，即修约后末尾数都为偶数，而当五的后面还有不为零的任何数时，则无论五的前面是奇数还是偶数，均应进位。由此可见，四舍六入五成双与四舍五入只是在被修约的数字等于五，且其前面是偶数时修约方式不一致，在其它情况下修约方式均完全一致。
所以当Excel这款通用软件应用于医药工业中时，其自身默认的四舍五入修约规则与中国药典中所规定的四舍六入五成双修约规则有所出入，不能完全符合中国药典的要求。
那么制药企业如何让Excel在日常使用中实现四舍六入五成双修约规则呢？
我们知道在Excel中可以运用ROUND函数按指定的位数对数值进行四舍五入，ROUNDDOWN函数按指定的位数向下舍入数字，ROUNDUP函数按指定的位数向上舍入数字。因此可以通过将其与IF等函数进行嵌套实现四舍六入五成双的函数算法，一个较为简洁的公式是：=IF(MOD(ABS(X*POWER(10,Y)),2)=0.5,ROUNDDOWN(X,Y),ROUND(X,Y))。其中，X为待修约的原始数值；Y为保留位数，可以为正值、零和负值，如+1表示进位到0.1，-2表示进位到100位，0表示进位到整数位。
下面进行解释，POWER（10，Y）函数表示进行10的Y次方乘幂运算，ABS函数返回给定数值（乘幂运算结果）的绝对值，MOD函数返回两数（上述绝对值与2）相除的余数，如果余数是0.5（说明被修约数值的尾数等于五，且其前面的数是偶数）则返回ROUNDDOWN(X,Y)，即将待修约数值X按Y保留位数向下舍入；如果余数不是0.5则返回ROUND(X,Y)，即将待修约数值X按Y保留位数进行四舍五入。
以一组实际数据为例，需要计算样品两次pH值测定结果的平均值，保留两位小数，直接运用AVERAGE函数即可，Excel默认的四舍五入修约规则得到的结果如下图所示：

而套用上述四舍六入五成双函数公式，以E3单元格为例，将X替换为AVERAGE（C3:D3）,将Y替换为2，其它单元格以此类推，得到四舍六入五成双修约结果如下图所示：

我们发现两张图中E4单元格计算结果不一样，因为5.56和5.57的平均值5.565取两位小数如按四舍五入修约得5.57，而依四舍六入五成双修约得5.56。
当然我们也发现计算公式大为增长了，这还只是一个求平均值的简单计算，如果是其它更为复杂的计算，上述公式中的X还需要进行更多的嵌套。那么除函数方法外，还有没有其它更加简洁的方法实现四舍六入五成双修约呢？
答案是肯定有的，一种常见的利用VBA编写自定义函数的方式：
Function TranValue(rng As Double, number As Integer) As Double
TranValue = Round(rng,number)
End Function
函数编写完，就可以在Excel工作表中引用TranValue（X,Y）该函数。其中，X为待修约的原始数值；Y为保留位数。
输入一组数据，以E3单元格为例，X为AVERAGE（C3:D3）,Y为2，其它单元格以此类推，得到四舍六入五成双修约结果如下图所示：

仔细观察，E3单元格计算结果0.545按四舍六入五成双修约应为0.54，为什么是0.55呢？但E5单元格计算结果0.565修约为0.56又是正确的，这又是什么原因呢？
不难看出，Excel中这种常见的运用宏命令实现四舍六入五成双修约的方法是存在缺陷的。因为计算机是基于二进制的，我们需要在代码中增加一个CDec转换函数，将双精度浮点型数据转换为十进制型，修改后的VBA代码如下图所示：

这样在输入同样的数据后，四舍六入五成双修约结果才完全正确，如下图所示：

小编此番讲解希望对制药同仁们有所启示与帮助，毕竟数据修约不当会影响到产品质量数据与极限数值的判定。
利用Excel能为提高工作效率带来立竿见影的效果，投入成本低，适应性强。实验室的信息化与自动化可以从Excel开始。eInfotree软件能弥补Excel在GxP领域合规性上的差距，实现Excel的用户访问控制，审计追踪，电子签名，完全符合CFR21 Part11的要求，让制药企业无顾虑地享用电子表格带来的便利。
文章转自微信公众号：实验室信息化与自动化

分
该帖子已被版主-检测一家亲加2积分，加2经验;加分理由:鼓励

相关话题

老兵

第1楼2019/02/21

应助达人

在Excel中实现四舍六入五成双修约的几种方法总体还是不错的。
但这个修约还有缺陷，因为对“四舍六入五成双”的表述并不完整，还差一句话，就是"五后面不为零须进一"。楼上举例0.545按四舍六入五成双修约后是0.54，但如果是0.5450001就必须修约成0.55。还有pH计算平均值的示例不严谨，严格地说不宜用算数平均，而应采用指数计算为H 浓度的均值后再反算为pH。

1

分
该帖子已被版主-qianguiyun1加2积分，加2经验;加分理由:讨论

Ins_d30d8932

第2楼2019/02/22

文中是有详细介绍四舍六入五成双修约规则的：“而当五的后面还有不为零的任何数时，则无论五的前面是奇数还是偶数，均应进位”。
现在pH值一般都是直接从pH计中读数，仪器已经替我们完成了指数计算H 浓度反算为pH的过程，所以直接用算数平均并无太大不妥啊。
老兵(wangliqian) 发表:在Excel中实现四舍六入五成双修约的几种方法总体还是不错的。
但这个修约还有缺陷，因为对“四舍六入五成双”的表述并不完整，还差一句话，就是"五后面不为零须进一"。楼上举例0.545按四舍六入五成双修约后是0.54，但如果是0.5450001就必须修约成0.55。还有pH计算平均值的示例不严谨，严格地说不宜用算数平均，而应采用指数计算为H 浓度的均值后再反算为pH。

0

分
该帖子已被版主-qianguiyun1加2积分，加2经验;加分理由:讨论

qianguiyun1

第3楼2019/02/25

应助达人

修约规则变了，Excel也应该改进

0

Ins_d30d8932

第4楼2019/03/08

不过只有中国药典要求四舍六入五成双修约哦，美国药典和欧洲药典还是四舍五入修约规则的。
qianguiyun1(qianguiyun1) 发表:修约规则变了，Excel也应该改进

0

qqqid

第5楼2019/03/13

"6成双“会不会造成系统性偏低？

0

Ins_d30d8932

第6楼2019/03/15

应该说是4舍5入修约会造成系统性偏高，而5成双不会。
qqqid(qqqid) 发表:"6成双“会不会造成系统性偏低？

0

gongdw

第7楼2019/07/18

学习

0

qgp

第8楼2019/07/18

实用。

0

Insm_3139229b

第9楼2019/08/02

最后一步计算被修约是不够的，每一步计算理论上都要按规定修约，这时候，excel就不够用了。

0

近期热榜

热门活动

猜你喜欢最新推荐热门推荐更多推荐

品牌合作伙伴

日立科学仪器

珀金埃尔默仪器（上海）有限公司（PerkinElmer）

珀金埃尔默仪器（上海）有限公司（PerkinElmer）

日本电子株式会社

赛默飞世尔科技

马尔文帕纳科

上海仪电科仪

梅特勒托利多

布鲁克核磁

举报帖子

点赞用户

好友列表

加载中...

正在为您切换请稍后...