Excel軟件在回歸分析教學(xué)中的應(yīng)用

劉力平

（常德市二中 湖南常德 415500）

回歸分析是高中數(shù)學(xué)必修3、選修1-2以及選修2-3的學(xué)習(xí)內(nèi)容，數(shù)學(xué)必修3之后，學(xué)生已經(jīng)學(xué)習(xí)了兩個變量之間的相關(guān)關(guān)系，包括畫散點圖、最小二乘法求回歸直線方程等內(nèi)容。在人教A版選修1-2、2-3《回歸分析的基本思想及其初步應(yīng)用》這一節(jié)中進一步介紹回歸分析的基本思想及其初步應(yīng)用。這部分內(nèi)容包括：線性回歸模型的數(shù)學(xué)表達式，隨機誤差項產(chǎn)生的原因，使學(xué)生能正確理解回歸方程的預(yù)報結(jié)果，并能從殘差分析角度討論回歸模型的擬合效果，從相關(guān)系數(shù)、相關(guān)指數(shù)角度探討回歸模型的擬合效果，以及建立回歸模型的基本步驟，介紹兩個變量非線性相關(guān)關(guān)系等。

一、回歸分析的基本思想及其步驟

回歸分析在經(jīng)濟學(xué)、統(tǒng)計學(xué)、遺傳學(xué)及日常生活中應(yīng)用得非常廣泛。例如，某種農(nóng)作物的產(chǎn)量和溫度有密切的關(guān)系，但產(chǎn)量不僅與溫度有關(guān)，還和日照時間、濕度等有關(guān)。又如，人的身高和體重是相關(guān)的，但影響人的身高還有其他的因素。像這樣的兩個變量之間的關(guān)系我們稱為相關(guān)關(guān)系，顯然相關(guān)關(guān)系是一種不確定的關(guān)系，那么這種關(guān)系怎樣用函數(shù)關(guān)系去表示呢？表示出來后的擬合程度怎樣？這就是回歸分析的內(nèi)容。其方法過程可分為以下幾個步驟：

1.畫出兩個相關(guān)變量的散點圖；

2.根據(jù)散點圖的大致形狀，選擇回歸模型，若是線性相關(guān)關(guān)系的，可利用最小二乘法

3.對求得的回歸方程的可信度進行檢驗，求出解釋變量對預(yù)報變量變化的貢獻率R2，畫出殘差圖，并進行殘差分析。

二、Excel在回歸分析教學(xué)中的應(yīng)用

由于回歸分析的應(yīng)用性和實踐性很強，因此計算量大，如果采用常規(guī)的分析和手工計算或者計算器計算,不僅教師難教，學(xué)生也感覺枯燥。針對這種情況，筆者在教學(xué)中有意加強了Excel軟件的應(yīng)用，提高學(xué)生利用計算機進行數(shù)據(jù)分析處理的操作技能，不僅教學(xué)過程輕松，學(xué)生也取得了不錯的學(xué)習(xí)效果。

下面以教材選修2-3中例1為例，詳細介紹在教學(xué)中用Excel做回歸分析的方法與步驟。

例1.從某大學(xué)中隨機選取8名女大學(xué)生。其身高和體重數(shù)據(jù)如下表所示：

編 號 1 2 3 4 5 6 7 8身高/cm 165 165 157 170 175 165 155 170體重/kg 48 57 50 54 64 61 43 59

（一） 繪制散點圖

1.打開Excel軟件，在表格中輸入身高和體重的數(shù)據(jù)（圖1）

▲圖1

2.選定身高與體重的全部數(shù)據(jù)，在插入菜單中打開圖表（H）彈出如下對話框（圖2），在圖表類型中選擇XY散點圖，點擊2次下一步。

▲圖2

3.彈出圖表選項對話框（圖3），在標(biāo)題選項中輸入圖表標(biāo)題名稱：散點圖，在數(shù)值（x）軸、數(shù)值（y）軸中分別輸入x軸和y軸，再單擊下一步、完成，彈出散點圖（圖4）。

▲圖3

▲圖4

4.雙擊圖表（圖4） 的坐標(biāo)區(qū)域，彈出圖表區(qū)格式對話框（圖5），可以對圖表繪圖區(qū)顏色、字體大小進行設(shè)置。雙擊擊圖表（圖4）的x、y軸，彈出x、y軸格式對話框（圖6），可以選取適當(dāng)?shù)目潭葐挝弧⒆钪档?。如x軸格式對話框中最小值和最大值根據(jù)數(shù)據(jù)特點可以分別設(shè)為145和180，主要刻度單位可以選擇5；y軸格式對話框中的最小值可以選擇40，最大值選擇70。

▲圖5

▲圖6

5.完成圖表（圖7）：

▲圖7

（二）回歸直線方程的建立及擬合系數(shù)R2求法

觀察散點圖，讓學(xué)生判斷散點分布是否具有線性趨勢。通過散點圖可以判斷，散點是呈條狀分布的，說明身高和體重有比較好的線性相關(guān)關(guān)系，因此選擇用回歸直線y=bx+a來近似刻畫它們之間的關(guān)系。

接下來，用Excel來求回歸方程，步驟如下：

1.選中散點，點擊鼠標(biāo)右鍵，選擇添加趨勢線，彈出添加趨勢線對話框，在選項卡中選擇線性（圖8），點擊確定即可得到擬合直線（圖 9）。

▲圖8

▲圖9

2.雙擊直線，彈出趨勢線對話框，點擊選項，選擇顯示公式和顯示R平方值（圖10），點擊確定，即可得到回歸直線的方程以及R2=0.6376， （圖 11）。

▲圖10

▲圖11

這就是所求回歸直線的方程，b=0.8485是回歸方程斜率的估計值，說明身高每增加一個單位，體重y就會增加0.8485個單位，這說明體重和身高具有正的線性相關(guān)關(guān)系。R2=0.6376為擬合系數(shù)，表示解釋變量與預(yù)報變量的相關(guān)關(guān)系，R2越接近1，表示回歸的效果越好，R2越接近0，模型的擬合效果越差。圖中表明“女大學(xué)生的身高解釋了64%的體重變化”或者“女大學(xué)生的體重差異有64%是由身高引起的”。

（三）殘差分析

1.將自變量X的數(shù)值由小到大的順序進行排列，將圖1中的數(shù)據(jù)重新進行排序，先選中編號、身高X、體重Y在內(nèi)的所有數(shù)據(jù)。然后打開數(shù)據(jù)菜單，選擇排序（S），彈出排序?qū)υ捒颍▓D12），在主要關(guān)鍵字中選擇“身高X”，（其余不用選），選中升序排列。最后點擊確定，得到身高從小到大排列好的數(shù)據(jù)（圖13）。

▲圖12

▲圖13

2.在工具菜單選項中選擇數(shù)據(jù)分析選項，出現(xiàn)數(shù)據(jù)分析對話框（圖14），在對話框中選擇回歸，點擊確定，彈出回歸對話框。

▲圖14

3.在輸入選項中，首先輸入Y值區(qū)域：$D$2:$D$9和X值區(qū)域：$C$2:$C$9，也可以用鼠標(biāo)在工作表中直接選中，然后選擇置信度95%。在輸出選項中，選擇新工作表組，并命名。殘差選項中選擇殘差和殘差圖。如圖15所示：

▲圖15

4.點擊確定，在工作表中取得殘差分析結(jié)果，如圖16紅色標(biāo)注部分。此時得到的殘差值和殘差圖是按自變量X的升序排列的。

▲圖16

5.將得到的殘差數(shù)據(jù)復(fù)制到工作表中，然后重新進行排序，再分別畫出以編號、殘差為變量散點圖(圖17)。

▲圖17

三、小結(jié)

在回歸分析教學(xué)中，Excel是很好的教學(xué)工具軟件，利用這個工具軟件，我們可以有效地解決回歸分析教學(xué)中的難點問題，取得事半功倍的效果。同時，我們可以在教學(xué)過程中讓學(xué)生自己動手操作，培養(yǎng)學(xué)生的動手能力，提高學(xué)生的信息技術(shù)素養(yǎng)，激發(fā)學(xué)生的學(xué)習(xí)興趣。

[1]王黎明.應(yīng)用回歸分析[M].復(fù)旦大學(xué)出版社，2008.

[2]董鳳鳴,周萍.excel在一元線性回歸分析中的應(yīng)用[J].科技信息，2007 （12）：144-145.

[3]張海燕.解一元線性回歸問題[J].天津農(nóng)學(xué)院學(xué)報，2002（4） .