EXCEL在医学统计学中的应用.docx

1、EXCEL在医学统计学中的应用EXCEL在医学统计学中的应用 倪关森资料和方式1. 资料 主要根据高等医药院校教材卫生统计学3、上海第一医学院卫生统计教研组编写的医学统计方式4、中国医学百科全书医学统计学5、医用数理统计方式6和临床统计学7寿命表及其应用8等书中有关统计公式和例题进行编程。2. 编程软件 应用Microsoft Excel 电子表格软件。编程的函数主要有： 统计函数例如详见统计函数说明工作簿(请下载)。1)求集中趋势的统计函数：AVERAGE(number1,number2,)等，详细请见统计函数解释工作簿的集中趋势的统计函数工作表。2)求离散趋势的统计函数：AVEDEV(nu

2、mber1,number2, )等，详细请见统计函数解释工作簿中的离散趋势的统计函数工作表。3)求频数分布的统计函数：FREQUENCY(data_array,bins_array)等，详细请见统计函数解释工作簿中的频数分布的统计函数工作表。4)正态分布的统计函数：NORMDIST(x,mean,standard_dev,cumulative)等，详细请见统计函数解释工作簿中的正态分布的统计函数工作表。5)总体均数估计和假设检验的统计函数：TTEST(array1,array2,tails, type)等，详细请见统计函数解释工作簿中的总体均数估计和假设检验的统计函数工作表。6)方差分析的统计

3、函数：FTEST(array1,array2)等，详细请见统计函数解释工作簿中的方差分析的统计函数工作表。7)X2查验的统计函数： CHIDIST(x,degrees_freedom)等，详细请见统计函数说明工作簿中的卡方查验的统计函数工作表。8)秩和检验的统计函数： RANK(number,ref,order)等，详细请见统计函数解释工作簿中的秩和检验的统计函数工作表。9)回归与相关：CORREL(array1,array2)等，详细请见统计函数解释工作簿中的回归与相关工作表。经常使用数学、逻辑和查找函数省略，见EXCEL利用指南，例如详见一样函数说明工作簿(请下载)。3应用EXCEL进行统

4、计分析的方式用EXCEL进行统计分析有四种方式来实现：1) 直接应用EXCEL统计函数公式；2) 应用EXCEL分析工具库；3) 应用EXCEL电子表格和函数编制应用程序；4) 应用EXCEL统计宏。其中统计宏的编制需要一定软件基础，难以展开讨论，以下对其他三种方法结合实例分析。 应用EXCEL统计函数公式进行统计计算 EXCEL函数公式不但有强大的科学运算函数和财务分析函数，而且还有丰富的统计函数，这些统计函数可直接对电子表格上指定区域内数据进行统计计算。以两个样本的均数T检验为例：TTEST 求两个样本的均数t 查验TTEST判断两个样本是否可能来自两个具有相同均值的总体。 语法TTEST

5、 (array1,array2,tails,type)Array1为第一个数据集；Array2为第二个数据集。 Tails指明单尾或双尾检验。如果 tails = 1，函数 TTEST 使用单尾检验。如果 tails = 2，函数 TTEST 使用双尾检验。 Type为 t 检验的类型。 1成对检验方法；2等方差双样本检验；3异方差双样本检验。 例1. 某单位研究饮食中缺乏维生素E与维生素A含量的关系，将同种属的大白鼠按性别相同，年龄、体重相近者配成对字，共8对，并将每对中的两头动物随机分到正常饲料组和维生素E缺乏组，过一按时期将大白鼠杀死，测得其肝中维生素A的含量，见表，问不同饲料的大白鼠肝

6、中维生素A含量有无不同？ 操作步骤：(1)在J44：J51区域内输入正常饲料组维生素A含量，(2)在K44：K51区域内输入维生素E缺乏组维生素A含量，(3)在I44格中输入“TTEST(J44：J51,K44：K51,2,1)”。(4)输入回车键后，I44格就显示P值结果：“”。 推断分析：因为P值,故可认为两种饲料组所得肝中维生素A含量的差别有显著性意义，即维生素E缺乏对大白鼠肝中维生素A含量有影响。详细请见统计函数解释工作簿中总体均数估计和假设检验的统计函数工作表。 例2. 某克山病区测得11例克山病患者与13名健康人的血磷值(mmol/L)如下，问该地急性克山病患者与健康人的血磷值是不

7、是不同？ 患者X1： 健康人X2： 操作步骤：(1)在J28：J38区域内输入患者血磷值；(2)在K28：K40区域内输入健康人血磷值；(3)在I28格中输入TTEST(J28：J38，K28：K40，2，2)；(4)输入回车键后，I28格就显示P值结果：。 推断分析：因为P值,故两样本的均数差别不显著。详细请见统计函数解释工作簿中总体均数估计和假设检验的统计函数工作表。卡方查验 也是常用的统计方法，可用CHITEST函数直接计算，举例说明如下： 语法CHITEST (actual_range, expected_range)Actual_range用以检验期望值的观测值区域。 Expecte

8、d_range指包含行列汇总的乘积除以总和后的数据区域。 该函数公式为 CHITEST=p(Xc2)。 其中：Aij = i行j列中的实际值；Eij = i行j列中的期望值。r = 行数；c = 列数。 CHITEST 返回 chi-squared 统计的概率和自由度df，其中 df=(r-1)(c-1)。 例4. 在二乙基亚硝胺诱发大白鼠鼻咽癌的实验中，一组单纯用亚硝胺向鼻腔滴注(鼻注组)，另一组在鼻注基础上加肌注维生素B12，实验结果见表，问两组发癌率有无不同？ 操作步骤：1)在J3：K4区域内输入实际频数数据；2)在J6：K7区域内输入理论频数数据；3)在I3格中输入CHITEST(J3

9、：K4，J6：K7), 4)输入回车键后，I3格就显示P值： 。 推断分析：卡方检验结果显示P值=，故可认为两组发癌率有差别，说明增加肌注维生素B12有可能提高大白鼠的鼻咽癌发生率。详细请见统计函数解释工作簿中卡方检验的统计函数工作表。 应用EXCEL分析工具库进行统计分析 详细请见统计分析工具解释工作簿(请下载)。在EXCEL中还提供统计分析工具库有如下：t-检验：成对双样本均值分析工具、双样本等方差假设分析工具，双样本异方差假设分析工具及其公式；F - 检验：双样本方差分析分析工具，Anova：单因素方差分析工具，Anova：可重复双因素分析工具，Anova：无重复双因素分析工具，相关系数

10、分析工具及其公式，协方差分析工具，直方图分析工具，双样本均值分析，抽样分析工具，相关和回归分析工具，描述统计分析工具，排位和百分比排位分析工具，随机数发生器分析工具，指数平滑分析工具，移动平均分析工具。这些分析工具库利历时都需要对选项进行选择，下面就单因素方差分析、无重复双因素分析、可重复双因素分析工具、相关和回归分析工具的应用，举例说明如下： Anova：单因素方差分析工具 此分析工具通过简单的方差分析(anova)，对两个以上样本均值进行相等性假设检验。 例5. 某职业病防治院对31名石棉矿工中的石棉肺患者、可疑患者及非患者进行了使劲肺活量(L)测定，结果见表，问三组石棉矿工的使劲肺活量有

11、无不同？ 分析提示：此为成组设计的定量资料，是单因素三个样本均数的比较，可用成组方差分析。 操作步骤： 1)设置数据区域，并输入数据。如本例J6：J17为石棉肺患者数据区，K6：K15为可疑患者数据区，L6：L17为非患者数据区。各列第一行为标志位，即石棉肺患者、可疑患者和非患者。 2)选取工具栏中数据分析。 3)选定Anova：单因素方差分析。 4)选择确定按钮。 5)在输入区域 框输入J6：L17。 6)在分组方式框选择逐列，即样本数据按列分组。 7)打开标志位于第一行复选框。 8)在显著性检验水平框输入 9)在输出区域框输入A14。 10)再选择确定按钮。 11)有关单因素方差分析检验结

12、果显示如下：详细请见统计分析工具解释工作簿中单因素方差分析工作表。 （点击图片可放大）Anova：“无重复双因素分析分析工具此分析工具通过双因素 anova 分析(但每组数据只包括一个样本)，对两个以上样本均值进行相等性假设查验。 例6. 为研究雌激素对子宫发育的作用，以四个种类的未成年雌性大白鼠各3只，每只按一种剂量注射雌激素，经一按时期掏出子宫，称重，结果见表。试比较雌激素的作用在三种剂量间的不同，同时比较四个种系大白鼠之间的不同。 （点击图片可放大）分析提示：此是计量资料，按雌激素剂量分为三组，这是一个因素，按大白鼠的不同种系分组，又是一个因素，该试验属随机分组设计，故应用两因素方差分析

13、。 操作步骤： 1)设置数据区域，在垂直和水平两个方向上，进行分组分层设计数据区域并输入数据。如本例、B1、C1、D1为垂直分组，A2、A3、A4、A5为水平分层，将样本数据输入到相应的各组各层区域内，即甲种系大白鼠按雌激素剂量分别将106、116、145(g)输入到B2、C2、D2单元格，乙、丙和丁种系大白鼠子宫重量依此类推输入到B3、C3和D3， B4、C4和D4，B5、C5和D5。 2)选取工具栏中数据分析。 3)选定Anova：无重复双因素分析。 4)选择确定按钮。 5)在输入区域 框输入A1：D5。 6)打开标志复选框。 7)在显著性检验水平框输入。 8)在输出区域框输入A20。 9

14、)再选择确定按钮。 10)有关Anova：无重复双因素分析检验结果显示如下：详细请见统计分析工具解释工作簿中无重复双因素方差分析工作表。查验结果：P,在水准拒绝H0，同意H1， 推断分析：故认为雌激素不同剂量组间的差别有显著性意义，大白鼠不同种系间差别亦有显著性意义。详细请见统计分析工具解释工作簿中无重复双因素方差分析工作表。Anova：可重复双因素分析工具 此分析工具是对单因素 anova 分析的扩展，即每一组数据包含不止一个样本。 例6. 为探讨某化学反映中温度和催化剂对收率的阻碍，有人选了4种温度和三种不同的催化剂甲，乙，丙，对所有可能的组合在相同条件都重复2次实验，所得数据见表，试判定

15、温度、催化剂的作用和它们之间的交互作用是不是显著。 解题思路：此题是某化学反映中温度和催化剂二因素作用下重复2次实验的计量资料，考虑温度和催化剂两个因素是不是确系阻碍收率的重要因素，进一步考虑这两个因素有无交互作用，依照题意和数据特点可用重复实验的两因素方差分析。 操作步骤：1)设置数据区域，在垂直和水平两个方向上，进行分组分层设计数据区域并输入数据。 如本例B1、C1、D1、E1为不同温度分组，A2和A3、A4和A5、A6和A7为甲，乙，丙催化剂分层，其中A2和A3为甲催化剂同一层次重复数据区域，A4和A5，A6和A7为乙，丙催化剂二个层次的重复数据区域。将样本数据输入到相应的各组各层区域内

16、。如重复数据为3次，则每一层应有3个数据区域，A2，A3和A4；A5，A6和A7；A8，A9和A10各为一层。重复次数增加，相应以此类推。 2)选取工具栏中数据分析。 3)选定Anova：可重复双因素分析。 4)选择确定按钮。显示方差分析：可重复双因素分析对话框。 5)在输入区域 框输入A1：E7。 6)在每个样本的行数框内输入2，意味重复试验2次的数据。 7)在显著性检验水平框输入。 8)在输出区域框输入A20。 9)再选择确定按钮。 10)有关方差分析：可重复双因素分析检验结果显示如下： 查验结果：1)温度因素的F值(98.)，催化剂因素的F值(63.)，温度和催化剂交互作用的F值；2)温

17、度因素的P值(P； 催化剂因素的P值(P；推断分析：温度和催化剂交互作用不显著；温度因素对收率有极显著影响；催化剂因素对收率也有极显著影响。详细请见统计分析工具解释工作簿中可重复双因素方差分析工作表。 相关系数分析工具 此分析工具及其公式可用于判断两组数据集(可以使用不同的度量单位)之间的关系。总体相关性计算的返回值为两组数据集的协方差除以它们标准偏差的乘积： 注释若是需要返回两个单元格区域的相关系数，可直接利用CORREL工作表函数。 例7. 某地一年级12名女大学生的体重与肺活量数据如下，试求肺活量(L)Y对体重(kg)X的直线回归方程。 体 重(kg)424246464650505052

18、52X5858肺活量(L)Y分析提示：此为求肺活量(L)Y对体重(kg)X的直线回归方程，可用直线回归与相关分析。 操作步骤： 1)设置数据区域，并输入数据。如本例J4：J16为体重(kg)X自变量数据区，K4：K16为肺活量(L)Y因变量数据区。 2)选取“工具”栏中“数据分析”。 3)选定“相关系数”。 4)选择“确定”按钮。显示“相关系数”对话框。 5)在“输入区域”框输入J4：K16。 6)在“分组方式”框选择“逐列”，即自变量和因变量数据按列分组。 7)打开“标志位于第一行”复选框。 8)在“输出区域”框输入A14。 9)再选择“确定”按钮。 10)有关相关系数分析结果显示如下： 体

19、重(kg)肺活量(L)体重(kg)1肺活量(L)1 检验结果：X相关系数r值。详细请见统计分析工具解释工作簿中相关与回归工作表。 回归分析工具 例7. 同上 操作步骤： 1)设置数据区域，并输入数据。如本例J4：J16为体重(kg)X自变量数据区，K4：K16为肺活量(L)Y因变量数据区 。 2)选取工具栏中数据分析。 3)选定回归分析。 4)选择确定按钮。显示回归分析对话框。 5)在Y 值输入区域 框输入K4：K16。 6)在X值输入区域 框输入J4：J16。 7)在分组方式框选择逐列，即自变量和因变量数据按列分组。 8)打开标志位于第一行复选框。 9)在输出区域框输入A34。 10)再选择

20、确定按钮。 11)有关回归分析结果显示如下： （点击图片可放大）查验结果：回归系数b值，截距 a值；直线回归方程(Y=+。 推断分析：今1=1, 2=10，查附表F界值表，得P。按=水准拒绝H0，同意H1，故能够为一年级女大学生肺活量与体重之间有直线关系。详细请见统计分析工具说明工作簿中相关与回归工作表。 应用EXCEL电子表格和函数编制应用程序进行统计分析 根据统计的原理和计算的步骤，应用上述EXCEL函数，就可以编写成常用的统计程序，实际编程，举例如下： 统计程序设计方式 示例详细请见统计程序设计解释工作簿(请下载)。在EXSEL电子表上编写医学统计应用程序，尽管与真正意义上程序设计有很大

21、差别，但是在程序设计某些基本方面还是很相似，如根据需要解决的具体问题画出流程图，按流程图应用计算机语句(在此应用数学公式、数据管理命令和统计函数)一步一步地解决流程中每一个方面小问题，并把数据、公式和函数写在电子表格的一定区域和单元格，最终得到我们需要解决问题的最后结果。这一进程与EXCEL的宏的制作进程相似，只只是没有记录成一个宏的名字，其解决问题全进程都罗列在电子表上，比较直观，容易发觉问题。最大的益处在于，在解决课题具体统计问题时，EXCEL等没有现成程序和软件，自己动手编程，能恰如其分解决问题，为科研带来专门大方便。专门在反复计算或数据量大时更是如此。为了能清楚地说明编程方式，采纳举例

22、说明，便于读者明白得。 计量资料的统计程序设计以几何均数t查验和单因素方差分析及二二比较为例。几何均数t查验程序流程框图： 程序设计进程： 依照程序流程框图的要求，在电子表格上输入EXCEL函数，为了更简练表达意思，把在C3单元格中输入=IF(B3=0,LOG10(B3)，简化成C3=IF(B3=0,LOG10(B3)。详细情形可打开成组设计的两样本几何均数的t查验程序观看，进程说明如下： 1)设立数据区并且转换数据先B3：B22作为样本甲的数据输入区，C3：C22作为对样本甲的数据转换成对数形式的区域，C3=IF(B3D33,IF(AND(B30B33),)双侧：B37=IF(B30E33,

23、IF(AND(B30C33),)5)分析、推断和结论：省略程序详细请见统计程序设计说明工作簿中几何均数t查验工作表。例8选甲型流感病毒血凝抑制抗体滴度(倒数)5者24人,随机分为两组,每组12人,用甲型流感病毒活疫苗进行免疫,一组用气雾法,另一组用鼻腔喷雾法。免疫后一月采血，别离测定血凝抑制抗体滴度，结果如下，问两法免疫的成效有无不同？ 气 雾 组，X1：40 20 30 25 10 15 25 30 40 10 15 30 鼻腔喷雾组，X2：50 40 30 35 60 70 30 20 25 70 35 25计算结果： t值，B35为单侧查验结果(，E35为单侧查验P值；B37为双侧查验结

24、果(，E37为双侧查验P值。 推断结论：按=水准拒绝H0，接受H1，故可认为两法免疫的效果有差别，用鼻腔喷雾免疫法比气雾免疫法的效果好。 单因素方差分析及二二比较程序设计 程序设计： 根据程序流程框图的要求，另选一张工作表在电子表格上开始输入EXCEL函数。 1设定甲、乙和乙三样本数据区域：样本甲区域：B3：B15；样本乙区域：C3：C15 ；样本丙区域：D3：D15。 2.求各样本数据之和及总和：B16=SUM(B3：B15)C16=SUM(C3：C15)；D16=SUM(D3：D15)；E16=SUM(E3：E16)。 3. 求各样本数据之均数及总均数：B17=AVERAGE(B3：B15)；C17=AVERAGE(C3：C15)；D17=AVERAGE(D3：D15)；E17=AVERAGE(B3：D15)。4.求各组样本数和总样本数B18=COUNT(B3：B15)；C18=COUNT(C3：C15)；D18=COUNT(D3：D15)；E18=COUNT(B3：D15)。 5.求各组合计均方和总均方B19=SUMSQ(B3：B15)；C19=SUMSQ(C3：C15)；D19=SUMSQ(D3：D15)；E19=SUMSQ(B3：D15)。 6.求各组方差和总方差B20=(B19-(B16)2/B18)/(B18-1)；C20=(C19-(C16