如何使用Excel執行簡單線性迴歸

簡單線性迴歸（simple linear regression）是行為和社會科學很常使用到的分析方法，主要用在具有線性關係的一個自變項和一個依變項上。除了探討兩變項間的關聯程度外，也會在兩者間建構出一條最小平方迴歸線，透過直線的斜率來瞭解依變項如何隨著自變項產生變化以及自變項是否能幫助依變項的預測。

運用統計分析軟體如 SPSS 可以很容易地執行簡單線性迴歸，軟體會輸出足以瞭解兩變項之間關係的豐富資訊。但在沒有統計分析軟體的情況下，又想執行這種分析時，可以改利用微軟的 Excel 來獲得類似的分析結果。運用 Excel 執行簡單線性迴歸有2種方法，第1種是透過 LINEST 函數，第2種則是利用「分析工具箱」裡的資料分析工具。

本篇文章將直接進入技術層面的操作，不做學理的說明，若您想瞭解最小平方迴歸線的概念和簡單線性迴歸的假設檢定過程，請分別參考最小平方迴歸線的建構和計算、簡單線性迴歸的假設檢定。下面內容將使用〈簡單線性迴歸的假設檢定〉裡孩童年齡和問題解決時不相關回應次數的例子，示範如何使用 Excel 執行簡單線性迴歸。

• 運用 LINEST 函數執行簡單線性迴歸
• 運用資料分析工具執行簡單線性迴歸

運用 LINEST 函數執行簡單線性迴歸

利用 Excel 執行簡單線性迴歸的第1種方法為 LINEST 函數，這個函數使用最小平方的方法計算出一條最適合所有資料的直線，並傳回能夠描述這條線的資訊，而這條線就是最小平方迴歸線。這函數的語法為 LINEST(known_y´s, [known_x´s], [const], [stats])，括弧裡4個引數的意義如下：

• known_y´s：依變項的資料範圍。
• known_x´s：自變項的資料範圍，可以是1個或數個自變項（多元線性迴歸）。
• const：是否強制截距為0，為 TRUE 或 FALSE 的邏輯值。TRUE 或省略不寫代表不強制，FALSE 則強制截距為0。
• stats：是否傳回更多的統計資訊，為 TRUE 或 FALSE 的邏輯值。TRUE 代表傳回更多的統計資訊，FALSE 或省略不寫僅傳回截距和斜率的數值。

LINEST 函數屬於陣列公式（array formula），指一個公式或函數可以傳回數個結果。Excel 2019 和之前版本的陣列公式稱為舊式或 CSE 陣列公式，必須先選取回傳數值的全部儲存格範圍，然後在選取範圍裡輸入陣列公式，再按 Ctrl+Shift+Enter 才會傳回全部的運算結果。Microsoft 365 和 Excel 2021 則把陣列公式稱為動態陣列公式並發展新的函數（例如 FILTER、SORT、UNIQUE和XLOOKUP 函數），只需在單一儲存格裡輸入公式並按 Enter 即可傳回全部的結果。

這裡先示範 Excel 2021 的操作方法，再說明之前版本的不同之處。首先，以一欄為一個變項、第1列為變項名稱的方式，把孩童年齡和問題解決時不相關回應次數例子的資料輸入至一空白的活頁簿或工作表裡，如下圖。

因為是 Excel 2021 的版本，所以不用先選取回傳結果的所有儲存格範圍，直接在儲存格F3或任一空白的儲存格輸入下面的語法：

=LINEST(C2:C13, B2:B13, TRUE, TRUE)

語法裡的引數 C2:C13 代表儲存格C2到C13，引數 B2:B13 代表儲存格B2到B13。整個語法是指依變項的資料範圍是從儲存格C2到C13，自變項的資料範圍是從儲存格B2到B13，以一般的方法計算直線的截距（也就是不強制截距為0）並輸出所有的統計資訊，運用最小平方的準則來建構一條最小平方迴歸線。

語法輸入完成後，直接按 Enter，會傳回如下圖的結果，不過只有數值沒有任何的說明，實在令人難以瞭解每個數值的意義。

下圖整理了每一格數值所代表的資訊，可以看出最小平方迴歸線方程式為。若把所有數值都四捨五入至小數點後第3位，Ｒ平方（決定係數）為0.664，估計標準誤為1.671，Ｆ檢定統計量（Ｆ值）為19.728，自由度為10。若想進一步瞭解Ｆ值的由來和Ｆ分配，請參考F分配的定義和F分配臨界值表的解讀。

LINEST 函數傳回的數值裡沒有獲得Ｆ檢定統計量的機率值，因此若想評估假設檢定的結果，可使用 F.DIST.RT 函數來取得Ｆ檢定統計量的機率值，再運用機率比較的決策規則。當獲得Ｆ值的機率值等於或小於事先設定的顯著水準時，即可拒絕虛無假設，接受對立假設。

在簡單線性迴歸裡，Ｆ值的分子自由度為1、分母自由度為樣本總數減2，若要取得獲得上面Ｆ值的機率值，可找一空白的儲存格輸入下面的語法：

=F.DIST.RT(F6, 1, G6)

這個語法的引數F6指Ｆ值的儲存格位置，而引數G6為分母自由度的儲存格位置。語法輸入完成後按下 Enter，得到機率值為0.001。比較這個機率和事先設定的顯著水準0.05，因為，所以拒絕虛無假設。分析結果指出整體迴歸模型達到統計顯著，也就是孩童年齡和問題解決時不相關的回應次數有顯著的關聯性。

若您使用 Excel 2019 或之前的版本，須先選取2欄5列的儲存格範圍，再輸入 LINEST 函數的語法，才能讓統計資訊完整地傳回。語法輸入完成後，按下 Ctrl+Shift+Enter，就會傳回最小平方迴歸線的完整統計資訊。雖然操作步驟比較多一點，但 LINEST 函數仍舊可在 Excel 2021 以前的版本使用。

雖然 LINEST 函數的使用很簡單，但是傳回的數值沒有任何的說明，實在有點難理解。此外，傳回的數值裡沒有獲得斜率和Ｆ檢定統計量的機率值，也沒有斜率的信賴區間，若要評估假設檢定的結果，須利用公式或函數進行額外的運算，操作上稍嫌麻煩。另一種簡單線性迴歸的執行方法是利用「分析工具箱」裡的資料分析工具，操作過程很簡單，下面示範操作方法。

運用資料分析工具執行簡單線性迴歸

資料分析工具的使用須先安裝「分析工具箱」（Analysis ToolPak），如果不確定是否已經安裝，可以查看功能表的資料 » 分析裡有沒有資料分析的圖示。若有，代表已安裝「分析工具箱」；若沒有，請參考〈如何使用Excel一次取得描述性統計量〉裡安裝分析工具箱的步驟進行安裝。

在資料分析圖示上點一下，會開啟「資料分析」視窗。從分析工具(A)的選項裡選擇迴歸，然後按下視窗右側的確定，帶出「迴歸」視窗。

在「迴歸」視窗裡，先分別輸入依變項和自變項資料所在的儲存格範圍，可以直接輸入（例如從儲存格C1到C13，可以輸入C1:C13）或按 ↥ 圖示後再從工作表中選取。如果第1列是變項名稱，須勾選標記(L)。分析結果已預設95%信賴區間的輸出，除非想探討其他的信心程度，不然不用特地勾選信賴度(O)。

這裡的信賴區間是指迴歸線斜率的信賴區間，而不是自變項數值的預測區間，若想瞭解預測區間的計算，請參考如何使用Excel計算最小平方迴歸線的預測區間。在輸出選項裡可以選擇輸出表格存放的位置，選項輸出範圍(O)指目前的工作表，須指出存放的儲存格位置。此外，依據個人的喜好或需求，也可以輸出至新工作表(P)或新活頁簿(W)。

若想瞭解每一個觀察值（這裡的例子指每一位孩童）的預測誤差，可勾選殘差裡的殘差(R)，會輸出每一個觀察值的預測值和預測誤差。殘差圖(D)則是自變項和預測誤差的散布圖，用來檢視整體簡單線性迴歸模型和資料間的適合度（goodness of fit）。

完成需要的輸入和勾選後，按下視窗右側的確定，會傳回如下圖的分析結果，為了讓輸出結果更清楚，把所有無法整除的數值都四捨五入到小數點後第3位。在「迴歸統計」表裡，「Ｒ的倍數」的英文實際上是 Multiple R，這裡的中文翻譯不太正確。這數值在簡單線性迴歸裡指2個變項的皮爾森積差相關係數，多元線性迴歸裡則是指複相關係數。

從「ANOVA」表格可以清楚地看到迴歸（分子）和殘差（分母）的自由度、可解釋的變異和不可解釋的變異。此外，也可看到Ｆ值為19.728，而獲得這個值的機率為0.001，因為這機率值小於事先設定的顯著水準0.05，所以可拒絕虛無假設，接受對立假設，也就是孩童年齡和問題解決時不相關的回應次數有顯著的關聯。

最後一個表格為最小平方迴歸線的截距和斜率係數、ｔ檢定統計量、獲得ｔ檢定統計量的機率和信賴區間。從表中可看出，斜率的數值為-0.72，ｔ檢定統計量為-4.442，而獲得該檢定統計量的機率為0.001。因為0.001小於事先設定的顯著水準0.05，所以可拒絕虛無假設，分析結果指出孩童年齡對問題解決時不相關回應次數的預測有明顯的幫助。

最後一個表格也可以看到斜率的信賴區間，不過令人好奇地出現2組。第2組信賴區間其實是「迴歸」視窗裡選項信賴度(O)的預設輸出，若在操作過程中勾選這選項並修改成其他的信心程度，分析結果就會出現在第2組信賴區間裡。

最後，如果在「迴歸」視窗的操作過程中有勾選殘差(R)和殘差圖(D)的話，會出現如下圖的輸出結果。從「殘差輸出」表可以看到利用最小平方迴歸線方程式計算出來的每位孩童的不相關回應次數的預測值和預測誤差，而殘差圖呈現出來的隨機分布型態代表整體簡單線性迴歸模型和資料的適合度還不錯。

從上面的示範過程可以看到利用資料分析工具裡的迴歸分析結果較 LINEST 函數更清楚易懂，統計資訊也更豐富。雖然沒有像統計分析軟體有更多的進階選項，但輸出結果能幫助我們瞭解自變項和依變項間的關聯性、自變項對依變項預測的幫助程度以及整體簡單線性迴歸模型和資料間的適合度，在沒有統計分析軟體的情形下，可說是一個不錯的簡單線性迴歸分析工具。

以上為本篇文章對如何使用 Excel 執行簡單線性迴歸的介紹，希望透過本篇文章，您學會了運用 Excel 執行簡單線性迴歸的2種方法。若您喜歡本篇文章，請將本網站加入書籤，並持續回訪本網站喔！另外，也歡迎追蹤本網站的 Facebook 和／或 Twitter 專頁喲！

如果您覺得本篇文章對您有幫助，歡迎買杯珍奶給 Dr. Fish！小小珍奶，大大鼓勵，您的支持將給 Dr. Fish 更多撰寫優質文章的動力喔！