利用 EXCEL實現計酬分配預測的回歸分析＊

朱玨鈺

(湖南第一師范學院信息科學與工程系,湖南長沙 410205)

利用 EXCEL實現計酬分配預測的回歸分析＊

朱玨鈺

(湖南第一師范學院信息科學與工程系,湖南長沙 410205)

在經濟管理理論和應用研究中,回歸分析的應用日益廣泛.通過運用一元、多元回歸分析的理論及建模方法,搜集某大型公司的數據,利用分析工具庫提供的回歸工具進行回歸分析,擬合出回歸模型.同時檢驗模型中自變量對因變量的影響程度,利用增減法,通過不斷嘗試增加自變量來建立和確定最優回歸模型.

回歸分析;回歸模型;預測;計酬分配

在現實世界中,許多現象之間客觀地存在著各種各樣的有機聯系,這種聯系經常表現為數量上的相互依存關系.例如在生產活動中,糧食的產量要受肥量、降雨量、氣溫等因素的影響.又如在市場經濟環境下,商品的銷售量與商品的價格、商品的質量以及消費者的收入水平等因素有關.回歸分析預測就是從各種因素之間的因果關系出發,通過分析與預測對象相關聯因素的變動趨勢,推算預測對象的未來數量狀態.

1 回歸分析預測的原理

無論是哪一種回歸模型,在建立模型時都需要計算各個變量的均值、離差、平方和等多項指標.而對回歸模型進行統計檢驗更需要進一步將總離差平方和分解成回歸平方和與殘差平方和,并進行擬合優度檢驗、回歸系數的顯著性檢驗、自相關檢驗等.其運算復雜、計算工作量大.應用 EXCEL分析工具庫提供的回歸工具就可以快捷地完成回歸分析預測所需的幾乎所有的計算.采用回歸分析工具的分析結果包括 SUMMRAY OUTPUT(回歸匯總輸出)、RESI DUAL OUTPUT(殘差輸出 )和 PROBAB I LITY OUTPUT(正態概率輸出)三部分,其中回歸匯總輸出是回歸結果中最重要的部分,主要包括回歸統計信息 (如可決系數)、調整可決系數、標準誤差和觀測值數、方差分析表、回歸參數信息 (如回歸參數值)、回歸參數 t統計量值、P-value值等.下面以某快遞公司按工作時間計酬分配的回歸分析預測為例來說明 EXCEL在計酬分配預測回歸分析中的應用[1].

2 操作方法與步驟

設某快遞公司準備制定按工作時間計酬的分配方案,人事部門隨機抽取了 10名員工一個月的業務記錄,計算出他們每天工作的平均行駛距離和工作時間數據.現需要通過分析行駛距離與工作時間的因果關系,建立相應的回歸模型.該問題只有一個自變量——行駛距離,是典型的一元線性回歸問題.應用 EXCEL“回歸”分析工具建立預測模型的方法如下:

(1)將需要分析的統計數據輸入到工作表中;

(2)單擊“工具”菜單中的“數據分析”命令彈出“數據分析”對話框;

(3)在“分析工具”列表框中選擇“回歸”工具,然后單擊“確定”按鈕彈出“回歸”對話框;

(4)Y值輸入區域:在此輸入對因變量數據區域的引用,該區域必須由單列數據組成.指定工作時間數據所在的單元格區域 B1:B11.X值輸入區域:EXCEL將對此區域中的自變量從左到右進行升序排列.指定行駛距離數據所在的單元格區域 C1:C11.標志:如果輸入區域的第一行或第一列包含標志,選此復選框.EXCEL將在輸出表中生成適宜的數據標志.置信度:如果需要在匯總輸出表中包含附加的置信度信息,選此復選框.在右側的框中,輸入所要使用的置信度,默認值為 95%.輸出區域:指定分析結果輸出到本工作表,并鍵入輸出區域的左上角單元格地址 E1.回歸分析結果中回歸匯總輸出(SUMMRAYOUTPUT)(如圖 1所示).

在“回歸統計”中給出了相關系數 R=0.8187、系數 R2=0.6702、調整后的 R2=0.6290以及標準誤差 =1.0472等回歸統計結果,說明該回歸模型擬合優度較好.在“方差分析”結果中給出了 F檢驗值16.2564,說明回歸效果顯著.在最下方的回歸模型區域給出了回歸系數的檢驗結果,自變量 P-value=0.003<0.05,通過了顯著性檢驗.最后建立的一元回歸模型為:y=1.9578+0.0434x.

圖1

應用上述回歸模型,即可根據給定的行駛距離計算出大致的工作時間.在 B18單元格輸入上述模型的計算公式“=F17+F18＊C18”.其中 F17和F18單元格中分別為回歸分析計算出的一元線性回歸模型的截距和斜率.C18為指定自變量數值的單元格,只要在 C18單元格輸入指定的行駛距離即可得到模型預測的工作時間.例如指定行駛距離為 90公里,相應的預測結果 B18=5.86631.

進一步分析上述“回歸統計”結果可知,該回歸模型的判定系數只有 0.6702,并不十分理想,這說明行駛距離這一因素對工作時間的變動影響大約只占 67%.還需要考慮是否有其他的因素被忽略了.經過分析發現,員工每天承擔的業務次數對工作時間也有重要的影響,所以增加了一個自變量業務次數,然后重新建立回歸模型.因為有兩個自變量,所以這屬于多元回歸模型,具體來說是二元回歸模型.多元回歸分析的操作步驟與一元回歸類似.有關的統計數據及多元回歸分析結果如圖 2所示.

圖2

對于多元回歸來說,R為復相關系數,判定系數R2也應該使用調整后的 R2.因為在多元回歸模型中,R2會隨著模型自變量個數的增加而增加,出現虛高的現象.另外,多元回歸模型還應進行回歸方程的顯著性檢驗,即自變量和因變量總體之間的線性關系是否顯著,這主要是通過“方差分析”結果中的 F檢驗值來判斷.比較兩個模型,可以看出二元回歸模型的各項指標比一元回歸模型有了很大的改善,說明模型更加科學、合理,預測也更為準確.最后建立的二元回歸模型為:y=-0.0572+0.0357x1+0.9268x2.

應用該回歸模型,即可根據給定的行駛距離和業務次數計算出大致的工作時間.在 B19單元格輸入上述模型的計算公式“=G17+G18＊C19+G19＊D19”.其中 G17、G18和 G19單元格中分別為回歸分析計算出的二元線性回歸模型的 3個系數.C19為指定第一個變量行駛距離的單元格,D19為指定第二個自變量業務次數的單元格.只要在 C19和D19單元格輸入指定的行駛距離和業務次數,即可得到模型預測的工作時間.例如指定行駛距離為90公里,業務次數為 3次,相應的預測結果 B19=5.93911[2].

回歸分析是統計學中的精華,也是最為實用之處,被廣泛地應用于自然科學和社會科學的研究及商業統計分析中,而 EXCEL提供的各類函數及回歸工具能幫助用戶快速地實現各種類型的回歸分析,并在此基礎上進行預測.回歸分析最為常用且普遍的方法是采用回歸分析工具方法,熟練地掌握回歸分析工具的應用,并能夠正確利用分析的輸出結果,將會給人們的工作提供巨大的幫助.

[1]EXCEL Home.EXCEL數據處理與分析[M].北京:人民郵電出版社,2008.

[2]鐘曉鳴,等.EXCEL在統計分析中的應用 [M].北京:科學出版社,2009.

TP317.3

A

1008-4681(2010)02-0071-02

2010-03-24

朱玨鈺(1980-),女,湖南長沙人,湖南第一師范學院信息科學與工程系講師.研究方向:計算機科學與技術.

(責任編校:小蒙)