用EXCEL自定义公式解决食品检测中有效位数和四舍六入的问题

用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自己的计算公式计算，数据要逐个查看，保留有效位数和修约，表二是通过我们的自定义函数，能够自动修约，保留有效位数。对于大批量的数据，能够有效避免人为出错，节约时间。

表一

表二

6、 总结

本文通过利用EXCEL 的VBA函数，实现检测结果快速修约，保留有效位数，在食品检测领域能够有效提高数据处理效率，减少人为错误。不足之处在于当修约数据修约后末尾为0时，小数点后0不会显示，需手动添加。对于该问题还需进一步研究。