Z分數是一個統計值,它告訴你一個特定值與整個資料集的平均值之間的標準差有多少。您可以使用AVERAGE和STDEV.S或STDEV.P公式來計算資料的平均值和標準偏差,然後使用這些結果來確定每個值的Z值。
Z分數是比較兩個不同資料集的值的簡單方法。它被定義為遠離資料點所在的平均值的標準偏差的數量。一般公式如下:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)下面是一個有助於澄清的例子。假設你想比較不同老師教的兩個代數學生的測試結果。你知道第一個學生在一個班的期末考試中得了95%,而另一個班的學生得了87%。
乍一看,95%的成績更讓人印象深刻,但如果第二堂課的老師考得更難呢?你可以根據每個班級的平均分數和每個班級分數的標準差來計算每個學生的Z分數。比較兩個學生的Z分數可以發現,與他們班的其他學生相比,得分為87%的學生比得分為98%的學生表現得更好。
您需要的第一個統計值是“平均值”,Excel的“平均值”函式計算該值。它只是將單元格範圍內的所有值相加,然後除以包含數值的單元格數(忽略空白單元格)。
我們需要的另一個統計值是“標準差”,Excel有兩個不同的函式以稍微不同的方式計算標準差。
以前版本的Excel只有“STDEV”函式,它計算標準差,同時將資料作為總體的“樣本”。Excel 2010將其分解為兩個計算標準差的函式:
您選擇的是基於您的資料集。差異通常很小,但是對於相同的資料集,“STDEV.P”函式的結果總是小於“STDEV.S”函式的結果。假設資料中存在更多的可變性是一種更為保守的方法。
對於我們的示例,我們有兩列(“Values”和“Z-Score”)和三個“helper”單元格,用於儲存“AVERAGE”、“STDEV.S”和“STDEV.P”函式的結果。“Values”列包含10個以500為中心的隨機數,“Z-Score”列是我們使用“helper”單元格中儲存的結果計算Z-Score的位置。
首先,我們將使用“平均”函式計算值的平均值。選擇儲存“AVERAGE”函式結果的單元格。
鍵入以下公式,然後按enter鍵或使用“公式”選單。
=AVERAGE(E2:E13)要透過“公式”選單訪問函式,請選擇“更多函式”下拉列表,選擇“統計”選項,然後單擊“平均”
在“函式引數”視窗中,選擇“值”列中的所有單元格作為“Number1”欄位的輸入。您不必擔心“Number2”欄位。
現在按“好”
接下來,我們需要使用“STDEV.S”或“STDEV.P”函式計算值的標準偏差。在本例中,我們將向您展示如何計算這兩個值,從“STDEV.S”開始。選擇儲存結果的單元格。
要使用“STDEV.S”函式計算標準偏差,請鍵入此公式並按Enter鍵(或透過“公式”選單訪問)。
=STDEV.S(E3:E12)要透過“公式”選單訪問函式,請選擇“更多函式”下拉列表,選擇“統計”選項,向下滾動一點,然後單擊“STDEV.S”命令。
在“函式引數”視窗中,選擇“值”列中的所有單元格作為“Number1”欄位的輸入。你也不必擔心這裡的“Number2”欄位。
現在按“好”
接下來,我們將使用“STDEV.P”函式計算標準偏差。選擇儲存結果的單元格。
要使用“STDEV.P”函式計算標準偏差,請鍵入此公式並按Enter鍵(或透過“公式”選單訪問)。
=標準偏差P(E3:E12)
要透過“公式”選單訪問函式,請選擇“更多函式”下拉列表,選擇“統計”選項,向下滾動一點,然後單擊“STDEV.P”公式。
在“函式引數”視窗中,選擇“值”列中的所有單元格作為“Number1”欄位的輸入。同樣,您不需要擔心“Number2”欄位。
現在按“好”
現在我們已經計算了資料的平均值和標準差,我們已經有了計算Z分數所需的一切。我們可以使用一個簡單的公式來引用包含“AVERAGE”和“STDEV.S”或“STDEV.P”函式結果的單元格。
選擇“Z分數”列中的第一個單元格。在本例中,我們將使用“STDEV.S”函式的結果,但是您也可以使用“STDEV.P”函式的結果
鍵入以下公式並按Enter鍵:
=(E3-$G$3)/$H$3或者,可以使用以下步驟輸入公式,而不是鍵入:
已經為第一個值計算了Z分數。它比平均值低0.15945個標準差。要檢查結果,可以將標準偏差乘以該結果(6.271629*-0.15945),並檢查結果是否等於值與平均值之間的差值(499-500)。兩個結果是相等的,所以這個值是有意義的。
讓我們計算其餘值的Z值。突出顯示從包含公式的單元格開始的整個“Z分數”列。
按Ctrl+D,將頂部單元格中的公式向下複製到所有其他選定單元格中。
現在,公式已“填充”到所有單元格,由於“$”字元,每個單元格將始終引用正確的“AVERAGE”和“STDEV.S”或“STDEV.P”單元格。如果出現錯誤,請返回並確保輸入的公式中包含“$”字元。
助手單元格儲存結果,就像那些儲存“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”函式的結果,從而節省處理器資源並加快計算結果所需的時間。
而且,“$G$3”比“平均值($E$3:$E$12)”需要更少的位元組儲存和更少的RAM載入。這一點很重要,因為Excel的標準32位版本限制為2GB的RAM(64位版本對可以使用多少RAM沒有任何限制)。
大多數時候,搜尋microsoftexcel電子表格是相當容易的。如果您不能只掃描它的行和列,您可以使用Ctrl+F來搜尋它。如果您使用的是一個非常大的電子表格,那麼使用這四個查詢函式之一可以節省大量時間。 ...
...處理多組資料而不是一組資料時最有用。它充當資料集和microsoftexcel之間的橋樑。 ...
如果您在microsoftexcel中處理資料,那麼建立圖表是顯示資料的一種乾淨而有吸引力的方式。Excel提供了許多圖表型別,從餅圖到條形圖再到折線圖。 ...
每個人都知道IF語句在指令碼程式中的用途有多廣泛,但是您知道您可以在Excel的單元格中使用許多相同的邏輯嗎? ...
... microsoftexcel的Mac版本看起來與Windows版本非常不同。這門課程用蘋果的同類產品取代了Windows的圖形和選單樹,非常適合蘋果生態系統中的人們。 ...
...大多數“高階”線上課程只包括一些短小的課程的地方,microsoftexcel捆綁包涵蓋了超過43小時的教育內容。 ...
一旦你進入一個龐大的排序選項列表,microsoftexcel就會成為一個強大的工具。這裡我們將介紹它最直接的排序選項,這是一個簡單的選項,使我們能夠對特定列中的資料重新排序。 在電子表格中,突出顯示包含要排序的標題的...
如果需要從Microsoft Excel中的日期快速提取年份,可以使用“年份”函式。這將在單獨的單元格中為您提供年份值,允許您在單獨的公式中使用它。 如果在Excel中將文字轉換為日期值,並且希望從資料中提取年份值,則年份函式...
理貨圖是一張理貨標記表,用來表示發生某事的頻率。microsoftexcel有大量的內建圖表型別可用,但它沒有計數圖選項。幸運的是,這可以使用Excel公式建立。 在本例中,我們希望建立一個統計圖來視覺化列表中每個人收到的選票...
...公式和自動計算建立檔案的軟體時,你可能會想到一個像microsoftexcel或googlesheets這樣的電子表格程式。然而,電子表格的性質和格式使得它們難以使用,也不適合作為一種形式分發給人們。 幸運的是,adobeacrobat,一個頂級的PDF建...