用Excel作統(tǒng)計分析

1、 實訓五實訓五用用Excel作統(tǒng)計分析作統(tǒng)計分析 如何運用如何運用 Excel 作統(tǒng)計圖（直方圖）作統(tǒng)計圖（直方圖）下列數(shù)據(jù)是80個工人的月收入（單位：元），繪制直方圖，觀察月收入的分布狀況。102713021573165817051794192020401050135615781665171218001934207011091435157916701730183019402076115414521600167017501854195821401200145216301678175318601965214312351543163016791758187019732270123715431645

2、169017791870198723001260154616491690178018701998235412801560165216991784189020002478129815701652170017931893203825951、數(shù)據(jù)處理、數(shù)據(jù)處理 注意：對任意數(shù)據(jù)繪制直方圖時，首先必須進行簡單的數(shù)據(jù)處理，即對數(shù)據(jù)進行排序，分組，找分界點。具體表現(xiàn)為：（1）組數(shù)的確定，為數(shù)據(jù)總量（80個），可計算擬分為8組（2）組距的確定，為數(shù)據(jù)最大值與最小值（3）結(jié)合組數(shù)和組距以及原始數(shù)據(jù)確定各組的起始點，即為 第一組1200以下，第二組12001400，依次類推，第八組為2400以上。（4）在Exc

3、el空白區(qū)域輸入各組分界點，依次為，1199.99，1399.99，1599.99，1799.99，1999.99，2199.99，2399.99，即區(qū)域B2:B8。2、操作步驟、操作步驟 （1）、輸入原始數(shù)據(jù)（區(qū)域A1:A80，B2:B8），并對該數(shù)據(jù)排序。 操作方法：EXCEL數(shù)據(jù)排序。（2）繪直方圖操作過程： 第一步：選擇第一步：選擇“工具工具”“數(shù)據(jù)分析數(shù)據(jù)分析”，見下圖：，見下圖：第二步：選擇第二步：選擇“數(shù)據(jù)分析數(shù)據(jù)分析”“直方圖直方圖”，見下圖，見下圖第三步：在第三步：在“直方圖直方圖”字對話框依次執(zhí)行下列操作：字對話框依次執(zhí)行下列操作：在“輸入?yún)^(qū)域”用鼠標點擊A1:A80得到如

4、圖所示結(jié)果； 在“分組區(qū)域”用鼠標點擊B2:B8得到如圖所示結(jié)果； 在“輸出選項”子菜單下面，選擇“圖表輸出”可得到直方圖，選擇“新工作簿”表示將頻數(shù)分布表存放在新的工作表中，也可選擇“輸出區(qū)域”存放在你認為合適的地方3、操作結(jié)果、操作結(jié)果 完成上述操作后，EXCEL會相應輸出下列操作結(jié)果。 （1）表格：）表格：根據(jù)上述操作可得到下列表格，左圖為Excel原始輸出，右圖為對應的含義解釋。接收頻率1199.9941399.9981599.99111799.99281999.99172199.9972399.993其他2按月收入分組頻率1200以下4120014008140016001116001

5、8002818002000172000220072200240032400以上2 (2）直方圖：（）直方圖：（調(diào)整后如下圖 ）二、如何運用二、如何運用Excel計算描述統(tǒng)計量：計算描述統(tǒng)計量：包括平均數(shù)、眾數(shù)、中位數(shù)、方差、偏度、峰度等包括平均數(shù)、眾數(shù)、中位數(shù)、方差、偏度、峰度等 下面是某保險公司160名員工的某月保險銷售額，（單位：千元）要求計算相關(guān)描述統(tǒng)計量，了解該公司員工的保險銷售額的情況8.414.2615.7616.9817.3917.6124.226.518.8114.5215.7717.0717.4117.6124.2626.79.2414.6115.8217.1417.4317

6、.6224.826.749.6414.7815.8417.1617.4317.6625.0526.9311.0514.8715.8617.1617.4817.725.2927.511.4314.9215.8817.2517.4817.7525.427.7612.3715.0815.8817.2517.5217.7525.5331.1612.7415.1315.9917.2517.5717.7925.6431.3412.9615.1516.1617.3117.5717.8425.8332.413.2215.2716.2217.3417.5717.926.0732.5513.2515.3417.9

7、318.4218.9621.0921.5322.6513.3315.3717.9318.5119.0521.1621.6422.8413.5115.4217.9618.5519.2521.1621.6623.1613.6115.4317.9718.6319.2721.1921.7223.2713.6115.4618.0218.6419.3521.2521.8123.413.6815.4818.2218.6419.4521.3121.8823.4513.815.5618.2418.6919.4621.3421.9223.513.8515.6418.2818.7319.5521.422.1323.

8、6513.9715.6518.2918.7819.9121.4222.2823.7614.1615.7118.3318.9421.0321.5322.3823.941、 操作步驟操作步驟 第一步：輸入原始數(shù)據(jù)（A1：A160），并進入描述統(tǒng)計菜單，操作步驟如下：Excel工具數(shù)據(jù)分析描述統(tǒng)計 第二步：在“描述統(tǒng)計”子菜單下面作如下選擇：“輸入?yún)^(qū)域”：選擇A1：A160，表示對該數(shù)據(jù)作描述統(tǒng)計分析。“輸入?yún)^(qū)域”：選擇“匯總統(tǒng)計”，表示輸出各種描述統(tǒng)計量，包括平均數(shù)、 眾數(shù)、中位數(shù)、方差、 偏度、峰度等各個描述統(tǒng)計量。選擇“新工作簿”，表示將描述統(tǒng)計分析結(jié)果存放在新的工作表格中。2、操作結(jié)果、操作

9、結(jié)果 完成上述操作步驟后，Excel相應輸出下列分析結(jié)果 統(tǒng)計量計算結(jié)果平均18.77194標準誤差0.354332中位數(shù)17.915眾數(shù)17.25標準差4.48198方差20.08814峰度0.678236偏度0.608222區(qū)域24.15最小值8.4最大值32.55求和3003.51觀測數(shù)160三、如何運用三、如何運用Excel作區(qū)間估計作區(qū)間估計 現(xiàn)有一大批糖果，先從重隨機地取16袋，稱得重量（以克計）如下： 506 508 499 503 504 510 497 512 514 505 493496， 設(shè)袋裝糖果重量近似服從正態(tài)分布，試求總體均值的置信度為0.95的置信區(qū)間 1、操作目

10、標、操作目標 要進行區(qū)間估計，需構(gòu)造形如下圖的工作表：其中，左邊A5：A16是變量名，右邊B5：B16是變量相應的計算公式，輸入樣本數(shù)據(jù)F2：F17。A列“樣本統(tǒng)計量”包含三個，依次為樣本個數(shù)、樣本均值、樣本標準差，B列給出計算三個樣本統(tǒng)計量的計算公式（或稱函數(shù)）；A列“用戶輸入”，輸入進行區(qū)間估計的置信水平，通常為0.95；A列“計算結(jié)果”，下面包含進行區(qū)間估計所需的六個量： 抽樣標準誤差、自由度、t值、置信區(qū)間半徑、置信區(qū)間下限、置信區(qū)間上限。B列為各自對應計算公式注：若事先構(gòu)造好下表，那么任意數(shù)據(jù)進行區(qū)間，只要輸入數(shù)據(jù)，直接可以得到區(qū)間估計的結(jié)果。2、操作步驟、操作步驟 第一步：在Exc

11、el表格中輸入上圖中A4：A16和F1：F17的內(nèi)容。第二步：名稱定義。（1）、進行變量定義，將A列的變量名稱定義為B列各公式計算結(jié)果的變量名。 方法：選定A5：B7和A9：B9，A11：B16（先用鼠標選擇第一部分，再按住CTRL鍵選擇另外兩個部分），然后選擇“插入”“名稱”“指定”“最左列”“確定”。如下圖：（2）、為樣本數(shù)據(jù)進行名稱定義，即將F2：F17的數(shù)據(jù)定義為“樣本數(shù)據(jù)”。方法：“插入”“名稱”“指定 ”“首行”“確定” 第三步：進行區(qū)間估計。依次在B列輸入A列對應位置變量的計算公式，回車可得到各個變量值。1、計算“樣本個數(shù)”，在B5內(nèi)編輯計算公式：“=count(F2：F16)”

12、（點中F2：F16），回車即可得到樣本個數(shù)計算值16。 2、計算“樣本均值”，在B6內(nèi)編輯計算公式：“=average(F2：F16)”（點中F2：F16），回車即可得到樣本均值計算值503.75 3、計算“樣本標準差”，在B7內(nèi)編輯計算公式：“=stdev(F2：F16)”（點中F2：F16），回車即可得到樣本標準差6.202。4、“置信水平”，由用戶自行輸入，一般為0.9 5、“抽樣標準誤差”，在B11內(nèi)編輯計算公式：“=樣本標準差/sqrt(樣本個數(shù))”（方法：輸入等于號，點中B7,輸入“/”和函數(shù)“sqrt”，點中B5，回車即可得到抽樣標準差1.55。6、“自由度”，在B12內(nèi)編輯計算

13、公式：“=樣本個數(shù)-1”,具體公式編輯方法同（5），回車可得自由度值為15。（或者不需要公式，直接填入自由度值）7、“t值”，在B13內(nèi)編輯計算公式：“=tinv(1-B9,B12) ”,具體公式編輯方法同（5），回車t值為2.1314. 8、 “置信區(qū)間半徑”，在B14內(nèi)編輯計算公式：“=B13*B11 ”，具體公式編輯方法同（5），回車，可得值為3.3049、“置信區(qū)間上限”，在B15內(nèi)編輯計算公式：“=B6+B14 ”，具體公式編輯方法同（5），回車，可得值為507.054910、“置信區(qū)間下限”，在B15內(nèi)編輯計算公式：“=B6-B14 ”，具體公式編輯方法同（5），回車值為500.4

14、451 經(jīng)過上述步驟可得區(qū)間估計的結(jié)果，上述過程看似復雜，實際上只要構(gòu)造好一個區(qū)間估計的固定工作表，對任意數(shù)據(jù)進行區(qū)間估計，只要代入數(shù)據(jù)即可。 五、如何運用五、如何運用Excel作假設(shè)檢驗作假設(shè)檢驗 某車間用一臺包裝機包裝葡萄糖，包得的袋裝葡萄糖重量是一個隨機變量，它服從正態(tài)分布。當機器正常時，其均值為0.5公斤，標準差為0.015公斤。某日開工后為檢驗包裝機是否正常，隨機的抽取所包裝的糖9袋，稱得凈重為 0.497 0.506 0.518 0.524 0.498 0.511 0.520 0.515 0.512 （公斤） 問：機器是否正常？(總體標準差穩(wěn)定）1、操作目標：構(gòu)造形如下圖的工作表：

15、、操作目標：構(gòu)造形如下圖的工作表：要進行假設(shè)檢驗，需構(gòu)造形如下圖的工作表：其中，左邊A5：A16是變量名，右邊B5：B16是變量相應的計算公式，輸入樣本數(shù)據(jù)E2：E10A列“樣本統(tǒng)計量”包含兩個，依次為樣本個數(shù)、樣本均值，B列給出計算個樣本統(tǒng)計量的計算公式（或稱函數(shù)）；A列“用戶輸入”，輸入進行假設(shè)檢驗的置信水平，通常為0.95，總體標準差和總體均值假設(shè)值（通常為已知條件）；A列“計算結(jié)果”，下面包含進行區(qū)間估計所需的兩個量： 抽樣標準誤差、計算Z值后面依次為單側(cè)檢驗和雙側(cè)檢驗，由你自行選擇。注：若事先構(gòu)造好下表，那么任意數(shù)據(jù)進行假設(shè)檢驗，只要輸入數(shù)據(jù)，直接可以得到假設(shè)檢驗的結(jié)果。2、操作方法

16、：、操作方法：第一步：在Excel表格中輸入上圖中A2：A19和E1：E10的內(nèi)容第二步：名稱定義。（方法基本上相同于區(qū)間估計）（1）進行變量定義，將A列的名稱定義為B列各個公式計算結(jié)果的變量名。 選定A3：B4和A6：B8，A10：B11，A13：B15，A17：B19（先用鼠標選擇第一部分，再按住CTRL鍵選擇另外兩個部分），然后選擇“插入”名稱指定最左列確定。（2）為樣本數(shù)據(jù)定義。如圖“插入”“名稱”“指定 ”“首行”，此操作是將E2：E10的數(shù)據(jù)名稱定義為“樣本數(shù)據(jù)”第三步：計算依次在B列輸入計算公式，形如下圖，依次可得到各個函數(shù)值，(1)、計算“樣本個數(shù)”，在B3內(nèi)編輯計算公式：“=

17、count(E2：E10)”（點中E2：E10），回車即可得到樣本個數(shù)計算值9。 （2）計算樣本均值，在B4內(nèi)編輯計算公式：“=average(E2：E10)”（點中E2：E10），回車即可得到樣本均值計算值0.511（3）、輸入假設(shè)檢驗所需要的參數(shù)，包括置信水平0.95，總體標準差0.015，總體均值假設(shè)值0.5 （在進行假設(shè)檢驗時，這些參數(shù)值一般已知）（4）、“抽樣標準誤差”，在B10內(nèi)編輯公式，“=B7/B3”，回車可得0.002（5）“計算Z值”，在B11內(nèi)編輯公式，“=NORMSINV（1-B6）”，回車可得-1.64（6）進行雙側(cè)檢驗，“雙側(cè)Z值”，在B16內(nèi)編輯公式，“=NORM

18、SINV(1-B6)”“檢驗結(jié)果”，在B18內(nèi)編輯公式，“=IF（ABS（B11）ABS(B17),”拒絕H0” , 接受H0”）形如下圖，輸入該公式后，回車即為檢驗結(jié)果六、如何運用六、如何運用Excel作方差分析作方差分析隨機選取26位被訪者，了解他們對安樂死的贊同程度。然后按不同血型進行分組，資料如下表。在顯著性水平為0.05時進行方差分析，判斷不同血型的人對安樂死的態(tài)度是否有顯著差異性？ 現(xiàn)假設(shè)已把數(shù)據(jù)輸入到Excel表格中，區(qū)域A2:D11OAABAB8080739278778086946992707384768579526886409173758164 （1） 操作步驟操作步驟： 第

19、一步：選擇“工具”下拉菜單，然后選擇“數(shù)據(jù)分析”選項 。第二步：在分析工具中選擇“單因素方差分析”。如下圖：第三步：當出現(xiàn)對話框后 ，在“輸入?yún)^(qū)域”方框內(nèi)鍵入A2：D11（直接讓鼠標選中所要分析的數(shù)據(jù)即可）；在方框內(nèi)輸入0.05；在“輸出選項”中選擇輸出區(qū)域為（可以任意指定空白區(qū)域），若選擇“新工作表組”，方差分析結(jié)果將在另一表格中呈現(xiàn)；最后選擇“確定”。（2）輸出結(jié)果：）輸出結(jié)果：完成上述操作步驟后，EXCEL輸出下列分析結(jié)果：表一：樣本數(shù)據(jù)描述統(tǒng)計組計數(shù)求和平均方差列 11080180.176.54444列 2747567.85714257.8095列 3538977.882.2列 443

20、3383.2587.58333 表二：方差分析結(jié)果差異源SSdfMSFP-valueF crit組間840.07753280.02582.1789530.1192323.049124組內(nèi)2827.30722128.514總計3667.38525從上表中可以看出， 049124. 3)22, 3(178953. 205. 0FF，所以說明不同血型的人對安樂死的態(tài)度沒有顯著差異。七、如何運用七、如何運用Excel作相關(guān)分析作相關(guān)分析下表為年齡（X）與脂肪百分比（Y）的數(shù)據(jù)，試計算兩者的相關(guān)系數(shù)。 年齡2323272739414549脂肪的百分比9.527.97.817.831.425.927.425.2 (1)、操作步驟：、操作步驟： 第一步：選擇“工具”“數(shù)據(jù)分析”“相關(guān)系數(shù)”“確定”第二步：在“相關(guān)系數(shù)”子對話框中，作如下選擇：“輸入?yún)^(qū)域”：選中原數(shù)據(jù)即可，如圖所示?！拜敵鲞x項”：若選擇“輸出區(qū)域”（任意選定空白區(qū)域即可，則相關(guān)系數(shù)計算結(jié)果顯示在該區(qū)域。若選則“新工作表”，則相關(guān)系數(shù)顯示在該工作表中。最后選擇“確定”進行相關(guān)分析。（2）結(jié)果：）結(jié)果： 完成上述步驟后，顯