实验室标准曲线excel表评定

实验室标准曲线（线性、非线性）A 类不确定度的 Excel 评定 苏秀娟 1 郭琴 1 刘新荣 1 苏敬武 1 1. 威海市疾病预防控制中心，山东威海市 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 均数 1 0.1 0.021 0.021 0.021 2 0.2 0.04 0.04 0.04 3 0.4 0.082 0.082 0.082 4 0.8 0.158 0.158 0.158 5 1.2 0.239 0.239 0.239 6 1.6 0.316 0.316 0.316 7 2 0.391 0.391 0.391 均数 0.9 0.178142857 0.178142857 0.178142857 1.2 选择相应的数据插入散点图，并选择趋势线和公式及相关指数 R2（设置标签的数据小 数位数适当的位数） ，如图 1： 0 0.5 1 1.5 2 2.5 0 0.1 0.2 0.3 0.4 0.5 图 1 水中氨氮标准曲线数据散点图及趋势线 1.3 设计由 y 值依据回归方程计算 x 值的 Excel 表： 根据不同的回归方程，设计由 y 计算 x 值的计算表，表格方式如表 2： 表 2：水中氨氮重复 测定结果 序号 测定参数 y 分析结果 x 1 0.148 0.769059913 2 0.147 0.763942227 3 0.146 0.758824541 均值 0.147 0.763942227 1.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 式 3 2 次多项式：y=ax 2+bx+c 式 4= 24()2 注：2 次多项式函数可求的虚、实 2 个解，可根据实际情况选用。 幂函数方程式：y=ax b 式 5=(lnln)/ 注：x 值不可为 “0”。 1.4 从直接图表标签中直接获取回归系数 b、截距 a 及相关指数 R2。 1.5 用函数 DEVSQ()分别计算变量 x 及 y 值的离均差平方和 Lxx、L yy。 1.6 计算回归不确定度： 相关指数： R 2=1-L剩余Lyy 式中：L 剩余 ：残差平方和： ，L yy 变量 y 的离均差平方和。(y-y)2 1.6.1 计算剩余离均差平方和 L 剩余 ： 式 6L剩余 =(1-R2)Lyy 1.6.2 计算回归标准误 式 7()=S.= ()22 = 剩余n-2 1.6.3 计算回归曲线标准不确定度： y 的估计值 的标准误（标准不确定度）：y 式 8 ()=() (1+1+()2) 式中：S y.x （ ）回归标准误；n 回归方程中变量 x 的个数；m 重复测量值中的变() 量数；x i 重复测量时的测量结果 x 值； 标准曲线中变量 x 的均值；Lxx 标准曲线中变量 xx 的离均差平方和。 1.6.4 计算回归相对不确定度： 式 9u()=S 式中： 估计值 的标准误（标准不确定度） ； 重复测定结果 y 值的平均值。Sy y yi 1.7 计算截距 a 的不确定度： 式 10ua=Sa=Sy.x 1+2 式中：S y.x 回归标准误；n 回归方程中变量 x 的个数； 回归方程中变量 x 的均数； Lxx 标准曲线中变量 x 的离均差平方和。 1.7.1 截距 a 的相对不确定度： 式 11()=/ 式中： 截距 a 标准不确定度； 重复测定结果 y 值的平均值。 yi 1.8 计算重复测量结果标准不确定度： 式 12 ( )=s( )/n=() 式中：s(x k)重复测量结果 xk 的标准差，可由 Excel 函数 STDEV()求得；n 重复测量 xk的 个数。 1.8.1 计算重复测量结果相对标准不确定度： 式 13 ()=() 式中： 重复测量结果 xi 的均值。xi 1.9 计算标准曲线测定结果的合成不确定度： 式 14u= u 2()+u2()+u2() 2 应用实例 2.1 线性函数应用 纳氏试剂法测定水中的氨氮，标准曲线和样品重复测定数据如下，试对分析结果进行 A 类不确定度评价。 标准曲线数据：参见表 1。 样品重复测定数据：参见表 2。 2.1.1 配合散点图及趋势线：参见图 1 2.1.2 A 类不确定评价结果： 表 3：标准曲线测定结果 A 类不确定度 Excel 表计算结果 参数名称 计算公式或 Excel 函数 计算结果 变量 x 离均差平方和 Lxx DEVSQ() 3.18 变量 y 离均差平方和 Lyy DEVSQ() 0.12144 回归系数 b 0.19540 截距 a 0.00227 相关指数 R2 直接由趋势线给出或使用 相关的 excel 函数求的。 0.99986969 残余平方和 L 剩余 公式 6 1.5825E-05 回归系数标准差 () 公式 7 0.00178 的标准误（标准不确定度）y () 公式 8 0.00124 回归相对标准不确定度 u() 公式 9 0.00840 截距 a 的标准不确定度 u(a) 公式 10 0.000339 截距 a 的相对标准不确定度 Urel(a) 公式 11 0.0023086 测定结果标准差 s STDEV() 0.005118 测定结果标准不确定度 ( ) 公式 12 0.002955 测定结果相对标准不确定度 () 公式 13 0.0038677 A 类合成不确定度 urel 公式 14 0.0095335 注：在线性回归中，回归系数 b、截距 a、剩余标准差也可以由相关的 Excel 函数给出。 2.2 2 次多项式函数应用： 用石墨炉原子吸收分光光度法测定水中的镉，标准曲线和样品测定数据如下，试对分 析结果进行 A 类不确度评价。 表 4：水中镉标准曲线测定数据 序号 浓度 X 测定参数 Y1 测定参数 Y2 y 均值 1 0 0.0003 -0.0003 0 2 1.0 0.0775 0.0764 0.07695 3 2.5 0.1738 0.1702 0.172 4 5.0 0.2986 0.2966 0.2976 5 10.0 0.4185 0.4187 0.4186 均数 3.7 0.19374 0.19232 0.19303 表 5：水中镉样品重复测定数据 序号 测定参数 y 分析结果 x1 分析结果 x2 1 0.148 2.105421832 19.68643337 2 0.147 2.089350677 19.70250453 3 0.146 2.073308798 19.71854641 均值 0.147 2.089360436 舍弃 注：测定结果 x 由公式： 给出。= 24()2 2.2.1 配合散点图及趋势线 图 2 水中镉标准曲线散点图及趋势线 2.2.2 A 类不确定评价结果： 表 6：水中镉样品标准曲线测定结果 A 类不确定度 Excel 表计算结果 参数名称 计算公式或 excel 函数 计算结果 变量 x1 离均差平方和 Lxx DEVSQ() 63.8 变量 y 离均差平方和 Lyy DEVSQ() 0.112994118 x 回归系数 b1 0.077056 x2 回归系数 b2 -0.003536 截距 a 0.001439 相关指数 R2 直接由趋势线给出 0.999942 残余平方和 L 剩余 公式 6 6.55366E-06 回归系数标准差 () 公式 7 0.001478023 的标准误（标准不确定度）y () 公式 8 0.001119786 回归相对标准不确定度 u() 公式 9 0.00761759 截距 a 的标准不确定度 u(a) 公式 10 0.000306188 截距 a 的相对标准不确定度 Urel(a) 公式 11 0.00208291 测定结果标准差 s STDEV() 0.016056519 测定结果标准不确定度 ( ) 公式 12 0.009270236 测定结果相对标准不确定度 () 公式 13 0.00443688 A 类合成不确定度 urel 公式 14 0.00905826 2.3 指数函数应用： 用砷-铈还原法测定碘离子，标准曲线和样品重复测定结果如下，试对分析结果 A 类不 确定度进行评价。 表 7 砷-铈还原法测定碘离子标准系列 序号 浓度 X 测定参数 Y1 测定参数 Y2 y 均值 1 0 1.334 1.334 2 50 0.994 0.994 3 100 0.735 0.735 4 150 0.507 0.507 5 200 0.364 0.364 6 250 0.261 0.261 7 300 0.177 0.177 均数 150 0.62457143 0.62457143 表 8 砷 -铈还原法测定碘离子样品重复测定结果 序号 测定参数 y 分析结果 x 1 0.261 247.75286 2 0.262 247.185518 3 0.26 248.32238 均值 0.261 247.753586 2.2.1 配合散点图及趋势线 图 3 水中碘测定标准曲线散点图及趋势线 2.2.2 A 类不确定评价结果： 表 7：水中碘样品标准曲线测定结果 A 类不确定度 Excel 表计算结果 参数名称 计算公式或 excel 函数 计算结果 变量 x 离均差平方和 Lxx DEVSQ() 70000 变量 y 离均差平方和 Lyy DEVSQ() 1.066185714 回归系数 b 0.00674037 截距 a 1.38640085 相关指数 R2 直接由趋势线给出 0.9985116 残余平方和 L 剩余 公式 6 0.001586911 回归系数标准差 () 公式 7 0.017815223 的标准误（标准不确定度）y () 公式 8 0.013944909 回归相对标准不确定度 u() 公式 9 0.05342877 截距 a 的标准不确定度 u(a) 公式 10 0.003817548 截距 a 的相对标准不确定度 Urel(a) 公式 11 0.01462662 测定结果标准差 s STDEV() 0.568431513 测定结果标准不确定度 () 公式 12 0.328184087 测定结果相对标准不确定度 () 公式 13 0.00132464 3 讨论 3.1 本文由 Excel 散点图的趋势线直接给出相关回归参数以及相关指数等统计参数，很方便 的计算分析结果的 A 类不确定度，并可以对不同的函数方程的优劣进行优选。 3.2 应用公式： 计算剩余平方和，避免了一些复杂的运算，并且适用L剩余 =(1-R2)Lyy 于线性、非线性的多种函数方程关系。 3.3 应用公式 计算相对不确定度，以样品测定 y 值的均数为基础，这样可u()=S 以避免将 值标准不确定度 转化成相应的 x 值的麻烦，而且