如何使用 Microsoft Excel 計算 Z 分數

已發表: 2022-01-29

Z 分數是一個統計值，它告訴您某個特定值恰好與整個數據集的平均值有多少標準偏差。您可以使用 AVERAGE 和 STDEV.S 或 STDEV.P 公式計算數據的平均值和標準差，然後使用這些結果來確定每個值的 Z 分數。

什麼是 Z 分數，AVERAGE、STDEV.S 和 STDEV.P 函數有什麼作用？

Z 分數是比較來自兩個不同數據集的值的一種簡單方法。它被定義為遠離數據點平均值的標準偏差數。一般公式如下所示：

 =(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)

這是一個幫助澄清的例子。假設您想比較由不同老師教的兩個代數學生的測試結果。你知道第一個學生在一個班級的期末考試中得到了 95%，而另一個班級的學生得到了 87%。

乍看之下，95%的成績更令人印象深刻，但如果二班的老師給出了更難的考試呢？您可以根據每個班級的平均成績和每個班級的成績標準差來計算每個學生的 Z-Score。比較這兩名學生的 Z 分數可以發現，與班上其他學生相比，得分為 87% 的學生比得分為 98% 的學生與班上其他學生相比表現更好。

您需要的第一個統計值是“平均值”，Excel 的“平均”函數會計算該值。它只是將單元格範圍內的所有值相加，然後將該總和除以包含數值的單元格數（它忽略空白單元格）。

我們需要的另一個統計值是“標準偏差”，Excel 有兩個不同的函數來計算標準偏差，方式略有不同。

以前的 Excel 版本只有“STDEV”函數，它計算標準偏差，同時將數據視為總體的“樣本”。 Excel 2010 將其分解為兩個計算標準偏差的函數：

STDEV.S：此功能與之前的“STDEV”功能相同。它計算標準偏差，同時將數據視為總體的“樣本”。人口樣本可能類似於為研究項目收集的特定蚊子或被擱置並用於碰撞安全測試的汽車。
STDEV.P：此函數計算標準偏差，同時將數據視為整個總體。整個人口就像地球上的所有蚊子或特定型號生產中的每輛汽車。

您選擇哪個取決於您的數據集。差異通常很小，但對於相同的數據集，“STDEV.P”函數的結果總是小於“STDEV.S”函數的結果。假設數據中存在更多可變性是一種更保守的方法。

讓我們看一個例子

在我們的示例中，我們有兩列（“Values”和“Z-Score”）和三個“helper”單元格，用於存儲“AVERAGE”、“STDEV.S”和“STDEV.P”函數的結果。 “值”列包含十個以 500 為中心的隨機數，“Z-Score”列是我們將使用存儲在“幫助”單元格中的結果計算 Z-Score 的地方。

首先，我們將使用“AVERAGE”函數計算值的平均值。選擇要存儲“AVERAGE”函數結果的單元格。

輸入以下公式並按回車鍵 - 或 - 使用“公式”菜單。

 =平均（E2：E13）

要通過“公式”菜單訪問該函數，請選擇“更多函數”下拉菜單，選擇“統計”選項，然後單擊“平均”。

在 Function Arguments 窗口中，選擇“Values”列中的所有單元格作為“Number1”字段的輸入。您無需擔心“Number2”字段。

現在按“確定”。

接下來，我們需要使用“STDEV.S”或“STDEV.P”函數計算值的標準偏差。在此示例中，我們將向您展示如何計算這兩個值，從“STDEV.S”開始。選擇將存儲結果的單元格。

要使用“STDEV.S”函數計算標準偏差，請輸入此公式並按 Enter（或通過“公式”菜單訪問）。

 =STDEV.S(E3:E12)

要通過“公式”菜單訪問該函數，請選擇“更多函數”下拉菜單，選擇“統計”選項，向下滾動一點，然後單擊“STDEV.S”命令。

在 Function Arguments 窗口中，選擇“Values”列中的所有單元格作為“Number1”字段的輸入。您也不必擔心這裡的“Number2”字段。

現在按“確定”。

接下來，我們將使用“STDEV.P”函數計算標準偏差。選擇將存儲結果的單元格。

要使用“STDEV.P”函數計算標準偏差，請輸入此公式並按 Enter（或通過“公式”菜單訪問）。

=STDEV.P(E3:E12)

要通過“公式”菜單訪問函數，請選擇“更多函數”下拉菜單，選擇“統計”選項，向下滾動一點，然後單擊“STDEV.P”公式。

在 Function Arguments 窗口中，選擇“Values”列中的所有單元格作為“Number1”字段的輸入。同樣，您無需擔心“Number2”字段。

現在按“確定”。

既然我們已經計算了數據的平均值和標準差，我們就擁有了計算 Z 分數所需的一切。我們可以使用一個簡單的公式來引用包含“AVERAGE”和“STDEV.S”或“STDEV.P”函數結果的單元格。

選擇“Z-Score”列中的第一個單元格。我們將在本例中使用“STDEV.S”函數的結果，但您也可以使用“STDEV.P”的結果。

輸入以下公式，然後按 Enter：

 =(E3-$G$3)/$H$3

或者，您可以使用以下步驟來輸入公式，而不是鍵入：

單擊單元格 F3 並鍵入=(
選擇單元格 E3。（您可以按一次左箭頭鍵或使用鼠標）
輸入減號-
選擇單元格 G3 然後按F4添加“$”字符以對單元格進行“絕對”引用（它將循環通過“G3”>“ $ G $ 3”>“G $ 3”>“ $ G3”> “G3”，如果您繼續按F4 ）
類型)/
選擇單元格 H3（或 I3，如果您使用“STDEV.P”）並按F4添加兩個“$”字符。
按回車

已為第一個值計算 Z 分數。它比平均值低 0.15945 個標準差。要檢查結果，您可以將標準差乘以該結果 (6.271629 * -0.15945)，並檢查結果是否等於值與平均值之間的差 (499-500)。兩個結果是相等的，所以這個值是有意義的。

讓我們計算其餘值的 Z 分數。突出顯示從包含公式的單元格開始的整個“Z 分數”列。

按 Ctrl+D，將頂部單元格中的公式向下複製到所有其他選定的單元格。

現在公式已“填充”到所有單元格，並且每個單元格都將始終引用正確的“AVERAGE”和“STDEV.S”或“STDEV.P”單元格，因為“$”字符。如果您遇到錯誤，請返回並確保您輸入的公式中包含“$”字符。

在不使用“輔助”單元的情況下計算 Z 分數

輔助單元存儲結果，就像存儲“AVERAGE”、“STDEV.S”和“STDEV.P”函數結果的單元格一樣。它們可能很有用，但並不總是必要的。您可以在使用以下通用公式計算 Z 分數時完全跳過它們。

這是使用“STDEV.S”功能的一個：

 =(Value-AVERAGE(Values))/STDEV.S(Values)

還有一個使用“STEV.P”功能：

 =(Value-AVERAGE(Values))/STDEV.P(Values)

在函數中輸入“值”的單元格範圍時，請務必添加絕對引用（使用 F4 的“$”），以便在“填充”時不會計算不同範圍的平均值或標準偏差每個公式中的單元格。

如果您有一個大型數據集，使用輔助單元格可能更有效，因為它不會每次都計算“AVERAGE”和“STDEV.S”或“STDEV.P”函數的結果，從而節省處理器資源和加快計算結果的時間。

此外，與“AVERAGE($E$3:$E$12).”相比，“$G$3”需要更少的字節來存儲和更少的 RAM 來加載。這一點很重要，因為標準 32 位版本的 Excel 被限制為 2GB 的 RAM（64 位版本對可以使用多少 RAM 沒有任何限制）。