excel在統(tǒng)計(jì)中的應(yīng)用

1、第十章 Excel在統(tǒng)計(jì)中的應(yīng)用,學(xué)習(xí)目標(biāo)本章結(jié)構(gòu)教學(xué)內(nèi)容統(tǒng)計(jì)術(shù)語案例分析 課后練習(xí),學(xué)習(xí)目標(biāo),通過本章的學(xué)習(xí)了解Excel是統(tǒng)計(jì)工作的基本工具。理解用Excel處理統(tǒng)計(jì)數(shù)據(jù)及統(tǒng)計(jì)分析的基本程序與基本原理。掌握用Excel進(jìn)行時(shí)間序列分析、指數(shù)分析和相關(guān)與回歸分析。熟練掌握及運(yùn)用Excel搜集與整理數(shù)據(jù)、計(jì)算描述統(tǒng)計(jì)量。,本章結(jié)構(gòu),第一節(jié) 用Excel搜集與整理數(shù)據(jù) 第二節(jié) 用Excel計(jì)算描述統(tǒng)計(jì)量 第三節(jié) 用

2、Excel進(jìn)行時(shí)間序列分析 第四節(jié) 用Excel進(jìn)行指數(shù)分析第五節(jié) 用Excel進(jìn)行相關(guān)與回歸分析,第一節(jié) 用Excel搜集與整理數(shù)據(jù),一、用Excel搜集數(shù)據(jù)二、用Excel整理數(shù)據(jù) 三、用Excel作統(tǒng)計(jì)圖,一、用Excel搜集數(shù)據(jù),搜集數(shù)據(jù)的方法有多種，可以采用統(tǒng)計(jì)報(bào)表、典型調(diào)查、重點(diǎn)調(diào)查或抽樣調(diào)查，以后我國(guó)的統(tǒng)計(jì)調(diào)查將以抽樣為主。針對(duì)抽樣調(diào)查，Excel的數(shù)據(jù)分析工具中提供了一個(gè)專門的“抽樣”工具，可以幫助使用者快

3、速完成抽樣工作。 使用Excel進(jìn)行抽樣，首先要對(duì)各個(gè)總體單位進(jìn)行編號(hào)，編號(hào)可以按隨機(jī)原則，也可以按有關(guān)標(biāo)志或無關(guān)標(biāo)志。 例10-1假定有100名消費(fèi)者，從中選取15人進(jìn)行工資收入調(diào)查。,首先，將100名消費(fèi)者編號(hào)為1~100號(hào)，輸入編號(hào) （如圖10-1）,其次，利用“抽樣”工具進(jìn)行抽樣的具體操作如下：,第一步：?jiǎn)螕簟肮ぞ摺辈藛?，選擇“數(shù)據(jù)分析”選項(xiàng),若無數(shù)據(jù)分析選項(xiàng)，可在工具菜單下選擇加載宏，在彈出的對(duì)話

4、框中選擇分析工具庫，便可出現(xiàn)數(shù)據(jù)分析選項(xiàng)。,第二步：打開“數(shù)據(jù)分析”對(duì)話框，從其對(duì)話框的“分析工具”列表中選擇“抽樣”選項(xiàng)，（如圖10-2所示）,第三步：?jiǎn)螕簟按_定”按鈕，打開“抽樣”對(duì)話框，確定輸入?yún)^(qū)域、抽樣方法和輸出區(qū)域（如圖10-3所示）,1．確定輸入?yún)^(qū)域2．選定抽樣方法在“抽樣方法”框中，有“周期”和“隨機(jī)”兩種抽樣模式。（1）“周期”模式即所謂的等距抽樣、機(jī)械抽樣或系統(tǒng)抽樣。此種抽樣方法，需要確定周期間隔，周期間隔由總體

5、單位數(shù)除以要抽取的樣本數(shù)而求得。本例中，要在100個(gè)總體單位中抽取15個(gè)，則在“間隔”框中輸入6。（2）“隨機(jī)”模式適用于簡(jiǎn)單隨機(jī)抽樣、分層抽樣、整群抽樣和階段抽樣。3．指定輸出方向在“輸出選項(xiàng)”框中有三種輸出去向。在“輸出區(qū)域”框中輸入總體單位編號(hào)所在的單元格區(qū)域。在本例中，輸入?yún)^(qū)域?yàn)?A$12。也可以通過選擇“新工作表”或“新工作簿”將抽樣結(jié)果放在新工作表或新工作薄中。,第四步：?jiǎn)螕簟按_定”按鈕后，在指定的位置給出抽樣的結(jié)果。

6、（如圖10-4所示）,二、用Excel整理數(shù)據(jù),通過統(tǒng)計(jì)調(diào)查得到的數(shù)據(jù)是雜亂的，沒有規(guī)則的，因此，必須對(duì)搜集到的大量的原始數(shù)據(jù)加工整理，經(jīng)過數(shù)據(jù)分析得到科學(xué)結(jié)論。統(tǒng)計(jì)整理包括對(duì)數(shù)據(jù)進(jìn)行分類匯總并計(jì)算各類指標(biāo)及利用統(tǒng)計(jì)圖或統(tǒng)計(jì)表描述統(tǒng)計(jì)匯總結(jié)果等。Excel提供了多種數(shù)據(jù)整理工具：頻數(shù)分布函數(shù)與直方圖分析工具等。,對(duì)其按800~900、900~1000、1000~1100、1100~1200、1200~1300、1300~1400、140

7、0~1500、1500~1600、1600以上分為9個(gè)組。,例10-2 根據(jù)抽樣調(diào)查，某月某市50戶居民購買消費(fèi)品支出資料如圖10-5所示。（單位：元）,1．頻數(shù)分布函數(shù),Excel提供了一個(gè)專門用于統(tǒng)計(jì)分組的頻數(shù)分布函數(shù)（FREQUENCY），它以一列垂直數(shù)組返回某個(gè)區(qū)域中的數(shù)據(jù)分布，描述數(shù)據(jù)分布狀態(tài)。用頻數(shù)分布函數(shù)進(jìn)行統(tǒng)計(jì)分組的操作過程如下。首先，在使用此函數(shù)時(shí)，先將樣本數(shù)據(jù)排成一列，本例中為A1：A50。然后，利用頻數(shù)分布函

8、數(shù)進(jìn)行統(tǒng)計(jì)分組和計(jì)算頻數(shù)，具體操作步驟如下：,第一步：選定單元格區(qū)域，本例中選定的區(qū)域?yàn)镈3：D11，單擊“插入”菜單，選擇“函數(shù)”選項(xiàng)，彈出“插入函數(shù)”對(duì)話框，（如圖10-6所示）,在“選擇類別”中選擇“統(tǒng)計(jì)”，在“選擇函數(shù)”中選擇“FREQUENCY”（如圖10-7所示）,,第二步：打開“FREQUENCY”對(duì)話框，輸入待分組數(shù)據(jù)與分組標(biāo)志 （如圖10-8所示）,第三步：按“Ctrl+Shift+Enter” 組合鍵，在最初選定單元

9、格區(qū)域內(nèi)得到頻數(shù)分布結(jié)果，在本例中為D3：D11，（如圖10-9所示）,2．直方圖分析工具,與頻數(shù)分布函數(shù)只能進(jìn)行統(tǒng)計(jì)分組和頻數(shù)計(jì)算相比，直方圖分析工具可完成數(shù)據(jù)的分組、頻數(shù)分布與累積頻數(shù)的計(jì)、繪制直方圖與累積折線圖等一系列操作。仍以例10-2為操作范例，闡述直方圖分析工具的統(tǒng)計(jì)整理功能，其操作過程如下。,首先，將樣本數(shù)據(jù)排成一列，最好對(duì)數(shù)據(jù)進(jìn)行排序，本例中已利用排序操作排好序，為A1：A51。輸入分組標(biāo)志，本例中為B1：B10，分別是

10、899、999、1099、1199、1299、1399、1499、1599、1699（如圖10-10所示）,然后 ，利用直方圖分析工具進(jìn)行分析，具體操作步驟如下。,第一步：?jiǎn)螕簟肮ぞ摺辈藛危x擇“數(shù)據(jù)分析”選項(xiàng)；打開“數(shù)據(jù)分析”對(duì)話框，從“分析工具”列表中選擇“直方圖”選項(xiàng)，（如圖10-11所示）,第二步：打開“直方圖”對(duì)話框，確定輸入?yún)^(qū)域、接收區(qū)域和輸出區(qū)域，（如圖10-12所示）,（1）“輸入?yún)^(qū)域”輸入待分析數(shù)據(jù)區(qū)域的單元格引用，若

11、輸入?yún)^(qū)域有標(biāo)志項(xiàng)，則選中“標(biāo)志”復(fù)選框；否則，系統(tǒng)自動(dòng)生成數(shù)據(jù)標(biāo)志?！敖邮諈^(qū)域”輸入接收區(qū)域的單元格引用，該框可為空，則系統(tǒng)自動(dòng)利用輸入?yún)^(qū)域中的最小值和最大值建立平均分布的區(qū)間間隔的分組。本例中輸入?yún)^(qū)域?yàn)?A$2:$A$51，接收區(qū)域?yàn)?B$2:$B$10。（2）在“輸出”選項(xiàng)中可選擇輸出去向，輸出去向類似于“抽樣”對(duì)話框的輸出去向。本例中選擇“輸出區(qū)域”為$C$1。（3）選擇“柏拉圖”可以在輸出表中同時(shí)按降序排列頻數(shù)數(shù)據(jù)；選擇“累

12、積百分率”可在輸出表中增加一列累積百分比數(shù)值，并繪制一條百分比曲線；選擇“圖表輸出”可生成一個(gè)嵌入式直方圖。,第三步：?jiǎn)螕簟按_定”按鈕，在輸出區(qū)域單元格可得到頻數(shù)分布，（如圖10-13所示）,第四步：將條形圖轉(zhuǎn)換成標(biāo)準(zhǔn)直方圖， （如圖10-14所示）,三、用Excel作統(tǒng)計(jì)圖,傳統(tǒng)的統(tǒng)計(jì)表格需要數(shù)據(jù)使用者自己精心地進(jìn)行分析，而統(tǒng)

13、計(jì)圖顯示資料則具有形象生動(dòng)、一目了然的優(yōu)點(diǎn)，通過圖形可以方便地觀察到數(shù)量之間的對(duì)比關(guān)系、總體的結(jié)構(gòu)特征以及變化發(fā)展趨勢(shì)。統(tǒng)計(jì)圖在統(tǒng)計(jì)整理中的應(yīng)用越來越廣泛。Excel提供了大量的統(tǒng)計(jì)圖形供用戶根據(jù)需要和圖形功能選擇使用。Excel提供的圖形工具有：柱形圖、折線圖、餅圖、散點(diǎn)圖、面積圖、環(huán)形圖、股價(jià)圖等。各種圖的作法大同小異，本章以柱形圖為，介紹制作統(tǒng)計(jì)圖的工作步驟。 例11-3 根據(jù)我國(guó)1991-2003年國(guó)內(nèi)生產(chǎn)總值構(gòu)成數(shù)據(jù)

14、制作相應(yīng)的統(tǒng)計(jì)圖。,第一步：創(chuàng)建工作表，即將統(tǒng)計(jì)資料輸入到Excel中(如圖10-15所示),第二步：在想繪制圖形的數(shù)據(jù)中選擇任一單元格，然后在“插入”菜單中選擇“圖表”選項(xiàng)，或單擊工具欄中的圖表向?qū)О粹o，出現(xiàn)圖10-16。,第三步：選定圖表類型。在彈出的“圖表向?qū)?4步驟之1-圖表類型”對(duì)話框中選擇所需要的圖表的類型，此處在“圖表類型”中選“柱形圖”，在“子圖表類型”中選第一個(gè)“簇狀柱形圖”，然后單擊“下一步”按鈕，如圖10-17所

15、示。,第四步：確定數(shù)據(jù)范圍。在彈出的“圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)”對(duì)話框中規(guī)定數(shù)據(jù)區(qū)域。點(diǎn)擊“系列”選項(xiàng)，規(guī)定每一數(shù)據(jù)系列的名字和數(shù)值的區(qū)域，如圖10-18所示。然后單擊“下一步”按鈕。,第五步：選用圖表選項(xiàng)。在彈出的“圖表向?qū)?4步驟之3-圖表選項(xiàng)”對(duì)話框中可給圖表命名，將“分類（X）軸”命名為“年份”；將“數(shù)值（Y）軸”命名為“百分比（%）”。然后，單擊“下一步”按鈕，如圖10-19年示。,第六步：選擇圖表位置并顯示結(jié)果。在彈

16、出的“圖表向?qū)?4步驟之4圖表位置”對(duì)話框中可為圖表選擇保存位置，或放在獨(dú)立的工作表中，或作為一個(gè)對(duì)象放在當(dāng)前工作表中。然后，單擊“完成”按鈕。本例中選擇了放在獨(dú)立的工作表中，如圖10-20所示。,第二節(jié) 用Excel計(jì)算描述統(tǒng)計(jì)量,一、用函數(shù)計(jì)算描述統(tǒng)計(jì)量二、描述統(tǒng)計(jì)菜單項(xiàng)的使用,一、用函數(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ù)等。一般來說，在E

17、xcel中求這些統(tǒng)計(jì)量，未分組資料可用函數(shù)計(jì)算，已分組資料可用公式計(jì)算。這里我們僅介紹如何用函數(shù)來計(jì)算未分組資料的描述統(tǒng)計(jì)量。用函數(shù)運(yùn)算有兩種方法：一是手工輸入函數(shù)名稱及參數(shù)。這種輸入形式比較簡(jiǎn)單、快捷。但需要非常熟悉函數(shù)名稱及其參數(shù)的輸入形式。所以，只有比較簡(jiǎn)單的函數(shù)才用這種方法輸入；二是函數(shù)導(dǎo)入法。這是一種最為常用的辦法，它適合于所有函數(shù)的使用，而且在導(dǎo)入過程中有向?qū)崾?，因而非常方便。函?shù)的一般導(dǎo)入過程為：點(diǎn)菜單“插入”；找“函

18、數(shù)”，此時(shí)出現(xiàn)一個(gè)“插入函數(shù)”對(duì)話框；在對(duì)話框的“選擇類別”中確定函數(shù)的類別（如常用函數(shù)或統(tǒng)計(jì)）；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱，如SUM、MODE等；點(diǎn)“確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對(duì)話框向?qū)?，再按向?qū)У奶崾就逻\(yùn)行即可。下面介紹統(tǒng)計(jì)中常用指標(biāo)的函數(shù)運(yùn)算方法。,1．眾數(shù),例10-4 為了解某單位職工的工資收入情況，隨機(jī)抽取30人月工資如下：,（1）手工輸入函數(shù)名稱及參數(shù),單擊任一單元格，輸入“=MODE（A1：A30）”，

19、回車后即可得眾數(shù)為1560。如圖10-22所示。,（2）函數(shù)導(dǎo)入法,點(diǎn)菜單“插入”；找“函數(shù)”，此時(shí)出現(xiàn)一個(gè)“插入函數(shù)”對(duì)話框；在對(duì)話框的“選擇類別”中確定函數(shù)的類別“統(tǒng)計(jì)”；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱“MODE”，如圖10-23所示。,點(diǎn)“確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對(duì)話框向?qū)?，在Number1處輸入A1：A30或選擇Excel中的A1：A30區(qū)域，如圖10-23所示。按“確定”，在Excel中即得到眾數(shù)1560。,2．

20、中位數(shù),單擊任一空白單元格，輸入“=MEDIAN（A1：A30）”，回車后得中位數(shù)為1550。,3．算術(shù)平均數(shù),單擊任一空白單元格，輸入“=AVERAGE（A1：A30）”，回車后得算術(shù)平均數(shù)為1531.666667。,4．幾何平均數(shù),單擊任一空白單元格，輸入“=GEOMEAN（A1：A30）”，回車后得幾何平均數(shù)為1526.3。,5．調(diào)和平均數(shù),單擊任一空白單元格，輸入“=HARMEAN（A1：A30）”，回車后得調(diào)和

21、平均數(shù)為1521.06。,6．截尾平均數(shù),將數(shù)據(jù)按由小到大順序排列后，因數(shù)據(jù)兩端值不夠穩(wěn)定，按一定比例去掉頭尾兩端一定數(shù)量的觀察值，然后再求平均，這樣得到的平均數(shù)就是截尾平均數(shù)。如果按6/30，即從30個(gè)數(shù)據(jù)中去掉最大的三個(gè)值和最小的三個(gè)值，再求平均數(shù)。單擊任一空白單元格，輸入“=TRIMEAN（A1：A30，6/30）”，回車后得截尾平均數(shù)為1526.25,7．全距,單擊任一空白單元格，輸入“=MAX（A1：A30）-MIN（A1

22、：A30）”，回車后得全距為730。,8．標(biāo)準(zhǔn)差,單擊任一空白單元格，輸入“=STDEV（A1：A30）”，回車后得標(biāo)準(zhǔn)差為132.5371。,9．標(biāo)準(zhǔn)差系數(shù),單擊任一空白單元格，輸入“=STDEV（A1：A30）/AVERAGE（A1：A30）”，回車后得標(biāo)準(zhǔn)差系數(shù)為0．086531,10．偏度系數(shù),單擊任一空白單元格，輸入“=SKEW（A1：A30）”，回車后得偏度系數(shù)為0.914565。,11．峰度系數(shù),單擊任一空白

23、單元格，輸入“=KURT（A1：A30）”，回車后得峰度系數(shù)為3.808279。,二、描述統(tǒng)計(jì)菜單項(xiàng)的使用,仍使用上面的例子我們已經(jīng)把數(shù)據(jù)輸入到A1：A30單元格，然后按以下步驟操作：,第一步：在工具菜單中選擇數(shù)據(jù)分析選項(xiàng)，從其對(duì)話框中選擇描述統(tǒng)計(jì)，按確定后打開描述統(tǒng)計(jì)對(duì)話框，如圖10-24所示。,第二步：在輸入?yún)^(qū)域中輸入$A$1:$A$30，在輸出區(qū)域中選擇$C$13，其他復(fù)選框可根據(jù)需要選定，選擇匯總統(tǒng)計(jì)，可給出一系列描述統(tǒng)

24、計(jì)量；選擇平均數(shù)置信度，會(huì)給出用樣本平均數(shù)估計(jì)總體平均數(shù)的置信區(qū)間；第K大值和第K小值會(huì)給出樣本中第K個(gè)大值和第K個(gè)小值。,第三步：?jiǎn)螕舸_定，可得輸出結(jié)果，如圖10-25所示。,上面的結(jié)果中，平均指樣本均值；標(biāo)準(zhǔn)誤差指樣本平均數(shù)的標(biāo)準(zhǔn)差；中值即中位數(shù)；模式指眾數(shù)；標(biāo)準(zhǔn)偏差指樣本標(biāo)準(zhǔn)差，自由度為n-1；峰值即峰度系數(shù)；偏斜度即偏度系數(shù)；區(qū)域?qū)嶋H上是極差，或全距；可以看出與我們前面用函數(shù)計(jì)算的結(jié)果完全相同。最大值為1980，最小值為1250

25、，第三個(gè)最大值為1690，第三個(gè)最小值為1350。,第三節(jié) 用Excel進(jìn)行時(shí)間序列分析,一、測(cè)定增長(zhǎng)量和平均增長(zhǎng)量二、測(cè)定發(fā)展速度和平均發(fā)展速度三、計(jì)算長(zhǎng)期趨勢(shì)四、計(jì)算季節(jié)變動(dòng),一、測(cè)定增長(zhǎng)量和平均增長(zhǎng)量,例11-5 根據(jù)我國(guó)1998年~2003年社會(huì)消費(fèi)品零售總額，計(jì)算逐期增長(zhǎng)量、累計(jì)增長(zhǎng)量和平均增長(zhǎng)量。如圖10-26所示。,,計(jì)算步驟如下：,第一步：在A列輸入年份，在B列輸入社會(huì)消費(fèi)品零售總額。第二步：計(jì)算逐期增長(zhǎng)量，

26、在C3中輸入公式：“=B3-B2”，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C7區(qū)域。第三步：計(jì)算累計(jì)增長(zhǎng)量，在D3中輸入公式：“=B3-$B$2”，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D7區(qū)域。第四步：計(jì)算平均增長(zhǎng)量（水平法），在C10中輸入公式：“=（B7-B2）/5”，按回車鍵，即可得以平均增長(zhǎng)量。,二、測(cè)定發(fā)展速度和平均發(fā)展速度,例11-6 以1998年~2003年社會(huì)消費(fèi)品零售總額為例，說明如何計(jì)算定基發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速

27、度。如圖10-27所示。,第一步：在A列輸入年份，在B列輸入社會(huì)消費(fèi)品零售總額。第二步：計(jì)算定基發(fā)展速度，在C3中輸入公式：“=B3/$B$2”，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C7區(qū)域。第三步：計(jì)算環(huán)比發(fā)展速度，在D3中輸入公式：“=B3/B2”，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D7區(qū)域。第四步：計(jì)算平均發(fā)展速度（水平法），選中C9單元格，單擊插入菜單，選中函數(shù)選項(xiàng)，出現(xiàn)插入函數(shù)對(duì)話框后，選擇GEOMEAN（返回幾何平均值）函數(shù)，在

28、數(shù)值區(qū)域中輸入D3：D8即可。,三、計(jì)算長(zhǎng)期趨勢(shì),影響時(shí)間數(shù)列各項(xiàng)數(shù)值變動(dòng)的因素是多方面的，主要有4種：長(zhǎng)期趨勢(shì)（T）、季節(jié)變動(dòng)（S）、循環(huán)變動(dòng)（C）、不規(guī)則變動(dòng)（I）。本書主要就長(zhǎng)期趨勢(shì)與季節(jié)變動(dòng)進(jìn)行分析。以直線趨勢(shì)說明長(zhǎng)期趨勢(shì)的測(cè)定與預(yù)測(cè)方法。而測(cè)定直線趨勢(shì)的方法主要采用移動(dòng)平均法。移動(dòng)平均法按一定的間隔逐期移動(dòng)，計(jì)算一系列動(dòng)態(tài)平均數(shù)，從而形成一個(gè)由動(dòng)態(tài)平均數(shù)組成的新的時(shí)間數(shù)列，修勻原時(shí)間數(shù)列，顯示出長(zhǎng)期趨勢(shì)。在Excel中，使用

29、移動(dòng)平均法測(cè)定長(zhǎng)期趨勢(shì)，可以利用公式或AVERAGE函數(shù)，也可利用Excel提供的“移動(dòng)平均”工具。由于公式或函數(shù)方法前面已講過，而且只能獲得數(shù)據(jù)，不能直接獲得長(zhǎng)期趨勢(shì)圖，因此長(zhǎng)期趨勢(shì)主要以“移動(dòng)平均”工具來計(jì)算分析。,例10-7 根據(jù)我國(guó)1990~2003年的國(guó)內(nèi)生產(chǎn)總值的數(shù)據(jù)（單位：億元），如圖11-28所示，用移動(dòng)平均法計(jì)算預(yù)測(cè)我國(guó)國(guó)內(nèi)生產(chǎn)總值的長(zhǎng)期發(fā)展趨勢(shì)。,第一步：?jiǎn)螕簟肮ぞ摺辈藛危x擇“數(shù)據(jù)分析”選項(xiàng)。打開“數(shù)據(jù)分析”對(duì)話

30、框，從其“分析工具”列表中選擇“移動(dòng)平均”選項(xiàng)，單擊“確定”按鈕，打開“移動(dòng)平均”對(duì)話框。圖10-29所示。,第二步：確定輸入?yún)^(qū)域和輸出區(qū)域，選中“圖表輸出”復(fù)選框。如圖10-29所示。,第三步：?jiǎn)螕簟按_定”按鈕后，在指定位置給出移動(dòng)平均計(jì)算結(jié)果，如圖10-30所示。,四、計(jì)算季節(jié)變動(dòng),1、按月平均法2．移動(dòng)平均趨勢(shì)剔除法。,1、按月平均法,例11-8 某啤酒廠近五年全年及分月啤酒銷售量數(shù)據(jù)如圖11-31所示。結(jié)合五年分月數(shù)據(jù)，利

31、用Excel按月平均法測(cè)定季節(jié)變動(dòng)。,第一步：按已知數(shù)據(jù)資料列出計(jì)算表，將各年同月的數(shù)值列在同一列內(nèi)。 第二步：計(jì)算各年合計(jì)與各年同月數(shù)值之和。計(jì)算每年的啤酒銷量總數(shù)：?jiǎn)螕鬘3單元格，輸入“=SUM（B3：M3）”，并用鼠標(biāo)拖曳將公式復(fù)制到N4：N7區(qū)域，得各年銷量總數(shù)；計(jì)算各年同月銷售總數(shù)：?jiǎn)螕鬊8單元格，輸入“=SUM（B3：B7）”，并用鼠標(biāo)拖曳將公式復(fù)制到C8：N8區(qū)域，得各年同月銷量總數(shù)與全部銷量之和。 第三步

32、：計(jì)算同月平均數(shù)與總的月平均數(shù)。計(jì)算同月平均數(shù)：?jiǎn)螕鬊9單元格，輸入“=B8/5”，并用鼠標(biāo)拖曳將公式復(fù)制到C9：M9區(qū)域；計(jì)算總的月平均數(shù)：?jiǎn)螕鬘9單元格，輸入“=N8/60”回車得結(jié)果為43.21667。 第四步：計(jì)算季節(jié)比率。單擊B10單元格，輸入“=B9*100/43.21667”，并用鼠標(biāo)拖曳將公式復(fù)制到C10：M10區(qū)域。 第五步：計(jì)算季節(jié)比率之和，繪制季節(jié)變動(dòng)曲線。單擊N10單元格，輸入“=SUM（B10：

33、M10）”，回車得季節(jié)比率之和為1200。根據(jù)季節(jié)比率，可繪制季節(jié)變動(dòng)曲線。,2．移動(dòng)平均趨勢(shì)剔除法,直接用按月平均法忽略了長(zhǎng)期趨勢(shì)的影響，因此得出的季節(jié)比率不夠精確。為了彌補(bǔ)這一缺點(diǎn)，可以采用移動(dòng)平均趨勢(shì)剔除法來測(cè)定季節(jié)變動(dòng)。利用移動(dòng)平均趨勢(shì)剔除法分析季節(jié)變動(dòng)有兩種方法：乘法型時(shí)間數(shù)列季節(jié)變動(dòng)分析和加法型時(shí)間數(shù)列季節(jié)變動(dòng)分析。下面仍以例11-8的資料，采用乘法型時(shí)間數(shù)列變動(dòng)分析說明移動(dòng)趨勢(shì)剔除法的操作方法。,第一步：輸入各年季度數(shù)據(jù)資

34、料，如圖10-33所示,第二步：計(jì)算四個(gè)季度的移動(dòng)平均數(shù)。計(jì)算移動(dòng)平均數(shù)，可以采用移動(dòng)平均工具，也可以使用公式與函數(shù)。移動(dòng)平均工具在前面內(nèi)容中已講過，本例采用公式與函數(shù)方法來計(jì)算。單擊D4單元格，輸入“=AVERAGE（C2：C5）”，并用鼠標(biāo)拖曳將公式復(fù)制到D5：D20區(qū)域。第三步：移正平均。因?yàn)楸纠桥紨?shù)項(xiàng)移動(dòng)平均，所以還需將四項(xiàng)移動(dòng)平均值再進(jìn)行兩項(xiàng)“移正”平均，如果是奇數(shù)項(xiàng)移動(dòng)平均，則該步驟省去。單擊E4單元格，輸入“=AVE

35、RAGE（D4：D5）”，并用鼠標(biāo)拖曳將公式復(fù)制到E5：E19區(qū)域。,第四步：消除長(zhǎng)期趨勢(shì)。本例采用乘法模型，因此，將原數(shù)列除以趨勢(shì)值以消除長(zhǎng)期趨勢(shì)。單擊F4單元格，輸入“=C4*100/E4”，并用鼠標(biāo)拖曳將公式復(fù)制到F5：F19區(qū)域。,第五步：計(jì)算季節(jié)比率。將圖11-34中Y/T*100得到的數(shù)據(jù)重新編排，得到如圖11-35中左邊數(shù)據(jù)表中前五行的基本數(shù)據(jù)（本例將圖11-34中Y/T*100的數(shù)據(jù)四舍五入，保留兩位小數(shù)）。然后利用按月

36、平均法計(jì)算季節(jié)比率，具體步驟參見月平均法，本例省略，最終結(jié)果如圖11-35所示。,第四節(jié) 用Excel進(jìn)行指數(shù)分析,一、用Excel計(jì)算總指數(shù)二、用Excel計(jì)算平均指數(shù)三、用Excel進(jìn)行因素分析,一、用Excel計(jì)算總指數(shù),例11-9 圖中是某企業(yè)甲、乙、丙三種產(chǎn)品的生產(chǎn)情況，以基期價(jià)格P作為同度量因素，計(jì)算生產(chǎn)量指數(shù)。如圖11-36所示。,計(jì)算步驟：,第一步：計(jì)算各個(gè)p0q0：在G2中輸入“=C2*D2”，并用鼠標(biāo)拖曳將公式

37、復(fù)制到G2：G4區(qū)域。第二步：計(jì)算各個(gè)p0q1：在H2中輸入“=C2*F2”，并用鼠標(biāo)拖曳將公式復(fù)制到H2：H4區(qū)域。第三步：計(jì)算∑p0q0和∑p0q1：選定G2：G4區(qū)域，單擊工具欄上的“∑”按鈕，在G5出現(xiàn)該列的求和值。選定H2：H4區(qū)域，單擊工具欄上的“∑”按鈕，在H5出現(xiàn)該列的求和值。第四步：計(jì)算生產(chǎn)量綜合指數(shù)Iq=∑p0q1/∑p0q0：在C6中輸入“=H5/G5”便可得到生產(chǎn)量綜合指數(shù)。,二、用Excel計(jì)算平均指數(shù),

38、例10-10 圖中的A1：A4區(qū)域內(nèi)是某企業(yè)生產(chǎn)情況的統(tǒng)計(jì)資料，我們要以基期總成本為同度量因素，計(jì)算生產(chǎn)量平均指數(shù)。如圖10-37所示。,計(jì)算步驟：,第一步：計(jì)算個(gè)體指數(shù)k=q1/q0：在F2中輸入“=D2/C2”，并用鼠標(biāo)拖曳將公式復(fù)制到F2：F4區(qū)域。第二步：計(jì)算k* p0q0并求和。在G2中輸入“=F2*E2”，并用鼠標(biāo)拖曳將公式復(fù)制到G2：G4區(qū)域。選定G2：G4區(qū)域，單擊工具欄上的“∑”按鈕，在G5列出現(xiàn)該列的求和值。

39、第三步：計(jì)算生產(chǎn)量平均指數(shù)：在C6中輸入“=G5/E5”即得到所求的值。,三、用Excel進(jìn)行因素分析,例11-11 資料同例11-10，有關(guān)資料及運(yùn)算結(jié)果如圖11-38所示，進(jìn)行因素分析的步驟如下：第一步：計(jì)算各個(gè)p0q0和∑p0q0：在G2中輸入“=C2*D2”，并用鼠標(biāo)拖曳將公式復(fù)制到G2：G4區(qū)域。選定G2：G4區(qū)域，單擊工具欄上的“∑”按鈕，在G5出現(xiàn)該列的求和值。第二步：計(jì)算各個(gè)p0q1和∑p0q1：在H2中輸入“=C

40、2*F2”，并用鼠標(biāo)拖曳將公式復(fù)制到H2：H4區(qū)域。選定H2：H6區(qū)域，單擊工具欄上的“∑”按鈕，在H5出現(xiàn)該列的求和值。,第三步：計(jì)算各個(gè)p1q1和∑p1q1：在I2中輸入“=E2*F2”，并用鼠標(biāo)拖曳將公式復(fù)制到I2：I4區(qū)域。選定I2：I4區(qū)域，單擊工具欄上的“∑”按鈕，在I5出現(xiàn)該列的求和值。第四步：計(jì)算總成本指數(shù)：在C6中輸入“=I5/G5”，即求得總成本指數(shù)。第五步：計(jì)算產(chǎn)量指數(shù)：在C7中輸入“=H5/G5”，即得產(chǎn)量指

41、數(shù)。第六步：計(jì)算單位成本指數(shù)：在C8中輸入“=I5/H5”即求得單位成本指數(shù)。,第五節(jié) 用Excel進(jìn)行相關(guān)與回歸分析,一、用Excel進(jìn)行相關(guān)分析二、用Excel進(jìn)行回歸分析,一、用Excel進(jìn)行相關(guān)分析,1．利用函數(shù)計(jì)算相關(guān)系數(shù)2．用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù),1．利用函數(shù)計(jì)算相關(guān)系數(shù),在Excel中，提供了兩個(gè)計(jì)算兩個(gè)變量之間相關(guān)系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個(gè)函數(shù)是等價(jià)的，這里我們介紹用CORRE

42、L函數(shù)計(jì)算相關(guān)系數(shù)。第一步：?jiǎn)螕羧我粋€(gè)空白單元格，單擊“插入”菜單，選擇“函數(shù)”，打開“插入函數(shù)”對(duì)話框，在“選擇類別”中選擇“統(tǒng)計(jì)”；在“選擇函數(shù)”中選擇“CORREL”，單擊確定后，出現(xiàn)CORREL對(duì)話框。第二步：在array1中輸入B2：B11，在array2中輸入C2：C11，即可在對(duì)話框下方顯示出計(jì)算結(jié)果為0.896028。如圖10-40所示。,2．用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù),第一步：?jiǎn)螕簟肮ぞ摺辈藛危x擇“數(shù)據(jù)分析”項(xiàng)，

43、在“數(shù)據(jù)分析”項(xiàng)中選擇“相關(guān)系數(shù)”，彈出“相關(guān)系數(shù)”對(duì)話框，如圖10-41所示。,第二步：在“輸入?yún)^(qū)域”輸入$B$1:$C$11，“分組方式”選擇逐列，選擇“標(biāo)志位于第一行”，在“輸出區(qū)域”中輸入$E$1，單擊“確定”，得輸出結(jié)果如圖10-42所示。,二、用Excel進(jìn)行回歸分析,Excel進(jìn)行回歸分析同樣分函數(shù)和回歸分析宏兩種形式，其提供了9個(gè)函數(shù)用于建立回歸模型和預(yù)測(cè)，這9個(gè)函數(shù)分別是：1．INTERCEPT返回線性回歸模型的截

44、距2．SLOPE返回線性回歸模型的斜率3．RSQ返回線性回歸模型的判定系數(shù)4．FORECAST返回一元線性回歸模型的預(yù)測(cè)值5．STEYX計(jì)算估計(jì)的標(biāo)準(zhǔn)誤差6．TREND計(jì)算線性回歸線的趨勢(shì)值7．GROWTH返回指數(shù)曲線的趨勢(shì)值8．LINEST返回線性回歸模型的參數(shù)9．LOGEST返回指數(shù)曲線模型的參數(shù)用函數(shù)進(jìn)行回歸分析比較麻煩，這里介紹使用回歸分析宏進(jìn)行回歸分析。,第一步：?jiǎn)螕簟肮ぞ摺辈藛危x擇“數(shù)據(jù)分析”選項(xiàng)，出現(xiàn)“

45、數(shù)據(jù)分析”對(duì)話框，在分析工具中選擇“回歸”，如圖10-43所示。,第二步：?jiǎn)螕簟按_定”按鈕，彈出“回歸”對(duì)話框，在“Y值輸入?yún)^(qū)域”輸入$B$1：$B$11；在“X值輸入?yún)^(qū)域”輸入$C$1:$C$11，在“輸出選項(xiàng)”選擇“新工作表組”，如圖10-44所示。,第三步：?jiǎn)螕舸_定按鈕，得回歸分析結(jié)果如圖11-45所示。,在輸出結(jié)果中，第一部分為匯總統(tǒng)計(jì)，MultipleR指復(fù)相關(guān)系數(shù)，RSquare指判定系數(shù)，Adjusted指調(diào)整的判定系數(shù)，

46、標(biāo)準(zhǔn)誤差指估計(jì)的標(biāo)準(zhǔn)誤差，觀測(cè)值指樣本容量；第二部分為方差分析，df指自由度，SS指平方和，MS指均方，F(xiàn)指F統(tǒng)計(jì)量，Singnificance of F指p值；第三部分包括：Intercept指截距，Coefficient指系數(shù)，t stat指t統(tǒng)計(jì)量。,統(tǒng)計(jì)術(shù)語,統(tǒng)計(jì)               

47、; statistics 統(tǒng)計(jì)工作           statistical operation統(tǒng)計(jì)資料        statistical data 統(tǒng)計(jì)學(xué) 

48、60;             statistics理論統(tǒng)計(jì)學(xué)    theoretical statistics 應(yīng)用統(tǒng)計(jì)學(xué)       applied statistics描述統(tǒng)計(jì)學(xué)  &#

49、160; descriptive statistics 推斷統(tǒng)計(jì)學(xué)       inferential statistics總體                population

50、 樣本                   sample變量                va

51、riable 連續(xù)性變量       continuous離散型變量    discrete variable 標(biāo)志            

52、0;      mark參數(shù)                parametric 定性數(shù)據(jù)         

53、  qualitative data定量數(shù)據(jù)        quantitative data 定類尺度          nominal定序尺度        o

54、rdinal level 定距尺度           interval level定比尺度        ratio level,案例分析,2005年“五一”黃金周旅游統(tǒng)計(jì)報(bào)告 根據(jù)國(guó)家旅游局、國(guó)家統(tǒng)計(jì)局

55、聯(lián)合制定的《“黃金周”旅游信息統(tǒng)計(jì)調(diào)查制度》的要求，全國(guó)31個(gè)省、自治區(qū)、直轄市分別對(duì)轄區(qū)內(nèi)“五一”黃金周的接待規(guī)模和效益進(jìn)行了統(tǒng)計(jì)調(diào)查。經(jīng)國(guó)家旅游局、國(guó)家統(tǒng)計(jì)局匯總，結(jié)果如下。,課后練習(xí),實(shí)訓(xùn)一某企業(yè)50名工人日產(chǎn)量數(shù)據(jù)如圖11-46所示。運(yùn)用Excel進(jìn)行數(shù)據(jù)分組并制作直方圖。,實(shí)訓(xùn)二,我國(guó)網(wǎng)民2004年文化程度分布如圖11-47所示。運(yùn)用Excel制作餅圖。,實(shí)訓(xùn)三,某省2000~2004年的職工平均生活費(fèi)收入和商品銷