◇劉惠娟

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

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

一、案例選擇

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

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

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

表1 長(zhǎng)治1997～2016年農(nóng)用機(jī)械及玉米產(chǎn)量的數(shù)據(jù)單位：千公頃、人、噸、臺(tái)、噸

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

（二）統(tǒng)計(jì)預(yù)測(cè)及模型建立方法

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

其中，A表示影響生產(chǎn)的轉(zhuǎn)化效率系數(shù)，i表示變量的序號(hào)，α表示幾個(gè)變量對(duì)產(chǎn)量影響的彈性系數(shù)。

二、建模的方法與步驟

（一）“分析工具庫(kù)”的加載

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

（二）利用Excel進(jìn)行回歸分析

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

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

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

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

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

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檢驗(yàn)，顯著性達(dá)到99.99%以上。

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

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

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

（四）模型的精度

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

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

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

三、結(jié)論與討論

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

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

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

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