超級促銷週Claude Skills — 20% 折扣
Tips

如何在 Excel 中進行迴歸分析:完整指南

Powerdrill Bloom·
如何在 Excel 中進行迴歸分析:完整指南

若要在 Excel 中執行迴歸分析,請先啟用 Analysis ToolPak,接著前往「資料」>「資料分析」並選擇「迴歸」。選取您的 Y 範圍與 X 範圍,挑選輸出位置,然後點選「確定」。Excel 會在新表格中傳回係數、R 平方以及其他統計資料。

本指南涵蓋在 Excel 中執行迴歸的三種方法:Analysis ToolPak、LINEST 函數以及圖表趨勢線。此外,還會逐步介紹完整範例、說明如何解讀輸出結果,並展示在大型檔案中測試關聯性的更快方法。詳細資訊源自 Microsoft 支援頁面,讀取日期為 2026 年 11 月 12 日。

迴歸分析能告訴您什麼

迴歸分析衡量一個數值隨另一個數值變化而改變的情況。您想要解釋的數值是應變數(Dependent Variable),即 Y。您認為驅動它的數值是自變數(Independent Variables),即 X。

Microsoft 這樣描述迴歸工具:它「透過使用『最小平方法』擬合一組觀測值的直線,來執行線性迴歸分析」。

該直線有一個簡單的方程式。Microsoft 將其寫為 y = mx + b,其中 m 是斜率,b 是截距。當有多個 X 時,方程式變為 y = m1x1 + m2x2 + ... + b。

以商業術語來說,迴歸分析可以回答以下問題:

  • 每多成長一個月,銷售額會增加多少?
  • 廣告支出與註冊人數的關聯程度如何?
  • 成本變化中有多少可以由訂單量來解釋?

Microsoft 自己的範例是關於運動員。它指出您可以分析「運動員的表現如何受到年齡、身高和體重等因素的影響」。

迴歸分析顯示的是關聯性,而非因果關係的證明。兩個數值之間強烈的線性關係仍可能源自於您未測量的第三個因素。

在 Excel 中設定資料

迴歸分析需要乾淨、成對匹配的數字。每一列應包含一個觀測值,Y 放在一個欄位,每個 X 放在各自的欄位中。

以下是來自 Microsoft 簡單線性迴歸範例的資料。它列出了六個月的銷售額(以美元計)。

月份 銷售額
1 3,100
2 4,500
3 4,400
4 5,400
5 7,500
6 8,100

將「月份」放在 A 欄,「銷售額」放在 B 欄,標題放在第 1 列。下方的範例皆使用此佈局。資料位於第 2 到 7 列,這也是為什麼下方的公式使用這些範圍的原因。

幾項規則可確保結果的可靠性:

  • 無空白儲存格。 每一列都需要同時具備 Y 值和 X 值。
  • 僅限數字。 以文字格式儲存的數值會導致錯誤。
  • 足夠的列數。 六列資料適用於示範,但實際分析需要更多資料。
  • 每一列代表一個觀測值。 請勿在同一個範圍內混合月份和週次的資料列。

啟用 Analysis ToolPak

迴歸工具位於 Analysis ToolPak 中,這是 Excel 隨附的增益集。您可能需要先將其啟用。

Microsoft 針對 Windows 版 Excel 的步驟如下:

  1. 選取「檔案」,接著選取「選項」,然後選取「增益集」。
  2. 在「管理」方塊中,選取「Excel 增益集」,然後選取「執行」。
  3. 在「增益集」方塊中,勾選「Analysis ToolPak」核取方塊,然後選取「確定」。

在 Mac 上,Microsoft 表示請從檔案選單前往「工具」>「Excel 增益集」。如果未列出 ToolPak,請選取「瀏覽」來尋找它。

啟用後,您將在「資料」索引標籤上看到「資料分析」。Microsoft 指出,如果該指令缺失,「您需要載入並啟動 Analysis ToolPak 增益集程式」。

方法 1:使用 Analysis ToolPak 執行迴歸分析

這是最完整的方法。它能一個步驟就提供完整的摘要表。

  1. 前往「資料」索引標籤並選取「資料分析」。
  2. 從清單中選擇「迴歸」並選取「確定」。
  3. 在「輸入 Y 範圍」中,選取「銷售額」欄位,包含標題。
  4. 在「輸入 X 範圍」中,選取「月份」欄位,包含標題。
  5. 勾選「標記」,因為兩個範圍都包含標題。
  6. 選擇「輸出範圍」或「新工作表」。
  7. 選取「確定」。

Excel 會建立包含三個區塊的摘要:迴歸統計資料、ANOVA 表以及係數表。下一節將說明如何解讀這些內容。

若要進行多元迴歸,請在步驟 4 中選取多個相鄰的 X 欄位。Microsoft 指出該工具可以分析「單一應變數如何受到一個或多個自變數數值的影響」。

在底層,該工具使用工作表函數。Microsoft 表示「迴歸工具使用 LINEST 工作表函數」。

方法 2:使用 LINEST 函數

LINEST 以公式形式提供相同的數字。它適合希望結果能隨資料變化而更新的使用者。

Microsoft 的語法為 LINEST(known_y's, [known_x's], [const], [stats])。Known_y's 是銷售額範圍,known_x's 是月份範圍。

針對銷售資料,在空白儲存格中輸入:

=LINEST(B2:B7, A2:A7)

該函數會傳回兩個值:斜率,然後是截距。Microsoft 表示 LINEST 傳回的陣列為 "{mn,mn-1,...,m1,b}",因此截距總是排在最後。

若要取得更多統計資料,請將最後一個引數設為 TRUE:

=LINEST(B2:B7, A2:A7, TRUE, TRUE)

當 stats 設為 TRUE 時,LINEST 會傳回標準誤差、R 平方、F 統計量、自由度以及平方和。Microsoft 指出該函數「必須以陣列公式輸入」,因為它會同時傳回多個值。

Microsoft 的頁面還展示了單一儲存格預測。下方的公式根據第 1 到 6 個月估計第 9 個月的銷售額:

=SUM(LINEST(B2:B7, A2:A7)*{9,1})

Microsoft 對此範例的計算結果為 11,000。

方法 3:在散佈圖中加入趨勢線

趨勢線在圖表上顯示迴歸線。這是查看直線是否擬合的最快方法。

  1. 選取兩欄資料。
  2. 插入散佈圖。
  3. 選取圖表,然後從圖表項目選單中加入「趨勢線」。
  4. 開啟「格式化趨勢線」窗格並保留「線性」選項。
  5. 勾選在圖表上顯示方程式和 R 平方值的方塊。

圖表接著會在線條旁邊印出方程式,例如 y = 1000x + 2000。這與銷售資料的 LINEST 結果一致。

趨勢線也可以進行前瞻預測。Microsoft 表示您可以「在『前推』和『後推』欄位中設定數值,將您的資料投射到未來」。

趨勢線適合快速查看或用於簡報投影片。若需要標準誤差、p 值或多個 X 變數,請使用方法 1 或方法 2。

該使用哪種方法

每種方法都適合不同的工作。請根據您需要展示的內容以及閱讀對象來挑選。

方法 最適用於 提供 p 值 隨資料更新
Analysis ToolPak 單一模型的完整報告 是 否,需重新執行工具
LINEST 工作試算表內部的模型 否,但提供計算它們的統計資料 是
散佈圖趨勢線 快速視覺檢查或簡報投影片 否 是

當您需要在會議中為結果辯護時,請使用 ToolPak。其摘要表包含審查者可能詢問的每個數字,包括每個係數的 p 值。

當模型需要提供給其他公式(例如預測表)時,請使用 LINEST。因為它是公式,斜率和截距會在有新資料列加入時立即改變。

當受眾需要看到模式時,請使用趨勢線。帶有線條及其方程式的圖表比表格更容易閱讀。許多分析師會同時使用這三種方法:用趨勢線探索,用 LINEST 建模,並用 ToolPak 報告。

迴歸分析範例

以下是三種方法針對六個月銷售額所傳回的結果。

統計量 數值 代表意義
斜率 (m) 1,000 銷售額每月上升約 1,000
截距 (b) 2,000 第 0 個月時線條的數值
R 平方 0.93 約 93% 的銷售變化遵循每月趨勢
第 9 個月估計值 11,000 1,000 × 9 + 2,000

您可以手動檢查斜率。平均月份為 3.5,平均銷售值為 5,500。斜率是離差交叉乘積之和除以月份離差平方和。計算結果為 17,500 除以 17.5,即 1,000。

截距隨之而來。它是平均銷售值減去斜率乘以平均月份:5,500 減去 3,500,即 2,000。

R 平方告訴您線條的擬合程度。Microsoft 將其描述為一個「數值範圍從 0 到 1」的值。當為 1 時,線條擬合每個點。當為 0 時,Microsoft 表示「迴歸方程式對於預測 y 值沒有幫助」。

如何解讀迴歸輸出結果

Analysis ToolPak 的輸出結果看起來可能很密集。大多數決策取決於少數幾個數字。

  • Multiple R。 實際值與預測值之間的相關性。越接近 1 表示擬合越緊密。
  • R Square。 Y 的變化中由 X 解釋的比例。在此約為 0.93。
  • Adjusted R Square。 針對 X 變數數量調整後的 R 平方。在比較具有不同輸入數量的模型時使用它。
  • Standard Error。 實際點與線條之間的典型距離。
  • Coefficients。 截距和每個 X 的斜率。這些構成了您的方程式。
  • P-value。 如果真實效應為零,看到如此大係數的機率。許多分析師將低於 0.05 的值視為顯著。
  • F and Significance F。 對整個模型的檢定。較小的 Significance F 表示模型解釋的內容多於偶然。

Microsoft 增加了一項關於準確性的說明。它表示「資料越呈線性,LINEST 模型就越準確」。

請以純單位解讀係數。斜率 1,000 意味著在此期間,平均而言,每多一個月會增加約 1,000 的銷售額。

使用 AI 在 Excel 中執行迴歸分析

當您有許多欄位且不確定哪些欄位重要時,手動測試每一對欄位會很耗時。AI 工具可以檢查整個資料集的關聯性並進行解釋。

以下是使用 Powerdrill Bloom 的三個步驟方法。

步驟 1:上傳您的 Excel 檔案

上傳包含 Y 欄位和候選 X 欄位的工作簿。Excel Calculations 頁面會要求您先上傳工作簿。它補充道:「確保您的資料格式正確,以實現順暢的計算」。

保持每一列代表一個觀測值,並清除欄位標題。AI 使用標題來理解每個欄位的含義。

上傳 Excel 檔案以在 Powerdrill Bloom 中執行迴歸分析

步驟 2:以自然語言要求分析

描述您想要測試的內容。例如:「對銷售額與月份和廣告支出進行線性迴歸分析,並解釋哪一個更重要」。

同一個頁面表示您可以「以文字輸入您想要的計算請求」。您可以提出後續問題,例如當您刪除一個異常月份時,關聯性是否仍然成立。

步驟 3:檢查結果並繪製圖表

對照您自己工作簿中的 LINEST 檢查斜率。然後以視覺化方式展示關聯性。Scatter Graph Creator 旨在「透過互動式且可自訂的散佈圖視覺化資料關聯性」。

對於超出直線範圍的預測,AI Forecasting 工具會建立帶有信賴區間的預測圖表。有關該方法本身的背景資訊,請參閱我們關於 迴歸分析 的說明文章。

檢查迴歸結果並繪製關聯性圖表

應避免的常見錯誤

這些是人們在 Excel 中執行迴歸分析時最常出現的錯誤。

  • 混淆 X 和 Y。 Y 是您想要解釋的數值。交換它們會改變斜率。
  • 忘記勾選「標記」方塊。 如果您的範圍包含標題,請勾選「標記」,否則 Excel 會將標題視為資料。
  • 在網頁版 Excel 中執行迴歸。 Microsoft 表示您「無法在那裡建立迴歸分析」,因為迴歸工具不可用。請使用桌面應用程式。
  • 僅閱讀 R 平方。 高 R 平方並不能證明因果關係,且可能隱藏曲線模式。務必查看散佈圖。
  • 使用太少的列數。 六個點可能會偶然產生一條整齊的線。更多資料能提供更可靠的結果。
  • 過度外推。 擬合第 1 到 6 個月的線條可能不適用於第 24 個月。
  • 忽略離群值。 一個異常的月份可能會拉動整條線。檢查圖表中遠離其他點的點。
  • 加入許多相關的 X 欄位。 同步變動的輸入會使係數不穩定。Microsoft 指出 LINEST 會「檢查共線性」並移除多餘的 X 欄位。

當您準備好測試整個資料集的關聯性時,請使用去年的每月數據 嘗試 Powerdrill Bloom。

常見問題

如何在 Excel 中執行簡單線性迴歸?

啟用 Analysis ToolPak,前往「資料」>「資料分析」,然後選擇「迴歸」。選取您的 Y 和 X 範圍,如果包含標題請勾選「標記」,然後選取「確定」。您也可以使用 =LINEST(Y 範圍, X 範圍)。

我可以在網頁版 Excel 中執行迴歸分析嗎?

無法完全執行。Microsoft 表示您可以在網頁版 Excel 中檢視迴歸結果,但無法在那裡建立迴歸分析。請在桌面應用程式中開啟檔案來執行。

Excel 迴歸中的 R 平方代表什麼?

R 平方顯示線條解釋了多少 Y 的變化。它的範圍從 0 到 1。0.93 的值意味著約 93% 的變化遵循擬合線。

如何在 Excel 中執行多元迴歸?

將每個 X 變數放在各自相鄰的欄位中。在迴歸工具中,選取所有 X 欄位作為「輸入 X 範圍」。LINEST 也接受多個 X 欄位。

LINEST 和迴歸工具之間有什麼區別?

它們提供相同的係數。Microsoft 表示迴歸工具使用 LINEST。該工具會寫出完整的摘要表,而 LINEST 則以公式形式傳回數字,並隨您的資料更新。

來源: Microsoft 支援,使用 Analysis ToolPak 執行複雜的資料分析 · Microsoft 支援,LINEST 函數 · Microsoft 支援,執行迴歸分析 · Microsoft 支援,在圖表中加入趨勢線或移動平均線。