Excel在統(tǒng)計(jì)學(xué)中應(yīng)用剖析

Excel在統(tǒng)計(jì)學(xué)中應(yīng)用2021/6/281第一節(jié)用Excel做統(tǒng)計(jì)圖一、用Excel做頻率分布表和直方圖一般統(tǒng)計(jì)數(shù)據(jù)有兩大類，即定性數(shù)據(jù)和定量數(shù)據(jù)。我們這里就不討論把定性數(shù)據(jù)轉(zhuǎn)換成定量數(shù)據(jù)了，而是主要學(xué)習(xí)定量數(shù)據(jù)的作圖。下面的例子說明如何利用Excel進(jìn)行分組，并作頻率分布表和直方圖。[案例]2010年1月四十個(gè)大中城市房屋銷售價(jià)格指數(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、109.6、110.3、110、105.1、105.9、113、106.2、99.5、104.8、103.1、105.9、103.8（1）據(jù)此編制分布數(shù)列（提示：價(jià)格指數(shù)是連續(xù)變量）；（2）計(jì)算向上累計(jì)頻數(shù)（率）；（3）畫出次數(shù)分布直方圖。2021/6/282[步驟]

第一步：在Excel界面中輸入40個(gè)企業(yè)的數(shù)據(jù)，從上到下輸入A列（也可以分組排列）。

第二步：選擇“工具”下拉菜單，如圖1所示。2021/6/283第三步：選擇“數(shù)據(jù)分析”選項(xiàng)，如果沒有該功能則要先行安裝?！皵?shù)據(jù)分析”的具體安裝方法，選擇“工具”下拉菜單中“加載宏”，在出現(xiàn)的選項(xiàng)中選擇“分析工具庫”，“確定”就可自動(dòng)安裝。第四步：在分析工具中選擇“直方圖”，如圖2所示。2021/6/284第五步：當(dāng)出現(xiàn)“直方圖”對(duì)話框時(shí)，在“輸入?yún)^(qū)域”方框內(nèi)鍵入A2:A41或＄A＄2:＄A＄41（“＄”符號(hào)表示的是絕對(duì)地址起，到固定單元格的作用），40個(gè)數(shù)據(jù)已輸入該區(qū)域內(nèi)，如果是分組排列的，就應(yīng)該選擇整個(gè)分組區(qū)域。在“接收區(qū)域”方框內(nèi)鍵入C2:C9或＄C＄2:＄C＄9，所有數(shù)據(jù)分成8組（主要根據(jù)資料的特點(diǎn)，決定組數(shù)、組距和組限），把各組的上限輸入該區(qū)域內(nèi)。在“輸出區(qū)域”方框內(nèi)鍵入E2或＄E＄2，也可以在其他位置重新建表。對(duì)話框中，還選擇“累積百分率”、“圖表輸出”如圖所示。然后點(diǎn)擊“確定”。對(duì)話框內(nèi)主要選項(xiàng)的含義如下：輸入?yún)^(qū)域：在此輸入待分析區(qū)域的單元格范圍。2021/6/285接收區(qū)域（可選）：在此輸入接收區(qū)域的單元格范圍，該區(qū)域包含一組可選的用來計(jì)算頻數(shù)的邊界值。這些值應(yīng)當(dāng)按升序排列。只要存在，Excel將統(tǒng)計(jì)在各個(gè)相鄰邊界值之間的數(shù)據(jù)出現(xiàn)的次數(shù)。如果省略此處的接收區(qū)域，Excel將在數(shù)據(jù)組的最小值和最大值之間建立一組平滑分布的接收區(qū)間。2021/6/286標(biāo)志：如果輸入?yún)^(qū)域的第一行或第一列中包含標(biāo)志項(xiàng)，則選中此復(fù)選框；如果輸入?yún)^(qū)域沒有標(biāo)志項(xiàng)，則清除此復(fù)選框，Excel將在輸出表中生成適宜的數(shù)據(jù)標(biāo)志。輸出區(qū)域：在此輸入計(jì)算結(jié)果顯示的單元格地址。如果不輸入具體位置將覆蓋已有的數(shù)據(jù)，Excel會(huì)自動(dòng)確定輸出區(qū)域的大小并顯示信息。柏拉圖：選中此復(fù)選框，可以在輸出表中同時(shí)顯示升序、將序排列頻率數(shù)據(jù)。如果此復(fù)選框被清除，Excel將只按升序來排列數(shù)據(jù)。累積百分比：選中此復(fù)選框，可以在輸出結(jié)果中添加一列累積百分比數(shù)值，并同時(shí)在直方圖表中添加累積百分比折線。如果清除此選項(xiàng)，則會(huì)省略以上結(jié)果。圖表輸出：選中此復(fù)選框，可以在輸出表中同時(shí)生成一個(gè)嵌入式直方圖表。2021/6/287[結(jié)果]操作得到的結(jié)果如圖所示。通常包括三列和一個(gè)頻數(shù)分布圖，第一列是數(shù)值的區(qū)域范圍，第二列是數(shù)值分布的頻數(shù)（不是頻率），第三列是頻數(shù)分布的累積百分比。直方圖是用矩形高度來表示頻數(shù)分布的圖形。繪制直方圖時(shí)，將所研究的變量放在橫軸上，頻數(shù)、頻率放在縱軸上。每組的頻數(shù)、頻率在圖上就是一個(gè)長方形，長方形的底在橫軸上，寬度是組距，長方形的高就是對(duì)應(yīng)的頻數(shù)或頻率。圖13-4中是一個(gè)條形圖，而不是直方圖，若要把它變成直方圖，可按如下操作：用鼠標(biāo)左鍵單擊圖中任一直條形，然后右鍵單擊，在彈出的快捷菜單中選取“數(shù)據(jù)系列”格式，彈出數(shù)據(jù)系列格式對(duì)話框。在對(duì)話框中選擇“選項(xiàng)”標(biāo)簽，把“分類間距”寬度改為零，按確定后即可得到直方圖，如圖所示。2021/6/2882021/6/289二、用Excel做常用統(tǒng)計(jì)圖Excel提供的統(tǒng)計(jì)圖有多種，包括柱形圖、條形圖、折線圖、餅圖、散點(diǎn)圖、面積圖、環(huán)形圖、雷達(dá)圖、曲面圖、氣泡圖、股價(jià)圖、圓柱圖、圓錐圖等，各種圖的作法大同小異，可根據(jù)需要的類型進(jìn)行適當(dāng)選擇。（一）餅圖的繪制餅圖是用圓形以及園內(nèi)扇形的面積來表示數(shù)值大小的圖形，主要用于表示總體中各組成部分所占的比例，常用于研究結(jié)構(gòu)性問題。[案例]某地區(qū)××年因違法被捕的男性情況如下所示，根據(jù)資料利用Excel繪制餅圖。2021/6/2810[步驟]先把數(shù)據(jù)輸入到工作表中，如圖所示，然后按下面的步驟操作：2021/6/2811第一步：選擇“插入”下拉菜單中的“圖表”選項(xiàng)。第二步：在圖表類型中選擇“餅圖”，然后在子圖表類型中選擇一種類型，這里我們選用系統(tǒng)默認(rèn)的方式。然后單擊下一步按鈕，打開源數(shù)據(jù)對(duì)話框。如圖7所示。2021/6/2812第三步：在圖標(biāo)源數(shù)據(jù)對(duì)話框中填入數(shù)據(jù)所在區(qū)域，單擊下一步，在圖表選項(xiàng)中，對(duì)“標(biāo)題”、“圖例”和“數(shù)據(jù)標(biāo)志”適當(dāng)處理。如果要對(duì)圖形修改，可以用鼠標(biāo)雙擊圖表，然后用鼠標(biāo)雙擊需要修改的部分，并進(jìn)行修改。[結(jié)果]即可得如圖所示的餅圖。2021/6/2813（二）折線圖的繪制折線圖主要用于比較數(shù)據(jù)變動(dòng)的方向和趨勢(shì)，表現(xiàn)數(shù)據(jù)在不同時(shí)期發(fā)展變化的不同趨勢(shì)。[案例]根據(jù)福建省2005年到2009年間三大產(chǎn)業(yè)的總產(chǎn)出情況繪制折線圖，描述這幾年來三大產(chǎn)業(yè)總產(chǎn)值的變化趨勢(shì)。2021/6/2814[步驟]第一步：資料輸入工作表后，選擇“插入”下拉菜單，再選擇“圖表”選項(xiàng)。第二步：在圖表類型中選擇“折線圖”，然后在子圖表類型中選擇一種類型，這里我們選用如圖所示的方式。然后單擊“下一步”按鈕，打開源數(shù)據(jù)對(duì)話框。2021/6/2815第三步：在源數(shù)據(jù)對(duì)話框中，“數(shù)據(jù)區(qū)域”中輸入相關(guān)資料（可用鼠標(biāo)點(diǎn)擊并框定數(shù)據(jù)區(qū)域）。再在“系列”的“分類（X）軸標(biāo)志”區(qū)域輸入年份區(qū)域，如圖所示第四步：資料輸入后的下一步，進(jìn)入“圖表選項(xiàng)”。分別對(duì)“標(biāo)題”、“坐標(biāo)軸”、“網(wǎng)格線”、“圖例”、“數(shù)據(jù)標(biāo)志”和“數(shù)據(jù)表”等選項(xiàng)進(jìn)行設(shè)置，當(dāng)然設(shè)置各選項(xiàng)時(shí)根據(jù)需要進(jìn)行取舍。最后點(diǎn)擊“完成”，就在工作表中得到折線圖。[結(jié)果]經(jīng)過上述各步驟，在工作表中得到折線圖：2021/6/2816第二節(jié)用Excel計(jì)算描述統(tǒng)計(jì)量一、利用“數(shù)據(jù)分析”功能[案例]根據(jù)某縣的一項(xiàng)關(guān)于鄉(xiāng)鎮(zhèn)企業(yè)工資的調(diào)查，下面是40名雇員的月工資按升冪排列的一個(gè)樣本，對(duì)該企業(yè)雇員工資進(jìn)行描述統(tǒng)計(jì)分析。（單位：元）2742792903263293413784054365005155415585666187087608678688698889159329429609759761014102510961118116611931194124312771304132713431398[步驟]第一步：在Excel的區(qū)域“A1:A40”的單元格中輸入數(shù)據(jù)，從上到下排成4列。第二步：選擇“工具”下拉菜單，再選擇“數(shù)據(jù)分析”選項(xiàng)第三步：在分析工具中選擇“描述統(tǒng)計(jì)”，如圖11所示2021/6/28172021/6/2818第四步：當(dāng)出現(xiàn)對(duì)話框時(shí)，在“輸入?yún)^(qū)域”方框內(nèi)鍵入A1:A40（或用鼠標(biāo)選擇這區(qū)域），在“輸出選項(xiàng)”中選擇輸出區(qū)域（在此選擇C4），再選擇“匯總統(tǒng)計(jì)”（該選項(xiàng)給出全部描述統(tǒng)計(jì)量）。第五步：點(diǎn)擊“確定”鍵。其計(jì)算結(jié)果如圖所示。2021/6/28192021/6/2820二、利用“統(tǒng)計(jì)函數(shù)”工具計(jì)算我們可以用兩種方法來得到描述統(tǒng)計(jì)量，除上述“數(shù)據(jù)分析”功能計(jì)算外，還可采用Excel的函數(shù)工具計(jì)算。仍以該企業(yè)40名雇員工資為例進(jìn)行計(jì)算。第一步：與上面操作相同，在Excel中輸入數(shù)據(jù)。第二步：選擇“插入”下拉菜單中的“函數(shù)”選項(xiàng)，如圖所示。第三步：出現(xiàn)的“插入函數(shù)”界面中，在“或選擇類別”選項(xiàng)中，選“統(tǒng)計(jì)”。再在“選擇函數(shù)”中，選“AVERAGE”（算術(shù)平均數(shù)），點(diǎn)擊“確定”如圖14所示。2021/6/282113-1313-142021/6/2822第四步：出現(xiàn)“AVERAGE函數(shù)參數(shù)”界面中，在“Number1”中鍵入A1:A40（或用鼠標(biāo)選擇這區(qū)域），然后點(diǎn)擊“確定”，就能得出“算術(shù)平均數(shù)=817.85”。重復(fù)上述各步驟，還可計(jì)算“調(diào)和平均數(shù)”、“幾何平均數(shù)”、“樣本標(biāo)準(zhǔn)差”和“總體標(biāo)準(zhǔn)差”等統(tǒng)計(jì)量，只要分別選擇相應(yīng)的函數(shù)。常用描述統(tǒng)計(jì)量函數(shù)如表A-4所示2021/6/28232021/6/2824第三節(jié)用Excel進(jìn)行抽樣一、用Excel進(jìn)行隨機(jī)抽樣當(dāng)總體數(shù)量太大，不可能進(jìn)行全面調(diào)查或者在某些情況下不需要進(jìn)行全面調(diào)查又需要了解總體的情況時(shí)必須采用抽樣。使用Excel進(jìn)行抽樣，首先要對(duì)各個(gè)總體單位進(jìn)行編號(hào)，編號(hào)可以按隨機(jī)原則，也可以按有關(guān)標(biāo)志或無關(guān)標(biāo)志，編號(hào)后，將編號(hào)輸入工作表。注：本功能需要使用Excel擴(kuò)展功能，如果您的Excel尚未安裝數(shù)據(jù)分析，請(qǐng)依次選擇“工具”“加載宏”，在安裝光盤的支持下加載“數(shù)據(jù)分析庫”。加載成功后，才可以在工具的下拉菜單中看到“數(shù)據(jù)分析”選項(xiàng)。2021/6/2825[案例]假定有100個(gè)總體單位，每個(gè)總體給一個(gè)編號(hào)，一共有100個(gè)編號(hào)，輸入工作表后，如圖所示2021/6/2826[步驟]先輸入各總體單位的編號(hào)，再按照下面的步驟操作：第一步：單擊菜單欄中的“工具”選項(xiàng)，在下拉列表框中選擇“數(shù)據(jù)分析”選項(xiàng)，此時(shí)彈出“數(shù)據(jù)分析”對(duì)話框，分析工具選擇“抽樣”。如圖所示2021/6/2827第二步：單擊“確定”，彈出“抽樣”對(duì)話框，如圖所示2021/6/2828第三步：在輸入?yún)^(qū)域框中輸入總體單位編號(hào)所在單元格區(qū)域，在本例是$A$1:$J$10,實(shí)際操作中一般是用鼠標(biāo)將編號(hào)所在區(qū)域選中即可。系統(tǒng)將從A列開始抽取樣本，然后按順序抽取B列至J列。如果輸入?yún)^(qū)域的第一行或第一列為標(biāo)志項(xiàng)（橫行標(biāo)題或縱行標(biāo)題），可單擊“標(biāo)志”復(fù)選框。第四步：抽樣方法有周期和隨機(jī)兩種模式。 “周期模式”即所謂的等距抽樣，采用這種抽樣方法，需將總體單位數(shù)除以要抽取的樣本單位數(shù)，可得到取樣的周期間隔。這里我們假設(shè)要在100個(gè)總體單位中抽取10個(gè)，則在“間隔”框中輸入“10”。2021/6/2829“隨機(jī)模式”適用于純隨機(jī)抽樣、分類抽樣、整群抽樣和階段抽樣。采用純隨機(jī)抽樣，只需在“樣本數(shù)”中直接輸入要抽取的樣本數(shù)即可；若用分類抽樣，必須先將總體按某一標(biāo)志分類編號(hào)，然后再每一類中隨機(jī)抽取若干單位i，這種抽樣方法實(shí)際上是分組法和隨機(jī)抽樣方法的結(jié)合；整群抽樣也要將總體單位分類編號(hào)，然后按隨機(jī)原則抽取若干類作為樣本，對(duì)抽中的所有單位全部進(jìn)行調(diào)查。第五步：制定輸出區(qū)域，這里我們鍵入“F12:F22”，然后單擊“確定”按鈕即可。[結(jié)果]10個(gè)隨機(jī)抽樣的樣本編號(hào)顯示在工作表的“F12:F22”區(qū)域2021/6/2830二、用Excel進(jìn)行抽樣估計(jì)——求置信區(qū)間 調(diào)用Excel中一些常用的“統(tǒng)計(jì)函數(shù)”可以直接進(jìn)行抽樣推斷中的區(qū)間估計(jì)計(jì)算。下面結(jié)合案例來說明具體的操作步驟。[案例]為了解學(xué)校某班的統(tǒng)計(jì)學(xué)成績情況，對(duì)該班的全體同學(xué)隨機(jī)抽取13名作為調(diào)查樣本，統(tǒng)計(jì)抽取得到的樣本如A列所示。假設(shè)該班學(xué)生的統(tǒng)計(jì)學(xué)成績總體上服從正態(tài)分布，用95%的置信水平估計(jì)該班學(xué)生統(tǒng)計(jì)學(xué)成績的置信區(qū)間。為構(gòu)造區(qū)間估計(jì)的工作表，在工作表中先輸入以下內(nèi)容：A列中輸入樣本數(shù)據(jù)，B列輸入各統(tǒng)計(jì)指標(biāo)名稱，C列中輸入計(jì)算公式，D列輸入計(jì)算的結(jié)果。2021/6/2831自由度為樣本數(shù)-1（n-1），上式有誤2021/6/2832[步驟]第一步：把樣本數(shù)據(jù)輸入A2:A14單元格。第二步：在D2中輸入公式“COUNT(A2:A14)”,得到計(jì)算結(jié)果“13”?！癈OUNT”是計(jì)數(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”。再輸入每一個(gè)公式回車后，即可得到如表的結(jié)果。2021/6/2833自由度為n-1，有誤2021/6/2834[結(jié)果]從上面的結(jié)果我們可以知道，該班學(xué)生的統(tǒng)計(jì)學(xué)成績的置信上限是84.0039804，置信下限是61.53448112.計(jì)算結(jié)果表明，我們有95%的把握認(rèn)為該班學(xué)生的統(tǒng)計(jì)學(xué)成績平均在61.53448112-84.0039804之間。利用上面的統(tǒng)計(jì)指標(biāo)，輸入不同的樣本數(shù)據(jù)，根據(jù)指標(biāo)所在位置的變化對(duì)計(jì)算公式作相應(yīng)的更改，則可以計(jì)算不同樣本的置信區(qū)間。2021/6/2835第四節(jié)用Excel進(jìn)行假設(shè)檢驗(yàn)假設(shè)檢驗(yàn)包括一個(gè)正態(tài)總體的參數(shù)檢驗(yàn)和兩個(gè)正態(tài)總體的參數(shù)檢驗(yàn)。對(duì)于一個(gè)正態(tài)總體參數(shù)的檢驗(yàn)，可以直接利用函數(shù)工具和輸入公式的方法計(jì)算相應(yīng)的統(tǒng)計(jì)量，并進(jìn)行檢驗(yàn)。這里主要介紹如何利用Excel進(jìn)行兩個(gè)正態(tài)分布總體的均值差檢驗(yàn)。Excel中提供了四種假設(shè)檢驗(yàn)?zāi)Ｊ剑浩骄档某蓪?duì)二樣本分析、雙樣本等方差假設(shè)、雙樣本異方差假設(shè)和雙樣本平均差檢驗(yàn)。其中較為常用的是后三種，所以這里就不介紹第一種?？梢杂靡詧D來說明后三種模式適用的條件。2021/6/2836[案例]設(shè)有A、B兩種安眠藥，為了比較他們的治療效果。以X表示失眠病人服下A藥睡眠時(shí)間延長的時(shí)數(shù)；以Y表示失眠病人服下B藥睡眠時(shí)間延長的時(shí)數(shù).現(xiàn)在獨(dú)立觀察20個(gè)病人，其中10個(gè)服下A藥，另10個(gè)服下B藥，延長時(shí)數(shù)的情況所圖中A列和B列所示。假設(shè)X與Y都服從正態(tài)分布。試問，這兩種藥的療效是否有顯著的差異。2021/6/2837[步驟]第一步：選擇“工具”下拉菜單，再選擇“數(shù)據(jù)分析”選項(xiàng)第二步：在“分析工具”選擇“t檢驗(yàn)：雙樣本異方差檢驗(yàn)”（這里總體方差未知，且假設(shè)兩個(gè)對(duì)比樣本的方差不相等）如圖所示。2021/6/2838第三步：當(dāng)出現(xiàn)對(duì)話框時(shí)，在“變量1的區(qū)域”方框內(nèi)輸入A2:A11，實(shí)際操作中只要選中這部分區(qū)域即可；在“變量2的區(qū)域”方框內(nèi)輸入B2:B11；在“假設(shè)平均差”方框內(nèi)鍵入“0”；“”默認(rèn)為0.05；在“輸出選項(xiàng)”中選擇“新工作表組”，如圖所示。待所有選項(xiàng)設(shè)置好后，單擊“確定”按鈕。2021/6/2839[結(jié)果]輸出結(jié)果如表所示計(jì)算的P值為0.206253，大于0.05。所以不拒絕原假設(shè)，認(rèn)為兩樣本的均值是相等的。即在本例中，認(rèn)為A、B兩種藥品的療效沒有顯著性差異。2021/6/2840第五節(jié)用Excel進(jìn)行相關(guān)與回歸分析一、相關(guān)分析相關(guān)分析就是對(duì)兩個(gè)變量之間線性關(guān)系的描述和度量，利用Excel進(jìn)行相關(guān)分析時(shí)，先要繪制散點(diǎn)圖大致描繪出兩個(gè)變量變化趨勢(shì)的關(guān)系形態(tài)，散點(diǎn)圖的做法前面已經(jīng)介紹，這里就不再贅言，如果是線性關(guān)系，再計(jì)算相關(guān)系數(shù)來度量兩個(gè)變量之間的關(guān)系強(qiáng)度。用Excel計(jì)算相關(guān)系數(shù)有兩種方法，一是直接利用相關(guān)系數(shù)函數(shù)計(jì)算，如“CORREL函數(shù)”和“PEARSON函數(shù)”；另一種是利用“數(shù)據(jù)分析”中的“相關(guān)系數(shù)”工具。這里主要介紹后者。2021/6/2841[案例]7個(gè)省區(qū)人均GDP（元）和建筑合同的價(jià)值（億元）資料如下表2021/6/2842根據(jù)資料數(shù)據(jù)先繪制散點(diǎn)圖如圖所示2021/6/2843從散點(diǎn)圖可以看出，兩個(gè)變量之間存在一定的正線性關(guān)系下面再用Excel計(jì)算相關(guān)系數(shù)，并說明兩變量相關(guān)的方向和程度。[步驟]第一步：將人均GDP和建筑合同價(jià)值兩個(gè)變量的數(shù)據(jù)分別輸入到A2:A8和B2:B8區(qū)域。第二步：選擇“工具”下拉菜單，再選擇“數(shù)據(jù)分析”選項(xiàng)。第三步：在分析工具中選擇“相關(guān)系數(shù)”。第四步：出現(xiàn)對(duì)話框，在輸入?yún)^(qū)域中鍵入“A2:B8”，在“輸出區(qū)域”中選擇輸出區(qū)域，這里我們選擇輸入到“新工作表”，最后單擊“確定”按鈕。2021/6/2844[