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

1、EXCEL 在医学统计学中的应用(一) 概 述 本课程所需所有 EXCEL 示例程序，请下载： 一般函数解释.xls ， 统计函数解释.xls ， 统计分析工具解释.xls ， 统计程序设计解释.xls ， EXCEL 实用临床统计程序.xls 。 统计学是医学科学的基础学科，对临床医学和实验研究有重要的指导价值1。对大多数临床医师而言，在正确应用统计分析和计算时常感力不从心。为此，早在十几年前，就有统计学工作者应用 BASIC 语言编写统计程序2，以后随着微机的软件和硬件技术的发展，为统计学计算带来了飞跃发展，但建筑在DOS 操作系统和数据库基础上的统计软件包需要培训，故其普及和推广应用受到

2、一定限制3。近年来，建筑在 Windows 操作平台的 OFFICE 软件得到迅速普及，其中 EXCEL 优越的数据处理性能和强大的统计计算功能开始为大家所熟悉，在国外有许多医院和实验室已经用 EXCEL 进行医学数据资料的统计计算的报道4，国内也有这方面的报道5，经推广使用和好医生网站交流，颇受欢迎。 EXCEL 不仅具有现有的的强大的编程和统计功能，更有利于医学资料和统计资源的共享。EXCEL 是一个很有发展潜力的软件，其宏命令和 VBA(Visual Basic for Application)更为 EXCEL 扩展功能提供了界面，以编程方式对数据分析功能的访问是所有表处理软件中最高级的

3、；EXCEL 提供了电子邮件的内部特性的功能，使创建的文件可以和 INTERNET 上其他人提供的文件进行联接，下载工作簿到用户计算机，也可上载工作簿到 FTP(文件传输协议)站点，其数据交换和网络通讯，为统计学工作者数据资料的收集、处理和统计分析提供了更广阔的范围。 EXCEL 在医学统计中的应用 在电子表格上，编制了一些常用的医学统计程序，经实际使用以及和 SAS、SPSS 对比，计算结果一致。现把如何应用介绍如下：进入统计程序 每一张电子表格是一个统计程序，当进入 EXCEL，打开卫生统计工作簿时，用鼠标点开标签，则该统计程序的电子表格就显示在屏幕上，就可直接使用该统计程序。 常用统计程

4、序 2.2.1 计量资料的统计程序 U 检验、t 检验、几何均数 t 检验、配对 t 检验、t 检验、方差分析(三样本)、配伍方差分析、方差分析(四样本)、随机区组方差分析、拉丁方设计方差分析、无重复试验的两因素方差分析、重复试验的两因素方差分析、几个处理组均数与一个对照组均数比较、多个样本两两比较(Newman-Keuls 法)、例数相等协方差分析、例数不相等协方差分析、2X2 析因方差分析、2X2X2 方差分析、直线回归与相关。 详细请见 EXCEL 实用临床统计程序.xls 2.2.2 计数资料的统计程序 卡方检验有：基本公式、四格表专用公式、2X2 等列联表和四格表的确切概率法。RXC

5、 G 检验等。(详细请见 EXCEL 实用临床统计程序.xls) 2.2.3 非参数的统计程序 秩和检验：配对秩和检验、成组设计两样本比较的 U 秩和检验、等级资料的两样本秩和检验、成组设计两样本比较的秩和检验、成组三样本秩和检验(Kruskal-Wallis 法)、成组四样本秩和检验(Kruskal-Wallis 法)、成组秩和检验(Kruskal-Wallis 法)、多个样本两两比较的秩和检验、RIDIT 分析、配对资料的符号检验、样本与总体中位数比较的符号检验、升降趋势的符号检验(Cox-Stuart)等。(详细请见EXCEL 实用临床统计程序.xls) 2.2.2 其他统计程序 均数的

6、抽样、样本均数与总体均数比较(或配对比较)、两样本均数比较、多个样本均数比较、率的抽样、两样本率的比较、配对分类资料比较、多个样本率比较。标准化率：直接法计算疾病标准化死亡率(1/10 万)、间接法计算疾病标准化死亡率(1/10 万)。寿命表及其应用：简略寿命表、研究肿瘤(或其他慢性疾病)生存率的改进寿命表法，n 月生存率方差、标准误和 95%可信区间的计算；完全数据的队列寿命表法、被随访者同时进入随访队列的寿命表、被随访者随时进入随访队列的寿命表、随访资料中被随访者尚有生存者并有失访者的常见队列寿命表、用寿命表方法计算概率病死数等。(详细请见 EXCEL 实用临床统计程序.xls) 使用方法

7、和注意要点 2.3.1 对初学者而言，先打开统计程序后，就可看到电子表下面的标签内容，有使用说明、U 检验、成组 T 检验、配对 T 检验、几何 T 检验。只要求使用者根据自己想解决的问题，正确选择统计方法和相应的统计程序，选择所需的工作表后，将统计资料输入到原始数据输入区，即红框内就完成操作；就可举重若轻地解决烦琐的计算问题，得到统计结果。 2.3.2 数据输入 当样本资料超过资料框时，可先进行插入行操作，再输入资料数据即可，但不能作列插入，否则无法进行统计计算。有的数据插入后，其右边单元格中公式需要复制，如配对设计 t 检验、几何均数比较的t 检验、配伍组设计方差分析等，复制时只要将上面有

8、公式的单元格复制框向下拖动即可。 整批样本资料数据(千万注意不要将格式和公式一起复制)用复制方式输入数据，复制后须将指示框移开。 重新计算，一定要将资料样本区的上一批数据全部清除，否则会影响本次计算结果。 计算迅速精度高，同时显示出各统计步骤的中间结果，便于核对和查找存在的问题。计算结果的精度可达小数点后 12 位，满足统计计算的要求。 这些程序自 98 年编写以来，不断改进，并不断有新的实用程序与大家见面，对临床医学和实验研究起到了重要的指导作用。但另一方面，EXCEL 虽有分析工具的现成统计软件供大家使用，但尚有许多有用的统计方法没有提供，如二二比较、2X2X2 设计资料的方差分析、秩和检

9、验、配对资料的符号检验、样本与总体中位数比较的符号检验、升降趋势的符号检验(Cox-Stuart)、标准化率、寿命表及其应用等。本文目的在于抛砖引玉，希望统计学工作者能用 EXCEL 编写出更多更实用的具有中国特色的统计程序，更希望有更多的统计学工作者和临床医师结合起来，设计出更多符合实际需要的统计程序，能使大家能从繁琐的计算中解放出来，让电脑代替人脑，使人们的精力更多地集中在科研设计和创造性的思维中去。 参考文献 1. 方积乾，徐勇勇，余松林。医学统计学与电脑实验。上海：科学技术出版社，1997：23。 2. 毛宗秀。BASIC 语言常用数理统计方法程序汇编。浙江：科学技术出版社，1979：

10、1172 3. 俞顺章，俞国培。疾病数据的管理和分析。上海：上海医科大学出版社，1992：25 4. Kokol-P. A tool for software and hardware evaluation .J-Med-Syst.1966 Jun;20(3):167172 5. 董大钧，张宝珍等。利用 Excel 进行医学统计 t 检验分析。中国卫生统计，1999,16(3)：184185 EXCEL 在医学统计学中的应用 (二) 应用 EXCEL 统计函数公式进行统计分析本课程所需所有 EXCEL 示例程序，请下载： 一般函数解释.xls ， 统计函数解释.xls ， 统计分析工具解释.x

11、ls ， 统计程序设计解释.xls ， EXCEL 实用临床统计程序.xls 。 应各位医生、其他专业的朋友和网友的要求，现将 EXCEL 在医学统计学中的应用的原理和方法介绍如下： 资料 主要根据高等医药院校教材卫生统计学1、上海第一医学院卫生统计教研组编写的医学统计方法2、中国医学百科全书医学统计学3、医用数理统计方法4和临床统计学5寿命表及其应用6等书中有关统计公式和例题进行编程。 编程软件 2.1 统计函数 : 示例详见 统计函数解释工作簿 (请下载)。 1) 求集中趋势的统计函数： AVERAGE(number1,number2, .)等，详细请见统计函数解释工作簿的集中趋势的统计函

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

13、函数解释工作簿中的总体均数估计和假设检验的统计函数工作表。 6) 方差分析的统计函数： FTEST(array1,array2)等，详细请见统计函数解释工作簿中的方差分析的统计函数工作表。 7) X2 检验的统计函数： CHIDIST(x,degrees_freedom) 等，详细请见统计函数解释工作簿中的卡方检验的统计函数工作表。 8) 秩和检验的统计函数： RANK(number,ref,order) 等，详细请见统计函数解释工作簿中的秩和检验的统计函数工作表。 9) 回归与相关： CORREL(array1,array2) 等，详细请见统计函数解释工作簿中的回归与相关工作表。 2.2 常

14、用数学、逻辑和查找函数 ： 省略，见 EXCEL 使用指南详细请见 一般函数解释工作簿 。 应用 EXCEL 进行统计分析的方法 用 EXCEL 进行统计分析有四种方法来实现：1 直接应用 EXCEL 统计函数公式；2 应用 EXCEL 分析工具库；3 应用 EXCEL 电子表格和函数编制应用程序；4 应用 EXCEL 统计宏。其中统计宏的编制需要一定软件基础，难以展开讨论，以下对其他三种方法结合实例分析。 3.1 应用 EXCEL 统计函数公式进行统计计算 EXCEL 函数公式不但有强大的科学运算函数和财务分析函数，而且还有丰富的统计函数，这些统计函数可直接对电子表格上指定区域内数据进行统计

15、计算。以两个样本的均数 T 检验为例： 3.1.1 TTEST 求两个样本的均数 t 检验 TTEST 判断两个样本是否可能来自两个具有相同均值的总体。 语法 TTEST(array1,array2,tails,type) Array1 为第一个数据集；Array2 为第二个数据集。 Tails 指明单尾或双尾检验。 如果 tails = 1，函数 TTEST 使用单尾检验。 如果 tails = 2，函数 TTEST 使用双尾检验。 Type 为 t 检验的类型。 1 成对检验方法； 2 等方差双样本检验； 3 异方差双样本检验。 例 1某单位研究饮食中缺乏维生素 E 与维生素 A 含量的关

16、系，将同种属的大白鼠按性别相同，年龄、体重相近者配成对字，共 8 对，并将每对中的两头动物随机分到正常饲料组和维生素 E 缺乏组，过一定时期将大白鼠杀死，测得其肝中维生素 A 的含量，见表，问不同饲料的大白鼠肝中维生素 A 含量有无差别？ 操作步骤：(1)在 J44：J51 区域内输入正常饲料组维生素 A 含量，(2)在 K44:K51 区域内输入维生素 E 缺乏组维生素 A 含量，(3)在 I44 格中输入“TTEST(J44:J51,K44:K51,2,1)“。(4)输入回车键后，I44 格就显示 P 值结果：“0.004001“。 推断分析：因为 P 值 0.05, 故两样本的均数差别不

17、显著。详细请见统计函数解释工作簿中总体均数估计和假设检验的统计函数工作表 3.1.2 卡方检验 也是常用的统计方法，可用 CHITEST 函数直接计算，举例说明如下： 语法 CHITEST(actual_range, expected_range) Actual_range 用以检验期望值的观测值区域。 Expected_range 指包含行列汇总的乘积除以总和后的数据区域。 该函数公式为 CHITEST=p( Xc2 )。 其中：Aij = i 行 j 列中的实际值 ；Eij = i 行 j 列中的期望值。r = 行数；c = 列数。 CHITEST 返回 chi-squared 统计的概率

18、和自由度 df, 其中 df = (r - 1)(c - 1). 例 4在二乙基亚硝胺诱发大白鼠鼻咽癌的实验中，一组单纯用亚硝胺向鼻腔滴注(鼻注组)，另一组在鼻注基础上加肌注维生素 B12，实验结果见表，问两组发癌率有无差别？ 操作步骤：(1)在 J3：K4 区域内输入实际频数数据；(2)在 J6：K7 区域内输入理论频数数据；(3)在 I3 格中输入“CHITEST(J3：K4，J6：K7)“, (4)输入回车键后，I3 格就显示 P 值: “0.01092“。 推断分析：卡方检验结果显示 P 值= 0.01092，D33, “B33),“0.05“) 双侧：B37=IF(B30E33, “

19、C33),“0.05“) 5)分析、推断和结论：省略， 4.2.2 程序：详细请见统计程序设计解释工作簿中几何均数 t 检验工作表 例 8：选甲型流感病毒血凝抑制抗体滴度(倒数)9.21,“方差明显不齐“,IF(AND(B225.99),“方差齐性不好“,“方差齐性好“) 10.设置方差分析表。 求组内、组间和总变异： B27=E19-E22； B28=(B162/B18+C162/C18+D162/D18)-E22； B29=B27-B28 11. 求组内、组间和总自由度： C27=E18-1；C28=3-1；C29=E18-3 12.求均方： D28=B28/C28；D29=B29/C29

20、 13.求 F 值：E28=D28/D29 14.查 F 界值： B31=VLOOKUP(D30，A40：Y90，B30)； E31=VLOOKUP(D30，A100：Y149，B30)。 15.求 P 值： B32=IF(E28E31,“B31),“ 0.05“)。 16.作两两比较。 设置两两比较表。将各组均数排序并自动填入表中： A46=LARGE(B17：D17，1) A47=LARGE(B17：D17，2) A48=LARGE(B17：D17，3) 17.根据排序情况自动将各组样本数填入表中： B46=HLOOKUP(A46，B17：D18，2)； B47=HLOOKUP(A47，B

21、17：D18，2)； B48=HLOOKUP(A48，B17：D18，2)。 18.求对比组均数差值： (Xa-Xb)：D46=A46-A48；D47=A46-A47；D48=A47-A48 19.求各对比组 q 值： F46=D46/SQRT(D29/2)*(1/B46+1/B48)； F47=D47/SQRT(D29/2)*(1/B46+1/B47)； F48=D48/SQRT(D29/2)*(1/B47+1/B48)。 20.设置对比组组距和自由度： E46=B49,B49=3；E47=D49,D49=2；E48=F49,F49=2；H48=C29 21.查找 q 界值(0.05),并填

22、入表中： C51= VLOOKUP(H49,AA62:AJ77,B49)； C52=VLOOKUP(H49,AA62:AJ77,D49)； C53= VLOOKUP(H49,AA62:AJ77,F49)； G46=C51；G47=C52；G48=C53。 22.查找 q 界值(0.01), 并填入表中： E51=VLOOKUP(H49,AA82:AJ97,B49)； E52=VLOOKUP(H49,AA82:AJ97,D49)； E53=VLOOKUP(H49,AA82:AJ97,F49)； H46=E51；H47=E52；H48=E53。 23.求 P 值： C54=IF(F46E51,“C

23、51),“0.05“)； C55=IF(F47E52,“C52),“0.05“)； C56=IF(F48E53,“C53),“0.05“)；I46=C54； I47=C55；I48=C56。 24.设置 F0.05 界值表区域： A61：Y111 区域为 0.05F 界值表； B61：Y61 区域为分子自由度区域。 A62：A111 区域为分母自由度区域，输入 F0.05 界值； B62：Y111 区域，填入 F0.05 界值。 25. 设置 F0.01 界值表区域： A120：Y170 区域为 0.01 界值表； B120：Y120 区域为分子自由度区域。 A121：A170 区域为分母自由

24、度区域，输入 F0.01 界值； B121：Y170 区域，填入 F0.01 界值。 26.设置 Q 界值表： 确定 Q0.05 界值表区域： AA61：AJ77 为 Q0.05 界值表区域； AB61：AJ61 为组距区域； AA62：AA77 为自由度区域，输入 Q0.05 界值； AA62：AJ77 区域，填入 Q0.05 界值。 确定 Q0.01 界值表区域： AA81：AJ97 为 Q0.01 界值表区域； AB81：AJ81 为组距区域； AA82：AA97 为自由度区域，输入 Q0.01 界值； AA82：AJ97 区域，填入 Q0.01 界值。 27.程序： 详细请见统计程序设

25、计解释工作簿中三样本成组方差分析工作表。 例 9：例题和结果同例 5。 操作调试： (1)按照上述方法在工作表中组成一个三样本成组方差分析电子表格。 (2)分别将三样本的原始数据输入到 B3：B15，C3：C15 和 D3：D15 区域中。 (3)A25：G29 为方差分析表，B27 为总方差(10.8)，B28 为组间方差(9.265656566)，B29 为组内方差(1.534343434)；C27 为总自由度(30)，C28 为组间自由度(2)，C29 为组内自由度(28)；D28 为组间均方差(4.632828283)，D29 为组内均方差(0.05479798)；E28 为方差值(8

26、4.5437788)，F28 为 P 值大于或小于0.05 或 0.01 的情况(=0.01,B150,1,“ “)，。 （4）取绝对值：E3=ABS(F3)，。 （5）说明：在数据输入后，秩和检验前，须对数据资料以差值的绝对值为关键词，进行递增排序。该区域排序可用 Excel 工作区顶部的菜单栏中“数据“排序命令，通过排序对话框来实现。 2）编秩、秩次校正和确定检验统计量 （1）符号标志：B15=IF(D3=“ “,0,D3)，。 （2）自小而大排列绝对值：C15=SMALL(E3:E12,1)，C16=SMALL(E3:E12,2)，。 （3）复制输入区域绝对值：D15=E3，。 （4）对

27、排序后的数据统一编秩次：E15=RANK(D15,D15:D24,1)，。 （5）去零校正：F15=E15-E13。 （6）去零秩次：G15=IF(F150,F15,“ “)，。去除差值为零的秩次。 （7）同号处理：H15=IF(G15=“ “,“ “,1)； H16=IF(AND(G160),G16+1,G16)，。同符号同秩次的校正。 （8）连续同号处理：与上比较：I15=“ “；I16=IF(AND(C16=C15,B16*B150,L15,“ “) ，。 （12）负号秩次：N15=IF(L1510,“ “,IF(A34=B34,“,IF(AND(B31A34, B31“,“10,“ “

28、,IF(C34=D34,“ “,IF(AND(B31C34, B31“,“10,“ “,IF(E34=F34,“ “,IF(AND(B31E34, B31“,“10,“ “,IF(G34=H34,“ “,IF(AND(B31G34, B31“,“ 0.05。按双侧 0.05 水准，不拒绝 H0，故尚不能认为两法测定车间空气中 CS2 含量有差别。 对统计程序设计的注释 上述各表达式如：F3=B3-C3，意思是在 F3 单元格中输入“=B3-C3“，EXCEL 程序设计是在相关的单元格输入有关函数表达式，单元格将显示结果，激活时仅显示函数表达式。 3.1 程序编写 在 EXCEL 电子表格上的编

29、写程序，比较直观，明白易懂。而且有许多统计语句可直接调用，这就使统计程序的编写变得十分简洁和方便。 3.2 修饰 通过对字体和单元格修饰，如颜色、单元格边框、图案等，使各功能块更醒目，大红边框单元格区域是原始资料数据输入区，粉红边框单元格区域是数据排序区。 3.3 调试 根据程序框图步骤，在指定的单元格或区域输入命令、数学公式和统计函数，则程序编写完毕。但是，需要进一步调试，才能应用。调试时可先将统计学例题中数据送入数据区域，再逐步检查各中间计算结果，发现错误，弄清原因，加以纠正，最终结果应与例题一致，说明程序调试成功。当然也要注意编程产生的问题和特殊数据可能产生的极端情况，如：配对数据的确保

30、措施，秩和检验中的多数据重复的校正等。 3.4 保护 当调试完毕后，在数据区域、各中间结果和最后结果周围单元格注明X、X2、自由度、均数、均方差、t 值、t 界值和 P 值大小，便于日后使用方便。在使用前，最好把除数据区域以外的单元格的命令、公式和函数保护起来，以免在使用过程中不小心改写或擦掉，可用 EXESL 中工具栏下拉菜单的保护命令，输入密码后，就能起保护作用。 3.5 标签 在工作表标签处写上几何均数 t 检验，以便以后使用时识别寻找。 3.6 运行环境 本法编制的常用统计程序在 Windows 95 、97、98 及以上操作系统环境下均可运行，使用 OFFICE 中的 EXCEL 7.0 以上版本即可。 参考文献 o 1. 杨树勤，周有尚，倪旱雨。卫生统计学。北京：人民卫生出版社，1994：24178。 2. 上海医科大学卫生统计学教研组。医学统计方法。上海：科学技术出版社，1979：5761 3. 杨树勤主编。中国医学百科全书医学统计学。上海：科学技术出版社，1985：129144 4. 郭祖超主编。医用数理统计方法。北京：人民卫生出版社，1988：631656 5. 丁道芳，刘延令。临床统计学。北京：人民卫生出版社，1986：139142 6. 王仁安，陈育德。寿命表编制方法及其应用。北京：人民卫生出版社，1990：133171