林建華

摘 要： EXCEL是一款功能非常強大的辦公軟件，文章利用其內(nèi)置的公式和函數(shù)給出了主成分回歸分析的完整算法和詳細過程，得到的計算結(jié)果與專業(yè)統(tǒng)計軟件給出的相同。因此，應(yīng)用EXCEL實現(xiàn)主成分回歸分析是可行的。

關(guān)鍵詞： 主成分分析； 多元回歸分析； EXCEL

中圖分類號： O 245 文獻標志碼： A 文章編號： 1671-2153（2015）05-0097-05

0 引 言

主成分回歸是一種利用主成分作回歸的方法，它既可以消除變量間的多重共線性，又能夠保留全部的解釋變量，回歸方程的擬合效果也能夠得到提高。主成分回歸分析運算量較大，一般需要用專業(yè)統(tǒng)計軟件來處理。這在一定程度上限制了不熟悉專業(yè)軟件的使用者對主成分回歸分析的實際應(yīng)用。EXCEL是目前一款非常流行的實用辦公軟件，它不但具有功能強大的電子表格，而且還包含大量的函數(shù)來供數(shù)據(jù)分析和數(shù)據(jù)運算，以解決研究或?qū)嶋H工作中的問題。目前，雖然有應(yīng)用EXCEL做主成分分析和多元線性回歸的一些公開文獻[1-3]，但尚未發(fā)現(xiàn)有利用EXCEL做主成分回歸分析的具體算例及完整的實現(xiàn)過程。鑒于EXCEL使用的普遍性和廣泛性，本文試圖利用文獻[4]提供的算例對應(yīng)用EXCEL實現(xiàn)主成分回歸分析的主要步驟和過程作詳細的闡述。

1 基本原理

1.1 確定主成分

設(shè)有n個樣品，每個樣本觀測p個指標，原始數(shù)據(jù)矩陣為x=（x1，x2，…，xp），將原始數(shù)據(jù)標準化，可得

X=X11 X12 … X1pX21 X22 … X2p… … … …Xn1 Xn2 … Xnp=（X1，X2，…，Xp）。 （1）

計算標準化數(shù)據(jù)矩陣X的相關(guān)矩陣R=（rij）p×p，解特征方程|R-λI|=0，可得到R的特征根λ1≥λ2≥…λp≥0，在此基礎(chǔ)上分別求出對應(yīng)于特征值λi（i=1，2，…，p）的單位特征向量αi=（α1i，α2i，…，αpi）。最后，求得p個主成分Fi=α1iX1+α2iX2+…+αpiXp（i=1，2，…，p）。

1.2 進行回歸分析

用標準化的原變量觀測數(shù)據(jù)計算前m（mY=θ0+θ1F1+…+θmFm， （2）

由于主成分Fi（i=1，2，…，m）是標準化變量X1，X2，…，Xp表示的方程，所以再把m個主成分Fi代入式（2）得到：

Y=β0+β1X1+…+βpXp， （3）

其中β0=θ0，βi=θ1αi1+θ2αi2+…+θmαim（i=1，2，…，p）。

對回歸方程進行統(tǒng)計檢驗，包括擬合優(yōu)度檢驗（R2檢驗）、方程總體線性顯著性檢驗（F檢驗）和變量的顯著性檢驗（t檢驗）。

將式（3）中的X1，X2，…，Xp和Y還原為用原始變量x1，x2，…，xp和y來表示，即得到用原始變量表示的回歸方程：

y=b0+b1x1+…+bpxp。 （4）

值得注意的是，只有在原變量間存在較強的多重共線性時，采用主成分回歸才會收到比較好的效果，而不是在任何情況下做主成分回歸都有滿意的效果，每種方法都有它的適用范圍和局限性。

2 EXCEL實現(xiàn)過程

在EXCEL工作表Sheet1的單元格區(qū)域A1：E12，輸入文獻[4]所提供的原始數(shù)據(jù)，如表1所示。為了確保計算結(jié)果的精度，本文約定除在圖表和有關(guān)方程式中只顯示數(shù)字的幾位小數(shù)外，中間運算過程中的數(shù)字盡可能保留多位小數(shù)。

2.1 原始數(shù)據(jù)的標準化處理

按式（5）對表1提供的原始數(shù)據(jù)x1，x2，x3和y進行中心標準化處理，即

Xij=（xij-j） Yij=（yij-j） 。 （5）

打開EXCEL工作表Sheet1，在單元格F2中輸入公式：“=（B2-AVERAGE（B2：B12））/SQRT（DEVSQ（B2：B12））”，按回車鍵確認，單元格F2中就會顯示數(shù)值-0.4774。然后，用鼠標按住F2右下角垂直下拖至F12單元格，便可以生成自變量x1的中心標準化數(shù)值X1。同理，可得到自變量x2和x3及因變量y的中心標準化數(shù)值X2，X3和Y，如表2所示。

2.2 計算樣本相關(guān)系數(shù)矩陣

單擊“數(shù)據(jù)”菜單中的“數(shù)據(jù)分析”，在彈出的“數(shù)據(jù)分析”窗口中選擇“相關(guān)系數(shù)”，點擊“確定”后，打開對話框，在“輸入?yún)^(qū)域（I）：”輸入“F2：H12”，在“輸出區(qū)域（O）：”輸入“F15：H17”，點擊“確定”按鈕，就會在輸出區(qū)域給出相關(guān)系數(shù)矩陣的下三角的數(shù)值。相關(guān)系數(shù)矩陣系對稱矩陣，上三角的數(shù)值與下三角的數(shù)值相等，可以采用復(fù)制、選擇性粘貼、轉(zhuǎn)置在另外單元格區(qū)域生成上三角的數(shù)值，之后，再把上三角的數(shù)值復(fù)制粘貼到相關(guān)系數(shù)矩陣的上三角的空白部分，計算結(jié)果如表3所示。

為方便以后的運算與使用，需要對EXCEL中所建立的相關(guān)系數(shù)矩陣A和單位矩陣I進行命名。選定相關(guān)系數(shù)矩陣所在的單元格區(qū)域G16：I18，單擊編輯欄左端“名稱”欄，輸入自定義名稱“A”后按回車鍵確認，以后在當前工作簿的所有工作表中，名稱“A”就是指單元格區(qū)域G16：I18。同理，在單元格區(qū)域G23：I25建立3階單位矩陣，并將其命名為“I”。

2.3 計算相關(guān)系數(shù)矩陣A的特征值及其單位特征向量

求解特征方程|A-λI|=0，可得到A的3個非負的特征值λ1、λ2、λ3，且λ1≥λ2≥λ3≥0。特征值λi（i=1，2，3）是主成分的方差。由Gersgorin圓盤定理[5]可知，λ1、λ2、λ3落在區(qū)間[0，2]內(nèi)。用EXCEL求解λi（i=1，2，3）分兩步，先確定每個特征值所在的小區(qū)間，然后利用“規(guī)劃求解”命令求出每個特征值的更精確的近似值。

操作步驟如下：在單元格A1輸入0，A2輸入0.001（視求解精度而定），用鼠標按住A1：A2右下角垂直下拖至A20001單元格，在單元格B1輸入公式：“=MDETERM（A-A1×I）”，用鼠標按住B1右下角垂直下拖至B20001單元格。觀察B1：B20001單元格區(qū)域數(shù)值的正負符號的變化情況，每一次符號改變都說明這兩個返回值之間有一個特征值。據(jù)此可得3個特征值λi（i=1，2，3）所在的小區(qū)間為[0.002，0.003]，[0.9981，0.9982]和[1.9991，0.9992]。單擊“數(shù)據(jù)”菜單中的“規(guī)劃求解”命令，打開“規(guī)劃求解參數(shù)”對話框，在“設(shè)置目標：（T）”中輸入“B1”，點擊“目標值：（V）輸入“0”，在“通過更改可變單元格：（B）中，輸入或選擇單元格區(qū)域“A1：A3”，單擊“添加（A）”按鈕，在“遵守約束：（U）欄中，分別添加條件：A1>=0.002，A1<=0.003，A2>=0.9981，A2<=0.9982，A3>=1.9991，A3<=1.9992，B2=0，B3=0，單擊“求解（S）”按鈕完成操作。此時單元格A1，A2，A3中的值分別顯示為：0.0026908908，0.9981541760，1.9991549345，它們就是相關(guān)系數(shù)矩陣A的3個特征值，即λ1=1.999155，λ2=0.998154和λ3=0.002691。

對應(yīng)于λi（i=1，2，3）的特征向量可由下面的齊次方程組解出：

（r11-λi）ψ1+r12ψ2+r13ψ3=0r11ψ1+（r12-λi）ψ2+r13ψ3=0r11ψ1+r12ψ2+（r13-λi）ψ3=0， （6）

將相關(guān)矩陣A的值和λi值代入方程組，并令ψ3=1，把方程組左邊的最后一項移到等式的右邊作為常數(shù)項，用前面的2個方程聯(lián)立求解得到齊次方程的一組解，對解向量進行標準化可得到特征值λi對應(yīng)的單位特征向量。

下面以λ1=1.999155為例，采用逆矩陣方法求解特征向量，詳細過程如下（見表4）。

（1） 輸入系數(shù)矩陣和常數(shù)項矩陣。將λ1代入方程組，在單元格區(qū)域A1：B2輸入系數(shù)矩陣，在區(qū)域C1：C2輸入常數(shù)矩陣。

（2） 求系數(shù)矩陣的逆矩陣。在輸入系數(shù)矩陣和常數(shù)矩陣后，選取單元格區(qū)域A5：B6，在活動單元格輸入公式：“=MINVERSE（A1：B2）”，按下Ctrl+Shift+Enter鍵，區(qū)域A5：B6就會顯示系數(shù)矩陣的逆矩陣區(qū)域。

（3） 求齊次方程的一組解。選取單元格區(qū)域D5：D6，在活動單元格輸入公式：“=MMULT（A5：B6，C1：C2）”，按下Ctrl+Shift+Enter鍵，單元格D5和D6分別顯示數(shù)字“0.9997”和“0.0616”，并在單元格D7輸入“1”，這樣就得到齊次方程的一組解。

（4） 對解向量進行標準化。利用單位化公式αi=ψi/對解向量進行標準化，得到單位化特征向量αi=（α1i，α2i，α3i）。在單元格E5輸入：“=D5/SQRT（SUMSQ（D5：D7））”，按下Ctrl+Shift+Enter鍵，用鼠標按住E5右下角垂直下拖至E7單元格，可以得到λ1的單位特征向量為α1=（0.7063，0.0435，0.7065）。依此類推，將λ2和λ3代入方程組，改變A1：B2主對角線單元格的值，求得其他兩個特征值所對應(yīng)的單位特征向量。即λ2的單位特征向量為α2=（-0.0357，0.999，-0.0258）；λ3的單位特征向量為α3=（-0.7070，-0.0070，0.7072）。值得注意的是，EXCEL中特征向量的正負號選擇是一個比較重要的問題，具體如何確定可參考文獻[6]，本文恕不贅述。

2.4 計算方差貢獻率和累計貢獻率

第k（k=1，2，3）個主成分的方差貢獻率為

λk λj，前m個主成分的累計貢獻率為λj λj。一般情況下，若m個主成分的累計貢獻率超過85%，就認為m個主成分基本包含了原來指標的信息，即可以取前m個主成分進行分析和評價。方差貢獻率的計算比較簡單，如第1主成分的方差貢獻率為1.9991/3=66.64%，前2個主成分的方差累計貢獻率（1.9991+0.9981）/3=99.91%，因前2個主成分的方差累計貢獻率為99.91%（>85%），故保留前2個主成分，而把第3個主成分剔除。

2.5 確定主成分表達式及計算其得分

根據(jù)λ1和λ2對應(yīng)的單位特征向量，可得到以下2個主成分：

F1=0.7063X1+0.0435X2+0.7065X3F2=-0.0357X1+0.999X2-0.0258X3。 （7）

將λ1和λ2對應(yīng)的單位特征向量，輸入單元格區(qū)域J3：J5和J9：J11。在單元格K2輸入公式：“=MMULT（F2：H12，J3：J5）”，按下Ctrl+Shift+Enter鍵，用鼠標按住K2右下角垂直下拖至K12單元格，可得到第一主成分F1的得分。同理，可在單元格區(qū)域L2：L12得到第二主成分F2的得分，計算主成分F1和F2得分，如表5所示。

2.6 建立主成分回歸模型

單擊“數(shù)據(jù)”菜單中的“數(shù)據(jù)分析”命令，選中“數(shù)據(jù)分析”這一選項框，在分析工具（A）中選擇“回歸”，單擊“確定”按鈕。彈出“回歸”對話框，在“Y值輸入?yún)^(qū)域（Y）：輸入“I2：I12”

在“X值輸入?yún)^(qū)域（X）：輸入“K2：L12”，再對其他一些選項進行適當選擇，單擊“確定”按鈕完成操作（見圖1）。輸出的線性回歸結(jié)果如圖2所示。

對回歸方程進行統(tǒng)計檢驗。從圖2的輸出結(jié)果可知，R2=0.9883，R2=0.9854，兩者都接近于1。這表明自變量和因變量的線性相關(guān)性較強。F分布和t分布的臨界值不用查表，可以用EXCEL的函數(shù)生成。在顯著性水平α=0.05下，F(xiàn)α（2，8）=FINV（0.05，2，8）=4.46，F(xiàn)=337.23>Fα（2，8），t（8）=TINV（0.05，8）=2.306，t1=25.486，t2=4.993>t（8），回歸方程通過R2檢驗、F檢驗和t檢驗。

從圖2提供的回歸結(jié)果可知，回歸系數(shù)估計值為θ1=0.690，θ2=0.191，常數(shù)項θ0≈0，得到回歸方程為

Y=0.69F1+0.191F2-3.783E-17， （8）

將F1和F2代入式（8），可得：

Y=0.4804X1+0.2211X2+0.4826X3， （9）

將式（5）代入式（9），可得：

=0.4804（）+

0.2211（）+0.4826（），（10）

對式（10）進行整理，可得以原始變量表示的回歸方程：

y=-9.1301+0.07278x1+0.60922x2+0.10626x3。（11）

這一結(jié)果與SAS[7]和Visual Basic[8]編程得到的計算結(jié)果完全相同，與SPSS[9]處理結(jié)果也近似相等。這就驗證了上述利用EXCEL做主成分回歸的方法是正確的，得到的結(jié)果是可靠的。

3 結(jié)束語

主成分回歸計算過程較復(fù)雜，運算量較大。EXCEL是一款最為常用的數(shù)據(jù)處理軟件，功能非常強大，完全可以滿足主成分回歸分析的計算需要，前面的計算例子已經(jīng)驗證了這一點。因此，對于一些不熟悉專業(yè)統(tǒng)計軟件的使用者來說，利用EXCEL做主成分回歸具有極大的便利性。

參考文獻：

[1] 林澤陽. 主成分分析法及其EXCEL實現(xiàn)[J]. 寧波職業(yè)技術(shù)學(xué)院學(xué)報，2012，16（5）：24-28.

[2] 王旭輝，敖運忠. Exce1 2000多元線性回歸在體育教學(xué)中的應(yīng)用[J]. 上饒師范學(xué)院學(xué)報，2005，25（6）：117-120.

[3] 高平. EXCEL在多元線性回歸分析中的應(yīng)用[J]. 青海統(tǒng)計，2006（12）：27-29.

[4] 王松桂，史建紅，尹素菊，等. 線性模型引論[M]. 北京：科學(xué)出版社，2004：187.

[5] 滕常春. 利用Gersgorin圓盤定理判定特征值的范圍[J].濰坊學(xué)院學(xué)報，2014，14（2）：103-104.

[6] 許淑娜，李長坡. 對主成分分析法三個問題的剖析[J]. 數(shù)學(xué)理論與應(yīng)用，2011，31（4）：116-121.

[7] 高惠璇. 處理多元線性回歸中自變量共線性的幾種方法[J]. 數(shù)理統(tǒng)計與管理，2000，20（5）：49-55.

[8] 郎云雯，段美英，趙全燕，等. 關(guān)于主成分回歸分析程序的研究[J]. 統(tǒng)計與決策，2013（18）：75-77.

[9] 郭呈全，陳希鎮(zhèn). 主成分回歸的SPSS實現(xiàn)[J]. 統(tǒng)計與決策，2011（5）：157-159.