如何在 Excel 中計算兩個變數之間的相關係數？

Author Sun Last modified 2025-08-23

我們通常使用相關係數（值範圍從 -1 到 1）來表示兩個變數之間線性關係的強度和方向。相關係數是一種廣泛使用的統計量，可幫助您理解銷售與廣告支出、溫度與冰淇淋銷售或其他成對數據之間的關聯。在 Excel 中，有多種簡單的方法可以計算相關係數，包括內建函數和分析工具。

注意：相關係數為 +1 表示完全正線性關係，意味著隨著變數 X 增加，變數 Y 也會上升；同樣，當 X 減少時，Y 也下降。相反，值為 -1 表示完全負相關，因此隨著 X 增加，Y 減少，反之亦然。接近 0 的係數表明變數之間幾乎沒有或沒有線性關係。

方法 A：直接使用 CORREL 函數

方法 B：應用數據分析並輸出分析結果

方法 C：使用 PEARSON 函數作為替代方案

方法 D：使用 VBA 程式碼計算多對變數的相關係數

方法 A：直接使用 CORREL 函數

考慮兩個數據列表，每個列表代表一個變數。如果您想在 Excel 中計算這兩個變數之間的相關係數，這個方法既快速又高效。

實際使用時，請確保兩個數據範圍都是數字並且包含相同數量的觀測值。例如，如果您有以下成對數據：
sample data

選擇一個空白單元格以顯示計算結果。輸入以下公式，然後按「Enter」鍵計算相關係數：

=CORREL(A2:A7,B2:B7)

get the correlation coefficient with formula

在此公式中，A2:A7 和 B2:B7 代表您要分析的兩個變數列表。範圍必須等長，且每對應對應相同的觀察值。

實用提示：CORREL 自動忽略空單元格和文字，但如果兩列中沒有有效的數字對，則會返回 #DIV/0！錯誤。確保您的數據正確對齊並包含數字對，以便準確計算相關性。

一旦您計算出相關係數，您可以插入折線圖以視覺化觀察關係並進一步解釋相關性，如下所示：
insert a line chart to view the correlation coefficient

此方法最適合在兩個小型數據集之間進行快速手動檢查，或者在電子表格內互動操作時使用。它非常適合尋求立即結果而無需高級統計輸出的用戶。

使用 Kutools AI 解鎖 Excel 的魔法

智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
自訂公式：生成量身定制的公式，簡化您的工作流程。
VBA 編碼：輕鬆編寫和實現 VBA 代碼。
公式解釋：輕鬆理解複雜的公式。
文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！

方法 B：應用數據分析並輸出分析結果

如果您需要一次分析多個變數之間的相關性，或者想要一個更全面的輸出表，Excel 的「分析工具庫」提供了一個有用的解決方案。這個外掛生成相關矩陣，並允許您一步比較多個變數，對於大型數據集或建立統計報告很有價值。

1. 如果您已經將數據分析外掛添加到數據標籤，則可以直接跳到第 3 步。否則，點擊文件 > 選項。在「Excel 選項」對話框中，選擇 增益集 左側窗格中的選項，然後點擊前往按鈕旁邊的「Excel 增益集」框。
click Add-Ins > Go in Excel Options dialog

2. 在「增益集」對話框中，勾選名為 分析工具庫的框，然後點擊確定。這將把「數據分析」組添加到數據標籤中。
check Analysis ToolPak

3. 接下來，點擊數據 > 數據分析。在彈出的「數據分析」對話框中，選擇 相關性 從列表中，然後點擊確定.
click Data > Data Analysis select Correlation in the dialog

4. 在相關性對話框中，配置以下內容：
1) 選擇包含數據的範圍。
2) 根據數據的組織方式，選擇「列」或「行」選項。
3) 如果您的數據包含標題，請勾選「標籤位於第一行」選項。
4) 在「輸出選項」中指定一個輸出位置以顯示結果。
set options in the Correlation dialog

5. 點擊確定生成相關分析表。相關係數將顯示在指定範圍內。
get the analysis result

當您需要評估兩個以上變數之間的關係或希望獲得用於報告目的的摘要表時，此方法是合適的。數據分析輸出簡潔，但不提供額外的顯著性統計數據。如果收到意外結果，請仔細檢查數據的一致性、空單元格和正確的範圍選擇。

方法 C：使用 PEARSON 函數作為替代方案

除了 CORREL，Excel 還提供了 PEARSON 函數，該函數也可以計算兩個變數之間的皮爾森相關係數。功能上，PEARSON 和 CORREL 返回相同結果。但是，PEARSON 嚴格遵循原始數學公式，而 CORREL 則針對 Excel 環境進行了優化。如果您習慣於統計理論或在 Excel 外部使用統計工具，PEARSON 可能感覺更熟悉。

例如，對於 A2:A7 和 B2:B7 內的兩個數字列表，您可以按以下方式計算相關性：

1. 選擇要在其中顯示結果的單元格，並輸入以下公式：

=PEARSON(A2:A7,B2:B7)

2。按 Enter 完成計算。如果您想分析其他數據對，請相應調整單元格範圍，或將公式拖動到其他單元格。
提示： PEARSON 忽略文本或邏輯值，因此確保兩個範圍僅包含數字值且等長。如果某一列中有缺失數據，請相應對齊範圍以避免錯誤。

使用 PEARSON 對於從其他統計軟體遷移過來的用戶，或在需要嚴格遵守術語的學術環境中尤其實用。對於大多數 Excel 使用情況，CORREL 和 PEARSON 提供相同結果。

如果遇到 #DIV/0！錯誤，請檢查兩個範圍是否等長且不含不匹配的空或非數字單元格。

優點：使用方便，與統計軟體一致；缺點：對大多數用戶而言與 CORREL 沒有顯著差異。

方法 D：使用 VBA 程式碼計算多對變數的相關係數

如果您需要自動計算多對數據的相關係數（例如，當處理許多變數組合時），編寫一個簡單的 VBA 宏是一個高效的選擇。這種方法非常適合希望處理大型數據集或自動化重複分析任務的高級用戶。

1. 要使用此方法，首先通過點擊開發工具 > Visual Basic 打開 VBA 編輯器。在 Visual Basic for Applications 窗口中，轉到插入 > 模塊，然後將以下程式碼粘貼到模塊中：

Sub BatchCalculateCorrelations()
    Dim ws As Worksheet
    Dim rng1 As Range, rng2 As Range
    Dim lastRow As Long
    Dim i As Long
    Dim resultCol As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set rng1 = Application.InputBox("Select first variable range (single column)", xTitleId, Type:=8)
    Set rng2 = Application.InputBox("Select second variable range (multiple columns)", xTitleId, Type:=8)
    Set resultCol = Application.InputBox("Select starting cell for output", xTitleId, Type:=8)
    
    If rng1.Rows.Count <> rng2.Rows.Count Then
        MsgBox "The two data ranges must have the same number of rows.", vbCritical, xTitleId
        Exit Sub
    End If
    
    For i = 1 To rng2.Columns.Count
        resultCol.Cells(1, i).Value = "Correlation with " & rng2.Cells(1, i).EntireColumn.Column
        resultCol.Cells(2, i).Value = WorksheetFunction.Correl(rng1, rng2.Columns(i))
    Next i
End Sub

2. 插入程式碼後，關閉 VBA 編輯器。在 Excel 中，按下 Alt + F8，選擇 BatchCalculateCorrelations，然後點擊執行。系統將提示您選擇：

第一個變數範圍（單一列，例如 A2:A7）
第二個變數範圍（一或多列，例如 B2:D7）
您希望結果開始的單元格（例如 F2）

然後宏將計算第一個變數與第二個範圍內每一列的相關係數，並將結果從所選單元格水平顯示。

優勢：自動化重複計算，節省大量時間處理大型數據集，並確保一致性。

如果遇到“兩個數據範圍必須具有相同的行數”之類的問題，請確保所有選定列共享完全相同的行數且沒有空白行。對於錯誤排查，請驗證宏是否啟用以及範圍是否正確選擇。

在 Excel 中處理相關係數時，選擇正確的方法取決於您的數據結構和分析需求。對於一次性快速計算兩個序列之間的相關性，像 CORREL 或 PEARSON 這樣的公式既高效又易於使用。對於多個變數或需要摘要表的情況，分析工具庫非常實用。如果您需要對大型數據集進行重複分析或想要自定義工作流程，考慮使用 VBA 自動化以節省時間並減少人為錯誤。
始終確保數據範圍對齊、清潔且不含空或非數字單元格，以避免公式錯誤。如果遇到意外結果，請仔細檢查選擇和數據類型。

在 Excel 中計算兩個數字之間的百分比變化或差異
本文討論如何在 Excel 中計算兩個數字之間的百分比變化或差異。

在 Excel 中計算或分配字母成績
根據學生的分數分配字母成績可能是教師的一項常見任務。例如，我有一個定義好的分級標準，其中 0-59 = F，60-69 = D，70-79 = C，80-89 = B，90-100 = A，了解更多。

在 Excel 中計算折扣率或價格
當聖誕節即將來臨時，購物商場一定會有很多促銷活動。但是如果不同類別的商品有不同的折扣，您如何計算不同商品的折扣率或價格呢？

在 Excel 中計算兩個日期之間的天數 / 工作日 / 週末
也許有時候，您只想計算兩個日期之間的工作日數，而有時候，您需要計算兩個日期之間的週末天數。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多