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

1、第11章 Excel在統(tǒng)計(jì)中的應(yīng)用 43第十一章 Excel在統(tǒng)計(jì)中的應(yīng)用學(xué)習(xí)目標(biāo) 了解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ì)量。統(tǒng)計(jì)學(xué)是一門(mén)應(yīng)用性非常強(qiáng)的學(xué)科。而統(tǒng)計(jì)工作的每一個(gè)環(huán)節(jié)幾乎都離不開(kāi)統(tǒng)計(jì)計(jì)算機(jī)軟件的應(yīng)用。典型的統(tǒng)計(jì)軟件有SAS、SPSS、MINITAB、STATISTICA、Excel等。其中由美國(guó)微軟公司開(kāi)發(fā)的Excel電子表格軟件，是辦公自動(dòng)化中非常重要的一款軟件，它不僅能夠進(jìn)行表格處理、圖形分析、數(shù)據(jù)的自

2、動(dòng)處理和計(jì)算，而且簡(jiǎn)單易用，具有一定統(tǒng)計(jì)基礎(chǔ)知識(shí)的人都可以利用它進(jìn)行統(tǒng)計(jì)工作。本章介紹Excel2003在統(tǒng)計(jì)中的應(yīng)用及正確使用的方法。第一節(jié) 用Excel搜集與整理數(shù)據(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è)專(zhuān)門(mén)的“抽樣”工具，可以幫助使用者快速完成抽樣工作。使用Excel進(jìn)行抽樣，首先要對(duì)各個(gè)總體單位進(jìn)行編號(hào)，編號(hào)可以按隨機(jī)原則，也可以按有關(guān)標(biāo)志或無(wú)關(guān)標(biāo)志，本節(jié)以例11-1為操作范例進(jìn)行闡述。例11-1 假定有100名消費(fèi)者，從中選取15人進(jìn)行工資收入調(diào)

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

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

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

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

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

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

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

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

11、驟如下。 第一步：?jiǎn)螕簟肮ぞ摺辈藛危x擇“數(shù)據(jù)分析”選項(xiàng)；打開(kāi)“數(shù)據(jù)分析”對(duì)話(huà)框，從“分析工具”列表中選擇“直方圖”選項(xiàng)，如圖11-11所示。圖11-11 “數(shù)據(jù)分析”對(duì)話(huà)框第二步：打開(kāi)“直方圖”對(duì)話(huà)框，確定輸入?yún)^(qū)域、接收區(qū)域和輸出區(qū)域，如圖11-12所示。 圖11-12 “直方圖”對(duì)話(huà)框（1）“輸入?yún)^(qū)域”輸入待分析數(shù)據(jù)區(qū)域的單元格引用，若輸入?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。

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

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

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

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

16、象放在當(dāng)前工作表中。然后，單擊“完成”按鈕。本例中選擇了放在獨(dú)立的工作表中，如圖11-20所示。圖11-20 1991-2003年國(guó)內(nèi)生產(chǎn)總值構(gòu)成圖表其他圖形的繪制步驟與柱形圖的繪制步驟類(lèi)似，只是在圖形類(lèi)型中選擇不同的圖形類(lèi)型即可。第二節(jié) 用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ù)等。一般來(lái)說(shuō)，在Excel中求這些統(tǒng)計(jì)量，未分組資料可用函數(shù)計(jì)算，已分組資料可用公式計(jì)算。這里我們僅介紹如何用函數(shù)來(lái)計(jì)算未分組資

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

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

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

20、A1：A30）”，回車(chē)后得幾何平均數(shù)為1526.3。5調(diào)和平均數(shù)單擊任一空白單元格，輸入“=HARMEAN（A1：A30）”，回車(chē)后得調(diào)和平均數(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）”，回車(chē)后得截尾平均數(shù)為1526.257全距單擊任一空白單元格，輸入“=MAX（A1：A30）-MIN（A1：A30）”，回車(chē)后得全距為730。8

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

22、述統(tǒng)計(jì)，按確定后打開(kāi)描述統(tǒng)計(jì)對(duì)話(huà)框，如圖11-24所示。圖11-24 描述統(tǒng)計(jì)對(duì)話(huà)框第二步：在輸入?yún)^(qū)域中輸入$A$1:$A$30，在輸出區(qū)域中選擇$C$13，其他復(fù)選框可根據(jù)需要選定，選擇匯總統(tǒng)計(jì)，可給出一系列描述統(tǒng)計(jì)量；選擇平均數(shù)置信度，會(huì)給出用樣本平均數(shù)估計(jì)總體平均數(shù)的置信區(qū)間；第K大值和第K小值會(huì)給出樣本中第K個(gè)大值和第K個(gè)小值。第三步：?jiǎn)螕舸_定，可得輸出結(jié)果，如圖11-25所示。圖11-25 描述統(tǒng)計(jì)輸出結(jié)果上面的結(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上是極差，或全

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

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

25、算環(huán)比發(fā)展速度，在D3中輸入公式：“=B3/B2”，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D7區(qū)域。第四步：計(jì)算平均發(fā)展速度（水平法），選中C9單元格，單擊插入菜單，選中函數(shù)選項(xiàng)，出現(xiàn)插入函數(shù)對(duì)話(huà)框后，選擇GEOMEAN（返回幾何平均值）函數(shù)，在數(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）。本書(shū)主要就長(zhǎng)期趨勢(shì)與季節(jié)變動(dòng)進(jìn)行分析。以直線(xiàn)趨勢(shì)說(shuō)明長(zhǎng)期趨勢(shì)的測(cè)定與預(yù)測(cè)方法。而測(cè)定直線(xiàn)趨勢(shì)的方法主要采用移動(dòng)平均法。移動(dòng)平均法按一定的間隔逐期移動(dòng)，計(jì)算一系列動(dòng)態(tài)平均數(shù)，從而形成一個(gè)由動(dòng)態(tài)平均數(shù)

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

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

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

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

30、變動(dòng)分析。下面仍以例11-8的資料，采用乘法型時(shí)間數(shù)列變動(dòng)分析說(shuō)明移動(dòng)趨勢(shì)剔除法的操作方法。 第一步：輸入各年季度數(shù)據(jù)資料，如圖11-33所示。圖11-33 某酒廠近五年全年及分季啤酒銷(xiāo)售量第二步：計(jì)算四個(gè)季度的移動(dòng)平均數(shù)。計(jì)算移動(dòng)平均數(shù)，可以采用移動(dòng)平均工具，也可以使用公式與函數(shù)。移動(dòng)平均工具在前面內(nèi)容中已講過(guò)，本例采用公式與函數(shù)方法來(lái)計(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單元格，輸入“=AV

31、ERAGE（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ū)域。圖11-34 移動(dòng)平均及剔除趨勢(shì)數(shù)據(jù) 第五步：計(jì)算季節(jié)比率。將圖11-34中Y/T*100得到的數(shù)據(jù)重新編排，得到如圖11-35中左邊數(shù)據(jù)表中前五行的基本數(shù)據(jù)（本例將圖11-34中Y/T*100的數(shù)據(jù)四舍五入，保留兩位小數(shù)）。然后利用按月平均法計(jì)算季節(jié)比率，具體步驟參見(jiàn)月平均法，本例省略，最終結(jié)果如圖11-35所示。圖11-35 利用移動(dòng)平均趨勢(shì)剔除法分析季

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

33、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ù) 現(xiàn)以生產(chǎn)量平均指數(shù)為例，說(shuō)明加權(quán)算術(shù)平均法的計(jì)算方法。 例11-10 圖中的A1：A4區(qū)域內(nèi)是某企業(yè)生產(chǎn)情況的統(tǒng)計(jì)資料，我們要以基期總成本為同度量因素，計(jì)算生產(chǎn)量平均指數(shù)。如圖11-37所示。圖11-37 用Excel計(jì)算平均指數(shù)資料及結(jié)果計(jì)算步驟：第一步：計(jì)算個(gè)體指數(shù)k=q1/q0：在F2中輸入“=D2/C2”，并用

34、鼠標(biāo)拖曳將公式復(fù)制到F2：F4區(qū)域。第二步：計(jì)算k* p0q0并求和。在G2中輸入“=F2*E2”，并用鼠標(biāo)拖曳將公式復(fù)制到G2：G4區(qū)域。選定G2：G4區(qū)域，單擊工具欄上的“”按鈕，在G5列出現(xiàn)該列的求和值。第三步：計(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è)p0

35、q1和p0q1：在H2中輸入“=C2*F2”，并用鼠標(biāo)拖曳將公式復(fù)制到H2：H4區(qū)域。選定H2：H6區(qū)域，單擊工具欄上的“”按鈕，在H5出現(xiàn)該列的求和值。圖11-38 用Excel進(jìn)行因素分析資料及結(jié)果第三步：計(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)量指數(shù)。第六步：計(jì)算單位成本指數(shù)：在C8中輸入“=I5/H5”即求得單位成本指數(shù)。第五節(jié) 用E

36、xcel進(jìn)行相關(guān)與回歸分析例11-12 10個(gè)學(xué)生身高和體重的情況如下：首先把有關(guān)數(shù)據(jù)輸入Excel的單元格中，如圖11-39所示：圖11-39 Excel數(shù)據(jù)集一、用Excel進(jìn)行相關(guān)分析用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ù)”，打開(kāi)“插入函數(shù)”對(duì)話(huà)框，在“選擇類(lèi)別”中選擇“統(tǒng)計(jì)”；在“選擇函數(shù)”中選擇“CORRE

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