實驗五用EXCEL進行時間序列分析

1、實驗五 用EXCEL進行時間序列分析一、實驗目的² 利用Excel進行時間序列分析二、實驗內(nèi)容1測定發(fā)展水平和平均發(fā)展水平2. 測定增長量和平均增長量3. 測定發(fā)展速度、增長速度和平均發(fā)展速度4. 計算長期趨勢5. 計算季節(jié)變動三、實驗指導時間序列分析常用的方法有兩種：指標分析法和構成因素分析法。指標分析法，通過計算一系列時間序列分析指標，包含發(fā)展水平、平均發(fā)展水平、增長量、平均增長量、發(fā)展速度、平均發(fā)展速度等來揭示現(xiàn)象的發(fā)展狀況和發(fā)展變化程度。構成因素分析法，是將時間序列看做由長期趨勢、季節(jié)變動、循環(huán)變動、不規(guī)則變動四種因素構成，將各影響因素分別從時間序列中分離出去并加以測定、對未

2、來發(fā)展做出預測的過程。 發(fā)展水平： 發(fā)展水平是指某一經(jīng)濟現(xiàn)象在各個時期達到的實際水平。在時間序列中，各指標數(shù)值就是該指標所反映的社會經(jīng)濟現(xiàn)象在所屬時間的發(fā)展水平。在時間序列中，我們用y表示指標值，t表示時間，則(t=0,1,2,3,n)表示各個時期的指標值。平均發(fā)展水平：平均發(fā)展水平又稱“序時平均數(shù)”、“動態(tài)平均數(shù)”，是時間序列中各項發(fā)展水平的平均數(shù)，反映現(xiàn)象在一段時期中發(fā)展的一般水平。 增長量： 增長量是指某一經(jīng)濟現(xiàn)象在一定時期增長或減少的絕對量。它是報告期發(fā)展水平減基期發(fā)展水平之差。 平均增長量：平均增長量是時間序列中的逐期增長量的序時平均數(shù)，它表明現(xiàn)象在一定時段內(nèi)平均每期增加（減少）的數(shù)

3、量。公式表示如下： 發(fā)展速度：發(fā)展速度是說明事物發(fā)展快慢程度的動態(tài)相對數(shù)。它等于報告期水平對基期水平之比。發(fā)展速度有兩種：分為環(huán)比發(fā)展速度和定基發(fā)展速度。 1環(huán)比發(fā)展速度：也稱逐期發(fā)展速度，是報告期發(fā)展水平與前一期發(fā)展水平之比。 2定基發(fā)展速度：是報告期水平與固定基期水平之比。 平均發(fā)展速度：平均發(fā)展速度是動態(tài)數(shù)列中各期環(huán)比發(fā)展速度或各期定基發(fā)展速度中的環(huán)比發(fā)展速度的序時平均數(shù)。它說明在一定時期內(nèi)發(fā)展速度的一般水平。平均發(fā)展速度的計算方法有幾何法和方程法。1幾何法計算平均發(fā)展速度：實際動態(tài)數(shù)列各期環(huán)比發(fā)展速度連乘積等于理論動態(tài)數(shù)列中各期平均發(fā)展速度的連乘積2方程法計算平均發(fā)展速度：方程法平均發(fā)

4、展速度的特點是實際動態(tài)數(shù)列各項之和等于理論動態(tài)數(shù)列各項之和，所以稱為“累積法”（1）測定發(fā)展水平和平均發(fā)展水平在時間上的觀察值，就是該時間點的發(fā)展水平。平均發(fā)展水平是現(xiàn)象在時間（i=1,2,n）上各期觀察值的平均數(shù)。時期序列的序時平均數(shù)計算打開工作簿“實驗5.1.xls”，利用“全國GDP”工作表中的數(shù)據(jù)，計算年度平均國內(nèi)生產(chǎn)總值，計算結果如圖5.1.1所示： 圖5.1.1 用EXCEL計算平均發(fā)展水平資料及結果計算步驟：第一步：計算觀察值個數(shù)n：在B14中輸入公式“=count(A2:A13)”,得到觀察值個數(shù)為12。第二步：計算總國內(nèi)生產(chǎn)總值：在B15中輸入公式“=sum（A2:A13)”

5、，得到總生產(chǎn)總值為2821530.71。第三步：計算平均國內(nèi)生產(chǎn)總值：在B16中輸入公式“=B15/B14”，得到平均國內(nèi)生產(chǎn)總值。時點序列的序時平均數(shù)計算連續(xù)時點序列序時平均數(shù)：打開工作簿“實驗5.1.xls”，利用“庫存量”工作表中的數(shù)據(jù)，計算12月份平均日庫存量，計算結果如圖5.1.2所示： 圖5.1.2 用EXCEL計算平均發(fā)展水平資料及結果計算步驟：第一步：計算：B4、C4、D4、E4、F4中輸入各日期天數(shù)T，在G4中輸入公式“=SUM(B4:F4)”，得到值為31天。第二步：計算：在B5中輸入公式“=B3*B4”, 得到日期14的值240件，C5中輸入公式“=C3*C4”，得到日期

6、510的值378件，D5中輸入公式“=D3*D4”，得到日期1120的值500件，E5中輸入公式“=E3*E4”，得到日期2126的值270件，F(xiàn)5中輸入公式“=F3*F4”，得到日期2731的值200件。在G5中輸入公式“=SUM(B5:F5)” 可得值1588。第三步：計算平均日庫存量：在B7中輸入公式“=G5/G4，得到平均日庫存量44件。間斷時點序列序時平均數(shù)：打開工作簿“實驗5.1.xls”，利用“存款余額”工作表中的數(shù)據(jù)，計算平均存款余額，計算結果如圖5.1.3所示： 圖5.1.3 用EXCEL計算平均發(fā)展水平資料及結果計算步驟：第一步：計算兩個時間點的平均觀察值：在C5中輸入公式

7、“=average(B3:C3)”, 并用鼠標拖曳將公式復制到D5：H5區(qū)域。第二步：算出間隔天數(shù)：t1、t2、t3、t4、t5。第三步：計算值：在C9中輸入公式“=C5*C7”，并用鼠標拖曳將公式復制到D9：H9區(qū)域。第四步：計算總天數(shù)：在B10輸入公式：“= SUM(C7:H7)”，得到總天數(shù)365天。第五步：計算平均存款額：在B11輸入公式：“=SUM(C9:H9)/SUM(C7:H7)”，得到平均存款額為120.03百萬元。（2）測定增長量和平均增長量增長量是報告期水平與基期水平之差，其中逐期增長量：；累積增長量：。打開工作簿“實驗5.2.xls”，利用“全國GDP”工作表中的數(shù)據(jù)，計

8、算逐期增長量、累積增長量和平均增長量，計算結果如圖5.2所示： 圖5.2 用EXCEL計算增長量和平均增長量資料及結果計算步驟：第一步：計算逐期增長量：在C3中輸入公式“=B3-B2”，并用鼠標拖曳將公式復制到C4：C13區(qū)域。第二步：計算累計增長量：在D3中輸入公式“=B3-$B$2”，并用鼠標拖曳公式復制到D4：D13區(qū)域。第三步：計算平均增長量（水平法）：在B15中輸入公式“=D13/11”，按回車鍵，即可得到平均增長量。（3）測定發(fā)展速度、增長速度和平均發(fā)展速度發(fā)展速度是同一現(xiàn)象在兩個不同時期發(fā)展水平對比的結果，用于描述現(xiàn)象在觀察期內(nèi)的相對發(fā)展變化程度。發(fā)展速度=報告期水平/基期水平*

9、100%。其中定基發(fā)展速度：；環(huán)比發(fā)展速度：增長速度又稱增長率，是增長量和基期水平之比。增長速度=增減量/基期水平=（報告期水平-基期水平）/基期水平=發(fā)展速度-1。其中定基增長速度：；環(huán)比增長速度：。平均發(fā)展速度是現(xiàn)象各個時期環(huán)比發(fā)展速度的平均數(shù)，用于描述現(xiàn)象在整個觀察期內(nèi)平均發(fā)展變化的程度。水平法的計算公式：平均增長速度=平均發(fā)展速度-1。打開工作簿“實驗5.3.xls”，利用“全國GDP”工作表中的數(shù)據(jù)，計算定基發(fā)展速度、環(huán)比發(fā)展速度和平均發(fā)展速度，計算結果如圖5.3所示： 圖5.3 用EXCEL計算發(fā)展速度、增長速度和平均發(fā)展速度資料及結果計算步驟：第一步：計算定基發(fā)展速度：在C3中輸

10、入公式“=B3/$B$2”，并用鼠標拖曳將公式復制到C4：C13區(qū)域。第二步：計算環(huán)比發(fā)展速度：在D3中輸入公式“=B3/B2”，并用鼠標拖曳將公式復制到D4：D13區(qū)域。第三步：計算定基增長速度：在E3中輸入公式“=C3-1”，并用鼠標拖曳將公式復制到D4：D13區(qū)域。第四步：計算環(huán)比增長速度：在F3中輸入公式“=D3-1”，并用鼠標拖曳將公式復制到D4：D13區(qū)域。第五步：計算平均發(fā)展速度（水平法）：在B15中輸入公式“= GEOMEAN（D3：D13）” 按回車鍵，即可得到平均發(fā)展速度。第六步：計算平均增長速度（水平法）：在B16中輸入公式“=B15-1”，按回車鍵，即可得到平均增長速度

11、。（4）計算長期趨勢趨勢分析方法有許多，這里介紹移動平均法和指數(shù)平滑法，主要通過對時間序列進行平滑以消除其隨機波動。 移動平均法，是通過對時間序列逐期遞移求得平均數(shù)作為趨勢值或預測值的一種平滑法。設移動間隔長度為K，則移動平均序列寫成：。指數(shù)平滑法，是用過去的觀察值的加權平均數(shù)作為趨勢值，它是加權平均的一種特殊形式?？梢岳煤瘮?shù)和公式法，或者使用工具進行分析。函數(shù)和公式法打開工作簿“實驗5.4.xls”，利用“月度數(shù)據(jù)”工作表中的數(shù)據(jù)，分別用移動平均法（K=3和K=4）和一次指數(shù)平滑法(=0.2，一月份預測值為286331315)計算2012年全國進出口總額的長期趨勢，結果如圖5.4.1所示。

12、圖5.4.1 用EXCEL計算長期趨勢資料及結果計算步驟：第一步：計算三項移動平均：在C3中輸入“=average（B2：B4）”，并用鼠標拖曳將公式復制到C3：C12區(qū)域。第二步：計算四項移動平均：在D4中輸入“=average（B2：B5）”，并用鼠標拖曳將公式復制到D4：D12區(qū)域。第三步：計算二項移正平均數(shù)：在E4中輸入“=average（D4：D5）”，并用公式拖曳將公式復制到E4：E11區(qū)域。第四步：計算一次指數(shù)平滑預測數(shù)：在F2中輸入286331315，在F3中輸入“=0.2*B2+(1-0.2)*F2”，并用鼠標拖曳將公式復制到F4：F14區(qū)域。F14為未來下一個月的預測值。工

13、具分析法移動平平均法：單擊“工具”|“數(shù)據(jù)分析”，打開數(shù)據(jù)分析對話框，如圖5.4.2(a)所示。選擇“移動平均”，彈出“移動平均”對話框，在“輸入?yún)^(qū)域”中輸入“B2：B13”，在“間隔”輸入“3”，在“輸出區(qū)域”輸入“C15”，如圖5.4.2（b）。單擊確定就可以得到n=3的移動平均值。n=5的時候，也是同樣的操作。 （a） (b)圖5.4.2 移動平均法指數(shù)平滑法：單擊“工具”|“數(shù)據(jù)分析”，打開數(shù)據(jù)分析對話框，如圖5.4.3(a)所示。選擇“指數(shù)平滑”，彈出“指數(shù)平滑”對話框，在“輸入?yún)^(qū)域”中輸入“B2：B13”，在“阻尼系數(shù)”輸入“0.8”，在“輸出區(qū)域”輸入“F15”，如圖5.4.3（

14、b）。單擊確定就可以得到的指數(shù)平滑值。 （a） （b）圖5.4.3 指數(shù)平滑法（5）計算季節(jié)變動季節(jié)變動是指現(xiàn)象受到自然因素和社會習俗等因素影響而發(fā)生的有規(guī)律的周期性變動。測定季節(jié)變動可分為兩種：一是不排除長期趨勢的影響，直接根據(jù)原時間序列來測定；二是依據(jù)剔除長期趨勢后的時間序列來測定。前者用簡單平均法，或者用移動平均趨勢剔除法。簡單平均法計算季節(jié)指數(shù)打開工作簿“實驗5.5.1.xls”，利用“季度數(shù)據(jù)”工作表中的數(shù)據(jù)，計算季節(jié)變動，結果如圖5.5.1所示。圖5.5.1 用簡單平均法計算季節(jié)指數(shù)結果計算步驟：第一步：計算出同季合計：在B9輸入“=SUM(B3:B8)”，并用鼠標拖曳將公式復制到

15、C9：E9區(qū)域。第二步：計算出全年合計：在F3輸入“=SUM(B3:E3)”，并用鼠標拖曳將公式復制到F4：F9區(qū)域。第三步：計算出同季平均：在B10輸入“=B9/6”，并用鼠標拖曳將公式復制到C10：E10區(qū)域。在F10輸入“=F9/24”。第四步：計算季節(jié)指數(shù)：在B11輸入“=B10/$F$10”，并用鼠標拖曳將公式復制到C11：F11區(qū)域。移動平均趨勢剔除法計算季節(jié)指數(shù)打開工作簿“實驗5.5.2.xls”，利用“季度數(shù)據(jù)”工作表中的數(shù)據(jù)，計算季節(jié)變動，結果如圖5.5.2(a)和5.5.2(b)所示。 （a）圖5.5.2 計算剔除趨勢值的結果（b）圖5.5.2 移動平均趨勢剔除法計算季節(jié)指數(shù)結果計算步驟：第一步：計算四項移動平均：在D3中輸入“=AVERAGE(C2:C5)”，并用鼠標拖曳將公式復制到D4：D23區(qū)域。第二步：計算移正平均T：在E4中輸入“=AVERAGE(D3:D4)”，并用鼠標拖曳將公式復制到E5：E23區(qū)域。第三步：剔除長期趨勢Yt：即計算Y/T：在F4中輸入“=C4/E4”，并用鼠標拖曳將公式復制到F5：F23區(qū)域。第四步：重新排列F4：F23區(qū)域中的數(shù)字，使