朱玨鈺

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

利用 EXCEL實現(xiàn)計酬分配預測的回歸分析＊

朱玨鈺

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

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

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

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

1 回歸分析預測的原理

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

2 操作方法與步驟

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

(1)將需要分析的統(tǒng)計數(shù)據(jù)輸入到工作表中;

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

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

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

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

圖1

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

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

圖2

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

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

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

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

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

TP317.3

A

1008-4681(2010)02-0071-02

2010-03-24

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

(責任編校:小蒙)