Excel在测量不确定度评定中的应用
The Application o f Excel in Evaluation of Measure U ncertainty
刘兴胜 陈 旭
(中国工程物理研究院电子工程研究所,四川绵阳621900)
摘 要:主要介绍了在使用Word编写测量不确定度的评定方法时,如何将Eexcel电子表格插入Word文档,举例分析如何利用它的计算功能来实现合并样本标准差的计算,如何实现测量结果的合成标准不确定度及有效自由度和扩展不确定度的计算。
关键词:Excel;不确定度;评定;电子天平;自由度
1 引言
作为计量校准人员,我们需要对测量结果的不确定度进行评定。当测量结果发生变化时,其测量不确定度也会发生变化,因此不确定度评定是一件很复杂的工作。我们若用贝塞尔公式来计算,通常是使用计算器,由于众多的按键操作,很可能把数据输错。若用Eecel来完成计算,将非常直观方便,它可以直观的检查每个数据是否输错,如在某个单元格中将25输成35是很容易检查出来的,若在计算器中将很难发现。现在利用Excel软件,设计了一个简单的电子表格来辅助计算,使问题大大简化,只要在设计好的Excel电子表格中输入测量结果,便立刻得到测量结果的合成不确定度及有效自由度和扩展不确定度。
2 Eecel电子表格的设计步骤
Eecel电子表格的设计步骤如下:
(1)将光标移到要插入的位置,执行 插入菜单中的 对象命令,在 新建对象类型选项中单击 Microsoft Excel工作表,而后单击确定,一个Excel电子表格就被插入到Word文档里。
(2)假如在A1~H6单元格中输入了数据,为了美观,需要加上边框和调整显示窗口。选中A1~H6单元格内区域加上边框,调整显示窗口只显示A1~H6单元格区域,方法是:将鼠标指向下边中间的控点,当出现上下方向的箭头时,点击并按住鼠标,即可上下调整显示窗口,同样将鼠标指向右边中间的控点,当出现左右方向的箭头时,点击并按住鼠标,即可左右调整显示窗口。
(3)将鼠标指向Excel电子表格以外的地方,按左键即可退出Eecel。以后若再对该表格进行修改编辑,双击表即可。
3 实例分析
下面以发表在计量与测试技术2009年第6期上的文章!电子天平示值误差测量结果不确定度评定?一文为例详细分析,因为该文具有普遍的代表性,既有合并样本标准差的计算,又在一个标准不确定度中包含两个不同的分量。
3 1 重复性合并样本标准差的设计表格
(1)首先在A1单元格中输入1,按住ctrl键的同时拖动填充柄至A10,则可直接输入10个数值。在B1~B10单元格中输入10次测量值。在B11单元格中输入计算第1组数据的实验标准差,输入 =STDEV(B1:B10),按回车键,即可得到第1组数据的实验标准差。
(2)由于原来的单位是g,文中单次测量的标准差单位是mg,所以在B12单元格中输入 =B11*1000,在C12,D12分别输入第二组,第三组实验标准差。
(3)单击B13单元格,输入 S(m1)=B12^2,按回车键,从B13单元格拖动填充柄至D13,即可获得3次实验标准差的平方。
(4)在E12单元格中输入 S(m1)=,在F12单元格中输入 =SQRT(SUM(B13:D13)/3),按回车键,可得到合并样本标准差。
(5)在E13单元格中输入 u(m1)=,因为实际在电子天平的校准中,每次测量的重复性为6次,所以在F13单元格中输入 =F12/SQRT(6),按回车键,可得到测量重复性的不确定度分量。
(6)选中A1~F13单元格区域,设置为居中对齐并加上边框,调整显示窗口只显示A1~F13单元格区域,将鼠标指向Eecel电子表格以外的地方,单击即可退出Eecel。
这样,一个计算合并样本标准差的Eecel电子表格就设计好了,如表1所示。
文中第2,3组数据省略了,实际操作时,我们可以在C1~C10输入第2组数据,在D1~D10输入第3组数据,从B11拖动填充柄至D11,得到3组数据的实验标准差。
62
!计量与测试技术?2011年第38卷第3期
再从B12拖动填充柄至D12,可得单位换算后3组数据的实验标准差。
表1 合并样本标准差的设计表格
1199 9999
2199 9999
3199 9998
4199 9998
5200
6199 9999
7199 9999
8199 9998
9199 9999
10200
7 4E-05
0 0740 070 076S(m1)=0 073
5 44E-034 90E-035 78E-03u(m1)=0 03
3 2 各不确定度分量汇总的设计表格
(1)在A1~H1单元格中分别输入各列的名称或符号。
(2)在A2~A4单元格中分别输入各标准不确定度来源的序号。
(3)在B2~B4单元格中分别输入各标准不确定度来源。
(4)在C2~C4单元格中分别输入标准不确定度的值。
(5)在D2~D4单元格中分别输入灵敏系数的值。
(6)在E2单元格中输入 =C2*D2,从E2单元格中拖动填充柄至E4,即可获得各标准不确定度与灵敏系数的乘积。
(7)在F2~F4单元格中分别输入各不确定度的自由度。
(8)在G2单元格中输入 =E2^2,从G2单元格中拖动填充柄至G4,即可获得各标准不确定度与灵敏系数的乘积的平方。
(9)在H2单元格中输入 =E2^4/F2,从H2单元格中拖动填充柄至H4,即可获得各标准不确定度与灵敏系数的乘积的4次方再与自由度的商,其中在H2单元格中输入 0以解决自由度为#时的情况。
(10)在A5单元格中输入 u(m)=,在B5单元格中输入 =SQRT(SUM(G3:G4)),即可获得u(m)的值。
(11)在C5单元格中输入 v(m)=,在D5单元格中输入 =B5^4/SUM(H3:H4),即可获得u(m)的有效自由度。
(12)在A6单元格中输入 u c( m)=,在B6单元格中输入 =SQRT(SUM(G2:G4)),即可获得合成标准不确定度。
(13)在C6单元格中输入 v eff=,在D6单元格中输入 =B6^4/(SUM(H2+B5^4/D5)),即可获得合成标准不确定度的有效自由度。
(14)在C7单元格中输入 k95=,根据选定的置信概率95%,查t分布表将有效自由度近似取为#得到: k95=t95=1 960,将此值输入到D7单元格中,在A7单元格中输入 U95=,在B7单元格中输入 =B6*D7,即可在B7单元格中获得测量结果的扩展不确定度U95。
(15)选中A1~H7单元格区域,设置为居中对齐并加上边框,调整显示窗口只显示A1~H7单元格区域,将鼠标指向Eecel电子表格以外的地方,单击即可退出Ee cel,如表2所示。
如果这三个分量彼此不相关,则第5行可以省略,在D6单元格中输入 =B6^4/(SUM(H2:H4)),可获得合成标准不确定度的有效自由度。
为了使数据尽可能准确,开始取的小数位数应尽可能多一些,最后再根据实际情况进行取舍,本文中的小数位数都经过取舍。以后要计算不确定度时,只需分别在C2、C3、C4单元格中输入相应的数值,即刻在B6和D6单元格中得到u c( m)和v e ff,查表得k95,输入到D7单元格中,可在B7单元格中得到U95。
表2 各不确定度分量汇总的设计表格
序号不确定度来源u i c i u i c i v i(u i c i)2(u i c i)4/v i 1标准砝码0 165-1-0 165#0 0272250
2测量重复性0 0310 03270 00090 00000003 3天平分辨力0 0310 03500 00091 62E-08
u(m)=0 0424v(m)=70
u c( m)=0 170367V eff=18235
U95=0 33k95=1 960
4 结论
测量不确定度评定的表格到此全部完成,看似复杂,其实它具有直观性和更大的灵活性。本文只是提供了一种思路,评定者可以方便的根据实际情况增加或删去某些分量,就能立即得到新的不确定度评定结果,对给出任意一个测量结果的不确定度评定都能够轻松应对,大大减轻了我们的工作量,同时避免了计算器使用时众多按键操作带来的按键错误。
参考文献
[1]张戈.电子天平示值误差测量结果不确定度评定.计量与测试技术,2009(6).
[2]范巧成.计量基础知识.北京:中国计量出版社,2004.
[3]杜茂康.Excel与数据处理.北京:电子工业出版社,2005.
作者简介:刘兴胜,男,高级工程师。工作单位:中国工程物理研究院电子工程研究所。通讯地址:621900绵阳市919-523信箱。
陈旭,中国工程物理研究院电子工程研究所(绵阳621900)。
收稿时间:2010-10-29
刘兴胜等:Excel在测量不确定度评定中的应用 63