統(tǒng)計頻數(shù)-excel在統(tǒng)計中的應用

1、第11章 Excel在統(tǒng)計中的應用 43第十一章 Excel在統(tǒng)計中的應用學習目標 了解Excel是統(tǒng)計工作的基本工具。 理解用Excel處理統(tǒng)計數(shù)據(jù)及統(tǒng)計分析的基本程序與基本原理。 掌握用Excel進行時間序列分析、指數(shù)分析和相關與回歸分析。 熟練掌握及運用Excel搜集與整理數(shù)據(jù)、計算描述統(tǒng)計量。統(tǒng)計學是一門應用性非常強的學科。而統(tǒng)計工作的每一個環(huán)節(jié)幾乎都離不開統(tǒng)計計算機軟件的應用。典型的統(tǒng)計軟件有SAS、SPSS、MINITAB、STATISTICA、Excel等。其中由美國微軟公司開發(fā)的Excel電子表格軟件，是辦公自動化中非常重要的一款軟件，它不僅能夠進行表格處理、圖形分析、數(shù)據(jù)的自

2、動處理和計算，而且簡單易用，具有一定統(tǒng)計基礎知識的人都可以利用它進行統(tǒng)計工作。本章介紹Excel2003在統(tǒng)計中的應用及正確使用的方法。第一節(jié) 用Excel搜集與整理數(shù)據(jù) 一、用Excel搜集數(shù)據(jù)搜集數(shù)據(jù)的方法有多種，可以采用統(tǒng)計報表、典型調(diào)查、重點調(diào)查或抽樣調(diào)查，以后我國的統(tǒng)計調(diào)查將以抽樣為主。針對抽樣調(diào)查，Excel的數(shù)據(jù)分析工具中提供了一個專門的“抽樣”工具，可以幫助使用者快速完成抽樣工作。使用Excel進行抽樣，首先要對各個總體單位進行編號，編號可以按隨機原則，也可以按有關標志或無關標志，本節(jié)以例11-1為操作范例進行闡述。例11-1 假定有100名消費者，從中選取15人進行工資收入調(diào)

3、查。首先，將100名消費者編號為1100號，輸入編號，形成總體單位編號表如圖11-1所示。圖11-1 總體各單位編號表完成總體單位編號清單后，利用“抽樣”工具進行抽樣的具體操作如下： 第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項（若無數(shù)據(jù)分析選項，可在工具菜單下選擇加載宏，在彈出的對話框中選擇分析工具庫，便可出現(xiàn)數(shù)據(jù)分析選項）。 第二步：打開“數(shù)據(jù)分析”對話框，從其對話框的“分析工具”列表中選擇“抽樣”選項，如圖11-2所示。圖11-2 “數(shù)據(jù)分析”對話框 第三步：單擊“確定”按鈕，打開“抽樣”對話框，確定輸入?yún)^(qū)域、抽樣方法和輸出區(qū)域，如圖11-3所示。圖11-3 “抽樣”對話框1確定輸入?yún)^(qū)

4、域在“抽樣”對話框的“輸入?yún)^(qū)域”框中輸入總體單位編號所在的單元格區(qū)域。在本例中，輸入?yún)^(qū)域為$A$1:$J$10。輸入?yún)^(qū)域有兩種方法：一是用手工逐字錄入；二是用鼠標左鍵單擊圖11-1中的$A$1位置，出現(xiàn)虛線框，然后拖拉虛線框，選中表中全部數(shù)字，自動在圖11-3中的“輸入?yún)^(qū)域”顯示出$A$1:$J$10。系統(tǒng)將從A列開始抽取樣本，然后按順序抽取B列至J列。如果輸入?yún)^(qū)域的第一行或第一列為標志項（橫行標題或縱列標題），可單擊“標志”選框。2選定抽樣方法在“抽樣方法”框中，有“周期”和“隨機”兩種抽樣模式。（1）“周期”模式即所謂的等距抽樣、機械抽樣或系統(tǒng)抽樣。此種抽樣方法，需要確定周期間隔，周期間隔

5、由總體單位數(shù)除以要抽取的樣本數(shù)而求得。本例中，要在100個總體單位中抽取15個，則在“間隔”框中輸入6。（2）“隨機”模式適用于簡單隨機抽樣、分層抽樣、整群抽樣和階段抽樣。簡單隨機抽樣即純隨機抽樣，只需在“樣本數(shù)”框中輸入要抽取的樣本單位數(shù)即可，本例為15。分類抽樣即類型抽樣，先將總體單位按某一標志分類編號，然后在每一類中隨機抽取若干單位，這種抽樣方法實際是分組法與隨機抽樣的結(jié)合。整群抽樣，先將總體單位分類編號，然后按隨機原則抽取若干類作為樣本，對抽中的類的所有單位全部進行調(diào)查。可以看出，本例所使用的編號輸入方法，只適用于等距抽樣和簡單隨機抽樣。3指定輸出方向在“輸出選項”框中有三種輸出去向。

6、在“輸出區(qū)域”框中輸入總體單位編號所在的單元格區(qū)域。在本例中，輸入?yún)^(qū)域為$A$12。也可以通過選擇“新工作表”或“新工作簿”將抽樣結(jié)果放在新工作表或新工作薄中。第四步：單擊“確定”按鈕后，在指定的位置給出抽樣的結(jié)果。如圖11-4所示。圖11-4 等距抽樣結(jié)果 二、用Excel整理數(shù)據(jù)通過統(tǒng)計調(diào)查得到的數(shù)據(jù)是雜亂的，沒有規(guī)則的，因此，必須對搜集到的大量的原始數(shù)據(jù)加工整理，經(jīng)過數(shù)據(jù)分析得到科學結(jié)論。統(tǒng)計整理包括對數(shù)據(jù)進行分類匯總并計算各類指標及利用統(tǒng)計圖或統(tǒng)計表描述統(tǒng)計匯總結(jié)果等。Excel提供了多種數(shù)據(jù)整理工具：頻數(shù)分布函數(shù)與直方圖分析工具等。例11-2 根據(jù)抽樣調(diào)查，某月某市50戶居民購買消費

7、品支出資料如圖11-5所示。（單位：元）圖11-5 居民購買消費品月支出對其按800900、9001000、10001100、11001200、12001300、13001400、14001500、15001600、1600以上分為9個組。1頻數(shù)分布函數(shù)Excel提供了一個專門用于統(tǒng)計分組的頻數(shù)分布函數(shù)（FREQUENCY），它以一列垂直數(shù)組返回某個區(qū)域中的數(shù)據(jù)分布，描述數(shù)據(jù)分布狀態(tài)。用頻數(shù)分布函數(shù)進行統(tǒng)計分組的操作過程如下。首先，在使用此函數(shù)時，先將樣本數(shù)據(jù)排成一列，本例中為A1：A50。然后，利用頻數(shù)分布函數(shù)進行統(tǒng)計分組和計算頻數(shù)，具體操作步驟如下：第一步：選定單元格區(qū)域，本例中選定的區(qū)域

8、為D3：D11，單擊“插入”菜單，選擇“函數(shù)”選項，彈出“插入函數(shù)”對話框，如圖11-6所示。圖11-6 插入函數(shù)對話框在“選擇類別”中選擇“統(tǒng)計”，在“選擇函數(shù)”中選擇“FREQUENCY”。如圖11-7所示：圖11-7 選擇“FREQUENCY”對話框 第二步：打開“FREQUENCY”對話框，輸入待分組數(shù)據(jù)與分組標志，如圖11-8所示。圖11-8 “FREQUENCY”對話框 在“FREQUENCY”對話框中有“Data_array”和“Bins_array”兩個文本框。（1）Data_array用于計算頻率的數(shù)組，或?qū)?shù)組單元區(qū)域的引用。本例中為A1：A50。 （2）Bins_arra

9、y數(shù)據(jù)接受區(qū)間，為一組數(shù)或?qū)?shù)組區(qū)間的引用，設定對Data_array進行頻率計算的分段點。本例中為899、999、1099、1199、1299、1399、1499、1599、1699。 注意：頻數(shù)分布函數(shù)要求按組距的上限分組，不接受非數(shù)值的分組標志（如“不足××”或“××以上”等）。在輸入的數(shù)據(jù)兩端必須加大括號，各數(shù)據(jù)之間用分號隔開。輸入完成后，由于頻數(shù)分布是數(shù)組操作，所以不能單擊“確定”按鈕。 第三步：按“Ctrl+Shift+Enter” 組合鍵，在最初選定單元格區(qū)域內(nèi)得到頻數(shù)分布結(jié)果，在本例中為D3：D11，如圖11-9所示。圖11-9 頻數(shù)分布

10、結(jié)果 至此，頻數(shù)分布函數(shù)進行統(tǒng)計分析的功能就全部操作完成了。2直方圖分析工具與頻數(shù)分布函數(shù)只能進行統(tǒng)計分組和頻數(shù)計算相比，直方圖分析工具可完成數(shù)據(jù)的分組、頻數(shù)分布與累積頻數(shù)的計、繪制直方圖與累積折線圖等一系列操作。仍以例11-2為操作范例，闡述直方圖分析工具的統(tǒng)計整理功能，其操作過程如下。首先，將樣本數(shù)據(jù)排成一列，最好對數(shù)據(jù)進行排序，本例中已利用排序操作排好序，為A1：A51。輸入分組標志，本例中為B1：B10，分別是899、999、1099、1199、1299、1399、1499、1599、1699。如圖11-10所示：圖11-10 數(shù)據(jù)升序排列然后 ，利用直方圖分析工具進行分析，具體操作步

11、驟如下。 第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項；打開“數(shù)據(jù)分析”對話框，從“分析工具”列表中選擇“直方圖”選項，如圖11-11所示。圖11-11 “數(shù)據(jù)分析”對話框第二步：打開“直方圖”對話框，確定輸入?yún)^(qū)域、接收區(qū)域和輸出區(qū)域，如圖11-12所示。 圖11-12 “直方圖”對話框（1）“輸入?yún)^(qū)域”輸入待分析數(shù)據(jù)區(qū)域的單元格引用，若輸入?yún)^(qū)域有標志項，則選中“標志”復選框；否則，系統(tǒng)自動生成數(shù)據(jù)標志。“接收區(qū)域”輸入接收區(qū)域的單元格引用，該框可為空，則系統(tǒng)自動利用輸入?yún)^(qū)域中的最小值和最大值建立平均分布的區(qū)間間隔的分組。本例中輸入?yún)^(qū)域為$A$2:$A$51，接收區(qū)域為$B$2:$B$10。

12、（2）在“輸出”選項中可選擇輸出去向，輸出去向類似于“抽樣”對話框的輸出去向。本例中選擇“輸出區(qū)域”為$C$1。（3）選擇“柏拉圖”可以在輸出表中同時按降序排列頻數(shù)數(shù)據(jù)；選擇“累積百分率”可在輸出表中增加一列累積百分比數(shù)值，并繪制一條百分比曲線；選擇“圖表輸出”可生成一個嵌入式直方圖。第三步：單擊“確定”按鈕，在輸出區(qū)域單元格可得到頻數(shù)分布，如圖11-13所示。圖11-13 直方圖頻數(shù)分布結(jié)果 第四步：將條形圖轉(zhuǎn)換成標準直方圖，如圖11-14所示。具體做法：左鍵單擊條形圖的任一直條，再單擊右鍵，在快捷菜單中選取“數(shù)據(jù)系列格式”，然后在“數(shù)據(jù)系列格式”對話框中選擇選項標簽，將間距寬度改為0，單擊

13、“確定”按鈕即可。圖11-14 標準直方圖三、用Excel作統(tǒng)計圖 傳統(tǒng)的統(tǒng)計表格需要數(shù)據(jù)使用者自己精心地進行分析，而統(tǒng)計圖顯示資料則具有形象生動、一目了然的優(yōu)點，通過圖形可以方便地觀察到數(shù)量之間的對比關系、總體的結(jié)構特征以及變化發(fā)展趨勢。統(tǒng)計圖在統(tǒng)計整理中的應用越來越廣泛。Excel提供了大量的統(tǒng)計圖形供用戶根據(jù)需要和圖形功能選擇使用。Excel提供的圖形工具有：柱形圖、折線圖、餅圖、散點圖、面積圖、環(huán)形圖、股價圖等。各種圖的作法大同小異，本章以柱形圖為，介紹制作統(tǒng)計圖的工作步驟。例11-3 根據(jù)我國1991-2003年國內(nèi)生產(chǎn)總值構成數(shù)據(jù)制作相應的統(tǒng)計圖。 第一步：創(chuàng)建工作表，即將統(tǒng)計資料

14、輸入到Excel中，如圖11-15所示。圖11-15 1991-2003年國內(nèi)生產(chǎn)總值構成數(shù)據(jù) 第二步：在想繪制圖形的數(shù)據(jù)中選擇任一單元格，然后在“插入”菜單中選擇“圖表”選項，或單擊工具欄中的圖表向?qū)О粹o，出現(xiàn)圖11-16。圖11-16 “圖表向?qū)?4步驟之1-圖表類型”對話框第三步：選定圖表類型。在彈出的“圖表向?qū)?4步驟之1-圖表類型”對話框中選擇所需要的圖表的類型，此處在“圖表類型”中選“柱形圖”，在“子圖表類型”中選第一個“簇狀柱形圖”，然后單擊“下一步”按鈕，如圖11-17所示。圖11-17 “圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)”對話框第四步：確定數(shù)據(jù)范圍。在彈出的“圖表向?qū)?4步驟

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

16、象放在當前工作表中。然后，單擊“完成”按鈕。本例中選擇了放在獨立的工作表中，如圖11-20所示。圖11-20 1991-2003年國內(nèi)生產(chǎn)總值構成圖表其他圖形的繪制步驟與柱形圖的繪制步驟類似，只是在圖形類型中選擇不同的圖形類型即可。第二節(jié) 用Excel計算描述統(tǒng)計量Excel中用于計算描述統(tǒng)計量的方法有兩種，函數(shù)方法和描述統(tǒng)計工具的方法。一、用函數(shù)計算描述統(tǒng)計量常用的描述統(tǒng)計量有眾數(shù)、中位數(shù)、算術平均數(shù)、調(diào)和平均數(shù)、幾何平均數(shù)、極差、四分位差、標準差、方差、標準差系數(shù)等。一般來說，在Excel中求這些統(tǒng)計量，未分組資料可用函數(shù)計算，已分組資料可用公式計算。這里我們僅介紹如何用函數(shù)來計算未分組資

17、料的描述統(tǒng)計量。用函數(shù)運算有兩種方法：一是手工輸入函數(shù)名稱及參數(shù)。這種輸入形式比較簡單、快捷。但需要非常熟悉函數(shù)名稱及其參數(shù)的輸入形式。所以，只有比較簡單的函數(shù)才用這種方法輸入；二是函數(shù)導入法。這是一種最為常用的辦法，它適合于所有函數(shù)的使用，而且在導入過程中有向?qū)崾?，因而非常方便。函?shù)的一般導入過程為：點菜單“插入”；找“函數(shù)”，此時出現(xiàn)一個“插入函數(shù)”對話框；在對話框的“選擇類別”中確定函數(shù)的類別（如常用函數(shù)或統(tǒng)計）；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱，如SUM、MODE等；點“確定”后即可出現(xiàn)該函數(shù)運算的對話框向?qū)?，再按向?qū)У奶崾就逻\行即可。下面介紹統(tǒng)計中常用指標的函數(shù)運算方法。1眾數(shù)

18、 例11-4 為了解某單位職工的工資收入情況，隨機抽取30人月工資如下：圖11-21 職工月工資收入用函數(shù)方法求眾數(shù)，應先將30個人的工資數(shù)據(jù)輸入A1：A30單元格。（1）手工輸入函數(shù)名稱及參數(shù)單擊任一單元格，輸入“=MODE（A1：A30）”，回車后即可得眾數(shù)為1560。如圖11-22所示。圖11-22 手工輸入函數(shù)名稱及參數(shù)（2）函數(shù)導入法點菜單“插入”；找“函數(shù)”，此時出現(xiàn)一個“插入函數(shù)”對話框；在對話框的“選擇類別”中確定函數(shù)的類別“統(tǒng)計”；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱“MODE”，如圖11-23所示。圖11-22 函數(shù)導入法“插入函數(shù)”對話框點“確定”后即可出現(xiàn)該函數(shù)運算的對話

19、框向?qū)?，在Number1處輸入A1：A30或選擇Excel中的A1：A30區(qū)域，如圖11-23所示。按“確定”，在Excel中即得到眾數(shù)1560。圖11-23 函數(shù)導入法“函數(shù)參數(shù)”對話框運用函數(shù)導入法，只要知道每個函數(shù)的涵義，即可按上述程序得到相應的運算結(jié)果，為節(jié)約篇幅這里不再一一講解，下面僅列示各函數(shù)的涵義及本例中的運行結(jié)果。2中位數(shù)單擊任一空白單元格，輸入“=MEDIAN（A1：A30）”，回車后得中位數(shù)為1550。3算術平均數(shù)單擊任一空白單元格，輸入“=AVERAGE（A1：A30）”，回車后得算術平均數(shù)為1531.666667。4幾何平均數(shù)單擊任一空白單元格，輸入“=GEOMEAN（

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

21、標準差單擊任一空白單元格，輸入“=STDEV（A1：A30）”，回車后得標準差為132.5371。9標準差系數(shù)單擊任一空白單元格，輸入“=STDEV（A1：A30）/AVERAGE（A1：A30）”，回車后得標準差系數(shù)為008653110偏度系數(shù)單擊任一空白單元格，輸入“=SKEW（A1：A30）”，回車后得偏度系數(shù)為0.914565。11峰度系數(shù)單擊任一空白單元格，輸入“=KURT（A1：A30）”，回車后得峰度系數(shù)為3.808279。二、描述統(tǒng)計菜單項的使用仍使用上面的例子，我們已經(jīng)把數(shù)據(jù)輸入到A1：A30單元格，然后按以下步驟操作：第一步：在工具菜單中選擇數(shù)據(jù)分析選項，從其對話框中選擇描

22、述統(tǒng)計，按確定后打開描述統(tǒng)計對話框，如圖11-24所示。圖11-24 描述統(tǒng)計對話框第二步：在輸入?yún)^(qū)域中輸入$A$1:$A$30，在輸出區(qū)域中選擇$C$13，其他復選框可根據(jù)需要選定，選擇匯總統(tǒng)計，可給出一系列描述統(tǒng)計量；選擇平均數(shù)置信度，會給出用樣本平均數(shù)估計總體平均數(shù)的置信區(qū)間；第K大值和第K小值會給出樣本中第K個大值和第K個小值。第三步：單擊確定，可得輸出結(jié)果，如圖11-25所示。圖11-25 描述統(tǒng)計輸出結(jié)果上面的結(jié)果中，平均指樣本均值；標準誤差指樣本平均數(shù)的標準差；中值即中位數(shù)；模式指眾數(shù)；標準偏差指樣本標準差，自由度為n-1；峰值即峰度系數(shù)；偏斜度即偏度系數(shù)；區(qū)域?qū)嶋H上是極差，或全

23、距；可以看出與我們前面用函數(shù)計算的結(jié)果完全相同。最大值為1980，最小值為1250，第三個最大值為1690，第三個最小值為1350。第三節(jié) 用Excel進行時間序列分析一、測定增長量和平均增長量例11-5 根據(jù)我國1998年2003年社會消費品零售總額，計算逐期增長量、累計增長量和平均增長量。如圖11-26所示。圖11-26 用Excel計算增長量和平均增長量資料及結(jié)果計算步驟如下：第一步：在A列輸入年份，在B列輸入社會消費品零售總額。第二步：計算逐期增長量，在C3中輸入公式：“=B3-B2”，并用鼠標拖曳將公式復制到C3：C7區(qū)域。第三步：計算累計增長量，在D3中輸入公式：“=B3-$B$2

24、”，并用鼠標拖曳將公式復制到D3：D7區(qū)域。(英文輸入法狀態(tài)，按下“shift”和“4”組合鍵)第四步：計算平均增長量（水平法），在C10中輸入公式：“=（B7-B2）/5”，按回車鍵，即可得以平均增長量。二、測定發(fā)展速度和平均發(fā)展速度例11-6 以1998年2003年社會消費品零售總額為例，說明如何計算定基發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速度。如圖11-27所示。圖11-27 用Excel計算發(fā)展速度和平均發(fā)展速度資料及結(jié)果第一步：在A列輸入年份，在B列輸入社會消費品零售總額。第二步：計算定基發(fā)展速度，在C3中輸入公式：“=B3/$B$2”，并用鼠標拖曳將公式復制到C3：C7區(qū)域。第三步：計

25、算環(huán)比發(fā)展速度，在D3中輸入公式：“=B3/B2”，并用鼠標拖曳將公式復制到D3：D7區(qū)域。第四步：計算平均發(fā)展速度（水平法），選中C9單元格，單擊插入菜單，選中函數(shù)選項，出現(xiàn)插入函數(shù)對話框后，選擇GEOMEAN（返回幾何平均值）函數(shù)，在數(shù)值區(qū)域中輸入D3：D8即可。三、計算長期趨勢影響時間數(shù)列各項數(shù)值變動的因素是多方面的，主要有4種：長期趨勢（T）、季節(jié)變動（S）、循環(huán)變動（C）、不規(guī)則變動（I）。本書主要就長期趨勢與季節(jié)變動進行分析。以直線趨勢說明長期趨勢的測定與預測方法。而測定直線趨勢的方法主要采用移動平均法。移動平均法按一定的間隔逐期移動，計算一系列動態(tài)平均數(shù)，從而形成一個由動態(tài)平均數(shù)

26、組成的新的時間數(shù)列，修勻原時間數(shù)列，顯示出長期趨勢。在Excel中，使用移動平均法測定長期趨勢，可以利用公式或AVERAGE函數(shù)，也可利用Excel提供的“移動平均”工具。由于公式或函數(shù)方法前面已講過，而且只能獲得數(shù)據(jù)，不能直接獲得長期趨勢圖，因此長期趨勢主要以“移動平均”工具來計算分析。例11-7 根據(jù)我國19902003年的國內(nèi)生產(chǎn)總值的數(shù)據(jù)（單位：億元），如圖11-28所示，用移動平均法計算預測我國國內(nèi)生產(chǎn)總值的長期發(fā)展趨勢。圖11-28 19902003年國內(nèi)生產(chǎn)總值數(shù)據(jù)表第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項。打開“數(shù)據(jù)分析”對話框，從其“分析工具”列表中選擇“移動平均”選項

27、，單擊“確定”按鈕，打開“移動平均”對話框。圖11-29所示。圖11-29 “移動平均”對話框第二步：確定輸入?yún)^(qū)域和輸出區(qū)域，選中“圖表輸出”復選框。如圖11-29所示。第三步：單擊“確定”按鈕后，在指定位置給出移動平均計算結(jié)果，如圖11-30所示。圖11-30 移動平均法計算結(jié)果四、計算季節(jié)變動季節(jié)變動是指在一年以內(nèi)，受自然季節(jié)和社會習俗等因素影響而發(fā)生的有規(guī)律的、周期性的變動。測定季節(jié)變動的方法有兩種：按月（季）平均法和移動平均趨勢剔除法。1、按月平均法例11-8 某啤酒廠近五年全年及分月啤酒銷售量數(shù)據(jù)如圖11-31所示。結(jié)合五年分月數(shù)據(jù)，利用Excel按月平均法測定季節(jié)變動。圖11-31

28、 某酒廠近五年全年及分月啤酒銷售量第一步：按已知數(shù)據(jù)資料列出計算表，將各年同月的數(shù)值列在同一列內(nèi)。第二步：計算各年合計與各年同月數(shù)值之和。計算每年的啤酒銷量總數(shù)：單擊N3單元格，輸入“=SUM（B3：M3）”，并用鼠標拖曳將公式復制到N4：N7區(qū)域，得各年銷量總數(shù)；計算各年同月銷售總數(shù)：單擊B8單元格，輸入“=SUM（B3：B7）”，并用鼠標拖曳將公式復制到C8：N8區(qū)域，得各年同月銷量總數(shù)與全部銷量之和。 第三步：計算同月平均數(shù)與總的月平均數(shù)。計算同月平均數(shù)：單擊B9單元格，輸入“=B8/5”，并用鼠標拖曳將公式復制到C9：M9區(qū)域；計算總的月平均數(shù)：單擊N9單元格，輸入“=N8/60”回車

29、得結(jié)果為43.21667。 第四步：計算季節(jié)比率。單擊B10單元格，輸入“=B9*100/43.21667”，并用鼠標拖曳將公式復制到C10：M10區(qū)域。 第五步：計算季節(jié)比率之和，繪制季節(jié)變動曲線。單擊N10單元格，輸入“=SUM（B10：M10）”，回車得季節(jié)比率之和為1200。根據(jù)季節(jié)比率，可繪制季節(jié)變動曲線。圖11-32 按月平均法分析季節(jié)變動數(shù)據(jù)圖2移動平均趨勢剔除法。直接用按月平均法忽略了長期趨勢的影響，因此得出的季節(jié)比率不夠精確。為了彌補這一缺點，可以采用移動平均趨勢剔除法來測定季節(jié)變動。利用移動平均趨勢剔除法分析季節(jié)變動有兩種方法：乘法型時間數(shù)列季節(jié)變動分析和加法型時間數(shù)列季節(jié)

30、變動分析。下面仍以例11-8的資料，采用乘法型時間數(shù)列變動分析說明移動趨勢剔除法的操作方法。 第一步：輸入各年季度數(shù)據(jù)資料，如圖11-33所示。圖11-33 某酒廠近五年全年及分季啤酒銷售量第二步：計算四個季度的移動平均數(shù)。計算移動平均數(shù)，可以采用移動平均工具，也可以使用公式與函數(shù)。移動平均工具在前面內(nèi)容中已講過，本例采用公式與函數(shù)方法來計算。單擊D4單元格，輸入“=AVERAGE（C2：C5）”，并用鼠標拖曳將公式復制到D5：D20區(qū)域。第三步：移正平均。因為本例是偶數(shù)項移動平均，所以還需將四項移動平均值再進行兩項“移正”平均，如果是奇數(shù)項移動平均，則該步驟省去。單擊E4單元格，輸入“=AV

31、ERAGE（D4：D5）”，并用鼠標拖曳將公式復制到E5：E19區(qū)域。 第四步：消除長期趨勢。本例采用乘法模型，因此，將原數(shù)列除以趨勢值以消除長期趨勢。單擊F4單元格，輸入“=C4*100/E4”，并用鼠標拖曳將公式復制到F5：F19區(qū)域。圖11-34 移動平均及剔除趨勢數(shù)據(jù) 第五步：計算季節(jié)比率。將圖11-34中Y/T*100得到的數(shù)據(jù)重新編排，得到如圖11-35中左邊數(shù)據(jù)表中前五行的基本數(shù)據(jù)（本例將圖11-34中Y/T*100的數(shù)據(jù)四舍五入，保留兩位小數(shù)）。然后利用按月平均法計算季節(jié)比率，具體步驟參見月平均法，本例省略，最終結(jié)果如圖11-35所示。圖11-35 利用移動平均趨勢剔除法分析季

32、節(jié)變動數(shù)據(jù)圖第四節(jié) 用Excel進行指數(shù)分析指數(shù)分析法是研究社會經(jīng)濟現(xiàn)象數(shù)量變動情況的一種統(tǒng)計分析法。指數(shù)有總指數(shù)與平均指數(shù)之分，在這一節(jié)我們介紹如何用Excel進行指數(shù)分析與因素分析。一、用Excel計算總指數(shù)例11-9 圖中是某企業(yè)甲、乙、丙三種產(chǎn)品的生產(chǎn)情況，以基期價格P作為同度量因素，計算生產(chǎn)量指數(shù)。如圖11-36所示。圖11-36 用Excel計算總指數(shù)資料及結(jié)果計算步驟：第一步：計算各個p0q0：在G2中輸入“=C2*D2”，并用鼠標拖曳將公式復制到G2：G4區(qū)域。第二步：計算各個p0q1：在H2中輸入“=C2*F2”，并用鼠標拖曳將公式復制到H2：H4區(qū)域。第三步：計算p0q0和

33、p0q1：選定G2：G4區(qū)域，單擊工具欄上的“”按鈕，在G5出現(xiàn)該列的求和值。選定H2：H4區(qū)域，單擊工具欄上的“”按鈕，在H5出現(xiàn)該列的求和值。第四步：計算生產(chǎn)量綜合指數(shù)Iq=p0q1/p0q0：在C6中輸入“=H5/G5”便可得到生產(chǎn)量綜合指數(shù)。二、用Excel計算平均指數(shù) 現(xiàn)以生產(chǎn)量平均指數(shù)為例，說明加權算術平均法的計算方法。 例11-10 圖中的A1：A4區(qū)域內(nèi)是某企業(yè)生產(chǎn)情況的統(tǒng)計資料，我們要以基期總成本為同度量因素，計算生產(chǎn)量平均指數(shù)。如圖11-37所示。圖11-37 用Excel計算平均指數(shù)資料及結(jié)果計算步驟：第一步：計算個體指數(shù)k=q1/q0：在F2中輸入“=D2/C2”，并用

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

35、q1和p0q1：在H2中輸入“=C2*F2”，并用鼠標拖曳將公式復制到H2：H4區(qū)域。選定H2：H6區(qū)域，單擊工具欄上的“”按鈕，在H5出現(xiàn)該列的求和值。圖11-38 用Excel進行因素分析資料及結(jié)果第三步：計算各個p1q1和p1q1：在I2中輸入“=E2*F2”，并用鼠標拖曳將公式復制到I2：I4區(qū)域。選定I2：I4區(qū)域，單擊工具欄上的“”按鈕，在I5出現(xiàn)該列的求和值。第四步：計算總成本指數(shù)：在C6中輸入“=I5/G5”，即求得總成本指數(shù)。第五步：計算產(chǎn)量指數(shù)：在C7中輸入“=H5/G5”，即得產(chǎn)量指數(shù)。第六步：計算單位成本指數(shù)：在C8中輸入“=I5/H5”即求得單位成本指數(shù)。第五節(jié) 用E

36、xcel進行相關與回歸分析例11-12 10個學生身高和體重的情況如下：首先把有關數(shù)據(jù)輸入Excel的單元格中，如圖11-39所示：圖11-39 Excel數(shù)據(jù)集一、用Excel進行相關分析用Excel進行相關分析有兩種方法，一是利用相關系數(shù)函數(shù)，另一種是利用相關分析宏。1利用函數(shù)計算相關系數(shù)在Excel中，提供了兩個計算兩個變量之間相關系數(shù)的方法，CORREL函數(shù)和PERSON函數(shù)，這兩個函數(shù)是等價的，這里我們介紹用CORREL函數(shù)計算相關系數(shù)。第一步：單擊任一個空白單元格，單擊“插入”菜單，選擇“函數(shù)”，打開“插入函數(shù)”對話框，在“選擇類別”中選擇“統(tǒng)計”；在“選擇函數(shù)”中選擇“CORRE

37、L”，單擊確定后，出現(xiàn)CORREL對話框。第二步：在array1中輸入B2：B11，在array2中輸入C2：C11，即可在對話框下方顯示出計算結(jié)果為0.896028。如圖11-40所示。圖11-40 CORREL對話框及輸入結(jié)果2用相關系數(shù)宏計算相關系數(shù)第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析”項，在“數(shù)據(jù)分析”項中選擇“相關系數(shù)”，彈出“相關系數(shù)”對話框，如圖11-41所示。圖11-41 相關系數(shù)對話框 第二步：在“輸入?yún)^(qū)域”輸入$B$1:$C$11，“分組方式”選擇逐列，選擇“標志位于第一行”，在“輸出區(qū)域”中輸入$E$1，單擊“確定”，得輸出結(jié)果如圖11-42所示。圖11-42 相關分析輸出結(jié)果 在上面的輸出結(jié)果中，身高和體重的