Excel 在相關(guān)與回歸分析中的應(yīng)用

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

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

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

1 相關(guān)分析

1.1 問(wèn)題的提出

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

1.2 統(tǒng)計(jì)學(xué)理論與方法

相關(guān)分析是檢驗(yàn)衡量?jī)勺兞恐g關(guān)聯(lián)強(qiáng)度的分析過(guò)程。按相關(guān)關(guān)系的方向劃分，兩變量之間的相關(guān)關(guān)系可分為三種情況：（1）正相關(guān)。當(dāng)一個(gè)變量增加/減少時(shí)，另一個(gè)變量也相應(yīng)地增加/減少。（2）負(fù)相關(guān)。一個(gè)變量增加/減少時(shí)，另一個(gè)變量卻減少/增加。（3）無(wú)相關(guān)。兩變量是獨(dú)立的，互不影響。

1.3 應(yīng)用Excel 進(jìn)行相關(guān)分析

首先，做個(gè)說(shuō)明：論文中，軟件操作的表述均以Excel 2016 為藍(lán)本。

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

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

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

2 回歸分析

2.1 問(wèn)題的提出

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

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

2.2 統(tǒng)計(jì)學(xué)理論與方法

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

2.3 應(yīng)用Excel 進(jìn)行回歸分析與預(yù)測(cè)

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

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

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

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

相對(duì)來(lái)說(shuō)，散點(diǎn)圖法更簡(jiǎn)便、直觀，可以描述線性回歸關(guān)系，也可以描述非線性回歸關(guān)系，但只能進(jìn)行一元回歸分析。數(shù)據(jù)分析法適合一元回歸分析，也適合多元回歸分析，但只能進(jìn)行線性回歸分析。

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

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

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

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

3 結(jié)語(yǔ)

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

引用

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

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

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

[4] 張國(guó)鋒.Excel商務(wù)應(yīng)用與建模[M].北京:清華大學(xué)出版社,2009.