基于Excel平臺的時間數列長期趨勢模型的建立

【摘要】通過實例說明在Excel平臺上建立時間數列長期趨勢模型常用的方法和步驟，進一步說明了Excel是一款集表格處理、數據管理、統(tǒng)計制圖、統(tǒng)計分析于一體且實用性強操作簡單方便的辦公軟件。

【關鍵詞】Excel平臺 時間數列 趨勢模型

【資助項目】廣西高等教育教學改革工程項目立項2014JGA209。

【中圖分類號】G64 【文獻標識碼】A 【文章編號】2095-3089（2015）01-0222-02

時間數列長期趨勢模型的測定就是根據時間數列中指標值的發(fā)展變化趨勢，配合一條理想的趨勢線，從直觀上看，這條趨勢線要離各散點最近。本文將通過實例說明如何在Excel平臺上實現時間數列曲線趨勢模型的建立。

1.時間數列長期趨勢模型的選擇

時間數列長期趨勢模型的選擇可以根據時間數列的分析指標來確定，當數列指標值的一級增長量大致相等時，應選直線模型；二級增長量大致相等時，應選二次曲線模型；環(huán)比發(fā)展速度大致相等時，應選指數模型。

2.趨勢模型中未知參數的確定

趨勢模型中未知參數的確定最常用的方法是最小二乘法，即時間數列指標值與相應趨勢值的離差平方和最小。

2.3模型為yt=abt時，對上述方程兩邊取對數得：lny=lna+tlnb令lny=Y，lna=A，lnb=B則即可化為2.1的情形。

3.基于Excel的時間數列長期趨勢模型的建立

3.1直線趨勢模型的建立

例1 設某企業(yè)2005—2013年的產品銷售收入資料已輸入到Excel工作表中（圖1）。求銷售收入的趨勢模型。

圖1 產品銷售收入的長期趨勢計算表

從圖1可見，產品銷售收入的一級增長量在48.5（平均值）左右，所以可配合直線趨勢。

在Excel的數據分析工具中，有一個專用于進行回歸分析的工具。使用此工具，可以更加方便快捷準確地進行回歸分析，并能提供更多的數據信息。

第一步，用鼠標點擊工作表中待分析數據的任一單元格，選擇“工具”菜單的“數據分析”子菜單，用鼠標雙擊“回歸”選項，進入回歸對話框。

第二步，在回歸對話框中，在“y值輸入區(qū)域”框中輸入C2：C10，在“x值輸入區(qū)域”框中輸入B2：B10，選中“標志”復選框，在“輸出區(qū)域”中輸入A12，選中“殘差”、“ 標準殘差”、 “線性擬合圖”等復選框。

第三步，單擊“確定”按鈕，即在以A12為起點的右邊空白區(qū)域給出結果（圖2）。

圖2 “回歸”輸出結果

結果表明，趨勢方程為：yt=654.87+48.26t

從判定系數看，在時間對產品銷售收入的影響中，有99%可由該回歸方程解釋。從t檢驗看，截距項和回歸系數的p值都遠遠小于0.05，表明二者都是顯著的。從F檢驗看，Significance F為2.3316E-12，表明該模型通過了5%的顯著性檢驗，模型整體也是顯著的。所以回歸方程是合適的。

3.2曲線線趨勢的建立

例2 設某企業(yè)1996—2013年某產品產量資料已輸入到Excel工作表中（圖3），求該產品產量趨勢模型。

3.2.1計算時間數列指標值的二級增長量

單擊D3單元格，輸入“=C3-C2”，回車后得第一個逐期增長量為26，移動鼠標箭頭至D3單元格右下方使其變?yōu)椤?”號，按住鼠標左鍵向下拖拽，至D18單元格放開鼠標，即可得1996-2013年的逐期增長量。類似前面作法可得所有的二級增長量。從圖3中可看出，企業(yè)產品產量的二級增長量大致相等，可以擬合拋物線趨勢模型。

圖3某企業(yè)1996—2013年產品產量相關數據

3.2.2用常規(guī)方法建立二次曲線回歸方程

第一步，單擊F2單元格，輸入“=B2？鄢C2”，回車后得第一個xy值為-1608，類似3.2.1作法，即可得1996-2013年的xy值。同樣依次算出1996-2013年的x2，x2y，x4值。

第二步，單擊B19單元格，輸入“=SUM（B2：B19）”，回車后得■x值為0，類似算出■y，■xy，■x2，■x2y，■x4的值。

第三步，將上述各合計值代入方程組（2）解得a=970.29，b=178.7，c=10.38，即回歸方程為yx=970.29+178.7x+10.38x2 。

若要用此方程求各年的趨勢值，可以單擊J2單元格，輸入“=970.29+178.7？鄢B2+10.38？鄢E2”，回車后得1996年預測值為205.01，再算出1997-2013各年預測值。從圖3中可看出，各年預測值與實際相差不大，說明回歸方程的代表性是很高的。

3.2.3 用“添加線性趨勢線”建立二次曲線回歸方程

第一步，拖動鼠標選定數值區(qū)域B2：C18，不包括數據上面的標志項。

第二步，選擇“插入”菜單的“圖表”子菜單，進入圖表向導。

第三步，選擇“圖表類型”為“散點圖”，然后單擊“下一步”。

第四步，繼續(xù)單擊“下一步”，選擇“標題”下的子項“圖表標題”，在其中輸入“產量與時間”，在“數值（X）軸（A）”子項中輸入 間”，在“數值（Y）軸（V）”子項中輸入“產量”。單擊“完成”，即生成散點圖。

第五步，在散點圖中，用鼠標對準任一數據點，單擊右鍵，選擇“添加趨勢線”，在“類型”選項卡中選擇“多項式”，階數選擇“2”，在“選項”選項卡中單擊“顯示公式”和“顯示平方”復選框，再單擊“確定”即可得添加線性趨勢線的結果，如圖4 。

圖4添加線性趨勢線結果

結果表明所求的回歸方程也是：yx=970.29+178.7x+10.38x2

從以上過程可看到，Excel是一款集表格處理、數據管理、統(tǒng)計制圖、統(tǒng)計分析于一體且實用性強操作簡單方便的辦公軟件。借助Excel，幾乎可以完成所有統(tǒng)計數據分析與處理，我們可以根據需要選擇不同的方法。

參考文獻：

[1]盛驟等.概率論與數理統(tǒng)計[M].北京，高等教育出版社.2001年12月第三版.294-313

[2]范秀榮等.統(tǒng)計學原理[M].北京，高等教育出版社.2010年8月第一版.197-203

[3]王劍武等.統(tǒng)計學[M].長沙，湖南師范大學出版社.2014年1月第一版.192-198

[4]黃應繪等.統(tǒng)計學實驗[M].成都，西南財經大學出版社.2009年7月第二版.111-121

作者簡介：

黃基廷（1964-），男，廣西天等縣人，副教授，主要從事應用數學研究.