PAGE52 統(tǒng)計(jì)學(xué)原理./第十一章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é)是一門應(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ù)的自動(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è)專門的"抽樣"工具,可以幫助使用者快速完成抽樣工作。使用Excel進(jìn)行抽樣,首先要對(duì)各個(gè)總體單位進(jìn)行編號(hào),編號(hào)可以按隨機(jī)原則,也可以按有關(guān)標(biāo)志或無(wú)關(guān)標(biāo)志,本節(jié)以例11-1為操作X例進(jìn)行闡述。假定有100名消費(fèi)者,從中選取15人進(jìn)行工資收入調(diào)查。首先,將100名消費(fèi)者編號(hào)為1~100號(hào),輸入編號(hào),形成總體單位編號(hào)表如圖11-1所示。圖11-1總體各單位編號(hào)表完成總體單位編號(hào)清單后,利用"抽樣"工具進(jìn)行抽樣的具體操作如下：第一步：?jiǎn)螕?工具"菜單,選擇"數(shù)據(jù)分析"選項(xiàng)〔若無(wú)數(shù)據(jù)分析選項(xiàng),可在工具菜單下選擇加載宏,在彈出的對(duì)話框中選擇分析工具庫(kù),便可出現(xiàn)數(shù)據(jù)分析選項(xiàng)〕。第二步：打開(kāi)"數(shù)據(jù)分析"對(duì)話框,從其對(duì)話框的"分析工具"列表中選擇"抽樣"選項(xiàng),如圖11-2所示。圖11-2"數(shù)據(jù)分析"對(duì)話框第三步：?jiǎn)螕?確定"按鈕,打開(kāi)"抽樣"對(duì)話框,確定輸入?yún)^(qū)域、抽樣方法和輸出區(qū)域,如圖11-3所示。圖11-3"抽樣"對(duì)話框1．確定輸入?yún)^(qū)域在"抽樣"對(duì)話框的"輸入?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)虛線框,然后拖拉虛線框,選中表中全部數(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ī)"兩種抽樣模式?！?〕"周期"模式即所謂的等距抽樣、機(jī)械抽樣或系統(tǒng)抽樣。此種抽樣方法,需要確定周期間隔,周期間隔由總體單位數(shù)除以要抽取的樣本數(shù)而求得。本例中,要在100個(gè)總體單位中抽取15個(gè),則在"間隔"框中輸入6。〔2〕"隨機(jī)"模式適用于簡(jiǎn)單隨機(jī)抽樣、分層抽樣、整群抽樣和階段抽樣。簡(jiǎn)單隨機(jī)抽樣即純隨機(jī)抽樣,只需在"樣本數(shù)"框中輸入要抽取的樣本單位數(shù)即可,本例為15。分類抽樣即類型抽樣,先將總體單位按某一標(biāo)志分類編號(hào),然后在每一類中隨機(jī)抽取若干單位,這種抽樣方法實(shí)際是分組法與隨機(jī)抽樣的結(jié)合。整群抽樣,先將總體單位分類編號(hào),然后按隨機(jī)原則抽取若干類作為樣本,對(duì)抽中的類的所有單位全部進(jìn)行調(diào)查?？梢钥闯?本例所使用的編號(hào)輸入方法,只適用于等距抽樣和簡(jiǎn)單隨機(jī)抽樣。3．指定輸出方向在"輸出選項(xiàng)"框中有三種輸出去向。在"輸出區(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)行分類匯總并計(jì)算各類指標(biāo)與利用統(tǒng)計(jì)圖或統(tǒng)計(jì)表描述統(tǒng)計(jì)匯總結(jié)果等。Excel提供了多種數(shù)據(jù)整理工具：頻數(shù)分布函數(shù)與直方圖分析工具等。例11-2根據(jù)抽樣調(diào)查,某月某市50戶居民購(gòu)買消費(fèi)品支出資料如圖11-5所示?！矄挝唬涸硤D11-5居民購(gòu)買消費(fèi)品月支出對(duì)其按800~900、900~1000、1000~1100、1100~1200、1200~1300、1300~1400、1400~1500、1500~1600、1600以上分為9個(gè)組。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ì)分組的操作過(guò)程如下。首先,在使用此函數(shù)時(shí),先將樣本數(shù)據(jù)排成一列,本例中為A1：A50。然后,利用頻數(shù)分布函數(shù)進(jìn)行統(tǒng)計(jì)分組和計(jì)算頻數(shù),具體操作步驟如下：第一步：選定單元格區(qū)域,本例中選定的區(qū)域?yàn)镈3：D11,單擊"插入"菜單,選擇"函數(shù)"選項(xiàng),彈出"插入函數(shù)"對(duì)話框,如圖11-6所示。圖11-6插入函數(shù)對(duì)話框在"選擇類別"中選擇"統(tǒng)計(jì)",在"選擇函數(shù)"中選擇"FREQUENCY"。如圖11-7所示：圖11-7選擇"FREQUENCY"對(duì)話框第二步：打開(kāi)"FREQUENCY"對(duì)話框,輸入待分組數(shù)據(jù)與分組標(biāo)志,如圖11-8所示。圖11-8"FREQUENCY"對(duì)話框在"FREQUENCY"對(duì)話框中有"Data_array"和"Bins_array"兩個(gè)文本框?！?〕Data_array用于計(jì)算頻率的數(shù)組,或?qū)?shù)組單元區(qū)域的引用。本例中為A1：A50。〔2〕Bins_array數(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ù)分布結(jié)果至此,頻數(shù)分布函數(shù)進(jìn)行統(tǒng)計(jì)分析的功能就全部操作完成了。2．直方圖分析工具與頻數(shù)分布函數(shù)只能進(jìn)行統(tǒng)計(jì)分組和頻數(shù)計(jì)算相比,直方圖分析工具可完成數(shù)據(jù)的分組、頻數(shù)分布與累積頻數(shù)的計(jì)、繪制直方圖與累積折線圖等一系列操作。仍以例11-2為操作X例,闡述直方圖分析工具的統(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)行分析,具體操作步驟如下。第一步：?jiǎn)螕?工具"菜單,選擇"數(shù)據(jù)分析"選項(xiàng)；打開(kāi)"數(shù)據(jù)分析"對(duì)話框,從"分析工具"列表中選擇"直方圖"選項(xiàng),如圖11-11所示。圖11-11"數(shù)據(jù)分析"對(duì)話框第二步：打開(kāi)"直方圖"對(duì)話框,確定輸入?yún)^(qū)域、接收區(qū)域和輸出區(qū)域,如圖11-12所示。圖11-12"直方圖"對(duì)話框〔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?！?〕在"輸出"選項(xiàng)中可選擇輸出去向,輸出去向類似于"抽樣"對(duì)話框的輸出去向。本例中選擇"輸出區(qū)域"為$C$1?！?〕選擇"柏拉圖"可以在輸出表中同時(shí)按降序排列頻數(shù)數(shù)據(jù)；選擇"累積百分率"可在輸出表中增加一列累積百分比數(shù)值,并繪制一條百分比曲線；選擇"圖表輸出"可生成一個(gè)嵌入式直方圖。第三步：?jiǎn)螕?確定"按鈕,在輸出區(qū)域單元格可得到頻數(shù)分布,如圖11-13所示。圖11-13直方圖頻數(shù)分布結(jié)果第四步：將條形圖轉(zhuǎn)換成標(biāo)準(zhǔn)直方圖,如圖11-14所示。具體做法：左鍵單擊條形圖的任一直條,再單擊右鍵,在快捷菜單中選取"數(shù)據(jù)系列格式",然后在"數(shù)據(jù)系列格式"對(duì)話框中選擇選項(xiàng)標(biāo)簽,將間距寬度改為0,單擊"確定"按鈕即可。圖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ì)圖形供用戶根據(jù)需要和圖形功能選擇使用。Excel提供的圖形工具有：柱形圖、折線圖、餅圖、散點(diǎn)圖、面積圖、環(huán)形圖、股價(jià)圖等。各種圖的作法XX小異,本章以柱形圖為,介紹制作統(tǒng)計(jì)圖的工作步驟。例11-3根據(jù)我國(guó)1991-20xx國(guó)內(nèi)生產(chǎn)總值構(gòu)成數(shù)據(jù)制作相應(yīng)的統(tǒng)計(jì)圖。第一步：創(chuàng)建工作表,即將統(tǒng)計(jì)資料輸入到Excel中,如圖11-15所示。圖11-151991-20xx國(guó)內(nèi)生產(chǎn)總值構(gòu)成數(shù)據(jù)第二步：在想繪制圖形的數(shù)據(jù)中選擇任一單元格,然后在"插入"菜單中選擇"圖表"選項(xiàng),或單擊工具欄中的圖表向?qū)О粹o,出現(xiàn)圖11-16。圖11-16"圖表向?qū)?4步驟之1-圖表類型"對(duì)話框第三步：選定圖表類型。在彈出的"圖表向?qū)?4步驟之1-圖表類型"對(duì)話框中選擇所需要的圖表的類型,此處在"圖表類型"中選"柱形圖",在"子圖表類型"中選第一個(gè)"簇狀柱形圖",然后單擊"下一步"按鈕,如圖11-17所示。圖11-17"圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)"對(duì)話框第四步：確定數(shù)據(jù)X圍。在彈出的"圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)"對(duì)話框中規(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ì)話框中可給圖表命名,將"分類〔X〕軸"命名為"年份"；將"數(shù)值〔Y〕軸"命名為"百分比〔%〕"。然后,單擊"下一步"按鈕,如圖11-19年示。圖11-19"圖表向?qū)?4步驟之3-圖表選項(xiàng)"對(duì)話框第六步：選擇圖表位置并顯示結(jié)果。在彈出的"圖表向?qū)?4步驟之4圖表位置"對(duì)話框中可為圖表選擇保存位置,或放在獨(dú)立的工作表中,或作為一個(gè)對(duì)象放在當(dāng)前工作表中。然后,單擊"完成"按鈕。本例中選擇了放在獨(dú)立的工作表中,如圖11-20所示。圖11-201991-20xx國(guó)內(nèi)生產(chǎn)總值構(gòu)成圖表其他圖形的繪制步驟與柱形圖的繪制步驟類似,只是在圖形類型中選擇不同的圖形類型即可。第二節(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ì)算未分組資料的描述統(tǒng)計(jì)量。用函數(shù)運(yùn)算有兩種方法：一是手工輸入函數(shù)名稱與參數(shù)。這種輸入形式比較簡(jiǎn)單、快捷。但需要非常熟悉函數(shù)名稱與其參數(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ì)話框；在對(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ù)例11-4為了解某單位職工的工資收入情況,隨機(jī)抽取30人月工資如下：圖11-21職工月工資收入用函數(shù)方法求眾數(shù),應(yīng)先將30個(gè)人的工資數(shù)據(jù)輸入A1：A30單元格?！?〕手工輸入函數(shù)名稱與參數(shù)單擊任一單元格,輸入"=MODE〔A1：A30〕",回車后即可得眾數(shù)為1560。如圖11-22所示。圖11-22手工輸入函數(shù)名稱與參數(shù)〔2〕函數(shù)導(dǎo)入法點(diǎn)菜單"插入"；找"函數(shù)",此時(shí)出現(xiàn)一個(gè)"插入函數(shù)"對(duì)話框；在對(duì)話框的"選擇類別"中確定函數(shù)的類別"統(tǒng)計(jì)"；在"選擇函數(shù)"內(nèi)確定欲選的函數(shù)名稱"MODE",如圖11-23所示。圖11-22函數(shù)導(dǎo)入法"插入函數(shù)"對(duì)話框點(diǎn)"確定"后即可出現(xiàn)該函數(shù)運(yùn)算的對(duì)話框向?qū)?在Number1處輸入A1：A30或選擇Excel中的A1：A30區(qū)域,如圖11-23所示。按"確定",在Excel中即得到眾數(shù)1560。圖11-23函數(shù)導(dǎo)入法"函數(shù)參數(shù)"對(duì)話框運(yùn)用函數(shù)導(dǎo)入法,只要知道每個(gè)函數(shù)的涵義,即可按上述程序得到相應(yīng)的運(yùn)算結(jié)果,為節(jié)約篇幅這里不再一一講解,下面僅列示各函數(shù)的涵義與本例中的運(yùn)行結(jié)果。2．中位數(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)和平均數(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.257．全距單擊任一空白單元格,輸入"=MAX〔A1：A30〕-MIN〔A1：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．08653110．偏度系數(shù)單擊任一空白單元格,輸入"=SKEW〔A1：A30〕",回車后得偏度系數(shù)為0.914565。11．峰度系數(shù)單擊任一空白單元格,輸入"=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ì),按確定后打開(kāi)描述統(tǒng)計(jì)對(duì)話框,如圖11-24所示。圖11-24描述統(tǒng)計(jì)對(duì)話框第二步：在輸入?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上是極差,或全距；可以看出與我們前面用函數(shù)計(jì)算的結(jié)果完全相同。最大值為1980,最小值為1250,第三個(gè)最大值為1690,第三個(gè)最小值為1350。第三節(jié)用Excel進(jìn)行時(shí)間序列分析一、測(cè)定增長(zhǎng)量和平均增長(zhǎng)量例11-5根據(jù)我國(guó)1998年~20xx社會(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",并用鼠標(biāo)拖曳將公式復(fù)制到D3：D7區(qū)域。<英文輸入法狀態(tài),按下"shift"和"4"組合鍵>第四步：計(jì)算平均增長(zhǎng)量〔水平法〕,在C10中輸入公式："=〔B7-B2〕/5",按回車鍵,即可得以平均增長(zhǎng)量。二、測(cè)定發(fā)展速度和平均發(fā)展速度例11-6以1998年~20xx社會(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ì)算環(huán)比發(fā)展速度,在D3中輸入公式："=B3/B2",并用鼠標(biāo)拖曳將公式復(fù)制到D3：D7區(qū)域。第四步：計(jì)算平均發(fā)展速度〔水平法〕,選中C9單元格,單擊插入菜單,選中函數(shù)選項(xiàng),出現(xiàn)插入函數(shù)對(duì)話框后,選擇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〕。本書主要就長(zhǎng)期趨勢(shì)與季節(jié)變動(dòng)進(jìn)行分析。以直線趨勢(shì)說(shuō)明長(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中,使用移動(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ó)1990~20xx的國(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-281990~20xx國(guó)內(nèi)生產(chǎn)總值數(shù)據(jù)表第一步：?jiǎn)螕?工具"菜單,選擇"數(shù)據(jù)分析"選項(xiàng)。打開(kāi)"數(shù)據(jù)分析"對(duì)話框,從其"分析工具"列表中選擇"移動(dòng)平均"選項(xiàng),單擊"確定"按鈕,打開(kāi)"移動(dòng)平均"對(duì)話框。圖11-29所示。圖11-29"移動(dòng)平均"對(duì)話框第二步：確定輸入?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)是指在一年以內(nèi),受自然季節(jié)和社會(huì)習(xí)俗等因素影響而發(fā)生的有規(guī)律的、周期性的變動(dòng)。測(cè)定季節(jié)變動(dòng)的方法有兩種：按月〔季〕平均法和移動(dòng)平均趨勢(shì)剔除法。1、按月平均法例11-8某啤酒廠近五年全年與分月啤酒銷售量數(shù)據(jù)如圖11-31所示。結(jié)合五年分月數(shù)據(jù),利用Excel按月平均法測(cè)定季節(jié)變動(dòng)。圖11-31某酒廠近五年全年與分月啤酒銷售量第一步：按已知數(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ù)與全部銷量之和。第三步：計(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：M10〕",回車得季節(jié)比率之和為1200。根據(jù)季節(jié)比率,可繪制季節(jié)變動(dòng)曲線。圖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é)變動(dòng)分析。下面仍以例11-8的資料,采用乘法型時(shí)間數(shù)列變動(dòng)分析說(shuō)明移動(dòng)趨勢(shì)剔除法的操作方法。第一步：輸入各年季度數(shù)據(jù)資料,如圖11-33所示。圖11-33某酒廠近五年全年與分季啤酒銷售量第二步：計(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單元格,輸入"=AVERAGE〔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ì)剔除法分析季節(jié)變動(dòng)數(shù)據(jù)圖用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和∑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",并用鼠標(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è)p0q1和∑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ù)。用Excel進(jìn)行相關(guān)與回歸分析例11-1210個(gè)學(xué)生身高和體重的情況如下：首先把有關(guān)數(shù)據(jù)輸入Excel的單元格中,如圖11-39所示：圖11-39Excel數(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ì)話框,在"選擇類別"中選擇"統(tǒng)計(jì)"；在"選擇函數(shù)"中選擇"CORREL",單擊確定后,出現(xiàn)CORREL對(duì)話框。第二步：在array1中輸入B2：B11,在array2中輸入C2：C11,即可在對(duì)話框下方顯示出計(jì)算結(jié)果為0.896028。如圖11-40所示。圖11-40CORREL對(duì)話框與輸入結(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ì)話框,如圖11-41所示。圖11-41相關(guān)系數(shù)對(duì)話框第二步：在"輸入?yún)^(qū)域"輸入$B$1:$C$11,"分組方式"選擇逐列,選擇"標(biāo)志位于第一行",在"輸出區(qū)域"中輸入$E$1,單擊"確定",得輸出結(jié)果如圖11-42所示。圖11-42相關(guān)分析輸出結(jié)果在上面的輸出結(jié)果中,身高和體重的自相關(guān)系數(shù)均為1,身高和體重的相關(guān)系數(shù)為0.896028,與用函數(shù)計(jì)算的結(jié)果完全相同。二、用Excel進(jìn)行回歸分析Excel進(jìn)行回歸分析同樣分函數(shù)和回歸分析宏兩種形式,其提供了9個(gè)函數(shù)用于建立回歸模型和預(yù)測(cè),這9個(gè)函數(shù)分別是：1．INTERCEPT返回線性回