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

1、Excel在統(tǒng)計(jì)學(xué)中的應(yīng)用用Excel搜集與整理數(shù)據(jù)用Excel搜集數(shù)據(jù)統(tǒng)計(jì)數(shù)據(jù)的收集是統(tǒng)計(jì)工作過(guò)程的基礎(chǔ)性環(huán)節(jié)，方法有多種多樣，其中以抽樣調(diào)查最有代表性。在抽樣調(diào)查中，為保證抽樣的隨機(jī)性，需要取得隨機(jī)數(shù)字，所以我們?cè)谶@里介紹一下如何用Excel生成隨機(jī)數(shù)字并進(jìn)行抽樣的方法。需要提醒的是，在使用Excel進(jìn)行實(shí)習(xí)前，電腦中的Excel需要完全安裝，所以部分同學(xué)電腦中的office軟件需要重新安裝，否則實(shí)習(xí)無(wú)法正常進(jìn)行。本書(shū)中例題全部用Excel2007完成。使用Excel進(jìn)行抽樣，首先要對(duì)各個(gè)總體單位進(jìn)行編號(hào)，編號(hào)可以按隨機(jī)原則，也可以按有關(guān)標(biāo)志或無(wú)關(guān)標(biāo)志，具體可參見(jiàn)本書(shū)有關(guān)抽樣的章節(jié)，編號(hào)后

2、，將編號(hào)輸入工作表。1.我們假定統(tǒng)計(jì)總體有200個(gè)總體單位，總體單位的編號(hào)從1到200，輸入工作表后如圖10-1所示：圖10-1 總體各單位編號(hào)表各總體單位的編號(hào)輸入完成后，可按以下步驟進(jìn)行操作：第一步：選擇數(shù)據(jù)分析選項(xiàng)（如果你使用的是Excel2003，單擊工具菜單，若無(wú)數(shù)據(jù)分析選項(xiàng)，可在工具菜單下選擇加載宏，在彈出的對(duì)話框中選擇分析工具庫(kù)，便可出現(xiàn)數(shù)據(jù)分析選項(xiàng)；如果你使用的是Excel2007，點(diǎn)擊左上角Office標(biāo)志圖標(biāo)，Excel選項(xiàng)，加載項(xiàng)，在下面的管理下拉列表中選擇“Excel加載項(xiàng)”，轉(zhuǎn)到，勾選“分析工具庫(kù)”，確定。），打開(kāi)數(shù)據(jù)分析對(duì)話框，從中選擇抽樣。如圖10-2所示。圖10

3、-2數(shù)據(jù)分析對(duì)話框第二步：?jiǎn)螕舫闃舆x項(xiàng)，確定后彈出抽樣對(duì)話框。如圖10-3：圖10-3 抽樣對(duì)話框第三步：在輸入?yún)^(qū)域框中輸入總體單位編號(hào)所在的單元格區(qū)域，在本例是$A$1:$J$20，系統(tǒng)將從A列開(kāi)始抽取樣本，然后按順序抽取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個(gè)總體單位中抽取10個(gè)，則在“間隔”框中輸入20；如果在200個(gè)總體

4、單位中抽取24個(gè)，則在“間隔”框中輸入8（如果不能整除，則取整數(shù)）?！半S機(jī)模式”適用于純隨機(jī)抽樣、分類抽樣、整群抽樣和階段抽樣。采用純隨機(jī)抽樣，只需在“樣本數(shù)”框中輸入要抽取的樣本單位數(shù)即可；若采用分類抽樣，必須先將總體單位按某一標(biāo)志分類編號(hào)，然后在每一類中隨機(jī)抽取若干單位，這種抽樣方法實(shí)際是分組法與隨機(jī)抽樣的結(jié)合；整群抽樣也要先將總體單位分類編號(hào)，然后按隨機(jī)原則抽取若干類作為樣本，對(duì)抽中的類的所有單位全部進(jìn)行調(diào)查。由于本例中沒(méi)有分類或分群的標(biāo)志，所以無(wú)法進(jìn)行分類或整群抽樣，只適用于等距抽樣和純隨機(jī)抽樣。本例中選擇“隨機(jī)模式”，即選擇了純隨機(jī)抽樣方法進(jìn)行抽樣。第四步：指定輸出區(qū)域，在這里我們輸

5、入$A$22：$A$31，單擊確定后，即可得到抽樣結(jié)果，如圖10-4：圖10-4等距抽樣結(jié)果10.1.2 用Excel進(jìn)行統(tǒng)計(jì)分組用Excel進(jìn)行統(tǒng)計(jì)分組有兩種方法，一是利用FREQUENCY函數(shù)；二是利用數(shù)據(jù)分析中的“直方圖”工具。我們介紹一下后者的操作方法?！纠?0-2】某生產(chǎn)車間50名工人日加工零件數(shù)（單位：個(gè)）資料輸入工作表，如圖10-5所示：圖10-5工人月產(chǎn)量資料然后按以下步驟操作。第一步：分組標(biāo)志輸入。分組前要先輸入分組標(biāo)志，選定一個(gè)區(qū)域（直方圖菜單中稱這個(gè)區(qū)域?yàn)榻邮諈^(qū)域），輸入每一組的上限值，即在$A$7:$A$13位置輸入110，115，120，125，130，135，140

6、。第二步：?jiǎn)螕魯?shù)據(jù)分析選項(xiàng)，從其對(duì)話框的分析工具列表中選擇直方圖，打開(kāi)直方圖對(duì)話框。如圖10-6所示：圖10-6直方圖對(duì)話框第三步：在輸入?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ū)域、新工作表組或新工作薄。我們?cè)谶@里選擇輸入?yún)^(qū)域，可以直接選擇一個(gè)區(qū)域，也可以直接輸入一個(gè)單元格（代表輸出區(qū)域的左上角），這里我們推薦只輸入一個(gè)單元格（本例為A16），因?yàn)槲覀兺孪炔⒉恢谰唧w的輸出區(qū)域有多大。第五步：選擇圖表輸出，可以得到直方圖；選擇累計(jì)百分率

7、，系統(tǒng)將在直方圖上添加累積頻率折線；選擇柏拉圖，可得到按降序排列的直方圖（本例中，由于沒(méi)有選擇柏拉圖，所以不是降序排列）。第五步：按確定按鈕，可得輸出結(jié)果如圖10-7：圖10-7頻數(shù)分布和直方圖應(yīng)當(dāng)注意，上圖實(shí)際上是一個(gè)條形圖，而不是直方圖，若要把它變成直方圖，可按如下操作：用鼠標(biāo)左鍵單擊直條，然后右鍵單擊，在彈出的快捷菜單中選取“設(shè)置數(shù)據(jù)系列格式”，彈出數(shù)據(jù)系列格式對(duì)話框，如圖10-8所示：圖10-8數(shù)據(jù)系列格式對(duì)話框在對(duì)話框中將分類間距設(shè)置成0%，按關(guān)閉后即可得到直方圖，如圖10-9所示：圖10-9調(diào)整后的直方圖10.1.3 用Excel作統(tǒng)計(jì)圖Excel提供的統(tǒng)計(jì)圖有多種，包括柱形圖、條

8、形圖、折線圖、餅圖、散點(diǎn)圖、面積圖、環(huán)形圖、雷達(dá)圖、曲面圖、氣泡圖、股價(jià)圖、圓柱圖、圓錐圖等，各種圖的作法大同小異?！纠?0-3】我們這里采用浙江省2008年各市GDP資料，看一下如何作餅圖。首先把數(shù)據(jù)輸入到工作表中，如圖10-10所示：圖10-10浙江省2008年各市GDP按以下步驟可作出餅圖：第一步：選中某一單元格，單擊插入菜單，Excel2007菜單顯示如圖10-11：圖10-11插入圖表菜單（1）第二步：在圖表類型中選擇餅圖，再選擇二維餅圖，如圖10-12所示：圖10-12插入圖表菜單（2）第三步：?jiǎn)螕暨x擇數(shù)據(jù)菜單，在圖表數(shù)據(jù)輸入?yún)^(qū)域（D）中輸入$A$1:$B$12選擇在源數(shù)據(jù)對(duì)話框中

9、填入數(shù)據(jù)所在區(qū)域，如圖10-13：圖10-13源數(shù)據(jù)對(duì)話框 第四步：?jiǎn)螕舸_定后得到如下餅圖，如圖10-14所示：圖10-14餅圖10.1.4 用Excel作散點(diǎn)圖散點(diǎn)圖是觀察兩個(gè)變量之間關(guān)系程度最為直觀的工具之一，利用EXCEL的圖表向?qū)?，可以非常方便的?chuàng)建并且改進(jìn)一個(gè)散點(diǎn)圖，也可以在一個(gè)圖表中同時(shí)顯示兩個(gè)以上變量之間的散點(diǎn)圖。【例10-4】我們這里采用我國(guó)1981到2000年的人均GDP和人均居民消費(fèi)資料，看一下如何作散點(diǎn)圖。首先把數(shù)據(jù)輸入到工作表中，如圖10-15所示：圖10-15 我國(guó)1981到2000年的人均GDP和人均居民消費(fèi)資料具體步驟如上例子相似，選擇插入菜單，選擇散點(diǎn)圖，得散點(diǎn)

10、圖如圖10-16：圖10-16散點(diǎn)圖10.2 用EXCEL計(jì)算描述統(tǒng)計(jì)量EXCEL中用于計(jì)算描述統(tǒng)計(jì)量的方法有兩種，函數(shù)方法和描述統(tǒng)計(jì)工具的方法。10.2.1 用函數(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ù)等。一般來(lái)說(shuō)，在Excel中求這些統(tǒng)計(jì)量，未分組資料可用函數(shù)計(jì)算，已分組資料可用公式計(jì)算。這里我們僅介紹如何用函數(shù)計(jì)算?！纠?0-5】：已知某生產(chǎn)車間50名工人日加工零件數(shù)（單位：個(gè)）輸入表格后如圖10-17：圖10-17工人日加工零件數(shù)根據(jù)上面表格中的數(shù)據(jù)，利用函數(shù)求一些統(tǒng)計(jì)指標(biāo)。(1) 算術(shù)平均數(shù)單擊任一單

11、元格，輸入“=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。10.2.2 描述統(tǒng)計(jì)工具量的使用仍使用上面的例子，我們已經(jīng)把數(shù)據(jù)輸入到A1：A30單元格，然后按以下步驟操作：第一步：數(shù)據(jù)菜單中選擇數(shù)據(jù)分析，從其對(duì)話框中選擇描述統(tǒng)計(jì)，按確定后打開(kāi)描述統(tǒng)計(jì)對(duì)話框，如圖10-18所示：圖10-18

12、描述統(tǒng)計(jì)對(duì)話框第二步：在輸入?yún)^(qū)域中輸入$A$1:$J$5，在輸出區(qū)域中選擇$B$14，其他復(fù)選框可根據(jù)需要選定，選擇匯總統(tǒng)計(jì)，可給出一系列描述統(tǒng)計(jì)量；選擇平均數(shù)置信度，會(huì)給出用樣本平均數(shù)估計(jì)總體平均數(shù)的置信區(qū)間；第K大值和第K小值會(huì)給出樣本中第K個(gè)大值和第K個(gè)小值。分組方式選擇“逐行”，則每行分別進(jìn)行統(tǒng)計(jì)，這里有5行，則有5組統(tǒng)計(jì)結(jié)果。第三步：?jiǎn)螕舸_定，可得輸出結(jié)果，如圖10-19所示：圖10-19描述統(tǒng)計(jì)輸出結(jié)果10.3 用EXCEL進(jìn)行時(shí)間序列分析10.3.1 測(cè)定增長(zhǎng)量和平均增長(zhǎng)量【例10-6】：根據(jù)我國(guó)1998-2007年國(guó)內(nèi)生產(chǎn)總值，計(jì)算逐期增長(zhǎng)量、累計(jì)增長(zhǎng)量和平均增長(zhǎng)量。原始數(shù)據(jù)如

13、圖10-20，計(jì)算結(jié)果如圖10-21。圖10-20 我國(guó)1998-2007年國(guó)內(nèi)生產(chǎn)總值計(jì)算步驟如下：第一步：計(jì)算逐期增長(zhǎng)量：在C3中輸入公式：=B3-B2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C11區(qū)域。第二步：計(jì)算累計(jì)增長(zhǎng)量：在D3中輸入公式：=B3-$B$2，并用鼠標(biāo)拖曳公式復(fù)制到D3：D11區(qū)域。第三步：計(jì)算平均增長(zhǎng)量（水平法）：在C13中輸入公式：=（B11-B2）/9，按回車鍵，即可得到平均增長(zhǎng)量。圖10-21用EXCEL計(jì)算增長(zhǎng)量和平均增長(zhǎng)量資料及結(jié)果10.3.2 測(cè)定發(fā)展速度和平均發(fā)展速度【例10-7】：以我國(guó)1998-2007年國(guó)內(nèi)生產(chǎn)總值為例，說(shuō)明如何計(jì)算定基發(fā)展速度、環(huán)比發(fā)展速

14、度和平均發(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ù)對(duì)話框后，選擇GEOMEAN（返回幾何平均值）函數(shù)，在數(shù)值區(qū)域中輸入D3：D11即可。10.3.3 計(jì)算長(zhǎng)期趨勢(shì)【例10-8】：我們用我國(guó)19882007年國(guó)內(nèi)生產(chǎn)總值資料來(lái)說(shuō)明如何用移動(dòng)平均法計(jì)算長(zhǎng)期趨勢(shì)。

15、如圖10-23：圖10-23 用EXCEL計(jì)算長(zhǎng)期趨勢(shì)資料及結(jié)果第一步：計(jì)算三項(xiàng)移動(dòng)平均：在C3中輸入“=（B2+B3+B4）/3”，并用鼠標(biāo)拖曳將公式復(fù)制到C4：C20區(qū)域。第二步：計(jì)算四項(xiàng)移動(dòng)平均：在D4中輸入“=SUM（B2：B5）/4”，并用鼠標(biāo)拖曳將公式復(fù)制到D5：D20區(qū)域。第三步：計(jì)算二項(xiàng)移正平均數(shù)：在E4中輸入“=（D4+D5）/2”，并用公式拖曳將公式復(fù)制到E5：E19區(qū)域。10.3.4 計(jì)算季節(jié)變動(dòng)【例10-9】：利用某種商品四年分季度的銷售額資料，說(shuō)明如何用移動(dòng)平均趨勢(shì)剔除法測(cè)定季節(jié)變動(dòng)。如圖10-24：圖10-24用EXCEL計(jì)算季節(jié)變動(dòng)資料第一步：按圖上的格式在A列輸

16、入年份，在B列輸入季別，在C列輸入銷售額。第二步：計(jì)算四項(xiàng)移動(dòng)平均：在D3中輸入“=SUM（C2：C4）/4”，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D15區(qū)域。第三步：計(jì)算趨勢(shì)值（即二項(xiàng)移動(dòng)平均）T：在E4中輸入“=（D3+D4）/2”，并用鼠標(biāo)拖曳將公式復(fù)制到E4：E15區(qū)域。第四步：剔除長(zhǎng)期趨勢(shì)，即計(jì)算Y/T：在F4中輸入“=C4/E4”，并用鼠標(biāo)拖曳將公式復(fù)制到F4：F15區(qū)域。第五步：重新排列F4：F15區(qū)域中的數(shù)字，使同季的數(shù)字位于一列，共排成四列。（見(jiàn)圖3-6）。第六步：計(jì)算各年同季平均數(shù)：在B25單元格中輸入公式：=average(B22:B24)；在C25中輸入公式=average(

17、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é)果見(jiàn)圖10-25：圖10-25用EXCEL計(jì)算季節(jié)變動(dòng)結(jié)果10.4 用EXCEL進(jìn)行指數(shù)分析10.4.1 用Excel計(jì)算總指數(shù)【例10-10】：設(shè)某糧油連鎖店1998年和1999年三種商品的零售價(jià)格和銷售量資料如圖10-26。試分別以基期銷售量和零售價(jià)格為權(quán)

18、數(shù)，計(jì)算三種商品的價(jià)格綜合指數(shù)和銷售量綜合指數(shù)。圖10-26用EXCEL計(jì)算總指數(shù)資料及結(jié)果計(jì)算步驟：第一步：計(jì)算各個(gè)p0q0：在G3中輸入“=C3*E3”，并用鼠標(biāo)拖曳將公式復(fù)制到G3：G5區(qū)域。第二步：計(jì)算各個(gè)p0*q1：有H3中輸入“=D3*E3”，并用鼠標(biāo)拖曳將公式復(fù)制到H3：H5區(qū)域。第三步：計(jì)算各個(gè)p1*q1：有I3中輸入“=D3*F3”，并用鼠標(biāo)拖曳將公式復(fù)制到I3：I5區(qū)域。第四步：計(jì)算p0q0和p0q1：選定G3：G5區(qū)域，單擊工具欄上的“”按鈕，在G6出現(xiàn)該列的求和值。選定H3：H5區(qū)域，單擊工具欄上的“”按鈕，在H6出現(xiàn)該列的求和值。選定HI：I5區(qū)域，單擊工具欄上的“”

19、按鈕，在I6出現(xiàn)該列的求和值。第五步：計(jì)算生產(chǎn)量綜合指數(shù)Kq=p0q1/p0q0：在B8中輸入“=H6/G6”便可得到生產(chǎn)量綜合指數(shù)。第六步：計(jì)算價(jià)格綜合指數(shù)Kp=p1q1/p0q1：在B9中輸入“=I6/H6”便可得到價(jià)格綜合指數(shù)。注意：在輸入公式的時(shí)候，不要忘記等號(hào)，否則就不會(huì)出現(xiàn)數(shù)值。10.4.2 用Excel計(jì)算平均指數(shù)現(xiàn)以生產(chǎn)量平均指數(shù)為例，說(shuō)明加權(quán)算術(shù)平均法的計(jì)算方法?！纠?0-11】：仍以上例資料，計(jì)算生產(chǎn)量平均指數(shù)和價(jià)格平均指數(shù)。如圖10-27。圖10-27用EXCEL計(jì)算平均指數(shù)資料及結(jié)果計(jì)算步驟：第一步：計(jì)算個(gè)體指數(shù) k=q1/q0：在I3中輸入“=D3/C3”。并用鼠標(biāo)拖

20、曳將公式復(fù)制到I3：I5區(qū)域。第二步：計(jì)算k*p0q0并求和。在K3中輸入“=I3*G3”并用鼠標(biāo)拖曳將公式復(fù)制到K3：K5區(qū)域。選定K3：K5區(qū)域，單擊工具欄上的：“”按鈕，在K6列出現(xiàn)該列的求和值。第三步：計(jì)算生產(chǎn)量平均指數(shù)：在B7中輸入“=K6/G5”即得到所求的值。價(jià)格平均指數(shù)計(jì)算步驟略。10.5 用EXCEL進(jìn)行相關(guān)與回歸分析【例10-12】：中國(guó)居民人均消費(fèi)支出與人均GDP（元/人）如表10-1所示：表10-1中國(guó)居民人均消費(fèi)支出與人均GDP 計(jì)量單位：元年份人均居民消費(fèi)人均GDPCONSPGDPP1978395.8675.11979437716.91980464.1763.719

21、81501.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.71

22、0.5.1 用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中，提供了兩個(gè)計(jì)算兩個(gè)變量之間相關(guān)系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個(gè)函數(shù)是等價(jià)的，這里我們介紹用CORREL函數(shù)計(jì)算相關(guān)系數(shù)：第一步：?jiǎn)螕羧我粋€(gè)空白單元格，單擊插入菜單，選擇函數(shù)選項(xiàng)，打開(kāi)粘貼函數(shù)對(duì)話框，在函數(shù)分類中選擇統(tǒng)計(jì)，在函數(shù)名中選擇CORREL，單擊確定后，出現(xiàn)CORREL對(duì)話框。圖10-27 EXCEL數(shù)據(jù)集第二步：在array1中輸入B4：B26，在array2中輸入C4：C26，即可在對(duì)話框下方顯示出