主题：【分享】在Excel中实现四舍六入五成双修约的几种方法及问题

      数值修约规则是指在进行具体的数字运算前或计算出结果后，通过省略原数值的最后若干位数字，调整保留的末位数字，使最后所得到的值最接近原数值的过程。指导数字修约的具体规则被称为数值修约规则。
      进行数值修约时应首先确定“修约间隔”（修约值的最小数值单位）和“进舍规则”。一经确定，修约值即为“修约间隔”的整数倍。然后指定表达方式，即选择根据“修约间隔”保留到指定位数。最后我们需要注意的是应该一步到位修约，而不允许连续修约。
      目前广泛使用的数值修约规则主要有四舍五入和四舍六入五成双。
      四舍五入往往是人们习惯采用的一种数值修约规则，其具体使用方法是：在需要保留数字的位次后一位，逢四及以下就舍，逢五及以上就进。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的要求，让制药企业无顾虑地享用电子表格带来的便利。
文章转自微信公众号：实验室信息化与自动化