Excel软件在标准样品定值统计运算中的应用

实验室管理

Excel软件在标准样品定值统计运算中的应用

胡修伟,张翠敏,彭 霞,胡晓燕,唐本玲

(钢铁研究总院分析测试研究所,北京100081)

中图分类号:O21 文献标志码:B 文章编号:1001 4020(2010)07 0834 03

GB/T15000系列 标准样品工作导则要求标准样品定值一般采用多家实验室协作定值的方式,标准值确定过程中需应用正态性检验、异常值检验、等精度检验等手段判断数据正确性,数据运算量较大。尤其是夏皮罗 威尔克检验较为复杂,常用的统计软件如SPSS、SAS、OriginPro等在处理该统计时需编写程序,一般使用者难于掌握[1]。

Excel软件计算功能强大、操作简单,是目前应用最广泛的办公软件之一。本工作设计了一个Excel电子表格,使用者只需输入各实验室测试原始数据即可由Excel软件自动完成标准样品定值统计运算中所涉及的全部数据,无需借助其他专业软件。

1 标准样品定值统计运算中需计算和引用

的数据

将标准样品定值统计运算中需计算和引用10多种相关数据分别由Excel公式直接计算数据、引用数据或间接计算数据两类。

1.1 由Excel公式直接计算

极差、平均值、总平均值、标准偏差、数据组数、中位值和格拉布斯检验统计量等数据,具有定义简单、数据运算量小、Ex cel自带公式等特点,可由Excel公式直接计算获得。

1.2 引用数据或间接计算数据

1.2.1 正态性检验

根据GB/T4882-2001 数据的统计处理和解释正态性检验,当数据量不太大时(8!n!50),一般采用夏皮罗 威尔克检验(后经弗朗夏扩充,当n!100时均可使用[2])。

夏皮罗 威尔克检验是基于次序统计量对它们期望值的回归,是一个完全样本的方差分析形式的检验。检验统计量为样本次序统计量线性组合的平

收稿日期:2009 10 07方与通常的方差估计量之比。建立在次序观测值基础上,将n个独立观测值按非降次序记为x(1),x (2)∀x(n),如果一些观测值相等,按原数列中出现的次数,将相等的观测值重复列出,然后计算统计量W:

W=

[#l k=1 k(x n+1-k-x k)2]

#n

k=1

(x k-x)2

(1)式中:W为夏皮罗 威尔克检验统计量;k取值根据n 的奇偶性分别取1,2,3∀,n/2(n为偶数)或1,2, 3∀(n-1)/2(n为奇数); k为样本容量为n时对应k的一组特定系数,可由系数表查出,l值视n为奇数或偶数分别取(n-1)/2和n/2。

在显著性水平a=p下,如果统计量W值小于其p分位数则拒绝零假设,一般常用a=p=0.05和a=p=0.01时的p分位数判断正态性,即对于样本容量为n的一组测试数据,计算W,然后根据以下原则判断:

(1)若W大于样本容量为n,且a=p=0.05时的p分位数,则这组测试数据呈正态分布;

(2)若W介于样本容量为n,且a=p=0.05时的p分位数和a=p=0.01时的p分位数之间,则这组测试数据呈近似正态分布;

(3)若W小于样本容量为n,且a=p=0.01时的p分位数,则这组测试数据非正态分布。

1.2.2 异常值检验

根据GB/T4883-2008 数据的统计处理和解释正态样本离群值的判断和处理,异常值检验可采用奈尔检验、格拉布斯检验、狄克逊检验、偏度 丰度检验等方法,标准样品研制中一般进行格拉布斯上侧检验和下侧检验,即分别计算:

G(n)=[x(n)-x]/s(2)

G(n∃)=[x-x(1)]/s(3)式中:G(n)、G(n∃)分别为上、下侧统计量;x(n)、x

% 834 %

(1)分别为样本最大、最小观测值;x、s分别为样本平均值与样本标准差。

确定检验水平a和剔除水平a*,由格拉布斯检验法临界值表中查出对应n,a的临界值G1-a(n)和对应n,a*的临界值G1-a*(n),当G(n)、G(n∃)大于G1-a*(n)为高度异常值,必须剔除,G(n)、G(n∃)大G1-a(n)于时为异常值,需引起注意(一般a为0.05, a*为0.01)。

1.2.3 等精度检验

一般采用柯克伦检验判断数据是否等精度。根据GB/T6379.2-2004 测量方法与结果的准确度,当每个子样本(每个实验室数据)数相同时,先分别计算各实验室数据组内方差,再计算柯克伦检验统计量:

S2=#(x i-x)2

n-1

(4)

C=S2max

#S2(5)

式中:S2为各实验室数据组内方差;x i为各实验室原始数据;x为各实验室平均值;n为实验室独立测试次数;C为柯克伦检验统计量;S2max为各实验室数据组内方差中最大值。

确定检验水平a和剔除水平a*,由柯克伦法临界值表中查出对应n-1,a的临界值C(n-1,a)和对应n-1,a*的临界值C(n-1,a*)。当C大于C (n-1,a*)时,S2max所在实验室数据为高度异常值;当C大于C(n-1,a)时,S2max所在实验室数据为异常值(一般a为0.05,a*为0.01)。

2 Excel电子表格的设计

考虑到Ex cel各版本的普及性,此电子表格基于Ex cel2003设计。

2.1 Excel电子表格设计过程

采用表1所示的Ex cel电子表格作为标准值定值汇总表,在此电子表格内完成全部相关数据运算,由于部分数据不能直接计算,需计算过渡数据,在此将过渡数据一并置于表格内,最终完成后再隐藏相应行或列。

表1 Excel初始电子表格

Tab.1 Excel electronic initial chart

A B C D E F G H I J K L M N 1名称1编号1项目C

2实验室测量值极差组内标准偏差S2i n i/S2i平均值方法

31234

4A

5B

6C

7D

8E

9F

10G

11H

12I

13总平均值S N C0.01C0.05C n1n2b1b2W0.01()W0.05()W() 14

15

16

17中位值G0.01G0.05G min G max W0.01()W0.05()W() 18

19推荐值标准偏差数据组数

具体操作步骤如下:

(1)新建Ex cel工作簿,建立如表1所示工作表,命名为Sheet1。

(2)于此工作簿中建立DAT A工作表,在DATA工作表S~U列输入夏皮罗 威尔克检验p 分位数表、在A~C列输入格拉布斯检验临界值G1-a(n)和G1-a*(n)、在E~J列、L~Q列分别输入柯克伦检验临界值C(n-1,a)和C(n-1,a*),建立A工作表在A1~Z50区域内输入夏皮罗 威尔克检验系数a k。

(3)在行1,2,13,17相应单元格输入说明文字,如名称、编号、项目、平均值等。

(4)在A4~A12单元格内输入协作实验室名称(以A~J代替),B3~E3单元格为实验室重复试验次数(一般为4次),则B4~E12区域用于输入检测原始数据,本工作假设有n个实验室测试得出N

%

835

%