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

G( n)=[ x( n)- x] / s G( n ′ )=[ x - x( 1) ]/ s ( 2) ( 3)
1 标准样品定值统计运算中需计算和引用 的数据
将标准样品定值统计运算中需 计算和引用 10 多种相关数据分别由 Excel 公式直接计算数据 、引 用数据或间接计算数据两类 。 1. 1 由 Excel 公式直接计算 极差 、 平均值 、总平均值 、 标准偏差 、 数据组数 、 中位值和格拉布斯检验统计量等数据 , 具有定义简 单、 数 据 运算 量 小 、Ex cel 自 带公 式 等 特点 , 可 由 Excel公式直接计算获得 。 1. 2 引用数据或间接计算数据 1. 2. 1 正态性检验 根据 GB/ T 4882 2001《数据的统计处理和解 释 正态性检验》 , 当数据量不太大时( 8 ≤n ≤ 50) ,一 般采用夏 皮罗-威 尔克 检验 ( 后 经弗 朗夏 扩充 , 当 n ≤100时均可使用[ 2] ) 。 夏皮罗 -威尔克检验是基于次序统计量对 它们 期望值的回归 , 是一个完全样本的方差分析形式的 检验 。 检验统计量为样本次序统计量线性组合的平
S2 =
2 Excel 电子表格的设计
考虑到 Ex cel 各版本的普及性 , 此电子表格基 于 Ex cel 2003 设计 。 2. 1 Excel 电子表格设计过程 采用表 1 所示的 Ex cel 电子表格作为标准值定 值汇总表 , 在此电子表格内完成全部相关数据运算 , 由于部分数据不能直接计算 , 需计算过渡数据 , 在此 将过渡数据一并置于表格内 , 最终完成后再隐藏相 应行或列 。
＊
A 工作表在 A1 ～ Z50 区域内输入夏皮罗-威尔克检 验系数 a k 。 ( 3)在行 1 , 2 , 13 , 17 相应单元 格输入说 明文 字 , 如名称 、 编号 、项目 、 平均值等 。 ( 4)在 A4 ～ A12 单元格内输入协作实验室名 称( 以 A ～ J 代替) , B3 ～ E3 单元格为实验室重复试 验次数( 一般为 4 次) , 则 B4 ～ E12 区域用于输入检 测原始数据 , 本工作假设有 n 个实验室测试得出 N
＊ 2 ＊ 2 ＊
, 由格拉布斯检
1 -a
验法临界值表中查出对应 n , a 的临界值 G
( n) 和
对应 n , a 的临界值 G 1 -a ＊ ( n) , 当 G( n) 、G( n′ ) 大于 G 1 -a ＊( n) 为高度异常值 , 必须剔除 , G ( n) 、G ( n′ ) 大 G 1 -a( n) 于时为异常值 , 需引起注意( 一般 a 为 0 . 05 , a ＊为 0 . 01) 。 1. 2. 3 等精度检验 一般采用柯克伦检验判断数据是否等精度 。 根 据 GB/ T 6379 . 2 -2004 《测 量方法 与结 果的准 确 度》 , 当每个子样本( 每个实验室数据) 数相同时 , 先 分别计算各实验室数据组内方差 , 再计算柯克伦检 验统计量 :
· 835 ·
胡修伟等 : Excel 软件在标准样品定值统计运算中的应用
个数据( N =4n) , M4 ～ M 12 单元格用于输入各实验 室所采用分析方法 。 ( 5)在 F4 单 元格 内 输入 公式 “ =MAX ( B4 : E4) -M IN ( B4 : E4) ” , 并 拖动 填充 柄至 F 12 单 元 格 , 即可获得实验室内数据极差 。 ( 6) 在 H 4 单 元 格内 输 入 公 式 “ = POWER ( ST DEV ( B4 : E4) , 2) ” , 并拖动填充柄至 H 12 单元 格 , 即可获得实验室内数据方差 。 ( 7)在 L 4 单元格内 输入公式 “ =AVE RAGE ( B4 : E4) ” , 并拖动填充柄至 L 12 单元格 , 即可获得 实验室数据平均值 。 ( 8)在 A14 、B14 、 C14 、A18 单元格内分别输入 公式 “ =A VERAGE ( L4 : L 12) ” 、“ =ST DEV ( L4 : L12) ” 、“ = COUN T A ( L4 : L 12 ) ” 、“ = M EDIAN ( L4 : L12) ” , 分别计算总平均值 、标准偏差 、 数 据组 数( 实验室数) 、中位值 。 ( 9)在 F 14 单元格内输入公式 “ =M AX( H4: H 12) /S UM ( H4: H 12) ” 计算柯克伦检验统计量 , 在 H 14 单元格内输入公式“ =COUN T ( B3 : E3) ” 计算 实验室重复测量次数 , 在 D14 单元格 、E14 单元 格 内分别用 INDEX 函数引用 DA T A 工作表内柯 克 伦检验临界值 C( n1, a ) 和 C( n1 , a) 数据 。 INDEX 函 数 为 二 维 引 用 函 数 , 其 语 法 为 : INDEX( ar ray , row num , column num ) , 其中 array 为指定的查找区域 , row num , colum n num 分 别为行号和列号[ 3] 。 在 D14 、E14 单 元格分别输入 公式 “ =IN DEX ( DAT A ! ＄L : ＄Q , C14 , H 14 ) ” 、“ = = IN DEX ( DA T A ! ＄E : ＄ J , C14 , H 14 ) ” , 即分 别在 DA T A 工作表的 L ～ Q 列 、E ～ J 列查 找 C14 中数 据行 , H 14 中数据列所定位的单元格数据并引用 。 ( 10) 在 D18 、E18 单 元格 分 别输 入 公式 “ = ( A14 - MIN ( L4 : L12)/ B14 ” “ = ( MAX ( L4 : L12) -A14) / B14” , 计 算格拉布 斯检验上 、下侧 统 计量 , 在 B18 、C18 单 元格 用 LOOKUP 函数 引 用 DA TA 工作表内格拉布斯检验临界值 G 1 -a ＊ ( n) 和 G 1 -a( n) 数据 。 VLOOKUP 函 数 为 列 查 找 函 数 , 其语 法 为 : VLOOKUP( loo kup value , t able array , co l index num , range lookup) , 其中 lookup value 为首 列查 找数据 , table array 为查找区域 , col index num 为 返回数据位于第几列 , range lo okup 为进行精确查 找或模糊查找 , 为零或缺省时函数进行精确查找[ 3] 。
＊ ＊
原始数据 ; x 为各实验室平均值 ; n 为实验室独立测 试次数 ; C 为柯克伦检验统计量 ; S2 m ax 为各实验室数 据组内方差中最大值 。 确定检验水平 a 和剔除水平 a , 由柯克伦法来自百度文库 界值表中查出对应 n -1 , a 的临界值 C ( n -1 , a) 和 对应 n -1 , a ＊ 的临界值 C ( n -1 , a ＊) 。 当 C 大于 C ( n1, a ) 时 , S m ax 所在实验室数据为高度异常值 ; 当 C 大于 C ( n -1 , a) 时 , S m ax 所在实验室数据为异 常值( 一般 a 为 0 . 05 , a 为 0 . 01) 。
实验室管理
Excel 软件在标准样品定值统计运算中的应用
胡修伟 , 张翠敏 , 彭 霞 , 胡晓燕 , 唐本玲
( 钢铁研究总院 分析测试研究所 , 北京 100081) 中图分类号 : O 21 文献标志码 :B 文章编号 :1001-4020( 2010) 07 -0834-03
GB/ T 15000 系列《标准样品工作导则》 要求标 准样品定值一般采用多家实验室协作定值的方式 , 标准值确定过程中需应用正态性检验 、异常值检验 、 等精度检验等手段判断数据正确性 , 数据运算量较 大 。 尤其是夏皮罗-威尔克检验较为复杂 , 常用的统 计软件如 SPSS 、 SAS 、 OriginP ro 等在处理该统计时 需编写程序 , 一般使用者难于掌握
具体操作步骤如下 : ( 1)新建 Ex cel 工作簿 , 建立如表 1 所示工作 表 , 命名为 Shee t 1 。 ( 2) 于 此 工 作 簿 中 建 立 DA T A 工 作 表 , 在 DA TA 工作表 S ～ U 列输入夏皮罗-威尔克 检验 p 分位数表 、在 A ～ C 列输 入格拉 布斯 检验 临界 值 G 1 -a( n) 和 G 1 -a ＊( n) 、 在 E ～ J 列 、L ～ Q 列分别输入 柯克伦检验临界值 C( n -1 , a) 和 C( n -1 , a ) , 建立
收稿日期 : 2009 -10-07
式中 : G( n) 、G ( n′ ) 分别为上 、下侧统计量 ; x( n) 、x
· 834 ·
胡修伟等 : Excel 软件在标准样品定值统计运算中的应用
( 1) 分别为样本最大 、最小观测值 ; x 、s 分别为样本 平均值与样本标准差 。 确定检验水平 a 和剔除水平 a
∑
2 ( x k - x)
式中 : 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/ T 4883 -2008《数据的统计处理和解 释 正态样本离群值的判断和处理》 , 异常值检验可 采用奈尔检验 、 格拉布斯检验 、狄克逊检验 、 偏度-丰 度检验等方法 , 标准样品研制中一般进行格拉布斯 上侧检验和下侧检验 , 即分别计算 :
[ 1]
方与通常的方差估计量之比 。 建立在次序观测值基 础上 , 将 n 个独立观测值按非降次序 记为 x ( 1) ,x ( 2) …x ( n) , 如果一些观测值相等 , 按原数列中出现 的次数 , 将相等的观测值重复列出 , 然后计算统计量 W:
[ W =
k =1
。
∑α(x
k n k =1
∑(x
2 - x) n -1 i
( 4) ( 5)
S2 C = max S2
∑
式中 : S 2 为各实验室数据组内方差 ; x i 为各实验室
表 1 Excel 初始电子表格
Tab . 1 Excel electroni c initial chart
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 推荐值 标准偏差 数据组数 中位值 G0 . 01 G0 . 05 G min Gm ax W0 . 01( ) W0 . 05( ) W( ) A B C D E F G H I 总平均值 S N C0 . 01 C0 . 05 C n1 n2 b1 b2 W0 . 01( ) W0 . 05( ) W( ) 名称 实验室 B 1 测量值 1 2 3 4 C D 编号 E 1 极差 组内标准偏差 S2 i ni / S2 i F G H I J K L 项目 平均值 M C 方法 N
l
n+ 1k
2 - xk ) ]
( 1)
Excel 软件计算功能强大 、操作简单 , 是目前应 用最广泛 的 办 公软 件 之 一 。 本 工作 设 计 了 一 个 Excel电子表格 , 使用者只需输入各实验室测试原始 数据即可由 Excel 软件自动完成标准样品定值统计 运算中所涉及的全部数据 , 无需借助其他专业软件 。