Excel与数据分析（第3版）

数据整理篇
第1 章 Excel 基本知识...................................................................................2
1.1 Excel 的安装.............................................................................3
1.2 Excel 工作界面简介................................................................3
1.3 鼠标与键盘的操作...................................................................................9
1.4 单元格区域与整个工作表的选取.............................................................12
1.5 工作表重命名与复制副本..............................................................................................13
1.6 工作表的格式化.............................................................................14
1.7 工作表的打印................................................................................................19
第2 章 公式与函数..................................................................................21
2.1 公式的建立...................................................................................................22
2.2 函数的结构...............................................................................23
2.3 公式中的运算符...................................................................................24
2.4 运算符的优先顺序....................................................................................26
2.5 单元格的混合引用...............................................................................26
2.5.1 相对引用...............................................................................................................27
2.5.2 绝对引用...............................................................................................................27
2.5.3 混合引用...............................................................................................................27
2.5.4 A1 引用样式........................................................................................................28
2.5.5 应用实例...............................................................................................................28
2.6 编辑公式常用的快捷键....................................................................30
2.7 编辑公式时常见的错误信息..........................................................................................30
2.8 公式保护与撤销保护的技巧..........................................................................................33
2.9 常用数学函数应用举例...............................................................34
2.9.1 常用数学函数......................................................................................................35
2.9.2 数组运算...............................................................................................................39
2.9.3 矩阵的运算与求解线性方程组.........................................................................40
2.10 创建自定义函数..............................................................................43
2.11 获取函数帮助信息...............................................................................44
第3 章 数据清单的建立与利用.......................................................................45
3.1 数字的单元格格式.....................................................................46
3.2 数据的有效性设置...................................................................50
3.3 建立数据清单.................................................................................54
3.4 数据“记录单”的作用...................................................................................................56
3.5 数据的自动填充.................................................................................57
3.5.1 简单数据的自动填充..........................................................................................57
3.5.2 等比数列数据的填充..........................................................................................59
3.5.3 公式的自动填充..................................................................................................60
3.5.4 智能标记的应用..................................................................................................60
3.6 数据的分列..................................................................................61
3.7 添加与编辑批注..........................................................................63
3.8 特殊数学符号的键入....................................................................64
3.9 数据的导入与导出..................................................................66
第4 章 数据的编辑与查询.................................................................................68
4.1 窗口的冻结与拆分...........................................................................69
4.2 数据的剪贴与删除...........................................................................70
4.3 数据的查找与替换.....................................................................71
4.4 行列转置与选择性粘贴的其他妙用..............................................................................72
4.5 将连续型变量划分等级.....................................................................75
4.6 年龄的计算与当前日期的生成......................................................................................76
4.7 数据的跨工作表操作..............................................................................76
4.8 多个工作表的合并计算...............................................................................78
4.9 查询与新建子数据清单..........................................................................81
4.9.1 排序.......................................................................................................................81
4.9.2 自动筛选...............................................................................................................82
4.9.3 高级筛选...............................................................................................................85
4.9.4 选择不重复的记录..............................................................................................86
4.9.5 人工筛选...............................................................................................................86
4.10 两个类似文件的比较....................................................................88
第5 章 分类汇总与透视分析.................................................................................90
5.1 分类汇总........................................................................................91
5.2 数据透视表.......................................................................................94
5.2.1 创建数据透视表..................................................................................................95
5.2.2 数据透视表的结构及其工具栏.........................................................................96
5.2.3 由数据透视表获得均数标准差.........................................................................98
5.2.4 产生列联表........................................................................................................100
5.2.5 创建子数据清单................................................................................................106
5.2.6 设置报告格式与更新数据...............................................................................106
5.2.7 在数据透视图表中创建公式...........................................................................107
5.3 数据透视图........................................................................................108
5.3.1 创建数据透视图................................................................................................109
5.3.2 调整数据透视图................................................................................................109
5.3.3 将数据透视图更改为静态图........................................................................... 111
5.4 数据透视的其他应用................................................................................... 113
5.4.1 多个工作表的数据透视.................................................................................... 113
5.4.2 数据透视表网页................................................................................................ 113
统计分析篇
第6 章 统计图表.................................................................................................... 116
6.1 统计表的制作................................................................................................. 117
6.2 统计图的结构及特点......................................................................................... 118
6.3 Excel 标准图表类型简介................................................................................. 119
6.4 创建统计图的一般步骤.............................................................121
6.5 统计图的编辑与修饰..........................................................................124
6.6 绘制统计图举例...............................................................................127
6.6.1 分段条图.............................................................................................................127
6.6.2 误差条图.............................................................................................................129
6.6.3 百分条图.............................................................................................................131
6.6.4 饼图.....................................................................................................................132
6.6.5 复合条饼图........................................................................................................133
6.6.6 线图与半对数线图............................................................................................135
6.6.7 雷达图.................................................................................................................136
6.6.8 气泡图.................................................................................................................138
6.6.9 箱图.....................................................................................................................140
6.6.10 人口金字塔图..................................................................................................143
6.6.11 三角函数制作线图..........................................................................................146
6.6.12 曲面图......................................................................................................147
第7 章 统计描述.....................................................................................................149
7.1 数据分析工具的调用.............................................................................150
7.2 统计学中的数据类型......................................................................151
7.2.1 数据类型的辨析................................................................................................152
7.2.2 三类数据的计算................................................................................................153
7.2.3 三类数据的转化................................................................................................153
7.3 区间数据的统计学描述.............................................................154
7.3.1 区间数据的频数表与直方图...........................................................................154
7.3.2 集中位置指标....................................................................................................160
7.3.3 离散程度指标....................................................................................................163
7.3.4 数据分析工具：描述统计...............................................................................169
7.4 名义数据的统计描述.........................................................................170
7.4.1 COUNTIF 函数清点计数.................................................................................171
7.4.2 率的标准化法....................................................................................................171
7.4.3 动态数列.............................................................................................................173
第8 章 区间数据的假设检验..................................................................................176
8.1 假设检验的一般步骤.........................................................................177
8.2 样本均数与总体均数的比较........................................................................................178
8.2.1 已知样本均数和标准差....................................................................................178
8.2.2 已知样本原始数据............................................................................................179
8.3 配对资料的比较.......................................................................................180
8.4 两个样本均数的比较...............................................................................181
8.4.1 两样本的总体方差齐性检验...........................................................................181
8.4.2 总体方差齐同的t 检验.....................................................................................183
8.4.3 总体方差不齐的t 检验.....................................................................................185
8.4.4 大样本的z 检验.................................................................................................186
8.5 多样本方差Bartlett 齐性检验........................................................187
8.6 单因素方差分析...........................................................................................188
8.6.1 单因素方差分析工具........................................................................................188
8.6.2 单因素方差分析图示........................................................................................190
8.6.3 无原始数据的单因素方差分析.......................................................................191
8.6.4 单因素方差分析的多重比较...........................................................................191
8.7 双因素方差分析...................................................................................193
8.7.1 无重复双因素方差分析....................................................................................193
8.7.2 有重复双因素方差分析....................................................................................195
第9 章 名义与有序数据的假设检验...................................................................198
9.1 卡方检验........................................................................................................199
9.1.1 四格表资料的χ2 检验........................................................................................199
9.1.2 四格表配对资料的χ2 检验...............................................................................203
9.1.3 四格表资料的Fisher 精确概率检验...............................................................205
9.1.4 行×列表资料的χ2 检验.....................................................................................208
9.2 频数分布的拟合优度检验............................................................................................210
9.2.1 正态分布.............................................................................................................210
9.2.2 二项分布.............................................................................................................213
9.2.3 Poisson 分布.......................................................................................................215
9.2.4 均匀分布.............................................................................................................216
9.3 非参数检验............................................................................................217
9.3.1 配对资料Wilcoxon 符号秩检验.....................................................................218
9.3.2 两独立样本非参数检验....................................................................................221
9.3.3 多样本Kruskal-Wallis 秩和检验.....................................................................225
9.3.4 多个相关样本的Friedman 检验......................................................................228
9.4 Ridit 分析....................................................................................230
第10 章 相关与回归...............................................................................................235
10.1 简单相关与回归..........................................................................236
10.1.1 Excel 函数实现简单相关与回归...................................................................238
10.1.2 数据分析工具实现简单相关与回归.............................................................243
10.1.3 添加趋势线实现直线回归分析.....................................................................247
10.2 曲线回归...........................................................................................248
10.2.1 对数曲线拟合..................................................................................................249
10.2.2 乘幂曲线拟合..................................................................................................251
10.2.3 指数曲线拟合..................................................................................................252
10.2.4 多项式曲线拟合..............................................................................................253
10.3 非线性回归.........................................................................................255
10.3.1 对数曲线拟合..................................................................................................255
10.3.2 乘幂曲线拟合..................................................................................................256
10.3.3 指数曲线拟合..................................................................................................257
10.3.4 多项式曲线拟合..............................................................................................257
10.4 多重相关与回归.........................................................................................258
10.4.1 多重相关与多重协方差..................................................................................258
10.4.2 多重回归分析..................................................................................................260
10.4.3 用函数实现多重回归与相关.........................................................................261
10.5 加权logistic 回归................................................................................263
高级应用篇
第11 章 变量求解与方案优选....................................................................................268
11.1 单变量求解.....................................................................................269
11.1.1 目标搜索...........................................................................................................269
11.1.2 求解非线性方程..............................................................................................270
11.2 规划求解..............................................................................................271
11.3 方案管理器............................................................................................274
11.4 常见的方案优选方法...............................................................................277
11.4.1 确定型决策.......................................................................................................277
11.4.2 非确定型决策..................................................................................................278
11.4.3 风险型决策.......................................................................................................281
11.5 综合评价方法...................................................................................282
11.5.1 TOPSIS 法........................................................................................................282
11.5.2 层次分析法.......................................................................................................285
第12 章 预测分析...............................................................................................288
12.1 自相关与自回归............................................................................289
12.1.1 线性时序趋势..................................................................................................289
12.1.2 DW 统计量.......................................................................................................290
12.1.3 自相关...............................................................................................................291
12.1.4 自回归...............................................................................................................292
12.2 移动平均法......................................................................................294
12.3 指数平滑法................................................................................295
12.3.1 数据分析工具——指数平滑..........................................................................296
12.3.2 指数平滑的初始值..........................................................................................297
12.3.3 求解最佳平滑常数..........................................................................................298
12.4 季节预测法......................................................................................299
12.4.1 哑变量多重回归..............................................................................................300
12.4.2 AR（4）模型...................................................................................................303
12.4.3 季节指数法......................................................................................................304
12.5 Markov 预测法.................................................................................................306
12.6 灰色预测模型..................................................310
12.6.1 GM(1,1)模型的预测与评价方法...................................................................310
12.6.2 灰色模型预测实例..........................................................................................312
12.7 傅利叶分析................................................................................................314
12.8 圆形分布分析............................................................................................316
12.8.1 角度变换...........................................................................................................316
12.8.2 平均角及其假设检验......................................................................................317
12.8.3 圆形分布分析实例..........................................................................................318
12.8.4 两个或多个样本平均角的比较.....................................................................320
第13 章 寿命表............................................................................................324
13.1 现时寿命表........................................................................................325
13.2 去死因寿命表..................................................................................330
13.3 健康期望寿命表..............................................................................334
第14 章 随机化分组与样本含量估计.......................................................337
14.1 随机化分组.......................................................................................338
14.1.1 完全随机设计..................................................................................................338
14.1.2 配对设计...........................................................................................................339
14.1.3 配伍组设计......................................................................................................340
14.1.4 三个随机化分组程序介绍.............................................................................342
14.2 样本含量的估计.......................................................................343
14.2.1 Nomogram 图示法...........................................................................................345
14.2.2 假设检验的样本含量估计.............................................................................346
14.2.3 置信区间的样本含量估计.............................................................................353
14.3 检验功效的估计..........................................................................................357
14.4 流行病学研究的样本含量与检验功效......................................................................362
14.4.1 病例对照研究..................................................................................................362
14.4.2 队列研究...........................................................................................................365
14.5 方差分析等方法的样本含量与检验功效.................................................................366
14.5.1 方差分析的检验功效......................................................................................366
14.5.2 方差分析的样本含量......................................................................................367
14.5.3 其他方法的样本含量与检验功效.................................................................368
第15 章 高级科研统计图与Meta 分析.....................................................................369
15.1 统计地图.............................................................................................370
15.2 P-P 图与Q-Q 图..................................................................................373
15.2.1 创建绘图数据..................................................................................................373
15.2.2 绘制正态P-P 图..............................................................................................374
15.2.3 绘制正态Q-Q 图.............................................................................................375
15.3 生存曲线..................................................................................................375
15.3.1 Kaplan-Meier 法计算生存率及其标准误.....................................................376
15.3.2 绘制生存曲线..................................................................................................377
15.4 重复测量数据的曲线图...................................................................378
15.5 Meta 分析统计量的置信区间图.............................................................379
15.5.1 Meta 分析的计算公式....................................................................................380
15.5.2 连续资料的Meta 分析...................................................................................383
15.5.3 离散资料的Meta 分析...................................................................................387
15.5.4 相关系数资料的Meta 分析...........................................................................391
15.5.5 P 值的Meta 分析............................................................................................392
第16 章 诊断试验评价.........................................................................................394
16.1 常用统计指标...................................................................................395
16.2 ROC 曲线原理及其绘制方法.....................................................................................399
16.2.1 有序分类资料ROC 分析...............................................................................400
16.2.2 连续资料ROC 分析........................................................................................403
16.2.3 值小为阳性的ROC 分析...............................................................................404
16.3 诊断试验评价的Meta 分析........................................................................406
16.3.1 SROC 分析.......................................................................................................406
16.3.2 ROC 面积的Meta 分析..................................................................................412
16.4 样本含量估计...........................................................................................415
16.4.1 灵敏度与特异度的置信区间估计.................................................................415
16.4.2 ROC 曲线下面积的置信区间估计................................................................416
16.4.3 固定FPR 的灵敏度置信区间估计................................................................417
16.4.4 单个准确度指标的假设检验.........................................................................418
16.4.5 两灵敏度或特异度的比较.............................................................................420
16.4.6 ROC 曲线下面积的比较................................................................................421
16.5 Kappa 值估计...................................................................................422
16.6 Bland-Altman 法.........................................................................425
电脑实验篇
第17 章 概率分布............................................................................................428
17.1 正态分布............................................................................................429
17.1.1 采用公式制作正态分布电脑实验.................................................................429
17.1.2 正态曲线下的面积..........................................................................................431
17.1.3 正态分布综合电脑实验.................................................................................433
17.2 二项分布......................................................................................435
17.2.1 概率质量函数..................................................................................................435
17.2.2 累计分布函数..................................................................................................436
17.2.3 二项分布电脑实验..........................................................................................436
17.2.4 样本率及率的标准误.....................................................................................438
17.3 Poisson 分布.................................................................................438
17.3.1 概率质量函数与累计分布函数.....................................................................439
17.3.2 Poisson 分布电脑实验...................................................................................439
17.4 其他连续分布.......................................................................................440
17.4.1 对数正态分布..................................................................................................441
17.4.2 均匀分布...........................................................................................................441
17.4.3 指数分布...........................................................................................................442
17.4.4 Weibull 分布.....................................................................................................442
17.4.5 Beta 分布..........................................................................................................444
17.4.6 Gamma 分布.....................................................................................................444
17.5 其他离散分布............................................................................................445
17.5.1 均匀分布...........................................................................................................445
17.5.2 超几何分布......................................................................................................446
第18 章 随机抽样与抽样误差.............................................................................448
18.1 随机数发生器与随机函数..........................................................................................449
18.1.1 随机数发生器..................................................................................................449
18.1.2 随机数函数......................................................................................................454
18.2 有放回抽样与无归还抽样..........................................................................................455
18.2.1 “抽样”分析工具............................................................................................455
18.2.2 无归还抽样......................................................................................................456
18.3 随机抽样电脑实验............................................................................456
18.3.1 从正态总体中随机抽样..................................................................................457
18.3.2 从非正态总体中随机抽样.............................................................................461
第19 章 假设检验的概率分布与统计用表...............................................................466
19.1 z 分布.......................................................................................................467
19.1.1 标准正态分布曲线下面积.............................................................................467
19.1.2 标准正态分布分位数......................................................................................469
19.1.3 标准正态分布的其他应用.............................................................................469
19.1.4 z 分布电脑实验................................................................................................472
19.2 t 分布.............................................................................................473
19.2.1 t 分布的图形及其分布特征...........................................................................474
19.2.2 t 分布曲线下面积与检验界值.......................................................................476
19.2.3 t 分布电脑实验................................................................................................477
19.3 F 分布...............................................................................................479
19.3.1 F 分布的图形及其分布特征..........................................................................480
19.3.2 F 分布曲线下面积与检验界值......................................................................481
19.3.3 F 分布电脑实验..............................................................................................483
19.4 χ2 分布...........................................................................................484
19.4.1 χ2 分布的图形及其分布特征.........................................................................484
19.4.2 χ2 分布曲线下面积与检验界值.....................................................................486
19.4.3 χ2 分布电脑实验..............................................................................................486
第20 章 其他电脑实验与置信区间估计.........................................................489
20.1 随机现象.....................................................................................................490
20.2 置信区间......................................................................................................491
20.2.1 置信区间的电脑实验......................................................................................492
20.2.2 置信区间的估计..............................................................................................493
20.3 假设检验的两型错误..............................................................................498
20.4 不同检验方法所导致结论的差异......................................................................499
20.5 t 检验的两型错误................................................................................501
20.6 t 与t'检验.................................................................................502
20.7 一般t 检验做方差分析两两比较导致的I 型错误..................................................503
20.8 样本含量对假设检验的影响......................................................................................504
20.9 卡方检验公式校正的意义....................................................................................506
20.10 回归参数意义理解...........................................................................508
20.11 寿命表电脑实验.....................................................................................509
附录A Excel 函数.........................................................................................................510
附录B 概率基本知识.................................................................................................536
附录C Excel 2010 宏............................................................................................538
附录D Excel 2010 的数据分析新功能....................................................................546
附录E Excel 2010 VBA 应用..................................................................................551
参考文献...................................................................................................................554