目錄
Excel統計分析與應用教程第1章Excel基礎1
1.1數據和統計分析1
1.1.1數據、統計和統計數據1
1.1.2統計分析軟件2
1.2Excel簡介3
1.2.1Excel的功能3
1.2.2Excel的啟動和退出4
1.2.3Excel的工作窗口5
1.2.4Excel的功能區7
1.2.5Excel的分析工具9
1.3Excel數據的編輯與修飾10
1.3.1新建工作簿10
1.3.2輸入數據11
1.3.3數據的編輯14
1.3.4工作表的管理16
1.3.5修飾工作表17
1.4Excel公式與引用18
1.4.1公式的組成18
1.4.2公式的編輯19
1.4格引用21
1.5Excel函數23
1.5.1函數的組成23
1.5.2函數的使用24
1.6Excel圖表25
1.6.1圖表的創建25
1.6.2圖表的編輯26
習題28Excel統計分析與應用教程目錄第2章Excel的數據處理29
2.1數據收集和整理29
2.1.1數據收集29
2.1.2數據收集工具32
2.1.3利用Excel的開發工具設計調查問卷35
2.1.4調查問卷的回收與數據編碼38
2.1.5數據審核40
2.2Excel數據輸入41
2.2.1關於數據輸入41
2.2.2數據有效性41
2.3數據分析44
2.3.1數據的排序和分組44
2.3.2數據的篩選45
2.3.3數據的次數分布47
2.3.4數據的頻數分布49
2.3.5數據的圖表制作50
習題57第3章描述性統計分析58
3.1集中趨勢的測定與分析58
3.1.1集中趨勢的測定內容58
3.1.2用Excel函數描述集中趨勢59
3.2離中趨勢的測定與分析63
3.2.1離中趨勢的測定內容63
3.2.2用Excel函數計算方差和標準差64
3.3描述總體分布形態的統計量65
3.3.1描述總體分布形態的測定內容65
3.3.2用Excel函數描述總體分布形態66
3.4使用數據分析工具進行描述統計分析67
3.4.1加載分析工具67
3.4.2描述統計工具的使用68
習題70第4章抽樣分布與幾種重要的分布72
4.1概率72
4.1.1概率分布72
4.1.2概率分布與頻率分布的關繫74
4.2用Excel模擬抽樣過程77
4.2.1用Excel模擬抽樣過程的常用方法77
4.2.2周期抽樣法79
4.2.3隨機抽樣法79
4.3抽樣分布81
4.3.1抽樣推斷的幾個基本概念81
4.3.2樣本的抽樣分布與總體分布的關繫82
4.3.3抽樣分布定理84
4.4幾種重要的分布85
4.4.1二項分布85
4.4.2正態分布88
4.4.3學生t分布93
4.4.4卡方分布95
4.4.5F分布98
習題100第5章參數估計102
5.1參數估計概述102
5.1.1點估計102
5.1.2區間估計102
5.1.3估計量的評價標準103
5.2總體均值區間估計103
5.2.1總體方差已知或大樣本情況下的估計103
5.2.2總體方差未知且為小樣本情況下的估計106
5.2.3重復抽樣情況下的總體比例區間估計109
5.3必要樣本容量計算110
5.3.1總體方差已知或大樣本情況下的必要樣本容量計算110
5.3.2重復抽樣總體比例已知情況下的必要樣本容量計算111
5.4總體方差區間估計112
習題114第6章假設檢驗115
6.1假設檢驗的基本思想與基本步驟115
6.1.1假設檢驗的基本思想115
6.1.2原假設與備擇假設116
6.1.3顯著性水平與檢驗統計量116
6.1.4決策規則118
6.2單個總體假設檢驗120
6.2.1總體方差已知或大樣本情況下的均值假設檢驗120
6.2.2總體方差未知且為小樣本情況下的均值假設檢驗123
6.2.3總體比例假設檢驗131
6.3總體方差假設檢驗133
6.3.1總體方差假設檢驗的基本內容133
6.3.2總體方差雙側檢驗134
6.3.3總體方差單側檢驗136
6.4兩個總體均值之差假設檢驗138
習題143第7章方差分析145
7.1方差分析簡介145
7.2單因素方差分析145
7.2.1單因素方差分析的構想146
7.2.2檢驗模型148
7.2.3方差分析表151
7.3雙因素方差分析153
7.3.1無重復雙因素方差分析153
7.3.2可重復雙因素方差分析154
習題160第8章相關分析162
8.1相關分析簡介162
8.2雙變量相關分析164
8.2.1散點圖164
8.2.2相關繫數168
8.2.3相關分析工具171
8.3Spearman秩相關分析173
8.3.1Spearman秩相關分析簡介173
8.3.2Spearman秩相關分析實例174
8.4多重相關及偏相關分析176
8.4.1多重相關及偏相關分析簡介176
8.4.2多重相關及偏相關分析實例177
習題180第9章回歸分析181
9.1回歸分析簡介181
9.1.1回歸分析的基本思想181
9.1.2回歸分析的功能181
9.1.3利用Excel進行回歸分析182
9線性回歸分析186
9.2線性回歸的建模思想186
9.2.2參數β0和β1的小二乘估計187
9.2.3利用Excel線性回歸分析188
9線性回歸預測196
9.3.1參數估計196
9.3.2模型檢驗197
9.3.3回歸預測模型的預測200
9線性回歸分析200
9.4線性回歸模型201
9.4回歸模型的參數估計202
9.4.線性回歸方程的評價202
9.4.4利用Excel線性回歸分析204
9.5非線性回歸分析208
9.5.1常見的幾種非線性回歸分析和預測模型208
9.5.2利用Excel實現非線性回歸分析209
習題212第10章聚類分析214
10.1相似性度量214
10.1.1樣本相似性度量214
10.1.2變量相似性度量216
10.2繫統聚類216
10.2.1繫統聚類的基本思想216
10.2.2類間距離與繫統聚類法217
10.3上機實踐219
習題245第11章主成分分析與因子分析246
11.1主成分分析246
11.1.1主成分分析的基本思想246
11.1.2主成分分析的基本原理246
11.1.3求解主成分247
11.1.4主成分的性質248
11.1.5主成分的現實解釋248
11.2因子分析251
11.2.1因子分析的數學模型252
11.2.2公共因子與原始變量的關繫252
11.2.3用主成分分析法求因子載荷矩陣253
11.2.4因子旋轉254
11.3上機實踐255
11.3.1主成分分析255
11.3.2因子分析263
習題264第12章Excel綜合案例266
12.1Excel在農業生產中的應用266
12.2Excel在醫學中的應用268
12.3Excel在社會科學中的應用270
12.4Excel在經濟管理中的應用273
習題281附錄AExcel統計函數286參考文獻305

前言
Excel統計分析與應用教程隨著社會信息化水平的不斷提高，數據呈現爆炸式增長，“大數據”時代到來了，數據滲透到每個行業和業務領域，人們生活的方方面面都存在大量的數據。如何從紛繁復雜的數據中找出有用的信息，分析數據的規律，讓冰冷的數據“說話”，成為推動“大數據”研究的動力。研究者要在大量的數據中找出關鍵點，分析其規律，就必須借助各種各樣的統計方法和技巧。因此，掌握一定的數據統計方法和技巧，成為數據研究者的技能，特別是對於需要經常和數據打交道的人或者從事統計分析工作的人，不僅需要掌握統計分析的專業知識，還需要學會使用統計分析工具。
在“大數據”時代，由於數據的復雜性，統計方法也各不相同，與統計方法相應的統計分析軟件也層出不窮。在眾多的統計分析軟件中，Excel不僅具備豐富的統計和分析功能，而且對於大多數學習者而言，Excel入門簡單，使用方便，所以它受到廣泛的歡迎。通過Excel的數據鏈接功能和工作表函數，可以對各種數據進行統計分析。
本書基於Excel的統計函數和分析工具，通過大量的教學案例，幫助學習者掌握統計分析知識，學會對各種類型數據進行統計分析。在知識安排上，本書注重實踐，通過實際問題的引入和解決來引導讀者完成學習的過程。書中通過大量的實例，將抽像、復雜的概念和理論通過分析軟件直觀地展現出來，以提高讀者解決實際問題的能力。通過對本書的學習，讀者不僅可以掌握數據統計分析的理論知識，還可以掌握使用Excel解決實際問題的方法。
本書內容分為3部分，共12章。第1部分(第1章和第2章)主要介紹Excel的基礎知識，讓讀者對Excel有總體的了解；第2部分(第3～11章)以統計分析理論為主線，介紹Excel在統計分析中的具體應用,主要包括描述性統計分析、抽樣分布與幾種重要的分布、參數估計、假設檢驗、方差分析、相關分析、回歸分析、聚類分析、主成分分析與因子分析，使讀者掌握常用的統計分析方法；第3部分(第12章)通過4個綜合案例對前面各章內容加以融會貫通，以深化學習者對知識的理解。
本書編者是長期在高校從事教學的一線教師，有豐富的統計分析相關課程的教學經驗。本書編寫分工如下: 李春蓉(第1章和第2章)、楊翠芳(第3章和第4章)、陳傑(第5章和第6章)、高賢強(第7章和第8章)、史召峰(第10章和第11章)、張著(第9章和第12章)，主編為高賢強、張著，副主編為楊翠芳、陳傑。作者在編寫本書的過程中參考了一些相關圖書，在此向有關作者表示誠摯的感謝！本書的出版得到了全國高等院校計算機基礎教育研究會計算機基礎教育教學研究項目的支持(項目編號: 2018AFCEC134)。
本書可作為高等學校統計分析相關課程的教材，也可作為統計分析領域從業人員的自學參考書和培訓教材。
盡管編者盡了的努力，但書中仍然難免有不盡人意之處，懇請廣大讀者指正。

編者2019年4月

第5章第5章參 數 估 計在實際應用中，人們常常需要根據手中的數據分析或推斷全部數據的本質，即根據樣本推斷總體的分布或數字特征等，這需要統計推斷的相關知識。所謂統計推斷是指根據樣本對總體分布或分布的數字特征等作出合理的推斷。統計推斷是數理統計學的一個重要分支。
本章介紹的參數估計是統計推斷的重要內容之一。
5.1參數估計概述
參數估計是用樣本統計量估計總體參數。例如，用樣本均值x-估計總體均值μ，用樣本方差s2估計總體方差σ2，等等。如果用θ表示總體參數，參數估計也就是如何用樣本統計量來估計總體參數θ。
用來估計總體參數的統計量稱為估計量，用符號θ^表示。樣本均值、樣本比例、樣本方差等都可以是一個估計量。用來估計總體參數時計算出來的估計量的具體數值稱為估計值。例如，要估計一個地區的平均收入，不太可能將該地區的所有人的收入都統計一遍。一般的做法是: 選擇一部分人員進行收入統計，這部分被選擇人員的收入數據就是樣本數據。通過樣本數據可以求出樣本均值x-，它就是一個估計量θ^。假設計算出的樣本均值為10 000，則這個10 000就是估計值。一般而言，參數估計可以分為點估計和區間估計兩種。
5.1.1點估計
點估計是依據樣本估計總體分布中所含的未知參數或未知參數的函數，通常它們是總體的某個特征值，如期望、方差等。點估計問題就是要構造一個隻依賴於樣本的量，作為總體的估計量。在用點估計值代表總體參數值的同時，還必須給出點估計值的可靠性，也就是說，必須能說明點估計值與總體參數值的接近程度。
5.1.2區間估計
區間估計是利用抽取的樣本，根據一定的正確度與精確度的要求，構造出適當的區間，作為總體分布的未知參數的值所在範圍的估計。與點估計不同，進行區間估計時，根據樣本統計量的抽樣分布可以對樣本統計量與總體參數的接近程度給出一個概率度量。在區間估計中，由樣本統計量所構造的總體參數的估計區間稱為置信區間。其中，區間的小值稱為置信下限，用θ1表示；區間的值稱為置信上限，用θ2表示。總體參數落在［θ1,θ2］區間的概率為p=1-α，稱為置信水平，α稱為顯著性水平。置信區間表明總體參數的誤差範圍，置信水平表明總體參數落在置信區間的概率。
Excel統計分析與應用教程第5章參數估計5.1.3估計量的評價標準
在參數估計時，可以構造很多估計量，但不是每一個估計量都一樣優良。通常評價估計量的標準有無偏性、有效性和一致性。
(1) 無偏性。無偏估計量的抽樣分布的數學期望等於被估計的總體參數。設總體參數為θ，所選估計量為θ^，如果Eθ^=θ,則稱θ^為θ的無偏估計量。
(2) 有效性。一個無偏的估計量並不意味著就非常接近被估計的參數，它還必須符合與總體參數的離散程度充分小這一標準。同一總體參數的兩個無偏估計量中標準差更小的有效性更高。
(3) 一致性。指隨著樣本量的增大，點估計量的值越來越接近被估計的總體參數。換言之，一個大樣本給出的估計量要比一個小樣本給出的估計量更接近總體參數。
5.2總體均值區間估計
總體均值區間估計是一種常見的參數估計問題，例如，根據抽樣調查結果對某公司的平均利潤率進行估計，根據參與調查學生的每月平均生活消費估計在校大學生月平均生活費等。總體參數的估計區間通常是由樣本統計量加減抽樣誤差而得到的。根據樣本統計量的抽樣分布，能夠對樣本統計量與總體參數的接近程度給出一個概率度量。
在對總體均值進行區間估計時，需要考慮總體是否為正態分布、總體方差是否已知、用於構造估計量的樣本是大樣本還是小樣本等情況。
5.2.1總體方差已知或大樣本情況下的估計
當總體服從均值為μ、方差為σ2的正態分布時，取自該總體的隨機樣本x1，x2，…，xn的樣本均值x-應服從均值為μ、方差為σ2/n的正態分布，即x-～Nμ,σ2n。對x-進行標準化，對應的z值為z=x--μσ/n～N(0,1)。在給定顯著性水平α或置信水平p=1-α的條件下，有p(-zα/2步驟如下: 
(1) 打開“第5章參數估計.xlsx”文件，選擇“例5.1”工作表，在A1:格區域中輸入本例給出的數據。
(2) 在格中輸入“標準誤差”，在格中輸入公式“=B2/SQRT(B3)”。
圖5.1例5.1終結果
(3) 在格中輸入“Z值”，在格中輸入公式“=ABS(NORM.S.INV((1-B4)/2))”。
(4) 在格中輸入“極限誤差”，在格中輸入公式“=B6B7”。
(5) 在A格中輸入“置信上限”，在B格中輸入公式“=B1 B9”。在A格中輸入“置信下限”，在B格中輸入公式“=B1-B9”。
終結果如圖5.1所示。
例5.2某快遞公司要分析季度日均快遞件數，為此從季度抽取了40天的快遞件數，如圖5.2所示。在95%的置信水平下，求該快遞公司季度日均快遞件數的置信區間。
圖5.2從季度抽取的40天的快遞件數
步驟如下: 
(1) 打開“第5章參數估計.xlsx”文件，選擇“例5.2”工作表，在A1:格區域中輸入圖5.2中的數據。
(2) 計算樣本均值。在格中輸入“樣本均值”，在格中輸入公式“=AVERAGE(A1:J4)”。
(3) 計算標準差。在格中輸入“標準差”，在格中輸入公式“=STDEV(A1:J4)”。
(4) 計算樣本容量。在格中輸入“樣本容量”，在格中輸入公式“=COUNT(A1:J4)”。
(5) 在格中輸入“置信水平”，在格中輸入0.95。
(6) 計算標準誤差。在A格中輸入“標準誤差”，在B格中輸入公式“=B7/SQRT(B8)”。
(7) 計算Z值。在A格中輸入“Z值”，在B格中輸入公式“=ABS(NORM.S.INV((1-B9)/2))”。
(8) 計算極限誤差。在A格中輸入“極限誤差”，在B格中輸入公式“=B11B12”。
(9) 計算置信上限與置信下限。在A格中輸入“置信上限”，在B格中輸入公式“=B6 B14”。在A格中輸入“置信下限”，在B格中輸入公式“=B6-B14”。
終結果如圖5.3所示。
圖5.3例5.2終結果
由上述兩個例子可以發現，計算總體方差已知或大樣本情況下的均值置信區間的步驟幾乎是一樣的，可概括如下:
(1) 利用給定的數據計算樣本均值、標準差、樣本容量和置信水平。
(2) 根據步驟(1)中的標準差和樣本容量計算標準誤差，計算公式為標準誤差=標準差/SQRT(樣本容量)(3) 根據步驟(1)中的置信水平計算Z值，計算公式為Z值=ABS(NORM.S.INV((1-置信水平)/2))(4) 根據步驟(2)求出的標準誤差和步驟(3)求出的Z值計算極限誤差，計算公式為極限誤差=標準誤差×Z值(5) 根據步驟(1)中的樣本均值和步驟(4)求出的極限誤差計算置信區間，計算公式為置信上限=樣本均值 極限誤差
置信下限=樣本均值-極限誤差5.2.2總體方差未知且為小樣本情況下的估計
小樣本是指樣本容量n<30的情況。本節討論的情況的前提為總體方差未知並且樣本容量小於30。在這種情況下，可以使用樣本標準差s代替未知的總體標準差，新的統計量服從自由度為n-1的學生t分布，即t=x--μs/n～t(n-1)可以根據學生t分布來估計總體均值。在給定置信水平的情況下,總體均值μ的估計區間為x--tα/2sn,x- tα/2sn例5.3在例5.2的基礎上，如果假設季度僅抽取了20天的快遞件數，數據如圖5.4所示。在95%的置信水平下，求該快遞公司季度日均快遞件數的置信區間。
圖5.4季度抽取的20天的快遞件數
請注意，本例與例5.2的不同點在於減少了樣本數量，變成小樣本的情況。
步驟如下: 
(1) 打開“第5章參數估計.xlsx”文件，選擇“例5.3”工作表，在A1:格區域中輸入圖5.4中的數據。
(2) 計算樣本均值。在格中輸入“樣本均值”，在格中輸入公式“=AVERAGE(A1:J2)”。
(3) 計算標準差。在格中輸入“標準差”，在格中輸入公式“=STDEV(A1:J2)”
(4) 計算樣本容量。在格中輸入“樣本容量”，在格中輸入公式“=COUNT(A1:J2)”。
(5) 在格中輸入“置信水平”，在格中輸入0.95。
(6) 計算標準誤差。在格中輸入“標準誤差”，在格中輸入公式“=B5/SQRT(B6)”。
(7) 計算T值。在A格中輸入“T值”，在B格中輸入公式“=T.INV(1-B7,B6-1)”。
(8) 計算極限誤差。在A格中輸入“極限誤差”，在B格中輸入公式“=B9B10”。
(9) 計算置信上限與置信下限。在A格中輸入“置信上限”，在B格中輸入公式“=B4 B12”。在A格中輸入“置信下限”，在B格中輸入公式“=B4-B12”。
終結果如圖5.5所示。
圖5.5例5.3終結果
下面使用Excel提供的“函數”對話框計算置信區間。
例5.4圖5.6為某地區15戶家庭年收入數據(單位)。在95%的置信水平下，求該地區家庭年收入均值的置信區間。
圖5.6家庭年收入數據
步驟如下: 
(1) 打開“第5章參數估計.xlsx”文件，選擇“例5.4”工作表，在A1:格區域中輸入圖5.6中的數據。
(2) 計算樣本均值。在格中輸入“樣本均值”，在格中輸入公式“=AVERAGE(A1:E3)”。
(3) 計算標準差。在格中輸入“標準差”，在格中輸入公式“=STDEV(A1:E3)”
(4) 計算樣本容量。選擇格中輸入“樣本容量”，在格中輸入公式“=COUNT(A1:E3)”。
(5) 在格中輸入“置信水平”，在格中輸入0.95。
(6) 計算標準誤差。在A格中輸入“標準誤差”，在B格中輸入公式“=B5/SQRT(B6)”。
(7) 計算T值。在A格中輸入“T值”。選中B格，在功能區中單擊按鈕，打開“插入函數”對話框，如圖5.7所示，在“或選擇類別”下拉菜單中選擇“全部”選項，在“選擇函數”下拉菜單中找到TINV函數，單擊“確定”按鈕，打開TINV的“函數參數”對話框。在Probability文本框中輸入1-B8，在Deg_freedom文本框中輸入B7-1，如圖5.8所示，單擊“確定”按鈕，得到T值。
圖5.7“插入函數”對話框
圖5.8TINV的“函數參數”對話框
(8) 計算極限誤差。在A格中輸入“極限誤差”，在B格中輸入公式“=B10B11”。
(9) 計算置信上限與置信下限。在A格中輸入“置信上限”，在B格中輸入公式“=B5 B13”。在A格中輸入“置信下限”，在B格中輸入公式“=B5-B13”。
終結果如圖5.9所示。
圖5.9例5.4終結果
由上述兩個例子可以發現，計算總體方差未知且為小樣本情況下的均值置信區間的步驟與計算總體方差已知或大樣本情況下的均值置信區間的步驟幾乎一樣。不同之處在於: 在總體方差未知或小樣本情況下需要使用TINV函數計算T值，而在總體方差已知或大樣本情況下使用NORMSINV函數計算Z值。總體方差未知且小樣本情況下的均值置信區間的通用計算步驟如下: 
(1) 利用給定的數據計算樣本均值、標準差、樣本容量和置信水平。
(2) 根據步驟(1)中的標準差和樣本容量計算標準誤差，計算公式為
標準誤差=標準差/SQRT(樣本容量)
(3) 根據步驟(1)中的置信水平和樣本容量計算T值，計算公式為
T值=TINV(1-置信水平，樣本容量-1)
(4) 根據步驟(2)求出的標準誤差和步驟(3)求出的T值計算極限誤差，計算公式為
極限誤差=標準誤差×Z值
(5) 根據步驟(1)中的樣本均值和步驟(4)求出的極限誤差計算置信區間，計算公式為置信上限=樣本均值 極限誤差
置信下限=樣本均值-極限誤差5.2.3重復抽樣情況下的總體比例區間估計
總體比例分布直接來源於二項分布，但是當n較大時，概率計算比較復雜。根據中心極限定理，隨著樣本容量的增加，二項分布逐漸接近正態分布。一般來說，如果np≥5並且n(1-p)≥5，總體比例的抽樣分布可以用正態分布近似。在給定置信水平的情況下置信區間為p-zα/2p(1-p)n,p zα/2p(1-p)n其中，p為樣本比例，n為樣本容量。
例5.5某市隨機抽取了當年300名新生兒，其中有183名男嬰。在95%的置信水平下，求該市男嬰比例的置信區間。