利用Excel建立統(tǒng)計預測模型

◇劉惠娟

隨著科學技術的進步和時代的快速發(fā)展，各級統(tǒng)計工作已經從以往的事后統(tǒng)計逐漸向對未來的預警預示轉變，而基層的統(tǒng)計分析水平還停留在簡單的同比、環(huán)比等初級水平。隨著計算機技術的發(fā)展，大數(shù)據時代的來臨，大量的統(tǒng)計計算均可由軟件來實現(xiàn)?？蛇M行統(tǒng)計分析的軟件有許多，如SPSS，SAS，BMDP，MATLAB，BMDP 等。這些軟件的優(yōu)點是界面友好，統(tǒng)計功能強大，處理功能完善，可以進行二次開發(fā)，可直接用鼠標完成數(shù)據分析，圖表描述等，能夠提供常見的統(tǒng)計分析功能等。但是它們都存在著一些缺點：首先，軟件價格較為昂貴，普及性低；其次這些軟件命令和函數(shù)較為繁瑣，入門不易；第三，軟件系統(tǒng)配置要求高，不適合安裝運行在公共使用的計算機上。

Office一直是較為流行的、功能強大的辦公應用軟件之一，它是迄今為止Microsoft公司推出的最為復雜的產品之一。Excel價格便宜，普及率高。利用它可以非常方便地制作出集文字、數(shù)字、圖表、圖形于一體的電子表格，并且可以使用公式和函數(shù)對表格中的數(shù)據進行復雜的運算，同時，它還可以通過便捷的操作來完成非常復雜的數(shù)據分析、統(tǒng)計及管理工作。本文即利用Excel 2007軟件對長治地區(qū)1997～2016年農業(yè)統(tǒng)計數(shù)據進行分析，并成功設置柯布-道格拉斯（C-D）生產模型為例，為更好地利用這種辦公軟件，提高統(tǒng)計的預測預警功能，提供一種新思路。

一、案例選擇

（一）統(tǒng)計數(shù)據的采用

長治位于山西省東南部、太行山南段，東部與河北邯鄲、河南安陽為鄰，西部與臨汾相依，南部與晉城交界，北部與晉中腹地接壤。全境地勢由西北向東南緩緩傾斜，東西長約150公里，南北寬約140公里，總面積13896平方公里。地理坐標為東經 111°55′～112°44′，北緯 35°11′～37°08′，平均海拔高度為1000米。長治屬典型暖溫帶半濕潤大陸性季風氣候，雨熱同季。年平均溫度8.6℃～10.5℃，年平均降水537.4～656.7毫米，年平均濕度61%，年日照時數(shù)2418～2616小時，年平均無霜期在151.8～181.9天，年平均風速為1.1～2.3米/秒。是全省主要產糧區(qū)之一，非常適宜玉米作物的種植。

本文假定玉米產量是與農業(yè)生產資料（如播種面積和勞動力）和農業(yè)投入（如化肥施用量和農用機械量）等因素的有關的函數(shù)?！堕L治市統(tǒng)計年鑒》（1997-2016）提供了長治地區(qū)各縣1997～2016年玉米年產量（Y）、玉米播種面積（X1）、農業(yè)勞動力（X2）、化肥施用量（X3）、農業(yè)機械量（X4）方面的數(shù)據，如表1。

表1 長治1997～2016年農用機械及玉米產量的數(shù)據單位：千公頃、人、噸、臺、噸

續(xù)表單位：千公頃、人、噸、臺、噸

（二）統(tǒng)計預測及模型建立方法

柯布-道格拉斯生產函數(shù)是由美國經濟學家保羅.道格拉斯（P.H.Douglas）和數(shù)學家查理.柯布（C.W.Cobb）根據歷史統(tǒng)計資料研究20世紀初美國的資本投入和勞動投入對產量的影響時，得出的一種生產函數(shù)，是分析資源“投入”與產品“產出”之間經濟數(shù)量關系最為常用的一種生產函數(shù)。農業(yè)生產過程是各種生產資源不斷投入的過程，為了求得更高的收益，必須分析投入是否合理，總結經驗，以便決定下一步生產再投入的增減。C-D生產函數(shù)以其使用方便，便于比較被廣泛應用于農業(yè)技術經濟中。利用C-D生產函數(shù)，可以估測各項投入要素和糧食轉換率對農業(yè)產出的影響程度，從而判定那些是影響糧食產出的主要因素，那些是次要因素。本文假定玉米總產量（Y）為產出，玉米播種面積（X1）和農業(yè)勞動力（X2）為生存資料；化肥施用量（X3）和農用機械量（X4）為投入，那么假定的CD函數(shù)模型為：

其中，A表示影響生產的轉化效率系數(shù)，i表示變量的序號，α表示幾個變量對產量影響的彈性系數(shù)。

二、建模的方法與步驟

（一）“分析工具庫”的加載

通常情況下，Excel并沒有安裝“分析工具庫”，因此，需要先激活Excel的“數(shù)據分析”功能。步驟如下：打開Excel2007工作表，點擊左上角“Office按鈕”選擇“Excel選項”，在彈出的對話框中點擊“加載項”，選中“分析工具庫”，點擊“轉到”，隨機彈出“加載宏”界面，點擊“確定”；查看“數(shù)據”菜單，可發(fā)現(xiàn)最后一欄新增“數(shù)據分析”命令。

（二）利用Excel進行回歸分析

本文借用宮錫鴻、宮兆芳等采用Excel建立C-D函數(shù)數(shù)學模型的方法，先將各變量轉換為對數(shù)進行回歸分析，然后對得出的回歸方程進行反對數(shù)轉換，得出相應的C-D函數(shù)模型。具體步驟如下：

首先，將原始數(shù)據輸入Excel表格中：打開Excel2007工作表，根據表1在工作表的A1：F1單元格區(qū)域依次輸入“年份”“ 播種面積 X1”“農 業(yè)勞動力 X2”“化 肥施用量 X3”“農 用 機械量X4”“總產量Y”作為變量名稱；在單元格A2：A21中依次鍵入年份1997～2016年，單元格B2：B21中依次鍵入表格第二列數(shù)據，單元格C2：C21中依次鍵入表格第三列數(shù)據，單元格D2：D21中依次鍵入表格第四列數(shù)據，單元格E2：E21中依次鍵入表格第五列數(shù)據，單元格F2：F21中依次鍵入表格第六列數(shù)據。

其次，將輸入數(shù)據轉換為對數(shù)：在單元格G1：K1中依次鍵入 X'1、X'2、X'3、X'4、Y'，在單元格 G2 中鍵入“=log10（B2）”，回車得到1997年農業(yè)耕種機械數(shù)值的對數(shù)值，用鼠標選中G2單元格，再用左鍵填充柄拖動至單元格G21。同樣，在單元格H2 中鍵入“=log10（C2）”，并填充至單元格 H21，在單元格 I2、J2、K2 中鍵入“l(fā)og10（D2）”、“l(fā)og10（E2）”、“l(fā)og10（F2）”，并填充至單元格I21、J21、K21?；蜻x中G2直接用鼠標左鍵填充柄橫向拖動至 K2，再縱向依次拖動至 G21、H21、I21、J21、K21，得到所有變量的對數(shù)值。

然后，進行多元回歸分析：在菜單欄中選取“數(shù)據”→“數(shù)據分析”→“回歸”，從而出現(xiàn)回歸對話框。在回歸對話框中，單擊“Y值輸入區(qū)域（Y）”在輸入框右邊的折疊按鈕，然后在工作表中選取logY值區(qū)域＄K＄2:＄K＄21，用同樣方法完成“X值輸入區(qū)域（X）”的選擇＄G＄2：＄J＄21，在“輸出選項”框中選擇“輸出區(qū)域”單選框，并在其右面的框中鍵入“＄A＄24”，在“殘差”框中選擇“殘差（R）”復選框。最后單擊“回歸”對話框中的“確定”按鈕。這時，會在A24：A44行出現(xiàn)回歸分析結果，在A48：A70行出現(xiàn)回測結果的對數(shù)值。多元回歸分析結果為：F=25.06032871，常數(shù)項為3.316733799，四個回歸系數(shù)分別為0.684353534、-0.300109061、-0.275458343、0.956677266。

據常數(shù)項、四個回歸系數(shù)可以得到一個多元回歸方程：

Y'=0.684353534X'1-0.300109061X'2-0.275458343X'3+0.956677266X'4+3.316733799

其中：Y'=logY、X'1=logX1、X'2=logX2、X'3=logX3、X'4=logX4。由回歸分析表中可知，F(xiàn)臨界值為1.71814E-06，此回歸方程通過了α=0.05的F檢驗，顯著性達到99.99%以上。

（三）將回歸方程轉換為C-D函數(shù)模型

將上述回歸方程的常數(shù)項3.316733799進行反對數(shù)轉換的方法是：在上述工作表中的空白處單擊某一單元格，例如D73，以存放轉換后的常數(shù)項；在菜單欄中點擊“公式”，單擊“Fx”，在彈出的對話框中的“搜索函數(shù)”框中鍵入“POWER”，點擊“轉到”，即在“選擇函數(shù)”列表中出現(xiàn)“POWER”；選中“POWER”，單擊[確定]，緊接著彈出的“POWER”對話框，在“Number”輸入框中鍵入數(shù)碼“10”，在“Power”輸入框中輸入常數(shù)項“3.316733799”，單擊“確定”后，即在單元格D73中出現(xiàn)了常數(shù)項“3.316733799”的反對數(shù)“2073.642086”，因此，多元線性回歸方程轉化為柯布-道格拉斯生產函數(shù)：

其中，轉化效率系數(shù)為2073.642086，四個彈性系數(shù)分別為 0.684353534、-0.300109061、-0.275458343、0.956677266。

（四）模型的精度

模型的精度可以通過均值指標、穩(wěn)定性指標和綜合指標來確定。本文采用簡單的均值測定法。步驟如下：對B51：B70的預測Y值進行反對數(shù)轉換，即可得到玉米總產量的回測值。在單元格D50鍵入“回測值”，選取單元格D51，單擊“公式”欄中“Fx”按鈕，在彈出的函數(shù)對話框中“選擇函數(shù)”列表中的選中“POWER”函數(shù)；點擊“確定”，緊接著的彈出的“POWER”對話框，在“Number”輸入框中鍵入數(shù)碼“10”，在“POWER”輸入框中鍵入“B51”，單擊“確定”按鈕后即可在單元格D51中出現(xiàn)年序1，即1997年的玉米產量回測值。選中單元格D51并用鼠標左鍵填充柄拖動至D70，即得各年序的產量回測值，將回測值與實際值進行比較。如表2，E50：E70中輸入Y的實際值，F(xiàn)50:F70中計算出二者的誤差。

在誤差列表格下方，即Excel工作表的F71單元格中，單擊“Fx”，在“選擇函數(shù)”框中選取“AVERAGE”函數(shù)，點擊“確定”，點擊“Number1”右側按鈕，選中“F51：F70”，點擊“確定”，即彈出數(shù)據“-3622”。表明該模型得出的Y值與實測值間的平均誤差為“-3622”。

表2 C-D函數(shù)數(shù)學模型的回測值和實際值間的比較單位：噸

三、結論與討論

（一）所建立的C-D模型結果分析

通過對1997～2016年玉米產量與各因素間的統(tǒng)計分析得到的C-D生產函數(shù)，通過了顯著水平等于0.05的P檢驗和F檢驗，說明該模型可信度較高。修正樣本可決系數(shù)R2=0.835128573，該模型的解釋能力為83.5%。也就是說該模型在83.5%的程度上擬合了樣本數(shù)據。通過均值測定法測定模型精度,顯示該模型得出的Y值與實測值間的平均誤差為“-3622”。分析結果顯示：播種面積（X1）、農業(yè)勞動力（X2）、施肥總量（X3）和農用機械量（X4）的彈性系數(shù)分別為 0.684353534、-0.300109061、-0.275458343、0.956677266。可見，播種面積和農業(yè)機械的投入對玉米增產起到不可替代的作用。而近年來，農用耕地面積增長已臨近極限，農業(yè)人口的保持也面臨挑戰(zhàn)，農業(yè)用肥也逐漸趨于科學合理，各因素對玉米產量的影響，都是通過進入土壤中的水分起作用，而這就對農用機械提出更高的要求。因此，下一步的工作應從機理上進一步研究提高畝產和生產勞動率著手。綜上所述，此模型較為適用。

（二）Excel在統(tǒng)計分析中的應用

上述案例中，不對變量進行對數(shù)轉換，而是直接進行回歸分析，便可得出F=31.51257053，通過α=0.05的F檢驗的多元回歸方程：Y=5282.591751X1-0.570067034X2-1.816859017 X3+21.03342716 X4+223433.9603。由以上建立的C-D函數(shù)模型和回歸分析等步驟可以看出，Excel在進行數(shù)據輸入、對數(shù)轉換、多元回歸分析、反對數(shù)轉換等方面，均為簡便快捷，因此建立模型也同樣簡便快捷。如果年代較長、變量較多，可更顯示出Excel的強大的數(shù)學分析和建模功能。Microsoft Excel是最常用的辦公軟件，事實上，Excel在方差分析、主成分分析等各種分析方法，在灰色模型、空間面板、缺失面板等各種數(shù)學模型建立，在投資管理決策、動態(tài)監(jiān)控決策等各個領域均可巧妙應用。如果能夠充分利用與發(fā)揮，一定能為基層統(tǒng)計工作人員分析及預警預測發(fā)揮巨大的作用。