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

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

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

注意：對任意數據繪制直方圖時，首先必須進行簡單的數據處理，即對數據進行排序，分組，找分界點。具體表現為：（1）組數的確定，，為數據總量（80個），可計算擬分為8組（2）組距的確定，，為數據最大值與最小值（3）結合組數和組距以及原始數據確定各組的起始點，即為第一組1200以下，第二組1200~1400，依次類推，第八組為2400以上。（4）在Excel空白區(qū)域輸入各組分界點，依次為，1199.99，1399.99，1599.99，1799.99，1999.99，2199.99，2399.99，即區(qū)域B2:B8。1、數據處理注意：對任意數據繪制直方圖時，首先必須進行簡單2、操作步驟（1）、輸入原始數據（區(qū)域A1:A80，B2:B8），并對該數據排序。操作方法：EXCEL——數據——排序。2、操作步驟（1）、輸入原始數據（區(qū)域A1:A80，B2:B（2）繪直方圖操作過程：第一步：選擇“工具”——“數據分析”，見下圖：（2）繪直方圖操作過程：第一步：選擇“工具”——“數據分析第二步：選擇“數據分析”——“直方圖”，見下圖第二步：選擇“數據分析”——“直方圖”，見下圖第三步：在“直方圖”字對話框依次執(zhí)行下列操作：在“輸入區(qū)域”用鼠標點擊A1:A80得到如圖所示結果；

在“分組區(qū)域”用鼠標點擊B2:B8得到如圖所示結果；在“輸出選項”子菜單下面，選擇“圖表輸出”可得到直方圖，選擇“新工作簿”表示將頻數分布表存放在新的工作表中，也可選擇“輸出區(qū)域”存放在你認為合適的地方第三步：在“直方圖”字對話框依次執(zhí)行下列操作：在“輸入區(qū)域”3、操作結果完成上述操作后，EXCEL會相應輸出下列操作結果。（1）表格：根據上述操作可得到下列表格，左圖為Excel原始輸出，右圖為對應的含義解釋。接收頻率1199.9941399.9981599.99111799.99281999.99172199.9972399.993其他2按月收入分組頻率1200以下41200~140081400~1600111600~1800281800~2000172000~220072200~240032400以上2

3、操作結果完成上述操作后，EXCEL會相應輸出下列操作結果(2）直方圖：（調整后如下圖）(2）直方圖：（調整后如下圖）二、如何運用Excel計算描述統(tǒng)計量：包括平均數、眾數、中位數、方差、偏度、峰度等

下面是某保險公司160名員工的某月保險銷售額，（單位：千元）要求計算相關描述統(tǒng)計量，了解該公司員工的保險銷售額的情況8.414.2615.7616.9817.3917.6124.226.518.8114.5215.7717.0717.4117.6124.2626.79.2414.6115.8217.1417.4317.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.9318.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.6513.9715.6518.2918.7819.9121.4222.2823.7614.1615.7118.3318.9421.0321.5322.3823.94二、如何運用Excel計算描述統(tǒng)計量：包括平均數、眾數、中位1、操作步驟

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

完成上述操作步驟后，Excel相應輸出下列分析結果統(tǒng)計量計算結果平均18.77194標準誤差0.354332中位數17.915眾數17.25標準差4.48198方差20.08814峰度0.678236偏度0.608222區(qū)域24.15最小值8.4最大值32.55求和3003.51觀測數1602、操作結果完成上述操作步驟后，Excel相應輸出下列分三、如何運用Excel作區(qū)間估計

現有一大批糖果，先從重隨機地取16袋，稱得重量（以克計）如下：

506508499503504 510 497512514 505493 496，設袋裝糖果重量近似服從正態(tài)分布，試求總體均值的置信度為0.95的置信區(qū)間三、如何運用Excel作區(qū)間估計現有一大批糖果，先從重隨機1、操作目標

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

第一步：在Excel表格中輸入上圖中A4：A16和F1：F17的內容。第二步：名稱定義。（1）、進行變量定義，將A列的變量名稱定義為B列各公式計算結果的變量名。方法：選定A5：B7和A9：B9，A11：B16（先用鼠標選擇第一部分，再按住CTRL鍵選擇另外兩個部分），然后選擇“插入”——“名稱”——“指定”——“最左列”——“確定”。如下圖：2、操作步驟第一步：在Excel表格中輸入上圖中A4：A1（2）、為樣本數據進行名稱定義，即將F2：F17的數據定義為“樣本數據”。

方法：“插入”——“名稱”——“指定”——“首行”——“確定”

（2）、為樣本數據進行名稱定義，即將F2：F17的數據定義為第三步：進行區(qū)間估計。依次在B列輸入A列對應位置變量的計算公式，回車可得到各個變量值。

1、計算“樣本個數”，在B5內編輯計算公式：“=count(F2：F16)”（點中F2：F16），回車即可得到樣本個數計算值16。

第三步：進行區(qū)間估計。依次在B列輸入A列對應位置變量的計算公2、計算“樣本均值”，在B6內編輯計算公式：“=average(F2：F16)”（點中F2：F16），回車即可得到樣本均值計算值503.75

2、計算“樣本均值”，在B6內編輯計算公式：“=averag3、計算“樣本標準差”，在B7內編輯計算公式：“=stdev(F2：F16)”（點中F2：F16），回車即可得到樣本標準差6.202。3、計算“樣本標準差”，在B7內編輯計算公式：“=stdev4、“置信水平”，由用戶自行輸入，一般為0.9

5、“抽樣標準誤差”，在B11內編輯計算公式：“=樣本標準差/sqrt(樣本個數)”

（方法：輸入等于號，點中B7,輸入“/”和函數“sqrt”，點中B5，回車即可得到抽樣標準差1.55。4、“置信水平”，由用戶自行輸入，一般為0.9

5、“抽樣6、“自由度”，在B12內編輯計算公式：“=樣本個數-1”,具體公式編輯方法同（5），回車可得自由度值為15。（或者不需要公式，直接填入自由度值）6、“自由度”，在B12內編輯計算公式：“=樣本個數-1”,7、“t值”，在B13內編輯計算公式：“=tinv(1-B9,B12)”,具體公式編輯方法同（5），回車t值為2.1314.7、“t值”，在B13內編輯計算公式：“=tinv(1-B9

8、“置信區(qū)間半徑”，在B14內編輯計算公式：“=B13*B11”，具體公式編輯方法同（5），回車，可得值為3.3048、“置信區(qū)間半徑”，在B14內編輯計算公式：“=B139、“置信區(qū)間上限”，在B15內編輯計算公式：“=B6+B14”，具體公式編輯方法同（5），回車，可得值為507.05499、“置信區(qū)間上限”，在B15內編輯計算公式：“=B6+B110、“置信區(qū)間下限”，在B15內編輯計算公式：“=B6-B14”，具體公式編輯方法同（5），回車值為500.4451經過上述步驟可得區(qū)間估計的結果，上述過程看似復雜，實際上只要構造好一個區(qū)間估計的固定工作表，對任意數據進行區(qū)間估計，只要代入數據即可。10、“置信區(qū)間下限”，在B15內編輯計算公式：“=B6-B五、如何運用Excel作假設檢驗某車間用一臺包裝機包裝葡萄糖，包得的袋裝葡萄糖重量是一個隨機變量，它服從正態(tài)分布。當機器正常時，其均值為0.5公斤，標準差為0.015公斤。某日開工后為檢驗包裝機是否正常，隨機的抽取所包裝的糖9袋，稱得凈重為

0.4970.5060.5180.5240.4980.5110.5200.5150.512（公斤）問：機器是否正常？(總體標準差穩(wěn)定）五、如何運用Excel作假設檢驗某車間用一臺包裝機包裝葡萄糖1、操作目標：構造形如下圖的工作表：要進行假設檢驗，需構造形如下圖的工作表：其中，左邊A5：A16是變量名，右邊B5：B16是變量相應的計算公式，輸入樣本數據E2：E10A列“樣本統(tǒng)計量”包含兩個，依次為樣本個數、樣本均值，B列給出計算個樣本統(tǒng)計量的計算公式（或稱函數）；A列“用戶輸入”，輸入進行假設檢驗的置信水平，通常為0.95，總體標準差和總體均值假設值（通常為已知條件）；A列“計算結果”，下面包含進行區(qū)間估計所需的兩個量：抽樣標準誤差、計算Z值后面依次為單側檢驗和雙側檢驗，由你自行選擇。注：若事先構造好下表，那么任意數據進行假設檢驗，只要輸入數據，直接可以得到假設檢驗的結果。1、操作目標：構造形如下圖的工作表：要進行假設檢驗，需構造形2、操作方法：第一步：在Excel表格中輸入上圖中A2：A19和E1：E10的內容第二步：名稱定義。（方法基本上相同于區(qū)間估計）（1）進行變量定義，將A列的名稱定義為B列各個公式計算結果的變量名。選定A3：B4和A6：B8，A10：B11，A13：B15，A17：B19（先用鼠標選擇第一部分，再按住CTRL鍵選擇另外兩個部分），然后選擇“插入”——名稱——指定——最左列——確定。2、操作方法：第一步：在Excel表格中輸入上圖中A2：A1（2）為樣本數據定義。如圖“插入”——“名稱”——“指定”——“首行”，此操作是將E2：E10的數據名稱定義為“樣本數據”（2）為樣本數據定義。如圖“插入”——“名稱”——“指定”第三步：計算依次在B列輸入計算公式，形如下圖，依次可得到各個函數值，(1)、計算“樣本個數”，在B3內編輯計算公式：“=count(E2：E10)”（點中E2：E10），回車即可得到樣本個數計算值9。

第三步：計算依次在B列輸入計算公式，形如下圖，依次可得到各個（2）計算樣本均值，在B4內編輯計算公式：“=average(E2：E10)”（點中E2：E10），回車即可得到樣本均值計算值0.511（2）計算樣本均值，在B4內編輯計算公式：“=average（3）、輸入假設檢驗所需要的參數，包括置信水平0.95，總體標準差0.015，總體均值假設值0.5（在進行假設檢驗時，這些參數值一般已知）（3）、輸入假設檢驗所需要的參數，包括置信水平0.95，總體（4）、“抽樣標準誤差”，在B10內編輯公式，“=B7/B3”，回車可得0.002（4）、“抽樣標準誤差”，在B10內編輯公式，“=B7/B3（5）“計算Z值”，在B11內編輯公式，“=NORMSINV（1-B6）”，回車可得-1.64（5）“計算Z值”，在B11內編輯公式，“=NORMSINV（6）進行雙側檢驗，“雙側Z值”，在B16內編輯公式，“=NORMSINV(1-B6)”（6）進行雙側檢驗，“雙側Z值”，在B16內編輯公式，“=N“檢驗結果”，在B18內編輯公式，“=IF（ABS（B11）>ABS(B17),”拒絕H0”,接受H0”）形如下圖，輸入該公式后，回車即為檢驗結果“檢驗結果”，在B18內編輯公式，“=IF（ABS（B11）六、如何運用Excel作方差分析隨機選取26位被訪者，了解他們對安樂死的贊同程度。然后按不同血型進行分組，資料如下表。在顯著性水平為0.05時進行方差分析，判斷不同血型的人對安樂死的態(tài)度是否有顯著差異性？現假設已把數據輸入到Excel表格中，區(qū)域A2:D11OAABAB8080739278778086946992707384768579526886409173758164

六、如何運用Excel作方差分析隨機選取26位被訪者，了解他

（1）操作步驟：

第一步：選擇“工具”下拉菜單，然后選擇“數據分析”選項。第二步：在分析工具中選擇“單因素方差分析”。如下圖：（1）操作步驟：第一步：選擇“工具”下拉菜單，然后選擇第三步：當出現對話框后，在“輸入區(qū)域”方框內鍵入A2：D11（直接讓鼠標選中所要分析的數據即可）；在α方框內輸入0.05；在“輸出選項”中選擇輸出區(qū)域為（可以任意指定空白區(qū)域），若選擇“新工作表組”，方差分析結果將在另一表格中呈現；最后選擇“確定”。第三步：當出現對話框后，在“輸入區(qū)域”方框內鍵入A2：D1（2）輸出結果：

完成上述操作步驟后，EXCEL輸出下列分析結果：表一：樣本數據描述統(tǒng)計組計數求和平均方差列11080180.176.54444列2747567.85714257.8095列3538977.882.2列4433383.2587.58333

表二：方差分析結果差異源SSdfMSFP-valueFcrit組間840.07753280.02582.1789530.1192323.049124組內2827.30722128.514總計3667.38525從上表中可以看出，

，所以說明不同血型的人對安樂死的態(tài)度沒有顯著差異。（2）輸出結果：

完成上述操作步驟后，EXCEL輸出下列分析七、如何運用Excel作相關分析下表為年齡（X）與脂肪百分比（Y）的數據，試計算兩者的相關系數。年齡2323272739414549脂肪的百分比9.527.97.817.831.425.927.425.2七、如何運用Excel作相關分析下表為年齡（X）與脂肪百分比

(1)、操作步驟：

第一步：選擇“工具”——“數據分析”——“相關系數”——“確定”(1)、操作步驟：第一步：選擇“工具”——“數據分析”—第二步：在“相關系數”子對話框中，作如下選擇：

“輸入區(qū)域”：選中原數據即可，如圖所示。“輸出選項”：若選擇“輸出區(qū)域”（任意選定空白區(qū)域即可，則相關系數計算結果顯示在該區(qū)域。若選則“新工作表”，則相關系數顯示在該工作表中。最后選擇“確定”進行相關分析。第二步：在“相關系數”子對話框中，作如下選擇：

“輸入區(qū)域”（2）結果：

完成上述步驟后，顯示結果如下：表示年齡與脂肪百分比相關系數為0.792（2）結果：