這裡介紹如何在 Excel 中使用線性迴歸模型分析資料,產生統計報表。

線性迴歸(linear regression)是統計學上很常被使用的資料分析方法,而在 Excel 中也內建了迴歸分析的工具,讓使用者可以快速產生線性迴歸分析的統計報表。


以下我們使用鳶尾花資料集(iris)的資料來示範迴歸分析的步驟。

鳶尾花資料集

由於普通的線性迴歸模型只能用來分析連續型的數值資料,如果遇到離散的類別型資料(例如花的品種)就不能放進迴歸模型中,所以這裡我們只會使用到這個資料集的前四欄資料。

啟用分析工具箱

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.comExcel Easy