Excel在統(tǒng)計學(xué)中應(yīng)用剖析（課堂PPT）

1、一、 用Excel做頻率分布表和直方圖一般統(tǒng)計數(shù)據(jù)有兩大類，即定性數(shù)據(jù)和定量數(shù)據(jù)。我們這里就不討論把定性數(shù)據(jù)轉(zhuǎn)換成定量數(shù)據(jù)了，而是主要學(xué)習(xí)定量數(shù)據(jù)的作圖。下面的例子說明如何利用Excel進行分組，并作頻率分布表和直方圖。案例2010年1月四十個大中城市房屋銷售價格指數(shù)如下：110.2、109.6、102.6、102.6、105.9、102.7、105、104.5、105.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、1

2、09.6、110.3、110、105.1、105.9、113、106.2、99.5、104.8、103.1、105.9、103.8（1）據(jù)此編制分布數(shù)列（提示：價格指數(shù)是連續(xù)變量）；（2）計算向上累計頻數(shù)（率）；（3）畫出次數(shù)分布直方圖。 第三步：選擇“數(shù)據(jù)分析”選項，如果沒有該功能則要先行安裝。“數(shù)據(jù)分析”的具體安裝方法，選擇“工具”下拉菜單中“加載宏”，在出現(xiàn)的選項中選擇“分析工具庫”，“確定”就可自動安裝。 第四步：在分析工具中選擇“直方圖”，如圖2所示。 第五步：當(dāng)出現(xiàn)“直方圖”對話框時，在“輸入?yún)^(qū)域”方框內(nèi)鍵入A2:A41或A2:A41（“”符號表示的是絕對地址起，到固定單元格的作用

3、），40個數(shù)據(jù)已輸入該區(qū)域內(nèi)，如果是分組排列的，就應(yīng)該選擇整個分組區(qū)域。在“接收區(qū)域”方框內(nèi)鍵入C2:C9或C2: C9，所有數(shù)據(jù)分成8組（主要根據(jù)資料的特點，決定組數(shù)、組距和組限），把各組的上限輸入該區(qū)域內(nèi)。在“輸出區(qū)域”方框內(nèi)鍵入E2或E2，也可以在其他位置重新建表。對話框中，還選擇“累積百分率”、“圖表輸出”如圖所示。然后點擊“確定”。 對話框內(nèi)主要選項的含義如下： 輸入?yún)^(qū)域：在此輸入待分析區(qū)域的單元格范圍。 接收區(qū)域（可選）：在此輸入接收區(qū)域的單元格范圍，該區(qū)域包含一組可選的用來計算頻數(shù)的邊界值。這些值應(yīng)當(dāng)按升序排列。只要存在，Excel將統(tǒng)計在各個相鄰邊界值之間的數(shù)據(jù)出現(xiàn)的次數(shù)。如果

4、省略此處的接收區(qū)域，Excel將在數(shù)據(jù)組的最小值和最大值之間建立一組平滑分布的接收區(qū)間。 標(biāo)志：如果輸入?yún)^(qū)域的第一行或第一列中包含標(biāo)志項，則選中此復(fù)選框；如果輸入?yún)^(qū)域沒有標(biāo)志項，則清除此復(fù)選框，Excel將在輸出表中生成適宜的數(shù)據(jù)標(biāo)志。 輸出區(qū)域：在此輸入計算結(jié)果顯示的單元格地址。如果不輸入具體位置將覆蓋已有的數(shù)據(jù)，Excel會自動確定輸出區(qū)域的大小并顯示信息。 柏拉圖：選中此復(fù)選框，可以在輸出表中同時顯示升序、將序排列頻率數(shù)據(jù)。如果此復(fù)選框被清除，Excel將只按升序來排列數(shù)據(jù)。 累積百分比：選中此復(fù)選框，可以在輸出結(jié)果中添加一列累積百分比數(shù)值，并同時在直方圖表中添加累積百分比折線。如果清除

5、此選項，則會省略以上結(jié)果。 圖表輸出：選中此復(fù)選框，可以在輸出表中同時生成一個嵌入式直方圖表。 結(jié)果 操作得到的結(jié)果如圖所示。通常包括三列和一個頻數(shù)分布圖，第一列是數(shù)值的區(qū)域范圍，第二列是數(shù)值分布的頻數(shù)（不是頻率），第三列是頻數(shù)分布的累積百分比。 直方圖是用矩形高度來表示頻數(shù)分布的圖形。繪制直方圖時，將所研究的變量放在橫軸上，頻數(shù)、頻率放在縱軸上。每組的頻數(shù)、頻率在圖上就是一個長方形，長方形的底在橫軸上，寬度是組距，長方形的高就是對應(yīng)的頻數(shù)或頻率。圖13-4中是一個條形圖，而不是直方圖，若要把它變成直方圖，可按如下操作： 用鼠標(biāo)左鍵單擊圖中任一直條形，然后右鍵單擊，在彈出的快捷菜單中選取“數(shù)據(jù)

6、系列”格式，彈出數(shù)據(jù)系列格式對話框。在對話框中選擇“選項”標(biāo)簽，把“分類間距”寬度改為零，按確定后即可得到直方圖，如圖所示。 二、二、 用用Excel做常用統(tǒng)計圖做常用統(tǒng)計圖 Excel提供的統(tǒng)計圖有多種，包括柱形圖、條形圖、折線圖、餅圖、散點圖、面積圖、環(huán)形圖、雷達圖、曲面圖、氣泡圖、股價圖、圓柱圖、圓錐圖等，各種圖的作法大同小異，可根據(jù)需要的類型進行適當(dāng)選擇。 （一）（一） 餅圖的繪制餅圖的繪制 餅圖是用圓形以及園內(nèi)扇形的面積來表示數(shù)值大小的圖形，主要用于表示總體中各組成部分所占的比例，常用于研究結(jié)構(gòu)性問題。 案例 某地區(qū)年因違法被捕的男性情況如下所示，根據(jù)資料利用Excel繪制餅圖。步驟

7、先把數(shù)據(jù)輸入到工作表中，如圖所示，然后按下面的步驟操作： 第一步：選擇“插入”下拉菜單中的“圖表”選項。 第二步：在圖表類型中選擇“餅圖”，然后在子圖表類型中選擇一種類型，這里我們選用系統(tǒng)默認(rèn)的方式。然后單擊下一步按鈕，打開源數(shù)據(jù)對話框。如圖7所示。 第三步：在圖標(biāo)源數(shù)據(jù)對話框中填入數(shù)據(jù)所在區(qū)域，單擊下一步，在圖表選項中，對“標(biāo)題”、“圖例”和“數(shù)據(jù)標(biāo)志”適當(dāng)處理。如果要對圖形修改，可以用鼠標(biāo)雙擊圖表，然后用鼠標(biāo)雙擊需要修改的部分，并進行修改。 結(jié)果 即可得如圖所示的餅圖。 （二）折線圖的繪制 折線圖主要用于比較數(shù)據(jù)變動的方向和趨勢，表現(xiàn)數(shù)據(jù)在不同時期發(fā)展變化的不同趨勢。 案例 根據(jù)福建省20

8、05年到2009年間三大產(chǎn)業(yè)的總產(chǎn)出情況繪制折線圖，描述這幾年來三大產(chǎn)業(yè)總產(chǎn)值的變化趨勢。 步驟 第一步：資料輸入工作表后，選擇“插入”下拉菜單，再選擇“圖表”選項。 第二步：在圖表類型中選擇“折線圖”，然后在子圖表類型中選擇一種類型，這里我們選用如圖所示的方式。然后單擊“下一步”按鈕，打開源數(shù)據(jù)對話框。 第三步：在源數(shù)據(jù)對話框中，“數(shù)據(jù)區(qū)域”中輸入相關(guān)資料（可用鼠標(biāo)點擊并框定數(shù)據(jù)區(qū)域）。再在“系列”的“分類（X）軸標(biāo)志”區(qū)域輸入年份區(qū)域，如圖所示 第四步：資料輸入后的下一步，進入“圖表選項”。分別對“標(biāo)題”、“坐標(biāo)軸”、“網(wǎng)格線”、“圖例”、“數(shù)據(jù)標(biāo)志”和“數(shù)據(jù)表”等選項進行設(shè)置，當(dāng)然設(shè)置各

9、選項時根據(jù)需要進行取舍。最后點擊“完成”，就在工作表中得到折線圖。 結(jié)果 經(jīng)過上述各步驟，在工作表中得到折線圖： 一、利用“數(shù)據(jù)分析”功能 案例根據(jù)某縣的一項關(guān)于鄉(xiāng)鎮(zhèn)企業(yè)工資的調(diào)查，下面是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

10、 1343 1398 步驟 第一步：在Excel的區(qū)域“A1:A40”的單元格中輸入數(shù)據(jù)，從上到下排成4列。 第二步：選擇“工具”下拉菜單，再選擇“數(shù)據(jù)分析”選項 第三步：在分析工具中選擇“描述統(tǒng)計”，如圖11所示 第四步：當(dāng)出現(xiàn)對話框時，在“輸入?yún)^(qū)域”方框內(nèi)鍵入A1:A40（或用鼠標(biāo)選擇這區(qū)域），在“輸出選項”中選擇輸出區(qū)域（在此選擇C4），再選擇“匯總統(tǒng)計”（該選項給出全部描述統(tǒng)計量）。 第五步：點擊“確定”鍵。 其計算結(jié)果如圖所示。 二、二、 利用利用“統(tǒng)計函數(shù)統(tǒng)計函數(shù)”工具計算工具計算 我們可以用兩種方法來得到描述統(tǒng)計量，除上述“數(shù)據(jù)分析”功能計算外，還可采用Excel的函數(shù)工具計算。

11、仍以該企業(yè)40名雇員工資為例進行計算。 第一步：與上面操作相同，在Excel中輸入數(shù)據(jù)。 第二步：選擇“插入”下拉菜單中的“函數(shù)”選項，如圖所示。 第三步：出現(xiàn)的“插入函數(shù)”界面中，在“或選擇類別”選項中，選“統(tǒng)計”。再在“選擇函數(shù)”中，選“AVERAGE”（算術(shù)平均數(shù)），點擊“確定”如圖14所示。13-1313-14 第四步：出現(xiàn)“AVERAGE函數(shù)參數(shù)”界面中，在“Number1”中鍵入A1:A40（或用鼠標(biāo)選擇這區(qū)域），然后點擊“確定”，就能得出“算術(shù)平均數(shù)=817.85”。 重復(fù)上述各步驟，還可計算“調(diào)和平均數(shù)”、“幾何平均數(shù)”、“樣本標(biāo)準(zhǔn)差”和“總體標(biāo)準(zhǔn)差”等統(tǒng)計量，只要分別選擇相應(yīng)

12、的函數(shù)。常用描述統(tǒng)計量函數(shù)如表A-4所示 一、用Excel進行隨機抽樣 當(dāng)總體數(shù)量太大，不可能進行全面調(diào)查或者在某些情況下不需要進行全面調(diào)查又需要了解總體的情況時必須采用抽樣。使用Excel進行抽樣，首先要對各個總體單位進行編號，編號可以按隨機原則，也可以按有關(guān)標(biāo)志或無關(guān)標(biāo)志，編號后，將編號輸入工作表。 注：本功能需要使用Excel擴展功能，如果您的Excel尚未安裝數(shù)據(jù)分析，請依次選擇“工具” “加載宏”，在安裝光盤的支持下加載“數(shù)據(jù)分析庫”。加載成功后，才可以在工具的下拉菜單中看到“數(shù)據(jù)分析”選項。 案例 假定有100個總體單位，每個總體給一個編號，一共有100個編號，輸入工作表后，如圖所

13、示 步驟 先輸入各總體單位的編號，再按照下面的步驟操作： 第一步： 單擊菜單欄中的“工具”選項，在下拉列表框中選擇“數(shù)據(jù)分析”選項，此時彈出“數(shù)據(jù)分析”對話框，分析工具選擇“抽樣”。如圖所示 第二步：單擊“確定”，彈出“抽樣”對話框，如圖所示 第三步：在輸入?yún)^(qū)域框中輸入總體單位編號所在單元格區(qū)域，在本例是$A$1:$J$10,實際操作中一般是用鼠標(biāo)將編號所在區(qū)域選中即可。系統(tǒng)將從A列開始抽取樣本，然后按順序抽取B列至J列。如果輸入?yún)^(qū)域的第一行或第一列為標(biāo)志項（橫行標(biāo)題或縱行標(biāo)題），可單擊“標(biāo)志”復(fù)選框。 第四步：抽樣方法有周期和隨機兩種模式?！爸芷谀Ｊ健奔此^的等距抽樣，采用這種抽樣方法，需將

14、總體單位數(shù)除以要抽取的樣本單位數(shù)，可得到取樣的周期間隔。這里我們假設(shè)要在100個總體單位中抽取10個，則在“間隔”框中輸入“10”。 “隨機模式”適用于純隨機抽樣、分類抽樣、整群抽樣和階段抽樣。采用純隨機抽樣，只需在“樣本數(shù)”中直接輸入要抽取的樣本數(shù)即可；若用分類抽樣，必須先將總體按某一標(biāo)志分類編號，然后再每一類中隨機抽取若干單位i，這種抽樣方法實際上是分組法和隨機抽樣方法的結(jié)合；整群抽樣也要將總體單位分類編號，然后按隨機原則抽取若干類作為樣本，對抽中的所有單位全部進行調(diào)查。 第五步：制定輸出區(qū)域，這里我們鍵入“F12:F22”，然后單擊“確定”按鈕即可。 結(jié)果 10個隨機抽樣的樣本編號顯示在

15、工作表的“F12:F22”區(qū)域 二、用二、用ExcelExcel進行抽樣估計進行抽樣估計求置信區(qū)間求置信區(qū)間調(diào)用Excel中一些常用的“統(tǒng)計函數(shù)”可以直接進行抽樣推斷中的區(qū)間估計計算。下面結(jié)合案例來說明具體的操作步驟。 案例 為了解學(xué)校某班的統(tǒng)計學(xué)成績情況，對該班的全體同學(xué)隨機抽取13名作為調(diào)查樣本，統(tǒng)計抽取得到的樣本如 A列所示。假設(shè)該班學(xué)生的統(tǒng)計學(xué)成績總體上服從正態(tài)分布，用95%的置信水平估計該班學(xué)生統(tǒng)計學(xué)成績的置信區(qū)間。 為構(gòu)造區(qū)間估計的工作表，在工作表中先輸入以下內(nèi)容：A列中輸入樣本數(shù)據(jù)，B列輸入各統(tǒng)計指標(biāo)名稱，C列中輸入計算公式，D列輸入計算的結(jié)果。自由度為樣本數(shù)自由度為樣本數(shù) -1

16、（n-1），上），上式有誤式有誤 步驟 第一步：把樣本數(shù)據(jù)輸入A2:A14單元格。 第二步：在D2中輸入公式“COUNT(A2:A14)”,得到計算結(jié)果“13”?！癈OUNT”是計數(shù)函數(shù)，得到樣本含量（n=13）。 第三步：在D3中輸入“=AVERAGE(A2:A14)”，在D4中輸入“=STDEV(A2:A14)” 在D5中輸入“=D4/SQRT(D2)”,在D6中輸入0.95，在D7中輸入“=D2-1”，在D8中輸入“=TINV(1-D6，D7)”,在C9中輸入“=D8*D5”,在D10中輸入“=D3-D9”,在D11中輸入“=D3+D9”。 再輸入每一個公式回車后，即可得到如表的結(jié)果。自

17、由度為自由度為n-1，有誤，有誤 結(jié)果 從上面的結(jié)果我們可以知道，該班學(xué)生的統(tǒng)計學(xué)成績的置信上限是84.0039804，置信下限是61.53448112.計算結(jié)果表明，我們有95%的把握認(rèn)為該班學(xué)生的統(tǒng)計學(xué)成績平均在61.53448112-84.0039804之間。 利用上面的統(tǒng)計指標(biāo)，輸入不同的樣本數(shù)據(jù)，根據(jù)指標(biāo)所在位置的變化對計算公式作相應(yīng)的更改，則可以計算不同樣本的置信區(qū)間。 假設(shè)檢驗包括一個正態(tài)總體的參數(shù)檢驗和兩個正態(tài)總體的參數(shù)檢驗。對于一個正態(tài)總體參數(shù)的檢驗，可以直接利用函數(shù)工具和輸入公式的方法計算相應(yīng)的統(tǒng)計量，并進行檢驗。這里主要介紹如何利用Excel進行兩個正態(tài)分布總體的均值差檢

18、驗。 Excel中提供了四種假設(shè)檢驗?zāi)Ｊ?：平均值的成對二樣本分析、雙樣本等方差假設(shè)、雙樣本異方差假設(shè)和雙樣本平均差檢驗。其中較為常用的是后三種，所以這里就不介紹第一種。可以用以圖來說明后三種模式適用的條件。 案例 設(shè)有A 、B兩種安眠藥，為了比較他們的治療效果。以X表示失眠病人服下A藥睡眠時間延長的時數(shù)；以Y表示失眠病人服下B藥睡眠時間延長的時數(shù).現(xiàn)在獨立觀察20個病人，其中10個服下A藥，另10個服下B藥，延長時數(shù)的情況所圖中A列和B列所示。假設(shè)X與Y都服從正態(tài)分布。試問，這兩種藥的療效是否有顯著的差異。 步驟 第一步：選擇“工具”下拉菜單，再選擇“數(shù)據(jù)分析”選項 第二步：在“分析工具”選

19、擇“t檢驗：雙樣本異方差檢驗”（這里總體方差未知，且假設(shè)兩個對比樣本的方差不相等）如圖所示。 第三步：當(dāng)出現(xiàn)對話框時，在“變量1的區(qū)域”方框內(nèi)輸入A2:A11，實際操作中只要選中這部分區(qū)域即可；在“變量2的區(qū)域”方框內(nèi)輸入B2:B11；在“假設(shè)平均差”方框內(nèi)鍵入“0”；“”默認(rèn) 為0.05；在“輸出選項”中選擇“新工作表組”，如圖所示。待所有選項設(shè)置好后，單擊“確定”按鈕。 結(jié)果輸出結(jié)果如表所示計算的P值為0.206253，大于0.05。所以不拒絕原假設(shè)，認(rèn)為兩樣本的均值是相等的。即在本例中，認(rèn)為A、B兩種藥品的療效沒有顯著性差異。 一、相關(guān)分析一、相關(guān)分析 相關(guān)分析就是對兩個變量之間線性關(guān)系

20、的描述和度量，利用Excel進行相關(guān)分析時，先要繪制散點圖大致描繪出兩個變量變化趨勢的關(guān)系形態(tài)，散點圖的做法前面已經(jīng)介紹，這里就不再贅言，如果是線性關(guān)系，再計算相關(guān)系數(shù)來度量兩個變量之間的關(guān)系強度。用Excel計算相關(guān)系數(shù)有兩種方法，一是直接利用相關(guān)系數(shù)函數(shù)計算，如“CORREL函數(shù)”和“PEARSON函數(shù)”；另一種是利用“數(shù)據(jù)分析”中的“相關(guān)系數(shù)”工具。這里主要介紹后者。 從散點圖可以看出，兩個變量之間存在一定的正線性關(guān)系 下面再用Excel計算相關(guān)系數(shù)，并說明兩變量相關(guān)的方向和程度。 步驟 第一步：將人均GDP和建筑合同價值兩個變量的數(shù)據(jù)分別輸入到A2:A8和B2:B8區(qū)域。 第二步：選擇“工具”下拉菜單，再選擇“數(shù)據(jù)分析”選項。 第三步：在分析工具中選擇“相關(guān)系數(shù)”。 第四步：出現(xiàn)對話框，在輸入?yún)^(qū)域中鍵入“A2:B8”，在“輸出區(qū)域”中選擇輸出區(qū)域，這里我們選擇輸入到“新工作表”，最后單擊“確定”按鈕。 結(jié)果 由Excel