利用 EXCEL實(shí)現(xiàn)計(jì)酬分配預(yù)測的回歸分析＊

朱玨鈺

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

利用 EXCEL實(shí)現(xiàn)計(jì)酬分配預(yù)測的回歸分析＊

朱玨鈺

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

在經(jīng)濟(jì)管理理論和應(yīng)用研究中,回歸分析的應(yīng)用日益廣泛.通過運(yùn)用一元、多元回歸分析的理論及建模方法,搜集某大型公司的數(shù)據(jù),利用分析工具庫提供的回歸工具進(jìn)行回歸分析,擬合出回歸模型.同時(shí)檢驗(yàn)?zāi)Ｐ椭凶宰兞繉σ蜃兞康挠绊懗潭?利用增減法,通過不斷嘗試增加自變量來建立和確定最優(yōu)回歸模型.

回歸分析;回歸模型;預(yù)測;計(jì)酬分配

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

1 回歸分析預(yù)測的原理

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

2 操作方法與步驟

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

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

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

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

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

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

圖1

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

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

圖2

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

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

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

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

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

TP317.3

A

1008-4681(2010)02-0071-02

2010-03-24

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

(責(zé)任編校:小蒙)