excel学习笔记——校准曲线的统计检验

excel中的数据分析工具可解决大部分的统计检验项目，但数据分析工具输出的结果无法实现动态更新。这一讲介绍如何运用excel函数来制作动态更新的统计检验表格。 

统计检验的方法主要来源于国家标准GB/T 35655-2017《化学分析方法验证确认和内部质量控制实施指南 色谱分析》以及GB 17378.2-2007《海洋监测规范 第2部分: 数据处理与分析质量控制》。

本讲所用的校准曲线，以浓度为横坐标，以响应值为纵坐标。横坐标的数值位于表格的A1～A5，纵坐标的数值位于表格的B1～B5。

1.异常值检验

检验各点测定值是否为异常值

——残差

——剩余标准偏差

当M值＞1.5，判断为该浓度点为异常值

=ABS(B1-(SLOPE($B$1:$B$5,$A$1:$A$5)*A1+INTERCEPT($B$1:$B$5,$A$1:$A$5)))/STEYX($B$1:$B$5,$A$1:$A$5)

其他点依次类推

2.截距检验

检验曲线是否通过原点，将截距值与0进行t检验：

——截距

——截距标准误差

=ABS(INTERCEPT(B1:B5,A1:A5))/INDEX(LINEST(B1:B5,A1:A5,TRUE,TRUE),2,2)

亦可直接计算P值，直观判断截距值与0是否有显著性差异

=TDIST(ABS(INTERCEPT(B1:B5,A1:A5))/INDEX(LINEST(B1:B5,A1:A5,TRUE,TRUE),2,2),N-2,2)

截距的95%置信区间

置信下限=INTERCEPT(B1:B5,A1:A5)-TINV(0.05,N-2)*INDEX(LINEST(B1:B5,A1:A5,TRUE,TRUE),2,2)

置信上限=INTERCEPT(B1:B5,A1:A5)+TINV(0.05,N-2)*INDEX(LINEST(B1:B5,A1:A5,TRUE,TRUE),2,2)

3.残差

通过下式计算各浓度点的残差

=y-(SLPOE(y's,x's)*x+INTERCEPT(y's,x's))

对原始的残差值进行标准化处理，得到标准化残差（学生化残差）

=ABS(B1-(SLOPE($B$1:$B$5,$A$1:$A$5)*A1+INTERCEPT($B$1:$B$5,$A$1:$A$5)))/(STEYX($B$1:$B$5,$A$1:$A$5)*SQRT(1-(1/N+(A1-AVERAGE($A$1:$A$5))^2/DEVSQ($A$1:$A$5))))

其他点依次类推

通过绘制残差图，观察残差的分布情况，有助于确认异常值。