《Excel統計分析與電腦實驗》是2009年電子工業出版社出版的圖書，該書作者是宇傳華。
基本介紹
- 書名：Excel統計分析與電腦實驗
- 作者：宇傳華
- ISBN：9787121-077913
- 類別：計算機
- 頁數：508
- 定價：69
- 出版社：電子工業出版社
- 出版時間：2009-1-1
- 裝幀：平裝
- 開本：16開
著者說明,前 言,目 錄,文 摘,
著者說明
本書主要章節由宇傳華主筆完成，以下人員在Excel程式編寫及本書（包括前面的兩個版本）編寫方面作出了較大貢獻。
武漢大學 王震坤 楊子娟 李 鵬 連 肖
姚 聰 孫 敏 季 潔 李 妍
夏 欣 魏娜娜 郝玉偉
華中科技大學 王家春
北京法馬蘇提克醫藥有限公司 方龍
首都醫科大學 祝惠萍
廣西中醫藥大學 李京
騰訊公司 林嵩藝
深圳市衛生人口和計生委員會 董國營
深圳市寶安區婦幼保健院 鐘文明
上海市普陀區疾病預防控制中心 蔣麗麗
上海市嘉定區疾病預防控制中心 馬飛飛
廣東省檢驗檢疫局 顏 傑
中山大學 張晉昕
前 言
《Excel與數據分析》出版後不到1年的時間，曾兩次印刷了8500冊，該書2003年7月被中國高校教材圖書網列為屈指可數的暢銷書。讀者的一封封E-mail來信和“google學術搜尋”搜尋到的引文結果，使我感受到了讀者對Excel做統計學分析的熱情和厚望。出版社2003年年底希望我儘快做出第二版。我一直在努力，即使在美國University of Washington（西雅圖）做訪問學者的1年寶貴時間裡，我也一直沒有中斷過，試圖將Excel的數據整理、統計分析和電腦實驗功能得到更深層次的發掘。
本書正是在《Excel與數據分析》基礎上完成的，保留了上一本書的實例，但統計學分析模板和電腦實驗程式得到了更為完善的改進和補充。從數量上看，自編的Excel程式從原先的66個擴展到了203個，絕大多數程式只需鍵入數據，結果便自動在同一工作表內產生，具有即改即可見的效果。
選用Excel做統計分析的理由
與標準統計學軟體SAS、SPSS等相比較，Excel的優勢之處在於：
（1）強大的數據自動填充功能，這使得在Excel工作表中輸入數據變得相當簡單。
（2）方便的數據匯總與數據透視分析功能，可快速獲得整個工作表，甚至整個工作簿數據的探索性統計分析結果。
（3）靈活的單元格絕對引用與相對引用功能，使得大量類似的Excel公式輸入變得簡單，很多情況下只要鍵入一個公式，通過拖放方式便可完成該單元格周圍其他單元格公式的生成。
（4）完美的圖表內置格式，使得統計圖形既製作方便，又美觀好看。
（5）Excel的“數據分析”工具可完成大多數常用的統計分析，對於較複雜統計學方法可簡單利用Excel內置函式自編程式完成。
（6）正規渠道購買的計算機內大多數預裝有微軟Office辦公軟體，且為中文界面，使用它進行統計學分析無著作權問題。
本書特色
本書從實例出發，易學易懂。所配光碟內包括大量實例數據與程式，很多情況下，只需在編好的Excel程式中鍵入有關數據或參數，便可隨即獲得計算結果。
所配光碟程式均為開放代碼，讀者通過書中有關提示，結合光碟檔案可以自編新的程式，解決更複雜的統計學分析難題。部分Excel程式採用了工作表保護，目的是防止Excel工作表內重要數據或計算公式發生不經意的改動。如果需要去除保護，只需單擊選單“工具→保護→撤銷工作表保護”，輸入密碼“0”即可。
本書在Windows XP作業系統下，以Excel 2003為基礎，螢幕解析度為1024×768像素下製作完成。在Excel 2007中，所有程式經測試均可使用，只是調用宏程式檔案的方式有所不同。Excel 2003先設定“宏→安全性”為“中”，單擊“載入宏”，便可打開；對於Office 2007用戶，打開宏程式檔案時，會在功能區下方出現“安全警告宏已被禁用。”單擊“選項”，在彈出的對話框中選擇“啟用此內容”，單擊“確定”按鈕，即可在Excel 2007中正常運行宏程式。
本書內容
本書共分為數據整理、統計分析、高級套用、電腦實驗4篇。
在數據整理篇（第1～5章）中，較為全面系統地介紹了數據的編輯、查詢、建立子數據清單、數據透視表與透視圖分析等方法。
在統計分析篇（第6～10章）中，利用Excel圖表嚮導功能製作了多種常見統計圖形；利用Excel內置函式編制了大量統計分析模板，如卡方檢驗、常態分配等的擬合優度檢驗、Fisher精確機率法、非參數檢驗、Ridit分析、簡單相關與回歸分析、多重相關與回歸分析、加權Logistic回歸分析等。
在高級套用篇（第11～16章）中，編制了樣本含量與檢驗功效的估計、壽命表、診斷試驗評價的樣本含量估計、ROC曲線分析、Meta分析、Topsis法、層次分析法等統計模組。
在電腦實驗篇（第17～20章）中，利用“視圖→工具列→控制項工具箱”製作滾動條，結合Excel內置函式和內置圖表，製作了大量電腦實驗，如常態分配、二項分布、Poisson分布等10餘個機率分布電腦實驗；z分布、t分布、F分布、卡方分布等常用假設檢驗電腦實驗；中心極限定理、隨機現象、假設檢驗、置信區間等總計近50個電腦實驗程式。
對於“實用型”用戶，可直接利用有關程式進行統計學分析；對於“高級”用戶，可根據書中提示，結合光碟的相應檔案，對相應工作表略加修改，完成更複雜的計算。
該書是在華中科技大學第六批教改項目，華中科技大學出版基金的資助下完成的。在西雅圖做訪問學者的1年期間，在工作、生活等多方面得到了周曉華、桂華、王端、張宇、王雷、劉宇清、陳洪波、徐又農、鄭怡、楊鼎、楊力、Rich Fullner、Joanna Fullner等的幫助，我的妻子、女兒對我的編寫工作也給予了高度理解與支持，在此一併致謝。
限於作者水平有限，書中錯謬之處在所難免，懇請同行專家和廣大讀者不吝賜教。
宇傳華
華中科技大學、武漢大學
2008年10月1日於武漢
目 錄
數 據 整 理 篇
第1章 Excel基本知識 2
1.1 Excel的安裝 3
1.2 Excel工作界面簡介 3
1.3 滑鼠與鍵盤的操作 7
1.4 單元格區域與整個工作表的選取 9
1.5 工作表重命名與複製副本 10
1.6 工作表的格式化 11
1.7 工作表的列印 15
第2章 公式與函式 17
2.1 公式的建立 18
2.2 函式的結構 18
2.3 公式中的運算符 19
2.4 運算符的優先順序 21
2.5 單元格的混合引用 21
2.5.1 相對引用 22
2.5.2 絕對引用 22
2.5.3 混合引用 22
2.5.4 A1引用樣式 23
2.5.5 套用實例 23
2.6 公式編輯常用的快捷鍵 25
2.7 編輯公式時常見的錯誤信息 25
2.8 公式保護小技巧 27
2.9 常用數學函式套用舉例 28
2.9.1 常用數學函式 28
2.9.2 數組運算 32
2.9.3 矩陣的運算與求解線性方程組 33
2.10 創建自定義函式 36
2.11 獲取函式幫助信息 37
第3章 數據清單的建立與利用 38
3.1 數字的單元格格式 39
3.2 數據的有效性設定 42
3.3 建立數據清單 45
3.4 數據“記錄單”的作用 46
3.5 數據的自動填充 48
3.5.1 簡單數據的自動填充 48
3.5.2 等比數列數據的填充 49
3.5.3 公式的自動填充 50
3.5.4 智慧型標記的套用 51
3.6 數據的分列 51
3.7 添加與編輯批註 53
3.8 特殊數學符號的鍵入 54
3.9 數據的導入與導出 56
3.10 常用統計軟體調用Excel檔案 56
3.10.1 SAS 8.0調用Excel檔案 56
3.10.2 SPSS 13.0調用Excel檔案 57
3.10.3 S-PLUS 2000調用Excel檔案 57
第4章 數據的編輯與查詢 60
4.1 視窗的凍結與拆分 61
4.2 數據的剪貼與刪除 62
4.3 數據的查找與替換 63
4.4 行列轉置與選擇性貼上的其他妙用 64
4.5 將連續型變數劃分等級 66
4.6 年齡的計算與當前日期的生成 67
4.7 數據的跨工作表操作 67
4.8 多個工作表的合併計算 69
4.9 查詢與新建子數據清單 71
4.9.1 排序 71
4.9.2 自動篩選 72
4.9.3 高級篩選 75
4.9.4 選擇不重複的記錄 76
4.9.5 人工篩選 76
4.10 兩個類似檔案的比較 77
第5章 分類匯總與透視分析 79
5.1 分類匯總 80
5.2 數據透視表 83
5.2.1 創建數據透視表 84
5.2.2 數據透視表的結構及其工具列 85
5.2.3 由數據透視表獲得均數標準差 87
5.2.4 產生列聯表 89
5.2.5 創建子數據清單 93
5.2.6 設定報告格式與更新數據 95
5.2.7 數據透視圖表中創建公式 96
5.3 數據透視圖 96
5.3.1 創建數據透視圖 97
5.3.2 調整數據透視圖 98
5.3.3 將數據透視圖更改為靜態圖 99
5.4 數據透視的其他套用 101
5.4.1 多個工作表的數據透視 101
5.4.2 數據透視表網頁 101
5.4.3 幻燈片中引用數據透視表 102
統 計 分 析 篇
第6章 統計圖表 106
6.1 統計表的製作 107
6.2 統計圖的結構及特點 108
6.3 Excel標準圖表類型簡介 109
6.4 創建統計圖的一般步驟 111
6.5 統計圖的編輯與修飾 113
6.6 繪製統計圖舉例 118
6.6.1 分段條圖 118
6.6.2 誤差條圖 119
6.6.3 百分條圖 121
6.6.4 餅圖 122
6.6.5 複合條餅圖 123
6.6.6 線圖與半對數線圖 123
6.6.7 雷達圖 125
6.6.8 氣泡圖 126
6.6.9 箱圖 128
6.6.10 人口金字塔圖 131
6.6.11 三角函式製作線圖 133
6.6.12 曲面圖 134
第7章 統計描述 136
7.1 數據分析工具的調用 137
7.2 統計學中的數據類型 138
7.2.1 數據類型的辨析 139
7.2.2 三類數據的計算 140
7.2.3 三類數據的轉化 140
7.3 區間數據的統計學描述 141
7.3.1 區間數據的頻數表與直方圖 141
7.3.2 集中位置指標 146
7.3.3 離散程度指標 149
7.3.4 數據分析工具：描述統計 154
7.4 名義數據的統計描述 156
7.4.1 COUNTIF函式清點計數 156
7.4.2 率的標準化法 156
7.4.3 動態數列 158
第8章 區間數據的假設檢驗 161
8.1 假設檢驗的一般步驟 162
8.2 樣本均數與總體均數的比較 163
8.2.1 已知樣本均數和標準差 163
8.2.2 已知樣本原始數據 164
8.3 配對資料的比較 165
8.4 兩個樣本均數的比較 166
8.4.1 兩樣本的總體方差齊性檢驗 166
8.4.2 總體方差齊同的t檢驗 168
8.4.3 總體方差不齊的t檢驗 170
8.4.4 大樣本的z檢驗 171
8.5 多樣本方差Bartlett齊性檢驗 171
8.6 單因素方差分析 173
8.6.1 單因素方差分析工具 173
8.6.2 單因素方差分析圖示 174
8.6.3 無原始數據的單因素方差分析 175
8.6.4 單因素方差分析的多重比較 175
8.7 雙因素方差分析 177
8.7.1 無重複雙因素方差分析 178
8.7.2 有重複雙因素方差分析 179
第9章 名義與有序數據的假設檢驗 182
9.1 卡方檢驗 183
9.1.1 四格表資料的 檢驗 183
9.1.2 四格表配對資料的2檢驗 186
9.1.3 四格表資料的Fisher精確機率檢驗 188
9.1.4 行×列表資料的 檢驗 191
9.2 頻數分布的擬合優度檢驗 193
9.2.1 常態分配 193
9.2.2 二項分布 196
9.2.3 Poisson分布 197
9.2.4 均勻分布 198
9.3 非參數檢驗 199
9.3.1 配對資料Wilcoxon符號秩檢驗 200
9.3.2 兩獨立樣本非參數檢驗 202
9.3.3 多樣本Kruskal-Wallis秩和檢驗 206
9.3.4 多個相關樣本的Friedman檢驗 209
9.4 Ridit分析 211
第10章 相關與回歸 215
10.1 簡單相關與回歸 216
10.1.1 Excel函式實現簡單相關與回歸 218
10.1.2 數據分析工具實現簡單相關與回歸 223
10.1.3 添加趨勢線實現直線回歸分析 226
10.2 曲線回歸 227
10.2.1 對數曲線擬合 228
10.2.2 乘冪曲線擬合 229
10.2.3 指數曲線擬合 231
10.2.4 多項式曲線擬合 232
10.3 非線性回歸 233
10.3.1 對數曲線擬合 233
10.3.2 乘冪曲線擬合 234
10.3.3 指數曲線擬合 235
10.3.4 多項式曲線擬合 235
10.4 多重相關與回歸 236
10.4.1 多重相關與多重協方差 236
10.4.2 多重回歸分析 237
10.4.3 用函式實現多重回歸與相關 239
10.5 加權logistic回歸 241
高 級 應 用 篇
第11章 變數求解與方案優選 246
11.1 單變數求解 247
11.1.1 目標搜尋 247
11.1.2 求解非線性方程 248
11.2 規劃求解 249
11.3 方案 251
11.4 常見的方案優選方法 253
11.4.1 確定型決策 254
11.4.2 非確定型決策 255
11.4.3 風險型決策 258
11.5 綜合評價方法 258
11.5.1 TOPSIS法 258
11.5.2 層次分析法 260
第12章 預測分析 263
12.1 自相關與自回歸 264
12.1.1 線性時序趨勢 264
12.1.2 DW統計量 265
12.1.3 自相關 266
12.1.4 自回歸 267
12.2 移動平均法 268
12.3 指數平滑法 269
12.3.1 數據分析工具——指數平滑 270
12.3.2 指數平滑的初始值 271
12.3.3 求解最佳平滑常數 272
12.4 季節預測法 273
12.4.1 啞變數多重回歸 274
12.4.2 AR（4）模型 277
12.4.3 季節指數法 278
12.5 Markov預測法 280
12.6 傅立葉分析 283
第13章 壽命表 285
13.1 現時壽命表 286
13.2 去死因壽命表 291
13.3 健康期望壽命表 293
第14章 隨機化分組與樣本 含量估計 297
14.1 隨機化分組 298
14.1.1 完全隨機設計 298
14.1.2 配對設計 299
14.1.3 配伍組設計 300
14.1.4 三個隨機化分組程式介紹 301
14.2 樣本含量的估計 303
14.2.1 Nomogram圖示法 304
14.2.2 假設檢驗的樣本含量估計 305
14.2.3 置信區間的樣本含量估計 312
14.3 檢驗功效的估計 315
14.4 流行病學研究的樣本含量與檢驗功效 320
14.4.1 病例對照研究 320
14.4.2 佇列研究 323
14.5 方差分析等方法的樣本含量與檢驗功效 324
14.5.1 方差分析的檢驗功效 324
14.5.2 方差分析的樣本含量 325
14.5.3 其他方法的樣本含量與檢驗功效 325
第15章 高級科研統計圖與 Meta分析 326
15.1 統計地圖 327
15.2 P-P圖與Q-Q圖 329
15.2.1 創建繪圖數據 330
15.2.2 繪製正態P-P圖 331
15.2.3 繪製正態Q-Q圖 331
15.3 生存曲線 332
15.3.1 Kaplan-Meier法計算生存率及其標準誤 332
15.3.2 繪製生存曲線 333
15.4 重複測量數據的曲線圖 334
15.5 Meta分析統計量的置信區間圖 336
15.5.1 Meta分析的計算公式 336
15.5.2 連續資料的Meta分析 339
15.5.3 離散資料的Meta分析 343
15.5.4 相關係數資料的Meta分析 346
15.5.5 P值的Meta分析 348
第16章 診斷試驗評價 349
16.1 常用統計指標 350
16.2 ROC曲線原理及其繪製方法 354
16.2.1 有序分類資料ROC分析 355
16.2.2 連續資料ROC分析 357
16.2.3 值小為陽性的ROC分析 359
16.3 診斷試驗評價的Meta分析 360
16.3.1 SROC分析 360
16.3.2 ROC面積的Meta分析 366
16.4 樣本含量估計 368
16.4.1 靈敏度與特異度的置信區間估計 368
16.4.2 ROC曲線下面積的置信區間估計 369
16.4.3 固定FPR的靈敏度置信區間估計 371
16.4.4 單個準確度指標的假設檢驗 372
16.4.5 兩靈敏度或特異度的比較 373
16.4.6 ROC曲線下面積的比較 374
16.5 Kappa值估計 376
電 腦 實 驗 篇
第17章 機率分布 380
17.1 常態分配 381
17.1.1 採用公式製作常態分配電腦實驗 381
17.1.2 正態曲線下的面積 383
17.1.3 常態分配綜合電腦實驗 384
17.2 二項分布 386
17.2.1 機率質量函式 386
17.2.2 累計分布函式 387
17.2.3 二項分布電腦實驗 387
17.2.4 樣本率及率的標準誤 389
17.3 Poisson分布 390
17.3.1 機率質量函式與累計分布函式 390
17.3.2 Poisson分布電腦實驗 390
17.4 其他連續分布 392
17.4.1 對數常態分配 392
17.4.2 均勻分布 392
17.4.3 指數分布 393
17.4.4 Weibull分布 393
17.4.5 Beta分布 395
17.4.6 Gamma分布 395
17.5 其他離散分布 396
17.5.1 均勻分布 396
17.5.2 超幾何分布 397
第18章 隨機抽樣與抽樣誤差 399
18.1 隨機數發生器與隨機函式 400
18.1.1 隨機數發生器 400
18.1.2 隨機數函式 404
18.2 有放回抽樣與無歸還抽樣 405
18.2.1 “抽樣”分析工具 405
18.2.2 無歸還抽樣 406
18.3 隨機抽樣電腦實驗 407
18.3.1 從正態總體中隨機抽樣 407
18.3.2 從非正態總體中隨機抽樣 411
第19章 假設檢驗的機率分布與統計用表 415
19.1 z分布 416
19.1.1 標準常態分配曲線下面積 416
19.1.2 標準常態分配分位數 417
19.1.3 標準常態分配的其他套用 418
19.1.4 z分布電腦實驗 420
19.2 t分布 422
19.2.1 t分布的圖形及其分布特徵 423
19.2.2 t分布曲線下面積與檢驗界值 424
19.2.3 t分布電腦實驗 425
19.3 F分布 427
19.3.1 F分布的圖形及其分布特徵 428
19.3.2 F分布曲線下面積與檢驗界值 429
19.3.3 F分布電腦實驗 431
19.4 分布 431
19.4.1 分布的圖形及其分布特徵 432
19.4.2 分布曲線下面積與檢驗界值 433
19.4.3 分布電腦實驗 433
第20章 其他電腦實驗與置信區間估計 436
20.1 隨機現象 437
20.2 置信區間 438
20.2.1 置信區間的電腦實驗 439
20.2.2 置信區間的估計 440
20.3 假設檢驗的兩型錯誤 444
20.4 不同檢驗方法所導致結論的差異 446
20.5 t檢驗的兩型錯誤 447
20.6 t與t'檢驗 448
20.7 一般t檢驗做方差分析兩兩比較導致的I型錯誤 449
20.8 樣本含量對假設檢驗的影響 450
20.9 卡方檢驗公式校正的意義 452
20.10 回歸參數意義理解 453
20.11 壽命表電腦實驗 454
附錄A Excel函式 456
附錄B 機率基本知識 474
附錄C Excel 2003宏 475
附錄D Excel 2007的數據分析
新功能 483
附錄E Excel 2003 VBA套用 486
參考文獻 489
文 摘
2.數值的選擇性貼上
一般的貼上是將複製的單元格所有信息（包括公式、數值、格式與批註等）全部貼上到另一個單元格。很多情況下並不需要如此，例如，為了利用由公式計算的數據，複製該列數據，然後貼上到其他單元格後，由於公式的相對引用等原因，貼上的數據將可能變成其他數據值，而非先前複製的數據。解決這一問題的方法是：單擊選單中的“編輯一選擇性貼上”，在“貼上”選項下選擇“數值”，即可將先前公式計算的數據複製到所需貼上的位置。
有時，為了讓公式計算出的數據移動位置後，其數值保持不變。可先選取該單元格區域，複製後選擇性貼上“數值”到原單元格（左上角）位置，此時公式等不復存在，該列變成了純“數據”，可以任意移動位置而保持數值不變。
3.選擇性米占貼的運算選項
選擇性貼上的“運算”有加、減、乘、除等選項（見圖4。6）。有時需要將某數據加、減、乘、除到某一列的每個個體中。例如，為了將身高的單位“米”轉換成“厘米”，需要將身高乘以100，可按下列操作進行：
首先在某個空白單元格中鍵入100，選定此單元格，單擊“複製”按鈕，選取想修改的單元格區域，例如E2：E101；然後在“編輯一選擇性貼上一運算”選項中單選“乘”，單擊“確定”按鈕。為了保持數據清單的整潔，可將某個空白單元格中輸入的100刪除。
4.Excel統計圖貼上到Word
將Excel製作的統計圖形，按照一般的複製、貼上方法複製到Microsoft Word中後，統計圖的字型、坐標刻度有時會發生不期望的自動改變。如果在。Excel中，圖形已經編輯修飾好了，希望拷貝到Word後不再作編輯修改，這時可在Excel中選取圖形後單擊“複製”按鈕，打開或新建一個Word檔案，選擇需要貼上的位置，在Word的選單中單擊“編輯一選擇性貼上”，會彈出一個“選擇性貼上”對話框（見圖4-8），在對話框中選擇圖片後，單擊“確定”按鈕即可。
