KutoolsforOffice — 一套方案，五大工具。事半功倍。三月特賣：20% 折扣

如何在 Excel 中計算 z 分數：完整指南

作者Amanda Li修改日期

在統計學與數據分析領域中，掌握您的資料相對於平均值的表現至關重要。z 分數（又稱標準分數）能以標準差為單位，精準衡量資料點與資料集平均值之間的相對距離。無論您分析的是測驗分數、財務數據，還是其他任何數值型資料集，計算 z 分數都能為您提供深入洞察資料行為的關鍵視角。

使用 Excel 計算 Z 分數，兼具簡便與效率，能快速分析大型資料集，進行標準化比較與離群值偵測。本教學將帶您了解 Z 分數的定義、掌握在 Excel 中計算 Z 分數的方法、提供實用的公式範例、解讀資料中的 Z 分數意義，並分享執行相關計算時的重要注意事項。

計算 z 分數的通用公式截圖

什麼是 z 分數？

z 分數（又稱標準分數）是一種統計指標，用來衡量特定資料點與資料集平均值之間的距離，並以標準差為單位呈現。這項指標能清楚反映資料點偏離平均值的程度與方向（高於或低於平均值）。簡言之，z 分數將資料點轉換至統一尺度，讓不同資料集或多元母體之間的比較更直觀，且不受原始測量單位或分佈形狀的影響。

z 分數的概念與常態分佈密切相關。常態分佈是統計學中的基本概念，指的是一種數據分佈形態，其中大多數觀測值集中在中央峰值附近，且數值出現的機率從平均值向兩側對稱遞減。在常態分佈的情境下：

統計學中常態分佈曲線的示意圖

  • 約 68% 的資料落在一個標準差（±1 z 分數）範圍內，顯示與平均值呈現中等程度的偏離。
  • 約 95% 的觀測值落在兩個標準差（±2 z 分數）範圍內，顯示出明顯但並非極端的偏離。
  • 近 99.7% 的資料落在三個標準差（±3 z 分數）範圍內，涵蓋了分佈中幾乎所有觀測值，凸顯出極端偏離的罕見情況。

z 分數是統計分析中的關鍵工具，能協助研究人員與分析師將不同資料集中的個別觀測值標準化，輕鬆比較來自不同分佈的分數。透過轉換為 z 分數，可快速判斷特定觀測值在給定分佈中是否異常或典型，使其成為離群值偵測、假設檢定與資料標準化等應用中不可或缺的利器。

如何在 Excel 中計算 z 分數？

在 Excel 中，並無單一專用函數可直接計算 z 分數。此過程需先計算資料集的平均值（μ）與標準差（σ）。取得這兩項關鍵統計量後，您可透過以下兩種主要方法輕鬆計算 z 分數：

  • 手動計算方法：套用 z 分數公式：
    =(x-μ)/σ
  • 其中：
  • x 是您要檢視的資料點，
    μ是您資料集的平均值，
    σ是您資料集的標準差。
  • 使用 STANDARDIZE 函數：若要採用更整合的方式，Excel 的 STANDARDIZE 函數可直接根據資料點、平均值和標準差計算 z 分數：
    =STANDARDIZE(x, mean, standard_dev)

在 Excel 中計算 z 分數的公式範例

假設您在 A 欄中有一筆資料集，範圍從 A2 到 A101，以下是計算這些數值 z 分數的步驟：

  1. 計算平均值（μ）：使用 AVERAGE(range)函數輕鬆找出資料集的平均值（μ）。
    =AVERAGE(A2:A101)
    在 Excel 中使用 AVERAGE 函數計算平均值的截圖
  2. 計算標準差（σ）：請根據您的資料情境選擇最合適的公式。
    重要提示：為確保計算精準，務必選用符合您資料集特性的函數。（以我的資料 A2:A101 代表整個母體為例，我會使用第一個公式。）
    • 若您的資料代表整個母體（即這些數值並非取自更大群體的樣本），請使用 STDEV.P(range)函數。
      =STDEV.P(A2:A101)
    • 若您的資料是來自更大母體的樣本，或希望根據樣本估算母體標準差，請使用 STDEV.S(range) 函數。
      =STDEV.S(A2:A101)
      在 Excel 中使用 STDEV.P 函數計算標準差的截圖
  3. 計算 A2 中資料點的 z 分數：使用下列任一公式，兩者結果相同。（本例中，我將採用第二個公式。）
    • 手動計算方法為：將資料點減去平均值，再除以標準差。
      =(A2 - $E$2) / $E$3
    • 使用 STANDARDIZE(x, mean, standard_dev) 函數。
      =STANDARDIZE(A2, $E$2, $E$3)

      注意：美元符號（$）可確保公式無論複製到何處，始終鎖定參照特定儲存格（E2 為平均值，E3 為標準差）。

      在 Excel 中計算單一資料點 z 分數的截圖

  4. 為資料集中每個數值計算 z 分數：將步驟 3 中的公式向下複製至整欄，即可輕鬆完成所有數值的 z 分數計算！提示：按兩下儲存格的填滿控點，即可快速延伸公式。將 z 分數公式向下複製到 Excel 欄中的 GIF 動圖
提示：
  • 若要簡化整個資料集中 z 分數的計算，無需分別在不同儲存格中輸入平均值與標準差的公式，您可直接使用下列任一完整公式。
    =(A2 - AVERAGE($A$2:$A$101)) / STDEV.P($A$2:$A$101)
    =STANDARDIZE(A2, AVERAGE($A$2:$A$101), STDEV.P($A$2:$A$101))
  • 在科學與統計工作中，將 z 分數統一保留至小數點後三位，是一項值得推崇的標準做法。請選取您存放 z 分數的儲存格，並透過「常用」索引標籤中「數字」群組內的「減少小數位數」按鈕來達成此格式設定。

    Excel 中「減少小數位數」選項的截圖，用於將 z 分數格式化為三位小數

解讀資料中的 z 分數

解讀 z 分數對於掌握資料點在資料集中的相對位置與重要性至關重要。z 分數直接衡量該資料點距離平均值有多少個標準差，清楚呈現其偏離程度與罕見性。

與平均值的關係
  • z 分數 = 0：代表平均表現，該資料點恰好落在平均值上。
  • z 分數 > 0：代表數值高於平均，距離平均值越遠，表現越出色。
  • z 分數 < 0：代表數值低於平均，分數越低，表示偏離平均值的程度越大。
偏離程度
  • |z 分數| < 1：這些資料點接近平均值，落在常態分佈的主要區間內，展現標準表現。
  • |z 分數| < 2：表示與平均值偏離程度中等，此類觀測值雖不常見，但仍屬正常變異範圍內。
  • |z 分數| > 2：突顯遠離平均值的異常資料點，可能為離群值或明顯偏離預期常態的情形。

範例說明：

  • z 分數為 0.66 表示該資料點高於平均值 0.66 個標準差，不僅超越平均水準，而且仍處於典型的變異範圍內，相對接近整體趨勢。
  • 反之，z 分數為 -2.1 表示該資料點低於平均值 2.1 個標準差，明顯偏離典型範圍，顯示其顯著低於平均水準。

在 Excel 中計算 z 分數時需注意的事項

在 Excel 中計算 Z 分數時，精確度與準確性至關重要。為確保結果可靠，請務必留意以下關鍵事項：

  • 檢查資料是否符合常態分佈：z 分數最適用於呈常態分佈的資料。若您的資料集不符合此分佈，z 分數可能不適合作為分析工具。建議在進行 z 分數分析前，先執行常態性檢定。
  • 確保正確使用公式：請務必根據資料集特性選擇合適的標準差函數——STDEV.P 適用於整個母體，STDEV.S 則用於樣本。
  • 在 z 分數公式中對平均值與標準差使用絕對參照：當公式需套用至多個儲存格時，請務必對平均值與標準差使用絕對參照（例如）$A$1），以確保計算結果一致無誤！
  • 留意離群值：離群值會顯著影響平均值與標準差，進而導致計算出的 z 分數產生偏誤。
  • 確保資料完整性：計算 z 分數前，請務必確認資料集乾淨無誤。錯誤的資料輸入、重複項目或無關數值皆會顯著影響平均值與標準差，進而產生誤導性的 z 分數。
  • 避免過早四捨五入或截斷：Excel 能處理大量小數位數，保留這些位數有助於防止累積性捨入誤差扭曲最終的分析結果。

以上即為在 Excel 中計算 z 分數的完整教學內容，希望對您有所幫助！若您想掌握更多 Excel 技巧與訣竅，請點此處，立即瀏覽我們超過數千篇的實用教學文章，提升效率、輕鬆上手！