实验室标准曲线Excel表评定
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
回归系数b
直接由趋势线给出或使用相关的excel函数求的。
0.19540
截距a
0.00227
相关指数R2
0.99986969
残余平方和L剩余
公式6
1.5825E-05
回归系数标准差
公式7
0.00178
的标准误（标准不确定度）
公式8
0.00124
回归相对标准不确定度
公式9
0.00840
截距a的标准不确定度u(a)
6.55366E-06
回归系数标准差
公式7
0.001478023
的标准误（标准不确定度）
公式8
0.001119786
回归相对标准不确定度
公式9
0.00761759
截距a的标准不确定度u(a)
公式10
0.000306188
截距a的相对标准不确定度Urel(a)
公式11
0.00208291
测定结果标准差s
3.2应用公式： 计算剩余平方和，避免了一些复杂的运算，并且适用于线性、非线性的多种函数方程关系。
3.3应用公式 计算相对不确定度，以样品测定y值的均数为基础，这样可以避免将 值标准不确定度 转化成相应的x值的麻烦，而且 由于数值太小，往往在非线性函数方程中不在有效范围内，不能转化为合理的x值。A类标准不确定度用相对标准不确定度形式表示更方便与B类不确定度相加和。
标准曲线法是实验室常用分析方法，虽然现代分析仪器都能智能的选择标准曲线类型，并给出相应的计算结果，但是对分析结果的A类不确定度的评价仍需要进行手工计算，由于这类平价计算公式繁多，数据处理过程往往出错。另外由于采取不同的拟合曲线形式，也造成评价的困难。本文采用Excel表的图表给出趋势线参数，直接进行标准曲线A类不确定度评价，效果直观易懂、计算步骤简便、应用范围广，有较强的实用价值，现介绍如下：
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
1.5用函数DEVSQ()分别计算变量x及y值的离均差平方和Lxx、Lyy。
1.6计算回归不确定度：
相关指数： ⑴
式中：L剩余：残差平方和： ，Lyy变量y的离均差平方和。
1.6.1计算剩余离均差平方和L剩余：
……………式6
1.6.2计算回归标准误
………………式7
1.6.3计算回归曲线标准不确定度：
实验室标准曲线（线性、非线性）A类不确定度的Excel评定
苏秀娟1郭琴1刘新荣1苏敬武1
1.威海市疾病预防控制中心，山东威海市264200
摘要：本文用Excel表的图表直接给出的趋势线参数，对实验室标准曲线A类不确定度进行评价，计算步骤少，适应范围广（适用于线性和非线性），有较强的实用意义。
关键词：Excel表标准曲线A类不确定度
参数名称
计算公式或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
测定结果标准不确定度
公式12
0.328184087
测定结果相对标准不确定度
公式13
0.00132464
A类合成不确定度urel
公式14
0.05541052
3讨论
3.1本文由Excel散点图的趋势线直接给出相关回归参数以及相关指数等统计参数，很方便的计算分析结果的A类不确定度，并可以对不同的函数方程的优劣进行优选。
表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表计算结果
STDEV()
0.016056519
测定结果标准不确定度
公式12
0.009270236
测定结果相对标准不确定度
公式13
0.00443688
A类合成不确定度urel
公式14
0.00905826
2.3指数函数应用：
用砷-铈还原法测定碘离子，标准曲线和样品重复测定结果如下，试对分析结果A类不确定度进行评价。
均数
0.9
0.178142857
0.178142857
0.178142857
1.2选择相应的数据插入散点图，并选择趋势线和公式及相关指数R2（设置标签的数据小数位数适当的位数），如图1：⑵
图1水中氨氮标准曲线数据散点图及趋势线
1.3设计由y值依据回归方程计算x值的Excel表：
根据不同的回归方程，设计由y计算x值的计算表，表格方式如表2：
参考文献：
⑴、金丕焕医学统计方法上海：上海医科大学出版社1993
⑵、候彧马云燕精通Microsoft Excel 2002中文版北京：清华大学出版社 2002
⑶、中华人民共和国国家计量技术规范 JFF1059-1999测量不确定度评价与表示北京：中国计量出版社 1999
公式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函数给出。
参数名称
计算公式或excel函数
计算结果
变量x离均差平方和Lxx
DEVSQ()
70000
变量y离均差平方和Lyy
DEVSQ()
1.066185714
回归系数b
直接由趋势线给出
0.00674037
截距a
1.38640085
相关指数R2
0.9985116
残余平方和L剩余
公式6
0.001586911
回归系数标准差
y的估计值 的标准误（标准不确定度）：
…………式8
式中：Sy.x（ ）回归标准误；n回归方程中变量x的个数；m重复测量值中的变量数；xi重复测量时的测量结果x值； 标准曲线中变量x的均值；Lxx标准曲线中变量x的离均差平方和。
1.6.4计算回归相对不确定度：
……………式9
式中： 估计值 的标准误（标准不确定度）； 重复测定结果y值的平均值。
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
序号
浓度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
1、标准曲线A类不确定度的评价步骤：
标准曲线A类不确定评价主要有以下3部分组成：a、来自回归曲线的回归系数不确定度；b、来自回归曲线截距a不确定度；c、重复测定的不确定度。本文对自变量x的抽样误差未考虑。a、b两部分均是由回归曲线剩余平方和计算来的。
1.1将分析数据输入Excel表：格式如表1：
表1水中氨氮标准曲线数据
指数函数方程式：y=ae(bx)x=(lna-lny)/b……………………式3
2次多项式：y=ax2+bx+c ………………式4
注：2次多项式函数可求的虚、实2个解，可根据实际情况选用。
幂函数方程式：y=axb ……………式5
注：x值不可为“0”。
1.4从直接图表标签中直接获取回归系数b、截距a及相关指数R2。
表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+ax=(y-a)/b………………………式1
对数函数方程式：y=bln(x)+a x=EXP(y-a)/b)……………………式2
标准曲线数据：参见表1。
样品重复测定数据：参见表2。
2.1.1配合散点图及趋势线：参见图1
2.1.2A类不确定评价结果：
表3：标准曲线测定结果A类不确定度Excel表计算结果
参数名称
计算公式或Excel函数
计算结果
变量x离均差平方和Lxx
DEVSQ()
3.18
变量y离均差平方和Lyy
DEVSQ()
0.12144
1.7计算截距a的不确定度：
……………式10
式中：Sy.x回归标准误；n回归方程中变量x的个数； 回归方程中变量x的均数；Lxx标准曲线中变量x的离均差平方和。
1.7.1截距a的相对不确定度：
……………式11
式中： 截距a标准不确定度； 重复测定结果y值的平均值。
1.8计算重复测量结果标准不确定度：
⑶…………式12
式中：s(xk)重复测量结果xk的标准差，可由Excel函数STDEV()求得；n重复测量xk的个数。
1.8.1计算重复测量结果相对标准不确定度：
……………式13
式中： 重复测量结果xi的均值。
1.9计算标准曲线测定结果的合成不确定度：
……………式14
2应用实例
2.1线性函数应用
纳氏试剂法测定水中的氨氮，标准曲线和样品重复测定数据如下，试对分析结果进行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
均数
Biblioteka Baidu150
0.62457143
0.62457143
19.70250453
3
0.146
2.073308798
19.71854641
均值
0.147
2.089360436
舍弃
注：测定结果x由公式： 给出。
2.2.1配合散点图及趋势线
图2水中镉标准曲线散点图及趋势线
2.2.2 A类不确定评价结果：
表6：水中镉样品标准曲线测定结果A类不确定度Excel表计算结果
公式7
0.017815223
的标准误（标准不确定度）
公式8
0.013944909
回归相对标准不确定度
公式9
0.05342877
截距a的标准不确定度u(a)
公式10
0.003817548
截距a的相对标准不确定度Urel(a)
公式11
0.01462662
测定结果标准差s
STDEV()
0.568431513
直接由趋势线给出或使用相关的excel函数求的。
0.19540
截距a
0.00227
相关指数R2
0.99986969
残余平方和L剩余
公式6
1.5825E-05
回归系数标准差
公式7
0.00178
的标准误（标准不确定度）
公式8
0.00124
回归相对标准不确定度
公式9
0.00840
截距a的标准不确定度u(a)
6.55366E-06
回归系数标准差
公式7
0.001478023
的标准误（标准不确定度）
公式8
0.001119786
回归相对标准不确定度
公式9
0.00761759
截距a的标准不确定度u(a)
公式10
0.000306188
截距a的相对标准不确定度Urel(a)
公式11
0.00208291
测定结果标准差s
3.2应用公式： 计算剩余平方和，避免了一些复杂的运算，并且适用于线性、非线性的多种函数方程关系。
3.3应用公式 计算相对不确定度，以样品测定y值的均数为基础，这样可以避免将 值标准不确定度 转化成相应的x值的麻烦，而且 由于数值太小，往往在非线性函数方程中不在有效范围内，不能转化为合理的x值。A类标准不确定度用相对标准不确定度形式表示更方便与B类不确定度相加和。
标准曲线法是实验室常用分析方法，虽然现代分析仪器都能智能的选择标准曲线类型，并给出相应的计算结果，但是对分析结果的A类不确定度的评价仍需要进行手工计算，由于这类平价计算公式繁多，数据处理过程往往出错。另外由于采取不同的拟合曲线形式，也造成评价的困难。本文采用Excel表的图表给出趋势线参数，直接进行标准曲线A类不确定度评价，效果直观易懂、计算步骤简便、应用范围广，有较强的实用价值，现介绍如下：
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
1.5用函数DEVSQ()分别计算变量x及y值的离均差平方和Lxx、Lyy。
1.6计算回归不确定度：
相关指数： ⑴
式中：L剩余：残差平方和： ，Lyy变量y的离均差平方和。
1.6.1计算剩余离均差平方和L剩余：
……………式6
1.6.2计算回归标准误
………………式7
1.6.3计算回归曲线标准不确定度：
实验室标准曲线（线性、非线性）A类不确定度的Excel评定
苏秀娟1郭琴1刘新荣1苏敬武1
1.威海市疾病预防控制中心，山东威海市264200
摘要：本文用Excel表的图表直接给出的趋势线参数，对实验室标准曲线A类不确定度进行评价，计算步骤少，适应范围广（适用于线性和非线性），有较强的实用意义。
关键词：Excel表标准曲线A类不确定度
参数名称
计算公式或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
测定结果标准不确定度
公式12
0.328184087
测定结果相对标准不确定度
公式13
0.00132464
A类合成不确定度urel
公式14
0.05541052
3讨论
3.1本文由Excel散点图的趋势线直接给出相关回归参数以及相关指数等统计参数，很方便的计算分析结果的A类不确定度，并可以对不同的函数方程的优劣进行优选。
表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表计算结果
STDEV()
0.016056519
测定结果标准不确定度
公式12
0.009270236
测定结果相对标准不确定度
公式13
0.00443688
A类合成不确定度urel
公式14
0.00905826
2.3指数函数应用：
用砷-铈还原法测定碘离子，标准曲线和样品重复测定结果如下，试对分析结果A类不确定度进行评价。
均数
0.9
0.178142857
0.178142857
0.178142857
1.2选择相应的数据插入散点图，并选择趋势线和公式及相关指数R2（设置标签的数据小数位数适当的位数），如图1：⑵
图1水中氨氮标准曲线数据散点图及趋势线
1.3设计由y值依据回归方程计算x值的Excel表：
根据不同的回归方程，设计由y计算x值的计算表，表格方式如表2：
参考文献：
⑴、金丕焕医学统计方法上海：上海医科大学出版社1993
⑵、候彧马云燕精通Microsoft Excel 2002中文版北京：清华大学出版社 2002
⑶、中华人民共和国国家计量技术规范 JFF1059-1999测量不确定度评价与表示北京：中国计量出版社 1999
公式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函数给出。
参数名称
计算公式或excel函数
计算结果
变量x离均差平方和Lxx
DEVSQ()
70000
变量y离均差平方和Lyy
DEVSQ()
1.066185714
回归系数b
直接由趋势线给出
0.00674037
截距a
1.38640085
相关指数R2
0.9985116
残余平方和L剩余
公式6
0.001586911
回归系数标准差
y的估计值 的标准误（标准不确定度）：
…………式8
式中：Sy.x（ ）回归标准误；n回归方程中变量x的个数；m重复测量值中的变量数；xi重复测量时的测量结果x值； 标准曲线中变量x的均值；Lxx标准曲线中变量x的离均差平方和。
1.6.4计算回归相对不确定度：
……………式9
式中： 估计值 的标准误（标准不确定度）； 重复测定结果y值的平均值。
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
序号
浓度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
1、标准曲线A类不确定度的评价步骤：
标准曲线A类不确定评价主要有以下3部分组成：a、来自回归曲线的回归系数不确定度；b、来自回归曲线截距a不确定度；c、重复测定的不确定度。本文对自变量x的抽样误差未考虑。a、b两部分均是由回归曲线剩余平方和计算来的。
1.1将分析数据输入Excel表：格式如表1：
表1水中氨氮标准曲线数据
指数函数方程式：y=ae(bx)x=(lna-lny)/b……………………式3
2次多项式：y=ax2+bx+c ………………式4
注：2次多项式函数可求的虚、实2个解，可根据实际情况选用。
幂函数方程式：y=axb ……………式5
注：x值不可为“0”。
1.4从直接图表标签中直接获取回归系数b、截距a及相关指数R2。
表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+ax=(y-a)/b………………………式1
对数函数方程式：y=bln(x)+a x=EXP(y-a)/b)……………………式2
标准曲线数据：参见表1。
样品重复测定数据：参见表2。
2.1.1配合散点图及趋势线：参见图1
2.1.2A类不确定评价结果：
表3：标准曲线测定结果A类不确定度Excel表计算结果
参数名称
计算公式或Excel函数
计算结果
变量x离均差平方和Lxx
DEVSQ()
3.18
变量y离均差平方和Lyy
DEVSQ()
0.12144
1.7计算截距a的不确定度：
……………式10
式中：Sy.x回归标准误；n回归方程中变量x的个数； 回归方程中变量x的均数；Lxx标准曲线中变量x的离均差平方和。
1.7.1截距a的相对不确定度：
……………式11
式中： 截距a标准不确定度； 重复测定结果y值的平均值。
1.8计算重复测量结果标准不确定度：
⑶…………式12
式中：s(xk)重复测量结果xk的标准差，可由Excel函数STDEV()求得；n重复测量xk的个数。
1.8.1计算重复测量结果相对标准不确定度：
……………式13
式中： 重复测量结果xi的均值。
1.9计算标准曲线测定结果的合成不确定度：
……………式14
2应用实例
2.1线性函数应用
纳氏试剂法测定水中的氨氮，标准曲线和样品重复测定数据如下，试对分析结果进行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
均数
Biblioteka Baidu150
0.62457143
0.62457143
19.70250453
3
0.146
2.073308798
19.71854641
均值
0.147
2.089360436
舍弃
注：测定结果x由公式： 给出。
2.2.1配合散点图及趋势线
图2水中镉标准曲线散点图及趋势线
2.2.2 A类不确定评价结果：
表6：水中镉样品标准曲线测定结果A类不确定度Excel表计算结果
公式7
0.017815223
的标准误（标准不确定度）
公式8
0.013944909
回归相对标准不确定度
公式9
0.05342877
截距a的标准不确定度u(a)
公式10
0.003817548
截距a的相对标准不确定度Urel(a)
公式11
0.01462662
测定结果标准差s
STDEV()
0.568431513