統(tǒng)計(jì)學(xué)excel實(shí)驗(yàn)答案(最新版)

Excel在統(tǒng)計(jì)學(xué)中的應(yīng)用用Excel搜集與整理數(shù)據(jù)用Excel搜集數(shù)據(jù)統(tǒng)計(jì)數(shù)據(jù)的收集是統(tǒng)計(jì)工作過程的基礎(chǔ)性環(huán)節(jié)，方法有多種多樣，其中以抽樣調(diào)查最有代表性。在抽樣調(diào)查中，為保證抽樣的隨機(jī)性，需要取得隨機(jī)數(shù)字，所以我們在這里介紹一下如何用Excel生成隨機(jī)數(shù)字并進(jìn)行抽樣的方法。需要提醒的是，在使用Excel進(jìn)行實(shí)習(xí)前，電腦中的Excel需要完全安裝，所以部分同學(xué)電腦中的office軟件需要重新安裝，否則實(shí)習(xí)無法正常進(jìn)行。本書中例題全部用Excel2007完成。使用Excel進(jìn)行抽樣，首先要對各個總體單位進(jìn)行編號，編號可以按隨機(jī)原則，也可以按有關(guān)標(biāo)志或無關(guān)標(biāo)志，具體可參見本書有關(guān)抽樣的章節(jié)，編號后，將編號輸入工作表。1.我們假定統(tǒng)計(jì)總體有200個總體單位，總體單位的編號從1到200，輸入工作表后如圖10-1所示：圖10-1總體各單位編號表各總體單位的編號輸入完成后，可按以下步驟進(jìn)行操作：第一步：選擇數(shù)據(jù)分析選項(xiàng)（如果你使用的是Excel2003，單擊工具菜單，若無數(shù)據(jù)分析選項(xiàng)，可在工具菜單下選擇加載宏，在彈出的對話框中選擇分析工具庫，便可出現(xiàn)數(shù)據(jù)分析選項(xiàng)；如果你使用的是Excel2007，點(diǎn)擊左上角Office標(biāo)志圖標(biāo)，Excel選項(xiàng)，加載項(xiàng)，在下面的管理下拉列表中選擇“Excel加載項(xiàng)”，轉(zhuǎn)到，勾選“分析工具庫”，確定。），打開數(shù)據(jù)分析對話框，從中選擇抽樣。如圖10-2所示。圖10-2數(shù)據(jù)分析對話框第二步：單擊抽樣選項(xiàng)，確定后彈出抽樣對話框。如圖10-3：圖10-3抽樣對話框第三步：在輸入?yún)^(qū)域框中輸入總體單位編號所在的單元格區(qū)域，在本例是$A$1:$J$20，系統(tǒng)將從A列開始抽取樣本，然后按順序抽取B列至J列。如果輸入?yún)^(qū)域的第一行或第一列為標(biāo)志項(xiàng)（橫行標(biāo)題或縱列標(biāo)題），可單擊標(biāo)志復(fù)選框。第四步：選擇“隨機(jī)模式”，樣本數(shù)為10。在抽樣方法項(xiàng)下，有周期和隨機(jī)兩種抽樣模式。“周期”模式即所謂的等距抽樣（或機(jī)械抽樣），采用這種抽樣方法，需將總體單位數(shù)除以要抽取的樣本單位數(shù)，求得取樣的周期間隔。如我們要在200個總體單位中抽取10個，則在“間隔”框中輸入20；如果在200個總體單位中抽取24個，則在“間隔”框中輸入8（如果不能整除，則取整數(shù)）?！半S機(jī)模式”適用于純隨機(jī)抽樣、分類抽樣、整群抽樣和階段抽樣。采用純隨機(jī)抽樣，只需在“樣本數(shù)”框中輸入要抽取的樣本單位數(shù)即可；若采用分類抽樣，必須先將總體單位按某一標(biāo)志分類編號，然后在每一類中隨機(jī)抽取若干單位，這種抽樣方法實(shí)際是分組法與隨機(jī)抽樣的結(jié)合；整群抽樣也要先將總體單位分類編號，然后按隨機(jī)原則抽取若干類作為樣本，對抽中的類的所有單位全部進(jìn)行調(diào)查。由于本例中沒有分類或分群的標(biāo)志，所以無法進(jìn)行分類或整群抽樣，只適用于等距抽樣和純隨機(jī)抽樣。本例中選擇“隨機(jī)模式”，即選擇了純隨機(jī)抽樣方法進(jìn)行抽樣。第四步：指定輸出區(qū)域，在這里我們輸入$A$22：$A$31，單擊確定后，即可得到抽樣結(jié)果，如圖10-4：圖10-4等距抽樣結(jié)果用Excel進(jìn)行統(tǒng)計(jì)分組用Excel進(jìn)行統(tǒng)計(jì)分組有兩種方法，一是利用FREQUENCY函數(shù)；二是利用數(shù)據(jù)分析中的“直方圖”工具。我們介紹一下后者的操作方法?！纠?0-2】某生產(chǎn)車間50名工人日加工零件數(shù)（單位：個）資料輸入工作表，如圖10-5所示：圖10-5工人月產(chǎn)量資料然后按以下步驟操作。第一步：分組標(biāo)志輸入。分組前要先輸入分組標(biāo)志，選定一個區(qū)域（直方圖菜單中稱這個區(qū)域?yàn)榻邮諈^(qū)域），輸入每一組的上限值，即在$A$7:$A$13位置輸入110，115，120，125，130，135，140。第二步：單擊數(shù)據(jù)分析選項(xiàng)，從其對話框的分析工具列表中選擇直方圖，打開直方圖對話框。如圖10-6所示：圖10-6直方圖對話框第三步：在輸入?yún)^(qū)域輸入$A$1:$J$5，輸入?yún)^(qū)域指等分析數(shù)據(jù)的單元格區(qū)域。在接收區(qū)域輸入$A$7:$A$13。接收區(qū)域指的是分組標(biāo)志所在的區(qū)域，即第一步輸入的分組標(biāo)志上限值。第四步：選擇輸出選項(xiàng)，可選擇輸入?yún)^(qū)域、新工作表組或新工作薄。我們在這里選擇輸入?yún)^(qū)域，可以直接選擇一個區(qū)域，也可以直接輸入一個單元格（代表輸出區(qū)域的左上角），這里我們推薦只輸入一個單元格（本例為A16），因?yàn)槲覀兺孪炔⒉恢谰唧w的輸出區(qū)域有多大。第五步：選擇圖表輸出，可以得到直方圖；選擇累計(jì)百分率，系統(tǒng)將在直方圖上添加累積頻率折線；選擇柏拉圖，可得到按降序排列的直方圖（本例中，由于沒有選擇柏拉圖，所以不是降序排列）。第五步：按確定按鈕，可得輸出結(jié)果如圖10-7：圖10-7頻數(shù)分布和直方圖應(yīng)當(dāng)注意，上圖實(shí)際上是一個條形圖，而不是直方圖，若要把它變成直方圖，可按如下操作：用鼠標(biāo)左鍵單擊直條，然后右鍵單擊，在彈出的快捷菜單中選取“設(shè)置數(shù)據(jù)系列格式”，彈出數(shù)據(jù)系列格式對話框，如圖10-8所示：圖10-8數(shù)據(jù)系列格式對話框在對話框中將分類間距設(shè)置成0%，按關(guān)閉后即可得到直方圖，如圖10-9所示：圖10-9調(diào)整后的直方圖用Excel作統(tǒng)計(jì)圖Excel提供的統(tǒng)計(jì)圖有多種，包括柱形圖、條形圖、折線圖、餅圖、散點(diǎn)圖、面積圖、環(huán)形圖、雷達(dá)圖、曲面圖、氣泡圖、股價(jià)圖、圓柱圖、圓錐圖等，各種圖的作法大同小異?！纠?0-3】我們這里采用浙江省2023年各市GDP資料，看一下如何作餅圖。首先把數(shù)據(jù)輸入到工作表中，如圖10-10所示：圖10-10浙江省2023年各市GDP按以下步驟可作出餅圖：第一步：選中某一單元格，單擊插入菜單，Excel2007菜單顯示如圖10-11：圖10-11插入圖表菜單（1）第二步：在圖表類型中選擇餅圖，再選擇二維餅圖，如圖10-12所示：圖10-12插入圖表菜單（2）第三步：單擊選擇數(shù)據(jù)菜單，在圖表數(shù)據(jù)輸入?yún)^(qū)域（D）中輸入$A$1:$B$12選擇在源數(shù)據(jù)對話框中填入數(shù)據(jù)所在區(qū)域，如圖10-13：圖10-13源數(shù)據(jù)對話框第四步：單擊確定后得到如下餅圖，如圖10-14所示：圖10-14餅圖用Excel作散點(diǎn)圖散點(diǎn)圖是觀察兩個變量之間關(guān)系程度最為直觀的工具之一，利用EXCEL的圖表向?qū)?，可以非常方便的?chuàng)建并且改進(jìn)一個散點(diǎn)圖，也可以在一個圖表中同時(shí)顯示兩個以上變量之間的散點(diǎn)圖。【例10-4】我們這里采用我國1981到2000年的人均GDP和人均居民消費(fèi)資料，看一下如何作散點(diǎn)圖。首先把數(shù)據(jù)輸入到工作表中，如圖10-15所示：圖10-15我國1981到2000年的人均GDP和人均居民消費(fèi)資料具體步驟如上例子相似，選擇插入菜單，選擇散點(diǎn)圖，得散點(diǎn)圖如圖10-16：圖10-16散點(diǎn)圖10.2用EXCEL計(jì)算描述統(tǒng)計(jì)量EXCEL中用于計(jì)算描述統(tǒng)計(jì)量的方法有兩種，函數(shù)方法和描述統(tǒng)計(jì)工具的方法。用函數(shù)計(jì)算描述統(tǒng)計(jì)量常用的描述統(tǒng)計(jì)量有眾數(shù)、中位數(shù)、算術(shù)平均數(shù)、調(diào)和平均數(shù)、幾何平均數(shù)、極差、四分位差、標(biāo)準(zhǔn)差、方差、標(biāo)準(zhǔn)差系數(shù)等。一般來說，在Excel中求這些統(tǒng)計(jì)量，未分組資料可用函數(shù)計(jì)算，已分組資料可用公式計(jì)算。這里我們僅介紹如何用函數(shù)計(jì)算?！纠?0-5】：已知某生產(chǎn)車間50名工人日加工零件數(shù)（單位：個）輸入表格后如圖10-17：圖10-17工人日加工零件數(shù)根據(jù)上面表格中的數(shù)據(jù)，利用函數(shù)求一些統(tǒng)計(jì)指標(biāo)。(1)算術(shù)平均數(shù)單擊任一單元格，輸入“=AVERAGE(A1:J5)”，回車后得算術(shù)平均數(shù)為122.74。(2)眾數(shù)單擊任一空單元格，輸入“=MODE(A1:J5)”，回車后即可得眾數(shù)為123(3)中位數(shù)仍采用上面的例子，單擊任一空單元格，輸入“=MEDIAN（A1：J5）”，回車后得中位數(shù)為123。(4)標(biāo)準(zhǔn)差單擊任一單元格，輸入“=STDEV（A1：J5）”，回車后得標(biāo)準(zhǔn)差為7.74。描述統(tǒng)計(jì)工具量的使用仍使用上面的例子，我們已經(jīng)把數(shù)據(jù)輸入到A1：A30單元格，然后按以下步驟操作：第一步：數(shù)據(jù)菜單中選擇數(shù)據(jù)分析，從其對話框中選擇描述統(tǒng)計(jì)，按確定后打開描述統(tǒng)計(jì)對話框，如圖10-18所示：圖10-18描述統(tǒng)計(jì)對話框第二步：在輸入?yún)^(qū)域中輸入$A$1:$J$5，在輸出區(qū)域中選擇$B$14，其他復(fù)選框可根據(jù)需要選定，選擇匯總統(tǒng)計(jì)，可給出一系列描述統(tǒng)計(jì)量；選擇平均數(shù)置信度，會給出用樣本平均數(shù)估計(jì)總體平均數(shù)的置信區(qū)間；第K大值和第K小值會給出樣本中第K個大值和第K個小值。分組方式選擇“逐行”，則每行分別進(jìn)行統(tǒng)計(jì)，這里有5行，則有5組統(tǒng)計(jì)結(jié)果。第三步：單擊確定，可得輸出結(jié)果，如圖10-19所示：圖10-19描述統(tǒng)計(jì)輸出結(jié)果10.3用EXCEL進(jìn)行時(shí)間序列分析測定增長量和平均增長量【例10-6】：根據(jù)我國1998-2007年國內(nèi)生產(chǎn)總值，計(jì)算逐期增長量、累計(jì)增長量和平均增長量。原始數(shù)據(jù)如圖10-20，計(jì)算結(jié)果如圖10-21。圖10-20我國1998-2007年國內(nèi)生產(chǎn)總值計(jì)算步驟如下：第一步：計(jì)算逐期增長量：在C3中輸入公式：=B3-B2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C11區(qū)域。第二步：計(jì)算累計(jì)增長量：在D3中輸入公式：=B3-$B$2，并用鼠標(biāo)拖曳公式復(fù)制到D3：D11區(qū)域。第三步：計(jì)算平均增長量（水平法）：在C13中輸入公式：=（B11-B2）/9，按回車鍵，即可得到平均增長量。圖10-21用EXCEL計(jì)算增長量和平均增長量資料及結(jié)果測定發(fā)展速度和平均發(fā)展速度【例10-7】：以我國1998-2007年國內(nèi)生產(chǎn)總值為例，說明如何計(jì)算定基發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速度。如圖10-22：圖10-22用EXCEL計(jì)算發(fā)展速度和平均發(fā)展速度資料及結(jié)果第一步：計(jì)算定基發(fā)展速度：在C3中輸入公式：=B3/$B$2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C11區(qū)域。第二步：計(jì)算環(huán)比發(fā)展速度：在D3中輸入公式：=B3/B2，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D11區(qū)域。第三步：計(jì)算平均發(fā)展速度（水平法）：選中D13單元格，單擊插入菜單，選擇函數(shù)選項(xiàng)，出現(xiàn)插入函數(shù)對話框后，選擇GEOMEAN（返回幾何平均值）函數(shù)，在數(shù)值區(qū)域中輸入D3：D11即可。計(jì)算長期趨勢【例10-8】：我們用我國1988~2007年國內(nèi)生產(chǎn)總值資料來說明如何用移動平均法計(jì)算長期趨勢。如圖10-23：圖10-23用EXCEL計(jì)算長期趨勢資料及結(jié)果第一步：計(jì)算三項(xiàng)移動平均：在C3中輸入“=（B2+B3+B4）/3”第二步：計(jì)算四項(xiàng)移動平均：在D4中輸入“=SUM（B2：B5）/4”第三步：計(jì)算二項(xiàng)移正平均數(shù)：在E4中輸入“=（D4+D5）/2”計(jì)算季節(jié)變動【例10-9】：利用某種商品四年分季度的銷售額資料，說明如何用移動平均趨勢剔除法測定季節(jié)變動。如圖10-24：圖10-24用EXCEL計(jì)算季節(jié)變動資料第一步：按圖上的格式在A列輸入年份，在B列輸入季別，在C列輸入銷售額。第二步：計(jì)算四項(xiàng)移動平均：在D3中輸入“=SUM（C2：C4）/4”第三步：計(jì)算趨勢值（即二項(xiàng)移動平均）T：在E4中輸入“=（D3+D4）/2”第四步：剔除長期趨勢，即計(jì)算Y/T：在F4中輸入“=C4/E4”第五步：重新排列F4：F15區(qū)域中的數(shù)字，使同季的數(shù)字位于一列，共排成四列。（見圖3-6）。第六步：計(jì)算各年同季平均數(shù)：在B25單元格中輸入公式：=average(B22:B24)；在C25中輸入公式=average(C22:C24)；在D25中輸入公式=average(D21:23)；在E25中輸入公式=average(E21:E23)。第七步：計(jì)算調(diào)整系數(shù)：在B27中輸入公式：=4/sum(B25:E25)第八步：計(jì)算季節(jié)比率：在B26中輸入公式：=B25*$B$27，并用鼠標(biāo)拖曳將公式復(fù)制到單元格區(qū)域B26：E26，就可以得到季節(jié)比率的值，具體結(jié)果見圖10-25：圖10-25用EXCEL計(jì)算季節(jié)變動結(jié)果10.4用EXCEL進(jìn)行指數(shù)分析用Excel計(jì)算總指數(shù)【例10-10】：設(shè)某糧油連鎖店1998年和1999年三種商品的零售價(jià)格和銷售量資料如圖10-26。試分別以基期銷售量和零售價(jià)格為權(quán)數(shù)，計(jì)算三種商品的價(jià)格綜合指數(shù)和銷售量綜合指數(shù)。圖10-26用EXCEL計(jì)算總指數(shù)資料及結(jié)果計(jì)算步驟：第一步：計(jì)算各個p0q0：在G3中輸入“=C3*E3”第二步：計(jì)算各個p0*q1：有H3中輸入“=D3*E3”第三步：計(jì)算各個p1*q1：有I3中輸入“=D3*F3”第四步：計(jì)算Σp0q0和Σp0q1：選定G3：G5區(qū)域，單擊工具欄上的“Σ”按鈕，在G6出現(xiàn)該列的求和值。選定H3：H5區(qū)域，單擊工具欄上的“Σ”按鈕，在H6出現(xiàn)該列的求和值。選定HI：I5區(qū)域，單擊工具欄上的“Σ”按鈕，在I6出現(xiàn)該列的求和值。第五步：計(jì)算生產(chǎn)量綜合指數(shù)Kq=Σp0q1/Σp0q0：在B8中輸入“=H6/G6”第六步：計(jì)算價(jià)格綜合指數(shù)Kp=Σp1q1/Σp0q1：在B9中輸入“=I6/H6”注意：在輸入公式的時(shí)候，不要忘記等號，否則就不會出現(xiàn)數(shù)值。用Excel計(jì)算平均指數(shù)現(xiàn)以生產(chǎn)量平均指數(shù)為例，說明加權(quán)算術(shù)平均法的計(jì)算方法?！纠?0-11】：仍以上例資料，計(jì)算生產(chǎn)量平均指數(shù)和價(jià)格平均指數(shù)。如圖10-27。圖10-27用EXCEL計(jì)算平均指數(shù)資料及結(jié)果計(jì)算步驟：第一步：計(jì)算個體指數(shù)k=q1/q0：在I3中輸入“=D3/C3”第二步：計(jì)算k*p0q0并求和。在K3中輸入“=I3*G3”并用鼠標(biāo)拖曳將公式復(fù)制到K3：K5區(qū)域。選定K3：K5區(qū)域，單擊工具欄上的：“Σ”第三步：計(jì)算生產(chǎn)量平均指數(shù)：在B7中輸入“=K6/G5”價(jià)格平均指數(shù)計(jì)算步驟略。10.5用EXCEL進(jìn)行相關(guān)與回歸分析【例10-12】：中國居民人均消費(fèi)支出與人均GDP（元/人）如表10-1所示：表10-1中國居民人均消費(fèi)支出與人均GDP計(jì)量單位：元年份人均居民消費(fèi)人均GDPCONSPGDPP1978395.8675.11979437716.91980464.1763.71981501.9792.41982533.5851.11983572.8931.41984635.61059.219857161185.21986746.51269.61987788.31393.61988836.415271989779.71565.91990797.11602.31991861.41727.21992966.61949.819931048.62187.919941108.72436.119951213.12663.719961322.82889.119971380.93111.919981460.63323.119991564.43529.320001690.83789.7用EXCEL進(jìn)行相關(guān)分析首先把有關(guān)數(shù)據(jù)輸入EXCEL的單元格中。用EXCEL進(jìn)行相關(guān)分析有兩種方法，一是利用相關(guān)系數(shù)函數(shù)，另一種是利用相關(guān)分析宏。（1）利用函數(shù)計(jì)算相關(guān)系數(shù)在EXCEL中，提供了兩個計(jì)算兩個變量之間相關(guān)系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個函數(shù)是等價(jià)的，這里我們介紹用CORREL函數(shù)計(jì)算相關(guān)系數(shù)：第一步：單擊任一個空白單元格，單擊插入菜單，選擇函數(shù)選項(xiàng)，打開粘貼函數(shù)對話框，在函數(shù)分類中選擇統(tǒng)計(jì)，在函數(shù)名中選擇CORREL，單擊確定后，出現(xiàn)CORREL對話框。圖10-27EXCEL數(shù)據(jù)集第二步：在array1中輸入B4：B26，在array2中輸入C4：C26，即可在對話框下方顯示出計(jì)算結(jié)果為0.996。如圖10-28和圖10-29所