Excel 在相關與回歸分析中的應用

北京市石景山區(qū)業(yè)余大學 張俊榮

相關分析與回歸分析是研究現(xiàn)象之間數(shù)量關系的兩種重要的數(shù)據(jù)分析技術。Excel 軟件提供了數(shù)據(jù)分析的可視化操作界面，使相關與回歸分析工作簡單化。本文從實際問題入手，分析了相關分析與回歸分析的統(tǒng)計學理論與方法，并對如何應用Excel 軟件進行相關分析、回歸分析與預測進行了研究。

當今社會，人們越來越重視數(shù)據(jù)分析。擁有數(shù)據(jù)思維，分析數(shù)據(jù)間的邏輯關系，就會為組織帶來商業(yè)價值。因此，正確分析和運用數(shù)據(jù)，已經(jīng)成為職業(yè)人士必備的工作技能。數(shù)據(jù)分析曾經(jīng)是一項專業(yè)性很強的工作，工作任務繁重，需要具備統(tǒng)計學的功底，掌握經(jīng)濟學的理論，進行復雜的核算與分析。現(xiàn)在，Excel 軟件將煩瑣的數(shù)據(jù)分析核算過程進行了封裝，提供了可視化的操作界面，使數(shù)據(jù)分析工作簡單化。普通員工通過學習和訓練，使用Excel，也能進行數(shù)據(jù)分析，并進行經(jīng)濟預測。相關分析與回歸分析是數(shù)據(jù)分析的主要方法，兩者緊密結(jié)合，相互補充。相關分析只研究變量之間相關的方向和程度，不能推斷相關的具體形式，也無法從一個變量的變化來推測另一個變量，需要依靠回歸分析來表現(xiàn)相關的具體形式。而回歸分析則需要依靠相關分析來表現(xiàn)變量之間數(shù)量變化的相關程度，只有當變量之間存在高度相關時，進行回歸分析探尋相關的具體形式才有意義。因此，在實際應用中，為了達到分析和研究的目的，往往需要把相關分析和回歸分析結(jié)合起來使用。

1 相關分析

1.1 問題的提出

兩個變量之間是否相關，相關的程度如何？比如：水上世界的娛樂人數(shù)與溫度是否相關，關聯(lián)程度怎么樣？

1.2 統(tǒng)計學理論與方法

相關分析是檢驗衡量兩變量之間關聯(lián)強度的分析過程。按相關關系的方向劃分，兩變量之間的相關關系可分為三種情況：（1）正相關。當一個變量增加/減少時，另一個變量也相應地增加/減少。（2）負相關。一個變量增加/減少時，另一個變量卻減少/增加。（3）無相關。兩變量是獨立的，互不影響。

1.3 應用Excel 進行相關分析

首先，做個說明：論文中，軟件操作的表述均以Excel 2016 為藍本。

（1）散點圖法。在Excel 中，先選擇要分析的數(shù)據(jù)，然后點擊“插入”選項卡中“圖表”區(qū)的“插入散點圖(X、Y)或氣泡圖”，在彈出的列表中點擊“散點圖”，就得到了相關圖。相關圖反映了所選數(shù)據(jù)之間的相關性質(zhì)與相關方向，我們可以根據(jù)相關圖，分析所選數(shù)據(jù)的相關關系。（2）計算相關系數(shù)。我們可以使用Correl()函數(shù)計算相關系數(shù)。例如：某水上世界針對不同溫度對娛樂人數(shù)進行了統(tǒng)計，數(shù)據(jù)如圖1所示，要求：計算娛樂人數(shù)與溫度之間的相關系數(shù)。我們可以在D2 單元格中輸入“=CORREL(A2∶A10,B2∶B10)”，回車后便計算出了相關系數(shù)，計算結(jié)果為0.9945，說明娛樂人數(shù)與溫度之間存在高度正相關關系。

我們也可以使用Excel 的數(shù)據(jù)分析功能計算相關系數(shù)。點擊“數(shù)據(jù)”選項卡中的“數(shù)據(jù)分析”按鈕，在彈出的對話框中選擇“相關系數(shù)”，然后點擊“確定”按鈕，彈出“相關系數(shù)”對話框，在“輸入?yún)^(qū)域”中輸入“$A$1∶$B$10”，選擇“標志位于第一行”，在“輸出區(qū)域”，輸入“$D$4”，點擊“確定”按鈕，便計算出了相關系數(shù)，如圖1 所示。可見，計算結(jié)果與函數(shù)法相同。

圖1 統(tǒng)計數(shù)據(jù)與計算的相關系數(shù)結(jié)果Fig.1 Statistical data and calculated correlation coefficient results

2 回歸分析

2.1 問題的提出

某公司研發(fā)出了一種新氮肥，針對不同施肥量對農(nóng)作物產(chǎn)量的影響進行了試驗，試驗數(shù)據(jù)如圖2 所示。分析農(nóng)作物產(chǎn)量與施肥量之間的關系，并預測施肥量為5.2g 時農(nóng)作物的產(chǎn)量。

圖2 試驗數(shù)據(jù)與回歸分析結(jié)果Fig.2 Test data and regression analysis results

2.2 統(tǒng)計學理論與方法

回歸分析是一種預測性的建模技術，研究的是因變量和自變量之間的因果關系。例如，道路交通事故數(shù)量與司機的魯莽駕駛之間的因果關系。進行回歸分析，需先建立回歸模型，再根據(jù)實測數(shù)據(jù)求解模型的各個參數(shù)，然后評價回歸模型是否能夠很好地擬合實測數(shù)據(jù)。如果能夠很好地擬合，就可以根據(jù)自變量作進一步的預測。回歸分析最簡單的形式是一元線性回歸，它有一個因變量和一個自變量，因此可用線性方程y=ax+b 去擬合一系列變量x 和y 的觀測值。如果不借助于軟件，應用純數(shù)學的方法建立一元線性回歸模型，進行回歸分析，需要手工計算系數(shù)，做最小二乘估計，計算起來繁瑣復雜，做非線性回歸分析、多元回歸分析的工作量則更大，更容易出錯。

2.3 應用Excel 進行回歸分析與預測

Excel 的數(shù)據(jù)分析功能將數(shù)學模型和經(jīng)濟理論進行了封裝，為我們提供了簡單可視化的操作接口。類似于開車不需要了解發(fā)動機的原理，我們不需要做背后的數(shù)學建模與求解，不需要了解設計及生產(chǎn)過程，只需要借助Excel 的數(shù)據(jù)分析功能，通過可視化的操作界面，進行幾步簡單的操作，輸入數(shù)據(jù)，Excel 就能進行回歸分析，將計算分析結(jié)果呈現(xiàn)在我們面前。Excel 的數(shù)據(jù)分析功能使繁瑣的分析核算工作變得簡單，借助Excel 數(shù)據(jù)分析工具，普通員工也能做原來想做而無法完成的數(shù)據(jù)分析工作。下面，我們應用Excel 對農(nóng)作物產(chǎn)量與施肥量之間的關系進行回歸分析與預測。

（1）數(shù)據(jù)分析法。在Excel 中，點擊“數(shù)據(jù)”選項卡中的“數(shù)據(jù)分析”按鈕，在彈出的對話框中選擇“回歸”，然后點擊“確定”按鈕，彈出“回歸”對話框，在“Y 值輸入?yún)^(qū)域”輸入“$B$1∶$B$12”，在“X 值輸入?yún)^(qū)域”輸入“$A$1∶$A$12”，在輸出區(qū)域中輸出“$D$1”，然后點擊“確定”，Excel 進行計算后輸出分析結(jié)果，如圖2 所示。根據(jù)輸出結(jié)果，求出施肥量與產(chǎn)量之間的線性關系為Y=0.0229X+0.3427，擬合程度為0.5659，即56.59%，說明擬合得不夠好。相關系數(shù)為0.75，說明施肥量與產(chǎn)量之間中度線性正相關。將x=5.2 代入關系式，求得y=0.46，預測出農(nóng)作物的產(chǎn)量為0.46 公斤。

（2）散點圖法。在Excel 中，選擇施肥量與產(chǎn)量數(shù)據(jù)，然后點擊“插入”選項卡中“圖表”區(qū)的“插入散點圖(X、Y)或氣泡圖”，在彈出的列表中選“散點圖”，Excel系統(tǒng)就繪制出了施肥量與產(chǎn)量之間關系的散點圖，如圖3所示。右鍵點擊散點，在彈出的菜單中選擇“添加趨勢線”，在Excel 窗口右側(cè)出現(xiàn)“設置趨勢線格式”窗格，選擇“顯示公式”“顯示R 平方值”，系統(tǒng)就在圖中顯示出趨勢線方程y=0.0229x+0.3427、擬合程度R=0.5659。可見，散點圖法與數(shù)據(jù)分析法的計算結(jié)果是一致的。如果所有的點都在這條直線上，說明求出的線與原始數(shù)據(jù)擬合程度100%的好。觀察散點圖，發(fā)現(xiàn)大多數(shù)點都不在這條線上，說明擬合得不夠好。

圖3 施肥量與產(chǎn)量之間線性關系的散點圖Fig.3 Scatter diagram of linear relationship between fertilization and yield

相對來說，散點圖法更簡便、直觀，可以描述線性回歸關系，也可以描述非線性回歸關系，但只能進行一元回歸分析。數(shù)據(jù)分析法適合一元回歸分析，也適合多元回歸分析，但只能進行線性回歸分析。

（3）非線性回歸分析。從圖3 中散點變化的趨勢來看，產(chǎn)量與施肥量之間的關系更像一條拋物線。刪除直線趨勢線，添加“多項式”趨勢線。用二項式重新擬合，得到二次多項式趨勢線方程y=-0.0069x+0.0914x+0.2399 和擬合程度R=0.9609，如圖4 所示。很明顯，擬合程度從線性方程的57%提高到二次多項式的96%，對比圖3與圖4，可以看出，與線性方程相比，二次多項式方程更能成功地反映出觀察點的擬合程度。根據(jù)此回歸模型進行預測，將x=5.2 代入方程，求得y=0.53。預測出農(nóng)作物產(chǎn)量為0.53 公斤。

圖4 施肥量與產(chǎn)量之間非線性回歸分析的結(jié)果Fig.4 Results of nonlinear regression analysis between fertilization and yield

從經(jīng)濟學角度看，二次多項式擬合方程分為規(guī)模報酬遞增、規(guī)模報酬不變、規(guī)模報酬遞減三個階段，說明：氮肥做為一種生產(chǎn)要素，開始時，隨著施肥量的增加可以快速提高農(nóng)作物的產(chǎn)量，當施肥到達一定量后，產(chǎn)量增加的效果就不明顯了，如果過量施肥，反而會影響產(chǎn)量。

（4）多元線性回歸分析。影響農(nóng)作物產(chǎn)量的因素除了施肥量，還有土壤、水分和光照條件等。如果考慮這些因素，影響因子則不止一個，就需要做多元回歸分析，這時就無法應用散點圖的方法了，只能使用數(shù)據(jù)分析方法。但數(shù)據(jù)分析方法只適合于分析線性關系，如果是非線性關系，需要先轉(zhuǎn)化為線性關系。

3 結(jié)語

相關分析與回歸分析密不可分，是研究現(xiàn)象之間數(shù)量關系的兩種重要的數(shù)據(jù)分析技術。Excel 軟件將煩瑣的數(shù)據(jù)分析核算過程進行了封裝，提供了散點圖、相關系數(shù)函數(shù)、回歸分析工具等可視化的操作界面，使相關與回歸分析工作變得簡單。使用Excel 軟件，可以先對數(shù)據(jù)進行相關分析，如果數(shù)據(jù)之間高度相關，可進行回歸分析，深入探尋相關的具體形式，從而對公司的未來發(fā)展趨勢做出預測，為公司制定業(yè)務目標提供有效的戰(zhàn)略參考和決策依據(jù)，以確保公司的持續(xù)健康發(fā)展。

引用

[1] 朱小華,徐向東,馬玉鑫.統(tǒng)計學基礎[M].北京:中國人民大學出版社,2016.

[2] 楊雄,曾智.Excel在一元線性回歸分析中的應用[J].保山學院學報,2021,40(2):66-73.

[3] 葉峰.基于Excel的一元回歸分析教學的實踐[J].數(shù)學學習與研究,2019(2):123-124.

[4] 張國鋒.Excel商務應用與建模[M].北京:清華大學出版社,2009.