線性回歸模型的三種Ｅｘｃｅｌ構建方法分析及其比較

摘要：本文介紹了在Excel環境下的三種線性回歸模型的構建方法，分析了三種方法的優缺點，討論了各自的適用范圍，不同用戶可以根據自身特點和需要來選擇使用適合自己的方法。

關鍵詞：Excel；線性回歸；模型

中圖分類號：TP311文獻標識碼：A文章編號：1009-3044(2008)28-0242-02

Analyses of Three Kinds of Construction Methods for Linear Regression Model Based on Excel

ZHANG Jin

(Computer and Information Engineering College of Henan University， Kaifeng 475001， China)

Abstract: This paper offered there kinds of construction methods for linear regression model based on Excel and analyzed their application fields. Different users can chose appropriate method that adopt their character and needs.

Key words: excel; linear regression; model

1 引言

在自然界中，某一現象往往受多種因素的影響，如何在對現有數據進行分析的基礎上，找到因變量與自變量之間的關系，找出其內在規律是研究者關注的主要問題之一。線性回歸模型是實現上述目的的主要手段之一，如何根據已有數據構建線性回歸模型則是首先需要解決的問題。而一般情況下用于進行線性回歸分析的數據量和計算量都非常大，單純依靠手工方式分析計算是不太現實的，需要借助各種計算分析軟件來實現。Office辦公軟件是國內安裝使用最為廣泛的軟件，作為其套件之一，Excel界面友好，功能強大，且易學易用，是非專業人士進行數據分析的首選工具。但能熟練將Excel用于線性回歸分析的人士并不多。本文對Excel環境下線性回歸模型的構建加以分析，具體描述了三種模型構建方法并進行了比較。

2 Excel的三種線性模型構建方法

借助Excel特有的單元格之間的鏈接、函數及數據分析功能等，可以有效的解決諸如此類的相關問題。

2.1 手工模式的翻版

例：某一生產企業某年12個月的廣告費、銷售單價及銷售量如表1所示。

此企業各月的銷售量與銷售單價及廣告費之間存在一定的聯系，通過對三者之間關系的分析，可以有效地對未來企業的銷量進行預測，而進行預測的前提是首先構建三者之間的數據模型，從數據的初步分析可以發現三者之間存在明顯的線性關系，即：

y(銷售量)=a1*x1(銷售單價)+a2*x2(廣告費)+b

按照手工方式進行計算，需首先計算出相關的中間變量，然后帶入公式，得到相應的系數a1、a2及b的值。具體步驟如下：

1) 在A1：M4單元格依次錄入某企業12個月的銷售量、銷售單價及廣告費等原始數據；

2) 計算出中間結果∑x2、（∑x）2、∑y2、（∑y）2等；

3) 由此按照公式得到L11、L12、L22等；

4) 代入公式，計算出系數a1、a2及b的值（見圖1）。

2.2 利用Excel的函數功能

Excel中包含有大量實用的函數，可以借助INDEX、LINEST等函數對上述問題進行計算分析。同樣對于上例，步驟如下：

1) 如第一種方法的步驟一所示，首先錄入相關數據；

2) 利用INDEX、LINEST函數，進行計算（具體如圖2所示）。

a1參數的計算公式為：B6=INDEX(LINEST(C2:N13，C4:C5，TRUE，TRUE)，1，1)

a2參數的計算公式為：B7=INDEX(LINEST(C2:N13，C4:C5，TRUE，TRUE)，1，2)

b參數的計算公式為：B8=INDEX(LINEST(C2:N13，C4:C5，TRUE，TRUE)，1，3)

2.3 利用Excel的數據分析功能

Excel“工具”欄下的“數據分析”功能，對于構建此類線性回歸模型非常方便。如果Excel工具菜單中未加載此項功能，可以用過單擊“工具”下的“加載宏”，在出現的對話框中，選擇“分析工具庫”，單擊“確定”，完成此項功能的加載。如果已經有此項功能，可以按如下步驟完成相關計算：

1) 依次輸入例題中的相關數據（如圖2所示），尤其需要提醒的是此表中的數據必須按列輸入，否則此項功能無法實現，而前面兩種方法無論按行還是按列方式輸入均可實現；

2) 單擊“工具”菜單下的“數據分析”選項，選擇“回歸”功能，在對話框中依次輸入相關數據并選擇相關輸出內容，然后單擊“確定”，出現如圖3表格所示的數據。從中可以看到相關數據已經羅列出來。

3 對三種方法的比較

以上三種方法是構建線性回歸模型的常用方法，同時也是利用Excel解決諸如此類問題的一個有效途徑，三種方法各有優缺點，但都鮮明地體現了利用Excel進行數據分析時的特點。

3.1 三種計算方法的優缺點

第一種方法只是手工方法的照搬，此時Excel僅僅起了一個復雜計算器的作用。其中體現較為明顯的是Excel單元格之間公式的定義，借助單元格間關聯關系的界定，可以方便有效地完成相關計算。優點在于作為手工方式的“Excel化”，對于熟悉手工方式下線性回歸模式構建的人而言，可以較為容易地理解具體計算過程。此外，單元格間公式地定義相對來說也比較容易。但其缺點也是顯而易見的，這種方法并沒有完全脫離手工模式，只是通過Excel降低了計算的難度，而Excel特有的函數及相關功能并未加以利用。

第二種方法利用Excel函數，解決了回歸模型中有關參數計算的問題。Excel有著較為豐富的函數，大多數問題均可借助Excel函數加以解決。本方法的優點在于利用函數解決此類問題，便捷直觀，充分發揮了Excel強大的函數功能。通過此例可以充分說明利用Excel函數解決問題的高效性，尤其是通過不同函數的組合，可以解決許多貌似復雜的問題。本例就是利用INDEX、LINEX兩個函數的嵌套完成的，其不足在于Excel擁有函數多達幾百個，就是常用函數也有一百多個，如何對各類函數有效理解，進而熟練地加以運用，并不是每個人都能做到的，特別是對于非計算機專業人員而言，其難度可想而知。

第三種方法體現了Excel中“特殊功能”的用途，通過使用其中的“數據分析”模塊即可實現線性回歸模型的構建。優勢在于一是對于此類問題的解決，不需要了解詳細計算過程，只需選擇相應功能，就可以直接獲得計算結果。對于僅關心最后結果的人員來說，此方法是非常有效的；二是Excel以圖表方式給出了直觀的統計分析，通過相關設置可以得到用戶需要的各種統計結果。不足在于此功能的實現要求操作者應熟悉Excel的功能擴展途徑，了解此功能對于數據輸入的特殊要求，并在對話框中實現正確的設置；此外，還需具備一定的統計知識，并統計結果能有較為清晰的認識。相對來說，這些要求對于非專業人員是比較高的。

3.2 三種方法的適用范圍

第一種方法只是把Excel作為減少計算工作量的工具，其“內核”仍是手工模式，對于較為熟悉手工模式下線性回歸模型的構建，且了解Excel的一般用戶而言，此時可以減少大量計算的工作量，從而較為方便的得到相應結果。由于此種方法強調Excel單元格之間公式的定義、相互引用等，因此，也可作為介紹Excel此種功能時的例子使用。第二種方法更突出了Excel的函數功能，作為線性回歸模型構建的一種方法有著其較為特殊的適用范圍，對于那些較為熟悉Excel各種函數的參數、返回值及其適用范圍的用戶來說，這種方法不失為一種較好選擇。第三種方法充分體現了Excel強大的特定數據分析功能，可以便利的解決此類線性回歸建模問題，對于專業統計人員而言，不需了解計算過程，只需對相關的結果進行統計意義上的分析，因而該方法對此類人員較為適用。

參考文獻：

[1] Excel Home. Excel應用大全[M]. 北京：人民郵電出版社，2008.

[2] 于向輝，張國平. Excel在財會管理中的應用[M]. 北京：中國水利水電出版社，2004.

[3] 賈俊平. 統計學[M]. 北京：中國人民大學出版社，2003.