Excel與數(shù)據(jù)分析（第3版）

數(shù)據(jù)整理篇
第1 章 Excel 基本知識...................................................................................2
1.1 Excel 的安裝.............................................................................3
1.2 Excel 工作界面簡介................................................................3
1.3 鼠標與鍵盤的操作...................................................................................9
1.4 單元格區(qū)域與整個工作表的選取.............................................................12
1.5 工作表重命名與復制副本..............................................................................................13
1.6 工作表的格式化.............................................................................14
1.7 工作表的打印................................................................................................19
第2 章 公式與函數(shù)..................................................................................21
2.1 公式的建立...................................................................................................22
2.2 函數(shù)的結構...............................................................................23
2.3 公式中的運算符...................................................................................24
2.4 運算符的優(yōu)先順序....................................................................................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 常用數(shù)學函數(shù)應用舉例...............................................................34
2.9.1 常用數(shù)學函數(shù)......................................................................................................35
2.9.2 數(shù)組運算...............................................................................................................39
2.9.3 矩陣的運算與求解線性方程組.........................................................................40
2.10 創(chuàng)建自定義函數(shù)..............................................................................43
2.11 獲取函數(shù)幫助信息...............................................................................44
第3 章 數(shù)據(jù)清單的建立與利用.......................................................................45
3.1 數(shù)字的單元格格式.....................................................................46
3.2 數(shù)據(jù)的有效性設置...................................................................50
3.3 建立數(shù)據(jù)清單.................................................................................54
3.4 數(shù)據(jù)“記錄單”的作用...................................................................................................56
3.5 數(shù)據(jù)的自動填充.................................................................................57
3.5.1 簡單數(shù)據(jù)的自動填充..........................................................................................57
3.5.2 等比數(shù)列數(shù)據(jù)的填充..........................................................................................59
3.5.3 公式的自動填充..................................................................................................60
3.5.4 智能標記的應用..................................................................................................60
3.6 數(shù)據(jù)的分列..................................................................................61
3.7 添加與編輯批注..........................................................................63
3.8 特殊數(shù)學符號的鍵入....................................................................64
3.9 數(shù)據(jù)的導入與導出..................................................................66
第4 章 數(shù)據(jù)的編輯與查詢.................................................................................68
4.1 窗口的凍結與拆分...........................................................................69
4.2 數(shù)據(jù)的剪貼與刪除...........................................................................70
4.3 數(shù)據(jù)的查找與替換.....................................................................71
4.4 行列轉置與選擇性粘貼的其他妙用..............................................................................72
4.5 將連續(xù)型變量劃分等級.....................................................................75
4.6 年齡的計算與當前日期的生成......................................................................................76
4.7 數(shù)據(jù)的跨工作表操作..............................................................................76
4.8 多個工作表的合并計算...............................................................................78
4.9 查詢與新建子數(shù)據(jù)清單..........................................................................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 數(shù)據(jù)透視表.......................................................................................94
5.2.1 創(chuàng)建數(shù)據(jù)透視表..................................................................................................95
5.2.2 數(shù)據(jù)透視表的結構及其工具欄.........................................................................96
5.2.3 由數(shù)據(jù)透視表獲得均數(shù)標準差.........................................................................98
5.2.4 產(chǎn)生列聯(lián)表........................................................................................................100
5.2.5 創(chuàng)建子數(shù)據(jù)清單................................................................................................106
5.2.6 設置報告格式與更新數(shù)據(jù)...............................................................................106
5.2.7 在數(shù)據(jù)透視圖表中創(chuàng)建公式...........................................................................107
5.3 數(shù)據(jù)透視圖........................................................................................108
5.3.1 創(chuàng)建數(shù)據(jù)透視圖................................................................................................109
5.3.2 調整數(shù)據(jù)透視圖................................................................................................109
5.3.3 將數(shù)據(jù)透視圖更改為靜態(tài)圖........................................................................... 111
5.4 數(shù)據(jù)透視的其他應用................................................................................... 113
5.4.1 多個工作表的數(shù)據(jù)透視.................................................................................... 113
5.4.2 數(shù)據(jù)透視表網(wǎng)頁................................................................................................ 113
統(tǒng)計分析篇
第6 章 統(tǒng)計圖表.................................................................................................... 116
6.1 統(tǒng)計表的制作................................................................................................. 117
6.2 統(tǒng)計圖的結構及特點......................................................................................... 118
6.3 Excel 標準圖表類型簡介................................................................................. 119
6.4 創(chuàng)建統(tǒng)計圖的一般步驟.............................................................121
6.5 統(tǒng)計圖的編輯與修飾..........................................................................124
6.6 繪制統(tǒng)計圖舉例...............................................................................127
6.6.1 分段條圖.............................................................................................................127
6.6.2 誤差條圖.............................................................................................................129
6.6.3 百分條圖.............................................................................................................131
6.6.4 餅圖.....................................................................................................................132
6.6.5 復合條餅圖........................................................................................................133
6.6.6 線圖與半對數(shù)線圖............................................................................................135
6.6.7 雷達圖.................................................................................................................136
6.6.8 氣泡圖.................................................................................................................138
6.6.9 箱圖.....................................................................................................................140
6.6.10 人口金字塔圖..................................................................................................143
6.6.11 三角函數(shù)制作線圖..........................................................................................146
6.6.12 曲面圖......................................................................................................147
第7 章 統(tǒng)計描述.....................................................................................................149
7.1 數(shù)據(jù)分析工具的調用.............................................................................150
7.2 統(tǒng)計學中的數(shù)據(jù)類型......................................................................151
7.2.1 數(shù)據(jù)類型的辨析................................................................................................152
7.2.2 三類數(shù)據(jù)的計算................................................................................................153
7.2.3 三類數(shù)據(jù)的轉化................................................................................................153
7.3 區(qū)間數(shù)據(jù)的統(tǒng)計學描述.............................................................154
7.3.1 區(qū)間數(shù)據(jù)的頻數(shù)表與直方圖...........................................................................154
7.3.2 集中位置指標....................................................................................................160
7.3.3 離散程度指標....................................................................................................163
7.3.4 數(shù)據(jù)分析工具：描述統(tǒng)計...............................................................................169
7.4 名義數(shù)據(jù)的統(tǒng)計描述.........................................................................170
7.4.1 COUNTIF 函數(shù)清點計數(shù).................................................................................171
7.4.2 率的標準化法....................................................................................................171
7.4.3 動態(tài)數(shù)列.............................................................................................................173
第8 章 區(qū)間數(shù)據(jù)的假設檢驗..................................................................................176
8.1 假設檢驗的一般步驟.........................................................................177
8.2 樣本均數(shù)與總體均數(shù)的比較........................................................................................178
8.2.1 已知樣本均數(shù)和標準差....................................................................................178
8.2.2 已知樣本原始數(shù)據(jù)............................................................................................179
8.3 配對資料的比較.......................................................................................180
8.4 兩個樣本均數(shù)的比較...............................................................................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 無原始數(shù)據(jù)的單因素方差分析.......................................................................191
8.6.4 單因素方差分析的多重比較...........................................................................191
8.7 雙因素方差分析...................................................................................193
8.7.1 無重復雙因素方差分析....................................................................................193
8.7.2 有重復雙因素方差分析....................................................................................195
第9 章 名義與有序數(shù)據(jù)的假設檢驗...................................................................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 頻數(shù)分布的擬合優(yōu)度檢驗............................................................................................210
9.2.1 正態(tài)分布.............................................................................................................210
9.2.2 二項分布.............................................................................................................213
9.2.3 Poisson 分布.......................................................................................................215
9.2.4 均勻分布.............................................................................................................216
9.3 非參數(shù)檢驗............................................................................................217
9.3.1 配對資料Wilcoxon 符號秩檢驗.....................................................................218
9.3.2 兩獨立樣本非參數(shù)檢驗....................................................................................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 函數(shù)實現(xiàn)簡單相關與回歸...................................................................238
10.1.2 數(shù)據(jù)分析工具實現(xiàn)簡單相關與回歸.............................................................243
10.1.3 添加趨勢線實現(xiàn)直線回歸分析.....................................................................247
10.2 曲線回歸...........................................................................................248
10.2.1 對數(shù)曲線擬合..................................................................................................249
10.2.2 乘冪曲線擬合..................................................................................................251
10.2.3 指數(shù)曲線擬合..................................................................................................252
10.2.4 多項式曲線擬合..............................................................................................253
10.3 非線性回歸.........................................................................................255
10.3.1 對數(shù)曲線擬合..................................................................................................255
10.3.2 乘冪曲線擬合..................................................................................................256
10.3.3 指數(shù)曲線擬合..................................................................................................257
10.3.4 多項式曲線擬合..............................................................................................257
10.4 多重相關與回歸.........................................................................................258
10.4.1 多重相關與多重協(xié)方差..................................................................................258
10.4.2 多重回歸分析..................................................................................................260
10.4.3 用函數(shù)實現(xiàn)多重回歸與相關.........................................................................261
10.5 加權l(xiāng)ogistic 回歸................................................................................263
高級應用篇
第11 章 變量求解與方案優(yōu)選....................................................................................268
11.1 單變量求解.....................................................................................269
11.1.1 目標搜索...........................................................................................................269
11.1.2 求解非線性方程..............................................................................................270
11.2 規(guī)劃求解..............................................................................................271
11.3 方案管理器............................................................................................274
11.4 常見的方案優(yōu)選方法...............................................................................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 統(tǒng)計量.......................................................................................................290
12.1.3 自相關...............................................................................................................291
12.1.4 自回歸...............................................................................................................292
12.2 移動平均法......................................................................................294
12.3 指數(shù)平滑法................................................................................295
12.3.1 數(shù)據(jù)分析工具——指數(shù)平滑..........................................................................296
12.3.2 指數(shù)平滑的初始值..........................................................................................297
12.3.3 求解最佳平滑常數(shù)..........................................................................................298
12.4 季節(jié)預測法......................................................................................299
12.4.1 啞變量多重回歸..............................................................................................300
12.4.2 AR（4）模型...................................................................................................303
12.4.3 季節(jié)指數(shù)法......................................................................................................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 現(xiàn)時壽命表........................................................................................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 置信區(qū)間的樣本含量估計.............................................................................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 章 高級科研統(tǒng)計圖與Meta 分析.....................................................................369
15.1 統(tǒng)計地圖.............................................................................................370
15.2 P-P 圖與Q-Q 圖..................................................................................373
15.2.1 創(chuàng)建繪圖數(shù)據(jù)..................................................................................................373
15.2.2 繪制正態(tài)P-P 圖..............................................................................................374
15.2.3 繪制正態(tài)Q-Q 圖.............................................................................................375
15.3 生存曲線..................................................................................................375
15.3.1 Kaplan-Meier 法計算生存率及其標準誤.....................................................376
15.3.2 繪制生存曲線..................................................................................................377
15.4 重復測量數(shù)據(jù)的曲線圖...................................................................378
15.5 Meta 分析統(tǒng)計量的置信區(qū)間圖.............................................................379
15.5.1 Meta 分析的計算公式....................................................................................380
15.5.2 連續(xù)資料的Meta 分析...................................................................................383
15.5.3 離散資料的Meta 分析...................................................................................387
15.5.4 相關系數(shù)資料的Meta 分析...........................................................................391
15.5.5 P 值的Meta 分析............................................................................................392
第16 章 診斷試驗評價.........................................................................................394
16.1 常用統(tǒng)計指標...................................................................................395
16.2 ROC 曲線原理及其繪制方法.....................................................................................399
16.2.1 有序分類資料ROC 分析...............................................................................400
16.2.2 連續(xù)資料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 靈敏度與特異度的置信區(qū)間估計.................................................................415
16.4.2 ROC 曲線下面積的置信區(qū)間估計................................................................416
16.4.3 固定FPR 的靈敏度置信區(qū)間估計................................................................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 正態(tài)分布............................................................................................429
17.1.1 采用公式制作正態(tài)分布電腦實驗.................................................................429
17.1.2 正態(tài)曲線下的面積..........................................................................................431
17.1.3 正態(tài)分布綜合電腦實驗.................................................................................433
17.2 二項分布......................................................................................435
17.2.1 概率質量函數(shù)..................................................................................................435
17.2.2 累計分布函數(shù)..................................................................................................436
17.2.3 二項分布電腦實驗..........................................................................................436
17.2.4 樣本率及率的標準誤.....................................................................................438
17.3 Poisson 分布.................................................................................438
17.3.1 概率質量函數(shù)與累計分布函數(shù).....................................................................439
17.3.2 Poisson 分布電腦實驗...................................................................................439
17.4 其他連續(xù)分布.......................................................................................440
17.4.1 對數(shù)正態(tài)分布..................................................................................................441
17.4.2 均勻分布...........................................................................................................441
17.4.3 指數(shù)分布...........................................................................................................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 隨機數(shù)發(fā)生器與隨機函數(shù)..........................................................................................449
18.1.1 隨機數(shù)發(fā)生器..................................................................................................449
18.1.2 隨機數(shù)函數(shù)......................................................................................................454
18.2 有放回抽樣與無歸還抽樣..........................................................................................455
18.2.1 “抽樣”分析工具............................................................................................455
18.2.2 無歸還抽樣......................................................................................................456
18.3 隨機抽樣電腦實驗............................................................................456
18.3.1 從正態(tài)總體中隨機抽樣..................................................................................457
18.3.2 從非正態(tài)總體中隨機抽樣.............................................................................461
第19 章 假設檢驗的概率分布與統(tǒng)計用表...............................................................466
19.1 z 分布.......................................................................................................467
19.1.1 標準正態(tài)分布曲線下面積.............................................................................467
19.1.2 標準正態(tài)分布分位數(shù)......................................................................................469
19.1.3 標準正態(tài)分布的其他應用.............................................................................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 章 其他電腦實驗與置信區(qū)間估計.........................................................489
20.1 隨機現(xiàn)象.....................................................................................................490
20.2 置信區(qū)間......................................................................................................491
20.2.1 置信區(qū)間的電腦實驗......................................................................................492
20.2.2 置信區(qū)間的估計..............................................................................................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 回歸參數(shù)意義理解...........................................................................508
20.11 壽命表電腦實驗.....................................................................................509
附錄A Excel 函數(shù).........................................................................................................510
附錄B 概率基本知識.................................................................................................536
附錄C Excel 2010 宏............................................................................................538
附錄D Excel 2010 的數(shù)據(jù)分析新功能....................................................................546
附錄E Excel 2010 VBA 應用..................................................................................551
參考文獻...................................................................................................................554