第十四章迴歸

第十四章迴歸

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

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

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

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

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

點選圖內任一資料點

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

由於散佈圖顯示各圖點之分佈接近直線，故於『趨勢預測/迴歸分析類型』處，選「線性(L)」；另於最底下，加選「圖表上顯示公式(E)」與「圖表上顯示R平方值(R)」由於散佈圖顯示各圖點之分佈接近直線，故於『趨勢預測/迴歸分析類型』處，選「線性(L)」；另於最底下，加選「圖表上顯示公式(E)」與「圖表上顯示R平方值(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，我們是以下示步驟，來複製公式並進行運算：於Excel，我們是以下示步驟，來複製公式並進行運算： • 於A13輸入要求算之年數6.5 • 選點圖上之迴歸方程式，會變成以方框包圍

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

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

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

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

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

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

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

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

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

範例Ch14.xlsx『殘差與判定係數』工作表之E12的殘差平方和（26.06），就是迴歸線無法解釋的部份，將其除以F12之總平方和（1934.07），就是這條迴歸線無法解釋部份的百分比。以1減去無法解釋的百分比，就是這條迴歸線可幫助資料解釋的百分比，即D14之0.9865，我們稱之為判定係數（R2），恰等於原利用繪圖求迴歸方程式所算出之R2＝0.986：範例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.xlsx『廣告費與銷售量2』工作表，以其資料重新再求一次迴歸，其結果為：若將殘差絕對值最大之9月與4月兩筆資料排除，將其資料轉存到範例Ch14.xlsx『廣告費與銷售量2』工作表，以其資料重新再求一次迴歸，其結果為：由其判定係數（R2）0.902大於先前之0.851，可看出將殘差較大之特異樣本排除後，可獲得更好的迴歸模式。此時之迴歸方程式為：y = 9.131x + 306.1

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

點選其資料點，將其迴歸分析類型改為「多項式(P)」之順序「2」點選其資料點，將其迴歸分析類型改為「多項式(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時，以迴歸方程式進行預側，其所得將為多少？按鈕後，即可算出當年齡等於15時，以迴歸方程式進行預側，其所得將為多少？ • 將C2複製給C3:C15，可算出各年齡之所得預測值：

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

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

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

於繪圖中，利用加入趨勢線可求算之迴歸種類最多，包括：直線、多次式、指數、對數……等。如，下示之樹木直徑與其高度之資料，以「線性(L)」迴歸分析類型進行迴歸，其判定係數（R2）僅為0.674：於繪圖中，利用加入趨勢線可求算之迴歸種類最多，包括：直線、多次式、指數、對數……等。如，下示之樹木直徑與其高度之資料，以「線性(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於『輸入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，故其結果為棄卻因變數與自變數間無迴歸關係存在之虛無假設。此部份以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，再重新迴歸一次。此部份以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.05，故其結果為棄卻因變數與自變數間無迴歸關係存在之虛無假設。判定係數（R2）為0.9954，ANOVA檢定之顯著值0.0000<α=0.05，故其結果為棄卻因變數與自變數間無迴歸關係存在之虛無假設。 • 常數項（截距）為-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，重新進行迴歸，其結果為：由於，前面t-檢定之結果顯示，其截距應為0。故將其常數設定為0，重新進行迴歸，其結果為： • 最後之迴歸方程式應為：放款餘額=0.8582×存款餘額

第十四章 迴歸