第24卷第2期(总第140期) ・讲 座・
辐射防护通讯2004年4月
¹
操作和注意事项Excel 中一些常用统计分析工具的功能、
Funct ions, Operations and Precautions of
the St atistically Analytical T ools of Excel
胡逢全 肖运实(中国辐射防护研究院, 太原, 030006)
Hu Fengquan Xiao Yunshi (China Institute for Radiation Protection, 太原, 030006)
摘 要 在当今科研及其成果报告中, 成熟的统计分析软件被广泛使用。本文介绍Excel(中文版) 中一些常用统计分析工具的功能、操作和注意事项; 指出了在实例检验中发现此软件中存在的一些缺陷和问题, 强调进行全面的实例检验对正确使用此软件和深入学习数理统计的基本原理及重要概念均有重要价值。关键词: Excel 统计软件 统计描述 统计检验 方差分析 相关和回归
中图分类号:TP 317. 3 文献标识码:A 文章编号:1004-6356(2004) 02-0033-08
Abstr act T he mature statistically analytical softwar e is widely used in the scientific researches and the r esultant repor ts nowadays . T his paper is to describe the functions , operations and pr ecautions of the statistically analytical tools of Excel (Chinese edition ) in common use, indicating several drawbacks and problems discovered in case test. It is underscored that conducting compr ehensive case test will be of great help in corrective application of Excel's tools and ser ious study of the fundamental of mathematical statistics.
Key wor ds : Excel Statistical software Statistical description Statistical test
Var iance analysis Cor relation r egr ession
1 引言
在科研及其成果报告中, 数据的统计和分析是保证和提高质量的一个十分重要的步骤。随着计算机的普遍使用, 现成的如Sas 、Spss 和Excel 这些国际公认的成熟的统计分析软件的出现, 使统计分析的应用越来越便捷、准确和有效。由于
培训班Excel 有中文版, 加上已有很多应用手册、和电视讲座, 目前国内辐射防护界科研和工作人员, 已较普遍地能把Excel 作为一般的电子表格
程序应用, 将如辐射监测等数据用Excel 表格汇总给出; 但很少使用Excel 中十分有用的统计检验、方差分析及相关和回归等统计分析工具, Excel 的应用手册中也没有介绍这方面的内容。
¹
本文主要介绍Excel 中一些常用统计分析工具的功能、操作和注意事项。笔者在使用Excel 中的统计分析工具中, 逐一用过去做过的统计分析的例题作了检验, 发现其中有些描述不当或缺陷(或错
误) , 为此特别在表格中以“注意事项”给出, 并在建议中予以强调, 这些对防止应用错误是十分重要的。
2 Excel 常用统计分析工具介绍
表1～表5给出的依次是Excel 中有关统计描述、t 检验和F 检验、V 检验、方差分析、相关与回归分析等统计分析工具的功能、操作和注意事项。在Excel 中的这些统计分析工具, 分别在“插
2
收稿日期:2003-12-23
作者简介:胡逢全(1940-) , 男, 1965年毕业于北京大学技术物理系核物理专业, 研究员。
辐射防护通讯 2004年4月第24卷第2期
入”菜单下的“f x 函数→统计”和“工具”菜单下“数据分析”中(如果“工具”菜单下没有这个命令, 则需要安装“分析工具库”, 可以在“工具”菜单中, 单击“加载宏”命令, 选择“分析工具库”, 详情参见
粘贴函数或分析工具”Excel 帮助) 。在表中的“
中, 分别表示所使用的“粘贴函数”和“分析工具”。为了便于区分和阐述, 凡是在Excel 统计分析工具的输入和输出数据框中所用的量名或标志, 在本文及其各表中均打上“”号, 如“x ”、
“、“等。standard -dev ”array 1”
表1 Excel 中有关统计描述的功能、操作和注意事项
粘贴函数或
分析工具
功能及其输出结果以列表方式给出输入的一组或多组数据各自的以下统计参数:均值、单次测量标准差、样本容量、范围; 均值标准差、95%置信区间半宽度; 合计、中位数、方差; 峰度、偏斜度; 最大值、最小值、第几个最大和最小值。
输入参数
(1) 在“输入区域”, 输入包括标志单元在内的数据的区域(以单元格起止位置表示) ; (2) 在“分组方式”中选择按列还是按行排列, 并选中标志位于第1行; (3) 按输出内容要求, 选择“汇总统计”、置信区间的置信度及第几个最大值、最小值;
(4) 输出表的位置(输出区域的起始单元格) 。
输入数据的区域(不包括标志单元) 同上 同上同上
(1) 在“alp ha ”中输入显著性水平, 即A 值; (2) 在“stadar d-dev ”中输入单次测量标准差; (3) 在“Size ”中输入样本容量。
输入数据的区域同上
在“array1”和“array2”分别输入两组数据的区域(不包括标志单元) 输入数据的区域同上同上同上同上
(1) 在“array ”中输入数据的区域; (2) 在“K ”中输入p /100(如第50位百分位数, p =50, 则K =0. 5)
K =p /100, p 为第几位百分位数的序号
(1) 输入参数“s tandard-dev ”, 不是confid ence 框中所写的已知总体标准差, 而是样本标准偏差;
(2) 给出的不是自由度为(n -1) 的置信区间, 而是自由度为∞时的置信区间; (3) 给出的不是置信区间, 而是置信区间的半宽度。
减了3的峰度值a 4-3, a 4是第4阶原点矩
注意事项
(1) 输出参数的译名, 与通常或标准术语有的差别很大, 如:“置信度”、“标准误差”、“标准偏差”、“区域”和“计数”实际上分别是置信区间半宽度、均值标准差、单次测量标准差、最大与最小值之差和样本容量(或样品数) ; (2) “峰值”就是通常用于检验正态分布的“峰度”, 但是减去了3, 即是a 4-3; (3) 用一二个“粘贴函数”计算的参数值, 可检验输入有否错误。
工具→
数据分析→统计描述1)
average 2) stdev count var
给出一组数据的算术平均值给出一组数据的单次测量标准差, 在E xcel 中常又称“样本标准偏差”
给出一组数据的样本容量给出一组数据的方差给出一组数据均值的置信区间半宽度, 但它是按u 统计量计算的, 即按t =1. 96计算(与样品数无关)
给出一组数据的峰度给出一组数据的偏斜度给出两组数据的协方差给出一组数据的合计(总和) 给出一组数据的中位数给出一组数据的众数给出一组数据的最大值给出一组数据的最小值给出一组数据的第p 位百分位数P p
confiden ce
kurt skew covar sum median mode max min precentile
1) 由下拉菜单的操作途径为:工具→数据分析→分析工具→统计描述, 以下只给出分析工具的名称, 省略菜单操作路径; 2) 本文所有表中列出的“粘贴函数”全部是在“f x →统计”分类下的粘贴函数。
操作和注意事项 胡逢全Excel 中一些常用统计分析工具的功能、
表2 Excel 中有关t 检验和F 检验的功能、操作和注意事项
粘贴函数或
分析工具
功能及其输出结果已知比较的两组数据, 给出t 检验的概率“p ”, 即可能犯第一类错误的概率(否定原假设可能犯错误的概率) , p 的含义下同
已知比较数据的t 值和自由度, 给出成组比较t 检验的概率p
已知双侧检验的概率和自由度, 给出相应的t 分布的上侧分位数, 即通常所称的临界值, 相当于t A /2, df 表以列表形式给出包括以下内容的结果:各组样本数据均值、方差和样品数; 两组数据按成对合并后的方差、自由度和t 值; 成对t 检验双侧和单侧检验的概率p ; 选定A 下双侧和单侧的t 临界值
除给出方差相等的成组t 检验结果外, 输出包含的内容同上
除给出方差不等的成组t 检验结果外, 输出包含的内容同上
已知比较的两组样本数据, 给出双侧F 检验概率p
已知比较的两组数据的F 值及各组数据的自由度, 给出单侧F 检验的概率“p ”
已知单侧F 检验的概率A 及两组数据各自的自由度, 给出F 统计量单侧分位数, 即F A , df 1, df 2, 以列表形式给出包括以下
F 检验:
双样本方差”(比较)
内容的结果:各组样本数据
均值、方差和样品数; 两组数据合并的自由度和F 值; 单侧检验的概率p ; 选定A (0. 05) 下单侧的F 临界值
输入参数
(1) 在“ar ray1”和“array2”中分别输入两组
样本数据的区域, 分别以各自的单元区域表示, 如D4:D13和E4:E15; 也可用＄D ＄4:＄D ＄13和＄E ＄4:＄E ＄15; (2) 在“选框中选择单侧还是双侧检tails ”
验:输入1和2, 分别代表单侧和双侧; (3) 在“选框中选择成组还是成对检type ”验:输入1、2和3分别代表成对、方差相等的成组和方差不等的成组检验
(1) 在“X ”中输入由比较数据算得的t 值; (2) 在“中输入自由度df ; deg -freedom ”(3) 在“选框中选择单侧还是双侧检tails ”
验:输入1和2分别代表单侧和双侧(1) 在“probab ility ”中输入预定的双侧t 检验的(否定区) 概率A ; (2) 在“deg-freedom ”中输入自由度df
(1) 在“变量1”和“变量2”中分别输入两组比较数据的区域〔包括“标志”(即数据名称) 单元〕; (2) 选中“标志”选框; (3) 在“A ”选框中填入预定的显著性水平值A ;
(4) 输出区域(给出起始单元格) ; 也可另在新工作组表中给出, 余处类同同上 同上
在“array1”和“array2”中分别输入两组样本的数据区域
(1) 在“X ”中输入F 值, 如var1/var2(两组数据的方差之比) ; (2) 在“和“中deg -freedom 1”deg -freedom 2”分别输入两组数据的自由度d f 1和df 2
(1) 在“probability ”中输入单侧F 检验(否定区) 的概率A ; (2) 在“deg-freedom1”和“deg-freedom2”中分别输入两组数据的自由度d f 1和df 2(1) 在“变量1”和“变量2”中分别输入两组比较数据的区域(包括“标志”单元) , 并选中“标志”选框; (2) 在“A ”选框中填入预定的显著性水平值;
(3) 输出区域(给出起始单元格)
注意事项
只用于两组数据间的t 检验, 不能用于一组样本数据的参数与总体参数间的t 检验
只能给出成组等方差的t 检验结果; 也可用于一组样本数据的参数与总体参数的t 检验
输入的概率, 应是双侧的A (不能是A /2) ; 给出的是双侧的上侧分位数, 也是概率为A /2时的单侧的上侧分位数
(1) 主要功能与Ttest 相同, 但给出了中间阶段的更多信息;
(2) 若只要检验结果, 用T test 更简易, 结果便于直接并入数据表内; (3) 可用于检验Ttest 和均值、方差等参数的粘贴函数计算结果;
(4) 输出数据框中样品数误译为观测值同上 同上
给出的不是Ftest 数据框中指出的单侧检验的概率“p ”而是2p , 即双侧F 检验的概率
(1) Fdis t 数据框中没有说明给出的概率是单侧还是双侧的, 实际上给出的是单侧的“p ”;
(2) d f 1和df 2不能颠倒, 计算F 值时分子数据对应的为df 1;
(3) 常用双侧检验, 其概率应乘以2(1) 同上;
(2) 用于双侧, “probab ility ”要输入A /2
给出的是单侧检验的概率“p ”, 对双侧检验的概率, 要乘以2
Ttest
Tdis t
Tinv
t 检验:平均值的成对二样本分析
t 检验:双样本等方差假设t 检验:双样本异方差假设Ftest
Fd ist
Finv
辐射防护通讯 2004年4月第24卷第2期
表3 Excel 中有关V 2检验的功能、操作和注意事项
粘贴函数
功能及其输出结果已知一组数据在一
定分组条件下, 在各区段内的实际频数和(假定满足某种分布的) 理论频数, 给出判定是否满足该
2检验的概分布的V
率“p ”, 但其自由度
固定为(n -1) , n 为分组的区段数
2值和自由度, 已知V
2检验的给出单侧V
输入参数(1) 在“actual-range ”中输入实
际频数的数据区域, 如C 5:C 14; (2) 在“expected -中输入理range ”
论频数的数据区域, 如D5:D14
(1) 在“X ”中输入已知(或已算得
2值; 的) V
(2) 在“deg -freedom ”中输入相应的自由度df (1) 在“prob ability ”中输入预选的概率A ; (2) 在“deg-freedom ”中输入相应的自由度df
注意事项
(1) 各区段的实测频数和理论频数, 在chitest 框中分别为“actu al -rang e ”和“, 并分别译为“观察值的值域”和“理论值的值域”均exp ected -range ”是不合适的;
2
(2) 输出的是自由度为(n -1) 的单侧V 检验的概率p , 而实际中自由度不是(n -1) , 而是(n -k ) , k 是计算理论频数时所用的包括总频数在内的统计量数(如样本均值、样本标准差) , 所以在配合泊松分布和正
2态分布的V 检验时, 自由度分别为(n -2) 和(n -3) ;
(3) 可由Chitest 给出的概率p 和自由度(n -1) , 作为Chiinv 的输入参22
数, 得出相应的V 值; 再将这个V 值和实际的自由度(n -2) 或(n -3) 作为Chidest 的输入参数, 才能给出所需的对应实际自由度配合分布的2检验的概率; V
2分布随自由度变化十分明显, 尤其在自由度小的时候, 所以用(4) V
Chites t
Chitest 时, 纠正其结果与实际自由度的差别是十分重要和必需的(1) 给出的p 是单侧检验的概率; 在双侧检验时, 若A =0. 05, 接受和否定原假设的判据是:0. 025≤p ≤0. 975和p 0. 975;
2分布的收尾概率”(2) 在chidis t 框中, 给出的结果译为“返回V , 需注意上面的实际含义, 这类难理解的翻译很多
2分布的区在ch iinv 框中, 给出的结果译为“返回具有给定概率的收尾V
间点”, 注意在“功能和输出结果”中所述的实际含义
Chidis t
概率“p ”
已知单侧检验的概率A 和自由度df ,
Chiinv
2
给出相应的V 分布
2的分位数V A , df , 功
能同通常数理统计2
书中的V 分布表
表4 Excel 中有关方差分析的功能、操作和注意事项
分析工具
功能及其输出结果
以列表方式给出满足各组方差齐性条件下(无论各组重复测量数是否相同) 的单因素方差分析结果:在“表中给sum mary ”出各组的样本容量、总和、均值和方差; 在“方差分析”表中给出组间、组内和总计的平方和(SS ) 、自由度(d f ) 、均方(MS ) , 以及F 临界值、F 值及其相应的概率p
在“sammary ”表中分别给出各行和各列数据的样本容量、总和、均值和方差; 在“方差分析”表中给出行间、列间、误差和总计的以下各值:平方和(SS ) 、自由度(df ) 、均方(M S ) ; 以及分别给出行间和列间均方对误差均方的F 值及其相应的概率p 和F 临界值
基本同上, 但在“方差分析”表中增加给出反映有否交互作用的F 值及其相应的概率p 和F 临界值, 当然也定有交互项的SS 、d f 和MS
输入参数
(1) 在“输入区域”输入数据的区域, 包括标志行(或列) ; (2) 在“分组方式”中, 选择按列还是按行, 并选中“标志位于第1行”;
(3) 选择显著性水平A ; (4) 选择输出表的位置
(1) 在“输入区域”输入数据的区域, 包括标志行和列; (2) 选中“标志”;
(3) 选择显著性水平A ; (4) 选择输出表的位置
(1) 在“输入区域”输入数据的区域, 包括标志行和列; (2) 在“每一样本的行数”中输入重复测量(或样品) 数; (3) 选择显著性水平A ; (4) 选择输出表的位置
注意事项
(1) 没有指出适用条件是方差须齐性, 是否齐性, 多数情况下可以由比较各组方差大小大致判定;
(2) 即使p
仅用于没有重复测量的双因素方差分析
仅用于重复测量数相同条件下的双因素方差分析, 表的设计有专门要求, 即重复样品的数据要分别排在同列的不同行内, 如重复数=4, 则每种“处理”的4个数据, 要排在同列的4行中
方差分析:单因素方差分析
方差分析:无重复双因素方差分析
方差分析:可重复双因素方差分析
操作和注意事项 胡逢全Excel 中一些常用统计分析工具的功能、
表5 Excel 中有关回归和相关分析的功能、操作和注意事项
粘贴函数或分析工具
功能及其输出结果在“sum mary ”表中给出
(线性回归的) 相关系数
回归的误差S E ; R 、R 2、给出截距、斜率及其误差
和置信区间; 还可选择给出残差表、残差图和拟合曲线图, 在拟合图上还可进一步给出拟合方程(线性、对数、指数、多项式) 的拟合曲线及其方程和R 2
correl (array 1, array 2)
给出线性回归的相关系数R
RSQ (array 1, ar ray 2) covar (array 1, ar ray 2) intercept
(know-y's , know-x' s) slope
(know -y's , know -x' s ) steyx
(know -y's , know -x' s ) forecast
(x, know-y' s, know-x' s)
给出线性回归的相关指数R 2
给出两组数据的协方差给出线性回归的截距
输入参数
(1) 在“Y 值输入区域”和“X 值输入区域”分别输入因变量和自变量的区域(不包括标志) ;
(2) 选中“标志”, 选择是否要求通过原点和置信水平; (3) 在“残差”选框中分别选择是否要求输出残差、标准残差、残差图和拟合曲线图; (4) 输出图表的位置
在“和“中分别array 1”array 2”
输入相关分析的两组数据的区域(不包括标志) 同上同上
在“k now-y' s ”和“know-x' s ”
k now-y' s 必须是因变量, know-x' s 是自变
中分别输入因变量和自变量
量, 不能颠倒!
的区域(不包括标志) 同上
同上
注意事项
(1) 数据必须按列排列, 不能按行排列!
(2) Y 必须是因变量, 而X 是自变量, 不能颠倒! 否则截距、斜率值及其误差和置信区间等均将不同;
(3) 在“summ ary ou tpu t ”的最后1个子表中, 第1行以in tercept 为栏名的是截距行, 第2行以自变量标志为栏名的是斜率行, 依此可以判定输入的Y 和X 是否颠倒;
(4) 输出的标准残差=残差/(残差的误差S 0) , 这里的S 0相当于n 个残差的标准差, 不是真正的残差的误差(sum mar y output 中的) S E (自由度为(n -2) ) , S E /S 0=[(n -1) /(n -2) ]0. 5
回归
给出线性回归的斜率
给出线性回归的误差S E 给出线性回归的一个预测值
同上
同上。此外, 在“x ”中输入需要预测的数据点的自变量数值
同上
同上
3 举例
为了更直观地理解这些统计分析工具的功能和操作, 以下给出2个使用Excel 进行统计分析的例子。
3. 1例1
此例4条河流中铀浓度数据取自文献[1]方差分析一章中的表7-3。用Excel 中的“粘贴函数”和“工具→数据分析→分析工具”, 给出了各组数据的统计描述、各组数据均值间的t 检验和全部数据的方差分析结果。原始数据见表6; 统计描述和t 检验结果示于表7; 方差分析结果示于表8。这些表(除原始数据) 都是Excel 的原输出表结果(对保留的小数点位数经过处理) , 所有量名都是Excel 中的原名。
从表7和表8可见:
(1) 数据的统计描述由“粘贴函数”和”分析
工具”给出的结果, 除置信区间半宽度d 外, 都是一致的。
(2) 方差分析的结果全部与文献[1]中的相
同; F 值的概率p =0. 0026
(3) 如果采用Excel 数据表, 那么如平均值、标准差、n 、范围、置信区间等描述数据的必要的统计量的数值, 均可利用粘贴函数方便地完成, 并与原始数据合并在同一表中; 多组数据的结果是由单组(或两组比较) 数据, 经单元地址按相对引用“拖拉”而得到的(见第4节) 。
辐射防护通讯 2004年4月第24卷第2期
表6 4条河流中铀浓度(L g /L )
序号12345678
河流中铀浓度(L g/L)
A 0. 540. 700. 680. 710. 520. 750. 780. 61
B 0. 750. 800. 720. 710. 560. 680. 660. 61
C 0. 630. 610. 590. 560. 420. 400. 530. 55
D 0. 850. 870. 720. 780. 630. 900. 540. 63
表7 4条河流的铀浓度数据的统计描述和t 检验结果
粘贴函数:插入→f x 函数→统计
粘贴函数 Average stdev coun t min max
量 名-平均值x 标准差S x 样品容量n 最小值x min 最大值x max 范围x min ～x max
confidence
置信区间半宽度d 1置信区间半宽度d 2
取自“统计描述”
-+d 295%上置信限L =x --d 295%下置信限U =x 95%置信区间L ～U 成对比较双侧t 检验
j -(j +1)
T test
j -(j +2) j -(j +3) 注释
0. 507010250. 0308249630. 20116679
B 与C , C 与D 差异显著; A 与C 差异显著分析工具:工具→数据分析→统计描述
分析工具平均标准误差中值标准偏差样本方差峰值偏斜度
区域最小值最大值求和计数最大(1) 最小(1) 置信度(95. 0%)
A
0. 661 0. 0340. 690. 0950. 00907-1. 190-0. 4810. 260. 520. 785. 2980. 780. 520. 0796
B
0. 686 0. 0270. 6950. 0770. 00588-0. 207-0. 2920. 240. 560. 85. 4980. 80. 560. 0641
C
0. 536 0. 0300. 5550. 0850. 00714-0. 578-0. 8540. 230. 40. 634. 2980. 630. 40. 0706
D
0. 740 0. 0460. 750. 1310. 01726-1. 466-0. 2510. 360. 540. 95. 9280. 90. 540. 1098
0. 0002880. 158060298
A 0. 6610. 09580. 520. 780. 52～0. 780. 0660. 07960. 7410. 5820. 582～0. 741
B 0. 6860. 07780. 560. 80. 56～0. 80. 0530. 06410. 7500. 6220. 622～0. 750
p
0. 005606 C 0. 5360. 08580. 400. 630. 40～0. 630. 0590. 07060. 6070. 4660. 466～0. 607
D 0. 7400. 13180. 540. 90. 54～0. 90. 0910. 110. 8500. 6300. 630～0. 850
操作和注意事项 胡逢全Excel 中一些常用统计分析工具的功能、
表8 4条河流中铀浓度数据的方差分析输出结果
分析工具:工具→数据分析→方差分析→单因素方差分析
SUM MARY
组A
B C 计数888求和5. 295. 494. 29方差分析
差异源
SS
df 3
MS 0. 009838
Multiple R R Square
Adjus ted R Square 标准误差 观测值
Intercept
Coefficients 标准误差 t Stat P-valu e Lower 95% Upper 95%
观测值12345
x (m) [**************]00
6789101112
67. 3838. 8157. 6441. 75E-0547. 74187. 024
RESIDU AL OU TPUT
预测Y 64. 8562. 3159.
7757. 2454. 7049. 6344. 5539. 4834. 4129. 3316. 65-8. 72
残差41. 1517. 696. 23-15. 54-13. 00-11. 43-11. 45-14. 98-15. 21-10. 938. 2519. 22
F
P-value
F crit
平均0. 661250. 686250. 53625方差0. 009070. 0058840. 007141表10 某厂底泥表面C 照射量率y 与离废水排放口的距离x 的相关分析
粘贴函数:插入→f x 函数→统计(线性回归参数) 粘贴函数 correl intercept slope RSQ 量 名 相关系数R 截距a 斜率m 相关指数R 回归误差R 2
数 值-0. 76167. 383-0. 2540. 58分析工具:工具→数据分析→回归
SU MMARY OUT PUT
回归统计
0. 7610. 580. 53819. 31312X Variable 1-0. 2540. 068-3. 7150. 004-0. 406-0. 102
组间0. 1787090. 05957 6. 0551040. 00262. 946685
组内0. 27546328总计0. 45417231
3. 2例2
此例数据(见表9) 取自文献[2]7. 2节的表12; 用“粘贴函数”和“分析工具”所得结果示于表10; 拟合曲线图示于图1。表10中, “分析工具:工具→数据分析→回归”的量名和符号, 均是Excel 中的原名(或符号) , 要注意其中第3个子表中Intercept 列和X Variable 1列分别是截距(a ) 列和斜率(b ) 列, 表中第2行“标准误差”给出的就是a 和b 的误差, 余此类推(参见表5) ; 回归统计中的“标准误差”是回归的误差R E 。
表9 某厂离废水排放口不同距离x 处的
底泥表面C 照射量率y
y (L R/h ) 106806641. 741. 738. 2
x (m) [1**********]0
y (L R/h) 33. 124. 519. 218. 424. 910. 5
图1 底泥表面C 照射量率y 与离废水排放口的距离x 的拟合曲线图
辐射防护通讯 2004年4月第24卷第2期
从表10和图1可见, 由“粘贴函数”与“分析工具”给出的同一量的结果完全相同, 表与图给出的结果也相同, 且其与文献[2]计算的结果也一致。图1是在原始图上, 执行以下步骤得到的:(1) 选中图上任一数据点, 点击鼠标右键, 选“添加趋势线”。
(2) 在“添加趋势线”的“类型”选框中选择拟合曲线的函数类型:线性、对数、指数、幂函数和多项式; 再在“选项”框选中“显示公式”和“显示R 平方值”。
计分析计算的例子或有关数理统计教材中的举例, 也可自己编些计算简单的例子, 以便于核对。另外, 数理统计应用的学习, 特别是掌握常用统计分析方法的基本原理和重要概念, 非常需要多做
实例计算[3]; 而Excel 中的统计分析功能能很迅速地计算复杂例题, 而且计算准确, 所以它还可用作数理统计的学习工具。因此, 在使用前对Excel 中的这些常用工具进行全面的实例检验, 具有使用和深入学习的双重价值。
4. 2利用“粘贴函数”和“分析工具”互检
无论数据统计描述、统计检验或相关和回归分析, Excel 可从“粘贴函数”和“分析工具”两个途径得到结果。一般来说, “粘贴函数”给出的结果比较单一, 但便于直接汇总于包括原始数据的数据表中; “分析工具”输出的参数更多, 但有的不是汇总数据表中所需要的。两者的输入方式有的也有些不同, 如“粘贴函数”输入数据区域不包括标志, 而“分析工具”一般包括标志; “粘贴函数”对单元区域使用“相对引用”如D7:D14, 而“分析工具”多用“绝对引用”如＄D ＄7:＄D ＄14(最好用鼠标选中, Excel 将会自动给出合理的引用格式) 等。除了仔细核对输入数据外, 采用“粘贴函数”和“分析工具”两种输出, 是很省力的互检手段; 然后再根据需要, 将结果汇总于数据表。4. 3使用“粘贴函数”时宜多用“拖拉”操作
在多组数据进行相同操作时, 利用Excel 的“拖拉”操作, 可以大大减少工作量和差错。如在t 检验时, 利用Ttest 算相邻两列(或两行) 的t 检验结果p , 采用在该给出结果的单元格用拖拉方式, 可以立即给出其他相邻列(或行) 间同类检验的p 值; 为了用拖拉方式, 输出表的格式要稍作加工(见表7) 。至于要给出各自的统计参数, 只需在给出一组数据的统计结果的单元格用拖拉方式, 即可给出其余各组的相应结果。
4 使用中的几点建议
4. 1使用前需进行实例检验
从上述表1～表5的注意事项看, Excel(中文版) 中的统计工具还存在以下一些缺陷和问题:
(1) 一些输入参数和输出量的术语, 有不少是非标准的或非专业人员通用的, 而且不经过实例检验是容易被误解的。如“统计描述”中的“置信度”和“峰值”实际应分别是置信区间半宽度和减3后的峰度a 4-3; “回归”输出的两个表中的“标准误差”, 是十分有用的, 其分别是回归误差R E 和截距a 、斜率b 的误差S a 、S b , 但不经实例检验很难确认(见表5) 。
(2) 有些输出数据的含义是错误的或不明确的。如“Ftest ”输出的不是“单侧检验的概率”而是双侧检验的概率; “输出的不是通常的confidence ”自由度为(n -2
1) 的置信区间半宽度, 而是自由
度为∞时的置信区间半宽度; “Chitest ”输出的是单侧V 检验的概率, 但没有说明其对应的自由度df =n -1, 这又恰是配合分布检验中不大能用的(常用的配合泊松分布检验df =n -2或正态分布检验d f =n -3, 见表3) 。
(3) 有些对输入数据的排列有专门要求, 但又没有给出必要的指示。如“回归”要求两组数据按列排列; “可重复双因素方差分析”中要求重复样品数据排在同列的不同行内等。
这类缺陷和问题, 对初次使用者准确地理解输入和输出数据的确切含义及其如何输入造成很大困难, 甚至妨碍了它的推广应用。但是, 以笔者的体会, 只要在使用前经过实例检验, 这些困难就很容易解决, 就能便捷、准确、有效地使用这类统计软件。检验所用实例最好是用自己过去作过统
5 参考文献
1 高玉堂, 主编. 环境监测常用统计方法. 北京:原子能出版社,
1980.
2 胡逢全. 数据处理中常用统计方法的基本原理及重要概念
(II). 辐射防护通讯, 1996, 16(3):34.
3 胡逢全. 数据处理中常用统计方法的基本原理及重要概念
(I ). 辐射防护通讯, 1996, 16(1) :27.
(责任编辑:赵 宁)
第24卷第2期(总第140期) ・讲 座・
辐射防护通讯2004年4月
¹
操作和注意事项Excel 中一些常用统计分析工具的功能、
Funct ions, Operations and Precautions of
the St atistically Analytical T ools of Excel
胡逢全 肖运实(中国辐射防护研究院, 太原, 030006)
Hu Fengquan Xiao Yunshi (China Institute for Radiation Protection, 太原, 030006)
摘 要 在当今科研及其成果报告中, 成熟的统计分析软件被广泛使用。本文介绍Excel(中文版) 中一些常用统计分析工具的功能、操作和注意事项; 指出了在实例检验中发现此软件中存在的一些缺陷和问题, 强调进行全面的实例检验对正确使用此软件和深入学习数理统计的基本原理及重要概念均有重要价值。关键词: Excel 统计软件 统计描述 统计检验 方差分析 相关和回归
中图分类号:TP 317. 3 文献标识码:A 文章编号:1004-6356(2004) 02-0033-08
Abstr act T he mature statistically analytical softwar e is widely used in the scientific researches and the r esultant repor ts nowadays . T his paper is to describe the functions , operations and pr ecautions of the statistically analytical tools of Excel (Chinese edition ) in common use, indicating several drawbacks and problems discovered in case test. It is underscored that conducting compr ehensive case test will be of great help in corrective application of Excel's tools and ser ious study of the fundamental of mathematical statistics.
Key wor ds : Excel Statistical software Statistical description Statistical test
Var iance analysis Cor relation r egr ession
1 引言
在科研及其成果报告中, 数据的统计和分析是保证和提高质量的一个十分重要的步骤。随着计算机的普遍使用, 现成的如Sas 、Spss 和Excel 这些国际公认的成熟的统计分析软件的出现, 使统计分析的应用越来越便捷、准确和有效。由于
培训班Excel 有中文版, 加上已有很多应用手册、和电视讲座, 目前国内辐射防护界科研和工作人员, 已较普遍地能把Excel 作为一般的电子表格
程序应用, 将如辐射监测等数据用Excel 表格汇总给出; 但很少使用Excel 中十分有用的统计检验、方差分析及相关和回归等统计分析工具, Excel 的应用手册中也没有介绍这方面的内容。
¹
本文主要介绍Excel 中一些常用统计分析工具的功能、操作和注意事项。笔者在使用Excel 中的统计分析工具中, 逐一用过去做过的统计分析的例题作了检验, 发现其中有些描述不当或缺陷(或错
误) , 为此特别在表格中以“注意事项”给出, 并在建议中予以强调, 这些对防止应用错误是十分重要的。
2 Excel 常用统计分析工具介绍
表1～表5给出的依次是Excel 中有关统计描述、t 检验和F 检验、V 检验、方差分析、相关与回归分析等统计分析工具的功能、操作和注意事项。在Excel 中的这些统计分析工具, 分别在“插
2
收稿日期:2003-12-23
作者简介:胡逢全(1940-) , 男, 1965年毕业于北京大学技术物理系核物理专业, 研究员。
辐射防护通讯 2004年4月第24卷第2期
入”菜单下的“f x 函数→统计”和“工具”菜单下“数据分析”中(如果“工具”菜单下没有这个命令, 则需要安装“分析工具库”, 可以在“工具”菜单中, 单击“加载宏”命令, 选择“分析工具库”, 详情参见
粘贴函数或分析工具”Excel 帮助) 。在表中的“
中, 分别表示所使用的“粘贴函数”和“分析工具”。为了便于区分和阐述, 凡是在Excel 统计分析工具的输入和输出数据框中所用的量名或标志, 在本文及其各表中均打上“”号, 如“x ”、
“、“等。standard -dev ”array 1”
表1 Excel 中有关统计描述的功能、操作和注意事项
粘贴函数或
分析工具
功能及其输出结果以列表方式给出输入的一组或多组数据各自的以下统计参数:均值、单次测量标准差、样本容量、范围; 均值标准差、95%置信区间半宽度; 合计、中位数、方差; 峰度、偏斜度; 最大值、最小值、第几个最大和最小值。
输入参数
(1) 在“输入区域”, 输入包括标志单元在内的数据的区域(以单元格起止位置表示) ; (2) 在“分组方式”中选择按列还是按行排列, 并选中标志位于第1行; (3) 按输出内容要求, 选择“汇总统计”、置信区间的置信度及第几个最大值、最小值;
(4) 输出表的位置(输出区域的起始单元格) 。
输入数据的区域(不包括标志单元) 同上 同上同上
(1) 在“alp ha ”中输入显著性水平, 即A 值; (2) 在“stadar d-dev ”中输入单次测量标准差; (3) 在“Size ”中输入样本容量。
输入数据的区域同上
在“array1”和“array2”分别输入两组数据的区域(不包括标志单元) 输入数据的区域同上同上同上同上
(1) 在“array ”中输入数据的区域; (2) 在“K ”中输入p /100(如第50位百分位数, p =50, 则K =0. 5)
K =p /100, p 为第几位百分位数的序号
(1) 输入参数“s tandard-dev ”, 不是confid ence 框中所写的已知总体标准差, 而是样本标准偏差;
(2) 给出的不是自由度为(n -1) 的置信区间, 而是自由度为∞时的置信区间; (3) 给出的不是置信区间, 而是置信区间的半宽度。
减了3的峰度值a 4-3, a 4是第4阶原点矩
注意事项
(1) 输出参数的译名, 与通常或标准术语有的差别很大, 如:“置信度”、“标准误差”、“标准偏差”、“区域”和“计数”实际上分别是置信区间半宽度、均值标准差、单次测量标准差、最大与最小值之差和样本容量(或样品数) ; (2) “峰值”就是通常用于检验正态分布的“峰度”, 但是减去了3, 即是a 4-3; (3) 用一二个“粘贴函数”计算的参数值, 可检验输入有否错误。
工具→
数据分析→统计描述1)
average 2) stdev count var
给出一组数据的算术平均值给出一组数据的单次测量标准差, 在E xcel 中常又称“样本标准偏差”
给出一组数据的样本容量给出一组数据的方差给出一组数据均值的置信区间半宽度, 但它是按u 统计量计算的, 即按t =1. 96计算(与样品数无关)
给出一组数据的峰度给出一组数据的偏斜度给出两组数据的协方差给出一组数据的合计(总和) 给出一组数据的中位数给出一组数据的众数给出一组数据的最大值给出一组数据的最小值给出一组数据的第p 位百分位数P p
confiden ce
kurt skew covar sum median mode max min precentile
1) 由下拉菜单的操作途径为:工具→数据分析→分析工具→统计描述, 以下只给出分析工具的名称, 省略菜单操作路径; 2) 本文所有表中列出的“粘贴函数”全部是在“f x →统计”分类下的粘贴函数。
操作和注意事项 胡逢全Excel 中一些常用统计分析工具的功能、
表2 Excel 中有关t 检验和F 检验的功能、操作和注意事项
粘贴函数或
分析工具
功能及其输出结果已知比较的两组数据, 给出t 检验的概率“p ”, 即可能犯第一类错误的概率(否定原假设可能犯错误的概率) , p 的含义下同
已知比较数据的t 值和自由度, 给出成组比较t 检验的概率p
已知双侧检验的概率和自由度, 给出相应的t 分布的上侧分位数, 即通常所称的临界值, 相当于t A /2, df 表以列表形式给出包括以下内容的结果:各组样本数据均值、方差和样品数; 两组数据按成对合并后的方差、自由度和t 值; 成对t 检验双侧和单侧检验的概率p ; 选定A 下双侧和单侧的t 临界值
除给出方差相等的成组t 检验结果外, 输出包含的内容同上
除给出方差不等的成组t 检验结果外, 输出包含的内容同上
已知比较的两组样本数据, 给出双侧F 检验概率p
已知比较的两组数据的F 值及各组数据的自由度, 给出单侧F 检验的概率“p ”
已知单侧F 检验的概率A 及两组数据各自的自由度, 给出F 统计量单侧分位数, 即F A , df 1, df 2, 以列表形式给出包括以下
F 检验:
双样本方差”(比较)
内容的结果:各组样本数据
均值、方差和样品数; 两组数据合并的自由度和F 值; 单侧检验的概率p ; 选定A (0. 05) 下单侧的F 临界值
输入参数
(1) 在“ar ray1”和“array2”中分别输入两组
样本数据的区域, 分别以各自的单元区域表示, 如D4:D13和E4:E15; 也可用＄D ＄4:＄D ＄13和＄E ＄4:＄E ＄15; (2) 在“选框中选择单侧还是双侧检tails ”
验:输入1和2, 分别代表单侧和双侧; (3) 在“选框中选择成组还是成对检type ”验:输入1、2和3分别代表成对、方差相等的成组和方差不等的成组检验
(1) 在“X ”中输入由比较数据算得的t 值; (2) 在“中输入自由度df ; deg -freedom ”(3) 在“选框中选择单侧还是双侧检tails ”
验:输入1和2分别代表单侧和双侧(1) 在“probab ility ”中输入预定的双侧t 检验的(否定区) 概率A ; (2) 在“deg-freedom ”中输入自由度df
(1) 在“变量1”和“变量2”中分别输入两组比较数据的区域〔包括“标志”(即数据名称) 单元〕; (2) 选中“标志”选框; (3) 在“A ”选框中填入预定的显著性水平值A ;
(4) 输出区域(给出起始单元格) ; 也可另在新工作组表中给出, 余处类同同上 同上
在“array1”和“array2”中分别输入两组样本的数据区域
(1) 在“X ”中输入F 值, 如var1/var2(两组数据的方差之比) ; (2) 在“和“中deg -freedom 1”deg -freedom 2”分别输入两组数据的自由度d f 1和df 2
(1) 在“probability ”中输入单侧F 检验(否定区) 的概率A ; (2) 在“deg-freedom1”和“deg-freedom2”中分别输入两组数据的自由度d f 1和df 2(1) 在“变量1”和“变量2”中分别输入两组比较数据的区域(包括“标志”单元) , 并选中“标志”选框; (2) 在“A ”选框中填入预定的显著性水平值;
(3) 输出区域(给出起始单元格)
注意事项
只用于两组数据间的t 检验, 不能用于一组样本数据的参数与总体参数间的t 检验
只能给出成组等方差的t 检验结果; 也可用于一组样本数据的参数与总体参数的t 检验
输入的概率, 应是双侧的A (不能是A /2) ; 给出的是双侧的上侧分位数, 也是概率为A /2时的单侧的上侧分位数
(1) 主要功能与Ttest 相同, 但给出了中间阶段的更多信息;
(2) 若只要检验结果, 用T test 更简易, 结果便于直接并入数据表内; (3) 可用于检验Ttest 和均值、方差等参数的粘贴函数计算结果;
(4) 输出数据框中样品数误译为观测值同上 同上
给出的不是Ftest 数据框中指出的单侧检验的概率“p ”而是2p , 即双侧F 检验的概率
(1) Fdis t 数据框中没有说明给出的概率是单侧还是双侧的, 实际上给出的是单侧的“p ”;
(2) d f 1和df 2不能颠倒, 计算F 值时分子数据对应的为df 1;
(3) 常用双侧检验, 其概率应乘以2(1) 同上;
(2) 用于双侧, “probab ility ”要输入A /2
给出的是单侧检验的概率“p ”, 对双侧检验的概率, 要乘以2
Ttest
Tdis t
Tinv
t 检验:平均值的成对二样本分析
t 检验:双样本等方差假设t 检验:双样本异方差假设Ftest
Fd ist
Finv
辐射防护通讯 2004年4月第24卷第2期
表3 Excel 中有关V 2检验的功能、操作和注意事项
粘贴函数
功能及其输出结果已知一组数据在一
定分组条件下, 在各区段内的实际频数和(假定满足某种分布的) 理论频数, 给出判定是否满足该
2检验的概分布的V
率“p ”, 但其自由度
固定为(n -1) , n 为分组的区段数
2值和自由度, 已知V
2检验的给出单侧V
输入参数(1) 在“actual-range ”中输入实
际频数的数据区域, 如C 5:C 14; (2) 在“expected -中输入理range ”
论频数的数据区域, 如D5:D14
(1) 在“X ”中输入已知(或已算得
2值; 的) V
(2) 在“deg -freedom ”中输入相应的自由度df (1) 在“prob ability ”中输入预选的概率A ; (2) 在“deg-freedom ”中输入相应的自由度df
注意事项
(1) 各区段的实测频数和理论频数, 在chitest 框中分别为“actu al -rang e ”和“, 并分别译为“观察值的值域”和“理论值的值域”均exp ected -range ”是不合适的;
2
(2) 输出的是自由度为(n -1) 的单侧V 检验的概率p , 而实际中自由度不是(n -1) , 而是(n -k ) , k 是计算理论频数时所用的包括总频数在内的统计量数(如样本均值、样本标准差) , 所以在配合泊松分布和正
2态分布的V 检验时, 自由度分别为(n -2) 和(n -3) ;
(3) 可由Chitest 给出的概率p 和自由度(n -1) , 作为Chiinv 的输入参22
数, 得出相应的V 值; 再将这个V 值和实际的自由度(n -2) 或(n -3) 作为Chidest 的输入参数, 才能给出所需的对应实际自由度配合分布的2检验的概率; V
2分布随自由度变化十分明显, 尤其在自由度小的时候, 所以用(4) V
Chites t
Chitest 时, 纠正其结果与实际自由度的差别是十分重要和必需的(1) 给出的p 是单侧检验的概率; 在双侧检验时, 若A =0. 05, 接受和否定原假设的判据是:0. 025≤p ≤0. 975和p 0. 975;
2分布的收尾概率”(2) 在chidis t 框中, 给出的结果译为“返回V , 需注意上面的实际含义, 这类难理解的翻译很多
2分布的区在ch iinv 框中, 给出的结果译为“返回具有给定概率的收尾V
间点”, 注意在“功能和输出结果”中所述的实际含义
Chidis t
概率“p ”
已知单侧检验的概率A 和自由度df ,
Chiinv
2
给出相应的V 分布
2的分位数V A , df , 功
能同通常数理统计2
书中的V 分布表
表4 Excel 中有关方差分析的功能、操作和注意事项
分析工具
功能及其输出结果
以列表方式给出满足各组方差齐性条件下(无论各组重复测量数是否相同) 的单因素方差分析结果:在“表中给sum mary ”出各组的样本容量、总和、均值和方差; 在“方差分析”表中给出组间、组内和总计的平方和(SS ) 、自由度(d f ) 、均方(MS ) , 以及F 临界值、F 值及其相应的概率p
在“sammary ”表中分别给出各行和各列数据的样本容量、总和、均值和方差; 在“方差分析”表中给出行间、列间、误差和总计的以下各值:平方和(SS ) 、自由度(df ) 、均方(M S ) ; 以及分别给出行间和列间均方对误差均方的F 值及其相应的概率p 和F 临界值
基本同上, 但在“方差分析”表中增加给出反映有否交互作用的F 值及其相应的概率p 和F 临界值, 当然也定有交互项的SS 、d f 和MS
输入参数
(1) 在“输入区域”输入数据的区域, 包括标志行(或列) ; (2) 在“分组方式”中, 选择按列还是按行, 并选中“标志位于第1行”;
(3) 选择显著性水平A ; (4) 选择输出表的位置
(1) 在“输入区域”输入数据的区域, 包括标志行和列; (2) 选中“标志”;
(3) 选择显著性水平A ; (4) 选择输出表的位置
(1) 在“输入区域”输入数据的区域, 包括标志行和列; (2) 在“每一样本的行数”中输入重复测量(或样品) 数; (3) 选择显著性水平A ; (4) 选择输出表的位置
注意事项
(1) 没有指出适用条件是方差须齐性, 是否齐性, 多数情况下可以由比较各组方差大小大致判定;
(2) 即使p
仅用于没有重复测量的双因素方差分析
仅用于重复测量数相同条件下的双因素方差分析, 表的设计有专门要求, 即重复样品的数据要分别排在同列的不同行内, 如重复数=4, 则每种“处理”的4个数据, 要排在同列的4行中
方差分析:单因素方差分析
方差分析:无重复双因素方差分析
方差分析:可重复双因素方差分析
操作和注意事项 胡逢全Excel 中一些常用统计分析工具的功能、
表5 Excel 中有关回归和相关分析的功能、操作和注意事项
粘贴函数或分析工具
功能及其输出结果在“sum mary ”表中给出
(线性回归的) 相关系数
回归的误差S E ; R 、R 2、给出截距、斜率及其误差
和置信区间; 还可选择给出残差表、残差图和拟合曲线图, 在拟合图上还可进一步给出拟合方程(线性、对数、指数、多项式) 的拟合曲线及其方程和R 2
correl (array 1, array 2)
给出线性回归的相关系数R
RSQ (array 1, ar ray 2) covar (array 1, ar ray 2) intercept
(know-y's , know-x' s) slope
(know -y's , know -x' s ) steyx
(know -y's , know -x' s ) forecast
(x, know-y' s, know-x' s)
给出线性回归的相关指数R 2
给出两组数据的协方差给出线性回归的截距
输入参数
(1) 在“Y 值输入区域”和“X 值输入区域”分别输入因变量和自变量的区域(不包括标志) ;
(2) 选中“标志”, 选择是否要求通过原点和置信水平; (3) 在“残差”选框中分别选择是否要求输出残差、标准残差、残差图和拟合曲线图; (4) 输出图表的位置
在“和“中分别array 1”array 2”
输入相关分析的两组数据的区域(不包括标志) 同上同上
在“k now-y' s ”和“know-x' s ”
k now-y' s 必须是因变量, know-x' s 是自变
中分别输入因变量和自变量
量, 不能颠倒!
的区域(不包括标志) 同上
同上
注意事项
(1) 数据必须按列排列, 不能按行排列!
(2) Y 必须是因变量, 而X 是自变量, 不能颠倒! 否则截距、斜率值及其误差和置信区间等均将不同;
(3) 在“summ ary ou tpu t ”的最后1个子表中, 第1行以in tercept 为栏名的是截距行, 第2行以自变量标志为栏名的是斜率行, 依此可以判定输入的Y 和X 是否颠倒;
(4) 输出的标准残差=残差/(残差的误差S 0) , 这里的S 0相当于n 个残差的标准差, 不是真正的残差的误差(sum mar y output 中的) S E (自由度为(n -2) ) , S E /S 0=[(n -1) /(n -2) ]0. 5
回归
给出线性回归的斜率
给出线性回归的误差S E 给出线性回归的一个预测值
同上
同上。此外, 在“x ”中输入需要预测的数据点的自变量数值
同上
同上
3 举例
为了更直观地理解这些统计分析工具的功能和操作, 以下给出2个使用Excel 进行统计分析的例子。
3. 1例1
此例4条河流中铀浓度数据取自文献[1]方差分析一章中的表7-3。用Excel 中的“粘贴函数”和“工具→数据分析→分析工具”, 给出了各组数据的统计描述、各组数据均值间的t 检验和全部数据的方差分析结果。原始数据见表6; 统计描述和t 检验结果示于表7; 方差分析结果示于表8。这些表(除原始数据) 都是Excel 的原输出表结果(对保留的小数点位数经过处理) , 所有量名都是Excel 中的原名。
从表7和表8可见:
(1) 数据的统计描述由“粘贴函数”和”分析
工具”给出的结果, 除置信区间半宽度d 外, 都是一致的。
(2) 方差分析的结果全部与文献[1]中的相
同; F 值的概率p =0. 0026
(3) 如果采用Excel 数据表, 那么如平均值、标准差、n 、范围、置信区间等描述数据的必要的统计量的数值, 均可利用粘贴函数方便地完成, 并与原始数据合并在同一表中; 多组数据的结果是由单组(或两组比较) 数据, 经单元地址按相对引用“拖拉”而得到的(见第4节) 。
辐射防护通讯 2004年4月第24卷第2期
表6 4条河流中铀浓度(L g /L )
序号12345678
河流中铀浓度(L g/L)
A 0. 540. 700. 680. 710. 520. 750. 780. 61
B 0. 750. 800. 720. 710. 560. 680. 660. 61
C 0. 630. 610. 590. 560. 420. 400. 530. 55
D 0. 850. 870. 720. 780. 630. 900. 540. 63
表7 4条河流的铀浓度数据的统计描述和t 检验结果
粘贴函数:插入→f x 函数→统计
粘贴函数 Average stdev coun t min max
量 名-平均值x 标准差S x 样品容量n 最小值x min 最大值x max 范围x min ～x max
confidence
置信区间半宽度d 1置信区间半宽度d 2
取自“统计描述”
-+d 295%上置信限L =x --d 295%下置信限U =x 95%置信区间L ～U 成对比较双侧t 检验
j -(j +1)
T test
j -(j +2) j -(j +3) 注释
0. 507010250. 0308249630. 20116679
B 与C , C 与D 差异显著; A 与C 差异显著分析工具:工具→数据分析→统计描述
分析工具平均标准误差中值标准偏差样本方差峰值偏斜度
区域最小值最大值求和计数最大(1) 最小(1) 置信度(95. 0%)
A
0. 661 0. 0340. 690. 0950. 00907-1. 190-0. 4810. 260. 520. 785. 2980. 780. 520. 0796
B
0. 686 0. 0270. 6950. 0770. 00588-0. 207-0. 2920. 240. 560. 85. 4980. 80. 560. 0641
C
0. 536 0. 0300. 5550. 0850. 00714-0. 578-0. 8540. 230. 40. 634. 2980. 630. 40. 0706
D
0. 740 0. 0460. 750. 1310. 01726-1. 466-0. 2510. 360. 540. 95. 9280. 90. 540. 1098
0. 0002880. 158060298
A 0. 6610. 09580. 520. 780. 52～0. 780. 0660. 07960. 7410. 5820. 582～0. 741
B 0. 6860. 07780. 560. 80. 56～0. 80. 0530. 06410. 7500. 6220. 622～0. 750
p
0. 005606 C 0. 5360. 08580. 400. 630. 40～0. 630. 0590. 07060. 6070. 4660. 466～0. 607
D 0. 7400. 13180. 540. 90. 54～0. 90. 0910. 110. 8500. 6300. 630～0. 850
操作和注意事项 胡逢全Excel 中一些常用统计分析工具的功能、
表8 4条河流中铀浓度数据的方差分析输出结果
分析工具:工具→数据分析→方差分析→单因素方差分析
SUM MARY
组A
B C 计数888求和5. 295. 494. 29方差分析
差异源
SS
df 3
MS 0. 009838
Multiple R R Square
Adjus ted R Square 标准误差 观测值
Intercept
Coefficients 标准误差 t Stat P-valu e Lower 95% Upper 95%
观测值12345
x (m) [**************]00
6789101112
67. 3838. 8157. 6441. 75E-0547. 74187. 024
RESIDU AL OU TPUT
预测Y 64. 8562. 3159.
7757. 2454. 7049. 6344. 5539. 4834. 4129. 3316. 65-8. 72
残差41. 1517. 696. 23-15. 54-13. 00-11. 43-11. 45-14. 98-15. 21-10. 938. 2519. 22
F
P-value
F crit
平均0. 661250. 686250. 53625方差0. 009070. 0058840. 007141表10 某厂底泥表面C 照射量率y 与离废水排放口的距离x 的相关分析
粘贴函数:插入→f x 函数→统计(线性回归参数) 粘贴函数 correl intercept slope RSQ 量 名 相关系数R 截距a 斜率m 相关指数R 回归误差R 2
数 值-0. 76167. 383-0. 2540. 58分析工具:工具→数据分析→回归
SU MMARY OUT PUT
回归统计
0. 7610. 580. 53819. 31312X Variable 1-0. 2540. 068-3. 7150. 004-0. 406-0. 102
组间0. 1787090. 05957 6. 0551040. 00262. 946685
组内0. 27546328总计0. 45417231
3. 2例2
此例数据(见表9) 取自文献[2]7. 2节的表12; 用“粘贴函数”和“分析工具”所得结果示于表10; 拟合曲线图示于图1。表10中, “分析工具:工具→数据分析→回归”的量名和符号, 均是Excel 中的原名(或符号) , 要注意其中第3个子表中Intercept 列和X Variable 1列分别是截距(a ) 列和斜率(b ) 列, 表中第2行“标准误差”给出的就是a 和b 的误差, 余此类推(参见表5) ; 回归统计中的“标准误差”是回归的误差R E 。
表9 某厂离废水排放口不同距离x 处的
底泥表面C 照射量率y
y (L R/h ) 106806641. 741. 738. 2
x (m) [1**********]0
y (L R/h) 33. 124. 519. 218. 424. 910. 5
图1 底泥表面C 照射量率y 与离废水排放口的距离x 的拟合曲线图
辐射防护通讯 2004年4月第24卷第2期
从表10和图1可见, 由“粘贴函数”与“分析工具”给出的同一量的结果完全相同, 表与图给出的结果也相同, 且其与文献[2]计算的结果也一致。图1是在原始图上, 执行以下步骤得到的:(1) 选中图上任一数据点, 点击鼠标右键, 选“添加趋势线”。
(2) 在“添加趋势线”的“类型”选框中选择拟合曲线的函数类型:线性、对数、指数、幂函数和多项式; 再在“选项”框选中“显示公式”和“显示R 平方值”。
计分析计算的例子或有关数理统计教材中的举例, 也可自己编些计算简单的例子, 以便于核对。另外, 数理统计应用的学习, 特别是掌握常用统计分析方法的基本原理和重要概念, 非常需要多做
实例计算[3]; 而Excel 中的统计分析功能能很迅速地计算复杂例题, 而且计算准确, 所以它还可用作数理统计的学习工具。因此, 在使用前对Excel 中的这些常用工具进行全面的实例检验, 具有使用和深入学习的双重价值。
4. 2利用“粘贴函数”和“分析工具”互检
无论数据统计描述、统计检验或相关和回归分析, Excel 可从“粘贴函数”和“分析工具”两个途径得到结果。一般来说, “粘贴函数”给出的结果比较单一, 但便于直接汇总于包括原始数据的数据表中; “分析工具”输出的参数更多, 但有的不是汇总数据表中所需要的。两者的输入方式有的也有些不同, 如“粘贴函数”输入数据区域不包括标志, 而“分析工具”一般包括标志; “粘贴函数”对单元区域使用“相对引用”如D7:D14, 而“分析工具”多用“绝对引用”如＄D ＄7:＄D ＄14(最好用鼠标选中, Excel 将会自动给出合理的引用格式) 等。除了仔细核对输入数据外, 采用“粘贴函数”和“分析工具”两种输出, 是很省力的互检手段; 然后再根据需要, 将结果汇总于数据表。4. 3使用“粘贴函数”时宜多用“拖拉”操作
在多组数据进行相同操作时, 利用Excel 的“拖拉”操作, 可以大大减少工作量和差错。如在t 检验时, 利用Ttest 算相邻两列(或两行) 的t 检验结果p , 采用在该给出结果的单元格用拖拉方式, 可以立即给出其他相邻列(或行) 间同类检验的p 值; 为了用拖拉方式, 输出表的格式要稍作加工(见表7) 。至于要给出各自的统计参数, 只需在给出一组数据的统计结果的单元格用拖拉方式, 即可给出其余各组的相应结果。
4 使用中的几点建议
4. 1使用前需进行实例检验
从上述表1～表5的注意事项看, Excel(中文版) 中的统计工具还存在以下一些缺陷和问题:
(1) 一些输入参数和输出量的术语, 有不少是非标准的或非专业人员通用的, 而且不经过实例检验是容易被误解的。如“统计描述”中的“置信度”和“峰值”实际应分别是置信区间半宽度和减3后的峰度a 4-3; “回归”输出的两个表中的“标准误差”, 是十分有用的, 其分别是回归误差R E 和截距a 、斜率b 的误差S a 、S b , 但不经实例检验很难确认(见表5) 。
(2) 有些输出数据的含义是错误的或不明确的。如“Ftest ”输出的不是“单侧检验的概率”而是双侧检验的概率; “输出的不是通常的confidence ”自由度为(n -2
1) 的置信区间半宽度, 而是自由
度为∞时的置信区间半宽度; “Chitest ”输出的是单侧V 检验的概率, 但没有说明其对应的自由度df =n -1, 这又恰是配合分布检验中不大能用的(常用的配合泊松分布检验df =n -2或正态分布检验d f =n -3, 见表3) 。
(3) 有些对输入数据的排列有专门要求, 但又没有给出必要的指示。如“回归”要求两组数据按列排列; “可重复双因素方差分析”中要求重复样品数据排在同列的不同行内等。
这类缺陷和问题, 对初次使用者准确地理解输入和输出数据的确切含义及其如何输入造成很大困难, 甚至妨碍了它的推广应用。但是, 以笔者的体会, 只要在使用前经过实例检验, 这些困难就很容易解决, 就能便捷、准确、有效地使用这类统计软件。检验所用实例最好是用自己过去作过统
5 参考文献
1 高玉堂, 主编. 环境监测常用统计方法. 北京:原子能出版社,
1980.
2 胡逢全. 数据处理中常用统计方法的基本原理及重要概念
(II). 辐射防护通讯, 1996, 16(3):34.
3 胡逢全. 数据处理中常用统计方法的基本原理及重要概念
(I ). 辐射防护通讯, 1996, 16(1) :27.
(责任编辑:赵 宁)