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

數(shù)據(jù)整理篇
第1 章 Excel 基本知識(shí)...................................................................................2
1.1 Excel 的安裝.............................................................................3
1.2 Excel 工作界面簡(jiǎn)介................................................................3
1.3 鼠標(biāo)與鍵盤(pán)的操作...................................................................................9
1.4 單元格區(qū)域與整個(gè)工作表的選取.............................................................12
1.5 工作表重命名與復(fù)制副本..............................................................................................13
1.6 工作表的格式化.............................................................................14
1.7 工作表的打印................................................................................................19
第2 章 公式與函數(shù)..................................................................................21
2.1 公式的建立...................................................................................................22
2.2 函數(shù)的結(jié)構(gòu)...............................................................................23
2.3 公式中的運(yùn)算符...................................................................................24
2.4 運(yùn)算符的優(yōu)先順序....................................................................................26
2.5 單元格的混合引用...............................................................................26
2.5.1 相對(duì)引用...............................................................................................................27
2.5.2 絕對(duì)引用...............................................................................................................27
2.5.3 混合引用...............................................................................................................27
2.5.4 A1 引用樣式........................................................................................................28
2.5.5 應(yīng)用實(shí)例...............................................................................................................28
2.6 編輯公式常用的快捷鍵....................................................................30
2.7 編輯公式時(shí)常見(jiàn)的錯(cuò)誤信息..........................................................................................30
2.8 公式保護(hù)與撤銷保護(hù)的技巧..........................................................................................33
2.9 常用數(shù)學(xué)函數(shù)應(yīng)用舉例...............................................................34
2.9.1 常用數(shù)學(xué)函數(shù)......................................................................................................35
2.9.2 數(shù)組運(yùn)算...............................................................................................................39
2.9.3 矩陣的運(yùn)算與求解線性方程組.........................................................................40
2.10 創(chuàng)建自定義函數(shù)..............................................................................43
2.11 獲取函數(shù)幫助信息...............................................................................44
第3 章 數(shù)據(jù)清單的建立與利用.......................................................................45
3.1 數(shù)字的單元格格式.....................................................................46
3.2 數(shù)據(jù)的有效性設(shè)置...................................................................50
3.3 建立數(shù)據(jù)清單.................................................................................54
3.4 數(shù)據(jù)“記錄單”的作用...................................................................................................56
3.5 數(shù)據(jù)的自動(dòng)填充.................................................................................57
3.5.1 簡(jiǎn)單數(shù)據(jù)的自動(dòng)填充..........................................................................................57
3.5.2 等比數(shù)列數(shù)據(jù)的填充..........................................................................................59
3.5.3 公式的自動(dòng)填充..................................................................................................60
3.5.4 智能標(biāo)記的應(yīng)用..................................................................................................60
3.6 數(shù)據(jù)的分列..................................................................................61
3.7 添加與編輯批注..........................................................................63
3.8 特殊數(shù)學(xué)符號(hào)的鍵入....................................................................64
3.9 數(shù)據(jù)的導(dǎo)入與導(dǎo)出..................................................................66
第4 章 數(shù)據(jù)的編輯與查詢.................................................................................68
4.1 窗口的凍結(jié)與拆分...........................................................................69
4.2 數(shù)據(jù)的剪貼與刪除...........................................................................70
4.3 數(shù)據(jù)的查找與替換.....................................................................71
4.4 行列轉(zhuǎn)置與選擇性粘貼的其他妙用..............................................................................72
4.5 將連續(xù)型變量劃分等級(jí).....................................................................75
4.6 年齡的計(jì)算與當(dāng)前日期的生成......................................................................................76
4.7 數(shù)據(jù)的跨工作表操作..............................................................................76
4.8 多個(gè)工作表的合并計(jì)算...............................................................................78
4.9 查詢與新建子數(shù)據(jù)清單..........................................................................81
4.9.1 排序.......................................................................................................................81
4.9.2 自動(dòng)篩選...............................................................................................................82
4.9.3 高級(jí)篩選...............................................................................................................85
4.9.4 選擇不重復(fù)的記錄..............................................................................................86
4.9.5 人工篩選...............................................................................................................86
4.10 兩個(gè)類似文件的比較....................................................................88
第5 章 分類匯總與透視分析.................................................................................90
5.1 分類匯總........................................................................................91
5.2 數(shù)據(jù)透視表.......................................................................................94
5.2.1 創(chuàng)建數(shù)據(jù)透視表..................................................................................................95
5.2.2 數(shù)據(jù)透視表的結(jié)構(gòu)及其工具欄.........................................................................96
5.2.3 由數(shù)據(jù)透視表獲得均數(shù)標(biāo)準(zhǔn)差.........................................................................98
5.2.4 產(chǎn)生列聯(lián)表........................................................................................................100
5.2.5 創(chuàng)建子數(shù)據(jù)清單................................................................................................106
5.2.6 設(shè)置報(bào)告格式與更新數(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 調(diào)整數(shù)據(jù)透視圖................................................................................................109
5.3.3 將數(shù)據(jù)透視圖更改為靜態(tài)圖........................................................................... 111
5.4 數(shù)據(jù)透視的其他應(yīng)用................................................................................... 113
5.4.1 多個(gè)工作表的數(shù)據(jù)透視.................................................................................... 113
5.4.2 數(shù)據(jù)透視表網(wǎng)頁(yè)................................................................................................ 113
統(tǒng)計(jì)分析篇
第6 章 統(tǒng)計(jì)圖表.................................................................................................... 116
6.1 統(tǒng)計(jì)表的制作................................................................................................. 117
6.2 統(tǒng)計(jì)圖的結(jié)構(gòu)及特點(diǎn)......................................................................................... 118
6.3 Excel 標(biāo)準(zhǔn)圖表類型簡(jiǎn)介................................................................................. 119
6.4 創(chuàng)建統(tǒng)計(jì)圖的一般步驟.............................................................121
6.5 統(tǒng)計(jì)圖的編輯與修飾..........................................................................124
6.6 繪制統(tǒng)計(jì)圖舉例...............................................................................127
6.6.1 分段條圖.............................................................................................................127
6.6.2 誤差條圖.............................................................................................................129
6.6.3 百分條圖.............................................................................................................131
6.6.4 餅圖.....................................................................................................................132
6.6.5 復(fù)合條餅圖........................................................................................................133
6.6.6 線圖與半對(duì)數(shù)線圖............................................................................................135
6.6.7 雷達(dá)圖.................................................................................................................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)計(jì)描述.....................................................................................................149
7.1 數(shù)據(jù)分析工具的調(diào)用.............................................................................150
7.2 統(tǒng)計(jì)學(xué)中的數(shù)據(jù)類型......................................................................151
7.2.1 數(shù)據(jù)類型的辨析................................................................................................152
7.2.2 三類數(shù)據(jù)的計(jì)算................................................................................................153
7.2.3 三類數(shù)據(jù)的轉(zhuǎn)化................................................................................................153
7.3 區(qū)間數(shù)據(jù)的統(tǒng)計(jì)學(xué)描述.............................................................154
7.3.1 區(qū)間數(shù)據(jù)的頻數(shù)表與直方圖...........................................................................154
7.3.2 集中位置指標(biāo)....................................................................................................160
7.3.3 離散程度指標(biāo)....................................................................................................163
7.3.4 數(shù)據(jù)分析工具：描述統(tǒng)計(jì)...............................................................................169
7.4 名義數(shù)據(jù)的統(tǒng)計(jì)描述.........................................................................170
7.4.1 COUNTIF 函數(shù)清點(diǎn)計(jì)數(shù).................................................................................171
7.4.2 率的標(biāo)準(zhǔn)化法....................................................................................................171
7.4.3 動(dòng)態(tài)數(shù)列.............................................................................................................173
第8 章 區(qū)間數(shù)據(jù)的假設(shè)檢驗(yàn)..................................................................................176
8.1 假設(shè)檢驗(yàn)的一般步驟.........................................................................177
8.2 樣本均數(shù)與總體均數(shù)的比較........................................................................................178
8.2.1 已知樣本均數(shù)和標(biāo)準(zhǔn)差....................................................................................178
8.2.2 已知樣本原始數(shù)據(jù)............................................................................................179
8.3 配對(duì)資料的比較.......................................................................................180
8.4 兩個(gè)樣本均數(shù)的比較...............................................................................181
8.4.1 兩樣本的總體方差齊性檢驗(yàn)...........................................................................181
8.4.2 總體方差齊同的t 檢驗(yàn).....................................................................................183
8.4.3 總體方差不齊的t 檢驗(yàn).....................................................................................185
8.4.4 大樣本的z 檢驗(yàn).................................................................................................186
8.5 多樣本方差Bartlett 齊性檢驗(yàn)........................................................187
8.6 單因素方差分析...........................................................................................188
8.6.1 單因素方差分析工具........................................................................................188
8.6.2 單因素方差分析圖示........................................................................................190
8.6.3 無(wú)原始數(shù)據(jù)的單因素方差分析.......................................................................191
8.6.4 單因素方差分析的多重比較...........................................................................191
8.7 雙因素方差分析...................................................................................193
8.7.1 無(wú)重復(fù)雙因素方差分析....................................................................................193
8.7.2 有重復(fù)雙因素方差分析....................................................................................195
第9 章 名義與有序數(shù)據(jù)的假設(shè)檢驗(yàn)...................................................................198
9.1 卡方檢驗(yàn)........................................................................................................199
9.1.1 四格表資料的χ2 檢驗(yàn)........................................................................................199
9.1.2 四格表配對(duì)資料的χ2 檢驗(yàn)...............................................................................203
9.1.3 四格表資料的Fisher 精確概率檢驗(yàn)...............................................................205
9.1.4 行×列表資料的χ2 檢驗(yàn).....................................................................................208
9.2 頻數(shù)分布的擬合優(yōu)度檢驗(yàn)............................................................................................210
9.2.1 正態(tài)分布.............................................................................................................210
9.2.2 二項(xiàng)分布.............................................................................................................213
9.2.3 Poisson 分布.......................................................................................................215
9.2.4 均勻分布.............................................................................................................216
9.3 非參數(shù)檢驗(yàn)............................................................................................217
9.3.1 配對(duì)資料Wilcoxon 符號(hào)秩檢驗(yàn).....................................................................218
9.3.2 兩獨(dú)立樣本非參數(shù)檢驗(yàn)....................................................................................221
9.3.3 多樣本Kruskal-Wallis 秩和檢驗(yàn).....................................................................225
9.3.4 多個(gè)相關(guān)樣本的Friedman 檢驗(yàn)......................................................................228
9.4 Ridit 分析....................................................................................230
第10 章 相關(guān)與回歸...............................................................................................235
10.1 簡(jiǎn)單相關(guān)與回歸..........................................................................236
10.1.1 Excel 函數(shù)實(shí)現(xiàn)簡(jiǎn)單相關(guān)與回歸...................................................................238
10.1.2 數(shù)據(jù)分析工具實(shí)現(xiàn)簡(jiǎn)單相關(guān)與回歸.............................................................243
10.1.3 添加趨勢(shì)線實(shí)現(xiàn)直線回歸分析.....................................................................247
10.2 曲線回歸...........................................................................................248
10.2.1 對(duì)數(shù)曲線擬合..................................................................................................249
10.2.2 乘冪曲線擬合..................................................................................................251
10.2.3 指數(shù)曲線擬合..................................................................................................252
10.2.4 多項(xiàng)式曲線擬合..............................................................................................253
10.3 非線性回歸.........................................................................................255
10.3.1 對(duì)數(shù)曲線擬合..................................................................................................255
10.3.2 乘冪曲線擬合..................................................................................................256
10.3.3 指數(shù)曲線擬合..................................................................................................257
10.3.4 多項(xiàng)式曲線擬合..............................................................................................257
10.4 多重相關(guān)與回歸.........................................................................................258
10.4.1 多重相關(guān)與多重協(xié)方差..................................................................................258
10.4.2 多重回歸分析..................................................................................................260
10.4.3 用函數(shù)實(shí)現(xiàn)多重回歸與相關(guān).........................................................................261
10.5 加權(quán)l(xiāng)ogistic 回歸................................................................................263
高級(jí)應(yīng)用篇
第11 章 變量求解與方案優(yōu)選....................................................................................268
11.1 單變量求解.....................................................................................269
11.1.1 目標(biāo)搜索...........................................................................................................269
11.1.2 求解非線性方程..............................................................................................270
11.2 規(guī)劃求解..............................................................................................271
11.3 方案管理器............................................................................................274
11.4 常見(jiàn)的方案優(yōu)選方法...............................................................................277
11.4.1 確定型決策.......................................................................................................277
11.4.2 非確定型決策..................................................................................................278
11.4.3 風(fēng)險(xiǎn)型決策.......................................................................................................281
11.5 綜合評(píng)價(jià)方法...................................................................................282
11.5.1 TOPSIS 法........................................................................................................282
11.5.2 層次分析法.......................................................................................................285
第12 章 預(yù)測(cè)分析...............................................................................................288
12.1 自相關(guān)與自回歸............................................................................289
12.1.1 線性時(shí)序趨勢(shì)..................................................................................................289
12.1.2 DW 統(tǒng)計(jì)量.......................................................................................................290
12.1.3 自相關(guān)...............................................................................................................291
12.1.4 自回歸...............................................................................................................292
12.2 移動(dòng)平均法......................................................................................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é)預(yù)測(cè)法......................................................................................299
12.4.1 啞變量多重回歸..............................................................................................300
12.4.2 AR（4）模型...................................................................................................303
12.4.3 季節(jié)指數(shù)法......................................................................................................304
12.5 Markov 預(yù)測(cè)法.................................................................................................306
12.6 灰色預(yù)測(cè)模型..................................................310
12.6.1 GM(1,1)模型的預(yù)測(cè)與評(píng)價(jià)方法...................................................................310
12.6.2 灰色模型預(yù)測(cè)實(shí)例..........................................................................................312
12.7 傅利葉分析................................................................................................314
12.8 圓形分布分析............................................................................................316
12.8.1 角度變換...........................................................................................................316
12.8.2 平均角及其假設(shè)檢驗(yàn)......................................................................................317
12.8.3 圓形分布分析實(shí)例..........................................................................................318
12.8.4 兩個(gè)或多個(gè)樣本平均角的比較.....................................................................320
第13 章 壽命表............................................................................................324
13.1 現(xiàn)時(shí)壽命表........................................................................................325
13.2 去死因壽命表..................................................................................330
13.3 健康期望壽命表..............................................................................334
第14 章 隨機(jī)化分組與樣本含量估計(jì).......................................................337
14.1 隨機(jī)化分組.......................................................................................338
14.1.1 完全隨機(jī)設(shè)計(jì)..................................................................................................338
14.1.2 配對(duì)設(shè)計(jì)...........................................................................................................339
14.1.3 配伍組設(shè)計(jì)......................................................................................................340
14.1.4 三個(gè)隨機(jī)化分組程序介紹.............................................................................342
14.2 樣本含量的估計(jì).......................................................................343
14.2.1 Nomogram 圖示法...........................................................................................345
14.2.2 假設(shè)檢驗(yàn)的樣本含量估計(jì).............................................................................346
14.2.3 置信區(qū)間的樣本含量估計(jì).............................................................................353
14.3 檢驗(yàn)功效的估計(jì)..........................................................................................357
14.4 流行病學(xué)研究的樣本含量與檢驗(yàn)功效......................................................................362
14.4.1 病例對(duì)照研究..................................................................................................362
14.4.2 隊(duì)列研究...........................................................................................................365
14.5 方差分析等方法的樣本含量與檢驗(yàn)功效.................................................................366
14.5.1 方差分析的檢驗(yàn)功效......................................................................................366
14.5.2 方差分析的樣本含量......................................................................................367
14.5.3 其他方法的樣本含量與檢驗(yàn)功效.................................................................368
第15 章 高級(jí)科研統(tǒng)計(jì)圖與Meta 分析.....................................................................369
15.1 統(tǒng)計(jì)地圖.............................................................................................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 法計(jì)算生存率及其標(biāo)準(zhǔn)誤.....................................................376
15.3.2 繪制生存曲線..................................................................................................377
15.4 重復(fù)測(cè)量數(shù)據(jù)的曲線圖...................................................................378
15.5 Meta 分析統(tǒng)計(jì)量的置信區(qū)間圖.............................................................379
15.5.1 Meta 分析的計(jì)算公式....................................................................................380
15.5.2 連續(xù)資料的Meta 分析...................................................................................383
15.5.3 離散資料的Meta 分析...................................................................................387
15.5.4 相關(guān)系數(shù)資料的Meta 分析...........................................................................391
15.5.5 P 值的Meta 分析............................................................................................392
第16 章 診斷試驗(yàn)評(píng)價(jià).........................................................................................394
16.1 常用統(tǒng)計(jì)指標(biāo)...................................................................................395
16.2 ROC 曲線原理及其繪制方法.....................................................................................399
16.2.1 有序分類資料ROC 分析...............................................................................400
16.2.2 連續(xù)資料ROC 分析........................................................................................403
16.2.3 值小為陽(yáng)性的ROC 分析...............................................................................404
16.3 診斷試驗(yàn)評(píng)價(jià)的Meta 分析........................................................................406
16.3.1 SROC 分析.......................................................................................................406
16.3.2 ROC 面積的Meta 分析..................................................................................412
16.4 樣本含量估計(jì)...........................................................................................415
16.4.1 靈敏度與特異度的置信區(qū)間估計(jì).................................................................415
16.4.2 ROC 曲線下面積的置信區(qū)間估計(jì)................................................................416
16.4.3 固定FPR 的靈敏度置信區(qū)間估計(jì)................................................................417
16.4.4 單個(gè)準(zhǔn)確度指標(biāo)的假設(shè)檢驗(yàn).........................................................................418
16.4.5 兩靈敏度或特異度的比較.............................................................................420
16.4.6 ROC 曲線下面積的比較................................................................................421
16.5 Kappa 值估計(jì)...................................................................................422
16.6 Bland-Altman 法.........................................................................425
電腦實(shí)驗(yàn)篇
第17 章 概率分布............................................................................................428
17.1 正態(tài)分布............................................................................................429
17.1.1 采用公式制作正態(tài)分布電腦實(shí)驗(yàn).................................................................429
17.1.2 正態(tài)曲線下的面積..........................................................................................431
17.1.3 正態(tài)分布綜合電腦實(shí)驗(yàn).................................................................................433
17.2 二項(xiàng)分布......................................................................................435
17.2.1 概率質(zhì)量函數(shù)..................................................................................................435
17.2.2 累計(jì)分布函數(shù)..................................................................................................436
17.2.3 二項(xiàng)分布電腦實(shí)驗(yàn)..........................................................................................436
17.2.4 樣本率及率的標(biāo)準(zhǔn)誤.....................................................................................438
17.3 Poisson 分布.................................................................................438
17.3.1 概率質(zhì)量函數(shù)與累計(jì)分布函數(shù).....................................................................439
17.3.2 Poisson 分布電腦實(shí)驗(yàn)...................................................................................439
17.4 其他連續(xù)分布.......................................................................................440
17.4.1 對(duì)數(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 章 隨機(jī)抽樣與抽樣誤差.............................................................................448
18.1 隨機(jī)數(shù)發(fā)生器與隨機(jī)函數(shù)..........................................................................................449
18.1.1 隨機(jī)數(shù)發(fā)生器..................................................................................................449
18.1.2 隨機(jī)數(shù)函數(shù)......................................................................................................454
18.2 有放回抽樣與無(wú)歸還抽樣..........................................................................................455
18.2.1 “抽樣”分析工具............................................................................................455
18.2.2 無(wú)歸還抽樣......................................................................................................456
18.3 隨機(jī)抽樣電腦實(shí)驗(yàn)............................................................................456
18.3.1 從正態(tài)總體中隨機(jī)抽樣..................................................................................457
18.3.2 從非正態(tài)總體中隨機(jī)抽樣.............................................................................461
第19 章 假設(shè)檢驗(yàn)的概率分布與統(tǒng)計(jì)用表...............................................................466
19.1 z 分布.......................................................................................................467
19.1.1 標(biāo)準(zhǔn)正態(tài)分布曲線下面積.............................................................................467
19.1.2 標(biāo)準(zhǔn)正態(tài)分布分位數(shù)......................................................................................469
19.1.3 標(biāo)準(zhǔn)正態(tài)分布的其他應(yīng)用.............................................................................469
19.1.4 z 分布電腦實(shí)驗(yàn)................................................................................................472
19.2 t 分布.............................................................................................473
19.2.1 t 分布的圖形及其分布特征...........................................................................474
19.2.2 t 分布曲線下面積與檢驗(yàn)界值.......................................................................476
19.2.3 t 分布電腦實(shí)驗(yàn)................................................................................................477
19.3 F 分布...............................................................................................479
19.3.1 F 分布的圖形及其分布特征..........................................................................480
19.3.2 F 分布曲線下面積與檢驗(yàn)界值......................................................................481
19.3.3 F 分布電腦實(shí)驗(yàn)..............................................................................................483
19.4 χ2 分布...........................................................................................484
19.4.1 χ2 分布的圖形及其分布特征.........................................................................484
19.4.2 χ2 分布曲線下面積與檢驗(yàn)界值.....................................................................486
19.4.3 χ2 分布電腦實(shí)驗(yàn)..............................................................................................486
第20 章 其他電腦實(shí)驗(yàn)與置信區(qū)間估計(jì).........................................................489
20.1 隨機(jī)現(xiàn)象.....................................................................................................490
20.2 置信區(qū)間......................................................................................................491
20.2.1 置信區(qū)間的電腦實(shí)驗(yàn)......................................................................................492
20.2.2 置信區(qū)間的估計(jì)..............................................................................................493
20.3 假設(shè)檢驗(yàn)的兩型錯(cuò)誤..............................................................................498
20.4 不同檢驗(yàn)方法所導(dǎo)致結(jié)論的差異......................................................................499
20.5 t 檢驗(yàn)的兩型錯(cuò)誤................................................................................501
20.6 t 與t'檢驗(yàn).................................................................................502
20.7 一般t 檢驗(yàn)做方差分析兩兩比較導(dǎo)致的I 型錯(cuò)誤..................................................503
20.8 樣本含量對(duì)假設(shè)檢驗(yàn)的影響......................................................................................504
20.9 卡方檢驗(yàn)公式校正的意義....................................................................................506
20.10 回歸參數(shù)意義理解...........................................................................508
20.11 壽命表電腦實(shí)驗(yàn).....................................................................................509
附錄A Excel 函數(shù).........................................................................................................510
附錄B 概率基本知識(shí).................................................................................................536
附錄C Excel 2010 宏............................................................................................538
附錄D Excel 2010 的數(shù)據(jù)分析新功能....................................................................546
附錄E Excel 2010 VBA 應(yīng)用..................................................................................551
參考文獻(xiàn)...................................................................................................................554