




版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進(jìn)行舉報(bào)或認(rèn)領(lǐ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é)是一門應(yīng)用性非常強(qiáng)的學(xué)科。而統(tǒng)計(jì)工作的每一個(gè)環(huán)節(jié)幾乎都離不開統(tǒng)計(jì)計(jì)算機(jī)軟件的應(yīng)用。典型的統(tǒng)計(jì)軟件有SAS、SPSS、MINITAB、STATISTICA、Excel等。其中由美國微軟公司開發(fā)的Excel電子表格軟件,是辦公自動化中非常重要的一款軟件,它不僅能夠進(jìn)行表格處理、圖形分析、數(shù)據(jù)的自
2、動處理和計(jì)算,而且簡單易用,具有一定統(tǒng)計(jì)基礎(chǔ)知識的人都可以利用它進(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)查,以后我國的統(tǒng)計(jì)調(diào)查將以抽樣為主。針對抽樣調(diào)查,Excel的數(shù)據(jù)分析工具中提供了一個(gè)專門的“抽樣”工具,可以幫助使用者快速完成抽樣工作。使用Excel進(jìn)行抽樣,首先要對各個(gè)總體單位進(jìn)行編號,編號可以按隨機(jī)原則,也可以按有關(guān)標(biāo)志或無關(guān)標(biāo)志,本節(jié)以例11-1為操作范例進(jìn)行闡述。例11-1 假定有100名消費(fèi)者,從中選取15人進(jìn)行工資收入調(diào)
3、查。首先,將100名消費(fèi)者編號為1100號,輸入編號,形成總體單位編號表如圖11-1所示。圖11-1 總體各單位編號表完成總體單位編號清單后,利用“抽樣”工具進(jìn)行抽樣的具體操作如下: 第一步:單擊“工具”菜單,選擇“數(shù)據(jù)分析”選項(xiàng)(若無數(shù)據(jù)分析選項(xiàng),可在工具菜單下選擇加載宏,在彈出的對話框中選擇分析工具庫,便可出現(xiàn)數(shù)據(jù)分析選項(xiàng))。 第二步:打開“數(shù)據(jù)分析”對話框,從其對話框的“分析工具”列表中選擇“抽樣”選項(xiàng),如圖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ū)域?yàn)?A$1:$J$10。輸入?yún)^(qū)域有兩種方法:一是用手工逐字錄入;二是用鼠標(biāo)左鍵單擊圖11-1中的$A$1位置,出現(xiàn)虛線框,然后拖拉虛線框,選中表中全部數(shù)字,自動在圖11-3中的“輸入?yún)^(qū)域”顯示出$A$1:$J$10。系統(tǒng)將從A列開始抽取樣本,然后按順序抽取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ī)”模式適用于簡單隨機(jī)抽樣、分層抽樣、整群抽樣和階段抽樣。簡單隨機(jī)抽樣即純隨機(jī)抽樣,只需在“樣本數(shù)”框中輸入要抽取的樣本單位數(shù)即可,本例為15。分類抽樣即類型抽樣,先將總體單位按某一標(biāo)志分類編號,然后在每一類中隨機(jī)抽取若干單位,這種抽樣方法實(shí)際是分組法與隨機(jī)抽樣的結(jié)合。整群抽樣,先將總體單位分類編號,然后按隨機(jī)原則抽取若干類作為樣本,對抽中的類的所有單位全部進(jìn)行調(diào)查??梢钥闯觯纠褂玫木幪栞斎敕椒?,只適用于等距抽樣和簡單隨機(jī)抽樣。3指定輸出方向在“輸出選項(xiàng)”框中有三種輸出去向。
6、在“輸出區(qū)域”框中輸入總體單位編號所在的單元格區(qū)域。在本例中,輸入?yún)^(qū)域?yàn)?A$12。也可以通過選擇“新工作表”或“新工作簿”將抽樣結(jié)果放在新工作表或新工作薄中。第四步:單擊“確定”按鈕后,在指定的位置給出抽樣的結(jié)果。如圖11-4所示。圖11-4 等距抽樣結(jié)果 二、用Excel整理數(shù)據(jù)通過統(tǒng)計(jì)調(diào)查得到的數(shù)據(jù)是雜亂的,沒有規(guī)則的,因此,必須對搜集到的大量的原始數(shù)據(jù)加工整理,經(jīng)過數(shù)據(jù)分析得到科學(xué)結(jié)論。統(tǒng)計(jì)整理包括對數(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戶居民購買消費(fèi)
7、品支出資料如圖11-5所示。(單位:元)圖11-5 居民購買消費(fèi)品月支出對其按800900、9001000、10001100、11001200、12001300、13001400、14001500、15001600、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ì)分組的操作過程如下。首先,在使用此函數(shù)時(shí),先將樣本數(shù)據(jù)排成一列,本例中為A1:A50。然后,利用頻數(shù)分布函數(shù)進(jìn)行統(tǒng)計(jì)分組和計(jì)算頻數(shù),具體操作步驟如下:第一步:選定單元格區(qū)域,本例中選定的區(qū)域
8、為D3:D11,單擊“插入”菜單,選擇“函數(shù)”選項(xiàng),彈出“插入函數(shù)”對話框,如圖11-6所示。圖11-6 插入函數(shù)對話框在“選擇類別”中選擇“統(tǒng)計(jì)”,在“選擇函數(shù)”中選擇“FREQUENCY”。如圖11-7所示:圖11-7 選擇“FREQUENCY”對話框 第二步:打開“FREQUENCY”對話框,輸入待分組數(shù)據(jù)與分組標(biāo)志,如圖11-8所示。圖11-8 “FREQUENCY”對話框 在“FREQUENCY”對話框中有“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è)定對Data_array進(jìn)行頻率計(jì)算的分段點(diǎn)。本例中為899、999、1099、1199、1299、1399、1499、1599、1699。 注意:頻數(shù)分布函數(shù)要求按組距的上限分組,不接受非數(shù)值的分組標(biāo)志(如“不足××”或“××以上”等)。在輸入的數(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ù)進(jìn)行統(tǒng)計(jì)分析的功能就全部操作完成了。2直方圖分析工具與頻數(shù)分布函數(shù)只能進(jìn)行統(tǒng)計(jì)分組和頻數(shù)計(jì)算相比,直方圖分析工具可完成數(shù)據(jù)的分組、頻數(shù)分布與累積頻數(shù)的計(jì)、繪制直方圖與累積折線圖等一系列操作。仍以例11-2為操作范例,闡述直方圖分析工具的統(tǒng)計(jì)整理功能,其操作過程如下。首先,將樣本數(shù)據(jù)排成一列,最好對數(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、驟如下。 第一步:單擊“工具”菜單,選擇“數(shù)據(jù)分析”選項(xiàng);打開“數(shù)據(jù)分析”對話框,從“分析工具”列表中選擇“直方圖”選項(xiàng),如圖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ū)域有標(biāo)志項(xiàng),則選中“標(biāo)志”復(fù)選框;否則,系統(tǒng)自動生成數(shù)據(jù)標(biāo)志?!敖邮諈^(qū)域”輸入接收區(qū)域的單元格引用,該框可為空,則系統(tǒ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)中可選擇輸出去向,輸出去向類似于“抽樣”對話框的輸出去向。本例中選擇“輸出區(qū)域”為$C$1。(3)選擇“柏拉圖”可以在輸出表中同時(shí)按降序排列頻數(shù)數(shù)據(jù);選擇“累積百分率”可在輸出表中增加一列累積百分比數(shù)值,并繪制一條百分比曲線;選擇“圖表輸出”可生成一個(gè)嵌入式直方圖。第三步:單擊“確定”按鈕,在輸出區(qū)域單元格可得到頻數(shù)分布,如圖11-13所示。圖11-13 直方圖頻數(shù)分布結(jié)果 第四步:將條形圖轉(zhuǎn)換成標(biāo)準(zhǔn)直方圖,如圖11-14所示。具體做法:左鍵單擊條形圖的任一直條,再單擊右鍵,在快捷菜單中選取“數(shù)據(jù)系列格式”,然后在“數(shù)據(jù)系列格式”對話框中選擇選項(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ì)圖顯示資料則具有形象生動、一目了然的優(yōu)點(diǎn),通過圖形可以方便地觀察到數(shù)量之間的對比關(guān)系、總體的結(jié)構(gòu)特征以及變化發(fā)展趨勢。統(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ù)我國1991-2003年國內(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年國內(nèi)生產(chǎn)總值構(gòu)成數(shù)據(jù) 第二步:在想繪制圖形的數(shù)據(jù)中選擇任一單元格,然后在“插入”菜單中選擇“圖表”選項(xiàng),或單擊工具欄中的圖表向?qū)О粹o,出現(xiàn)圖11-16。圖11-16 “圖表向?qū)?4步驟之1-圖表類型”對話框第三步:選定圖表類型。在彈出的“圖表向?qū)?4步驟之1-圖表類型”對話框中選擇所需要的圖表的類型,此處在“圖表類型”中選“柱形圖”,在“子圖表類型”中選第一個(gè)“簇狀柱形圖”,然后單擊“下一步”按鈕,如圖11-17所示。圖11-17 “圖表向?qū)?4步驟之2-圖表源數(shù)據(jù)”對話框第四步:確定數(shù)據(jù)范圍。在彈出的“圖表向?qū)?4步驟
15、之2-圖表源數(shù)據(jù)”對話框中規(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)”對話框中可給圖表命名,將“分類(X)軸”命名為“年份”;將“數(shù)值(Y)軸”命名為“百分比(%)”。然后,單擊“下一步”按鈕,如圖11-19年示。圖11-19 “圖表向?qū)?4步驟之3-圖表選項(xiàng)”對話框 第六步:選擇圖表位置并顯示結(jié)果。在彈出的“圖表向?qū)?4步驟之4圖表位置”對話框中可為圖表選擇保存位置,或放在獨(dú)立的工作表中,或作為一個(gè)對
16、象放在當(dāng)前工作表中。然后,單擊“完成”按鈕。本例中選擇了放在獨(dú)立的工作表中,如圖11-20所示。圖11-20 1991-2003年國內(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ù)等。一般來說,在Excel中求這些統(tǒng)計(jì)量,未分組資料可用函數(shù)計(jì)算,已分組資料可用公式計(jì)算。這里我們僅介紹如何用函數(shù)來計(jì)算未分組資
17、料的描述統(tǒng)計(jì)量。用函數(shù)運(yùn)算有兩種方法:一是手工輸入函數(shù)名稱及參數(shù)。這種輸入形式比較簡單、快捷。但需要非常熟悉函數(shù)名稱及其參數(shù)的輸入形式。所以,只有比較簡單的函數(shù)才用這種方法輸入;二是函數(shù)導(dǎo)入法。這是一種最為常用的辦法,它適合于所有函數(shù)的使用,而且在導(dǎo)入過程中有向?qū)崾?,因而非常方便。函?shù)的一般導(dǎo)入過程為:點(diǎn)菜單“插入”;找“函數(shù)”,此時(shí)出現(xiàn)一個(gè)“插入函數(shù)”對話框;在對話框的“選擇類別”中確定函數(shù)的類別(如常用函數(shù)或統(tǒng)計(jì));在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱,如SUM、MODE等;點(diǎn)“確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對話框向?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ù)名稱及參數(shù)單擊任一單元格,輸入“=MODE(A1:A30)”,回車后即可得眾數(shù)為1560。如圖11-22所示。圖11-22 手工輸入函數(shù)名稱及參數(shù)(2)函數(shù)導(dǎo)入法點(diǎn)菜單“插入”;找“函數(shù)”,此時(shí)出現(xiàn)一個(gè)“插入函數(shù)”對話框;在對話框的“選擇類別”中確定函數(shù)的類別“統(tǒng)計(jì)”;在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱“MODE”,如圖11-23所示。圖11-22 函數(shù)導(dǎo)入法“插入函數(shù)”對話框點(diǎn)“確定”后即可出現(xiàn)該函數(shù)運(yùn)算的對話
19、框向?qū)?,在Number1處輸入A1:A30或選擇Excel中的A1:A30區(qū)域,如圖11-23所示。按“確定”,在Excel中即得到眾數(shù)1560。圖11-23 函數(shù)導(dǎo)入法“函數(shù)參數(shù)”對話框運(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(
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個(gè)數(shù)據(jù)中去掉最大的三個(gè)值和最小的三個(gè)值,再求平均數(shù)。單擊任一空白單元格,輸入“=TRIMEAN(A1:A30,6/30)”,回車后得截尾平均數(shù)為1526.257全距單擊任一空白單元格,輸入“=MAX(A1:A30)-MIN(A1:A30)”,回車后得全距為730。8
21、標(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ù)為008653110偏度系數(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),從其對話框中選擇描
22、述統(tǒng)計(jì),按確定后打開描述統(tǒng)計(jì)對話框,如圖11-24所示。圖11-24 描述統(tǒng)計(jì)對話框第二步:在輸入?yún)^(qū)域中輸入$A$1:$A$30,在輸出區(qū)域中選擇$C$13,其他復(fù)選框可根據(jù)需要選定,選擇匯總統(tǒng)計(jì),可給出一系列描述統(tǒng)計(jì)量;選擇平均數(shù)置信度,會給出用樣本平均數(shù)估計(jì)總體平均數(shù)的置信區(qū)間;第K大值和第K小值會給出樣本中第K個(gè)大值和第K個(gè)小值。第三步:單擊確定,可得輸出結(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í)間序列分析一、測定增長量和平均增長量例11-5 根據(jù)我國1998年2003年社會消費(fèi)品零售總額,計(jì)算逐期增長量、累計(jì)增長量和平均增長量。如圖11-26所示。圖11-26 用Excel計(jì)算增長量和平均增長量資料及結(jié)果計(jì)算步驟如下:第一步:在A列輸入年份,在B列輸入社會消費(fèi)品零售總額。第二步:計(jì)算逐期增長量,在C3中輸入公式:“=B3-B2”,并用鼠標(biāo)拖曳將公式復(fù)制到C3:C7區(qū)域。第三步:計(jì)算累計(jì)增長量,在D3中輸入公式:“=B3-$B$2
24、”,并用鼠標(biāo)拖曳將公式復(fù)制到D3:D7區(qū)域。(英文輸入法狀態(tài),按下“shift”和“4”組合鍵)第四步:計(jì)算平均增長量(水平法),在C10中輸入公式:“=(B7-B2)/5”,按回車鍵,即可得以平均增長量。二、測定發(fā)展速度和平均發(fā)展速度例11-6 以1998年2003年社會消費(fèi)品零售總額為例,說明如何計(jì)算定基發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速度。如圖11-27所示。圖11-27 用Excel計(jì)算發(fā)展速度和平均發(fā)展速度資料及結(jié)果第一步:在A列輸入年份,在B列輸入社會消費(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ù)對話框后,選擇GEOMEAN(返回幾何平均值)函數(shù),在數(shù)值區(qū)域中輸入D3:D8即可。三、計(jì)算長期趨勢影響時(shí)間數(shù)列各項(xiàng)數(shù)值變動的因素是多方面的,主要有4種:長期趨勢(T)、季節(jié)變動(S)、循環(huán)變動(C)、不規(guī)則變動(I)。本書主要就長期趨勢與季節(jié)變動進(jìn)行分析。以直線趨勢說明長期趨勢的測定與預(yù)測方法。而測定直線趨勢的方法主要采用移動平均法。移動平均法按一定的間隔逐期移動,計(jì)算一系列動態(tài)平均數(shù),從而形成一個(gè)由動態(tài)平均數(shù)
26、組成的新的時(shí)間數(shù)列,修勻原時(shí)間數(shù)列,顯示出長期趨勢。在Excel中,使用移動平均法測定長期趨勢,可以利用公式或AVERAGE函數(shù),也可利用Excel提供的“移動平均”工具。由于公式或函數(shù)方法前面已講過,而且只能獲得數(shù)據(jù),不能直接獲得長期趨勢圖,因此長期趨勢主要以“移動平均”工具來計(jì)算分析。例11-7 根據(jù)我國19902003年的國內(nèi)生產(chǎn)總值的數(shù)據(jù)(單位:億元),如圖11-28所示,用移動平均法計(jì)算預(yù)測我國國內(nèi)生產(chǎn)總值的長期發(fā)展趨勢。圖11-28 19902003年國內(nèi)生產(chǎn)總值數(shù)據(jù)表第一步:單擊“工具”菜單,選擇“數(shù)據(jù)分析”選項(xiàng)。打開“數(shù)據(jù)分析”對話框,從其“分析工具”列表中選擇“移動平均”選項(xiàng)
27、,單擊“確定”按鈕,打開“移動平均”對話框。圖11-29所示。圖11-29 “移動平均”對話框第二步:確定輸入?yún)^(qū)域和輸出區(qū)域,選中“圖表輸出”復(fù)選框。如圖11-29所示。第三步:單擊“確定”按鈕后,在指定位置給出移動平均計(jì)算結(jié)果,如圖11-30所示。圖11-30 移動平均法計(jì)算結(jié)果四、計(jì)算季節(jié)變動季節(jié)變動是指在一年以內(nèi),受自然季節(jié)和社會習(xí)俗等因素影響而發(fā)生的有規(guī)律的、周期性的變動。測定季節(jié)變動的方法有兩種:按月(季)平均法和移動平均趨勢剔除法。1、按月平均法例11-8 某啤酒廠近五年全年及分月啤酒銷售量數(shù)據(jù)如圖11-31所示。結(jié)合五年分月數(shù)據(jù),利用Excel按月平均法測定季節(jié)變動。圖11-31
28、 某酒廠近五年全年及分月啤酒銷售量第一步:按已知數(shù)據(jù)資料列出計(jì)算表,將各年同月的數(shù)值列在同一列內(nèi)。第二步:計(jì)算各年合計(jì)與各年同月數(shù)值之和。計(jì)算每年的啤酒銷量總數(shù):單擊N3單元格,輸入“=SUM(B3:M3)”,并用鼠標(biāo)拖曳將公式復(fù)制到N4:N7區(qū)域,得各年銷量總數(shù);計(jì)算各年同月銷售總數(shù):單擊B8單元格,輸入“=SUM(B3:B7)”,并用鼠標(biāo)拖曳將公式復(fù)制到C8:N8區(qū)域,得各年同月銷量總數(shù)與全部銷量之和。 第三步:計(jì)算同月平均數(shù)與總的月平均數(shù)。計(jì)算同月平均數(shù):單擊B9單元格,輸入“=B8/5”,并用鼠標(biāo)拖曳將公式復(fù)制到C9:M9區(qū)域;計(jì)算總的月平均數(shù):單擊N9單元格,輸入“=N8/60”回車
29、得結(jié)果為43.21667。 第四步:計(jì)算季節(jié)比率。單擊B10單元格,輸入“=B9*100/43.21667”,并用鼠標(biāo)拖曳將公式復(fù)制到C10:M10區(qū)域。 第五步:計(jì)算季節(jié)比率之和,繪制季節(jié)變動曲線。單擊N10單元格,輸入“=SUM(B10:M10)”,回車得季節(jié)比率之和為1200。根據(jù)季節(jié)比率,可繪制季節(jié)變動曲線。圖11-32 按月平均法分析季節(jié)變動數(shù)據(jù)圖2移動平均趨勢剔除法。直接用按月平均法忽略了長期趨勢的影響,因此得出的季節(jié)比率不夠精確。為了彌補(bǔ)這一缺點(diǎn),可以采用移動平均趨勢剔除法來測定季節(jié)變動。利用移動平均趨勢剔除法分析季節(jié)變動有兩種方法:乘法型時(shí)間數(shù)列季節(jié)變動分析和加法型時(shí)間數(shù)列季節(jié)
30、變動分析。下面仍以例11-8的資料,采用乘法型時(shí)間數(shù)列變動分析說明移動趨勢剔除法的操作方法。 第一步:輸入各年季度數(shù)據(jù)資料,如圖11-33所示。圖11-33 某酒廠近五年全年及分季啤酒銷售量第二步:計(jì)算四個(gè)季度的移動平均數(shù)。計(jì)算移動平均數(shù),可以采用移動平均工具,也可以使用公式與函數(shù)。移動平均工具在前面內(nèi)容中已講過,本例采用公式與函數(shù)方法來計(jì)算。單擊D4單元格,輸入“=AVERAGE(C2:C5)”,并用鼠標(biāo)拖曳將公式復(fù)制到D5:D20區(qū)域。第三步:移正平均。因?yàn)楸纠桥紨?shù)項(xiàng)移動平均,所以還需將四項(xiàng)移動平均值再進(jìn)行兩項(xiàng)“移正”平均,如果是奇數(shù)項(xiàng)移動平均,則該步驟省去。單擊E4單元格,輸入“=AV
31、ERAGE(D4:D5)”,并用鼠標(biāo)拖曳將公式復(fù)制到E5:E19區(qū)域。 第四步:消除長期趨勢。本例采用乘法模型,因此,將原數(shù)列除以趨勢值以消除長期趨勢。單擊F4單元格,輸入“=C4*100/E4”,并用鼠標(biāo)拖曳將公式復(fù)制到F5:F19區(qū)域。圖11-34 移動平均及剔除趨勢數(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é)果如圖11-35所示。圖11-35 利用移動平均趨勢剔除法分析季
32、節(jié)變動數(shù)據(jù)圖第四節(jié) 用Excel進(jìn)行指數(shù)分析指數(shù)分析法是研究社會經(jīng)濟(jì)現(xiàn)象數(shù)量變動情況的一種統(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ù)為例,說明加權(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ù)。第一步:單擊任一個(gè)空白單元格,單擊“插入”菜單,選擇“函數(shù)”,打開“插入函數(shù)”對話框,在“選擇類別”中選擇“統(tǒng)計(jì)”;在“選擇函數(shù)”中選擇“CORRE
37、L”,單擊確定后,出現(xiàn)CORREL對話框。第二步:在array1中輸入B2:B11,在array2中輸入C2:C11,即可在對話框下方顯示出計(jì)算結(jié)果為0.896028。如圖11-40所示。圖11-40 CORREL對話框及輸入結(jié)果2用相關(guān)系數(shù)宏計(jì)算相關(guān)系數(shù)第一步:單擊“工具”菜單,選擇“數(shù)據(jù)分析”項(xiàng),在“數(shù)據(jù)分析”項(xiàng)中選擇“相關(guān)系數(shù)”,彈出“相關(guān)系數(shù)”對話框,如圖11-41所示。圖11-41 相關(guān)系數(shù)對話框 第二步:在“輸入?yún)^(qū)域”輸入$B$1:$C$11,“分組方式”選擇逐列,選擇“標(biāo)志位于第一行”,在“輸出區(qū)域”中輸入$E$1,單擊“確定”,得輸出結(jié)果如圖11-42所示。圖11-42 相關(guān)分析輸出結(jié)果 在上面的輸出結(jié)果中,身高和體重的
溫馨提示
- 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
- 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
- 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
- 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
- 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
- 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
- 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時(shí)也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。
最新文檔
- 客服人員服務(wù)標(biāo)準(zhǔn)與獎勵(lì)協(xié)議3篇
- 定金合同轉(zhuǎn)讓人權(quán)益書3篇
- 工程項(xiàng)目施工安全合同樣本3篇
- 一日常規(guī)心得體會 一日常規(guī)工作培訓(xùn)心得(3篇)
- 家庭裝修施工合同違約上訴3篇
- 物業(yè)應(yīng)急預(yù)案范文(19篇)
- 遠(yuǎn)離垃圾食品關(guān)注身體健康演講稿(34篇)
- 會計(jì)核算心得體會(15篇)
- 保安個(gè)人述職報(bào)告(27篇)
- 2024年羅甸縣第一醫(yī)共體平巖分院招聘專業(yè)技術(shù)人員考試真題
- 《小學(xué)生預(yù)防溺水安全教育班會》課件
- 傳統(tǒng)園林技藝智慧樹知到期末考試答案2024年
- 直播中的禮儀與形象塑造
- 2024年八年級數(shù)學(xué)下冊期中檢測卷【含答案】
- 老年人中醫(yī)健康知識講座總結(jié)
- 海南聲茂羊和禽類半自動屠宰場項(xiàng)目環(huán)評報(bào)告
- 跳繩市場調(diào)研報(bào)告
- 《民法典》合同編通則及司法解釋培訓(xùn)課件
- 《大學(xué)生的情緒》課件
- 交通事故法律處理與索賠案例分析與實(shí)踐指導(dǎo)
- 大學(xué)生組織管理能力大賽題庫第三屆
評論
0/150
提交評論