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

实验室管理Excel软件在标准样品定值统计运算中的应用胡修伟,张翠敏,彭 霞,胡晓燕,唐本玲(钢铁研究总院分析测试研究所,北京100081)中图分类号:O21 文献标志码:B 文章编号:1001 4020(2010)07 0834 03GB/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]#nk=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)2n-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 chartA B C D E F G H I J K L M N 1名称1编号1项目C2实验室测量值极差组内标准偏差S2i n i/S2i平均值方法312344A5B6C7D8E9F10G11H12I13总平均值S N C0.01C0.05C n1n2b1b2W0.01()W0.05()W() 14151617中位值G0.01G0.05G min G max W0.01()W0.05()W() 1819推荐值标准偏差数据组数具体操作步骤如下:(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%个数据(N =4n),M4~M 12单元格用于输入各实验室所采用分析方法。

(5)在F4单元格内输入公式&=MAX (B4:E4)-M IN (B4:E4)∋,并拖动填充柄至F12单元格,即可获得实验室内数据极差。

(6)在H 4单元格内输入公式&=POWER (STDEV(B4:E4),2)∋,并拖动填充柄至H 12单元格,即可获得实验室内数据方差。

(7)在L4单元格内输入公式&=AVERA GE (B4:E4)∋,并拖动填充柄至L12单元格,即可获得实验室数据平均值。

(8)在A14、B14、C14、A18单元格内分别输入公式&=A VERAGE (L4:L12)∋、&=STDEV (L4:L12)∋、&=COUNT A (L4:L12)∋、&=M EDIAN (L4:L12)∋,分别计算总平均值、标准偏差、数据组数(实验室数)、中位值。

(9)在F14单元格内输入公式&=M AX(H 4:H 12)/SU M(H 4:H 12)∋计算柯克伦检验统计量,在H 14单元格内输入公式&=COUNT (B3:E3)∋计算实验室重复测量次数,在D14单元格、E14单元格内分别用INDEX 函数引用DAT A 工作表内柯克伦检验临界值C(n -1,a *)和C(n -1,a)数据。

INDEX 函数为二维引用函数,其语法为:INDEX(arr ay ,row _num,column_num ),其中ar r ay 为指定的查找区域,row _num,column_num 分别为行号和列号[3]。

在D14、E14单元格分别输入公式&=IN DEX (DATA!$L:$Q,C14,H 14)∋、&==IN DEX (DAT A!$E:$J,C14,H 14)∋,即分别在DA TA 工作表的L ~Q 列、E ~J 列查找C14中数据行,H 14中数据列所定位的单元格数据并引用。

(10)在D18、E18单元格分别输入公式&=(A14-MIN (L4:L12)/B14∋&=(MAX (L4:L12)-A14)/B14∋,计算格拉布斯检验上、下侧统计量,在B18、C18单元格用LOOKUP 函数引用DATA 工作表内格拉布斯检验临界值G 1-a *(n)和G 1-a (n)数据。

VLOOKUP 函数为列查找函数,其语法为:VLOOKUP(loo kup_value,table_array,co l_index _num,rang e_lookup),其中lookup_value 为首列查找数据,table_arr ay 为查找区域,col_index _num 为返回数据位于第几列,rang e_lo okup 为进行精确查找或模糊查找,为零或缺省时函数进行精确查找[3]。

在B18、C18单元格分别输入公式&=VLOOK UP(C14,DAT A!$A:$C,2)∋、&=VLOOKUP (C14,DAT A!$A:$C,3)∋,即分别在DAT A 工作表A ~C 列中查找A 列为C14单元格数据的行,并返回该行B 列、C 列单元格中的数据。

(11)在L14、M14、L18、M 18单元格用VLOOKU P 函数引用DATA 工作表内样本容量分别为n 、N 时a =p =0.01和a =p =0.05的p 分位数,公式与步骤(10)相似,不累述。

(12)在N14、N18单元格计算样本容量为N 、n 时的夏皮罗 威尔克检验统计量,涉及运算较多,以N14单元格计算样本容量为N 时w 为例说明如下:由式(1),分别计算对应每个k =1,2,∀∀l 的a k (x n +1-k -x k )2,这里需在15~16行计算25个a k (x n +1-k -x k )2数据,涉及MAX 、M IN 、LARGE 、SMA LL 、VLOOKUP 函数。