统计分析的Excel快速实现(1-5)
统计分析的Excel快速实现
彭长华
pzhh@https://www.360docs.net/doc/8c7479610.html,
前言
Excel具有表格管理、数据清单管理、统计图表处理以及数据分析与决策功能，目前已受到广大用户的青睐,并逐步成为进行数据处理分析的主要工具。然而，目前人们对Excel的应用大多还停留在一般的电子表格处理上；用户在初步接触Excel简单的入门书籍后，可能会觉得Excel只是一个制作数据排列和加减乘除的简单表格软件；在书店陈列的大量的Excel书籍，大多都是向读者介绍如何建立一个表格，如何美化表格等基本功能，有些什么函数，其格式如何等等，都是一些基于基础功能学习的内容。而使用Excel进行系统的常用统计分析的的图书比较少,特别是Excel在快速实现常用统计分析方面的图书更是难得。
以Excel完成医学统计分析较为实用，但仍需操作者全面熟悉界面、综合运用函数、公式及图表，这对于非统计专业人士和大、中专院校师生在进行科学研究和统计分析时颇为不便。本书参照数理统计方法和卫生统计学架构，以Excel 2000为平台，建立起“统计分析的Excel快速实现”程序，对常用统计分析方法的每一类型分为三部分作详细讲解：①提出统计分析目标, 介绍相关Excel函数及统计学知识；②Excel快速实现程序的建立过程；③Excel快速实现程序的应用方法。
Excel的某一单元格可调用另一单元格的数据，经合理部署原始数据、中间计算数据及最终统计分析结论之间的调用，使得原始数据与最终统计分析结论之间“联动”。改变原始数据，与之相关的最终统计分析结果也随之改变，具有“即改即得”的特点，本书充分利用Excel的这一特点，将常用统计分析的原始数据与最终统计分析结论部署在同一界面，使统计分析变得直观、快速。读者按第三部分介绍的应用方法,只需在指定单元格录入原始数据后即可得到统计结论。没有任何Excel 基础的初学者,也可以轻松、快速地利用Excel软件为自己工作。
本书阐述了“统计分析的Excel快速实现”程序的建立及应用，可作为高校相关专业师生进行统计学及Excel学习的参考书，也能充当各学科举办“Excel统计分析培训班”的教学用书。所附程序科学便捷，
读者在工作或学习中直接应用其光盘中编好Excel文件，即可达到Excel统计分析的快速实现的目的。
本书建立的统计分析的Excel快速实现的文件中所涉及的统计分析的类型包括频数分布图、描述集中趋势和离散趋势的主要指标、正态分布图、正态曲线下面积的分布规律、动差法正态性检验、用正态分布法确定正常人群参考范围、t检验、方差分析、q检验、LSD-t检验、Scheffe 检验；二项分布样本率的假设检验、Poisson分布样本均数的假设检验；假设检验中常用的几种样本含量估计、χ2检验、频数分布拟合优度的χ2检验、疗效评价—Ridit分析、W检验、秩和检验、诊断试验的评价—ROC分析、一致性(Kappa)检验、总体的区间估计、直线相关与回归、等级相关和序相关、曲线拟合、单纯随机抽样、配对设计、交叉设计、完全随机设计、配伍组设计、测量的信度估计。每种类型的统计分析都采用Excel实际分析图表进行介绍。
由于作者学识水平的局限，本书中错误或不妥之处在所难免，恳请读者批评指正。
作者
2007年10月
目录
第1章 Excel基础 (6)
1.1 Excel启动运行............................ . (1)
1.2 Excel界面及使用环境简介........................ . (2)
第2章描述集中趋势的主要指标 (8)
2.1 频数及频数分布图 (3)
2.2 其它描述集中趋势的主要指标 (11)
第3章描述离散趋势的主要指标 (18)
3.1 极差和四分位数间距 (13)
3.2 方差、标准差、变异系数及标准误 (13)
第4章正态分布 (20)
4.1 正态分布曲线图绘制 (15)
4.2 正态曲线下面积的分布规律 (18)
第5章动差法正态性检验 (25)
5.1 动差法正态性检验及实际分布与正态分布对比图 (20)
5.2 正态分布法确定人群参考范围 (30)
第6章 t检验............................. 错误！未定义书签。
6.1 样本均数与总体均数比较的t检验 (32)
6.2 配对计量资料比较的t检验 (36)
6.3 已知两样本原始数据(或对数转换)时
均数及变异系数的比较 (40)
6.4 已知两样本例数、均数、标准差时
均数及变异系数的比较 (47)
6.5 多样本均数间两两比较t检验 (50)
第7章方差分析—多个样本均数比较 ........ 错误！未定义书签。
7.1 完全随机设计方法的方差分析 (54)
7.2 配伍组设计方法的方差分析 (59)
7.3 多个样本均数间的两两比较的假设检验 (60)
7.3.1 SNK（Student–Newman–Keuls）检验，亦称q检验 (60)
7.3.2 LSD-t检验 (63)
7.3.3 Scheffe检验，亦称S检验法 (64)
7.4 测量误差的评估 (69)
第8章样本率的假设检验 .................. 错误！未定义书签。
8.1 样本率p与总体（理论）率p0比较 (69)
8.2 两个样本率p1、p2的比较 (79)
第9章 Poisson分布样本均数的假设检验..... 错误！未定义书签。
9.1 样本均数与总体均数的比较 (83)
9.1.1 直接计算概率法 (73)
9.1.2 正态近似法 (83)
9.2 两样本均数的比较 (91)
9.2.1 两样本观察单位相同 (91)
9.2.2 两样本观察单位不同 (92)
第10章假设检验中常用的几种样本含量估计 . 错误！未定义书签。
10.1 推断结论的错误种类 (95)
10.2 两个样本均数比较(t检验)时样本含量的估计 (97)
10.3 样本均数与总体均数比较(或配对比较)时
样本含量的估计 (99)
10.4 两个样本率比较时样本含量的估计 (101)
10.5 样本率与总体率比较时样本含量的估计 (104)
第11章χ2检验 (108)
11.1 四格表资料的χ2检验 (108)
11.2 行列表资料的χ2检验 (114)
11.3 行列表资料的分类及适用的统计方法 (118)
11.4 行列表资料的属性相关 (132)
第12章频数分布拟合优度的χ2检验........ 错误！未定义书签。
12.1 等级资料拟合优度的χ2检验 (134)
12.2 计量资料拟合优度的χ2检验 (140)
第13章疗效评价—行列表资料的Ridit分析 . 错误！未定义书签。
13.1 两组R值的均数检验 (147)
13.2 多组R值的均数检验 (153)
第14章诊断试验的评价—ROC分析.......... 错误！未定义书签。
14.1 诊断试验的评价指标及指标间的比较 (158)
14.2 诊断试验的一致性(Kappa)检验 (173)
14.3 诊断试验的诊断价值
—计量资料的ROC比较分析 (175)
14.4 诊断试验的诊断价值
—等级资料的ROC比较分析 (194)
第15章交叉积差法（W检验法）............ 错误！未定义书签。
15.1 等级资料的W检验 (200)
15.2 计量资料的W检验 (204)
第16章秩和检验 ......................... 错误！未定义书签。
16.1 配对比较的符号秩检验 (214)
16.2 配伍组设计多个样本比较的秩和检验 (220)
16.3 两样本(计量资料)比较的秩和检验 (225)
16.4 两样本(等级资料)比较的秩和检验 (230)
16.5 多样本(计量资料)比较的秩和检验 (233)
16.6 多样本(等级资料)比较的秩和检验 (237)
16.7 多指标变化的一致性检验 (240)
第17章总体的区间估计 .................. 错误！未定义书签。3
17.1 正态分布总体均数的区间估计 (243)
17.1.1总体标准差σ未知 (243)
17.1.2总体标准差σ已知 (243)
17.1.3总体的可信区间与医学参考范围 (243)
17.2 二项分布总体率的区间估计 (244)
17.2.1直接计算概率法 (244)
17.2.2正态近似法 (248)
17.3 Poisson分布总体均数的区间估计 (250)
17.3.1直接计算概率法 (250)
17.3.2正态近似法 (253)
第18章直线相关与回归 .................. 错误！未定义书签。5 18.1 直线相关 (255)
18.2 直线回归 (258)
18.3 应用直线回归与相关时的注意事项 (264)
18.4 利用回归方程进行预测 (265)
18.5 利用回归方程进行统计控制 (270)
18.6 利用直线相关与回归进行方法学评价 (271)
第19章等级相关和序相关 (278)
19.1 Spearman等级相关(计量资料) (278)
19.2 Spearman等级相关(频数数据) (282)
19.3 序相关(频数数据) .................... . (286)
19.4 序相关(计量资料) .................... . (297)
第20章曲线拟合和最小二乘法 ............. 错误！未定义书签。
20.1 逼近曲线的公式 (302)
20.2 最小二乘法 (302)
20.3 一次曲线拟合 (303)
20.4 二次曲线拟合 (316)
第21章调查设计的基本抽样方法 .......... 错误！未定义书签。7
21.1 单纯随机抽样 (327)
21.2 单纯随机抽样样本含量的估计方法 (334)
21.3 单纯随机抽样总体均数（率）的估计方法 (338)
第22章常用的实验设计方法 .............. 错误！未定义书签。1
22.1 配对设计 (341)
22.2 交叉设计 (343)
22.3 完全随机设计 (344)
22.4 配伍组设计 (346)
第23章测量的信度估计 (351)
23.1 二值变量计分测验的信度估计 (352)
23.2 等级变量计分测验的信度估计 (358)
23.3 连续型变量计分测验的信度估计 (361)
23.4 重复测量的信度估计 (366)
附录1 Excel操作过程简写的详解 (371)
附录2 常见统计用表的Excel表达 (373)
附录3 《多重比较q界值表》 (381)
附录4 《等级相关系数r s界值表》 (382)
附录5 《两个诊断试验ROC曲线下面积间的相关系数》 (384)
主要参考资料 (386)
第1章 Excel基础
在科学研究中，经常要对收集到的数据进行各种统计分析。使用较著名的统计分析软件包，如SAS，SPSS等，不仅能单变量分析，而且可做各种复杂的多变量分析。但使用者要有一定的统计学知识和相应的命令编写基础才能准确运用。实际工作中，在对数据进行处理时，多是使用常用统计，如计算均数、标准差、进行t检验、方差分析、相关分析及回归分析等。作这些统计分析时，使用Excel完全可满足要求。
Excel是Microsoft公司开发的办公集成化软件包Office家族中的一员，是一种使用极方便的电子表格软件，它有强大的数据管理功能，能制作各种统计图表，具有丰富的统计函数，能输出漂亮的统计图形和各式统计表格，可完成多种统计任务。Office随微机或者Windows操作系统捆绑销售的情形已经很普遍，使用Excel少有侵权嫌疑。
Excel的一单元格可调用另一单元格的数据，经合理部署原始数据、中间计算数据及最终统计分析结论之间的调用，使得原始数据与最终统计分析结论之间“联动”。改变原始数据，与之相关的最终统计分析结果也随之改变，具有“即改即得”的特点，这是SAS、SPSS等软件无法媲美的。
1.1Excel 启动运行
1快捷方式：双击桌面的Microsoft Excel图标：
2常规方式：单击“开始”“程序”“Microsoft Excel”
3打开Excel文件方式：直接双击任一Excel文件，其图标为：
1.2Excel 界面及使用环境简介
Excel启动后进入如下页图示界面，从上至下各区域依次是:
标题栏—显示Excel文件名，就是Excel工作簿名，系统默认的名称是“Book1”,兰色为活动工作簿。
菜单栏—Excel菜单名。
工具栏—Excel常用工具。
状态栏—当前活动单元格名称及内容。
列标—由A、B、C……大写英文字母标示。一个工作表可多至256列。行标—由1、2、3……阿拉伯数字标示。一个工作表可多至65536行。工作表标签—工作表名，系统默认“Sheet1、Sheet2、Sheet3”,三个工作表，白色为活动工作表。
单元格—列标、行标所对应的方格，黑色粗线框为活动单元格。
单元格内容—每一单元格内可录入数字、字符、表达式（公式）等。
使用环境:
操作系统：windows 98及以上版本。Excel: Excel 2000及以上版本
第2章描述集中趋势的主要指标
科学研究中收集到的一组数据如果在报道时一一列出原始数据就嫌繁冗(rǒng)，且不便比较交流。通常用能反映该组数据的集中趋势、平均水平、分布的平均位置等指标来作为代表，用于分析比较。如频数（frequency），平均数（average）、百分位数(percentile)等。
2.1频数及频数分布图
2.1.1统计分析目标与基础
2.1.1.1 统计分析目标
例2.1 某地196例体检正常者总胆红素（TB,μmol/L）数据如下，试绘制其频数分布图。
13.7 15.2 14.0 9.6 13.5 8.1 10.4 9.1 15.2 6.6
7.7 18.4 17.0 10.8 16.1 15.9 10.6 11.9 8.3 13.2
12.6 8.5 14.4 11.4 8.4 10.7 19.1 11.0 8.4 11.3
18.2 10.9 11.7 22.6 18.2 15.0 11.8 14.5 10.8 8.5
12.6 11.7 7.9 8.6 19.3 5.3 10.6 17.7 10.3 10.0
10.8 15.2 11.0 13.4 11.6 15.4 12.0 12.2 7.4 12.0
12.1 15.0 11.3 13.5 17.6 8.5 14.4 9.9 11.4 17.1
16.0 16.3 15.3 13.5 5.8 5.5 14.6 15.3 16.4 8.5
10.5 11.7 9.2 10.8 7.5 8.7 9.4 18.8 11.7 8.6
13.6 11.9 11.6 13.3 14.3 8.4 10.4 16.7 7.1 12.8
17.4 13.8 11.8 9.7 11.1 12.2 13.4 10.9 18.0 13.7
15.1 6.5 11.8 9.2 15.6 16.0 16.6 25.4 11.2 13.0
17.8 8.2 13.7 10.9 7.8 8.7 13.4 7.7 14.2 15.2
19.0 18.7 7.3 22.9 17.3 10.3 9.2 9.7 11.6 12.3
8.1 15.7 11.8 12.2 11.2 8.2 11.9 33.9 9.5 14.7
10.9 14.0 8.5 19.2 15.5 17.3 10.5 8.5 15.0 14.2
17.6 12.7 20.8 8.3 14.7 11.6 10.4 13.3 9.9
22.4 11.6 12.9 19.2 9.6 9.5 12.3 9.0 11.6
12.0 13.4 10.8 15.7 16.6 18.2 8.4 14.0 11.8
8.6 5.2 12.3 15.4 8.1 10.6 18.2 14.4 13.9
2.1.1.2 统计学基础
例2.1的数据中，最大值(max)是33.9；最小值(min)是5.2；极差
或全距(range)是28.7(max与min之差)；相邻两组段最小（或大）值之差称为组距（class interval）；处于某一组段的数据例数称为该组段的频数（frequency）；频数分布图中，高峰位于中部，左右两侧的频数大体对称，是为对称分布；如果高峰偏于左侧，长尾向右侧（观察值较大的一侧）伸延，称为正偏态分布；若高峰偏于右侧，长尾向左侧（观察值较小的一侧）伸延，称为负偏态分布。
2.1.1.3 相关Excel函数
COUNT（B2:B1000）：计算数组或单元格区域中数字项的个数（行标1000是原始数据可达行标，实际原始数据到达多少行，行标就是多少，例2.1行标为197即可，以下行标1000都是此意）。
MAX（B2:B1000）：数组或单元格区域中最大的数字。
MIN（B2:B1000）：数组或单元格区域中最小的数字。
FIXED(H2/D2,N2)：对H2/D2的值按N2指定的小数位数四舍五入。
IF（L2>G2,G2,L2）：如果L2内数值>G2，得到G2内数值，否则得到L2内数值。
TRUNC（B2）：直接去单元格B2内数值的小数部分,得到整数。
INT（B2）：将单元格B2内的数值向下取整为最接近的整数。
FREQUENCY（B2:B1000，E5）：得到单元格区域内所有≤E5格内数值的单元格数目。
2.1.2 频数分布图的Excel快速绘制程序的建立
2.1.2.1 Excel文件及工作表的建立
单击“开始”“程序”“Microsoft Office”“Microsoft Excel”,打开了一个名称为“Book1”的Excel文件，右键单击工作表标签Sheet1，选“重命名”录入“频数分布图”。单击Excel菜单“文件”“另存为”，在对话框的“保存位置”中选“本地磁盘（D：）”，在“文件名”中将“Book1”替换成“常用统计”，就将建立的“常用统计”文件存放在D盘的根目录中。
2.1.2.2 字符的录入
按图2.1在相应单元格内录入字符。
图2.1 各单元格内的字符
2.1.2.3 数字及公式的录入
1. 原始数据录入
从B2格开始往下录入原始数据13.7，7.7，……
在D2格录入原始数据的分组数，如本例录入15。
这些单元格内的数字每次使用时依使用者要分析的数据而改变，工作表中为加粗黑色字体。如图2.2所示。
图2.2 在B列录入原始数据
2. 非计算数字录入
在A2，A3内分别输入1，2。单击A2，按住鼠标左键拖至A3后放开，此时A2，A3为黑色粗线框，移动鼠标至黑色粗线框右下角，白+字变为黑+时按下左健不放，沿A列向下拖至A197格后放开，得到1至196的序号(这样的等差数列简写为A2=1 A3=2 A2:A3↓A197)。工作表中为加粗绿色字体，下次使用时不需改动。
3. 计算数字公式录入(由等号以及随后的所有字符组成)
单击E2格后录入=COUNT（B2:B1000）
此操作过程的简写为E2=COUNT（B2:B1000）
其它单元格内的字符录入简写如下：
F2=MAX（B2:B1000） G2=MIN（B2:B1000）
H2=F2-G2 I2=FIXED(H2/D2,N2)
J2=FIXED(AVERAGE(B2:B1000),N2)
K2=FIXED(STDEV(B2:B1000),N2)
L2=J2-6*K2 M2=IF(L2>G2,G2,L2)
N2=IF(B2-TRUNC(B2)=0,0,IF(B2*10-TRUNC(B2*10)=0,1,IF(B2*100 -TRUNC(B2*100)=0,2,3)))
E5=M2 E6=E5+$I$2
单击E6使其为黑色粗线框，移动鼠标至黑色粗线框右下角，白+字变为黑+时按下左健不放，沿E列向下拖至E34格后放开（此操作是将E6格公式向下复制至E34格，简写为E6↓E34），得到差为I2格内数字的等差数列。
F5=G5 F6=G6-G5 F6↓F34
G5=FREQUENCY($B$2:$B$1000,E5) G5↓G34
2.1.2.4 频数分布图的绘制
单击Excel工具栏图标：弹出图表向导步骤之1对话框:
图2.3(a) 图表向导步骤之1对话框
选中簇状柱形图后单击“下一步”弹出图表向导步骤之2对话框:
图2.3(b) 图表向导步骤之2对话框
在数据区域框中录入=频数分布图！$F$5:$F$34。单击标签“系列”，
弹出图表向导步骤之2的系列对话框:
在分类(x) 轴标志框中录入=频数分布图!$E$5:$E$34。单击“下一
步”弹出图表向导步骤之3对话框:
图2.3(d) 图表向导步骤之3对话框
在图表标题框中录入“频数分布图”；单击“完成”。分布图就插入Excel工作表中，如图2.3(e) :
图2.3(e) 插入Excel工作表中的频数分布图
按以下步骤除去灰色底色和图例方框“系列1”。
右键单击“系列1”方框，弹出对话框后单击“清除”。
右键单击灰色区域，选对话框中的“绘图区格式”项，单击对话框
中白色方块，示例下方框内变为白色，单击“确定”。得到无底色无图例的频数分布图:
图2.3(f) Excel工作表中无底色的频数分布图
2.1.2.5 复制频数分布图到Word文档
移动鼠标至Excel中的分布图上，鼠标下显示“图表区”时单击，图表四角显出黑色方块，选择Excel菜单“编辑”下的“复制”，活化Word文档，选择Word菜单“编辑”下的“选择性粘贴”，选定“图片（增强型图元文件）”后“确定”。
2.1.2.6 保存文件
单击Excel菜单“文件”“保存”“退出”。
2.1.3 频数分布图的Excel快速绘制程序的应用
2.1.
3.1 程序的运行
双击保存在D盘根目录中的Excel文件“常用统计”，单击工作表“频数分布图”。
2.1.
3.2 原始数据录入
单击B2格，将原始数据表中数据录入该工作表的B2至B197单元格，后在D2格录入想分组的段数（如15段），就可得到196例TB数据的频数分布图，改变D2格内数字，可观察TB分成不同组段数时的频数分布图。
2.1.
3.3 运行结果
在D2格录入的分组段数为15时，得到196例TB数据的频数分布如图2.4：
图2.4 Excel中196例TB数据及频数分布图
第2行D列至N列为频数分布图相关参数。
F列得到全部TB分布在各相应组段的例数，亦称频数。
G列得到不大于相应组段界值（E列同行数值）的例数。
公式中符号“$”为行列固定符，其后的行、列标在复制时不变。
频数分布图的作用：
1. 揭示数据分布特征。本例中TB数据集中在9.86～11.8组。
2. 便于发现某些特大或特小的可疑值。在～28.9、～30.8、～32.7连续三组段频数为0后，在～34.6组段还有一3
3.9的可疑数据。经综合分析证实此样本来自于非健康者，剔除此数据。
3. 样本量较大时可用频率作为概率的估计。
2.1.4 SPSS的分析结果
本例使用SPSS v13.0统计软件(G raphs→Histogram)得到频数分布图如图2.5:
图2.5 使用SPSS软件得到的频数分布图
2.2 其它描述集中趋势的主要指标
2.2.1 平均数（average）
用于描述一组同质计量资料的集中趋势（central tendency），反映一组观察值的平均水平，反映一个分布的平均位置的数称为平均数，它常代表一组资料用于组间分析比较。
2.2.1.1 算术平均数（arithmetic mean）
算术平均数简称均数（mean），样本均数用x表示：将所有观察值X i直接相加后除以观察值的个数n。
计算公式为x=（x1+ x2+ x3 +……+x n）/n
将所有观察值X i录入到Excel工作表“频数分布图”的B2至B1000后，在J2格录入Excel公式：
J2=AVERAGE(B2:B1000)
则J2格显示的数值即x。（见图2.4）
2.2.1.2 几何均数(geometric mean)
用G表示。观察值X i之间常呈倍数关系。
计算公式为 G=lg-1{(lgx1+ lgx2+ lgx3 +……+lgx n)/n}
将所有观察值X i录入到Excel工作表“频数分布图”的B2至B1000后，录入Excel公式：
C2=LOG10(B2) C2↓C197
J3=10^AVERAGE(C2:C1000)
则C列显示的数值是B列相应行数值的对数值；J3格显示的数值即几何均数G（见图2.4）。
2.2.2 百分位数(percentile)
百分位数常用于描述一组资料在某百分位置上的水平和分布特征。多个百分位数结合使用可更全面地描述总体或样本的分布特征。
百分位数以P x表示。一个P x将全部观察值分为两部分，理论上有x%的观察值比它小，有（100-x）%的观察值比它大。
P50是一个特定的百分位数，也称中位数（median），特用M示之。常用于描述偏态分布资料的集中趋势，它是居中位置的变量值的大小。它不受个别特大或特小观察值的影响。
将所有观察值X i录入到Excel工作表“频数分布图”的B2至B1000后，在单元格O2至O8内输入百分位数界值0.01、0.025、0.05、0.5、0.95、0.975和0.99。录入公式：
P2=PERCENTILE($B$2:$B$1000,O2) P2↓P8
则P列得到相应O列数值的百分位数（见图2.4）。
例2.1中第1%、2.5%、5%、50%、95%、97.5%、以及第99%百分位数分别是5.5、6.6、7.7、12.0、19.0、21.0、23.0。