基于EXCEL的微小厚度测量结果不确定度的评定

曾令辉

(北京理工大学,北京　102488)

基于EXCEL的微小厚度测量结果不确定度的评定

曾令辉

(北京理工大学,北京102488)

摘 要：大学物理实验中，测量值的不确定度评定成为测量系统最基本也是最重要的特征指标。EXCEL作为一款数据处理软件，具有强大的数据分析、处理功能。本文就劈尖干涉实验采集的原始数据使用EXCEL进行数据分析，给出了实验结果不确定度评定，编译制作了一款微小厚度测量结果不确定度的评定软件，为大家处理实验数据提供方便。

关键词：劈尖干涉；不确定度；EXCEL

任何测量都存在误差,由于测量的客观真值无法得知及测量条件的非理想化,使误差大小无法确定。测量的结果只能说是在一定的范围内是可信的，因此,测量结果的质量如何,测量结果是否有用,在很大程度上取决于其不确定度的大小。所以,测量值的不确定度评定成为测量系统最基本也是最重要的特征指标,是测量结果质量的定量评定,同时对各项检测检验工作也具有重要的意义。国际计量委员会通过的《实验不确定度的说明建议书INC-1(1980)》建议用不确定度(uncertainyt)取代误差(error)来表示实验结果,并按其性质将不确定度从估计方法上分为按统计分布的A类不确定度和按非统计分布的B类不确定度两类,分别进行处理后再进行合成。从而使得“由于测量误差的存在而对被测量值不能确定的程度”得到更科学的评估。

1问题的提出

Excle作为一个数据处理软件，它的函数功能非常强大，而且其操作界面友好，使用方便，与其它软件的兼容性好，尤其它的用户群体非常庞大。本文以“劈尖干涉测量微小厚度”测试结果不确定度的评定为例，阐述基于EXCEL软件评定不确定度的方法，给出一个实用的“劈尖干涉测量微小厚度”测量结果处理模块。

图1　劈尖干涉前面装置侧视图

图2　劈尖干涉条纹

即:D=(XLλ)/(2Lx)。

测量原始数据见表1。

表格1　劈尖干涉原始数据

通常情况下，对以上数据进行处理，需要计算各项的平均值，计算各平均值的统计不确定度和非统计不确定度，还需给出测量结果的置信概率等等，计算工作量比较大，手工计算效率低。

2EXCEL评定不确定度实现

2.1建立EXCLE不确定度评定系统

如图3所示，在新建的EXCEL工作表的相应单元格中依次录入实验项目名称、实验者专业、班级、学号等信息，并留出相应的空单元格，以备实验者填入个人信息和实验相应的固定参数，在“实验日期”备填单元格中使用函数“=TODAY()”，同时，将实验的原始数据录入相应单元格中。在“劈长”列的M10单元格中，引用函数“=ABS(K10-E10)”，1～5组数据的该列依次引用相对地址自动填充[6-7]。在“连续n条亮纹”列的O10单元格中，引用函数“=ABS(I10-G10)”，1～5组数据的该列依次引用相对地址自动填充。在“平均值”行的E15单元格中，引用函数“=AVERAGE(E10:E14)”，该行各组数据的各个依次引用相对地址自动填充。按照这样的方法，在“平均值的统计不确定度”E16单元格引用函数“=STDEV(E10:F14) /SQRT(5)”，该行各单元格相对引用自动填充。在“平均值的非统计不确定度”E17单元格引用函数“=$G$6/SQRT(3)”，该行各单元格相对引用自动填充。在“平均值的不确定度”E18单元格引用函数“=SQRT(E16^2+E17^2)”，该行各单元格相对引用自动填充。在“劈长的平均值”对应的“不确定度”I20单元格引用函数“=SQRT(E18^2+K18^2)”，对应的“相对不确定度”K20单元格引用函数“=I20/G20”。在“连续n条亮纹长度的平均值”对应的“不确定度”I21单元格引用函数“=SQRT(G18^2+ I18^2)”，对应的“相对不确定度”K21单元格引用函数“=I21/G21”。在“样品厚度的平均值”对应的“平均值”G22单元格引用函数“=$O$6*$K$6 /2*G20/G21*10^-3”，对应的“不确定度”I22单元格引用函数“=G22*K22”，对应的“相对不确定度”K22单元格引用函数“=SQRT(K20^2+K21^2)”。在统计表的有效区域外围，使用一些单元格作为辅助计算的单元格。如，Q4中记录有效数字位数“2”，S2中引用G22的结果，与之雷同，S3=I22,S5=1,R6=E22。在R8单元格引用函数“=IF(INT(S5)=1,0.683,IF(INT(S5)=2,0.954,IF(INT(S5)=3,0.997,-1)))”，在S9单元格引用函数“=S3*INT(S5)”，在S10单元格引用函数“=INT(LOG10(ABS(S9)/9.5)+ 2-Q4)”，在S11单元格引用函数“=INT(LOG10(ABS(S2)/9.5)+2-S5)”在S15单元格引用函数“=ROUND(S2,-S10)/10^S10”，在S16单元格引用函数“=ROUND(S9,-S10)/10^S10”，在S17单元格引用函数“=IF(AND(S10>-7,S10<0,S11>-3),0,3*INT((S10+2)/3))”，在S18单元格引用函数“=S15/ 10^ABS((S17-S10))”，在S19单元格引用函数“=S16/10^ABS((S17 -S10))”，在S21单元格引用函数“=IF(ABS(S9/S2)>4.9,"误差太大",IF(S17=0,CONCATENATE("(",FIXED(S18,ABS(S17-S10)),"±",FIXED (S19,ABS(S17-S10)),")",R6,"(P=",FIXED(R8,3),")"),CONCATENATE("(",FIXED(S18,ABS(S17-S10)),"±",FIXED(S19,ABS(S17-S10)),") x10^",TEXT(S17,0),R6,"(P=",FIXED(R8,3),")")))”。

图3　数据处理模块

2.2部分函数使用说明

1.“=AVERAGE(M10:M14)”，返回参数的算术平均值，在此给出劈长的测量平均值。

2．“=STDEV(M10:N14)/SQRT(3)”，STDEV(M10:N14)用来估算样本的标准偏差，函数 STDEV 的计算公式如下：

3.“=IF(ABS(S9/S2)>4.9,"误差太大",IF(S17=0,CONCATENATE("(",FIXED(S18,ABS(S17-S10)),"±",FIXED(S19,ABS(S17-S10)),")",R6,"(P=",FIXED(R8,3),")"),CONCATENATE ("(",FIXED(S18,ABS(S17-S10)),"±",FIXED(S19,ABS(S17-S10)),") x10^",TEXT(S17,0),R6,"(P=",FIXED(R8,3),")")) )”，FIXED函数用以将数字按指定的小数位数进行取整，利用句号和逗号，以小数格式对该数进行格式设置，并以文本形式返回结果，CONCATENATE函数用以将几个文本字符串合并为一个文本字符串。IF函数执行真假值判断，根据逻辑计算的真假值，返回不同结果。整体表达式用以显示测量结果的最终值，其中包括置信概率值P。

4.“=IF(INT(S5)=1,0.683,IF(INT (S5)=2,0.954,IF(INT(S5)=3,0.997,-1)))”,根据单元格S5的值，评定置信概率P的值。

5.“=INT(LOG10(ABS(S9)/9.5)+2 -Q4)”,INT函数为取整函数，用以将数字向下舍入到最接近的整数，LOG10是常用对数函数。该表达式根据用户设定的“有效数字位数”，计算出一个中间量，其它单元格数据计算时使用。

2.3制作数据处理模块

如图3所示，锁定EXCEL表中不需要用户改变内容的单元格，隐藏表的有效区域外围单元格内容。修饰表的有效区域，如增加边框、填充背景色，改变字形，字的颜色等等。最后给出用户使用的“劈尖干涉”实验数据处理软件，该软件不仅能计算实验数据的平均值，给出实验值，还能统计实验误差，并在统计过程中剔除无效数据，给出实验结果的不确定度。使用中，用户只需在黄色字体的位置输入实验中采集的原始数据即可得到完整的统计结果。

3结语

本文给出的软件，界面友好，使用方便，计算科学，在劈尖干涉实验测量结果的不确定度评定中使用该软件可以大大方便和简化计算，降低工作量，也有利于不确定度评定工作的普及。

参考文献：

[1]蒋贵芳. 一级平面等厚干涉仪示值误差的测量不确定度分析[J].计量与测试技术,2014,41(4):81-82.

[2]魏滨,王勤,殷茂荣.EXCEL在不确定度评定中的应用[J].中国卫生检验杂志，2011,21(4):1017-1019.

[3]范巧成.EXCEL在测量不确定度评定中的应用[J].理化检验化学分册,2009,45(8)：931-935.

[4]冉庆，陈永康，李建民，曹健等.JJF1100 -2003《平面等厚干涉仪》校准规范.全国几何量工程参量计量技术委员会.

[5]李慎安，施昌彦，刘风等.《测量不确定度评定与表示》JJF1059-1999.全国法制计量技术委员会.

[6]胡澄，林上金.不确定度漫议——一个数据处理结果引发的讨论[J].大学物理实验，2013(4)：44-46.

[7]刘灿，李新.物理实验中不确定度教学难点的突破与思考[J].大学物理实验，2012(5)：55-57.

The Uncertainty Evaluation about Measured Value of Tiny Thickness Based on EXCEL

ZENG Ling-hui

(Beijing Institute of Technology,Beijing 102488)

Abstract：In the university physics experiment,the evaluation of uncertainty about Measured Value becomes the most basic and the most important characteristic in measuring system.As a data processing software,EXCEL has powerful functions in data analysis and processing.This paper analyzes the original data from Cleft tip interference experiment by EXCEL,gives the evaluation of uncertainty about Measured Value,compiles a software of the uncertainty evaluation about Measured Value of Tiny Thickness,and provides processing experimental data convenience for everyone.

Key words：cleft tip interference；uncertainty；EXCEL

收稿日期：2015-10-31

文章编号：1007-2934(2016)02-0114-04

中图分类号：G 642.474

文献标志码：A

DOI：10.14139/j.cnki.cn22-1228.2016.002.030