第十四章 迴歸

求算迴歸之方法 當兩變數間存有相關時，即可進行迴歸分析，通常可由一個自變數 （預測變項，X），來預測一個因變數（被預測變項，Y）。於Excel 中，要求算迴歸，可有下列幾種方法： 於繪圖結果中，切換到『圖表工具/版面配置』索引標籤，按『分析』群組之「趨勢線」鈕進行求算迴歸，此為最簡便之方式，且其可求算之迴歸種類也最多。 切換到『資料』索引標籤, 點選『分析』群組『資料分析』鈕,利用其「迴歸」分析工具求迴歸，可獲致很多相關之統計數字。如：相關係數、判定係數、以F檢定因變數與自變數間是否有迴歸關係存在、以t檢定各迴歸係數是否不為0、……。

繪圖中加入趨勢線--直線迴歸 假定，範例Ch14.xlsx『直線迴歸』工作表，收集了某一廠牌同一車型 中古車之車齡及其售價資料：

擬繪製其資料散佈圖，並求車齡對售價之迴歸方程式。其處理步驟為： 選取A1:B11之範圍 切換到『插入』索引標籤，點選『圖表』群組之『散佈圖』鈕，將顯示一下 拉式選單 選擇繪製『帶有資料標記的XY散佈圖』

切換到『圖表工具/設計』索引標籤，點選『圖表配置』群組內選取『版面配 置1』 可為圖表加入X/Y軸之標題：

於X/Y軸之標題上（目前均為『座標軸標題』），點按一下滑鼠，即可重新 輸入新內容，分別將其改為：『車齡』與『價格(萬)』 於上方之圖表標題上（目前為『價格(萬)』），點按一下滑鼠，續點一下文 字，將其改為：『中古車齡與價格之關係圖』 以滑鼠點按右側之『 』圖例，續按Delete，將其刪除

點選圖內任一資料點

切換到『圖表工具/版面配置』索引標籤，點選『分析』群組之『趨勢線』鈕， 續選『其他趨勢線選項(M)…』，轉入

由於散佈圖顯示各圖點之分佈接近直線，故於『趨勢預測/迴歸分析類型』處， 選「線性(L)」；另於最底下，加選「圖表上顯示公式(E)」與「圖表上顯示R 平方值(R)」

按 鈕，即可於圖表上獲致迴歸方程式及其判定係數（R平方值）

其迴歸方程式為 y = -4.809x + 57.8 即 中古車車價 = -4.809×車齡 + 57.8 其判定係數0.986，表整個迴歸模式之解釋力很強，即車齡的變異可解釋 98.6%的售價差異。 取得迴歸方程式後，即可用以預測不同車齡之售價。假定，要求當車齡為6.5 年時，其售價應為多少？僅須將6.5代入其迴歸方程式之x： y = -4.809×(6.5) + 57.8 中古車車價 = -4.809×6.5 + 57.8=26.54 可求得其中古車車價為26.54萬：

於Excel，我們是以下示步驟，來複製公式並進行運算： 於A13輸入要求算之年數6.5 選點圖上之迴歸方程式，會變成以方框包圍

再選點迴歸方程式之內容，可進入編輯狀態，外圍之方框會消失 以拖曳方式，選取迴歸方程式之內容

切換到『常用』索引標籤，按『剪貼簿』群組之『複製』鈕，記下迴歸方程 式之內容 移回B13，按其資料編輯區轉入編輯狀態

切換到『常用』索引標籤，按『剪貼簿』群組之『貼上』鈕，將記下之迴歸 方程式內容貼進來

將其x改為*A13，使其變成 = -4.809*A13 + 57.8

按Enter鈕，即可計算出：當車齡為6.5年時，其售價應26.54萬元

馬上練習 依範例Ch14.xlsx『廣告費與銷售量』工作表資料，繪製其資料散佈圖 並求廣告費對銷售量之迴歸方程式。同時，求算當廣告費為400萬時， 其預測之銷售量為多少？

殘差與判定係數 有了迴歸方程式後，即可依此方程式 計算Y的預測值：（詳範例Ch14.xlsx『殘差與判定係數』工作表）

預測值與實際值之差距，即稱之為殘差：

若是判定係數不是很高，研究者於此應判斷是否有殘差很大之特異樣本？若有， 可將其排除後再重算一次迴歸，可求得更適當之迴歸方程式。但問題是殘差應 小於多少才好？並無一定標準，仍全憑研究者自行判斷！本例之判定係數 （R2）為0.9865，相當不錯，所以就不必再進行此一處理過程。 判定係數之公式為： 迴歸平方和佔總平方和之百分比，即是這條迴歸線可幫助資料解釋的部份。

範例Ch14. xlsx『殘差與判定係數』工作表之E12的殘差平方和（26 範例Ch14.xlsx『殘差與判定係數』工作表之E12的殘差平方和（26.06），就 是迴歸線無法解釋的部份，將其除以F12之總平方和（1934.07），就是這條 迴歸線無法解釋部份的百分比。以1減去無法解釋的百分比，就是這條迴歸線 可幫助資料解釋的百分比，即D14之0.9865，我們稱之為判定係數（R2）， 恰等於原利用繪圖求迴歸方程式所算出之R2＝0.986： 判定係數（R2）愈大，代表可解釋的部份愈大；若兩組迴歸模式之判定係數（R2）差不多，就選擇方程式較簡單之一組迴歸模式。

馬上練習 續上一個『馬上練習』，以所求得之廣告費對銷售量迴歸方程式 y = 9.184x + 299.8 就範例Ch14.xlsx『廣告費與銷售量1』工作表之內容，計算各樣本點 之預測值及殘差：

若將殘差絕對值最大之9月與4月兩筆資料排除，將其資料轉存到範例Ch14 若將殘差絕對值最大之9月與4月兩筆資料排除，將其資料轉存到範例Ch14.xlsx 『廣告費與銷售量2』工作表，以其資料重新再求一次迴歸，其結果為： 由其判定係數（R2）0.902大於先前之0.851，可看出將殘差較大之特異樣本排 除後，可獲得更好的迴歸模式。此時之迴歸方程式為： y = 9.131x + 306.1

非線性迴歸 有些資料間並不是單純的直線關係，如下例之『年齡與每月所得關係 圖』資料，以「線性(L)」之迴歸分析類型求其迴歸方程式，其判定係 數（R2）僅為0.000，根本不具任何解釋力：

點選其資料點，將其迴歸分析類型改為「多項式(P)」之順序「2」

其迴歸方程式為 y = -36.54x2 + 3463x – 42087 判定係數（R2）可高達0.884，就明顯較具解釋能力：

將所獲得之迴歸方程式 = -36.54x2 + 3463x – 42087 複製到C2，可看出原式之平方（x2）僅是以上標格式顯示，轉過來後僅變成 x2： 原式之x代表年齡，故將其改為A2，並轉為Excel可用之運算式（原式之平方 僅是以上標顯示且無星號，無法拿來運算）： = -36.54*A2^2 + 3463*A2 - 42087

按 鈕後，即可算出當年齡等於15時，以迴歸方程式進行預側，其所得將 為多少？ 將C2複製給C3:C15，可算出各年齡之所得預測值：

當然，若要我們於第17列，求算當年齡為48歲，其所得預測值將為多少？對 我們也不是難事：

馬上練習 以範例Ch14.xlsx『成就動機x成績』工作表之內容，繪製其資料散佈 圖並求成就動機對成績之迴歸方程式。檢視應以一次或二次較為合適？ 同時，求算當成就動機為80時，其預測之成績為多少：

一次式時之R2=0.436，二次式時之R2=0.913，故應選擇二次式之迴歸方程式， 當成就動機為80時，其預測之成績應為43.05：

於繪圖中，利用加入趨勢線可求算之迴歸種類最多，包括：直線、多次式、指 數、對數……等。如，下示之樹木直徑與其高度之資料，以「線性(L)」迴歸 分析類型進行迴歸，其判定係數（R2）僅為0.674：

改為使用「對數(O)」迴歸分析類型：

其迴歸方程式為 y = 21.51Ln(x) + 19.47 判定係數（R2）可高達0.925，就很明顯的較直線模式更具解釋力：

馬上練習 依範例Ch14.xlsx『對數迴歸』工作表資料，繪製XY散佈圖並求其對 數迴歸方程式：

使用『資料分析』進行迴歸 於繪圖中，利用加入趨勢線求算迴歸方程式，並無法對方程式及其係 數進行檢定，且很多統計數字亦未提供。 若使用『資料分析』增益集之「迴歸」項進行求算，則可獲致很多相 關之統計數字。如：求簡單相關係數、判定係數、以F檢定判斷因變數 與自變數間是否有迴歸關係存在、以t檢定判斷各迴歸係數是否不為0、 計算迴歸係數之信賴區間、計算殘差、……。甚至，還可繪製圖表。 （只是，並不很好看而已）

直線迴歸 假定，有範例Ch14.xlsx『廣告與銷售量』工作表之廣告費與銷售額 資料： 擬使用『資料分析』進行迴歸，其步驟為： 執行『資料』索引標籤『分析』群組『資料分析』鈕，選「迴歸」項

按 鈕，轉入

於『輸入Y範圍』處，以拖曳方式選取銷售額之範圍B1:B11 於『輸入X範圍』處，以拖曳方式選取廣告費之範圍A1:A11 由於上述兩範圍均含標記，故點選「標記(L)」 於『輸出選項』處，決定要將迴歸結果輸出於何處？本例選「輸出範圍(O)」， 並將其安排於原工作表之D1位置 若要分析殘差，可點選「殘差(R)」或「標準化殘差(T)」（本例選前者）

按 鈕，即可獲致迴歸結果。因其內容較多，將其拆分為幾個部 份說明其顯示結果之作用： 此部份在求算簡單相關係數0.9502（R，寫成『R的倍數』應是將coefficient of multiple correlation翻譯錯了，在複迴歸模式，此部份即複相關係數）、 判定係數（R平方）0.9029、調整後的R平方0.8907（在複迴歸時使用，有些 統計學家認為在複迴歸模式中，增加預測變數必然會使R平方增大，故必須 加以調整）標準誤195.8486與觀察值個數10。

此部份以ANOVA檢定，判斷因變數（Y）與自變數間（X，於複迴歸中則為 全部之自變數），是否有顯著之迴歸關係存在？判斷是否顯著，只須看顯著值 是否小於所指定之α值即可，如本例之顯著值0.0000<α=0.05，故其結果為棄 卻因變數與自變數間無迴歸關係存在之虛無假設。

此部份以t檢定，判斷迴歸係數與常數項是否為0（為0即無直線關係存在）？ 並求其信賴區間。其虛無假設為迴歸係數與常數項為0，判斷是否顯著，只須 看顯著值（P-值）是否小於所指定之α值即可，如本例之常數項（截距）為 306.106，其t統計量為1.309，顯著值（P-值）0.227＞α=0.05，故無法棄卻 其為0之虛無假設，迴歸方程式之常數項應為0，故往後可將其省略。最好， 是將截距（常數）定為0，再重新迴歸一次。 另，本例之自變數X（廣告費）的迴歸係數為9.131，其t統計量為8.623，顯 著值（P-值）0.000<α=0.05，故棄卻其為0之虛無假設，迴歸方程式之自變數 X的係數不為0，自變數與因變數間存有直線關係。

最後，Excel仍以 y = 9.131x + 306.106 進行後續之殘差分析：

此部份，為於求得迴歸方程式 y = 9.131x + 306.106 後，將各觀察值之X（廣告費）代入方程式。以求其預測之銷售量（萬），並 計算預測結果與原實際銷售量間之殘差（將兩者相減即可求得。如觀察值1之 廣告費為250萬，代入方程式所求得之預測銷售量為2588.15萬，以原實際銷 售量2600萬減去預測結果即為殘差11.15萬）。 研究者於此應判斷是否有殘差很大之特異樣本？若有，可將其排除後再重算一 次迴歸，可求得更適當之迴歸方程式。但問題是殘差應小於多少才好？並無一 定標準，仍全憑研究者自行判斷！ 由於，前面t-檢定之結果顯示，其截距應為0。故將其常數設定為0：

重新求一次迴歸，其結果為： 判定係數（R平方）0.993，還優於原判定係數0.9029。最後之迴歸方程式應 為： y = 10.4673x

馬上練習 以範例Ch14.xlsx『存放款』工作表之內容，繪製資料散佈圖並求存款 對放款之迴歸方程式：

同時，以『資料分析』之「迴歸」項，進行迴歸：

判定係數（R2）為0. 9954，ANOVA檢定之顯著值0. 0000<α=0 常數項（截距）為-2.9276，其t統計量為-1.6208，顯著值（P-值）0.1437＞ α=0.05，故無法棄卻其為0之虛無假設，迴歸方程式之常數項應為0。故可將 其設定為0，重新求算一次迴歸。 自變數X（存款餘額）的迴歸係數為0.8901，其t統計量為41.4538，顯著值 （P-值）0.0000<α=0.05，故棄卻其為0之虛無假設，迴歸方程式之自變數X 的係數不為0，自變數與因變數間存有直線關係。

由於，前面t-檢定之結果顯示，其截距應為0。故將其常數設定為0，重新進行 迴歸，其結果為： 最後之迴歸方程式應為： 放款餘額=0.8582×存款餘額

複迴歸 現實中，很多狀況並非簡單之單一變數即可以解釋清楚。如銷售量並 非完全決定於廣告費而已，產品品質、售價、銷售人員、……等，亦 均有其重要性。又如，銀行計算客戶之信用分數，亦不會只決定於其 每月所得而已，其動產、不動產甚或年齡、性別、教育程度、……等， 亦均有可能影響其信用分數。故於迴歸中，同時使用多個自變數以預 測某一因變數的情況已越來越多。這種同時使用多個自變數之迴歸， 即稱為複迴歸（multiple regression）或多元迴歸。 但於繪製圖表中，利用加入趨勢線之機會求迴歸模式，只可以求解單 變量之迴歸（只有一個X），並無法處理同時使用多個自變數（多個X） 之迴歸。此時，即得使用『資料分析』進行迴歸（最多可達16個自變 數）。

中古車車價之實例 假定，以範例Ch14.xlsx『中古車車價』工作表，同一廠牌同型中古車 之車齡、里程數及其價格資料：

擬使用『資料分析』進行複迴歸分析。其步驟為： 執行『資料』索引標籤『分析』群組『資料分析』鈕，選「迴歸」項， 按 鈕 於『輸入Y範圍』處，以拖曳方式選取因變數（價格）範圍C1:C11 於『輸入X範圍』處，以拖曳方式選取自變數（車齡及里程數）範圍A1:B11 （兩欄資料表使用兩個自變數，最多可達16個自變數） 由於上述兩範圍均含標記，故點選「標記(L)」 選「輸出範圍(O)」，並將其安排於原工作表之E1位置

按 鈕，即可獲致迴歸結果 此結果之複相關係數（R）為0.9716，判定係數（R平方）為0.9440、調整後 的R平方為0.9280。顯示整組迴歸方程式可解釋價格差異之程度相當高。

ANOVA表中之F檢定的顯著水準0. 000<α=0 最後之t檢定結果中，常數項（截距）為62.6468，其顯著水準（P-值） 0.000<α=0.05，故棄卻其為0之虛無假設，迴歸方程式之常數項不應為0，故 不可將其省略。

兩個自變數中之車齡的迴歸係數為-5. 3739，其顯著水準（P-值） 0. 003<α=0 故而，僅以『車齡』與『價格』再重新進行一次迴歸，記得不用將截距（常數 項）設定為0。其結果為：

所以，最後之迴歸方程式應為 y = -5.6121X1 +62.6667 （價格 = -5.6121×車齡 +62.6667）

信用分數之實例 再舉一個複迴歸之例子，假定，銀行為核發信用卡，而蒐集了申請人 之每月總收入、不動產、動產、每月房貸與扶養支出費用等資料，並 以主管之經驗，主觀的給予一信用分數：

為使評估信用分數能有一套公式，免得老是要主管抽空評分。擬以複迴歸來求 得一迴歸方程式，其處理步驟為： 切換到『資料』索引標籤『分析』群組『資料分析』鈕，選「迴歸」項， 按 鈕 於『輸入Y範圍』處，以拖曳方式選取因變數（信用分數）範圍F1:F9 於『輸入X範圍』處，以拖曳方式選取自變數（每月總收入、不動產、動產、 每月房貸與扶養支出）範圍A1:E9（五欄資料表使用5個自變數，最多可達16 個自變數） 由於上述兩範圍均含標記，故點選「標記(L)」 選「輸出範圍(O)」，並將其安排於原工作表之H1位置

按 鈕，即可獲致迴歸結果 此結果之複相關係數（R）為0.9910，判定係數（R平方）為0.9821、調整後 的R平方為0.9372。顯示整組迴歸方程式可解釋信用分數差異之程度相當高。

ANOVA表中之F檢定的顯著水準0. 0442<α=0 最後之t檢定結果中，常數項（截距）為57.0761，其顯著值（P-值） 0.0074<α=0.05，故棄卻其為0之虛無假設，迴歸方程式之常數項不應為0，故 不可將其省略。

所有五個自變數中，僅每月總收入之顯著值（P-值）為0. 0329<α=0 故可將這些變數之係數自迴歸方程式中排除掉。僅以『每月總收入』與『信用 分數』兩欄之資料重新進行一次迴歸，記得不用將截距（常數項）設定為0。 其結果為：

所以，最後之迴歸方程式應為 y = 4. 13146X1 + 55. 1983 （信用分數 = 4. 13146 × 每月總收入 + 55

馬上練習 老師為找出學生出席率高低之主要原因，以問卷調查蒐集了下列資料， 試以複迴歸求出席率高低之迴歸方程式：

由於，t檢定之結果僅截距『上課內容』與『上課時段』之P-值<0

（出席率=0.5502+0.1027*上課內容-0.0546*上課時段）

含二次式之複迴歸 像前文『年齡與每月所得關係圖』之資料，其迴歸方程式為： y = -36.54x2 + 3463x - 42087 係一含二次式之拋物線：

若仍擬以『資料分析』來求得迴歸方程式，得自行加入一平方項才可。其處 理步驟為： 於原年齡之前，插入一欄，將其安排為年齡之平方（如：A2之內容為=B2^2）

執行『資料』索引標籤『分析』群組『資料分析』鈕，選「迴歸」項， 按 鈕 於『輸入Y範圍』處，以拖曳方式選取因變數（每月所得）範圍C1:C15 於『輸入X範圍』處，以拖曳方式選取自變數（年齡平方與年齡）範圍 A1:B15 由於上述兩範圍均含標記，故點選「標記(L)」 選「輸出範圍(O)」，並將其安排於原工作表之E1位置

按 鈕，即可獲致迴歸結果 此結果之判定係數（R平方）為0.884033，ANOVA表中之F檢定的顯著水準7.14E-06<α=0.05，故其結果為棄卻因變數與自變數間無迴歸關係存在之虛無假設。

最後之t檢定結果中，常數項（截距）、年齡平方與年齡等之顯著水準（P-值） 均小於α=0 最後之t檢定結果中，常數項（截距）、年齡平方與年齡等之顯著水準（P-值） 均小於α=0.05，故棄卻其為0之虛無假設，故均不可將其省略。所以，最後之 迴歸方程式應為 y = -36.54x2 + 3463.75x – 42087.05 （每月所得 = -36.54×年齡平方 + 3463.75×年齡 – 42087.05）

馬上練習 下示資料分佈情況接近對數圖形，試新增一欄ln(x)資料，並以『資料 分析』求其迴歸方程式：

（y = -6.95925Ln(x) + 57.83382）

第十四章 結束 謝謝！