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

作者孫修改日期2025-07-11

我們通常使用相關係數（數值範圍介於 ——1 到 1 之間）來衡量兩個變數之間線性關係的強度與方向。作為一種廣泛應用的統計指標，相關係數能幫助您深入掌握成對資料間的關聯性，例如銷售額與廣告支出、氣溫與冰淇淋銷售量等。在 Excel 中，您可以透過多種直覺且高效的方式計算相關係數，包括內建函數與分析工具包。

注意：相關係數為 +1 表示完全正向線性關係，即當變數 X 增加時，變數 Y 也會隨之上升；反之，當 X 減少時，Y 亦會下降。若係數為 ——1，則代表完全負相關，也就是當 X 增加時，Y 會減少，反之亦然。而當係數接近 0 時，則表示兩個變數之間幾乎不存在線性關係。

方法 A：直接使用 CORREL 函數

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

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

方法 D：使用 VBA 程式碼為多組配對資料計算相關係數

方法 A：直接使用 CORREL 函數

假設您有兩組資料清單，分別代表一個變數，想在 Excel 中快速高效地計算這兩個變數之間的相關係數。

實際使用時，請確保兩組區域皆為數值，且包含相同數量的觀測值。例如，若您有以下成對資料：
範例資料

選取一個空白儲存格來顯示計算結果，輸入下列公式後按下「Enter 鍵」，即可算出相關係數：

=CORREL(A2:A7,B2:B7)

在此公式中，A2:A7 與 B2:B7 代表您要分析的兩組變數清單。這些範圍的長度必須相等，且每組配對須對應相同的觀測值。

實用提示：CORREL 會自動忽略空白儲存格與文字，但若兩欄資料中缺乏有效的數值配對，將傳回 #DIV/0! 錯誤。請務必確認您的資料正確對齊，並包含足夠的數值配對，以確保相關係數計算結果準確無誤。

計算完相關係數後，您可以插入折線圖以直觀觀察變數間的關係，進一步詮釋相關性，如下所示：
插入折線圖以檢視相關係數

此方法最適合用於兩組小型資料集之間的快速手動比對，或在試算表中進行互動操作時使用，尤其適合希望立即取得結果、無需進階統計輸出的使用者。

透過 KUTOOLS AI 解鎖 Excel 的神奇功能

智慧執行：透過簡易指令，輕鬆執行儲存格操作、分析資料，並建立圖表！
自訂公式：打造專屬公式，讓您的工作流程更順暢！
VBA 編碼：輕鬆撰寫並套用 VBA 程式碼，立即提升工作效率！
公式解析：輕鬆掌握複雜公式！
文字翻譯：輕鬆打破試算表中的語言隔閡！

運用 AI 驅動的工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

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

若您需要一次分析多個變數之間的相關性，或希望獲得更完整的輸出表格，Excel 的「分析工具箱」提供了一個實用的解決方案。此增益集可產生相關係數矩陣，讓您一步到位比較多個變數，對於處理大型資料集或建立統計報告極具價值。

1. 如果您已在「資料」索引標籤中新增了數據分析增益集，可直接跳至步驟 3. 否則，請按一下檔案 > 選項。在「Excel 選項」對話方塊開啟後，從左側窗格選擇增益集，然後按一下「Excel 增益集」方塊旁的前往按鈕。
在 Excel 選項對話方塊中按一下「增益集 > 轉到」

2. 在「增益集」對話方塊中，勾選標示為分析工具箱的核取方塊，再按一下確定，即可在資料索引標籤中新增「數據分析」群組。
勾選「分析工具箱」

3. 接下來，按一下資料 > 數據分析。在彈出的「數據分析」對話方塊中，從清單中選擇相關係數，然後按一下確定。
在對話方塊中選取「相關係數」

4。「相關係數」對話方塊中，請進行以下設定：
1) 選取包含您資料的範圍。
2) 根據資料排列方式，選擇「欄」或「列」選項。
3) 若資料包含標題，請勾選「第一列包含標籤」選項。
4) 在「輸出選項」中指定結果顯示位置。
在「相關係數」對話方塊中設定選項

5. 按一下確定以產生相關係數分析表格，相關係數將顯示於限定區域中。
取得分析結果

當您需要評估兩個以上變數之間的關係，或希望取得一份簡明的摘要表格以供報告使用時，此方法極為實用。其數據分析結果簡潔清晰，但不會提供額外的顯著性統計資訊。若獲得非預期的結果，請務必再次確認資料的一致性、是否有空白儲存格，以及所選範圍是否正確。

方法 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 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多