基于Excel的最優子集多元線性回歸預測模型設計

［摘要］ 回歸分析預測法是一種具體的、行之有效的、實用價值很高的常用市場預測方法。統計學中的回歸預測分析具有普遍的實用意義，但變量之間關系分析及計算繁雜，而借助Excel可方便高效地研究其數量變動關系，完成其繁雜的計算分析過程。本文利用Excel的圖表及數據分析工具，采用最優子集法，判斷各變量間的相關程度，并在此基礎上建立回歸模型進行預測，使回歸預測分析的計算過程更加簡單，統計預測方法更為實用。

［關鍵詞］ Excel； 最優子集； 多元線性回歸； 預測

doi : 10 . 3969 / j . issn . 1673 - 0194 . 2011 . 18. 046

［中圖分類號］F272.1； F224.7 ［文獻標識碼］A ［文章編號］1673 - 0194（2011）18- 0070- 02

1引言

回歸分析預測法有多種類型。依據相關關系中自變量的個數不同進行分類，可分為一元回歸分析預測法和多元回歸分析預測法。在一元回歸分析預測法中，自變量只有一個，而在多元回歸分析預測法中，自變量有兩個以上。依據自變量和因變量之間的相關關系不同，可分為線性回歸預測和非線性回歸預測。

多元線性回歸在諸多學科中有廣泛應用。在多元線性回歸的實際應用中，考慮的自變量Ｘｉ（ｉ ＝ １，２，…，ｍ，ｍ為自變量數目或個數）經常包括所有可能影響因變量Ｙ的因素。在眾多的Ｘｉ中，有的對Ｙ有顯著影響，有的影響很小甚至基本無影響。如果把對Ｙ影響小的Ｘｉ保留在回歸模型中，不僅增加收集數據和分析數據的負擔，使得回歸方程不穩定，而且會因Ｘｉ的數目過多而不便于使用。因此，自變量選擇在理論上和應用上都十分重要。自變量選擇通常采用的方法是最優子集法，即選出全局“最優”回歸模型。該法是對自變量各種不同的組合所建立的回歸方程進行比較，進而從全部組合中挑出一個“最優”回歸方程。挑選“最優”回歸模型的方法一般有Ｒ２法、校正Ｒ２法、殘差均方和或剩余標準差最小法、Ｃｐ統計量法、ＡＩＣ、ＢＩＣ及ＡＩＣＣ信息量準則等。

回歸分析預測法的一般步驟為：

1.1根據預測目標，確定自變量和因變量

明確預測的具體目標，也就確定了因變量。如預測具體目標是下一年度的銷售量，那么銷售量Ｙ就是因變量。通過市場調查和查閱資料，尋找與預測目標的相關影響因素，即自變量，并從中選出主要的影響因素。

1.2建立回歸預測模型

依據自變量和因變量的歷史統計資料進行計算，在此基礎上建立回歸分析方程，即回歸分析預測模型。

1.3進行相關分析

回歸分析是對具有因果關系的影響因素（自變量）和預測對象（因變量）所進行的數理統計分析處理。只有當變量與因變量確實存在某種關系時，建立的回歸方程才有意義。因此，作為自變量的因素與作為因變量的預測對象是否有關，相關程度如何，以及判斷這種相關程度的把握性多大，就成為進行回歸分析必須要解決的問題。進行相關分析，一般要求出相關系數（Ｒ），以相關系數的大小來判斷自變量和因變量的相關程度。

相關系數Ｒ的絕對值的大小和相關程度如下：

（1） 當Ｒ ＝ ０時，說明是零相關，所求回歸系數無效。

（2） 當 | R | = 1時，說明是完全相關，自變量Ｘ與因變量Ｙ之間的關系為函數關系。

（3） 當0 < | R | < 1時，說明是部分相關，絕對值越大相關程度越高。

1.4檢驗回歸預測模型，計算預測誤差

回歸預測模型是否可用于實際預測，取決于對回歸預測模型的檢驗和對預測誤差的計算。回歸方程只有通過各種檢驗，且預測誤差較小，才能將回歸方程作為預測模型進行預測。

1.5計算并確定預測值

利用回歸預測模型計算預測值，并對預測值進行綜合分析，確定最后的預測值。

２應用實例

某零售店將其連續１８個月的流動資金、廣告費、員工薪酬以及零售額等方面的數據作了一個匯總，這些數據顯示在工作表Ａ２：Ｅ１４單元格區域，如圖１所示。該店的管理人員試圖根據這些數據找到零售額與其他3個變量之間的關系，以便進行零售額預測并為未來的預算工作提供參考。試根據這些數據建立回歸模型。如果未來某月流動資金額為１５０萬元，廣告費預算為４４萬元，員工薪酬總額為２８萬元，試根據建立的回歸模型預測該月的銷售額。

３建立模型

3.1采用圖表法判斷各變量與零售額的線性相關關系

分別選中流動資金與零售額、廣告費與零售額、薪酬與零售額，利用圖表向導建立Ｘ、Ｙ散點圖，觀察是否是線性相關，在此基礎上右擊散點圖添加趨勢線，在打開的“添加趨勢線”。對話框中作如圖２所示的設置。結果如圖３、圖４和圖５所示。

擬合系數Ｒ（相關系數）分別為：０．８９０ ５ 、０．８３７ ３、０．７０９ ２，從中可以看出流動資金與廣告費這兩個自變量對零售額的影響要大于員工薪酬收入。

3.2利用數據分析工具得出回歸分析報告判斷其線性關系

首先選擇“工具”菜單 “加載宏”命令，在打開的“加載宏”對話框中選中“分析工具庫”；單擊“工具”菜單 “數據分析”命令，在打開的“數據分析”對話框中選中“回歸”，作如圖６所示的設置。得到的流動資金回歸分析報告如圖７所示。

其中Ｒ為０．８９０ ５２１ ９，截距為１７１．３９８ ３５，斜率為１１．３９９ ２３７，與圖表法相吻合。按照同樣的方法分別建立廣告費回歸分析報告、員工薪酬回歸分析報告、流動資金－廣告費回歸分析報告、流動資金－薪酬回歸分析報告、廣告費－薪酬回歸分析報告、流動資金－廣告費－薪酬回歸分析報告等。

3.3采用最優子集法作自變量篩選

將這些回歸分析報告進行整理，如表１所示，找出調整后Ｒ2的最大值是流動資金、廣告費兩個自變量。

3.4進行預測

根據流動資金、廣告費回歸分析報告，已知：回歸方程截距為８６．９５，斜率１（對應流動資金）為７．１１，斜率２（對應廣告費）為１３．６８，流動資金１５０萬元，廣告費４４萬元的零售額預測值為８６．９５ ＋ ７．１１ × １５０ ＋ １３．６８ × ４４ ＝ １ ７５５．４（萬元）。

4小結

從上述的分析計算過程中可以看出，Ｅｘｃｅｌ在回歸預測應用中，其分析計算簡單、準確，從而使統計預測分析更具實用價值。

主要參考文獻

［1］ 劉蘭娟. 財經管理中的計算機應用［M］. 上海：上海財經大學出版社，２００４.