如何用Excel算相關係數：完整教學與實用技巧

Byadmin

Excel 算相關係數，原來這麼簡單！

嘿，各位在數據世界裡奮鬥的朋友們！是不是常常在整理報表、分析數據時，遇到這樣一個問題：「這兩個變數之間，到底有沒有關係？它們的關係有多緊密？」身為一個長期在 Excel 這片領域耕耘的「老手」，我必須說，這絕對是大家最常遇到的疑惑之一。尤其是當你手上有一堆數據，卻不知道該如何量化它們之間的關聯性時，那種無力感，簡直讓人抓狂！

別擔心，今天這篇文章，就是要來徹底解決你的困擾！我們將帶你一步一步學會，如何用 Excel 算相關係數。這不僅僅是一個枯燥的計算過程，更是一種洞察數據之間隱藏連結的強大工具。學會了它，你會發現，分析數據不再是件難事，反而充滿了樂趣與發現！

快速精確解答：

要用 Excel 計算相關係數，最快、最直接的方法是利用其內建的 `CORREL` 函數。只需要選取你想分析的兩組數據範圍，然後輸入 `=CORREL(array1, array2)` 就能輕鬆得到皮爾森相關係數。如果你需要更深入的分析，可以透過「資料分析工具庫」中的「相關性」功能，一次計算多個變數之間的相關係數矩陣，並提供顯著性檢定。

什麼是相關係數？讓我們深入理解

在我們動手計算之前，先來釐清一下，到底什麼是「相關係數」？簡單來說，它就是一個數值，用來衡量兩個變數之間線性關係的強度和方向。這個數值介於 -1 到 +1 之間。讓我們來細細拆解：

  • +1： 表示完全正相關。也就是說，當一個變數增加時，另一個變數也以相同的比例增加；反之，當一個變數減少，另一個也跟著減少。像是「讀書時間」和「考試分數」，通常情況下，讀書時間越長，分數也會越高，這就是一種正相關的例子。
  • -1： 表示完全負相關。這代表當一個變數增加時，另一個變數反而以相同的比例減少；反之，當一個變數減少，另一個反而增加。想像一下「運動時間」和「體重」，通常情況下，運動時間越多，體重可能會下降，這就是一個負相關的例子。
  • 0： 表示兩個變數之間沒有線性相關。這不代表它們完全沒有關係，只是這種「線性」的連結性不存在。可能是曲線關係，或者根本就毫無關聯。
  • 介於 -1 和 +1 之間的數值： 則代表不同程度的相關性。數值越接近 +1 或 -1，表示相關性越強；越接近 0，表示相關性越弱。

我們最常使用的是「皮爾森積差相關係數」（Pearson correlation coefficient），它特別適合用來衡量兩個連續性變數之間的線性關係。所以，當我們說「用 Excel 算相關係數」，通常指的就是計算皮爾森相關係數。

如何用 Excel 算相關係數：基本方法 (CORREL 函數)

好啦，理論講完了，現在就來進入最實際的部分！如何用 Excel 算相關係數呢？最簡單、最快速的方法，就是善用 Excel 的內建函數 `CORREL`。這個函數非常直觀，只要你選對了範圍，幾秒鐘就能得到結果。

步驟一：準備你的數據

首先，確保你的數據已經整齊地排列在 Excel 工作表中。通常，你會需要兩欄數據，每一欄代表一個變數。請注意，這兩欄數據的列數必須相同，也就是說，它們的觀測值數量必須一樣。

例如，我們想看看「廣告支出」和「銷售額」之間的關係。我們可以在 A 欄放廣告支出，B 欄放對應的銷售額。

| 廣告支出 (千元) | 銷售額 (萬元) |

|—————–|—————–|

| 10 | 150 |

| 12 | 165 |

| 15 | 180 |

| 18 | 195 |

| 20 | 210 |

步驟二：輸入 CORREL 函數

找一個你想要顯示相關係數結果的儲存格。然後，輸入以下公式：

=CORREL(array1, array2)

這裡的 `array1` 和 `array2` 就是你剛剛準備好的兩欄數據範圍。假設你的廣告支出在 A2:A6，銷售額在 B2:B6，那麼公式就會變成：

=CORREL(A2:A6, B2:B6)

步驟三：按下 Enter 鍵

輸入完公式後，按下 Enter 鍵，Excel 就會立刻計算出這兩組數據之間的相關係數。你會得到一個數值，例如 0.998。這個數值非常接近 +1，表示廣告支出和銷售額之間有著非常強的正相關！是不是很方便呢？

進階技巧：使用資料分析工具庫一次計算多變數相關性

如果你的數據比較複雜，不只兩個變數，而是好幾個變數需要互相計算相關係數，那麼一個一個用 `CORREL` 函數會有點費時。這時候，Excel 的「資料分析工具庫」（Data Analysis ToolPak）就派上用場了！

請注意： 「資料分析工具庫」預設是沒有啟用的，你可能需要先手動啟用它。

啟用資料分析工具庫

  1. 點選 Excel 左上角的「檔案」（File）。
  2. 選擇「選項」（Options）。
  3. 在「Excel 選項」視窗中，點選左側的「增益集」（Add-ins）。
  4. 在下方的「管理」下拉選單中，選擇「Excel 增益集」，然後點選「執行」（Go…）。
  5. 在「增益集」視窗中，勾選「分析工具箱」（Analysis ToolPak），然後點選「確定」。

完成以上步驟後，你應該能在 Excel 功能區的「資料」（Data）標籤頁右側，看到「資料分析」（Data Analysis）這個選項。

步驟一：整理你的多變數數據

將所有你想分析的變數數據，整齊地排列在連續的欄位中。確保每一欄都代表一個變數，且列數相同。

例如，我們有「廣告支出」、「電視廣告」、「網路廣告」、「促銷活動」和「銷售額」這五個變數，並且都放在 A1:E10 的範圍內。

步驟二：執行「相關性」分析

  1. 點選「資料」標籤頁，然後點選「資料分析」。
  2. 在「資料分析」視窗中，選擇「相關性」（Correlation），然後點選「確定」。
  3. 在「相關性」對話框中：
    • 輸入範圍 (Input Range)： 選擇你所有變數的數據範圍，例如 `$A$1:$E$10`。
    • 群組方式 (Grouped By)： 預設是「欄」（Columns），這通常是我們需要的。如果你是將變數排成列，再改為「列」（Rows）。
    • 第一列是否為標籤 (Labels in first row)： 如果你的數據範圍包含標題列（例如「廣告支出」、「銷售額」等），請務必勾選此選項。
    • 輸出選項 (Output Options)： 你可以選擇將結果輸出到「新工作表」（New Worksheet）或「現有工作表」（Existing Worksheet）中的某個儲存格。我個人比較喜歡輸出到新工作表，這樣比較乾淨。
  4. 點選「確定」。

步驟三：解讀相關係數矩陣

Excel 會產生一個「相關性」表格，這是一個相關係數矩陣。矩陣中的每一個儲存格，都顯示了對應兩個變數之間的相關係數。

例如，這個表格可能會顯示：

廣告支出 電視廣告 網路廣告 促銷活動 銷售額
廣告支出 1.00 0.85 0.70 0.60 0.98
電視廣告 0.85 1.00 0.50 0.45 0.80
網路廣告 0.70 0.50 1.00 0.30 0.75
促銷活動 0.60 0.45 0.30 1.00 0.55
銷售額 0.98 0.80 0.75 0.55 1.00

從這個表格中，你可以一目了然地看出：

  • 對角線上的值永遠是 1.00，因為任何變數都與自身完全相關。
  • 矩陣是對稱的，例如「廣告支出」和「銷售額」的相關係數 (0.98)，會與「銷售額」和「廣告支出」的相關係數相同。
  • 你可以快速找到哪些變數之間的相關性最強（接近 1 或 -1），哪些較弱（接近 0）。在這個例子中，「廣告支出」和「銷售額」的相關性最強 (0.98)。

解讀相關係數的注意事項

雖然相關係數是個強大的工具，但我們在解讀時，絕對不能掉以輕心！以下幾點是絕對需要注意的：

1. 相關不等於因果 (Correlation does not imply causation)

這絕對是「相關係數」最最最重要的一句話！就算兩個變數高度相關，也不能直接斷定其中一個是另一個的原因。為什麼呢？

  • 潛在的第三變數： 可能有一個你沒有觀察到的「隱藏變數」，同時影響了這兩個變數，讓它們看起來好像有關聯。例如，夏天冰淇淋銷量和溺水人數都增加，但它們的共同原因是「天氣炎熱」，而不是吃冰淇淋導致溺水。
  • 巧合： 有時候，高度相關可能只是單純的巧合，尤其是在數據量較小時。
  • 反向因果： 也可能是因果關係是反向的。

我在分析客戶流失率和客服電話量時，發現兩者高度正相關。但我們不能直接說「客服電話量高導致客戶流失」，更可能是「客戶滿意度低」導致了「客服電話量高」和「客戶流失」這兩個現象同時發生。所以，務必保持理性，結合實際情況來判斷。

2. 僅衡量線性關係

皮爾森相關係數只能衡量「線性」關係。如果兩個變數之間存在的是一個強烈的「曲線」關係，那麼相關係數可能很低，甚至接近於零，但這並不代表它們毫無關係。這時候，我們可能需要繪製散佈圖來觀察數據的分布情況，以發現潛在的曲線關係。

3. 離群值的影響

離群值（Outliers），也就是那些遠離大多數數據點的異常值，對相關係數的影響非常大。一個明顯的離群值，有可能會大幅度地拉高或拉低相關係數，甚至改變其正負方向。因此，在計算相關係數之前，最好先繪製散佈圖，檢查是否有明顯的離群值，並思考如何處理它們（例如，移除或使用其他統計方法）。

4. 數據的性質

皮爾森相關係數最適用於連續性數據。如果你的數據是類別型的（例如，產品顏色、性別），那麼直接計算皮爾森相關係數就不太合適了，需要使用其他的統計方法，例如卡方檢定（Chi-squared test）。

常見問題與詳細解答

在學習和應用 Excel 計算相關係數的過程中，大家一定會遇到不少疑問。以下我整理了一些常見的問題，並希望能透過詳細的解答，幫助大家更深入地理解。

Q1：我的 Excel 版本沒有「資料分析工具庫」，怎麼辦？

這是一個很常見的狀況，尤其是在較舊的版本或是某些預設設定的 Excel 中。別擔心，正如前面提到的，你只需要按照我提供的步驟，手動啟用它即可。通常，在「檔案」>「選項」>「增益集」中，找到「Excel 增益集」，點選「執行」，然後勾選「分析工具箱」。如果仍然找不到，有可能是你的 Office 安裝檔缺少這個元件，可能需要重新安裝或修改 Office 安裝。

Q2：計算出來的相關係數是 0.5，這代表什麼意思？

當你得到一個相關係數為 0.5 時，這代表兩個變數之間存在一個「中等程度的正相關」。也就是說，它們確實有一起變動的趨勢，當一個增加，另一個通常也會增加，但這種關聯性不是非常緊密，也不是絕對的。例如，你的「學習時間」和「期中考分數」計算出來是 0.5，這表示學習時間長一點，分數有比較高的傾向，但也有可能有些人學習時間很長，分數卻不盡理想；反之亦然。在實際應用中，0.5 通常可以被認為是一個值得關注的相關性，但你需要進一步結合其他資訊來判斷它的意義。

Q3：相關係數是負的，是不是代表沒有關係？

絕對不是！相關係數為負值，恰恰代表了「負相關」。例如，如果計算出「運動時間」和「體重」的相關係數是 -0.7，這表示兩者之間存在一個「強烈的負相關」。也就是說，當運動時間增加時，體重有顯著下降的趨勢。負相關和正相關一樣，都代表了「關係」，只是關係的方向相反。數值越接近 -1，負相關性越強。

Q4：我看到有些地方提到 P 值，這跟相關係數有什麼關係？

這是一個非常專業的問題，也是進一步探討相關性「顯著性」的關鍵！簡單來說，相關係數告訴你相關的「強度」和「方向」，而 P 值（p-value）則告訴你這個相關性是「偶然」還是「統計上顯著」。

當我們從一個樣本數據計算出相關係數時，總會有一定的機率是隨機波動造成的。P 值就是用來衡量，在「兩個變數實際上沒有關聯」這個假設下，我們觀察到目前這麼強（或更強）的相關係數的機率。通常，我們會設定一個顯著水準（alpha，α），常見的是 0.05。

  • 如果 P 值小於 α (例如 P < 0.05)，我們就認為這個相關性是「統計上顯著的」，表示這個相關性不太可能是由偶然造成的，我們有足夠的證據拒絕「兩個變數實際上沒有關聯」這個假設。
  • 如果 P 值大於或等於 α (例如 P >= 0.05)，我們就認為這個相關性「不夠顯著」，表示我們沒有足夠的證據說這兩個變數之間存在真實的關聯，它很有可能只是隨機的結果。

「資料分析工具庫」中的「相關性」功能，除了計算相關係數，也會提供 P 值，這對於科學研究和嚴謹的數據分析非常重要。在日常的 Excel 工作表中，`CORREL` 函數本身只會給你相關係數，但如果你需要 P 值，就必須透過「資料分析工具庫」。

Q5：繪製散佈圖對於理解相關係數有什麼幫助？

我強烈建議大家，在計算出相關係數後，務必繪製一張散佈圖（Scatter Plot）！散佈圖能以視覺化的方式呈現你的數據點，讓你更直觀地看到：

  • 關係的類型： 是線性的，還是曲線的？
  • 關係的強度： 數據點是緊密地聚集在某個趨勢線上，還是非常分散？
  • 潛在的離群值： 是否有明顯偏離數據群的異常點？
  • 數據的分布： 數據是均勻分布，還是有聚集的現象？

繪製散佈圖的步驟也很簡單：

  1. 選取你的兩欄數據。
  2. 點選「插入」（Insert）標籤頁。
  3. 在「圖表」（Charts）群組中，找到「散佈圖」（Scatter），然後選擇最基本的散佈圖類型。

透過散佈圖，你可以把計算出來的相關係數，與數據的實際樣貌做對照，這樣就能得到更全面、更準確的理解，避免僅憑數字而產生誤判。

結語：讓 Excel 成為你數據分析的好幫手

看到這裡，相信你對「如何用 Excel 算相關係數」已經有了非常紮實的掌握。從簡單的 `CORREL` 函數，到強大的「資料分析工具庫」，Excel 提供了非常方便且有效的工具，讓我們能夠快速量化兩個變數之間的關係。不過，請永遠記得，數字只是工具，真正的洞察力來自於我們對數據的理解，以及對結果的審慎解讀。相關不等於因果，離群值會影響結果，線性關係只是其中一種考量。多多利用散佈圖，結合實際情境，你就能從數據中挖掘出更多有價值的資訊。

希望今天的分享，對你有所幫助！下次在處理數據時，不妨就動手試試看，讓 Excel 成為你數據分析的得力助手吧！

如何用Excel算相關係數