《統(tǒng)計(jì)學(xué)原理與實(shí)務(wù)》課件第九章 Excel 在統(tǒng)計(jì)中的具體應(yīng)用

運(yùn)用Excel進(jìn)行混凝土的統(tǒng)計(jì)計(jì)算

在建筑工程主體結(jié)構(gòu)施工過程中，技術(shù)人員經(jīng)常遇到混凝土抗壓強(qiáng)度數(shù)理統(tǒng)計(jì)評(píng)定，即計(jì)算同一驗(yàn)收批混凝土立方體抗壓強(qiáng)度的標(biāo)準(zhǔn)差以及回彈法檢測(cè)混凝土抗壓強(qiáng)度時(shí)的標(biāo)準(zhǔn)差計(jì)算。計(jì)算過程如采用手工計(jì)算則相當(dāng)繁瑣，現(xiàn)在工程技術(shù)人員普遍采用科學(xué)計(jì)算器中的統(tǒng)計(jì)功能進(jìn)行計(jì)算，大大提高了工效及準(zhǔn)確性。但如果同一驗(yàn)收批的數(shù)據(jù)量較多時(shí)，采用手工輸入計(jì)算器的方法經(jīng)常會(huì)出現(xiàn)某個(gè)數(shù)據(jù)輸入錯(cuò)誤或遺漏造成全部重來的現(xiàn)象，有時(shí)甚至算了兩三次其結(jié)果都不同。導(dǎo)入案例

在GBJ107-87混凝土強(qiáng)度檢測(cè)評(píng)定標(biāo)準(zhǔn)及JGJ/T23-2001回彈法檢測(cè)混凝土抗壓強(qiáng)度技術(shù)規(guī)程中的標(biāo)準(zhǔn)差可以利用Excel中標(biāo)準(zhǔn)差函數(shù)STDEV計(jì)算，方便快速地計(jì)算出結(jié)果，且輸入、修改、計(jì)算、復(fù)核過程可視化。如有100個(gè)C40的混凝土試塊數(shù)據(jù)(必須是28天齡期的試壓報(bào)告試塊數(shù)據(jù))，在Excel中依次輸入表格，一邊輸入一邊核對(duì)，然后利用函數(shù)計(jì)算即可，如需增加或減少樣本個(gè)數(shù)可以隨時(shí)修改，整個(gè)過程可視化，即時(shí)自動(dòng)計(jì)算。導(dǎo)入案例

導(dǎo)入案例分析：技術(shù)人員是怎樣借助Excel工具，將這些大量的、分散的原始資料進(jìn)行科學(xué)整理，使它系統(tǒng)化、條理化，同時(shí)如何借助Excel工具計(jì)算相關(guān)的統(tǒng)計(jì)指標(biāo)，進(jìn)行統(tǒng)計(jì)分析，以符合統(tǒng)計(jì)研究的需要呢？通過學(xué)習(xí)本章的內(nèi)容就能找到答案。目錄1第一節(jié)Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用2第二節(jié)Excel在描述統(tǒng)計(jì)量中的應(yīng)用3第三節(jié)Excel在時(shí)間序列分析中的應(yīng)用4第四節(jié)Excel在指數(shù)分析中的應(yīng)用5第五節(jié)Excel在抽樣估計(jì)中的應(yīng)用6第六節(jié)Excel在相關(guān)與回歸分析中的應(yīng)用第一節(jié)Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用1一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用一、用Excel進(jìn)行分組和編制頻數(shù)分布表用Excel進(jìn)行統(tǒng)計(jì)分組和編制頻數(shù)分布表有兩種方法，一是函數(shù)法；二是利用數(shù)據(jù)分析中的“直方圖”工具。這里重點(diǎn)介紹利用“直方圖”工具進(jìn)行統(tǒng)計(jì)分組。直方圖分析工具是一個(gè)用于確定數(shù)據(jù)的頻數(shù)分布、累計(jì)頻數(shù)分布，并提供直方圖的分析模塊。它在給定工作表中數(shù)據(jù)單元格區(qū)域和接收區(qū)間的情況下，計(jì)算數(shù)據(jù)的頻數(shù)和累積頻數(shù)。具體使用方法如下。一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用【例9-1】某班50名學(xué)生的英語成績(jī)?nèi)缦拢嚲幹拼苏{(diào)查數(shù)據(jù)的頻數(shù)分布表。表9-1某班學(xué)生英語成績(jī)學(xué)號(hào)成績(jī)（分）學(xué)號(hào)成績(jī)（分）310178312675310289312784310392312877310466312966310580313090310652313173310777313278310896313384310963313466311082313559311180313663311275313780311348313890311474313974311563314066311671314181311788314270一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用操作步驟：1．在“工具”菜單中，單擊“數(shù)據(jù)分析”選項(xiàng)，彈出“數(shù)據(jù)分析”對(duì)話框，如圖9-1所示。圖9-1數(shù)據(jù)分析對(duì)話框一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用注意：如果用戶在Excel的“工具”菜單中沒有找到“數(shù)據(jù)分析”選項(xiàng)，說明用戶安裝的Excel不完整，必須在Excel中重新安裝“分析工具庫(kù)”內(nèi)容。具體安裝方法如下。①在“工具”菜單中，單擊“加載宏”選項(xiàng)。②選中“分析工具庫(kù)”和“分析工具庫(kù)—VBA函數(shù)”復(fù)選框，單擊“確定”按鈕，將會(huì)引導(dǎo)用戶進(jìn)行安裝，如圖9-2所示。圖9-2加載宏對(duì)話框一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用如果用戶在安裝Excel時(shí)選擇的是“典型安裝”，則需要使用CD－ROM進(jìn)行安裝，如果用戶在安裝Excel時(shí)選擇的是“完全安裝”，則Excel會(huì)從硬盤中直接進(jìn)行安裝。③安裝完畢后，“數(shù)據(jù)分析”選項(xiàng)會(huì)自動(dòng)出現(xiàn)在Excel的工具菜單中。一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用圖9-3直方圖對(duì)話框2．在“分析工具”列表框中，單擊“直方圖”分析工具，會(huì)彈出“直方圖”對(duì)話框，如圖9-3所示。一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用3．選擇輸入選項(xiàng)。輸入?yún)^(qū)域：在此輸入待分析數(shù)據(jù)區(qū)域的單元格引用。接收區(qū)域：表示分組標(biāo)志所在的區(qū)域，在此輸入接收區(qū)域的單元格引用。該區(qū)域應(yīng)包含一組可選的用來定義接收區(qū)間的邊界值，這些值應(yīng)當(dāng)按升序排列。在“輸入?yún)^(qū)域”中，輸入$B$10；$B$59；選好接收區(qū)域的內(nèi)容：$E$2：$E$7。一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用4．選擇輸出選項(xiàng)。輸出選項(xiàng)中可以選擇輸出區(qū)域、新工作表組或新工作簿。在這里選擇輸出區(qū)域，可以直接選擇一個(gè)區(qū)域，也可以直接輸出一個(gè)單元格，該單元格代表輸出區(qū)域的左上角，這里常常只輸入一個(gè)單元格。如本例中我們輸入$Ill，因?yàn)槲覀兺孪炔⒉恢谰唧w的輸出區(qū)域有多大。一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用輸出選項(xiàng)中還有以下選項(xiàng)：柏拉圖：選中此復(fù)選框，可以在輸出表中同時(shí)按降序排列頻率數(shù)據(jù)。如果此復(fù)選框被清除，Excel將只按升序來排列數(shù)據(jù)。累積百分比：選中此復(fù)選框，可以在輸出表中添加一列累積百分比數(shù)值，并同時(shí)在直方圖表中添加累積百分比折線。如果清除此選項(xiàng)，則會(huì)省略累積百分比。圖表輸出：選中此復(fù)選框，可以在輸出表中同時(shí)生成一個(gè)嵌入式直方圖表。本例中，我們選中“累積百分比”和“圖表輸出”兩個(gè)復(fù)選框。一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用5．單擊“確定”按鈕，可得輸出結(jié)果。如圖9-4所示。圖9-4頻數(shù)分布和直方圖一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用在默認(rèn)的直方圖中，柱形之間彼此分開，如果要將其連接起來，操作步驟如下：

（1）單擊某個(gè)柱形，單擊鼠標(biāo)右鍵，在彈出菜單中，選擇“數(shù)據(jù)系列格式”選項(xiàng)，彈出“數(shù)據(jù)系列格式”對(duì)話框，如圖9-5所示。注意：圖9-5數(shù)據(jù)系列格式對(duì)話框一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用（2）在對(duì)話框中選擇“選項(xiàng)”標(biāo)簽，將間距寬度由“150”改成“0”，點(diǎn)擊“依數(shù)據(jù)點(diǎn)分色”，再單擊“確定”按鈕，則得到直方圖如圖9-6所示。圖9-6調(diào)整后的直方圖一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用二、用Excel繪制統(tǒng)計(jì)圖利用Excel繪制統(tǒng)計(jì)圖比較方便，Excel提供了“圖表向?qū)А惫ぞ撸瑤椭鷦?chuàng)建適合于數(shù)據(jù)信息和分析目的的各種圖表。Excel提供的統(tǒng)計(jì)圖有多種，包括柱形圖、條形圖、折線圖、餅圖、散點(diǎn)圖、面積圖、環(huán)形圖等，各種圖形的繪制方法大同小異。圖表操作的基本步驟如下：1．使用“圖表向?qū)А惫ぞ咧谱鲌D表之前，選定數(shù)據(jù)所在的單元格，如果希望數(shù)據(jù)的行列標(biāo)題顯示在圖表中，則選定區(qū)域還應(yīng)包括含有標(biāo)題的單元格。一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用2．單擊“插入”菜單中的“圖表”選項(xiàng)，按照“圖表向?qū)А彼惺镜?個(gè)步驟的指令進(jìn)行操作。①“圖表類型”：每種類型的圖表都有兩種以上的子類型，選中所需要的圖表類形以及它的子圖形，單擊“下一步”按鈕。②“圖表數(shù)據(jù)源”：選定包含數(shù)據(jù)和行列標(biāo)志的工作表單元格，即便工作表包含多個(gè)行列標(biāo)志，也能在圖表中顯示它們。生成圖表時(shí)，應(yīng)將各行列標(biāo)志包含到選定區(qū)域中。若預(yù)覽圖表看上去很合適，則表示所選數(shù)據(jù)區(qū)正確。單擊“下一步”按鈕。③“圖表選項(xiàng)”：為選定的圖表設(shè)置某些標(biāo)準(zhǔn)選項(xiàng)。修改這些設(shè)置時(shí)，隨時(shí)查看預(yù)覽圖表可以幫助我們確定設(shè)置是否合適。單擊“下一步”按鈕。④“圖表位置”：可以將圖表放置在工作表上，或者單獨(dú)置于一張新的圖表工作表上。在這一步中，我們可以為圖表工作表命名，或者選擇現(xiàn)有工作表的名稱，單擊“完成”按鈕。一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用根據(jù)圖9-7廣告類型和從業(yè)人數(shù)的數(shù)據(jù)，制作拼圖?！纠?-2】圖9-7廣告從業(yè)數(shù)據(jù)一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用操作步驟：1．選中某一單元格，單擊“插入”菜單，選擇“圖表”選項(xiàng)，彈出“圖表向?qū)А睂?duì)話框。在圖表類型中選擇餅圖，然后在子圖表類型中選擇一種類型，這里選用系統(tǒng)默認(rèn)的方式。如圖9-8所示。圖9-8插入圖表對(duì)話框一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用2．單擊“確定”按鈕，即可得到簡(jiǎn)單的餅圖，如圖9-9所示。圖9-9簡(jiǎn)單餅圖3．選定圖表，單擊“布局”菜單，選取“圖表標(biāo)題”按鈕，如圖9-10所示。圖9-10輸入餅圖標(biāo)題對(duì)話框一

Excel在統(tǒng)計(jì)數(shù)據(jù)整理和顯示中的應(yīng)用4．輸入圖表的標(biāo)題，如本例中輸入“廣告從業(yè)人員數(shù)”，如圖9-11所示。圖9-11含標(biāo)題的餅圖5．單擊“設(shè)計(jì)”菜單，選取“圖表布局”某一按鈕，如圖9-12所示。圖9-12廣告從業(yè)人員數(shù)餅圖第二節(jié)Excel在描述統(tǒng)計(jì)量中的應(yīng)用2二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用一、平均指標(biāo)（一）算術(shù)平均值【例9-3】2005年3月20日10種股票的收盤價(jià)格如表9-2所示，試求該10種股票價(jià)格當(dāng)日的算術(shù)平均值。代碼證券名價(jià)格（元）代碼證券名價(jià)格（元）600000浦發(fā)銀行7.24600011華能國(guó)際6.97600004白云機(jī)場(chǎng)8.54600015華夏銀行4.05600006東風(fēng)汽車2.94600016民生銀行5.69600008首創(chuàng)股份8.20600018上港集箱16.37600009上海機(jī)場(chǎng)16.65600019寶鋼股份6.17表9-22005年3月20日10種股票收盤價(jià)二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用采用AVERAGE函數(shù)求算術(shù)平均值。除采用公式外，Excel還給出了AVERAGE函數(shù)來求算術(shù)平均值，具體操作步驟如下：1．建一工作表，輸入表頭“股票價(jià)格算術(shù)平均值”和表9-1中信息。2．單擊C13單元格，單擊插入函數(shù)按鈕，選擇[統(tǒng)計(jì)]/[AVERAGE]，Excel默認(rèn)對(duì)C3：C12單元格求算術(shù)平均值，按回車鍵即可。從圖9-13可以看出，10種股票的平均價(jià)格為8.282元。圖9-13采用AVERAGE函數(shù)求算術(shù)平均值二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用（二）幾何平均值【例9-4】上證180指數(shù)的2003年12月和2004年每月的指數(shù)，如表9-3所示，求2004年12個(gè)月指數(shù)的平均收益率。表9-3上證180指數(shù)13個(gè)月觀測(cè)值月份指數(shù)值月份指數(shù)值2003122828.802004072548.492004013019.042004082479.162004023140.482004092591.002004033213.482004102452.732004042912.812004112490.282004052819.492004122362.072004062528.44——二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用使用GEOMEAN函數(shù)求幾何平均值。Excel給出了GEOMEAN函數(shù)來求幾何平均值，具體操作步驟如下。1．在工作表中輸入已知信息，求出對(duì)應(yīng)12個(gè)月收益率r和12個(gè)月l+r的值，具體步驟參見公式求幾何平均數(shù)。2．在獲得12個(gè)月1+r后運(yùn)用GEOMEAN函數(shù)求幾何平均值。單擊Dl6單元格，單擊插入函數(shù)按鈕，選擇[統(tǒng)計(jì)]/[GEOMEAN]，在出現(xiàn)GEOMEAN函數(shù)的[函數(shù)參考]對(duì)話框，單擊[Num-berl]后的折疊按鈕，選擇D4：D15對(duì)應(yīng)的單元格，完成后單擊[確定]按鈕。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用3．單擊D16單元格，單擊編輯欄，在編輯GEOMEAN函數(shù)后輸入“－1”。從圖9-14可以看出，該段時(shí)間上證180指數(shù)的平均收益率為-1.491%。圖9-14使用GEOMEAN函數(shù)求幾何平均值二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用（三）眾數(shù)【例9-5】表9-4中給出了某公司員工工資的頻數(shù)分布，試確定員工工資的眾數(shù)。表9-4

某公司員工工資頻數(shù)分布工資（元）員工數(shù)（人）工資（元）員工數(shù)（人）2000～250083500～4000142500～3000104000～4500103000～3500164500～50003二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用利用公式求員工工資的眾數(shù)，具本操作步驟如下：1．新建工作表，輸入表頭“組數(shù)據(jù)的眾數(shù)”，輸入表9-4中已知信息。2．確定包含眾數(shù)組的下組限，對(duì)應(yīng)頻數(shù)最大組為“3000～3500”即是眾數(shù)組，對(duì)應(yīng)下組限為3000。3．確定眾數(shù)所在組的頻數(shù)減去前一組頻數(shù)，單擊B9單元格，在編輯欄輸入“=B5－B4”，按回車鍵；確定眾數(shù)所在組的頻數(shù)減去后一組的頻數(shù)，單擊B10單元格，在編輯欄輸入“=B5－B6”，按回車鍵。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用圖9-15某公司員工工資的眾數(shù)

4．根據(jù)公式求出眾數(shù)。單擊B11單元格，在編輯輸入“=3000+（B9/（B9+B10））*500”，完成后按回車鍵，最終結(jié)果如圖9-15所示。從圖9-15中可以看出，該公司員工工資的眾數(shù)為3375元。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用（四）中位數(shù)表9-5上證180指數(shù)數(shù)據(jù)【例9-6】表9-5中給出上證180指數(shù)某時(shí)間段的觀測(cè)值數(shù)目和累積頻數(shù)，試確定上證180指數(shù)此時(shí)間段內(nèi)的中位數(shù)。組號(hào)上證180觀測(cè)值數(shù)目累積頻數(shù)組號(hào)上證180觀測(cè)值數(shù)目累積頻數(shù)1≤24001162800～290092522400～25003472900～300032832500～26005983000～310033142600～270031293100～320033452700～2800416103200～3300236二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用具體操作步驟如下：1．新建工作表，輸入表頭“組數(shù)據(jù)的中位數(shù)”，輸入表9-5中已知信息。2．確定中位數(shù)的觀測(cè)值位置，為（觀測(cè)值總數(shù)+1）/2。單擊D12單元格，在編輯欄輸入“=（C11+1）/2”。3．按插值法確定指數(shù)的中位數(shù)，單擊D13單元格，在編輯欄輸入“=2800+（D12－C6）/D7*100”，按回車鍵。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用圖9-16組數(shù)據(jù)的中位數(shù)最終結(jié)果如圖9-16所示。

從圖9-16中可以看出，該段時(shí)間內(nèi)上證180指數(shù)的中位數(shù)為2827.778。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用（五）調(diào)和平均數(shù)【例9-7】求5，8，12，16，17，9，20的調(diào)和平均數(shù)。根據(jù)公式求調(diào)和平均數(shù)，具體操作步驟如下：1．新建工作表，輸入表頭“求調(diào)和平均數(shù)”輸入已知數(shù)據(jù)X。2．求1/X，單擊B3單元格，在編輯欄輸入“=1/A3”。單擊B3單元格，拖動(dòng)鼠標(biāo)至B9單元格，運(yùn)用自動(dòng)填充單元格求出所有的1/X。3．根據(jù)公式求調(diào)和平均數(shù)，單擊B10單元格，在編輯欄輸入“=7/SUM（B3：B9）”。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用圖9-17求調(diào)和平均數(shù)最終結(jié)果如圖9-17所示。從圖9-17中可以看出，對(duì)應(yīng)數(shù)據(jù)的調(diào)和平均數(shù)為10.13。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用二、標(biāo)志變異指標(biāo)（一）方差如果選用算術(shù)平均值作為度量集中趨勢(shì)的方法，則方差和標(biāo)準(zhǔn)差就是合適的離中趨勢(shì)度量指標(biāo)，方差和標(biāo)準(zhǔn)差被廣泛地應(yīng)用于金融領(lǐng)域中作為風(fēng)險(xiǎn)和不確定性的度量。標(biāo)志變異指標(biāo)較常用的方法有：方差和標(biāo)準(zhǔn)差。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用表9-6浦發(fā)銀行股價(jià)

【例9-8】表9-6中有浦發(fā)銀行（600000）10天的股票價(jià)格數(shù)據(jù)，試確定股價(jià)樣本的方差。日期價(jià)格（元）日期價(jià)格（元）200503077.65200503147.37200503087.78200503157.16200503097.57200503167.16200503107.4520050317741200503187.19二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用除了采用定義來求樣本的方差外，EXCEL還專門給出了樣本方差VAR來實(shí)現(xiàn)樣本方差的快速求解。采用VAR函數(shù)求股價(jià)樣本的方差，具體操作步驟如下：1．在工作表中，輸入股價(jià)信息。2．單擊B28單元格，單擊插入函數(shù)按鈕，選擇[統(tǒng)計(jì)]/[VAR]，在[函數(shù)參考]對(duì)話框，單擊[NUMBERI]中折疊按鈕，選擇BT8：B27對(duì)應(yīng)的單元格區(qū)域，完成后單擊[確定]按鈕。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用圖9-18使用VAR求樣本方差最終結(jié)果如圖9-18所示。從圖9-18中可以看出，浦發(fā)銀行股價(jià)樣本的方差為0.05220。二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用（二）標(biāo)準(zhǔn)差表9-6浦發(fā)銀行股價(jià)【例9-9】仍用表9-6資料確定股價(jià)樣本的標(biāo)準(zhǔn)差。日期價(jià)格（元）日期價(jià)格（元）200503077.65200503147.37200503087.78200503157.16200503097.57200503167.16200503107.4520050317741200503187.19二

Excel在描述統(tǒng)計(jì)量中的應(yīng)用圖9-19未分組數(shù)據(jù)的標(biāo)準(zhǔn)差采用STDEV函數(shù)求樣本的標(biāo)準(zhǔn)差，具體操作步驟如下：1．建工作表，輸入表9-6中日期和價(jià)格。2．求樣本的標(biāo)準(zhǔn)差，單擊B13單元格，在編輯欄輸入“=STDEV（B3：B12）”，按回車鍵。從圖9-19中可以看出，浦發(fā)銀行股價(jià)的標(biāo)準(zhǔn)差為0.2284。第三節(jié)Excel在時(shí)間序列分析中的應(yīng)用3三Excel在時(shí)間序列分析中的應(yīng)用一、測(cè)定增長(zhǎng)量和平均增長(zhǎng)量圖9-20我國(guó)1998～2007年國(guó)內(nèi)生產(chǎn)總值【例9-10】根據(jù)我國(guó)1998～2007年國(guó)內(nèi)生產(chǎn)總值，計(jì)算逐期增長(zhǎng)量、累計(jì)增長(zhǎng)量和平均增長(zhǎng)量。原始數(shù)據(jù)如圖9-20。三Excel在時(shí)間序列分析中的應(yīng)用圖9-21計(jì)算逐期增長(zhǎng)量、累計(jì)增長(zhǎng)量和平均增長(zhǎng)量的結(jié)果計(jì)算步驟如下。1．計(jì)算逐期增長(zhǎng)量。在C3中輸入公式：=B3－B2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C11區(qū)域。2．計(jì)算累計(jì)增長(zhǎng)量。在D3中輸入公式：=B3－$B$2，并用鼠標(biāo)拖曳公式復(fù)制到D3：D11區(qū)域。3．計(jì)算平均增長(zhǎng)量（水平法）。在C13中輸入公式：=（B11－B2）/9，按回車鍵，即可得到平均增長(zhǎng)量。見圖9-21三Excel在時(shí)間序列分析中的應(yīng)用二、測(cè)定發(fā)展速度和平均發(fā)展速度【例9-11】以【例9-10】中我國(guó)1998～2007年國(guó)內(nèi)生產(chǎn)總值為例，說明如何計(jì)算定基發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速。1．計(jì)算定基發(fā)展速度。在C3中輸入公式：=B3/$B$2，并用鼠標(biāo)拖曳將公式復(fù)制到C3：C11區(qū)域。2．計(jì)算環(huán)比發(fā)展速度。在D3中輸入公式：=B3/B2，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D11區(qū)域。3．計(jì)算平均發(fā)展速度（水平法）。選中D13單元格，單擊插入菜單，選擇函數(shù)選項(xiàng)，出現(xiàn)插入函數(shù)對(duì)話框后，選擇GEOMEAN（返回幾何平均值）函數(shù)，在數(shù)值區(qū)域中輸入D3：D11即可。三Excel在時(shí)間序列分析中的應(yīng)用圖9-22計(jì)算發(fā)展速度和平均發(fā)展速度結(jié)果三Excel在時(shí)間序列分析中的應(yīng)用三、計(jì)算長(zhǎng)期趨勢(shì)【例9-12】我們用我國(guó)1988～2007年國(guó)內(nèi)生產(chǎn)總值資料來說明如何用移動(dòng)平均法計(jì)算長(zhǎng)期趨勢(shì)。1．計(jì)算三項(xiàng)移動(dòng)平均。在C3中輸入“（B2+B3+B4）/3”，并用鼠標(biāo)拖曳將公式復(fù)制到C4：C20區(qū)域。2．計(jì)算四項(xiàng)移動(dòng)平均。在D4中輸入“=SUM（B2：B5）/4”，并用鼠標(biāo)拖曳將公式復(fù)制到D5：D20區(qū)域。3．計(jì)算二項(xiàng)移動(dòng)平均：在E4中輸入“（D4+D5）/2”，并用鼠標(biāo)拖曳將公式復(fù)制到E5：E19區(qū)域。

結(jié)果見圖9-23三Excel在時(shí)間序列分析中的應(yīng)用圖9-23計(jì)算長(zhǎng)期趨勢(shì)資料及結(jié)果三

Excel在時(shí)間序列分析中的應(yīng)用四、計(jì)算季節(jié)變動(dòng)圖9-24計(jì)算季節(jié)變動(dòng)資料

【例9-13】利用某種商品四年分季度的銷售額資料，說明如何用移動(dòng)平均趨勢(shì)剔除法測(cè)定季節(jié)變動(dòng)。如圖9-24所示。三

Excel在時(shí)間序列分析中的應(yīng)用1．按圖上的格式在A列輸入年份，在B列輸入季別，在C列輸入銷售額。2．計(jì)算四項(xiàng)移動(dòng)平均。在D3中輸入“=SUM（C2：C4）/4”，并用鼠標(biāo)拖曳將公式復(fù)制到D3：D15區(qū)域。3．計(jì)算趨勢(shì)值（即二項(xiàng)移動(dòng)平均）T。在E4中輸入“=（D3+D4）/2”，并用鼠標(biāo)拖曳將公式復(fù)制到E4：E15區(qū)域。4．剔除長(zhǎng)期趨勢(shì)，即計(jì)算Y/T。在F4中輸入“=C4/E4”，并用鼠標(biāo)拖曳將公式復(fù)制到F4：F15區(qū)域。5．重新排列F4：F15區(qū)域中的數(shù)字，使同季的數(shù)字位于一列，共排成四列。6．計(jì)算各年同季平均數(shù)。在B6單元格中輸入公式：=average（B3：B5）；在C6中輸入公式=average（C3：C5）；在D6中輸入公式=average（D2：D4）；在E6中輸入公式=average（E2：E4）。三

Excel在時(shí)間序列分析中的應(yīng)用圖9-25計(jì)算季節(jié)變動(dòng)結(jié)果7．計(jì)算調(diào)整系數(shù)。在B8中輸入公式：=4/sum（B6：E6）8．計(jì)算季節(jié)比率。在B7中輸入公式：=B6*$B$8，并用鼠標(biāo)拖曳將公式復(fù)制到單元格區(qū)域B7：E7，就可以得到季節(jié)比率的值，具體結(jié)果見圖9-25。第四節(jié)Excel在指數(shù)分析中的應(yīng)用4四

Excel在指數(shù)分析中的應(yīng)用指數(shù)分析是利用指標(biāo)間形成的指標(biāo)體系，在編制指數(shù)的基礎(chǔ)上進(jìn)行的因素分析。從數(shù)量上分析研究對(duì)象的變動(dòng)，分別受各因素影響的方向、程度及絕對(duì)的數(shù)量。例如，在分析工人平均工資變動(dòng)時(shí)，要分析有多大程度是受各工資級(jí)別的人數(shù)比重變化的影響，有多大程度取決于各工資級(jí)別的工資平均變動(dòng)的影響。利用Excel工具表和各個(gè)指標(biāo)的分解形式，可以分析經(jīng)濟(jì)現(xiàn)象的指數(shù)體系，以及在此基礎(chǔ)上進(jìn)行因素分析。本節(jié)主要討論利用Excel計(jì)算綜合指數(shù)和平均指數(shù)，并進(jìn)行因素分析。四

Excel在指數(shù)分析中的應(yīng)用一、綜合指數(shù)的計(jì)算表9-74種產(chǎn)品的產(chǎn)量和出廠價(jià)格【例9-14】某工廠生產(chǎn)的4種產(chǎn)品的產(chǎn)量和出廠價(jià)格如表9-7所示。產(chǎn)品名稱單位產(chǎn)量?jī)r(jià)格基期報(bào)告期基期報(bào)告期甲臺(tái)202177乙噸80884241丙件32301012丁套586089四

Excel在指數(shù)分析中的應(yīng)用圖9-26輸入基期、報(bào)告期的產(chǎn)量和價(jià)格1．將數(shù)據(jù)輸入工作表中，如圖9-26所示。四

Excel在指數(shù)分析中的應(yīng)用圖9-27計(jì)算期、報(bào)告期產(chǎn)量和出廠價(jià)格和乘職2．在F2單元格中輸入“=B2*D2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至F5，得出基期產(chǎn)量和基期價(jià)格的乘積；在G2中輸入“=C2*D2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至G5，得出報(bào)告期產(chǎn)量和基期價(jià)格的乘積；在H2中輸入“=C2*E2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至H5，得出報(bào)告期產(chǎn)量和報(bào)告期價(jià)格的乘積。結(jié)果分別如圖9-27第F列、G列、H列所示。四

Excel在指數(shù)分析中的應(yīng)用3．光標(biāo)移至F6單元格，點(diǎn)擊工具欄上的自動(dòng)求和符號(hào)，按Enter確認(rèn)，得出基期產(chǎn)量和基期價(jià)格乘積的總和。在G6和H6單元格中進(jìn)行相同操作，可以分別得出基期價(jià)格和報(bào)告期產(chǎn)量乘積的總和以及報(bào)告期產(chǎn)量和報(bào)告期價(jià)格乘積的總和。四

Excel在指數(shù)分析中的應(yīng)用圖9-28綜合指數(shù)計(jì)算結(jié)果4．在G7單元格中輸入“=G6/F6"，即可得出產(chǎn)量綜合指數(shù)為1.0791，即該工廠全部工業(yè)產(chǎn)品產(chǎn)量綜合增長(zhǎng)了7.91%；在H7中輸入“=H6/G6”，即可得出價(jià)格綜合指數(shù)為1.0069，即該工廠全部工業(yè)產(chǎn)品價(jià)格綜合上漲0.69%。結(jié)果如圖9-28。四

Excel在指數(shù)分析中的應(yīng)用二、平均指數(shù)的計(jì)算平均指數(shù)法有加權(quán)算術(shù)平均法、加權(quán)調(diào)和平均法和固定權(quán)數(shù)加權(quán)平均法。本節(jié)主要介紹利用Excel求加權(quán)算術(shù)平均指數(shù)?！纠?-15】以【例9-14】的資料為例，利用加權(quán)算術(shù)平均法計(jì)算該工廠的銷售量指數(shù)。1．在F2單元格中輸入“=B2*D2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至F5，得出基期產(chǎn)量和基期價(jià)格的乘積。2．求出個(gè)體數(shù)量指標(biāo)指數(shù)，在G2中輸入“=C2/B2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至G5，得出個(gè)體數(shù)量指標(biāo)指數(shù)。3．在H2中輸入“=G2*F2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至H5，得出工廠基期銷售額與個(gè)體銷售指數(shù)的乘積。4．分別對(duì)F列和H列加總求和。四

Excel在指數(shù)分析中的應(yīng)用圖9-29計(jì)算銷售量指數(shù)5．在H7中輸入“=H6/F6"，即可得到銷售量指數(shù)。由表中可以看出與用綜合指數(shù)法算得的結(jié)果相同。具體的操作結(jié)果見圖9-29。四

Excel在指數(shù)分析中的應(yīng)用三、因素分析（一）總量指標(biāo)的因素分析表9-8某工廠2006年生產(chǎn)情況對(duì)于總量指標(biāo)的因素分析，本部分只介紹總量指標(biāo)變動(dòng)的兩因素分析?！纠?-16】某工廠2006年生產(chǎn)情況如表9-8所示。對(duì)生產(chǎn)費(fèi)用的變動(dòng)進(jìn)行因素分析。產(chǎn)品名稱單位產(chǎn)量單位成品成本/元基期報(bào)告期基期報(bào)告期甲臺(tái)160020001200900乙噸606045004000丙件40425500050000四

Excel在指數(shù)分析中的應(yīng)用圖9-30輸入基期、報(bào)告期的產(chǎn)量和成本1．把數(shù)據(jù)輸入工作表中，如圖9-30所示。四

Excel在指數(shù)分析中的應(yīng)用圖9-31計(jì)算基期、報(bào)告期產(chǎn)量和成本的乘積2．在F2單元格中輸入“=B2*D2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至F4，得出基期產(chǎn)量和基期成本的乘積；在G2中輸入“=C2*D2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至G4，得出報(bào)告期產(chǎn)量和基期成本的乘積；在H2中輸入“=C2*E2”，按Enter確認(rèn)，然后拖動(dòng)鼠標(biāo)至H4，得出報(bào)告期產(chǎn)量和報(bào)告期成本的乘積。操作如圖9-31所示。四

Excel在指數(shù)分析中的應(yīng)用3．分別對(duì)F列、G列、H列加總求和。4．在F6中輸入“=H5/F5”，得出生產(chǎn)支出總額指數(shù)為94.3%，F(xiàn)7中輸入“=H5－F5”，得出結(jié)果為－25萬元。說明該工廠生產(chǎn)支出總額報(bào)告期比基期下降了5.7%，絕對(duì)額減少了25萬元。在G6中輸入“=H5/G5”，得出產(chǎn)品成本指數(shù)為83.13%，在G7中輸入“=H5－G5”，得出結(jié)果為－84萬元。說明該工廠單位產(chǎn)品成本水平下降了16.87%。從而節(jié)約了生產(chǎn)費(fèi)用84萬元。在H6中輸入“=G5/F5”，得出產(chǎn)品產(chǎn)量指數(shù)為113.4%，在H7中輸入“=G5－F5”，得到結(jié)果為59萬元，說明該工廠由于產(chǎn)品產(chǎn)量增加了13.4%，使得支出總額增加了59萬元。具體結(jié)果如圖9-32所示。四

Excel在指數(shù)分析中的應(yīng)用圖9-32計(jì)算生產(chǎn)支出總額指數(shù)、產(chǎn)品成本指數(shù)、產(chǎn)品產(chǎn)量指數(shù)四

Excel在指數(shù)分析中的應(yīng)用（二）平均指標(biāo)變動(dòng)的因素分析表9-9某工廠職工的工資資料對(duì)于平均指標(biāo)變動(dòng)的因素分析，舉例加以說明。

【例9-17】某工廠職工的工資資料如表9-9所示。分析職工工資水平和工人結(jié)構(gòu)變動(dòng)對(duì)平均工資的影響。

月工資/元工人數(shù)/人基期報(bào)告期基期報(bào)告期技術(shù)工人580620245250輔助工人400420120800四

Excel在指數(shù)分析中的應(yīng)用圖9-33輸入基期、報(bào)告期的月工資和工人數(shù)1．在工作中表輸入數(shù)據(jù)，輸入結(jié)果如圖9-33所示。四

Excel在指數(shù)分析中的應(yīng)用圖9-34計(jì)算基期、報(bào)告期月工資和工人數(shù)的乘職2．在F2中輸入“=B2*D2”，拖動(dòng)鼠標(biāo)至F3；在G2中輸入“=B2*E2”，拖動(dòng)鼠標(biāo)至G3；在H2中輸入“=C2*E2”拖動(dòng)鼠標(biāo)至H3。3．分別對(duì)D列、E列、F列、G列、H列加總求和。結(jié)果如圖9-34所示。四

Excel在指數(shù)分析中的應(yīng)用4．在F5中輸入公式“=（H4/E4）/（F4/D4）”，得出工資平均指數(shù)為89.78%；在F6中輸入“=（H4/E4）－（F4/D4）”，得出結(jié)果為－53.2元。說明該工廠職工平均工資報(bào)告期比基期下降了10.21%，絕對(duì)額減少了53.2元。在G5中輸入“=（G4/E4）/（F4/D4）”，得出結(jié)構(gòu)影響指數(shù)為85.03%；在G6中輸入“=（G4/E4）－（F4/D4）”，得到結(jié)果為－77.96元。說明在該工廠職工工資水平不變的情況下，由于職工人數(shù)結(jié)構(gòu)發(fā)生變化使得平均工資減少了77.96元。在H5中輸入“=（H4/E4）/（G4/E4）”，得出固定構(gòu)成指數(shù)為105.59%；在H6中輸入“（H4/E4）－（G4/E4）”，得到結(jié)果為24.76元。說明在人數(shù)構(gòu)成不變的情況下，由于職工工資水平變化使得工資增加了24.76元。具體結(jié)果如圖9-35所示。四

Excel在指數(shù)分析中的應(yīng)用圖9-35計(jì)算工資平均指數(shù)、結(jié)構(gòu)影響指數(shù)和固定構(gòu)成指數(shù)第五節(jié)Excel在抽樣估計(jì)中的應(yīng)用5五Excel在抽樣估計(jì)中的應(yīng)用Excel中一個(gè)總體均值、總體方差和總體比例的抽樣估計(jì)可以用使用公式和函數(shù)或數(shù)據(jù)分析工具進(jìn)行，兩個(gè)總體均值之差也可按類似方法進(jìn)行，下面舉例說明。【例9-18】某飯店在7周內(nèi)抽查49位顧客的消費(fèi)額（元）如表9-10所示，估計(jì)概率保證程度為90%時(shí)顧客平均消費(fèi)額的區(qū)間。表9-1049位顧客的消費(fèi)額數(shù)據(jù)15243826304218302526344420352426344818284619303642243245362147262831424536242827323647532224324626

五Excel在抽樣估計(jì)中的應(yīng)用圖9-36顧客的消費(fèi)額數(shù)據(jù)1．把數(shù)據(jù)輸入到A2：A50單元格，如圖9-36所示五Excel在抽樣估計(jì)中的應(yīng)用圖9-37顧客的消費(fèi)額區(qū)間估計(jì)結(jié)果2．在C2中輸入公式“=COUNT（A2：A50）”，C3中輸入“=AVERAGE（A2：A50）”，在C4中輸入“STDEV（A2：A50）”，在C5中輸入“=C4/SQRT（C2）”，在C6中輸入0.90，在C7中輸入“=C2－1”，在C8中輸入，“=TINY（1-C6，C7）”，在C9中輸入“=C8*C5”，在C10中輸入“=C3－C9”，在C11中輸入“=C3+C9”。在輸入每一個(gè)公式按回車鍵后，便可得到上面的結(jié)果。從上面的結(jié)果可以知道，顧客平均消費(fèi)額的置信下限為29.73536，置信上限為34.26464，如圖9-37所示。五Excel在抽樣估計(jì)中的應(yīng)用二、數(shù)據(jù)分析工具圖9-36顧客的消費(fèi)額數(shù)據(jù)1．把數(shù)據(jù)輸入到A2：A50單元格，如圖9-36所示。五Excel在抽樣估計(jì)中的應(yīng)用圖9-38描述統(tǒng)計(jì)對(duì)話框（置信度90%）2．選擇“工具”下拉菜單，單擊“數(shù)據(jù)分析”，在分析工具中選擇“描述統(tǒng)計(jì)”，在“輸入?yún)^(qū)域”鍵入“A2：A50”，在“輸出區(qū)域”鍵入“B1”，選擇“匯總統(tǒng)計(jì)”，平均數(shù)置信度選擇“90%”，如圖9-38所示。五Excel在抽樣估計(jì)中的應(yīng)用圖9-39描述統(tǒng)計(jì)結(jié)果（置信度90%）3．選擇“確定”，得到輸出結(jié)果，如圖9-39所示。五Excel在抽樣估計(jì)中的應(yīng)用圖9-40顧客的消費(fèi)額區(qū)間估計(jì)結(jié)果4．選擇單元格D2（置信區(qū)間下限），鍵入單元格公式“=C3－C18”，選擇單元格D3（置信區(qū)間上限），鍵入單元格公式“=C3+C18”，C3是樣本均值，C18是誤差范圍（極限誤差）。結(jié)果如圖9-40所示。第六節(jié)Excel在相關(guān)與回歸

分析中的應(yīng)用6六

Excel在相關(guān)與回歸分析中的應(yīng)用一、相關(guān)圖的繪制表9-118個(gè)同類企業(yè)產(chǎn)品產(chǎn)量和生產(chǎn)費(fèi)用的相關(guān)表【例9-19】根據(jù)表9-11相關(guān)數(shù)據(jù)，繪制相關(guān)圖。企業(yè)編號(hào)月產(chǎn)品產(chǎn)量（千噸）月生產(chǎn)費(fèi)用（萬元）12.412424.117036.016247.5200510.0225612.4265714.1280816.2310六

Excel在相關(guān)與回歸分析中的應(yīng)用圖9-418個(gè)同類企業(yè)產(chǎn)品產(chǎn)量和生產(chǎn)費(fèi)用數(shù)據(jù)制作相關(guān)圖的步驟如下：1．建立工作表，輸入表9-11相關(guān)數(shù)據(jù)，選擇區(qū)域Al：B9，如圖9-41所示。六

Excel在相關(guān)與回歸分析中的應(yīng)用圖9-42散點(diǎn)圖類型的選擇

2．點(diǎn)擊“插入圖表”中選擇“XY散點(diǎn)圖”；如附圖9-42所示。在“子圖表類型”中選擇第一種散點(diǎn)圖，并點(diǎn)擊“確定”。六

Excel在相關(guān)與回歸分析中的應(yīng)用圖9-43月產(chǎn)品產(chǎn)量與生產(chǎn)費(fèi)用的散點(diǎn)圖

3．點(diǎn)擊“確定”，并對(duì)圖形進(jìn)行修飾編輯，得到圖9-43所示月產(chǎn)品產(chǎn)量與生產(chǎn)費(fèi)用之間的散點(diǎn)圖。六

Excel在相關(guān)與回歸分析中的應(yīng)用圖9-44設(shè)置趨勢(shì)線格式選擇圖

4．在散點(diǎn)圖的任意一個(gè)點(diǎn)上按右鍵—添加趨勢(shì)線—類型，選與散點(diǎn)圖近似的趨勢(shì)線如圖9-44所示。六

Excel在相關(guān)與回歸分析中的應(yīng)用圖9-45月產(chǎn)品產(chǎn)量與生產(chǎn)費(fèi)用的趨勢(shì)線圖

5．選擇選項(xiàng)中“?“”顯示公式和“?”顯示R平方值，點(diǎn)擊確定，最后得到如圖9-45所示的趨勢(shì)線圖、回歸方程和R平方值。六

Excel在相關(guān)與回歸分析中的應(yīng)用二、相關(guān)系數(shù)在Excel中，相關(guān)系數(shù)函數(shù)和相關(guān)系數(shù)宏提供了兩種計(jì)算相關(guān)系數(shù)的方法。1.相關(guān)系數(shù)函數(shù)。在Excel中，CORBEL函數(shù)和PERSON函數(shù)提供了計(jì)算兩個(gè)變量之間的相關(guān)系數(shù)的方法，這兩個(gè)函數(shù)是等價(jià)的。與相關(guān)系數(shù)有關(guān)的函數(shù)還有RSQ（相關(guān)系數(shù)的平方，即判定系數(shù)）和COVAR（協(xié)方差函數(shù)）。六

Excel在相關(guān)與回歸分析中的應(yīng)用【例9-20】以表9-11中資料為例，利用函數(shù)CORBEL函數(shù)計(jì)算相關(guān)系數(shù)。表9-118個(gè)同類企業(yè)產(chǎn)品產(chǎn)量和生產(chǎn)費(fèi)用的相關(guān)表企業(yè)編號(hào)月產(chǎn)品產(chǎn)量（千噸）月生產(chǎn)費(fèi)用（萬元）12.412424.117036.016247.5200510.0225612.4265714.1280816.2310六

Excel在相關(guān)與回歸分析中的應(yīng)用圖9-46CORBEL函數(shù)計(jì)算相關(guān)系數(shù)1．點(diǎn)擊Excel函數(shù)圖鈕“”，選擇“統(tǒng)計(jì)”函數(shù)。2．在統(tǒng)計(jì)函數(shù)點(diǎn)擊“CORBEL”，進(jìn)入函數(shù)向?qū)А?．在“Array1”中輸入第一個(gè)變量“月產(chǎn)品產(chǎn)量”的數(shù)據(jù)區(qū)域A2：A9。在“Array2”中輸入第二個(gè)變量“生產(chǎn)費(fèi)用”的數(shù)據(jù)區(qū)域B