实验室标准曲线Excel表评定.docx

实验室标准曲线（线性、非线性）A类不确定度的Excel评定苏秀娟1 郭琴1 刘新荣1 苏敬武11. 威海市疾病预防控制中心，山东威海市 264200摘要：本文用Excel表的图表直接给出的趋势线参数，对实验室标准曲线A类不确定度进行评价，计算步骤少，适应范围广（适用于线性和非线性），有较强的实用意义。关键词：Excel表 标准曲线 A类不确定度 标准曲线法是实验室常用分析方法，虽然现代分析仪器都能智能的选择标准曲线类型，并给出相应的计算结果，但是对分析结果的A类不确定度的评价仍需要进行手工计算，由于这类平价计算公式繁多，数据处理过程往往出错。另外由于采取不同的拟合曲线形式，也造成评价的困难。本文采用Excel表的图表给出趋势线参数，直接进行标准曲线A类不确定度评价，效果直观易懂、计算步骤简便、应用范围广，有较强的实用价值，现介绍如下：1、标准曲线A类不确定度的评价步骤：标准曲线A类不确定评价主要有以下3部分组成：a、来自回归曲线的回归系数不确定度；b、来自回归曲线截距a不确定度；c、重复测定的不确定度。本文对自变量x的抽样误差未考虑。a、b两部分均是由回归曲线剩余平方和计算来的。1.1 将分析数据输入Excel表：格式如表1：表1水中氨氮标准曲线数据序号浓度X测定参数Y1测定参数Y2测定参数Y均数10.10.0210.0210.02120.20.040.040.0430.40.0820.0820.08240.80.1580.1580.15851.20.2390.2390.23961.60.3160.3160.316720.3910.3910.391均数0.90.1781428570.1781428570.1781428571.2 选择相应的数据插入散点图，并选择趋势线和公式及相关指数R2（设置标签的数据小数位数适当的位数），如图1：图1水中氨氮标准曲线数据散点图及趋势线1.3 设计由y值依据回归方程计算x值的Excel表：根据不同的回归方程，设计由y计算x值的计算表，表格方式如表2：表2：水中氨氮重复测定结果序号测定参数y分析结果x10.1480.76905991320.1470.76394222730.1460.758824541均值0.1470.7639422271.3.1 常见的函数关系及x计算公式线性方程式：Y=bx+a x=(y-a)/b 式1对数函数方程式：y=bln(x)+a x=EXP(y-a)/b) 式2指数函数方程式：y=ae(bx) x=(lna-lny)/b 式32次多项式：y=ax2+bx+c x=-bb2-4a(c-y)2a 式4注：2次多项式函数可求的虚、实2个解，可根据实际情况选用。幂函数方程式：y=axb x=explny-lna/b式5注：x值不可为“0”。1.4 从直接图表标签中直接获取回归系数b、截距a及相关指数R2。1.5 用函数DEVSQ()分别计算变量x及y值的离均差平方和Lxx、Lyy。1.6 计算回归不确定度：相关指数：R2=1-L剩余Lyy 式中：L剩余：残差平方和：y-y2，Lyy变量y的离均差平方和。1.6.1计算剩余离均差平方和L剩余：L剩余=1-R2Lyy式61.6.2 计算回归标准误uc(yi)=Sy.x=y-y2n-2=L剩余n-2式71.6.3 计算回归曲线标准不确定度： y的估计值y的标准误（标准不确定度）：uc(y)=uc(yi)1n+1m+xi-x2LXX式8 式中：Sy.x （uc(y)）回归标准误；n 回归方程中变量x的个数；m 重复测量值中的变量数；xi重复测量时的测量结果x值；x 标准曲线中变量x的均值；Lxx标准曲线中变量x的离均差平方和。1.6.4 计算回归相对不确定度：urel(y)=Syyi 式9 式中：Sy估计值y的标准误（标准不确定度）；yi 重复测定结果y值的平均值。1.7 计算截距a的不确定度： ua=Sa=Sy.x1n+x2Lxx式10式中：Sy.x回归标准误；n 回归方程中变量x的个数；x 回归方程中变量x的均数；Lxx标准曲线中变量x的离均差平方和。1.7.1截距a的相对不确定度：urel(a)=ua/yi式11式中：ua截距a标准不确定度；yi 重复测定结果y值的平均值。1.8 计算重复测量结果标准不确定度： s(x )=s(xk )/n=u(x)式12式中：s(xk)重复测量结果xk的标准差，可由Excel函数STDEV()求得；n重复测量xk的个数。1.8.1 计算重复测量结果相对标准不确定度：urelxi=uxxi式13式中：xi重复测量结果xi的均值。1.9 计算标准曲线测定结果的合成不确定度：urel=urel2(y)+urel2(a)+urel2(xi)式142 应用实例2.1 线性函数应用纳氏试剂法测定水中的氨氮，标准曲线和样品重复测定数据如下，试对分析结果进行A类不确定度评价。标准曲线数据：参见表1。样品重复测定数据：参见表2。2.1.1 配合散点图及趋势线：参见图12.1.2 A类不确定评价结果：表3：标准曲线测定结果A类不确定度Excel表计算结果参数名称计算公式或Excel函数计算结果变量x离均差平方和LxxDEVSQ()3.18变量y离均差平方和LyyDEVSQ()0.12144回归系数b直接由趋势线给出或使用相关的excel函数求的。0.19540截距a0.00227相关指数R20.99986969残余平方和L剩余公式61.5825E-05回归系数标准差uc(yi)公式70.00178y的标准误（标准不确定度）uc(y)公式80.00124回归相对标准不确定度urel(y)公式90.00840截距a的标准不确定度u(a)公式100.000339截距a的相对标准不确定度Urel(a)公式110.0023086测定结果标准差sSTDEV()0.005118测定结果标准不确定度u(x)公式120.002955测定结果相对标准不确定度urel(xi)公式130.0038677A类合成不确定度urel公式140.0095335注：在线性回归中，回归系数b、截距a、剩余标准差也可以由相关的Excel函数给出。2.2 2次多项式函数应用：用石墨炉原子吸收分光光度法测定水中的镉，标准曲线和样品测定数据如下，试对分析结果进行A类不确度评价。表4：水中镉标准曲线测定数据序号浓度X测定参数Y1测定参数Y2y均值100.0003-0.0003021.00.07750.07640.0769532.50.17380.17020.17245.00.29860.29660.2976510.00.41850.41870.4186均数3.70.193740.192320.19303表5：水中镉样品重复测定数据序号测定参数y分析结果x1分析结果x210.1482.10542183219.6864333720.1472.08935067719.7025045330.1462.07330879819.71854641均值0.1472.089360436舍弃 注：测定结果x由公式： x=-bb2-4ac-y2a 给出。2.2.1 配合散点图及趋势线图2水中镉标准曲线散点图及趋势线2.2.2 A类不确定评价结果：表6：水中镉样品标准曲线测定结果A类不确定度Excel表计算结果参数名称计算公式或excel函数计算结果变量x1离均差平方和LxxDEVSQ()63.8变量y离均差平方和LyyDEVSQ()0.112994118x回归系数b1直接由趋势线给出0.077056x2回归系数b2-0.003536截距a0.001439相关指数R20.999942残余平方和L剩余公式66.55366E-06回归系数标准差uc(yi)公式70.001478023y的标准误（标准不确定度）uc(y)公式80.001119786回归相对标准不确定度urel(y)公式90.00761759截距a的标准不确定度u(a)公式100.000306188截距a的相对标准不确定度Urel(a)公式110.00208291测定结果标准差sSTDEV()0.016056519测定结果标准不确定度u(x)公式120.009270236测定结果相对标准不确定度urel(xi)公式130.00443688A类合成不确定度urel公式140.009058262.3 指数函数应用：用砷-铈还原法测定碘离子，标准曲线和样品重复测定结果如下，试对分析结果A类不确定度进行评价。表7砷-铈还原法测定碘离子标准系列序号浓度X测定参数Y1测定参数Y2y均值101.3341.3342500.9940.99431000.7350.73541500.5070.50752000.3640.36462500.2610.26173000.1770.177均数1500.624571430.62457143表8 砷-铈还原法测定碘离子样品重复测定结果序号测定参数y分析结果x10.261247.7528620.262247.18551830.26248.32238均值0.261247.7535862.2.1 配合散点图及趋势线图3水中碘测定标准曲线散点图及趋势线2.2.2 A类不确定评价结果：表7：水中碘样品标准曲线测定结果A类不确定度Excel表计算结果参数名称计算公式或excel函数计算结果变量x离均差平方和LxxDEVSQ()70000变量y离均差平方和LyyDEVSQ()1.066185714回归系数b直接由趋势线给出0.00674037截距a1.38640085相关指数R20.9985116残余平方和L剩余公式60.001586911回归系数标准差uc(yi)公式70.017815223y的标准误（标准不确定度）uc(y)公式80.013944909回归相对标准不确定度urel(y)公式90.05342877截距a的标准不确定度u(a)公式100.003817548截距a的相对标准不确定度Urel(a)公式110.01462662测定结果标准差sSTDEV()0.568431513测定结果标准不确定度u(x)公式120.328184087测定结果相对标准不确定度urel(xi)公式130.00132464A类合成不确定度urel公式140.055410523 讨论3.1 本文由Excel散点图的趋势线直接给出相关回归参数以及相关指数等统计参数，很方便的计算分析结果的A类不确定度，并可以对不同的函数方程的优劣进行优选。3.2 应用公式：L剩余=1-R2Lyy计算剩余平方和，避免了一些复杂的运算，并且适用于线性、非线性的多种函数方程关系。3.3 应用公式 urel(y)=Syyi计算相对不确定度，以样品测定y值的均数为基础，这样可以避免将y值标准不