利用Excel编制测量不确定度计算表格的方法与使用

闫立新　呼　和　刘晓飞　孙岩峰

(内蒙古自治区计量测试研究院，呼和浩特 010020)

0　引言

在计量检测校准工作中，大量常见计量器具的测量不确定度已被分析得非常透彻，或有经典的套路可参考，但是对每一个具体的计量器具，往往最棘手的是测量不确定度评定的计算过程比较繁琐。我们日常工作中常用的Excel表格软件，非常适合用于计量检测数据的计算处理。我院在近年的计量检测实践中，摸索出了一套符合国家计量技术规范JJF 1059—2012《测量不确定度评定与表示》和各计量专业检定规程中要求的，用于测量不确定度评定计算的电子表格编制方法，取得了很好的效果。为了使广大计量工作者能全面准确地了解并能熟练使用该方法，本文就该电子表格的编制方法与使用进行了详细介绍，供广大计量工作者参考。

1　测量不确定度评定计算流程

测量不确定度评定过程如下：

1)建模分析。建立不确定度数学模型,分析有哪些不确定度分量，各个不确定度分量构成、性质(A类或B类) ，确定灵敏系数。

2) A类、B类标准不确定度的评定计算。

3)计算合成标准不确定度、有效自由度、包含因子k、计算扩展不确定度。

4)给出测量结果不确定度报告。

2　用到的Excel中的函数简介

1)SUM (number1,number2, ...)返回某一单元格区域(1到30个)中所有数字之和，语法：

= SUM(number1,number2, ...)

2)STDEV(number1,number2,...)估算样本(1到 30个)的标准偏差(对应于贝塞尔公式)，反映相对于平均值的离散程度，语法：

=STDEV(number1,number2,...)

3)CUNT(value1,value2,...)返回包含数字以及包含参数列表(1到30个)中有数字的单元格的个数，该函数可以计算单元格区域或数字数组中数字字段的输入项个数,语法:

=COUNT(value1,value2,...)

只有数字类型的数据才被计算(计数)。

4)TINV(probability,degrees of freedom)作为概率和自由度函数, 返回学生t分布的t值。probability：对应于双尾学生t分布的概率，具体使用时=(1-置信水准(p<1))。Degrees of freedom ：分布的自由度。语法：

=TINV(1-置信水准(p<1),自由度)

5)ABS(number) 返回数字的绝对值，语法：

=ABS(number)

6) ROUNDUP(number,num_digits) 离零值，向上舍入数字，语法：

=ROUNDUP(number,num_digits)

number：为需要向上舍入的任意实数。

num_digits：四舍五入后的数字位数

7)IF(logical_test,value_if_true,value_if_false) 执行真假值判断，根据逻辑计算的真假值，返回不同结果，可以进行七层嵌套，用于对数值和公式进行条件检测，语法(此函数的用法必须熟练掌握)：

= IF(logical_test,value_if_true,value_if_false)

logical_test ：表示计算结果为 TRUE 或 FALSE 的任意值或表达式、单元格引用。

value_if_true：logical_test 为 TRUE 时返回的值，也可以是其他公式。

value_if_false：logical_test 为 FALSE 时返回的值，也可以是其他公式、单元格引用、嵌套。

还有一些本文用到的Excel基本函数和工作表、单元格之间计算引用、条件格式基本用法、单元格限定数值列表基本用法等技巧，在进行编制测量不确定度计算表前，均应熟悉并掌握。

3　编制基本过程

下面以中国计量出版社出版的《测量不确定度评定与表示指南》第十章测量不确定度应用实例十一、声学测量中的应用(P117)为例，阐述毫瓦级超声功率计示值误差测量不确定度计算电子表格的编制方法与过程。

3.1　建模分析

根据具体计量器具的数学模型的测量不确定度分析，确定各个不确定度分量构成、性质(A类或B类)，确定灵敏系数，此步骤决定计算表格编制的复杂程度(一般A类1个，B类4个，可满足常见计量器具测量不确定度分析评定计算)。

3.2　标准不确定度的评定计算表格编制

按图1编制电子表格，主要是设置单元格格式(列宽、行高、格式)，在单元格内输入相应项目名称、常用数据、函数公式及引用，单元格的背景颜色可根据个人喜好设定成不同颜色的，每次使用时需要重新录入数据的，设定为白色。

图1　毫瓦级超声功率计示值误差测量不确定度计算表

3.2.1A类标准不确定度的计算表格编制

A类标准不确定度的计算主要是对测量结果数列进行平均值计算、平均值“修约化整”、实验标准偏差的计算、标准不确定度和自由度的计算，具体见图1(A1:S4)部分，具体步骤如下：

1)单位引用：在R1中输入“单位”，在S3中输入“=R1”，在S6中输入“=R1”、在S12中输入“=R1”。

2)自由度：在N3中输入“=COUNT(D3:M3)-1”。此公式的作用是对有数据的单元格进行计数，然后再减去1计算出自由度。

3)平均值：在O3中输入“=SUM(D3:M3)/(N3+1)”，此公式的作用是对D3:M3单元格中的测量数据求和再除以(自由度+1)，计算测量数据列的平均值。

4)化整平均值：在P3中输入平均值的“修约化整”值，此处可用VBA编制函数模块进行自动计算[3]。

5)标准偏差：在Q3中输入“=IF(N3=3,STDEV

(D3:G3),IF(N3=4,STDEV(D3:H3),IF(N3=5,STDEV(D3:I3),IF(N3=6,STDEV(D3:J3),IF(N3=7,STDEV(D3:K3),IF(N3=8,STDEV(D3:L3),IF(N3=9,STDEV(D3:M3),"数据必须大于4")))))))”，此条件判断公式的作用是根据N3中自由度的值，选择单元格进行实验标准偏差s(x)的计算。

6)不确定度：在R3中输“=Q3/((N3+1)^0.5)”，此公式的作用是根据对“自由度+1”计算数据个数，再平方根后被标准偏差除，得测量数列的标准不确定度。

7)(格式准则)Gσ×s：在Q4中输入“=IF(N3=2,"不能判断",IF(N3=3,Q3*1.463,IF(N3=4,Q3*1.672,IF(N3=5,Q3*1.822,IF(N3=6,Q3*1.938,IF(N3=7,Q3*2.032,IF(N3=8,Q3*2.11,IF(N3=9,Q3*2.176,"超出范围"))))))))” ，此条件判断公式的作用是进行单元格内格拉布斯准则的临界值与标准偏差的乘积计算。

8)在相应单元格(D4:M4)中设置公式和条件格式用于异常数据判断显示: 在D4中输入“=IF(D3="","",ABS (D3-$P$3))”，然后选择D4填充至M4即可，如果D4至M4的10个单元格中的数据异常(≥Q4中的数据)，在D4至M4的10个单元格中的背景色显示红色，否则显示绿色。

设置单元格条件格式方法：单击选定单元格D4→菜单栏“格式(O)”→条件格式(D)→设置条件1(1)→设置条件2(2)；单击条件格式的“格式”按钮用于设定条件为真时，待用格式的形式→图案→颜色(红、绿)；然后选择D4填充至M4。条件格式设置窗口如图2所示。

图2　条件格式设置窗口

3.2.2B类标准不确定度计算表格编制

B类标准不确定度的计算主要根据输入的灵敏系数ci、不确定度区间的半宽度a、假设不确定度的概率分布、置信水平p等，估计置信因子k和根据“Δu(xi)/u(xi)”估计自由度，计算标准不确定度ub=a/k，见图1(I8:O12)部分，具体步骤：

1)在单元格内输入常用资料数据，用于单元格限定数值列表，具体见图1(A5:E12)部分。

2)在编制好的电子表格中设置“概率分布p”，单元格限定数值列表和“置信因子k值”条件判断公式，具体：选择J8→单击菜单栏“数据(D)”→“有效性(L)” →设置→允许(A)→序列→勾选忽略空值(B)和提供下拉箭头(I)→来源(S)=限定数值单元格列($A$6:$A$12)；然后在单元格K8内输入“=IF(J8="正态",1.96,IF(J8="均匀",1.732,IF(J8="三角",2.449,IF(J8="反正弦",1.414,IF(J8="两点",1,"自己设置")))))”，选定单元格K8填充至K12。

3)在编制好的表格中设置“Δu(xi)/u(xi)”单元格限定数值列表，然后 “估计自由度”条件判断公式，在单元格O8内输入“=IF(N8= $D$6,$F$6,IF(N8=$D$7,$F$7,IF(N8=$D$8,$F$8,IF(N8=$D$9,$F$9,IF(N8=$D$10,$F$10,IF(N8=$D$11,$F$11,IF(N8=$D$12,$F$12,A))))))) ”，再填充至O12。

4)不确定度：在M8中输“=ABS(I8)*L8/K8”，再填充至M12，此公式的作用是计算B类不确定度分量的标准不确定度。

3.2.3扩展不确定度计算表格编制

1)设置单元格格式(列宽、行高、格式)，在单元格输入项目名称、公式、引用，具体见图1(P5:S12)部分。

2)合成不确定度：在R6中按合成不确定度公式(相关、不相关)，进行相应单元引用计算，这里以不相关为例，在R6中输入“=(R3^2+(M8)^2+(M9)^2+(M10)^2+(M11)^2+(M12)^2)^0.5”，在S6中输入“=S4”，进行计量单位引用。

3)有效自由度：在R7中按韦尔奇一萨特思韦特(Welch-Satterthwaite)公式，进行相应单元引用计算，在R7中输入 “=(R6^4)/((R3^4)/N3)+((M8^4)/O8)+((M9^4)/O9)+((M10^4)/O10)+((M11^4)/O11)+((M12^4)/O12)”。

4)取置信水准p(1>p>0)，设置R8单元格的格式为数字百分比。

5)包含因子：在R10中输入“=IF(R7>10000,ROUND((TINV(1-R8,10000)),S9),ROUND((TINV(1-R8,R7)),S9))”，此公式的作用是当有效自由度大于10000，调用函数TINV(1-R8,R7)，按10000计算包含因子k值，并根据保留位数S9设定的值用ROUND对数据4舍5入；当有效自由度小于10000，按实际值计算包含因子k值，并进行数据4舍5入。

6)扩展不确定度：在R12中输入“=ROUND UP(R10*R6,S11)”，此公式的作用是用包含因子R10×合成不确定度R6，然后用函数ROUNDUP对S11单元格设定的有效位数进行舍入(只进不舍)。

7)相对扩展不确定度：在R13中输入“=ROUNDUP((R12/O3),2)，此公式的作用是用扩展不确定度÷化整平均值P3，然后用函数ROUNDUP对设定的有效位数进行舍入(只进不舍)。

3.2.4测量结果报告表格编制

在E13中输入="显示值/指示值 = "&Q1&S3&",实际测量值 = "&P3&S12&"："&"U ="&R12&S12&"；Ur ="&TEXT(R13,"0.0%")&"；k ="&TEXT(R10," 0.00")&" ，此公式的用途是用“&”连接文本和单元格引用(调整TEXT内的参数可设定小数后的有效位数)，输入数据后自动生成测量结果不确定度报告,从而完成整个测量不确定度的计算过程。

4　使用方法说明

掌握Excel的基本操作，熟悉测量不确定度评定过程和涉及的基本公式及定义，就能熟练运用本文介绍的方法编制出你想要的测量不确定度计算表格。本文给出的编制案例基本可满足95%以上的常见计量器具的测量不确定度的评定计算，对于比较复杂数学模型(有相关性要求)的测量不确定的计算，可直接修改图1表格的单元格中的公式即可，但要注意相关单元格中公式的绝对引用的修改。

5　结束语

本文给出计量数据测量不确定度计算电子表格的编制方法，希望能对从事计量检定、检测的同志有所帮助，有不妥之处欢迎广大同行批评指正。

[1]JJF 1059—2012测量不确定度评定与表示

[2]国家质量技术监督局计量司主编.测量不确定度评定与表示指南.北京：中国计量出版社，2005

[3]闫立新,呼和,张建平.测量数值修约化整程序的编制方法及使用. 中国计量,2013(4)

[4]刘翠萍. 浅谈Excel软件在计量管理及计量检定中的应用.计量技术,2003(7)

[5]李拥军，李献波，徐建民.EXCEL模板在数据处理中的应用.计量技术,2001(10)

[6]陈德恩，黄太柱，马冬晨.EXCEL在有效数字运算中的应用.计量技术,2006(11)