Excel在测量不确定度评定中的应用

Excel 在测量不确定度评定中的应用

The Use of Excel in Evaluation of Uncertainty in Measurement 

(250002) 山东电力研究院　范巧成　汪东军

摘　要　将Excel 用于测量不确定度的评定中,来实现实验标准偏差的计算、对各不确定度分量的汇总、合成标准不确定度及其有效自由度和扩展不确定度的计算,进而利用设计好的Excel 表格来实现对任意一个测量结果的扩展不确定度的计算。关键词　Excel 　测量不确定度评定中图分类号:TM930. 115 　　　　文献标识码:B 　　　　文章编号:1007 -9904(2003)01 -0010 -05 

　　在使用Word97 编写《测量不确定度的评定报告》时,如何将Excel 表格插入Word 文档,来实现对各不确定度分量的汇总,如何利用它的计算功能来实现实验标准偏差的计算、测量结果的合成标准不确定度及其有效自由度和扩展不确定度的计算,如何利用设计好的Excel 表格来实现对任意一个测量结果的扩展不确定度的计算,以下将作详细介绍,同时介绍一些处理技巧。

1 　Excel 在合并样本标准差计算中的应用

在测量不确定度的A 类评定中,我们若用白塞尔公式来计算实验标准差,通常是使用计算器, 此时最讨厌的事就是将数据输错。若用Excel 来完成实验标准差的计算,特别是对于规范化的常规测量,我们还可以求得一个合并样本标准差,将非常直观方便,它可以直观地检查每个数据是否输错,如在某单元格中将0. 0155 输成0. 155 是很容易检查出来的, 若是在计算器里将很难发现。Excel 表格的插入和设计步骤如下: 

(1) 在Word 文档下插入一个Excel 表格

将光标移到要插入的位置,单击菜单栏的“插入(I) ”出现子菜单,单击“对象(O). . . ”,在“对象类型(O) :”中单击“Microsoft Excel 工作表”,单击“确定”按钮,一个Excel 表格就被插入到了Word 文档里。

(2) Excel 表格内数据的输入

先选中整个工作表,对齐方式设置为居中。在B1～K1 单元格中输入测量次数1～10 , 在L1 单元格中输入实验标准差的符号si ,在A2～A5 单元格中输入数据组数1～4, 在B2 : K5 单元格区域中输入所测得的4 组数据。

(3) 实验标准差计算公式的输入

在L2 单元格中输入计算第1 组数据实验标准差的公式: 单击L2 单元格, 输入“= STDEV (B2 : K2) ”,按回车键,从L2 单元格拖动填充柄至L5 , 即可获得4 组数据的实验标准差。单击M2 单元格, 输入“=L2 3 L2”或“=L2^2”,按回车键,从M2 单元格拖动填充柄至M5 , 即可获得4 个实验标准差的平方,为计算合并样本标准差作准备。

(4) 合并样本标准差的计算公式的输入

合并样本标准差按sp= 1 Σsi 2 计算, 将

mim=1 

A6 、B6 和C6 单元格合并及居中并输入“合并样本

标准差sp= ”,设置为右对齐,在D6 单元格中输入“= SQRT(SUM(M2 :M5)Π4) ”,按回车键。

这样,一个计算实验标准差和合并样本标准差

的Excel 表格就基本作好了,如图(1) 所示。为了美

观,需要加上表格线和调整显示窗口。调整显示窗

口只显示A1～L6 单元格区域,调整的方法是: 将

鼠标指向图(1) 下边中间的小黑方块(如尖头所

指),当出现上下方向的尖头时,点击并按住鼠标, 

即可上下调整显示窗口,同样将鼠标指向图(1) 右

边中间的小黑方块,当出现左右方向的尖头时,点

击并按住鼠标,即可左右调整显示窗口。

　　将鼠标指向Excel 表格以外的地方,按左键即

可退出Excel , 出现表(1) 的效果。以后若再对该表

格进行修改编辑,则双击表(1) 即可。

10 

© 1995-2007 Tsinghua Tongfang Optical Disc Co., Ltd. All rights reserved. 

SHANDONGDIANLIJISHU 2003 年第1 期(总第129 期) 

图(1) 表(1) 

2 　Excel 在不确定度分量的汇总及扩展不确定度计算中的应用

　　下面以一种由高稳定度恒流源、标准电阻和直流数字电压表组成的测量系统,对某直流电阻进行测量时的测量不确定度评定为例,介绍Excel 在不确定度分量的汇总及扩展不确定度计算中的应用。

2. 1 　测量原理

这种直流电阻测量的原理是将标准电阻RN 和被测电阻RX 串联,并通一恒定电流,利用直流数字电压表测量它们上的压降UN 和UX ,则被测电阻为: 

RN

RX = UX

UN 　　当调定的电流使得UN 与标准电阻的实际值RN ( RN = R20 [1 + α( t -20) + β( t -20)2], t 为环境温度)“一致”时(如标准电阻实际值为99. 997 4 

© 1995-2007 Tsinghua Tongfang Optical Disc Co., Ltd. 

Ω时,数字电压表读数UN 为0.999 974 V) ,此时UN ΠRN 的数值为10n , n 为整数, 因此, RX 的数值为:{ RX }={ UX } ×10 n ,从而实现被测电阻的直读。

测量不同阻值范围时,为了提高测量准确度, 选用的标准电阻、恒流源量程和数字表量程会有所不同。

12. 2 　测量不确定度评定

22. 2. 1 　数学模型

在测量环境温度为(20 ±0. 5) ℃的条件下,由于温度测量不准对标准电阻的影响可忽略不计,数字表示值误差的温度影响也可忽略不计,则数学模型为: 

RN

RX = UX

UN 　　RN 、UN 、UX 彼此,按照JJ F1059 -1999 《测量不确定度的评定与表示》第6. 6 款的方法,采用相对标准不确定度按下式来计算RX 的相对标准不确定度: 

[ uc ( RX )ΠRX ]2 =[ u ( RN )ΠRN ]2 +[ u ( UX )ΠUX ]2 

+[ u ( UN )ΠUN ]2 

2. 2. 2 　各不确定度分量的评定下面以被测阻值范围在(104 ～105) Ω为例,进

All rights reserved. 

2003 年第1 期(总第129 期) SHANDONGDIANLIJISHU 

行各主要不确定度分量的评定。

①二等标准电阻的不确定度

a. 10 000 Ω二等标准电阻基本误差极限为±10 ×10 -6,即半宽值为10 ×10 -6 ,在此区间可认为

服从均匀分布( k =  3),则相对标准不确定度urel1 

= 10 ×10-6Π 3 。该标准电阻历年检定合格,其自由度估计为ν1= ∞。

b. 10 000 Ω二等标准电阻经上级传递合格, 证书给出传递扩展不确定度Urel =5 ×10 -6, k = 

2, 则相对标准不确定度urel2 =5 ×1026Π2 。该不确定度分量来源十分可靠,其自由度估计为ν2= ∞。

②恒流源的不稳定产生的不确定度

按恒流源说明书,其10 min 的电流稳定度为±2 ×10 -6,即半宽值为2 ×10 -6,在此区间可认为

服从均匀分布( k =  3),则相对标准不确定度urel3 

=2 ×10-6Π 3 。估计Δurel3Πurel3 = 0.2, 其自由度ν3 = 12 。

③标定电流时数字表的不确定度

按数字表在(23 ±1) ℃时的允许误差技术指标。

a. 在1V 量程测量1V 电压的允许示值误差为△N = ±(0.000 8 % ×1 V +0.000 15 % ×2V) = ±11μV, 其相对允许示值误差为±11 ×10 -6,即半宽值为11 ×10 -6,在此区间可认为服从均匀分布

( k =  3),则相对标准不确定度urel4 = 11 ×10-6

3 。估计Δurel4Πurel4 = 0.1, 其自由度ν4 =50 。Π 

b. 1081 在1V 量程时的输入阻抗Ri 大于10 000MΩ,在测量10 000Ω标准电阻上的电压时, 由输入阻抗引起的误差r = RΠRi = 10 000 ΩΠ 10 000MΩ=1 ×10 -6,即半宽值为1 ×10 -6,在此区间可认为服从均匀分布( k =  3),则相对标准不

确定度urel5 =1 ×10-6Π 3 。估计Δurel5Πurel5 = 0.2, 其自由度ν5 =12 。

应当注意,该项不确定度分量在电阻≤10 000 Ω时可忽略不计,对于大于105 Ω的电阻,由输入阻抗引起的误差将大于10 ×10 -6,因此不宜用该法。

④测量UX 时数字表的不确定度

a. 在10 V 量程测量被测电阻上的电压值UX 12 

© 1995-2007 Tsinghua Tongfang Optical Disc Co., Ltd. 

时的允许示值误差为: 

△X = ±(0.000 8% ×UX + 0.000 1% ×20 V) 　　其相对允许示值误差为rx = ±(0.000 8% + 

11 % ×20ΠUX ), 即半宽值为(0.000 8% + 

21% ×20ΠUX ),在此区间可认为服从均匀分

布( k =  3), 则相对标准不确定度urel6 = (8+ 

20ΠUX ) ×10-6Π 3 。UX 在Excel 表格中将作为一个变

量。估计Δurel6Πurel6 = 0.1, 其自由度ν6 =50 。

b. 1081 在10 V 量程时的输入阻抗Ri > 10 000MΩ,在测量被测电阻上的电压值UX 时,由输入阻抗引起的误差r = RΠRi ={ UX } ×10 000 ΩΠ10 000MΩ={ UX } ×10-6 ,可认为服从均匀分布

( k =  3), 则相对标准不确定度urel7 ={ UX } ×

10-6Π 3 。估计Δurel7Πurel7 = 0.2, 其自由度v7 =12 。

由于考虑了电源稳定性,数字表的分辨力又足

够,对测量重复性的不确定度为简化计算可不考

虑。

2. 2. 3 　各标准不确定度汇总表、合成标准不确定

度及其有效自由度和扩展不确定度计算对上述各不确定度分量的评定结果汇总为图

(2) 所示的Excel 表格来计算各点的扩展不确定度。

合成相对标准不确定度按ucrel = Σu 2relj 计算。

4 有效自由度按νeff -ucrel 计算。

u 4relj

j =1 νj 

图(2) 所示的Excel 表格的插入方法和一些处理技巧,与前面介绍的实验标准差的计算中Excel 表格的处理相同,这里只介绍与本表有关的主要设计方法。该Excel 表格的设计方法是: 

(1) 插入一个Excel 表格。

(2) 将各列的名称、各不确定度的序号和来源、各不确定度分量的半宽值及其包含因子( k = 3 

的输入1. 732) 分别输入图(2) 所示的相应位置。根据前面对各不确定度分量的评定,其中C6 单元格中输入“= F10Π10000”,C7 单元格中输入“=8+ 20ΠC10”,C8 单元格中输入“= F10 3 C10Π10000”。

(3) 在E2 单元格中输入“= C2ΠD2”,从E2 单元格拖动填充柄至E8 , 即可获得各分量的相对标准不确定度。选中E2～E8 单元格调整小数位数为1 

All rights reserved. 

SHANDONGDIANLIJISHU 2003 年第1 期(总第129 期) 

位。

(4) 在F2～F8 单元格中分别输入各分量的自由度。

(5) 在G2 单元格中输入“= E2 3 E2 ”或

“= E2^2”,从G2 单元格拖动填充柄至G8 , 即可获得各相对标准不确定度分量的平方。

　　(9) 按前面合成相对标准不确定度的计算公式在C9 单元格中输入“= SQRT(SUM (G2:G8)) ”,设置为左对齐和1 位小数,即可获得合成相对标准不确定度。

(10) 按前面有效自由度的计算公式在F9 单元格中输入“= C9^4Π(SUM (I2 : I8) ) ”, 设置为左对齐

图(2) 

1(6) 在H2 单元格中输入“= G2 3 G2”,从H2 单元格拖动填充柄至H8 , 即可获得各相对标准不确定度分量的4 次方。

2(7) 在I2 单元格中输入“= H2ΠF2”,从I2 单元格拖动填充柄至I8 , 即可获得各输出标准不确定度分量的4 次方与自由度的商,其中在I2 和I3 单元格中输入“0”以解决自由度为∞的情况。

3(8) 将A9 和B9 、A10 和B10 、A11 和B11 、A12 和B12 、D9 和E9 、D10 和E10 、D11 和E11 单元格合并及居中并输入上表所示的内容,均设置为右对齐。

和无小数位,即可获得有效自由度。

1在F10 单元格中输入标准电阻的标称值, 在F11 单元格中输入调定的电流值。

2取置信概率p =95 %, 根据νeff ,查t 分布表得到k95 = t95 (νeff ) ,将此值输入E12 单元格。

3在C10 单元格中输入从被测电阻上测得的电压值。

4在C11 单元格中输入“= C10 3 F10”即可获得被测电阻值。

表(2) 　不确定度汇总计算表

序号不确定度来源半宽值( ×10 -6) kj urel j ( ×10 -6) νj 1 标准电阻年稳定性10 11732 518 ∞2 标准电阻校准不确定度5 2215 ∞3 恒流源的不稳定211732 112 12 4 标定电流时数表的允许误差11 11732 614 50 5 标定电流时数表输入阻抗引起的误差111732 016 12 6 测量UX 时数表的允许误差181011732 1014 50 7 测量UX 时数表输入阻抗引起的误差21011732 112 12 

计算得到的合成标准不确定度( ×10 -6) u crel 　= 13. 8 　　有效自由度νeff 　= 137 测量UX 时直流数字电压表读数(V) UX = 2. 000 56 标准电阻标称值(Ω) :10 000 被测电阻的实际值(Ω) 　RX = 20 005. 6 恒流源电流(mA) :011 测量结果的扩展不确定度( ×10 -6) U95 =27 k95 =1.984 

© 1995-2007 Tsinghua Tongfang Optical Disc Co., Ltd. All rights reserved. 

2003 年第1 期(总第129 期) SHANDONGDIANLIJISHU 

利用87C196CA 的EPA 实现等精度转速测量

An Implement of Rotate Speed Measurement by EPA in 87C196CA 

(250002) 山东电力研究院　丁卫东　石金宝　吕曦晨

摘　要　论述了用TN87C196CA 的EPA 实现四路转速测量的方法及实现过程,分析了高速测量时的误差控制办法,并给出了EPA 设定及EPA 中断服务等示范程序。关键词　TN87C196CA 　EPA 　转速Abstract 　The method of rotate speed measurement by EPA in TN87C196CA was supplied , and the implement procedure was de2 scripted. The accuracy controlling in high speed was analyzed in detail. Programs with EPA environment settings and EPA interrupt service routine were listed. Key words 　TN87C196CA 　EPA 　rotate speed 中图分类号:TM771 　　　　文献标识码:B 　　　　文章编号:1007 -9904(2003)01 -0014 -03 

1 　前言CAN 通信控制器的16 位单片机。它提供了六个INTEL87C196CA 是带有32K 片内EPROM 和EPA ( Event Processor Array) 通道,其中EPA0 到EPA3 

　　(15) 在C12 单元格中输入“C9 3 E12”,即可获得相对扩展不确定度Urel95 = k95ucrel ,调整为无小数位。

(16) 将C9～C12 、F9～F11 和E12 单元格设置为左对齐,将C2～C5 、F10 、F11 单元格设置为斜体, 将C10 、E12 单元格也设置为斜体并填充为灰-25 % 。

初步设计好的Excel 表格如图(2) 所示。加上表格线,调整显示窗口只显示A1～F12 单元格区域,将鼠标指向Excel 表格以外的地方,按左键即可退出Excel , 如表(2) 所示。

用鼠标指向该表,双击即可进入Excel 状态, 表中需要输入的数据只有C2～C5 、F10 、F11 (设置为斜体) 和C10 、E12 (斜体并填充为灰-25 %) 单元格,其中C2～C5 、F10 、F11 单元格中的数据对于选定的标准电阻、恒流源电流和数字表量程是不变的,此时只要在C10 单元格中输入被测电阻上测得的电压值,即可得到合成标准不确定度和有效自由度,根据选定的置信概率95 %, 查t 分布表得到: k95 = t95 (νeff 　),将此值输入E12 单元格,即可在

C12 单元格中获得测量结果的扩展不确定度U95 。对于其它阻值范围的测量,可根据选定的标准电14 

© 1995-2007 Tsinghua Tongfang Optical Disc Co., Ltd. 

阻、恒流源电流和数字表量程对Excel 表格中的相应数据和算法作适当修改即可。

此例中没有关于灵敏系数的处理,若某个评定方法中有灵敏系数,则只需在Excel 表格中的自由度一列前面增加一列灵敏系数和一列灵敏系数与标准不确定度分量的乘积(即输出标准不确定度), 如果灵敏系数是可变的,我们就在其所在单元格中输入一个公式。另外,当一旦设计了一个计算不确定度的Excel 表格,您就可以将它复制到另一个需要插入Excel 表格的地方,修改后得到另一个需要的Excel 表格,无须从头来过。

3 　结束语

通过前面的介绍,我们可以总结出:若某测量结果的不确定度随被测量的大小不同而不同,其测量不确定度评定方法可以从相关文献中获得,此时就可以根据这个评定方法和具体的测量条件,将各不确定度分量汇总为一个Excel 表格,将变化的不确定度分量与测量结果联系起来,输入不同的测量结果,即可得到最终测量结果的合成标准不确定度及其有效自由度和扩展不确定度,使给出每一个测量结果的不确定度变得简单易行。□

(收稿日期:2002 -07 -25) 

All rights reserved.