統(tǒng)計(jì)學(xué)實(shí)驗(yàn)指導(dǎo)書(EXCEL2010)

PAGEPAGE28EXCEL實(shí)驗(yàn)指導(dǎo)書注意：由于簡裝版的EXCEL中沒有“數(shù)據(jù)分析”菜單，因此需要進(jìn)行以下操作步驟來添加該菜單，前提是計(jì)算機(jī)中必須有OFFICE2010的原始安裝軟件，否則下列操作無法完成?！拔募薄斑x項(xiàng)”——“加載項(xiàng)”——EXCEL加載項(xiàng)“轉(zhuǎn)到”——“加載宏”——選擇“分析工具庫”——確定。完成以上操作后“數(shù)據(jù)”菜單中出現(xiàn)“數(shù)據(jù)分析”則操作成功。

實(shí)驗(yàn)一利用Excel對(duì)數(shù)據(jù)進(jìn)行描述統(tǒng)計(jì)分析一、實(shí)驗(yàn)?zāi)康?、掌握Excel的基本知識(shí)2、學(xué)會(huì)應(yīng)用Excel創(chuàng)建表格，輸入和編輯信息3、熟練運(yùn)用excel的公式和函數(shù)求各種統(tǒng)計(jì)指標(biāo)4、利用Excel的分析工具對(duì)數(shù)據(jù)進(jìn)行描述性統(tǒng)計(jì)5、掌握組距式變量數(shù)列的編制原理二、實(shí)驗(yàn)要求1、掌握Excel的基本操作方法2、通過練習(xí)，能夠獨(dú)立運(yùn)用Excel進(jìn)行數(shù)據(jù)整理和數(shù)據(jù)分析3、掌握利用Excel對(duì)數(shù)據(jù)進(jìn)行分組編制的基本操作方法；三、實(shí)驗(yàn)內(nèi)容1、分別用函數(shù)和數(shù)據(jù)分析工具計(jì)算這31個(gè)地區(qū)人口的總和、平均值、中位數(shù)、眾數(shù)、標(biāo)準(zhǔn)差。表1-12008年全國各地區(qū)人口統(tǒng)計(jì)地區(qū)總?cè)丝?萬人)地區(qū)總?cè)丝?萬人)北京1695山東9417天津1176河南9429河北6989湖北5711山西3411湖南6380內(nèi)蒙2414廣東9544遼寧4315廣西4816吉林2734海南854黑龍江3825重慶2839上海1888四川8138江蘇7677貴州3793浙江5120云南4543安徽6135西藏287福建3604陜西3762青海554寧夏618新疆2131甘肅2628江西44002、根據(jù)抽樣調(diào)查，某月X市50戶居民購買消費(fèi)品支出資料如下（單位：元）：表1-2某月X市50戶居民購買消費(fèi)品支出83088012301100118015801210146011701080105011001070137012001630125013601270142011801030870115014101170123012601380151010108608101130114011901260135093014201080101010501250116013201380131012701250根據(jù)以上數(shù)據(jù)，以900、1000、1100、1200、1300、1400、1500、1600為組限，對(duì)居民戶月消費(fèi)支出額編制組距式變量數(shù)列，并計(jì)算居民戶月消費(fèi)支出額的累計(jì)頻數(shù)和頻率。同時(shí)對(duì)分組資料用各種統(tǒng)計(jì)圖的形式來加以表現(xiàn)。四、實(shí)驗(yàn)步驟用EXCEL函數(shù)計(jì)算這31個(gè)地區(qū)人口的總和、平均值、中位數(shù)、眾數(shù)、標(biāo)準(zhǔn)差（1）如圖1.1所示，在工作表中輸入數(shù)據(jù)。（2）熟悉統(tǒng)計(jì)常用函數(shù)及其功能函數(shù)功能函數(shù)功能函數(shù)功能COUNT記數(shù)NORMSINVZ值計(jì)算MEDIAN中位數(shù)MAX最大值A(chǔ)BS絕對(duì)值MODE眾數(shù)MIN最小值CHIINV卡方值QUARTILE四分位數(shù)SUM求和TINVT值計(jì)算VAR方差SUMSQ平方和FINVF值計(jì)算STDEV標(biāo)準(zhǔn)差A(yù)VERAGE平均值SQRT平方根GEOMEAN幾何平均數(shù)（3）使用函數(shù)進(jìn)行統(tǒng)計(jì)計(jì)算。（以求數(shù)據(jù)的總和為例）選定E2單元格，單擊工具欄上的“公式——插入函數(shù)”命令，出現(xiàn)下圖1.2所示對(duì)話框，在“選擇類別”后的對(duì)話框中選擇“全部”，在“選擇函數(shù)”中選擇求和函數(shù)“SUM”，然后點(diǎn)擊確定。②將鼠標(biāo)置于“Mumber1”右段的編輯框內(nèi)，在工作表中選擇B2：B32的區(qū)域，單擊“確定”按鈕，如下圖1.3所示。③則在E2單元格中顯示數(shù)據(jù)“130827”為所求的數(shù)據(jù)總和。④使用同樣的方法分別求的表中數(shù)據(jù)的平均值、中位數(shù)、眾數(shù)、標(biāo)準(zhǔn)差為下圖1.4所示。2、用分析工具試計(jì)算這31個(gè)地區(qū)人口的總和、平均值、中位數(shù)、眾數(shù)、標(biāo)準(zhǔn)差。（1）點(diǎn)擊“數(shù)據(jù)—數(shù)據(jù)分析”，打開“數(shù)據(jù)分析”對(duì)話框，如下圖1.5所示。（2）選擇“描述統(tǒng)計(jì)”功能，單擊“確定”，系統(tǒng)打開描述統(tǒng)計(jì)對(duì)話框，如下圖所示。選定數(shù)據(jù)的輸入?yún)^(qū)域?yàn)锽2：B32單元格；分組方式選擇“逐列”；選定一個(gè)輸出區(qū)域?yàn)镈9單元格；再選定“匯總統(tǒng)計(jì)”復(fù)選框，如圖1.6。（3）系統(tǒng)輸出2008年我國各地區(qū)人口描述統(tǒng)計(jì)的計(jì)算結(jié)果如圖1.7所示。（4）在系統(tǒng)的輸出中，“平均”即均值X，反映了全國各地區(qū)人口的平均水平；“標(biāo)準(zhǔn)誤差”為樣本均值的標(biāo)準(zhǔn)差，反映了用平均值代表性的大小，本例中標(biāo)準(zhǔn)誤差的值很大，說明平均值的代表性差，各地區(qū)的人口數(shù)量差異比較大；“眾數(shù)”即出現(xiàn)次數(shù)最多的標(biāo)志值，由于本例中31個(gè)標(biāo)志值互不相同，故沒有眾數(shù)；“標(biāo)準(zhǔn)差”為總體標(biāo)準(zhǔn)差；“方差”為總體方差；本例中峰度的值大于零，表示分布比正態(tài)分布更集中在平均數(shù)周圍，分布呈尖峰狀態(tài)；偏度的值大于零，說明分布呈正偏斜，即大部分標(biāo)志值是大于平均值的。2、對(duì)居民戶月消費(fèi)支出額編制組距式變量數(shù)列，（1）數(shù)據(jù)輸入。分別輸入“居民消費(fèi)品支出”和組限。如圖1.8所示（2）執(zhí)行菜單命令[數(shù)據(jù)]→[數(shù)據(jù)分析]，調(diào)出“數(shù)據(jù)分析”對(duì)話框，選擇“直方圖”選項(xiàng)，調(diào)出“直方圖”對(duì)話框。（3）在“直方圖”對(duì)話框中，輸入相關(guān)數(shù)據(jù)，見下圖1.9。輸入?yún)^(qū)域：$A$1:$J$5接收區(qū)域：$A$7:$A$14，接收區(qū)域的數(shù)值應(yīng)按升序排列輸出區(qū)域：$B$7（為輸出結(jié)果左上角單元格地址）選中“標(biāo)志”復(fù)選框。柏拉圖：選中此復(fù)選框，可以在輸出表中同時(shí)按降序排列頻率數(shù)據(jù)。如果此復(fù)選框被清除，Excel將只按升序來排列數(shù)據(jù)。累積百分比：選中此復(fù)選框，可以在輸出表中添加一列累積百分比數(shù)值，并同時(shí)在直方圖表中添加累積百分比折線。如果清除此選項(xiàng)，則會(huì)省略累積百分比。圖表輸出：選中此復(fù)選框，可以在輸出表中同時(shí)生成一個(gè)嵌入式直方圖表。本例中選中“累積百分比”和“圖表輸出”兩個(gè)復(fù)選框。（4）實(shí)驗(yàn)結(jié)果見下圖1.10（5）由圖中可知，對(duì)分組資料進(jìn)行整理后可得如下結(jié)果表1-3某月X市50戶居民購買消費(fèi)品支出分組資料組名居民購買消費(fèi)品支出分組居民戶數(shù)1900以下52900～1000131000～11001041100～120010512001400771400～1500481500～1600291600以上1實(shí)驗(yàn)二Excel圖表制作及編輯實(shí)驗(yàn)?zāi)康恼莆誆xcel的圖表創(chuàng)建熟悉Excel圖表編輯技巧二、實(shí)驗(yàn)要求掌握Excel的基本圖表創(chuàng)建操作方法。通過練習(xí)，能夠獨(dú)立運(yùn)用Excel圖表進(jìn)行數(shù)據(jù)整理和數(shù)據(jù)分析。三．實(shí)驗(yàn)內(nèi)容1．統(tǒng)計(jì)圖表的建立；2．學(xué)習(xí)、練習(xí)Excel的圖表編輯；四．實(shí)驗(yàn)步驟：1、統(tǒng)計(jì)圖表的創(chuàng)建與編輯，以表2-1為例，建立柱形圖。表2-12005—2009年我國普通高等教育、中等職業(yè)教育及普通高中招生人數(shù)（萬人）年份普通高等教育中等職業(yè)教育普通高中20055046568782006546748871200756681084020086088128372009640874830（1）將表2-1中的數(shù)據(jù)輸入Excel表格中，如下圖所示（2）移動(dòng)鼠標(biāo)到工具欄“插入”按鈕，點(diǎn)擊圖表中的窗口后，從中選擇想要的圖表類型，用鼠標(biāo)單擊該圖形，如下圖所示。（3）如下圖所示出現(xiàn)空白圖形，在圖形的任何位置點(diǎn)擊鼠標(biāo)右鍵，在菜單中點(diǎn)擊“選擇數(shù)據(jù)”，給圖形添加橫軸和縱軸的數(shù)據(jù)。（4）點(diǎn)擊“選擇數(shù)據(jù)”按鈕出現(xiàn)如圖所示的對(duì)話框，在“圖例項(xiàng)”下的“添加”中分別將“普通高等教育”“中等職業(yè)教育”“普通高中”三個(gè)系列的“系列名稱”“系列值”添加到圖中。在“水平軸標(biāo)簽”中“編輯”年份（6）如圖2.6所示，在圖表工具中選擇圖表布局類型，并分別修改“標(biāo)題”、“橫軸”、“縱軸（７）在如圖所示，用鼠標(biāo)右鍵單擊圖中某個(gè)系列的圖標(biāo)，出現(xiàn)如圖所示的編輯對(duì)話框，點(diǎn)擊“添加數(shù)據(jù)標(biāo)簽”，在圖中顯示每個(gè)系列的數(shù)值。（８）圖表基本內(nèi)容完成以后，需要修改某部分內(nèi)容時(shí)，將鼠標(biāo)移動(dòng)到所修改部位，點(diǎn)擊鼠標(biāo)右鍵，在所列菜單中進(jìn)行相應(yīng)的修改工作，最終建立的圖表如下圖所示2、利用下表中的數(shù)據(jù)制作餅形圖表2-2桂林電子科技大學(xué)信息科技學(xué)院教師職稱分布情況表職稱比例高級(jí)職稱30%碩士學(xué)位教師35.8%本科學(xué)位教師34.2%（1）將表2-2中的數(shù)據(jù)輸入Excel表格中，如圖所示（2）移動(dòng)鼠標(biāo)到工具欄“插入”按鈕，點(diǎn)擊圖表中的窗口后，從中選擇想要的圖表類型，用鼠標(biāo)單擊該圖形，如下圖所示。（3）如圖所示，出現(xiàn)“選擇源數(shù)據(jù)”窗口后，選擇“圖表數(shù)據(jù)區(qū)域”中選擇要建立圖表的數(shù)據(jù)區(qū)域A2:B4，再單擊下一步按鈕。(4)如圖所示，在圖表工具中選擇圖表布局類型“布局1”。（5）修改“圖表標(biāo)題”，并移動(dòng)圖中的數(shù)據(jù)位置，得到最終的餅形圖如下：實(shí)驗(yàn)三Excel區(qū)間估計(jì)一、實(shí)驗(yàn)?zāi)康?掌握Excel區(qū)間估計(jì)方法。二、實(shí)驗(yàn)流程：1. 進(jìn)入Excel.2. 使用直方圖進(jìn)行初步分析。3. 利用“描述統(tǒng)計(jì)”計(jì)算平均數(shù)和標(biāo)準(zhǔn)差。4. 進(jìn)行區(qū)間估計(jì)。三、實(shí)驗(yàn)內(nèi)容以下數(shù)據(jù)為50輛汽車傳動(dòng)系統(tǒng)出現(xiàn)故障時(shí)所行駛的實(shí)際里程數(shù)。8509232609594657743732534640903246459902393238964194219116803928576343665605858616434261978679985981710176995774121352695687427666998400017206925066770986992235662744256720211844453500792946454486813116269378318943173341852881381145340285586822567753988798求曾經(jīng)出現(xiàn)過傳動(dòng)系統(tǒng)問題的汽車總體中在出現(xiàn)傳動(dòng)系統(tǒng)問題時(shí)所行駛里程均值的95%置信區(qū)間。四、實(shí)驗(yàn)步驟1、首先將分析數(shù)據(jù)輸入Excel工作表Sheet1單元格A1:J5。如圖所示：2、利用“函數(shù)”分別計(jì)算平均值和標(biāo)準(zhǔn)差（步驟同實(shí)驗(yàn)一），得到計(jì)算結(jié)果為平均里程為73342.1，標(biāo)準(zhǔn)偏差為24899.9。3、進(jìn)行區(qū)間估計(jì)[1]. 單擊存放結(jié)果的單元格E7。[2]. 在單元格E7輸入公式：“=73342.1-CONFIDENCE(0.05,24899.9,50)”， 其中函數(shù)CONFIDENCE功能為返回均值的置信區(qū)間，其使用方法同實(shí)驗(yàn)一中所講函數(shù)的使用方法相同，見下圖。然后再按Enter鍵，得到結(jié)果為66440.34。[2]. 在在單元格E8輸入公式：“=73342.1+CONFIDENCE(0.05,24899.9,50)”，按Enter鍵，得到結(jié)果為80243.86。得到區(qū)間估計(jì)結(jié)果為80243.86。如圖所示實(shí)驗(yàn)四一元線性性回歸模型一、實(shí)驗(yàn)?zāi)康?．掌握用excel一次性算出回歸模型參數(shù)的方法和步驟；2．正確分析輸出結(jié)果并得出正確的回歸模型。二、實(shí)驗(yàn)內(nèi)容：某地區(qū)1994年到2002年的人均收入和商品零售總額的數(shù)據(jù)如下：表4.1地區(qū)1994年到2002年的人均收入和商品零售總額年份人均收入X（元）商品零售總額(Y億元)199445026199555032199668044199773062199881070199993089200010501032001116011520021250128試配合適當(dāng)?shù)幕貧w方程；若2003年該地區(qū)人均收入為1300元，試估計(jì)2003年商品零售總額為多少？三、操作步驟1．在excel的工作表中輸入如表4.1所示的人均收入X和商品零售總額Y的樣本數(shù)據(jù)。2．點(diǎn)擊主菜單中的“插入”菜單，在彈出的子菜單中點(diǎn)擊“圖表”選項(xiàng)，在標(biāo)準(zhǔn)類型選項(xiàng)下選擇“XY散點(diǎn)圖”，點(diǎn)擊下一步，在彈出對(duì)話框的輸入?yún)^(qū)域，拖動(dòng)鼠標(biāo)選擇人均收入X和商品零售總額Y的樣本值；系列產(chǎn)生在，選擇列；得到散點(diǎn)圖如圖4.1所示。由圖4.1可以看出人均收入X和商品零售總額Y之間存在很明顯的線性關(guān)系，可以建立一元線性回歸模型。3．點(diǎn)擊主菜單中的“數(shù)據(jù)”菜單，在彈出的子菜單中點(diǎn)擊“數(shù)據(jù)分析”在出現(xiàn)的數(shù)據(jù)分析對(duì)話框中選擇回歸，如圖4.2所示。圖4.2應(yīng)用excel軟件求回歸分析相關(guān)參數(shù)4．點(diǎn)擊圖4.2所示對(duì)話框中的確定，彈出“回歸”對(duì)話框，在Y值輸入?yún)^(qū)域，拖動(dòng)鼠標(biāo)選擇Y樣本值C2:C10，在X值輸入?yún)^(qū)域，拖動(dòng)鼠標(biāo)選擇X樣本值B2:B10,“置信度”輸入95%，“輸出區(qū)域”為A12，如圖4.3所示。圖4.3應(yīng)用excel軟件求回歸分析相關(guān)參數(shù)5．點(diǎn)擊圖4.3所示中的確定，彈出回歸分析有關(guān)參數(shù)的窗口，如圖4.4所示。圖4.4應(yīng)用excel軟件求回歸分析相關(guān)參數(shù)四、結(jié)果分析“回歸統(tǒng)計(jì)”中MultipleR為復(fù)相關(guān)系數(shù)；RSquare為可決系數(shù)R2=0.988786由圖4.4的輸出結(jié)果，可以得到本例中的回歸系數(shù)為a?=-38.68166,b?=，0.1336577故所求回歸方程為Y?-38.68166+0.1336577X，由于相關(guān)系數(shù)r=0.99可知兩變量是高度相關(guān)的，說明該回歸模型和回歸方程合理反映了人均收入和商品零售總額的相關(guān)關(guān)系，可以用來進(jìn)行預(yù)測和控制。當(dāng)人均收入X=1300元時(shí)，用回歸方程可以估計(jì)2003年商品零售總額為135.07335。實(shí)驗(yàn)五長期趨勢分析一、實(shí)驗(yàn)?zāi)康?、掌握用移動(dòng)平均法、趨勢方程法和利用函數(shù)進(jìn)行長期趨勢分析的方法與步驟；2、測定發(fā)展速度和平均發(fā)展速度、增長量和平均增長量。二、實(shí)驗(yàn)內(nèi)容：1、表5.1所示為我國2001-2009年國內(nèi)生產(chǎn)總值，請根據(jù)所給數(shù)據(jù)，分別用移動(dòng)平均法、趨勢方程法和利用函數(shù)對(duì)2010年的國民生產(chǎn)總值進(jìn)行預(yù)測。表5.1所示為我國2001-2009年國內(nèi)生產(chǎn)總值年份國內(nèi)生產(chǎn)總值（億元）2001109655200212033320031358232004159878200518493720062163142007265810200831404520093353532、計(jì)算發(fā)展速度和平均發(fā)展速度、增長量和平均增長量的值三、實(shí)驗(yàn)步驟：1.移動(dòng)平均法（1）在excel的工作表中輸入如表5.1所示的年份和國民國民生產(chǎn)總值的樣本數(shù)據(jù)。點(diǎn)擊“數(shù)據(jù)—數(shù)據(jù)分析—移動(dòng)平均”，出現(xiàn)數(shù)據(jù)分析對(duì)話框，如圖5.1所示。圖5.1數(shù)據(jù)分析對(duì)話框（2）點(diǎn)擊“確定”按鈕，出現(xiàn)“移動(dòng)平均”對(duì)話框，在其中設(shè)置各個(gè)參數(shù)，輸入?yún)^(qū)域?yàn)椋?B$2:$B$10；輸出區(qū)域?yàn)椋?C$2；選中“圖表輸出”復(fù)選框。如圖5.2所示。圖5.2移動(dòng)平均對(duì)話框（3）．點(diǎn)擊“確定”按鈕，即可得到經(jīng)過移動(dòng)平均之后的預(yù)測數(shù)值和繪制的長期趨勢圖，如圖5.3所示圖5.3用移動(dòng)平均法得到的長期趨勢線2、趨勢方程法（1）點(diǎn)擊主菜單中的“數(shù)據(jù)”菜單，在彈出的子菜單中點(diǎn)擊“數(shù)據(jù)分析”，在出現(xiàn)的數(shù)據(jù)分析對(duì)話框中選擇“回歸”。彈出“回歸”對(duì)話框，在Y值輸入?yún)^(qū)域，拖動(dòng)鼠標(biāo)選擇國內(nèi)生產(chǎn)總值B2:B10，在X值輸入?yún)^(qū)域，拖動(dòng)鼠標(biāo)選擇年份值A(chǔ)2:A10,“置信度”輸入95%，“輸出區(qū)域”為C2，如圖5.4所示。圖5.4應(yīng)用excel軟件求回歸分析相關(guān)參數(shù)(2)點(diǎn)擊圖5.4所示中的確定，彈出回歸分析有關(guān)參數(shù)的窗口及模型擬合值圖，如下圖5.5所示。（3）由表中數(shù)據(jù)可得直線趨勢方程為Y?-59956611.72+30005.63333X，將X=2010帶入趨勢方程可得，2010年我國國內(nèi)生產(chǎn)總值的預(yù)測值為354711.27333、利用函數(shù)進(jìn)行趨勢預(yù)測（1）打開“公式”菜單，選擇“插入函數(shù)”，在“函數(shù)分類”中選擇“全部”，在“函數(shù)名”中選擇“FORECAST”,按確定鍵，如下圖5.5所示。（2）在彈出的“函數(shù)參數(shù)”對(duì)話框中的“X”中輸入預(yù)測期的時(shí)間代碼“2010”；在“KNOWN-YS”中輸入國內(nèi)生產(chǎn)總值的樣本數(shù)據(jù)；在“KNOWN-XS”中輸入時(shí)間代碼；點(diǎn)擊“確定”按鈕。如下圖5.6所示。（3）最后返回的數(shù)值“354711.3”即為對(duì)2010年我國國內(nèi)生產(chǎn)總值的預(yù)測值。4、測定增長量和平均增長量根據(jù)1995-2001年河北省國內(nèi)生產(chǎn)總值，計(jì)算逐期增長量、累計(jì)增長量和平均增長量。如圖5.7所示：圖5.7用EXCEL計(jì)算增長量和平均增長量資料及結(jié)果計(jì)算步驟如下：第一步：在A列輸入年份，在B列輸入國內(nèi)生產(chǎn)總值。第二步：計(jì)算逐期增長量：在C3中輸入公式：=B3-B2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C8區(qū)域。第三步：計(jì)算累計(jì)增長量：在D3中輸入公式：=B3-$B$2，并用鼠標(biāo)拖曳公式復(fù)制到D3：D8區(qū)域。第四步：計(jì)算平均增長量（水平法）：在C10中輸入公式：=（B8-B2）/5，按回車鍵，即可得到平均增長量。5、測定發(fā)展速度和平均發(fā)展速度以1995-2001年河北省國內(nèi)生產(chǎn)總值為例，說明如何計(jì)算定基發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速度。如圖5.8所示：圖5.8用EXCEL計(jì)算發(fā)展速度和平均發(fā)展速度資料及結(jié)果計(jì)算步驟如下：第一步：在A列輸入年份，在B列輸入國內(nèi)生產(chǎn)總值。第二步：計(jì)算定基發(fā)展速度：在C3中輸入公式：=B3/$B$2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C8區(qū)域。第三步：計(jì)算環(huán)比發(fā)展速度：在D3中輸入公式：=B3/B2，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D8區(qū)域。第四步：計(jì)算平均發(fā)展速度（水平法）：選中C10單元格，單擊插入菜單，選擇函數(shù)選項(xiàng)，出現(xiàn)插入函數(shù)對(duì)話框后，選擇GEOMEAN（返回幾何平均值）函