excel在統(tǒng)計學中應用

1、Excel在統(tǒng)計學中應用,第一節(jié) 用Excel做統(tǒng)計圖,一、 用Excel做頻率分布表和直方圖一般統(tǒng)計數據有兩大類，即定性數據和定量數據。我們這里就不討論把定性數據轉換成定量數據了，而是主要學習定量數據的作圖。下面的例子說明如何利用Excel進行分組，并作頻率分布表和直方圖。[案例]2010年1月四十個大中城市房屋銷售價格指數如下：110.2、109.6、102.6、102.6、105.9、102.7、105、104.5、10

2、5.9、108.8、110.4、112.7、108、105.2、103.2、108.9、107.1、105.7、106、104.9、103.9、108.8、110.6、120.6、105.5、131.8、108.8、105.3、109.6、110.3、110、105.1、105.9、113、106.2、99.5、104.8、103.1、105.9、103.8（1）據此編制分布數列（提示：價格指數是連續(xù)變量）；（2）計算向上累計頻數（

3、率）；（3）畫出次數分布直方圖。,[步驟]第一步：在Excel界面中輸入40個企業(yè)的數據，從上到下輸入A列（也可以分組排列）。第二步：選擇“工具”下拉菜單，如圖1所示。,第三步：選擇“數據分析”選項，如果沒有該功能則要先行安裝?！皵祿治觥钡木唧w安裝方法，選擇“工具”下拉菜單中“加載宏”，在出現的選項中選擇“分析工具庫”，“確定”就可自動安裝。第四步：在分析工具中選擇“直方圖”，如圖2所示。,第五步：當出現“直方圖”對話框時，在

4、“輸入區(qū)域”方框內鍵入A2:A41或＄A＄2:＄A＄41（“＄”符號表示的是絕對地址起，到固定單元格的作用），40個數據已輸入該區(qū)域內，如果是分組排列的，就應該選擇整個分組區(qū)域。在“接收區(qū)域”方框內鍵入C2:C9或＄C＄2: ＄C＄9，所有數據分成8組（主要根據資料的特點，決定組數、組距和組限），把各組的上限輸入該區(qū)域內。在“輸出區(qū)域”方框內鍵入E2或＄E＄2，也可以在其他位置重新建表。對話框中，還選擇“累積百分率”、“圖表輸出”如圖所

5、示。然后點擊“確定”。對話框內主要選項的含義如下：輸入區(qū)域：在此輸入待分析區(qū)域的單元格范圍。,接收區(qū)域（可選）：在此輸入接收區(qū)域的單元格范圍，該區(qū)域包含一組可選的用來計算頻數的邊界值。這些值應當按升序排列。只要存在，Excel將統(tǒng)計在各個相鄰邊界值之間的數據出現的次數。如果省略此處的接收區(qū)域，Excel將在數據組的最小值和最大值之間建立一組平滑分布的接收區(qū)間。,標志：如果輸入區(qū)域的第一行或第一列中包含標志項，則選中此復選框；如果輸入

6、區(qū)域沒有標志項，則清除此復選框，Excel將在輸出表中生成適宜的數據標志。輸出區(qū)域：在此輸入計算結果顯示的單元格地址。如果不輸入具體位置將覆蓋已有的數據，Excel會自動確定輸出區(qū)域的大小并顯示信息。柏拉圖：選中此復選框，可以在輸出表中同時顯示升序、將序排列頻率數據。如果此復選框被清除，Excel將只按升序來排列數據。累積百分比：選中此復選框，可以在輸出結果中添加一列累積百分比數值，并同時在直方圖表中添加累積百分比折線。如果清除此

7、選項，則會省略以上結果。圖表輸出：選中此復選框，可以在輸出表中同時生成一個嵌入式直方圖表。,[結果]操作得到的結果如圖所示。通常包括三列和一個頻數分布圖，第一列是數值的區(qū)域范圍，第二列是數值分布的頻數（不是頻率），第三列是頻數分布的累積百分比。直方圖是用矩形高度來表示頻數分布的圖形。繪制直方圖時，將所研究的變量放在橫軸上，頻數、頻率放在縱軸上。每組的頻數、頻率在圖上就是一個長方形，長方形的底在橫軸上，寬度是組距，長方形的高就是對應

8、的頻數或頻率。圖13-4中是一個條形圖，而不是直方圖，若要把它變成直方圖，可按如下操作：用鼠標左鍵單擊圖中任一直條形，然后右鍵單擊，在彈出的快捷菜單中選取“數據系列”格式，彈出數據系列格式對話框。在對話框中選擇“選項”標簽，把“分類間距”寬度改為零，按確定后即可得到直方圖，如圖所示。,,,二、 用Excel做常用統(tǒng)計圖Excel提供的統(tǒng)計圖有多種，包括柱形圖、條形圖、折線圖、餅圖、散點圖、面積圖、環(huán)形圖、雷達圖、曲面圖、氣泡圖、股價

9、圖、圓柱圖、圓錐圖等，各種圖的作法大同小異，可根據需要的類型進行適當選擇。（一） 餅圖的繪制餅圖是用圓形以及園內扇形的面積來表示數值大小的圖形，主要用于表示總體中各組成部分所占的比例，常用于研究結構性問題。[案例]某地區(qū)××年因違法被捕的男性情況如下所示，根據資料利用Excel繪制餅圖。,[步驟]先把數據輸入到工作表中，如圖所示，然后按下面的步驟操作：,第一步：選擇“插入”下拉菜單中的“圖表”選項。第二步

10、：在圖表類型中選擇“餅圖”，然后在子圖表類型中選擇一種類型，這里我們選用系統(tǒng)默認的方式。然后單擊下一步按鈕，打開源數據對話框。如圖7所示。,第三步：在圖標源數據對話框中填入數據所在區(qū)域，單擊下一步，在圖表選項中，對“標題”、“圖例”和“數據標志”適當處理。如果要對圖形修改，可以用鼠標雙擊圖表，然后用鼠標雙擊需要修改的部分，并進行修改。[結果]即可得如圖所示的餅圖。,（二）折線圖的繪制折線圖主要用于比較數據變動的方向和趨勢，表現數據

11、在不同時期發(fā)展變化的不同趨勢。[案例]根據福建省2005年到2009年間三大產業(yè)的總產出情況繪制折線圖，描述這幾年來三大產業(yè)總產值的變化趨勢。,[步驟]第一步：資料輸入工作表后，選擇“插入”下拉菜單，再選擇“圖表”選項。第二步：在圖表類型中選擇“折線圖”，然后在子圖表類型中選擇一種類型，這里我們選用如圖所示的方式。然后單擊“下一步”按鈕，打開源數據對話框。,第三步：在源數據對話框中，“數據區(qū)域”中輸入相關資料（可用鼠標點擊并框定

12、數據區(qū)域）。再在“系列”的“分類（X）軸標志”區(qū)域輸入年份區(qū)域，如圖所示第四步：資料輸入后的下一步，進入“圖表選項”。分別對“標題”、“坐標軸”、“網格線”、“圖例”、“數據標志”和“數據表”等選項進行設置，當然設置各選項時根據需要進行取舍。最后點擊“完成”，就在工作表中得到折線圖。[結果]經過上述各步驟，在工作表中得到折線圖：,第二節(jié) 用Excel計算描述統(tǒng)計量,一、利用“數據分析”功能[案例]根據某縣的一項關于鄉(xiāng)鎮(zhèn)企業(yè)工資

13、的調查，下面是40名雇員的月工資按升冪排列的一個樣本，對該企業(yè)雇員工資進行描述統(tǒng)計分析。（單位：元）274 279 290 326 329 341 378 405 436 500 515 541 558 566 618 708 760 867 868 869 888 915 932 942 960 975 976 1014 1025 1096 1118 1166 1193 1194 1243 1277 1304 1327 1343 1

14、398 [步驟]第一步：在Excel的區(qū)域“A1:A40”的單元格中輸入數據，從上到下排成4列。第二步：選擇“工具”下拉菜單，再選擇“數據分析”選項第三步：在分析工具中選擇“描述統(tǒng)計”，如圖11所示,第四步：當出現對話框時，在“輸入區(qū)域”方框內鍵入A1:A40（或用鼠標選擇這區(qū)域），在“輸出選項”中選擇輸出區(qū)域（在此選擇C4），再選擇“匯總統(tǒng)計”（該選項給出全部描述統(tǒng)計量）。第五步：點擊“確定”鍵。其計算結果如圖所示。,二、

15、 利用“統(tǒng)計函數”工具計算我們可以用兩種方法來得到描述統(tǒng)計量，除上述“數據分析”功能計算外，還可采用Excel的函數工具計算。仍以該企業(yè)40名雇員工資為例進行計算。第一步：與上面操作相同，在Excel中輸入數據。第二步：選擇“插入”下拉菜單中的“函數”選項，如圖所示。第三步：出現的“插入函數”界面中，在“或選擇類別”選項中，選“統(tǒng)計”。再在“選擇函數”中，選“AVERAGE”（算術平均數），點擊“確定”如圖14所示。,13-13

16、,13-14,第四步：出現“AVERAGE函數參數”界面中，在“Number1”中鍵入A1:A40（或用鼠標選擇這區(qū)域），然后點擊“確定”，就能得出“算術平均數=817.85”。重復上述各步驟，還可計算“調和平均數”、“幾何平均數”、“樣本標準差”和“總體標準差”等統(tǒng)計量，只要分別選擇相應的函數。常用描述統(tǒng)計量函數如表A-4所示,第三節(jié) 用Excel進行抽樣,一、用Excel進行隨機抽樣當總體數量太大，不可能進行全面調查或者在某些情

17、況下不需要進行全面調查又需要了解總體的情況時必須采用抽樣。使用Excel進行抽樣，首先要對各個總體單位進行編號，編號可以按隨機原則，也可以按有關標志或無關標志，編號后，將編號輸入工作表。注：本功能需要使用Excel擴展功能，如果您的Excel尚未安裝數據分析，請依次選擇“工具” “加載宏”，在安裝光盤的支持下加載“數據分析庫”。加載成功后，才可以在工具的下拉菜單中看到“數據分析”選項。,[案例]假定有100個總體單位，每個總體給一個

18、編號，一共有100個編號，輸入工作表后，如圖所示,[步驟]先輸入各總體單位的編號，再按照下面的步驟操作：第一步： 單擊菜單欄中的“工具”選項，在下拉列表框中選擇“數據分析”選項，此時彈出“數據分析”對話框，分析工具選擇“抽樣”。如圖所示,第二步：單擊“確定”，彈出“抽樣”對話框，如圖所示,第三步：在輸入區(qū)域框中輸入總體單位編號所在單元格區(qū)域，在本例是$A$1:$J$10,實際操作中一般是用鼠標將編號所在區(qū)域選中即可。系統(tǒng)將從A列開始

19、抽取樣本，然后按順序抽取B列至J列。如果輸入區(qū)域的第一行或第一列為標志項（橫行標題或縱行標題），可單擊“標志”復選框。第四步：抽樣方法有周期和隨機兩種模式?！爸芷谀Ｊ健奔此^的等距抽樣，采用這種抽樣方法，需將總體單位數除以要抽取的樣本單位數，可得到取樣的周期間隔。這里我們假設要在100個總體單位中抽取10個，則在“間隔”框中輸入“10”。,“隨機模式”適用于純隨機抽樣、分類抽樣、整群抽樣和階段抽樣。采用純隨機抽樣，只需在“樣本數”

20、中直接輸入要抽取的樣本數即可；若用分類抽樣，必須先將總體按某一標志分類編號，然后再每一類中隨機抽取若干單位i，這種抽樣方法實際上是分組法和隨機抽樣方法的結合；整群抽樣也要將總體單位分類編號，然后按隨機原則抽取若干類作為樣本，對抽中的所有單位全部進行調查。第五步：制定輸出區(qū)域，這里我們鍵入“F12:F22”，然后單擊“確定”按鈕即可。[結果]10個隨機抽樣的樣本編號顯示在工作表的“F12:F22”區(qū)域,二、用Excel進行抽樣估計—

21、—求置信區(qū)間調用Excel中一些常用的“統(tǒng)計函數”可以直接進行抽樣推斷中的區(qū)間估計計算。下面結合案例來說明具體的操作步驟。[案例]為了解學校某班的統(tǒng)計學成績情況，對該班的全體同學隨機抽取13名作為調查樣本，統(tǒng)計抽取得到的樣本如 A列所示。假設該班學生的統(tǒng)計學成績總體上服從正態(tài)分布，用95%的置信水平估計該班學生統(tǒng)計學成績的置信區(qū)間。為構造區(qū)間估計的工作表，在工作表中先輸入以下內容：A列中輸入樣本數據，B列輸入各統(tǒng)計指標名稱，

22、C列中輸入計算公式，D列輸入計算的結果。,,自由度為樣本數 -1（n-1），上式有誤,[步驟]第一步：把樣本數據輸入A2:A14單元格。第二步：在D2中輸入公式“COUNT(A2:A14)”,得到計算結果“13”。“COUNT”是計數函數，得到樣本含量（n=13）。第三步：在D3中輸入“=AVERAGE(A2:A14)”，在D4中輸入“=STDEV(A2:A14)”在D5中輸入“=D4/SQRT(D2)”,在D6中輸入0.95，

23、在D7中輸入“=D2-1”，在D8中輸入“=TINV(1-D6，D7)”,在C9中輸入“=D8*D5”,在D10中輸入“=D3-D9”,在D11中輸入“=D3+D9”。再輸入每一個公式回車后，即可得到如表的結果。,自由度為n-1，有誤,[結果]從上面的結果我們可以知道，該班學生的統(tǒng)計學成績的置信上限是84.0039804，置信下限是61.53448112.計算結果表明，我們有95%的把握認為該班學生的統(tǒng)計學成績平均在61.53448

24、112-84.0039804之間。利用上面的統(tǒng)計指標，輸入不同的樣本數據，根據指標所在位置的變化對計算公式作相應的更改，則可以計算不同樣本的置信區(qū)間。,第四節(jié) 用Excel進行假設檢驗,假設檢驗包括一個正態(tài)總體的參數檢驗和兩個正態(tài)總體的參數檢驗。對于一個正態(tài)總體參數的檢驗，可以直接利用函數工具和輸入公式的方法計算相應的統(tǒng)計量，并進行檢驗。這里主要介紹如何利用Excel進行兩個正態(tài)分布總體的均值差檢驗。Excel中提供了四種假設檢驗

25、模式 ：平均值的成對二樣本分析、雙樣本等方差假設、雙樣本異方差假設和雙樣本平均差檢驗。其中較為常用的是后三種，所以這里就不介紹第一種?？梢杂靡詧D來說明后三種模式適用的條件。,[案例]設有A 、B兩種安眠藥，為了比較他們的治療效果。以X表示失眠病人服下A藥睡眠時間延長的時數；以Y表示失眠病人服下B藥睡眠時間延長的時數.現在獨立觀察20個病人，其中10個服下A藥，另10個服下B藥，延長時數的情況所圖中A列和B列所示。假設X與Y都服從正態(tài)分

26、布。試問，這兩種藥的療效是否有顯著的差異。,[步驟]第一步：選擇“工具”下拉菜單，再選擇“數據分析”選項第二步：在“分析工具”選擇“t檢驗：雙樣本異方差檢驗”（這里總體方差未知，且假設兩個對比樣本的方差不相等）如圖所示。,第三步：當出現對話框時，在“變量1的區(qū)域”方框內輸入A2:A11，實際操作中只要選中這部分區(qū)域即可；在“變量2的區(qū)域”方框內輸入B2:B11；在“假設平均差”方框內鍵入“0”；“”默認 為0.05；在“輸出選項”中

27、選擇“新工作表組”，如圖所示。待所有選項設置好后，單擊“確定”按鈕。,[結果]輸出結果如表所示,計算的P值為0.206253，大于0.05。所以不拒絕原假設，認為兩樣本的均值是相等的。即在本例中，認為A、B兩種藥品的療效沒有顯著性差異。,第五節(jié) 用Excel進行相關與回歸分析,一、相關分析 相關分析就是對兩個變量之間線性關系的描述和度量，利用Excel進行相關分析時，先要繪制散點圖大致描繪出兩個變量變化趨勢的關系形態(tài)，散點圖的做法前面

28、已經介紹，這里就不再贅言，如果是線性關系，再計算相關系數來度量兩個變量之間的關系強度。用Excel計算相關系數有兩種方法，一是直接利用相關系數函數計算，如“CORREL函數”和“PEARSON函數”；另一種是利用“數據分析”中的“相關系數”工具。這里主要介紹后者。,[案例]7個省區(qū)人均GDP（元）和建筑合同的價值（億元）資料如下表,根據資料數據先繪制散點圖如圖所示,從散點圖可以看出，兩個變量之間存在一定的正線性關系下面再用Excel計

29、算相關系數，并說明兩變量相關的方向和程度。[步驟]第一步：將人均GDP和建筑合同價值兩個變量的數據分別輸入到A2:A8和B2:B8區(qū)域。第二步：選擇“工具”下拉菜單，再選擇“數據分析”選項。第三步：在分析工具中選擇“相關系數”。第四步：出現對話框，在輸入區(qū)域中鍵入“A2:B8”，在“輸出區(qū)域”中選擇輸出區(qū)域，這里我們選擇輸入到“新工作表”，最后單擊“確定”按鈕。,[結果]由Excel計算得到的相關系數矩陣如圖所示，結合資料分

30、析，可以得到這7個省區(qū)的“人均GDP”和 “建筑合同價值”的相關系數為0.693511，屬于中度正相關,二、回歸分析相關分析的目的在于測度變量之間的關系強度，而大部分情況下，僅僅知道相關程度是遠遠不夠的，我們更希望能夠確定兩個或者多個變量之間具體的數學關系式，這就需要進行回歸分析。利用Excel可以很容易地進行回歸分析，包括一元線性回歸和多元線性回歸。[案例]20個家庭住房支出與年收入資料，如表所示,要求編制直線回歸方程，計算回歸的

31、標準誤差，并估計當家庭年收入為25時，住房支出的可能值。[步驟]第一步：選擇“工具”下拉菜單，再選擇“數據分析”項第二步：在分析工具中選擇“回歸”；第三步：彈出對話框，在“輸入Y的區(qū)域”方框內輸入A2:A21，在“輸入X的區(qū)域”方框輸入B2:B21，在“輸出區(qū)域”選擇“新工作表”，最后單擊“確定”即可。[結果]輸出結果如圖所示,Excel輸出的回歸結果包括以下幾個部分：第一部分是“回歸統(tǒng)計”，這部分給出了回歸分析中一些常用

32、統(tǒng)計量，包括相關系數（Multiple R）、判定系數（R Square）、調整的判定系數（Adjusted R Square）、標準誤差、觀測值的個數等，這里判定系數R=0.933511，說明回歸直線對數據的擬合優(yōu)度很高，根據判定系數的實際意義有：住房支出的差異有93%以上是年收入決定的，兩者有很強的線性關系；第二部分是“方差分析”，這部分給出的F檢驗的顯著性水平（Significance F）遠小于0.05表明回歸方程是顯著的；,