用EXCEL自定义公式解决食品检测中有效位数和四舍六入的问题
摘要:食品检测中涉及大量的数据计算和有效数字保留的问题,EXCEL基本的函数缺乏相应的函数。本文通过自定义VBA函数实现计算结果有效位数保留和四舍六入五成双功能,。
1.引言:
食品检测中,经常会遇到有效位数保留和四舍六入五留双的问题,由于EXCEL无相关公式可以直接使用,对于大批量数据的计算,需要检测人员逐一处理,不仅增加工作量,也容易出现错误。本文通过EXCEL VBA宏自定义了一个函数,方便易用,能够有效提高工作效率。
2.食品检测数据修约规则
GB/T 5009.1-2003 食品卫生检验方法 理化部分 总则 A.7 数字修约规则规定如下:1.在拟舍弃的数字中,若左边第一个数字小于5(不包括5)时,则舍去,即所拟保留的末位数字不变。2.在拟舍弃的数字中,若左边第一个数字大于5(不包括5)则进一,即所拟保留的末位数字加一。3.在拟舍弃的数字中,若左边第一个数字等于5,其右边的数字并非全部为零时,则进一,即所拟保留的末位数字加一。4.在拟舍弃的数字中,若左边第一个数字等于5,其右边的数字皆为零时,所拟保留的末位数字若为奇数则进一,若为偶数则不进。5.所拟舍弃的数字,若为两位以上数字时,不得连续进行多次修约,应根据所拟舍弃数字中左边第一个数字的大小,按上述规定一次修约出结果。
3. 程序流程图
4. 函数源程序
Function YXSZ(M, n)
i = 1
If M < 0 Then //判断待修约数据正负
i = -1
End If
A = M * 1000000 * i //待修约数据扩大1000000倍
B = A
For j = 0 To 10 Step 1 //统计待修约数据扩大1000000倍后,整数位数
B = Int(B / 10)
If B = 0 Then
Exit For
End If
Next
j = j + 1
YXSZ = (Round(A / (10 ^ j), n) * 10 ^ j) / 1000000 * i //返回修约数据并返回结果
End Function
本程序通过将待修约数据扩大后按照四舍六入五成双修约,再转换成原数据,实现数据修约。不足之处在于当修约数据修约后末尾为0时,小数点后0不会显示。
5. 应用实例
三个样品用GB 5009.268-2016第一法检测,得到四个元素的上机结果,表一是利用EXCEL自己的计算公式计算,数据要逐个查看,保留有效位数和修约,表二是通过我们的自定义函数,能够自动修约,保留有效位数。对于大批量的数据,能够有效避免人为出错,节约时间。
表一
样品 | 称样量g | 仪器读数ng/mL | 定容体积mL | 计算结果mg/kg | ||||||
铬 | 砷 | 镉 | 汞 | 铬 | 砷 | 镉 | 汞 | |||
样品1 | 0.3019 | 3.597 | 7.527 | 6.939 | 0.855 | 25 | 0.23091 | 0.60732 | 0.57382 | 0.06608 |
样品2 | 0.3129 | 1.116 | 3.969 | 3.274 | 0.586 | 25 | 0.02457 | 0.30169 | 0.26083 | 0.04227 |
样品3 | 0.3239 | 4.820 | 12.785 | 2.043 | 1.049 | 25 | 0.30962 | 0.97190 | 0.15695 | 0.07657 |
表二
样品 | 称样量g | 仪器读数ng/mL | 定容体积mL | 计算结果mg/kg | ||||||
铬 | 砷 | 镉 | 汞 | 铬 | 砷 | 镉 | 汞 | |||
样品1 | 0.3019 | 3.597 | 7.527 | 6.939 | 0.855 | 25 | 0.231 | 0.607 | 0.5744 | 0.0661 |
样品2 | 0.3129 | 1.116 | 3.969 | 3.274 | 0.586 | 25 | 0.0246 | 0.302 | 0.261 | 0.0423 |
样品3 | 0.3239 | 4.820 | 12.785 | 2.043 | 1.049 | 25 | 0.31 | 0.972 | 0.157 | 0.0766 |
6、 总结
本文通过利用EXCEL 的VBA函数,实现检测结果快速修约,保留有效位数,在食品检测领域能够有效提高数据处理效率,减少人为错误。不足之处在于当修约数据修约后末尾为0时,小数点后0不会显示,需手动添加。对于该问题还需进一步研究。