Excel 統計分析：方法與實踐

本書簡要介紹20餘種統計分析原理和計算方法，重點介紹了利用統計函數、邏輯函數、數學與三角函數等函數和數學分析工具方法進行統計分析的方法，同時介紹了數據整理、製作表格等10餘種實用技巧，將統計理論及其在Excel2016平臺的實現方法完美地結合起來，在一定程度上達到學習一本書熟悉一門科學和掌握一套工具的實用效果。本書非常適合教學或學習統計課程的師生和有統計分析需求的人員，有助於顯著提高教學效率和工作效率。

 

 

 

第1章  背景知識 

 

1.1  統計學背景知識 1

1.1.1  統計學概論 1

1.1.2  統計數據 2

1.1.3  幾組基本概念 5

1.2  Excel 2016工作環境 7

1.2.1  Excel 2016窗口 7

1.2.2  Excel 2016主題 11

1.2.3  Excel三大元素 11

1.3  Excel 2016函數公式 13

1.3.1  Excel函數結構 13

1.3.2  函數參數的類型 14

1.3.3  統計學常用函數 17

1.3.4  獲取Excel函數的幫助 22

1.3.5  啟用“開發工具”選項卡 23

1.3.6  加載“分析工具庫”工具 23

 

第2章  統計表 

 

2.1  統計表概述 25

2.1.1  統計表的構成 25

2.1.2  統計表的分類 26

2.1.3  Excel統計表的設計 27

2.1.4  對Excel表照相 30

2.1.5  隱藏區域或工作表的0值 32

2.1.6  將0值或空單元格標為“-” 35

2.2  次數分佈表 37

2.2.1  單項式次數分佈表 37

2.2.2  組距式次數分佈表 42

2.2.3  相對次數分佈表 65

2.2.4  累積次數及累積百分數分佈表 66

2.3  條件求和 69

2.3.1  單條件求和 69

2.3.2  多條件求和 75

2.4  分類匯總 79

2.5  排位與百分比排位 81

 

第3章  統計圖 

 

3.1  統計圖概述 84

3.1.1  統計圖的構成 84

3.1.2  統計圖的分類 85

3.1.3  Excel圖表概述 86

3.2  次數分佈圖 87

3.2.1  次數直方圖 87

3.2.2  次數排列圖 94

3.2.3  次數多邊圖 95

3.2.4  累積次數分佈圖 96

3.2.5  組合的次數分佈圖 98

3.3  直條圖 102

3.3.1  復合柱形圖 103

3.3.2  誤差條圖 107

3.3.3  雙向條形圖 109

3.3.4  不等寬柱形圖 114

3.4  餅圖 129

3.4.1  復合餅圖 130

3.4.2  雙層餅圖 132

3.5  折線圖 135

3.6  散點圖 137

3.7  Excel 2016新增圖表 140

3.7.1  樹狀圖 140

3.7.2  旭日圖 142

3.7.3  漏鬥圖 144

3.7.4  瀑布圖 145

3.7.5  箱形圖 146

3.8  莖葉圖 148

 

第4章  統計量 

 

4.1  集中量數 156

4.1.1  算術平均數 156

4.1.2  修剪平均數 158

4.1.3  幾何平均數 160

4.1.4  調和平均數 163

4.1.5  中數 168

4.1.6  眾數 169

4.1.7  分類的集中量數 171

4.2  差異量數 173

4.2.1  極差 173

4.2.2  平均差 175

4.2.3  四分位差 176

4.2.4  方差 177

4.2.5  標準差 179

4.2.6  變異系數 182

4.2.7  分類的差異量數 183

4.3  地位量數 186

4.3.1  百分位數 186

4.3.2  百分等級分數 188

4.3.3  標準分數 189

4.4  分佈形態 190

4.4.1  偏度 190

4.4.2  峰度 195

4.4.3  分類的偏斜度和峰值 198

4.5  描述統計工具 199

 

第5章  概率及其分佈 

 

5.1  概率原理 201

5.1.1  什麽是概率 201

5.1.2  基本計數原理 202

5.1.3  排列組合原理 203

5.2  二項分佈 213

5.2.1  二項分佈概述 213

5.2.2  二項分佈相關計算 213

5.2.3  繪制二項分佈圖 220

5.2.4  繪制多個二項分佈圖 221

5.2.5  二項分佈的動態演示 224

5.2.6  二項分佈的近似正態分佈演示 228

5.2.7  製作二項分佈表 233

5.3  泊松分佈 235

5.3.1  泊松分佈概述 235

5.3.2  泊松分佈相關計算 235

5.3.3  繪制泊松分佈圖 242

5.3.4  繪制多個泊松分佈圖 245

5.3.5  隨機生成泊松分佈 248

5.3.6  製作泊松分佈表 250

5.4  正態分佈 252

5.4.1  正態分佈概述 252

5.4.2  正態分佈相關計算 253

5.4.3  繪制正態分佈曲線 255

5.4.4  繪制多條正態分佈曲線 256

5.4.5  繪制正態分佈的動態置信區間 258

5.4.6  繪制原始數據的正態分佈曲線 261

5.4.7  正態抽樣及概率分佈的動態演示 267

5.5  標準正態分佈 270

5.5.1  標準正態分佈概述 270

5.5.2  標準正態分佈相關計算 271

5.5.3  繪制標準正態分佈曲線 274

5.5.4  隨機生成標準正態分佈 276

5.5.5  製作標準正態分佈表 278

5.5.6  繪制α、β區域示意圖 279

 

第6章  抽樣及其分佈 

 

6.1  抽樣方法 283

6.1.1  簡單隨機抽樣 284

6.1.2  等距隨機抽樣 300

6.1.3  等距不隨機抽樣 308

6.1.4  分層隨機抽樣 310

6.2  樣本容量的確定 313

6.2.1  估計總體均值時所需樣本容量 314

6.2.2  估計總體比例時所需樣本容量 317

6.2.3  樣本與總體均值差異顯著性檢驗時

所需樣本容量 319

6.2.4  兩樣本均值差異顯著性檢驗時所需

樣本容量 321

6.2.5  樣本與總體比例差異顯著性檢驗時

所需樣本容量 322

6.2.6  兩樣本比例差異顯著性檢驗時所需

樣本容量 323

6.2.7  分層抽樣時計算各層樣本容量 324

6.3  製作樣本容量表 325

6.3.1  估計總體均值時的樣本容量表 325

6.3.2  估計總體比例時的樣本容量表 327

6.3.3  兩個樣本均值假設檢驗時的樣本

容量表 327

6.3.4  兩個樣本比例假設檢驗時的樣本

容量表 328

6.4  卡方分佈 330

6.4.1  卡方分佈概述 330

6.4.2  計算卡方分佈值 330

6.4.3  繪制卡方分佈的兩類曲線 332

6.4.4  繪制多條卡方分佈曲線 334

6.4.5  繪制卡方分佈曲線的α區域 335

6.4.6  製作卡方分佈數值表 337

6.5  t分佈 338

6.5.1  t分佈概述 338

6.5.2  計算t分佈值 339

6.5.3  繪制t分佈的兩類曲線 341

6.5.4  繪制多條t分佈曲線 343

6.5.5  繪制t分佈曲線的α區域 344

6.5.6  製作t分佈數值表 346

6.6  F分佈 347

6.6.1  F分佈概述 347

6.6.2  計算F分佈值 347

6.6.3  繪制F分佈的兩類曲線 349

6.6.4  繪制多條F分佈曲線 351

6.6.5  繪制F分佈曲線的α區域 353

6.6.6  製作F分佈數值表 354

 

第7章  參數估計 

 

7.1  參數估計概述 356

7.1.1  點估計概述 356

7.1.2  區間估計概述 357

7.2  總體均值及其差異的區間估計 358

7.2.1  方差已知時正態總體均值的

區間估計 358

7.2.2  方差未知時正態總體均值的

區間估計 359

7.2.3  大樣本非正態總體均值的

區間估計 360

7.2.4  使用“描述統計”工具得到

區間半徑 362

7.2.5  方差已知時正態總體均值差的

區間估計 364

7.2.6  方差未知且相等時正態總體均值差的

區間估計 365

7.2.7  方差未知且不等時正態總體均值差的

區間估計 366

7.2.8  大樣本非正態總體均值差的

區間估計 367

7.3  總體標準差與方差的區間估計 368

7.3.1  正態總體標準差的區間估計 368

7.3.2  正態總體方差的區間估計 369

7.3.3  兩正態總體方差比的區間估計 370

7.4  總體比率及其差異的區間估計 372

7.4.1  大樣本時總體比率的區間估計 372

7.4.2  精確法對總體比率的區間估計 374

7.4.3  大樣本時兩總體比率差異的

區間估計 375

7.4.4  製作二項分佈的置信區間表 376

7.4.5  單變量求解泊松分佈λ的精確

置信區間 377

7.4.6  由選擇題捲面分數對真分數的

區間估計 381

7.4.7  使用工具預測數據隨日期變化的

區間 385

7.5  總體相關系數的區間估計 388

7.5.1  積差相關系數的區間估計 388

7.5.2  等級相關系數的區間估計 390

 

第8章  假設檢驗 

 

8.1  假設檢驗的基本原理 392

8.1.1  假設檢驗中的小概率原理 392

8.1.2  假設檢驗中的兩類錯誤 393

8.1.3  單側檢驗與雙側檢驗 395

8.1.4  假設檢驗的步驟與規則 396

8.1.5  假設檢驗的效能 397

8.2  總體均值的顯著性檢驗 397

8.2.1  方差已知時正態總體均值的

Z檢驗 398

8.2.2  方差未知時正態總體均值的

t檢驗 400

8.2.3  大樣本時非正態總體均值的近似

Z檢驗 402

8.2.4  大、小樣本時泊松分佈總體均值的

檢驗 403

8.2.5  方差已知時獨立正態總體均值差的

Z檢驗 405

8.2.6  方差未知且相等時獨立正態總體

均值差的t檢驗 408

8.2.7  方差未知且不等時獨立正態總體

均值差的近似t檢驗 411

8.2.8  相關系數已知時成對正態總體均值差

的t檢驗 414

8.2.9  相關系數未知時成對正態總體均值差

的t檢驗 415

8.2.10  大樣本時非正態總體均值差的近似

Z檢驗 418

8.2.11  泊松分佈總體均值差的近似

Z檢驗 419

8.3  總體方差的顯著性檢驗 421

8.3.1  樣本方差與正態總體方差比值的

卡方檢驗 421

8.3.2  獨立樣本時兩正態總體方差差異的

F檢驗 424

8.3.3  相關樣本時兩正態總體方差差異的

t檢驗 428

8.4  總體比率的顯著性檢驗 429

8.4.1  大樣本時總體比率的近似Z檢驗 429

8.4.2  小樣本時總體比率的p值法檢驗 430

8.4.3  小樣本時總體比率的直觀區間法

檢驗 431

8.4.4  總體比率的二項分佈置信區間法

檢驗 433

8.4.5  獨立大樣本時兩總體比率差異的

Z檢驗 434

8.4.6  相關樣本時兩總體比率差異的

Z檢驗 436

8.5  總體積差相關系數的顯著性

檢驗 439

8.5.1  ρ=0時單總體積差相關系數的

t檢驗 439

8.5.2  ρ≠0時單總體積差相關系數的

Z檢驗 441

8.5.3  獨立樣本時積差相關系數差異的

Z檢驗 442

8.5.4  相關樣本時積差相關系數差異的

t檢驗 443

8.5.5  製作積差相關系數r的臨界值表 445

8.5.6  製作相關系數r值的費舍

Zr轉換表 445

 

 

 

第9章  方差分析 

 

9.1  方差分析的基本原理 447

9.1.1  多重t檢驗的問題 447

9.1.2  方差分析的邏輯基礎 448

9.1.3  方差分析的基本過程 450

9.1.4  方差分析的基本條件 451

9.1.5  方差分析中的齊性檢驗 451

9.2  單因素方差分析 457

9.2.1  有原始數據的單因素方差分析 457

9.2.2  無原始數據的單因素方差分析 461

9.3  多因素方差分析 462

9.3.1  無重復雙因素方差分析 463

9.3.2  可重復雙因素方差分析 470

9.3.3  析因設計的方差分析 478

9.3.4  正交設計的方差分析 483

9.3.5  嵌套設計的方差分析 500

9.3.6  裂區設計的方差分析 506

9.3.7  拉丁方設計的方差分析 511

9.4  多個平均數之間的比較 515

9.4.1  最小顯著差數法 515

9.4.2  最小顯著極差法 517

9.5  方差分析前數據的轉換 522

9.5.1  平方根轉換 522

9.5.2  對數轉換法 523

9.5.3  反正弦轉換法 524

9.5.4  倒數轉換法 526

 

 

第10章  相關分析 

 

10.1  相關分析概述 528

10.1.1  相關關系 528

10.1.2  相關關系類型 529

10.1.3  相關分析 530

10.2  積差相關分析 530

10.2.1  用散點圖進行積差相關分析 531

10.2.2  積差相關系數的計算 533

10.2.3  積差相關系數的合並 536

10.3  等級相關分析 538

10.3.1  斯皮爾曼等級相關 538

10.3.2  肯德爾等級相關 544

10.4  質與量相關分析 552

10.4.1  點二列相關 552

10.4.2  二列相關 555

10.4.3  多列相關 559

10.5  品質相關分析 560

10.5.1  四分相關 561

10.5.2  Φ相關 563

10.5.3  列聯相關 566

10.6  一對多的相關分析 569

10.6.1  復相關分析 569

10.6.2  偏相關分析 573

10.6.3  半偏相關分析 580

 

 

第11章  回歸分析 

 

11.1  回歸分析概述 582

11.1.1  回歸分析的概念 582

11.1.2  回歸分析的分類與主要研究內容 583

11.2  一元線性回歸分析 583

11.2.1  用散點圖進行相關與回歸分析 583

11.2.2  一元線性回歸方程參數的計算 586

11.2.3  一元線性回歸方程的方差分析 588

11.2.4  一元線性回歸方程的F檢驗 590

11.2.5  相關系數與回歸系數的t檢驗 591

11.2.6  決定系數的計算與關系討論 593

11.2.7  應用一元線性回歸方程進行預測 595

11.2.8  使用“回歸”分析工具進行

綜合分析 597

11.2.9  建立積差相關系數臨界值表 598

11.3  多元線性回歸分析 599

11.3.1  用MDETERM函數進行多元線性

回歸 599

11.3.2  用規劃求解方法進行多元線性

回歸 602

11.3.3  用LINEST函數進行多元線性

回歸 605

11.3.4  多元線性回歸方程的方差分析 607

11.3.5  多元線性回歸偏回歸系數的

t檢驗 608

11.3.6  多元線性回歸偏回歸系數的

F檢驗 609

11.3.7  多元線性回歸偏回歸平方和的

F檢驗 611

11.3.8  使用“回歸”分析工具進行

綜合分析 613

11.3.9  多元線性回歸方程自變量的

選擇 614

11.3.10  偏回歸系數標準化及標準

回歸方程 616

11.4  非線性回歸分析 619

11.4.1  指數函數模型的回歸分析 619

11.4.2  對數函數模型的回歸分析 623

11.4.3  乘冪函數模型的回歸分析 625

11.4.4  倒數函數模型的回歸分析 628

11.4.5  雙曲線函數模型的回歸分析 630

11.4.6  生長曲線函數模型的回歸分析 631

11.4.7  多項式函數模型的回歸分析 634

11.4.8  規劃求解生長曲線參數 636

11.4.9  多元非線性回歸分析 638 

第12章  非參數檢驗

 

12.1  非參數檢驗概述 643

12.2  配對樣本的符號檢驗 644

12.2.1  符號檢驗的二項分佈原理 644

12.2.2  小樣本時的臨界值檢驗法 646

12.2.3  大樣本時的Z檢驗法 648

12.3  配對樣本的符號秩檢驗 650

12.3.1  小樣本時的臨界值檢驗法 651

12.3.2  小樣本時的精確概率查表法 652

12.3.3  大樣本時的Z檢驗法 654

12.3.4  製作符號秩檢驗界域表 655

12.4  獨立樣本的秩和檢驗 656

12.4.1  小樣本時的臨界值檢驗法 656

12.4.2  大樣本時的Z檢驗法 658

12.4.3  製作秩和檢驗臨界值表 661

12.5  獨立樣本的中數卡方檢驗 662

12.5.1  兩組樣本的中數卡方檢驗 662

12.5.2  多組樣本的中數卡方檢驗 664

12.6  秩次方差檢驗 667

12.6.1  獨立樣本的單向秩次方差分析 667

12.6.2  配對樣本的雙向秩次方差分析 671

 

第13章  主成分分析與因子分析 

 

13.1  主成分分析理論 674

13.1.1  什麽是主成分分析 674

13.1.2  主成分分析的基本原理 675

13.1.3  主成分分析的計算步驟 676

13.2  主成分分析實例 678

13.2.1  兩變量的主成分分析 678

13.2.2  多變量的主成分分析 688

13.3  因子分析理論 701

13.3.1  什麽是因子分析 701

13.3.2  因子分析的基本原理 702

13.3.3  因子分析的計算步驟 706

13.4  因子分析實例 706

13.4.1  兩變量的因子分析 706

13.4.2  多變量的因子分析 715

 

第14章  聚類分析 

 

14.1  聚類分析原理 723

14.1.1  聚類分析概述 723

14.1.2  數據矩陣 724

14.1.3  數據處理和變換 724

14.1.4  聚類距離 727

14.1.5  相似系數 734

14.2  直接聚類法 736

14.2.1  直接聚類法原理 736

14.2.2  直接聚類法實例 737

14.3  系統聚類法 741

14.3.1  系統聚類法原理 741

14.3.2  系統聚類距離公式 742

14.3.3  系統聚類法實例 749

14.4  動態聚類法 756

14.4.1  動態聚類法原理 756

14.4.2  動態聚類法實例 757

14.5  模糊聚類法 761

14.5.1  模糊聚類法原理 761

14.5.2  模糊聚類法實例 763

14.6  有序樣品聚類法 771

14.6.1  有序樣品聚類法原理 771

14.6.2  有序樣品聚類法實例 772

 

 

 

 

 

 

 

 

第15章  判別分析 

 

15.1  距離判別 776

15.1.1  距離判別原理 776

15.1.2  距離判別實例 778

15.2  貝葉斯判別 789

15.2.1  貝葉斯判別原理 789

15.2.2  貝葉斯判別實例 790

15.3  費希爾判別 792

15.3.1  費希爾判別原理 792

15.3.2  費希爾判別實例 793

 

第16章  預測分析 

 

16.1  移動平均法 797

16.1.1  移動平均法原理 797

16.1.2  移動平均法實例 798

16.2  指數平滑法 801

16.2.1  指數平滑法原理 801

16.2.2  指數平滑法實例 802

16.3  季節變動預測法 807

16.3.1  平均數趨勢法 807

16.3.2  趨勢比率法 810

16.3.3  環比法 812

16.3.4  移動平均法 815

16.3.5  虛擬變量回歸法 817

16.4  灰色預測模型 819

16.4.1  GM(1,1)預測分析 820

16.4.2  GM(1,N)預測分析 824

16.5  馬爾可夫預測法 827

16.5.1  馬爾可夫預測法原理 827

16.5.2  馬爾可夫預測法實例 828

 

第17章  層次分析 

 

17.1  層次分析法的原理 832

17.2  層次分析法實例 835

 

參考文獻 842