這裡介紹如何在 Excel 中使用線性迴歸模型分析資料,產生統計報表。
線性迴歸(linear regression)是統計學上很常被使用的資料分析方法,而在 Excel 中也內建了迴歸分析的工具,讓使用者可以快速產生線性迴歸分析的統計報表。
由於普通的線性迴歸模型只能用來分析連續型的數值資料,如果遇到離散的類別型資料(例如花的品種)就不能放進迴歸模型中,所以這裡我們只會使用到這個資料集的前四欄資料。
Excel 的迴歸分析是「分析工具箱」當中的一項功能,使用之前要先啟用「分析工具箱」,以下是啟用的步驟。
Step 1
點選主選單的「檔案」。
Step 2
點選「選項」。
Step 3
在左側選單中選擇「增益集」,接著尋找「分析工具箱」,如果發現它處於「非使用」狀態的話,就點選下方的「執行」,進行增益集的管理。
Step 4
將「分析工具箱」這個增益集打勾,按下「確定」。
這樣就完成「分析工具箱」的啟用動作了。
當「分析工具箱」成功啟用之後,就可以開始進行資料的迴歸分析了。
Step 1
從 Excel 的「資料」籤頁中,選擇「資料分析」功能。
Step 2
在資料分析功能中,有非常多的分析工具,請選擇「迴歸」。
Step 3
設定迴歸模型的輸入資料與各種選項。
以下是各個重要選項的簡要說明:
項目 | 說明 |
---|---|
輸入 Y 範圍 | 反應變數,只能有一欄。 |
輸入 X 範圍 | 解釋變數,可以是一欄或多欄。 |
標記 | 變數資料範圍的第一列是否就是變數的名稱。 |
常數為零 | 設定迴歸模型的截距項為零(迴歸線通過原點)。 |
信賴度 | 參數區間估計的信賴水準(confidence level)。 |
殘差 | 輸出每筆資料的殘差值(residual)。 |
常態機率圖 | 畫出常態分佈的 QQ 圖(quantile-quantile plot)。 |
這裡我拿第一欄的 sepal length
當作反應變數 Y,而 petal width
當作解釋變數 X,並輸出殘差與殘差圖。
Step 4
執行 Excel 的「迴歸」功能之後,就會產生類似這樣的報表。
如果修過迴歸分析的人,直接看這張報表應該就可以理解整個迴歸模型了,以下大約標示出幾個重點數值,首先要看一下 R 平方(R-squared),它是介於 0 到 1 之間的數值,愈接近 1 愈好(通常是這樣,但也有例外)。
接著看一下模型的 F 統計量與各係數的 t 統計量對應的 p-value,以這個例子來說都非常顯著,所以解釋變數與常數在模型中都有作用,算是還不錯的模型。
確認模型沒問題的話,就可以把配適出來的係數拿出來使用了。
我們也可以把多個解釋變數都放進迴歸模型中配適,操作方法都相同,只是在選擇輸入 X 的時候,選擇多欄的資料而已。
以下我拿第一欄的 sepal length
當作反應變數 Y,而將其餘三欄資料都當作解釋變數 X,產生的報表會像這樣。
參考資料:ExcelDemy.com、Excel Easy