第8章 回歸分析 8.1 線性回歸分析的基本原理 8.2 圖表分析與回歸函數分析 8.3 Excel回歸分析工具 8.4 多元回歸分析 第8章 回歸分析 8.1 線性回歸分析的基本原理 8.2 圖表分析與回歸函數分析 8.3 Excel回歸分析工具 8.4 多元回歸分析 8.5 非線性回歸分析

本章學習目標 u 回歸分析的基本思想 u 利用Excel圖表進行線性回歸分析 u利用Excel回歸分析工作表函數進行線性回歸分析

8.1 線性回歸分析的基本原理 8.1.1 回歸分析的概念 8.1.2 回歸分析的主要內容 返回首頁

8.1.1 回歸分析的概念 首先要區分兩種主要類型的變量︰一種變量相當于通常函數關係中的自變量，對這樣的變量能夠賦予一個需要的值（如室內的溫度、施肥量）或者能夠取到一個可觀測但不能人為控制的值（如室外的溫度），這樣的變量稱為自變量；自變量的變化能引起另一些變量（如水稻畝產量）的變化，這樣的變量稱為因變量。

由一個或一組非隨機變量來估計或預測某一個隨機變量的觀測值時，所建立的數學模型及所進行的統計分析，稱為回歸分析。因此，回歸分析是研究隨機變量與非隨機變量之間的數量關係的一種數學方法。如果所建立的模型是線性的就稱為線性回歸分析。線性回歸分析不僅告訴我們怎樣建立變量間的數學表達式，即經驗公式，而且還利用機率統計知識進行分析討論，判斷出所建立的經驗公式的有效性，從而可以進行預測或估計。 返回本節

8.1.2 回歸分析的主要內容 回歸分析的內容包括如何確定因變量與自變量之間的回歸模型；如何根據樣本觀測數據，估計並檢驗回歸模型及未知參數；在眾多的自變量中，判斷哪些變量對因變量的影響是顯著的，哪些變量的影響是不顯著的；根據自變量的已知值或給定值來估計和預測因變量的值。 Excel提供了許多回歸分析的方法與工具，它們可用于不同的分析目的。 返回本節

8.2 圖表分析與回歸函數分析 8.2.1 利用圖表進行分析 8.2.2 Excel中的回歸分析工作表函數 8.2 圖表分析與回歸函數分析 8.2.1 利用圖表進行分析 8.2.2 Excel中的回歸分析工作表函數 8.2.3 利用工作表函數進行回歸分析 返回首頁

8.2.1 利用圖表進行分析 例8-1 某種合成纖維的強度與其拉伸倍數之間存在一定關係，圖8-1所示（“線性回歸分析”工作表）是實測12個纖維樣品的強度y與相應的拉伸倍數x的數據記錄。試求出它們之間的關係。 （1）打開“線性回歸分析”工作表。 （2）在工具欄上選擇“圖表向導”按鈕，單擊打開圖表向導對話框，如圖8-2所示，在“圖表類型”列表框中選擇“XY散點圖”，單擊“下一步”按鈕進入圖表向導步驟2。

（3）在圖表向導步驟2對話框的“數據區域”中輸入“B2:C13”，選擇“系列產生在”為“列”，如圖8-3所示，單擊“下一步”按鈕進入步驟3。 （4）在圖表向導步驟3的對話框中，打開“圖例”頁面，取消“顯示圖例”，省略標題，如圖8-4所示。 （5）單擊“完成”按鈕，得到XY散點圖如圖8-5所示。 （6）在散點圖中，把鼠標放在任一數據點上，右擊，在快捷菜單中選擇“添加趨勢線”，打開趨勢線對話框。 （7）在“添加趨勢線”對話框中打開“類型”頁面，選擇“線性”選項，在“選項”頁面中選擇“顯示公式”和“顯示R平方”選項，單擊“確定”按鈕，得到趨勢回歸圖，如圖8-6所示。

圖8-1 “線性回歸分析.xls”工作表

圖8-2 圖表向導（步驟1）

圖8-3 圖表向導（步驟2）

圖8-4 圖表向導（步驟3）

圖8-5 XY散點圖

圖8-6 趨勢回歸直線 返回本節

8.2.2 Excel中的回歸分析工作表函數 Excel提供的回歸分析工作表函數主要有以下幾個︰ （1）截距函數。 （2）斜率函數。 （3）測定系數函數。 （4）估計標準誤差函數。

（1）截距函數。 其功能是利用現有的x值與y值計算直線與y軸的截距。截距為穿過已知的known_x's和known_y's數據點的線性回歸線與y軸的交點。當自變量為0時，使用INTERCEPT函數可以決定因變量的值。例如，當所有的數據點都是在室溫或更高的溫度下取得的，可以用INTERCEPT函數預測在0°C時金屬的電阻。 語法︰INTERCEPT(known_y's,known_x's)

圖8-7 x、y數據

圖8-8 計算截距

（2）斜率函數。 該函數返回根據known_y's和known_x's中的數據點擬合的線性回歸直線的斜率。斜率為直線上任意兩點的垂直距離與水準距離的比值，也就是回歸直線的變化率。 語法︰SLOPE (known_y's,known_x's) 其中︰Known_y's為數字型因變量數據點數組或單元格區域；Known_x's為自變量數據點集合。

（3）測定系數函數。 （3）測定系數函數。 該函數返回根據known_y's和known_x's中數據點計算得出的乘積矩相關係數的平方。R平方值可以解釋為y方差與x方差的比例。 語法︰RSQ(known_y's,known_x's)

回歸直線的斜率計算公式如下︰

圖8-9 計算斜率

（4）估計標準誤差函數。 該函數返回透過線性回歸法計算每個x的y預測值時所產生的標準誤差。標準誤差用來度量根據單個x變量計算出的y預測值的誤差量。 語法︰STEYX(known_y's,known_x's) 其中︰Known_y's為因變量數據點數組或區域，Known_x's為自變量數據點數組或區域。

預測值y的標準誤差計算公式如下︰ 返回本節

8.2.3 利用工作表函數進行回歸分析 例8-4 在某大學一年級新生體檢表中隨機抽取10張，得到10名大學生的身高（x）和體重（y）的數據，如圖8-10（“身高體重”工作表）所示。 用Excel提供的工作表函數進行相關計算。 （1）在單元格A12~A15中分別輸入“截距”、“斜率”、“測定系數”、“估計標準誤差”。 （2）在單元格B12中輸入公式“=INTERCEPT(C2:C11,B2:B11)”，返回后顯示-79.42015。 （3）在單元格B13中輸入公式“=SLOPE(C2:C11,B2:B11)”，返回后顯示0.8041825。 （4）在單元格B14中輸入公式“=RSQ(C2:C11,B2:B11)”，返回后顯示0.6817018。 （5）在單元格B15中輸入公式“=STEYX(C2:C11,B2:B11)”，返回后顯示2.8180738。計算結果如圖8-8所示。

圖8-10 “身高體重”工作表

圖8-11 “身高體重”回歸計算結果 返回本節

8.3 Excel回歸分析工具 8.3.1 回歸分析工具的主要內容 8.3.2 回歸分析工具的應用 8.3.3 回歸分析工具的輸出解釋 8.3.1 回歸分析工具的主要內容 8.3.2 回歸分析工具的應用 8.3.3 回歸分析工具的輸出解釋 返回首頁

8.3.1 回歸分析工具的主要內容 回歸分析工具是透過對一組觀察值使用“最小平方法”進行直線擬合，以分析一個或多個自變量對單個因變量的影響方向與影響程度的方法。它是Excel中數據分析工具的一個內容。

在“工具”菜單中選擇“數據分析”選項，會出現“數據分析”對話框，在分析工具中選擇“回歸”，單擊“確定”按鈕就會進入“回歸”對話框，如圖8-12所示。在此對話框中主要包括以下內容︰ Y值輸入區域︰ X值輸入區域︰ 標誌︰ 置信度︰ 常數為零︰

輸出區域︰ 新工作表組︰ 新工作簿︰ 殘差︰ 標準殘差︰ 殘差圖︰ 線形擬合圖︰ 正態機率圖︰ 返回本節

8.3.2 回歸分析工具的應用 例8-5 以例8-4資料為例，利用回歸分析工具進行回歸分析。 （1）打開“身高體重”工作表。 8.3.2 回歸分析工具的應用 例8-5 以例8-4資料為例，利用回歸分析工具進行回歸分析。 （1）打開“身高體重”工作表。 （2）在“工具”菜單中選擇“數據分析”選項，在“分析工具”列表中選擇“回歸”，單擊“確定”按鈕，打開“回歸”對話框。 （3）在“Y值輸入區域”中輸入“$C$1: $C$11”，在“X值輸入區域”中輸入“$B$1: $B$11”；選擇“標誌”，置信度默認；在“輸出選項”中選擇“輸出區域”，在其右邊輸入“$D$1”，如圖8-13所示，單擊“確定”按鈕輸出結果，如圖8-14所示。

圖8-13 “回歸”對話框

圖8-14 回歸分析結果 返回本節

8.3.3 回歸分析工具的輸出解釋 Excel回歸分析工具的輸出結果包括3個部分︰ 1．回歸統計表 2．方差分析表 3．回歸參數表

（1）Multiple R（複相關係數R）︰ （2）R Square（複測定系數R2）︰ 回歸統計表包括以下幾部分內容︰ （1）Multiple R（複相關係數R）︰ （2）R Square（複測定系數R2）︰ （3）Adjusted R Square（調整複測定系數R2）︰ （4）標準誤差︰ （5）觀測值︰ 返回本節

8.4 多元回歸分析 例8-6 有一個工廠會計部門在估計每月管理費y時，用工人的勞動日數x1與機器的開工台數x2作自變量，現將當年10個月的數據搜集起來，如圖8-15（“多元回歸分析”工作表）所示，估計y對x1與x2的線性回歸方程(α=0.05)。 返回首頁

（1）在“工具”菜單中選擇“數據分析”選項，在“分析工具”列表中選擇“回歸”，單擊“確定”按鈕，打開“回歸”對話框。 （2）在“Y值輸入區域”中輸入“D1:D11”，在“X值輸入區域”中輸入“B1:C11”；選擇“標誌”，置信度默認；在“輸出選項”中選擇“輸出區域”，在其右邊輸入“A12”，單擊“確定”按鈕輸出結果，如圖8-16所示。

圖8-15 “多元回歸分析”工作表

圖8-16 二元線性回歸分析計算結果 返回本節

8.5 非線性回歸分析 以最小平方法分析非線性關係資料在數量變化上的規律叫做非線性回歸分析。從非線性回歸的角度看，線性回歸僅是其中的一個特例。一個恰當的非線性回歸方程的確定不是很容易的，一般要經過變量轉換，將非線性問題轉化為線性問題解決。下面討論幾種非線性方程線性化的情況。 返回首頁

1． （1）添加趨勢線。 （2）利用回歸分析工具。

表8-1 微量元素超標量與患病患數

圖8-17 添加對數趨勢線結果

圖8-18 “回歸”工具獲得的對數曲線模型擬合結果

2． 表8-2 氰化物濃度數據

圖8-19 添加指數趨勢線結果

8-20 “回歸”工具獲得的指數曲線模型擬合結果

3．

返回本節