用Excel建模做基于样本统计量的单因素方差分析法

陈雄新

(湖南环境生物职业技术学院 护理学院，湖南 衡阳 421005)



用Excel建模做基于样本统计量的单因素方差分析法

陈雄新

(湖南环境生物职业技术学院 护理学院，湖南 衡阳 421005)

摘要：生态学与医学领域的科研工作中经常需要对数据做方差分析,在已知样本例数、平均数、标准差等样本统计量的前提下,如果利用Excel软件制作方差分析模板,既可为数据分析提供方便快捷的方差分析手段,也可为审阅论文数据时,如何判断其结论的真实性提供了新方法.图8，表2，参5.

关键词：单因素方差分析;样本统计量;Excel建模;生态学研究;快捷分析与判断

在生态学与医学领域的科研工作中经常需要对数据做方差分析,尽管统计软件有许多,但是多需要提供原始数据.当阅读科研论文时,经常遇到文中并没有提供原始数据仅列出了样本例数、平均数、标准差等样本统计量,而报告了方差分析的F值及其q检验结果的情况.那么,有没有一种方便快捷手段提供方差分析结果并判断其论文数据的真实性.因此，笔者介绍一种基于样本统计量并利用Excel建模做快速分析的单因素方差分析方法.

1材料来源

用二氧化矽50 mg对大鼠染尘后,不同时期的全肺湿重变化如表1[1],试比较染尘后1、3、6月3个时期的湿重有无差别.

表1　二氧化矽50 mg染尘后3个时期大鼠全肺湿重(单位：g)

某社区随机抽取了30名糖尿病患者、IGT异常及正常人进行载脂蛋白(mg/dL)测定,比较3种人的载脂蛋白结果有无差别[2]，结果见表2.

表2　糖尿病患者、IGT异常及正常人的载脂蛋白测定结果(单位：mg/dL)

2研究方法

2.1单因素方差分析

在Excel中其操作步骤为：①打开一个Excel工作表的窗口,将例1的原始数据按下图的方式列出,见A2-E8的格子,见图1.②单击Excel 菜单“工具”栏,→选择“数据分析”功能项,→选择“单因素方差分析”分析项,→单击“确定”按钮.③出现“单因素方差分析”对话框,在输入区域,选中包括原始数据A2-C8的所有格子,在输出区域,选中$I$1,在标志位于第一行处打“√”, 单击“确定”按钮.④在$I$20区域出现单因素方差分析结果,见图2[3].

图1　表1原始数据图Fig.1　Original data chart of tab.1

图2　表1单因素方差分析结果Fig.2　Single factor analysis of variance results from tab.1

2.2方差分析Excel模板的建立

2.2.1各比较组例数相等的单因素方差分析

组间的均方为：

其对应的自由度为vzj=k-1.

组内的均方为：

其对应的自由度为vzn=N-k,F=MSzj/MSzn.

2.2.2各比较组例数不相等的单因素方差分析

2.2.3各比较组例数相等方差分析Excel模板编制

按下列公式编制模板：

E12 =SUM(B12:D12),B15 =B14^2,C15 =C14^2,D15 =D14^2,

B17=((((B13)^2+(C13)^2+(D13)^2))-(((B13+C13+D13))^2)/3)/(3-1),

B18=B12*B17,B19=B16-1,B20=(B15+C15+D15)/B16,B21=(B12+C12+D12)-B16,B22 =B18/B20.

分析模板见图3.

图3　基于表1的方差分析Excel模板(样本例数相等)Fig.3　Variance analysis template by Excel based on tab.1 (sample cases are equal)

2.2.4各比较组例数不相等方差分析Excel模板编制

按下列公式编制模板：

E16=SUM(B16:D16),B19 =B18^2,C19 =C18^2,D19 =D18^2,

C21=(((((B17)^2)*B16+(C17^2)*C16+(D17^2)*D16))-((((B17*B16+C17*C16+D17*D16)^2)/E16)))/(E16-1),C22 =C21*(E16-1)/(B20-1),C23 =B20-1,

C24=((B18)^2*(B16-1)+(C18)^2*(C16-1)+(D18)^2*(D16-1))/(E16-B20),

C25 =E16-B20,C26 =C22/C24.

分析模板见图4.

图4　基于表2的方差分析Excel模板(样本例数不等)Fig.4　Variance analysis template by Excel based on tab.2 (sample cases are unequal)

2.3Excel模板保存与应用

按上述方法可以列出更多组比较的数据,将模板适当命名后按Excel工作表文件格式保存,以便今后使用.使用时只需要将待检验的样本统计量(样本例数、平均数和标准差)数据代入图3模板内区域B12：D14或图4模板内区域B16:D18替换原来的相应数据,从模板中立即就可得到数据更新后的方差分析结果.

2.4多个样本均数间的两两比较q检验

2.4.1样本例数相等的多个样本均数间的两两比较q检验结果

按下列公式编制模板：

C26=D13-B13,C27=C13-B13,C28=D13-C13,D26=SQRT((B20/2)*(1/B12+1/D12)),D27=SQRT((B20/2)*(1/C12+1/B12)),D28=SQRT((B20/2)*(1/C12+1/D12)),E26=C26/D26,E27=C27/D27,E28=C28/D28.结果见图5.

2.4.2样本例数不等的多个样本均数间的两两比较q检验结果

按下列公式编制模板：

C30=B17-C17,C31=B17-C17,C32=D17-C17,D30=SQRT((C24/2)*(1/B16+1/D16)),

D31=SQRT((C24/2)*(1/C16+1/B16)),D32=SQRT((C24/2)*(1/C16+1/D16)),

E30=C30/D30 ,E31=C31/D31,E32=C32/D32.结果见图6.

图6　基于表2的两两比较q检验结果(样本例数不等)Fig.6　q test results by two comparisons based on tab.2 (sample cases are unequal)

3结果

3.1样本例数相等方差分析结果的验证

在图3.基于表1的方差分析模板(样本例数相等)的单元格B12：D14位置分别代入需要检验的样本统计量(样本例数、平均数和标准差)数据后,马上出现方差分析的结果,见图7.根据组间自由度和组内自由度查F界值表,与α水平比较,可以作出统计学的结论.

图7　样本例数相等方差分析结果的验证Fig.7　Verification to results of variance analysis of equal sample cases

3.2样本例数不等方差分析结果的验证

在图4.基于表2的方差分析模板(样本例数不等)的单元格B16：D18位置,分别代入需要检验的样本统计量(样本例数、平均数和标准差)数据后,马上出现方差分析的结果,见图8.根据组间自由度和组内自由度查F界值表,与α水平比较,可以作出统计学的结论

图8　样本例数不等方差分析结果的验证Fig.8　Verification to results of variance analysis of unequal sample cases

4结论

通过两个实例,具体地说明了Excel建模在生态科学与医学领域科研中经常使用的完全随机设计的单因素方差分析的运用.其应用前提是使用者必须对统计学的基本概念和基本方法有所了解,并且对软件Excel的基本功能也有一定的了解.学会使用这一方法后,尤其是学会编制方差分析模板后,将为科研节约许多宝贵时间;并且,它可以帮助迅速判断科研论文中仅仅提供样本统计量所做的方差分析结果的可靠性;而且,Excel软件还可以快速和批量地做χ2检验[4],t检验[5]、u检验和相关分析等等;它可以与Word、SAS、PowerPoint、FoxPro和SPSS等软件之间进行数据的交换,使之发挥更多的作用.

参考文献：

[1] 马燕.卫生统计学[M].北京：人民卫生出版社,2000.

Ma Yan.Health Statistics [M].Beijing: People’s Medical Publishing House,2000.

[2] 倪宗赞.卫生统计学[M].北京：人民卫生出版社,2001.

Ni Zong-zan.Health Statistics [M].Beijing:People’s Medical Publishing House,2001.

[3] 陈雄新.利用Excel软件做统计描述和方差分析的方法[J].湖南环境生物职业技术学院学报,2008,14(3): 17-19.

Chen Xiong-xin.By using excel software to do statistical description and analysis of variance method [J].Journal of Hunan Polytechnic of Environment and Biology,2008,14(3):17-19.

[4] 陈雄新.Excel在t检验中的实用技巧[J].实用预防医学,2006，13(1):202-205.

Chen Xiong-xin.Excel in t-test and practical skills [J].Practical Preventive Medicine,2006,13(1):202-205.

[5] 陈雄新.Excel在四格表χ2检验中的应用技巧[J].现代预防医学,2006,33(10):1 667-1 670.

Chen Xiong-xin.The application of excel in four table chi-square test techniques [J].Modern Preventive Medicine,2006,33(10):1 667-1 670.

Biography：CHEN Xiong-xin,born in 1964,male,professor,research directions: medical statistics and epidemiology.

收稿日期：2016-04-09

作者简介：陈雄新(1964-)，男，湖南长沙人，教授，研究方向：卫生统计学与流行病学.

文章编号：2095-7300(2016)02-028-04

中图分类号：R195.1

文献标识码：A

Using Excel Modeling to Make Single Factor Analysis of Variance Based on Sample Statistics

CHEN Xiong-xin

(College of Nursing,Hunan Polytechnic of Environment and Biology,Hengyang 421005,China)

Abstract：In the scientific research of ecology or medicine,we often need to analyze the variance of the data.Under the premise that sample cases,average,standard deviation and other sample statistics are known,if using Excel software to produce variance analysis template,it can provide researchers with convenient and efficient means of variance analysis as well as a new method for us to judge the authenticity of its conclusions while reviewing the paper data.8figs.,2tabs.,5refs.

Keywords：single factor analysis of variance; sample statistics; Excel modeling; ecological studies; quick analysis and judgment