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

浏览 |回复22 电梯直达
bing031
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
该帖子已被新官人设置为精华; 奖励积分记录: 新官人(100分)

用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不会显示,需手动添加。对于该问题还需进一步研究。

为您推荐
新官人
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
bing031
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
原文由 新官人(p3299836) 发表:
很实用的原创文章!
谢谢,希望和大家多多交流
uil1010
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
之宣
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
十七
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
windlfr
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
skytoboo
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
你在函数里添加单引号,把当前格式定义为文本即可。

VBA不怎么会用,但是EXCEL输入单引号是可以定义为文本的,函数命令应该是可以的。这样0就会自动显示了。

"'" & 当前计算公式
赞贴
0
收藏
0
拍砖
0
2021/1/5 8:57:13 Last edit by skytoboo
Methonal
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
很实用,需要学习学习,用起来应该很方便。
nemo001
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵
挺简单的,不过一般都是用很长段的excel自带函数实现这个功能的,涉及需要验证的excel,一般不想用自定义函数
柳柳之枫
结帖率:
100%
关注:0 |粉丝:0
新手级: 新兵