2《计量基础知识》8Excel在不确定度评定中的应用及实例07.6.6

第八章 Excel在测量不确定度评定中的应用 随着ISO/IEC17025 (GB/T 15481)《检测和校准实验室能力的通用要求》的实施，对校准和检测实验室在测量结果的不确定度评定方面提出了更高的要求。目前，已有许多测量不确定度评定实例在一些权威杂志中发表，也有一些关于测量不确定度评定实例的专著出版，也就是说怎样去评定已基本不成问题，但是要给出每一个测量结果的不确定度，普遍认为是很麻烦的一件事，并尽量回避。因为在大多数情况下，当测量结果的大小发生变化时，其测量不确定度也发生变化。此时要做到给出每一个测量结果的不确定度，需要进行大量的计算，若还要计算有效自由度，其计算量可想而知。通过 设计 领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计 一个十分简单的Excel电子表格来辅助计算，可使问题大大简化，甚至只要在设计好的Excel电子表格中输入测量结果，便立刻得到测量结果的合成标准不确定度及其有效自由度和扩展不确定度。 本章主要介绍在使用Word编写测量不确定度的评定方法时，如何将Excel电子表格插入Word文档，来实现对各不确定度分量的汇总，如何利用它的计算功能来实现实验标准偏差的计算、测量结果的合成标准不确定度及其有效自由度和扩展不确定度的计算，如何利用设计好的Excel电子表格来实现对任意一个测量结果的扩展不确定度的计算。同时介绍一些处理技巧。当然也可以不插入Word文档，并在一个Excel工作簿中，设计出若干个满足需要的Excel电子表格。 第一节 Excel在合并样本标准差计算中的应用 在测量不确定度的A类评定中，我们若用贝塞尔公式来计算实验标准差，通常是使用计算器，此时最不愿发生的事就是将数据输错。若用Excel来完成实验标准差的计算，将非常直观方便，它可以直观地检查每个数据是否输错，如在某个单元格中将0.0155输成0.155是很容易检查出来的，若是在计算器里将很难发现。特别是对于规范化的常规测量，例如：按检定规程进行的校准工作，车间中的在线抽检，某种产品中成分的抽样化验等，我们还可以求得一个合并样本标准差sp。合并样本标准差sp这一符号的下标正体小写p，来源于英文pooled一词，表示并非来自一个被测量的实验结果，但sp所给出的则仍为这一条件下单次测量结果的标准偏差。sp是根据多个被测量在重复性条件或复现性条件下重复观测所得测量结果，按统计方法计算出的一次测量结果的分散性标准偏差。采用sp的前提是：检测方法不变；整个过程处于正常情况，被测量值的大小变化对分散性不起主要作用，若作用明显，则应在不同的量值下获得对应的sp。由于sp的自由度一般可以比较容易地达到20以上，认为是相当可靠的，一般把它保留下来作为一种技术档案而用于今后的相同条件下测量结果(往往只重复2次、3次，甚至不重复)不确定度的评定。下面分2种情况介绍Excel在合并样本标准差计算中的应用。 2.1 通过重复性条件下的多次独立测量来获得合并样本标准差 在校准或检测中，要获得某量值下的实验标准差，可选择m个被测量，各进行n次独立观测，获得m组数据，通过这m组数据使用Excel电子表格来计算合并样本标准差sp。 Excel电子表格的插入和设计步骤如下： (1) 在Word文档下插入一个Excel电子表格 将光标移到要插入的位置，执行“插入”菜单中的“对象”命令，在“新建 对象类型”选项中单击“Microsoft Excel 工作表”，而后单击“确定”按钮，一个Excel电子表格就被插入到了Word文档里。如图8—1。 (2) Excel电子表格内数据的输入 先选中整个工作表，对齐方式设置为居中。在B1～K1单元格中输入测量次数1～10，在L1单元格中输入实验标准差的符号si，在A2～A5单元格中输入数据组数1～4，在B2～K5单元格区域中输入所测得的4组数据。其中，次数1～10和组数1～4，可用先输入1，而后用拖动填充的方法输入其它数。 图8—1 刚插入的电子表格 (3) 实验标准差计算公式的输入 在L2单元格中输入计算第1组数据实验标准差的公式：单击L2单元格，输入“=STDEV(B2:K2)”，按回车键，从L2单元格拖动填充柄至L5，即可获得4组数据的实验标准差。 单击M2单元格，输入“=L2*L2”或“=L2^2”，按回车键，从M2单元格拖动填充柄至M5，即可获得4个实验标准差的平方，为计算合并样本标准差作准备。 (4) 合并样本标准差计算公式的输入 合并样本标准差按 计算，式中m为数据组数。在A6单元格中输入“sp=”，设置为右对齐，在B6单元格中输入“=SQRT(SUM(M2:M5)/4)”，按回车键。 这样，一个计算实验标准差和合并样本标准差的Excel电子表格就基本设计好了，如图8—2所示。 图8—2 显示窗口调整前的电子表格 为了美观，需要加上边框和调整显示窗口。选中A1～L6单元格区域加上边框，调整显示窗口只显示A1～L6单元格区域，调整的方法是：将鼠标指向图8—2下边中间的控点(如尖头所指)，当出现上下方向的尖头时，点击并按住鼠标，即可上下调整显示窗口，同样将鼠标指向图8—2右边中间的控点，当出现左右方向的尖头时，点击并按住鼠标，即可左右调整显示窗口。将窗口调整成图8—3所示。 图8—3 显示窗口调整后的电子表格 将鼠标指向Excel电子表格以外的地方，按左键即可退出Excel，出现表8—1的效果。此时应注意识别表格，自左至右分别为第A～L列，自上至下分别为第1～6行。以后若再对该表格进行修改编辑，则双击表8—1即可。 表8—1 合并样本标准差计算电子表格 有了表8—1，我们就可以在此基础上，通过插入列或删除列的办法，设计出重复测量次数大于10或小于10的Excel电子表格；通过插入行或删除行的办法，设计出测量组数大于4或小于4的Excel电子表格。也就是说，利用表8—1可方便快捷地得到重复测量次数为n，数据组数为m的Excel电子表格，来完成实验标准差及合并样本标准差的计算。这样得出的sp的自由度 。 2.2 根据以前的测量记录来获得合并样本标准差 某种测量按 规定 关于下班后关闭电源的规定党章中关于入党时间的规定公务员考核规定下载规定办法文件下载宁波关于闷顶的规定 一般应进行2次、4次甚至6次观测，用算术平均值作为测量结果报出。这种规范化的测量如对m 个被测量进行过了，则可以通过这m个记录中的若干个来获得合并样本标准差sp。 (1) 若是进行4次或6次观测，可先按贝塞尔公式计算这4次或6次观测值的标准偏差si，而后按 计算合并样本标准差，式中m为选取记录的个数。例如某精密压力表按规程规定应进行4次观测，今欲获得10 MPa的合并样本标准差，可选取10份测量记录，设计为表8—2的电子表格，该表的插入和设计方法参见本章2.1节，其中在B6单元格中输入计算第1组数据实验标准差的公式 “=STDEV(B2:B5)”，从B6单元格拖动填充柄至K6，即可获得10组数据的实验标准差。在B7单元格中输入“=B6^2”，从B7单元格拖动填充柄至K7，即可获得10个实验标准差的平方，在B8单元格中输入“=SQRT(SUM(B7:K7)/10)”即可获得合并样本标准差sp。 这样得出的sp的自由度 ，也就是测量次数减被测量的个数。 表8—2 4次观测时合并样本标准差计算电子表格 单位:MPa (2) 若是进行2次观测，可以通过被测量的两次测量结果之差Δ来求单次测量结果分散性标准差。例如：n 个被测量，每个均测了两次，得到n 个差值Δi，按贝塞尔公式计算差值Δi的标准偏差s(Δi)为： s(Δi)= 式中， 为n 个差值的算术平均值。由于单次测量结果的标准差s(xi)与s(Δi)之间的关系为： s(Δi)= 因此，用这一方法得出的s(Δi)还要除以 就是s(xi)，即单次测量结果xi的合并样本标准差sp。s(Δi)的自由度就是sp的自由度， 。采用这种方法时，应有较多的被测量，以使其自由度足够大，一般应有20个以上。 表8—3 2次观测时合并样本标准差计算电子表格 单位:MPa 例如某精密压力表按规程规定应进行2次观测，今欲获得10 MPa的合并样本标准差，可选取20份测量记录，设计为表8—3的电子表格，该表的插入和设计方法参见本章2.1节，其中在D2单元格中输入计算第1个差值Δ的公式 “=B2-C2”，从D2单元格拖动填充柄至D21，即可获得20个差值Δ。在B22单元格中输入“=SUM(D2:D21)/20”，可获得20 个差值的算术平均值 。在E2单元格中输入 “=(D2-$B$22)^2”，从E2单元格拖动填充柄至E21，即可获得20个 。在D22单元格中输入“=SQRT(SUM(E2:E21)/19)/SQRT(2)”，即可获得合并样本标准差sp。 这样得出的sp的自由度 。 第二节 应用Excel计算合成标准不确定度及其有效自由度的原理 中国计量出版社出版的《测量不确定度评定与表示指南》等关于测量不确定度评定的专著，以及在技术杂志中发表的测量不确定度评定实例，其中都给出了测量不确定度评定的完整步骤，给出了如何根据输入量的各标准不确定度分量及其自由度计算输入量的标准不确定度及其自由度，再根据灵敏系数计算输出量的合成标准不确定度及其自由度，同时给出了不确定度评定一览表，整个评定过程层次分明，一目了然。但是，如果测量结果的不确定度随测量结果的不同而变化时，若还按上述评定过程计算合成标准不确定度及其自由度，其计算量是相当大的。 本节介绍当各输入量互不相关时，可根据输入量的各标准不确定度分量及其自由度和灵敏系数直接计算输出量的合成标准不确定度及其自由度的原理，省去了输入量的标准不确定度及其自由度的计算，同时不确定度评定一览表采用插入的一个Excel电子表格，利用Excel的计算功能来完成从B类评定的分散区间的半宽a(注：本书中的a，可理解为采用p=100%对应分散区间的半宽，来源于证书的扩展不确定度U，以及来源于数字仪器分辨力或数据修约间隔的一半等)和A类评定的结果直接到合成标准不确定度及其自由度的计算，以及采用数值计算法计算输出不确定度分量的原理和方法。 3.1 利用Excel简化计算的公式 (1) 合成标准不确定度及其有效自由度简化计算一般公式的推导 假设被测量有三个输入量X1，X2，X3， 数学 数学高考答题卡模板高考数学答题卡模板三年级数学混合运算测试卷数学作业设计案例新人教版八年级上数学教学计划 模型为： 输入量X1的标准不确定度为u1，由两个标准不确定度分量u11和u12合成得到；输入量X2的标准不确定度为u2，也由两个标准不确定度分量u21和u22合成得到；输入量X3的标准不确定度为u3，由三个标准不确定度分量u31、u32和u33合成得到；输入量X1，X2，X3的灵敏系数分别为 ， ， ，其各不确定度分量及自由度如表8—4。 表8—4 各不确定度分量及自由度列表 输入量 输入量的标准不确定度分量 输入量的标准不确定度分量的自由度 输入量的标准不确定度 输入量的标准不确定度的自由度 灵敏系数 输出量的合成标准不确定度 输出量合成标准不确定度的自由度 X1 u11 u1 c1 uc u12 X2 u21 u2 c2 u22 X3 u31 u3 c3 u32 u33 按照《测量不确定度评定与表示指南》中介绍的方法要获得合成标准不确定度uc和有效自由度 ，应先按式(8.1)～(8.6)计算u1、u2、u3、 、 、 。 (8.1) (8.2) (8.3) (8.4) (8.5) (8.6) 再计算uc和 ： (8.7) (8.8) 将(8.1)、(8.3)、(8.5)式代入(8.7)式，将(8.2)、(8.4)、(8.6)式代入(8.8)式，即可得到根据输入量的各标准不确定度分量及其自由度和灵敏系数直接计算输出量的合成标准不确定度及其有效自由度的公式： (8.9) (8.10) (8.9)、(8.10)两个公式包括了中间计算过程，看似复杂了，但它们为后面使用Excel的计算功能来完成合成标准不确定度及其有效自由度的计算提供了依据。 (2) 函数形式为线性函数时合成标准不确定度及其有效自由度简化计算公式 当被测量Y为相互独立的输入量Xi的线性函数时，且灵敏系数ci为+1或-1，则式(8.9)和(8.10)可用简化的式(8.11)和(8.12)计算： (8.11) (8.12) (3) 函数形式为幂指数乘积时合成标准不确定度及其有效自由度简化计算公式 在Xi彼此独立的条件下，如果函数f的形式表现为： 式中，系数c并非灵敏系数，指数pi可以是正数、负数或分数，设pi的不确定度u(pi)可以忽略不计，则式(8.9)和(8.10)可用相对标准不确定度计算(要求y≠0和x≠0)，如式(8.13)和(8.14)： (8.13) (8.14) 如果指数pi只是+1或-1，式(8.13)和(8.14)还可进一步简化，这里不再列出。 (4) 合成标准不确定度及其有效自由度简化计算的通用公式 总结式(8.9)、(8.10)、(8.11)、(8.12)、(8.13)和(8.14)可以得到合成标准不确定度及其有效自由度计算的通用公式。设数学模型为： 则合成标准不确定度及其有效自由度的一般计算式可按式(8.15)和(8.16)计算： (8.15) (8.16) 当被测量Y为相互独立的输入量Xi的线性函数时，且灵敏系数ci为+1或-1，则合成标准不确定度及其有效自由度可按式(8.17)和(8.18)计算： (8.17) (8.18) 如果函数f的形式表现为幂指数乘积的形式则合成标准不确定度及其有效自由度可按式(8.19)和(8.20)计算： (8.19) (8.20) 其中，式(8.19)和(8.20)中的 为相对标准不确定度分量，可用urelij表示。 3.2 不确定度评定Excel电子表格的设计 下面以数学模型为 ，Xi的不确定度，Y的不确定度分量及自由度如表8—4为例，根据数学模型的几种形式，介绍用以进行各不确定度分量汇总和计算所用的Excel电子表格的设计方法。 (1) 函数f的形式表现为输入量Xi的线性函数时的电子表格设计 当被测量Y为相互独立的输入量Xi的线性函数时，此时灵敏系数ci为+1、-1或其它常数，则合成标准不确定度及其有效自由度可按式(8.9)、(8.10)或(8.11)、(8.12)计算，假设各不确定度来源分别用A、B、C、D、E、F和G表示，各分散区间的半宽aij及其分布对应的包含因子kij以及各输入量的灵敏系数ci如表8—5。 表8—5 假设的各不确定度来源、分散区间的半宽、包含因子、自由度以及灵敏系数 序号 不确定度来源 aij kij uij νij 输入量Xi ci 1 A 0.20 3 0.067 50 X1 1 2 B 0.10 0.058 12 3 C 0.10 0.058 12 X2 2 4 D 0.10 0.071 12 5 E 0.20 3 0.067 8 X3 0.5 6 F 0.30 0.173 8 7 G 0.100 9 表8—5中序号1～6采用B类评定，序号7采用A类评定。此时用以进行不确定度分量汇总和计算的Excel电子表格可设计为图8—4的形式。 我们用图8—4所示的Excel电子表格作为各标准不确定度汇总表，同时完成合成标准不确定度及其有效自由度和扩展不确定度的计算。图8—4所示的Excel电子表格设计简介如下： 图8—4 数学模型为线性函数时的各不确定度分量汇总及计算电子表格 ①先选中整个工作表，设置为居中。 ②在A1～J1单元格中分别输入各列的名称或符号。 ③在B2～B8单元格中分别输入各不确定度来源(假设用A～G表示)，设置为左对齐。 ④在C2～C7和D2～D7单元格中分别输入各分散区间的半宽及其包含因子， 的输入1.732， 的输入1.414等。 ⑤在E2单元格中输入“=C2/D2”，从E2单元格拖动填充柄至E7，即可获得各分散区间的半宽导致的标准不确定度，在E8单元格中输入“0.10”(A类评定结果)，选中E2～E8单元格调整小数位数为3位(根据具体情况设定，下同)。 ⑥在F2～F8单元格中分别输入各分量的灵敏系数(这里是假设的)。 ⑦在G2单元格中输入“=E2*F2”，从G2单元格拖动填充柄至G8，即可获得输入量Xi各标准不确定度与灵敏系数的乘积(以下称为输出量不确定度分量)，选中G2～G8单元格调整小数位数为3位。 ⑧在H2～H8单元格中分别输入各分量的自由度。 ⑨在I2单元格中输入“=G2*G2”或“=G2^2”，从I2单元格拖动填充柄至I8，即可获得输出量各不确定度分量的平方。为计算合成标准不确定度uc作准备。 ⑩在J2单元格中输入“=G2^4/H2”，从J2单元格拖动填充柄至J8，即可获得输出量各不确定度分量的4次方与自由度的商。为计算有效自由度 作准备。 ⑾在A9单元格中输入“uc=”，设置为右对齐，按本节给出的合成标准不确定度的计算公式(8.9)在B9单元格中输入“=SQRT(SUM(I2:I8))”，设置为左对齐和2位小数，即可获得合成标准不确定度。 ⑿在C9单元格中输入“ =”，设置为右对齐，按有效自由度的计算公式(8.10)在D9单元格中输入“=B9^4/(SUM(J2:J8))”， 设置为1位小数，即可获得合成标准不确定度的有效自由度。 ⒀在E10单元格中输入“ =”，设置为右对齐，根据 =47.5，查t分布表应将有效自由度值近似取整为45，在F10单元格中输入自由度“45”，设置为左对齐。 ⒁在C10单元格中输入“k95=”，设置为右对齐，取置信概率p=95%，根据合成标准不确定度的有效自由度 =45查表得到：k95=t95( )=t95(45)=2.01，将此值输入D10单元格。 ⒂在A10单元格中输入“U95=”，设置为右对齐，在B10单元格中输入“B9*D10”，设置为左对齐和2位小数，即可获得扩展不确定度U95=k95uc。 ⒃选中A1～J10单元格区域加上边框，调整显示窗口只显示A1～J10单元格区域，将鼠标指向Excel电子表格以外的地方，单击即可退出Excel，如表8—6。 表8—6 数学模型为线性函数时的各不确定度分量汇总及计算电子表格 表8—7 数学模型为线性函数时的各不确定度分量汇总及计算电子表格(隐藏了I、J列) 也可采用本章第二节所介绍的方法调整显示窗口，将第I和J列隐藏起来，退出Excel状态，如表8—7。 这样一个能够计算合成标准不确定度及其有效自由度和扩展不确定度的Excel电子表格就完成了，在实际的不确定度评定中，如果各不确定度分量随着测量结果的不同是变化的，则只需在C2～C7单元格中分别输入各分散区间的半宽，在E8单元格中输入A类评定结果(可用第二节的方法求得)，便能立即得到所求的输出量的合成标准不确定度及其有效自由度，再根据给定的置信概率p=95，查表得到k95=t95( )并输入到D10单元格，即可得到扩展不确定度U95。 如果扩展不确定度不需用Up表示，而是用k=2的扩展不确定度U表示，则评定过程可省去对各不确定度分量自由度的估计，此时用以进行不确定度分量汇总和计算的Excel电子表格可省去表8—6中的H和J列，简化为图8—5的形式。 图8—5 简化的各不确定度分量汇总及计算电子表格 将鼠标指向Excel电子表格以外的地方，单击即可退出Excel，如表8—8。 表8—8 简化的各不确定度分量汇总及计算电子表格 (2) 函数f的形式表现为输入量Xi的幂指数乘积时的电子表格设计 当被测量Y为相互独立的输入量Xi的幂指数乘积时，可用相对标准不确定度计算(要求y≠0和x≠0)，则相对合成标准不确定度及其有效自由度可按式(8.13)和(8.14)计算，此时用以进行不确定度分量汇总和计算的Excel电子表格可设计为图8—6的形式。设计过程与表8—6类似，不再详述。 将鼠标指向Excel电子表格以外的地方，单击即可退出Excel，如表8—9。 在大多数的检测和校准工作中，数学模型的函数形式表现为输入量Xi的线性函数或幂指数乘积的形式，这两种函数形式的特点是灵敏系数ci和幂指数pi为常数，如果测量结果的不确定度随测量结果大小发生变化时，则只需在各不确定度分量汇总及计算电子表格的C列(即分散区间的半宽一列)将变化了的分散区间的半宽输入相应的位置，可即时得到合成标准不确定度以至扩展不确定度。 图8—6 数学模型为幂指数乘积时的各不确定度分量汇总及计算电子表格 表8—9 数学模型为幂指数乘积时的各不确定度分量汇总及计算电子表格 更有甚者，如果某个分散区间的半宽与输入量存在确定的函数关系，则可在电子表格中增加一行输入量和输出量的数据，利用相对引用或绝对引用将该数据与分散区间的半宽建立关系，同时根据数学模型计算出输出量(最终测量结果)。此时只要输入不同的输入量数据，可即时得到最终测量结果及其合成标准不确定度以至扩展不确定度。以表8—6为基础，假设序号1、3和5的分散区间的半宽与输入量存在确定的函数关系，则可将电子表格设计为图8—7的形式。其中在F11、H11和J11单元格中输入三个输入量的最佳估计值，D11单元格中根据数学模型输入引用F11、H11和J11单元格的计算式子，在C2、C4和C6单元格中根据分散区间的半宽与输入量之间的函数关系，分别输入引用F11、H11和J11单元格的计算式子，在B11单元格中输入“B10/D11”可得到相对扩展不确定度U95rel。 图8—7 与测量结果相联系的各不确定度分量汇总及计算电子表格 表8—10 与测量结果相联系的各不确定度分量汇总及计算电子表格 电子表格其它部分的设计与表8—6相同。有了该电子表格，只要在F11、H11和J11单元格中输入三个输入量的最佳估计值，可即时得到最终测量结果y及其合成标准不确定度uc以至扩展不确定度U95和相对扩展不确定度U95rel。 将鼠标指向Excel电子表格以外的地方，单击即可退出Excel，如表8—10。 至此，我们可以感受到Excel电子表格的好处，只要掌握部分简单的Excel知识就可方便快捷地实现对任意一个测量结果的不确定度评定。 (3) 函数f的形式表现为输入量Xi的其它复杂形式时的电子表格设计 当被测量Y为相互独立的输入量Xi的其它复杂形式时，此时灵敏系数 可能不为常数，而是某几个输入量最佳估计值的函数，因此灵敏系数的处理变得非常复杂，这是进行测量不确定度评定最难解决的问题，此时的合成标准不确定度及其有效自由度只能按式(8.9)和(8.10)计算。在这里如果u(xi)与xi相比相对较小，各不确定度分量大小与对应各输入量存在确定的函数关系，则我们同样可以用Excel电子表格来处理，并能做到只要输入各输入量的最佳估计值，即刻得到输出量的最佳估计值及其合成标准不确定度以至扩展不确定度。 假设数学模型为： 灵敏系数的获得可有三种：偏导数法、数值计算法和实验方法。简述如下： ①偏导数法 偏导数应是在Xi的期望值下评定，即： (8.21) 可以用“指数乘系数、指数减1”的办法来求某些函数形式输入量的偏导。 对线性函数式，输入量的偏导数就是其系数，如： y=ax1+bx2+…+axn x1的偏导数就是a，x2的偏导数就是b。 对于非线性关系式，如： P=U2/R=U2R-1 U的偏导为： R的偏导为： ②数值计算法 (8.22) ③实验方法 (8.23) 保持其它量不变，xi变化一Δxi，引起y变化一Δyi。 这里我们采用数值计算法获得灵敏系数： (8.24) 其中x1、x2、…、xi、…xN分别为输入量X1、X2、…、Xi、…XN的最佳估计值，dxi是相对于xi为很小的数值。 假如u(xi)与xi相比相对较小，则偏导数 也可近似为： (8.25) 乘以u(xi) 获得因xi的不确定度引起的y的不确定度ui(y)。为便于理解，参照《化学分析中不确定度的评估指南》，用符号u(y,xi)表示，当输入量有几个不确定度来源时，用uj(y,xi)表示。即： (8.26) 因此，u(y,xi)只是分别用[xi+u(xi)]和xi计算出来的y之差。这样我们就可以避免了求偏导，并为使用Excel电子表格来处理输出量的最佳估计值的扩展不确定度提供了依据。 下面以聚氯乙烯树脂溶液粘数测量结果的不确定度评定为例，介绍此时用以进行不确定度分量汇总和计算的Excel电子表格的设计。 测量模型为： (2-27) 式中：VN——粘数，mL/g； ts——溶液三次流经时间的算术平均值，s； t0——溶剂三次流经时间的算术平均值，s； ρ——溶液的质量浓度，g/mL； V——容量瓶体积，mL； m——样品质量，g。 图8—8是设计好的聚氯乙烯树脂溶液粘数测量结果不确定度评定各不确定度分量汇总及计算电子表格。 该电子表格的设计特点是： ①在B9、D9、F9和H9单元格中分别放置溶液三次流经时间的算术平均值ts、溶剂三次流经时间的算术平均值t0、容量瓶体积V和样品质量m的最佳估计值，根据数学模型在B8单元格中输入公式“=(B9/D9-1)*F9/H9”可获得输出量的最佳估计值VN。 ②F列是计算u(y,xi)的，该列数据分别是用[xi+u(xi)]和xi计算出来的y之差，即VN值之差。公式的输入方法为：先在F2单元格中输入“=(B$9/D$9-1)*F$9/H$9-B$8”，从F2单元格拖动填充柄至F5单元格，而后将F2、F3、F4和F5单元格中的公式分别修改为“=((B$9+E2)/D$9-1)*F$9/H$9-B$8”、“=(B$9/(D$9+E3)-1)*F$9/H$9-B$8”、“=(B$9/D$9-1)*(F$9+E4)/H$9-B$8”和“=(B$9/D$9-1)*F$9/(H$9+E5)-B$8”。 ③F6单元格中是根据平行试样的单个测试值和两次平均值的相对差值不得大于0.7%。则由重复性导致的标准不确定度u(VN)= VN×0.7%/2.4，而输入公式“=B8*0.7%/2.4”。 ④在F8单元格中输入“=D8/B8”，并将单元格数字格式设置为“百分比”，即可获得以百分数表示的相对扩展不确定度。 ⑤电子表格其它部分的设计与前面相同。 图8—8 聚氯乙烯树脂溶液粘数测量结果各不确定度分量汇总及计算电子表格 将鼠标指向Excel电子表格以外的地方，单击即可退出Excel，如表8—11。 表8—11 聚氯乙烯树脂溶液粘数测量结果各不确定度分量汇总及计算电子表格 该电子表格设计好后，对于任意溶液粘数的测量，如果测量条件和程序相同，则只要在B9、D9、F9和H9单元格中分别输入溶液三次流经时间的算术平均值ts、溶剂三次流经时间的算术平均值t0、容量瓶体积V和样品质量m的最佳估计值，即刻在B8单元格中获得输出量的最佳估计值VN，在B7单元格中获得其合成标准不确定度uc，在D7单元格中获得合成标准不确定度uc的有效自由度 ，根据 的数值，查t分布表得kp值，将此值输入F7单元格中，即刻在D8单元格中获得其扩展不确定度Up，在F8单元格中获得其相对扩展不确定度Uprel。 如果不考虑自由度和置信概率，测量不确定度用k=2的扩展不确定度表示，则表8—11的电子表格可设计为如图8—9所示。 图8—9 聚氯乙烯树脂溶液粘数测量结果各不确定度分量汇总及计算电子表格 将鼠标指向Excel电子表格以外的地方，单击即可退出Excel，如表8—12。 表8—12 聚氯乙烯树脂溶液粘数测量结果各不确定度分量汇总及计算电子表格 如果函数形式虽比较复杂，但灵敏系数通过偏导数法很容易获得，此时电子表格的设计也可以采用4.12一例的形式，将灵敏系数与输入量的最佳估计值联系起来。 3.3 使用Excel电子表格的意义 使用Excel电子表格纯粹是为了简化计算，并没有干预对每个不确定度分量大小及其分布和自由度的估计，更没有改变最终评定的扩展不确定度，但从数学模型的三种形式看，所设计的Excel电子表格均巧妙的解决了对灵敏系数的计算，特别是对于复杂的函数形式。由于Excel的参与也使评定过程大为简化。因此对于某一个认为合理的评定实例，就可以利用本方法来完成任意一个测量结果的合成标准不确定度及其有效自由度和扩展不确定度的计算，使我们给出任意一个测量结果的不确定度变得不再那么复杂。 同使用计算器计算相比，用该法计算扩展不确定度可省去众多的按键操作，并可避免使用计算器时产生的按键错误，在有多个不确定度分量的情况下表现更为优越；同利用其它计算机软件相比，该法不需要复杂的编程，只需掌握基本的Excel知识。 另外，当一旦设计了一个计算不确定度的Excel电子表格，就可以将它复制到另一个需要插入Excel电子表格的地方，修改后得到另一个需要的Excel电子表格，无须从头来过。具体做法是：在Word状态下，用鼠标单击要复制的Excel电子表格，按“Ctrl+C”键或用鼠标单击“复制”按钮，而后将光标移到要插入Excel电子表格的位置，再按 “Ctrl+V”键或用鼠标单击“粘贴”按钮。 该法具有直观性和更大的灵活性，评定者可以方便地根据具体情况增加或删去某些分量，并立刻得到新的不确定度评定结果，同时看出该分量对合成标准不确定度或扩展不确定度贡献的大小，从而指导测量者应该重点控制哪些测量条件和所采用仪器的准确度或不确定度，在满足测量不确定度要求的情况下寻求最佳的测量方案，包括测量程序、环境条件控制以及选用的仪器设备等，做到既经济合理又满足实际需要。从而也使本方法应用于检定规程、校准规范、测量方法和标准的制定等。这也是采用Excel电子表格辅助测量不确定度评定的另一亮点。 第三节 Excel在不确定度分量的汇总及扩展不确定度计算中的应用 下面以一种由高稳定度恒流源、标准电阻和直流数字电压表组成的测量系统，对某直流电阻进行测量时的测量不确定度评定为例，介绍Excel在不确定度分量的汇总及扩展不确定度计算中的应用。 4.1 测量原理 这种直流电阻测量的原理图如图8—10。图中，RX为被测电阻，RN为标准电阻。 图8—10 直流电阻测量原理图 将开关K与UN接通，调节恒流源输出，使数字电压表读数UN与标准电阻的实际值RN(RN=R20[1+α(t-20)+β(t-20)2]，t为环境温度)“一致”。如标准电阻实际值为99.997 4 Ω时，数字电压表读数UN为0.999 974 V。此时有： (8.28) 将开关K与UX接通，数字电压表读数为UX ，此时有： (8.29) 由式(8.28)和(8.29)得： (8.30) 由于UN与RN数值“一致”，UN/RN的数值为10n，n为整数，因此，RX的数值为：{RX}={UX}×10n，从而实现被测电阻的直读，这就是该测量系统的工作原理。 4.2 测量系统的构成及各部分技术指标 (1) 直流数字电压表：采用1081数字多用表，所用100 mV、1 V和10 V量程的输入阻抗大于10 000 MΩ，这三个量程在(23±1)℃条件下的最大允许示值误差如表8—13。 表8—13 1081数字多用表的最大允许示值误差 量 程 100 mV 1 V 10 V 允许示值误差 ±(a%UX +b%Um) 0.001 1+0.000 20 0.000 8+0.000 15 0.000 8+0.00010 其中：UX为数字表读数，Um=2×满量程 (2) 标准电阻：采用BZ3型二等标准电阻，其中(10-1～104)Ω年稳定性为10×10-6，校准证书中给出扩展不确定度为Urel=5×10-6，k=3；(10-3、10-2、105)Ω年稳定性为20×10-6，校准证书中给出扩展不确定度为Urel=10×10-6，k=3。 (3) 恒流源：采用RT9601高性能直流精密电源(上海兰斯汀仪表研究所生产)，电流量程及稳定度：0.1 mA 1 mA 10 mA 100 mA稳定度≤2×10-6，扩展电流1 A稳定度≤3×10-6，10 A稳定度≤5×10-6。 (4) 转换开关：采用低电势油浸开关，或上海兰斯汀仪表研究所生产的RT9602电阻箱标准电阻测试组件。 (5) 温度计：分度值为0.1℃的温度计1支。 测量不同阻值范围时选用的标准电阻、恒流源量程和数字表量程可参照表8—14。 表8—14 不同阻值范围选用的标准电阻、恒流源量程和数字表量程对照表 阻值范围(Ω) 104～105 103～104 102～103 10～102 1～10 10-1～1 10-2～10-1 10-3～10-2 标准电阻(Ω) 10 000 1000 100 10 10 10 0.1 0.001 恒流源电流 0.1 mA 1 mA 10 mA 100 mA 100 mA 100 mA 1 A 10 A 标定电流时 数表量程 1 V 1 V 1 V 1 V 1 V 1 V 100 mV 100 mV 测量UX时 数表量程 10 V 10 V 10 V 10 V 1 V 100 mV 100 mV 100 mV 4.3 测量不确定度评定 (1) 数学模型 在环境温度为(20±0.5)℃的条件下，由于温度测量不准对标准电阻的影响可忽略不计，数字表示值误差的温度影响也可忽略不计，则数学模型为： (8.31) RN、UN、UX估计值彼此独立，按照JJF1059-1999《测量不确定度的评定与表示》第6.6款的方法，采用相对标准不确定度按下式来计算RX的相对合成标准不确定度： (8.32) (2) 各不确定度分量的评定 下面以被测阻值范围在104～105 Ω为例，进行各主要不确定度分量的评定。 ①二等标准电阻RN的不确定度分量 a. 10 000 Ω二等标准电阻的年变化允许值为±10×10-6，即分散区间的半宽为10×10-6，在此区间可认为服从均匀分布( )，则相对标准不确定度urel1= 。该标准电阻历年检定合格，其自由度估计为 =∞。 b. 10 000 Ω二等标准电阻经上级传递合格，传递扩展不确定度Urel=5×10-6 ，k=3，则相对标准不确定度urel2= 。该不确定度分量来源十分可靠，其自由度估计为 =∞。 ②测量UN时的不确定度分量 a. 恒流源的不稳定产生的不确定度 按恒流源说明书，其10 min的电流稳定度为2×10-6，即分散区间的半宽为2×10-6，在此区间可认为服从均匀分布( )，则相对标准不确定度urel3= 。估计Δurel3/urel3=0.2，其自由度 =12。 b. 标定电流时数字表的不确定度按数字表在(23±1)℃时的允许误差技术指标。在1 V量程测量1 V电压的最大允许示值误差为ΔN=±(0.000 8%×1 V+0.000 15%×2 V)=11 μV，其相对允许示值误差为±11×10-6，即分散区间的半宽为11×10-6，在此区间可认为服从均匀分布( )，则相对标准不确定度urel4= 。估计Δurel4/urel4=0.1，其自由度 =50。 c. 1081数字表在1 V量程时的输入阻抗Ri大于10 000 MΩ，在测量10 000 Ω标准电阻上的电压时，由输入阻抗引起的误差 ±10 000 Ω/10 000 MΩ=±1×10-6，即分散区间的半宽为1×10-6，在此区间可认为服从均匀分布( )，则相对标准不确定度urel5= 。估计Δurel5/urel5=0.1，其自由度 5=50。 应当注意，该项不确定度分量在电阻≤10 000 Ω时可忽略不计，对于大于105 Ω的电阻，由输入阻抗引起的误差限将大于10×10-6，因此不宜用该法。 ③测量UX时的不确定度分量 a. 恒流源的不稳定产生的不确定度与②中相同。相对标准不确定度urel6= ，其自由度 6=12。 b. 测量UX时数字表的不确定度，在10 V量程测量被测电阻上的电压值UX时的允许示值误差为： ΔX =±(0.000 8%×UX +0.000 1%×20 V) 其相对允许示值误差为 ±(0.000 8%+0.000 1%×20V/UX )，即分散区间的半宽为(0.000 8%+0.000 1%×20V/UX )，在此区间可认为服从均匀分布( )，则相对标准不确定度urel7= 。UX在Excel电子表格中将作为一个变量。估计Δurel7/urel7=0.1，其自由度 7=50。 c. 1081数字表在10 V量程时的输入阻抗Ri>10 000 MΩ，在测量被测电阻上的电压值UX时，由输入阻抗引起的误差 {UX}×10 000 Ω/10 000 MΩ={UX}×10-6，可认为服从均匀分布( )，则相对标准不确定度urel8={UX} 。估计Δurel8/urel8=0.2，其自由度 8=12。 由于考虑了电源稳定性，数字表的分辨力又足够，对测量重复性的不确定度为简化计算可不考虑。 (3) 各不确定度分量汇总及计算电子表格设计 此函数f的形式表现为输入量Xi的幂指数乘积，因此对上述各不确定度分量的评定结果汇总为图8—11所示的Excel电子表格来计算各点的扩展不确定度。 图8—11 各不确定度分量汇总及计算电子表格 各输入量估计值认为彼此独立，合成相对标准不确定度按式(8.33)计算，有效自由度按式(8.34)计算。 (8.33) (8.34) 图8—11所示的Excel电子表格的插入方法和一些处理技巧，与前面介绍的实验标准差的计算中Excel电子表格的处理相同，这里只介绍与本表有关的主要设计方法。当然读者也可以设计为其它形式，只要直观方便地计算出最后的扩展不确定度即可。该Excel电子表格的设计方法是： ①插入一个Excel电子表格。 ②在A1～J1单元格中分别输入各列的名称或符号，将各列先设置为居中。 ③在A2～A9单元格中分别输入各不确定度来源的序号。 ④在B2～B9单元格中分别输入各不确定度来源，设置为左对齐。 ⑤在C2～C9和D2～D9单元格中分别输入各分散区间的半宽及其包含因子， 的输入1.732。根据前面对各不确定度的评定，其中C6单元格中输入“=F11/10000”，C8单元格中输入“=8+20/C11”，C9单元格中输入“=F11*C11/10000”。 ⑥在E2单元格中输入“=C2/D2”，从E2单元格拖动填充柄至E9，即可获得各相对标准不确定度。选中E2～E9单元格调整小数位数为1位。 ⑦在F2～F9单元格中分别输入各输入量的指数pi。 ⑧在G2单元格中输入“=E2*F2”，从G2单元格拖动填充柄至G9，即可获得各相对标准不确定度与指数pi的乘积。 ⑨在H2～H9单元格中分别输入各不确定度的自由度。 ⑩在I2单元格中输入“=G2*G2”或“=G2^2”，从I2单元格拖动填充柄至I9，即可获得各相对标准不确定度与指数pi的乘积的平方。该列被隐藏。 ⑾在J2单元格中输入“=G2^4/H2”，从J2单元格拖动填充柄至J9，即可获得输出量各不确定度与指数pi的乘积的4次方再与自由度的商，其中在J2和J3单元格中输入“0”以解决自由度为∞的情况。该列也被隐藏。 ⑿将A10和B10、A11和B11、A12和B12、A13和B13、D10和E10、D11和E11、D12和E12单元格合并及居中并输入图8—11所示的内容，均设置为右对齐。 ⒀按合成相对标准不确定度的计算公式(8.33)在C10单元格中输入“=SQRT(SUM(I2:I9))”，设置为左对齐和1位小数，即可获得合成相对标准不确定度。 ⒁按有效自由度的计算公式(8.34)在F10单元格中输入“=C10^4/(SUM(J2:J9))”，设置为左对齐和无小数位，即可获得有效自由度。 ⒂在F11单元格中输入标准电阻的标称值，在F12单元格中输入调定的电流值。 ⒃取置信概率p=95%，根据 ，查t分布表得到k95=t95( )，将此值输入E13单元格。 ⒄在C11单元格中输入从被测电阻上测得的电压值。 ⒅在C12单元格中输入“=C11*F11”，即可获得被测电阻值。 ⒆在C13单元格中输入“C10*E13”，即可获得相对扩展不确定度U95rel=k95ucrel，调整为无小数位。 ⒇将C10～C13、F10～F12和E13单元格设置为左对齐，将C11、E13单元格设置为斜体。 初步设计好的Excel电子表格如图8—11所示。选中A1～H13单元格区域加上边框，调整显示窗口只显示A1～H13单元格区域，将鼠标指向Excel电子表格以外的地方，点击即可退出Excel，如表8—15所示。 表8—15 各不确定度分量汇总及计算电子表格 用鼠标指向该表，双击即可进入Excel状态，表中需要输入的数据只有C2～C5、F11、F12和C12、E13单元格，其中C2～C5、F11、F12单元格中的数据对于选定的标准电阻、恒流源电流和数字表量程是不变的，此时只要在C11单元格中输入被测电阻上测得的电压值，即可得到合成标准不确定度和有效自由度，根据选定的置信概率95%，查t分布表得到：k95=t95( )，将此值输入E13单元格，即可在C13单元格中获得测量结果的扩展不确定度U95。对于其它阻值范围的测量，可根据选定的标准电阻、恒流源电流和数字表量程对Excel电子表格中的相应数据和算法作适当修改即可。 第四节 应用Excel进行测量不确定度评定的步骤 按照本章应用Excel进行测量不确定度评定的原理论述，提出如下的测量不确定度评定步骤，可使评定过程大为简化。 5.1 概述 在概述中主要介绍如下内容： 测量依据——依据的具体检定规程、校准规范、国家标准或其它技术规范； 环境条件——测量过程中对温度、相对湿度等的要求； 测量标准——测量时使用的计量标准、标准物质和测量仪器等； 被测对象——本评定方法适应的被测对象； 测量过程——对测量过程以及数据处理等的简要描述； 评定结果的使用——对评定结果的使用和各电子表格的用途作简要说明。 5.2 数学模型 根据测量过程的描述写出数学模型的表达式，并对各符号的含义作一注释，并注明所采用的单位。 5.3 各输入量的标准不确定度分量的评定 根据数学模型和对测量过程的描述定性确定各输入量的标准不确定度分量，而后对每个分量进行定量评定。 (1) 采用A类评定方法的，若能通过多组测量获得合并样本标准差sp的，尽量采用合并样本标准差，尽而获得一个自由度较大的实验标准差。此时应注意采用合并样本标准差sp时以下三个条件必须同时满足： ①规范化的常规测量，即测量的条件、方法、程序、观测人员不变。 ②被测量之值的大小影响重复性标准偏差不明显，即即使被测量大小有某种改变，其重复性标准偏差无明显改变。是否能满足这一要求，需通过实验加以 证明 住所证明下载场所使用证明下载诊断证明下载住所证明下载爱问住所证明下载爱问 。 ③在通过实验得到sp的时间到使用sp对测量结果不确定度进行评定时，是处于统计控制状态，或称之为随机状态，也就是重复性条件或复现性条件能充分保证的状态。 (2) 采用B类评定方法的，只需确定分散区间的半宽a(注：这里的a，可理解为采用p=100%对应分散区间的半宽，来源于证书的扩展不确定度U，以及来源于数字仪器分辨力或数据修约间隔的一半等)，对分布进行估计(即确定对应的包含因子k)，以及必要时对自由度进行估计，不必将标准不确定度计算出来(即不必计算出u=a/k的结果)。若某个输入量xi有几个不确定度来源，设其标准偏差及自由度为ui1、 ，ui2、 ，ui3、 等，也不必按式(8.40)、(8.41)计算出输入量的标准不确定度及其自由度。 (8.40) (8.41) 5.4 合成标准不确定度及扩展不确定度的评定 根据数学模型的表现形式，确定选用的Excel电子表格的形式，来完成对各不确定度分量的汇总和合成标准不确定度及其有效自由度(需要时)的计算，并将扩展不确定度也在电子表格中计算出，有的甚至将最终测量结果也在电子表格中计算出。因为在电子表格中有时无法输入单位，因此采用Excel电子表格进行计算时，要特别注意各输入量所采用的