4.6 學(xué)生考試成績的標(biāo)準(zhǔn)分轉(zhuǎn)換與分析

案例背景
    廣東省普通高考從上個世紀(jì)90年代初開始實行標(biāo)準(zhǔn)分制度。2007年因高中實行新課程改革，考生高考時可以選擇不同科目，選考X科的考生人數(shù)也不一樣，就又改為使用原始分。由于實行標(biāo)準(zhǔn)分更為合理、科學(xué)，同時也有利于高中階段學(xué)校的招生選拔。因而我國有些城市在中考中也在開始實行標(biāo)準(zhǔn)分制度。標(biāo)準(zhǔn)分制度是根據(jù)教育測量學(xué)理論建立的一套有關(guān)分?jǐn)?shù)報告、分?jǐn)?shù)解釋和分?jǐn)?shù)使用的制度。
    不論我市今后是否會實行標(biāo)準(zhǔn)分制度，作為教師還是應(yīng)該了解標(biāo)準(zhǔn)分，學(xué)會將“原始分”轉(zhuǎn)換成標(biāo)準(zhǔn)分，從而科學(xué)、客觀的評價學(xué)生的學(xué)習(xí)狀況。
    下面以某中學(xué)高一第二學(xué)期期末考試成績?yōu)槔?，將原始分轉(zhuǎn)換成標(biāo)準(zhǔn)分Z和標(biāo)準(zhǔn)分T，然后應(yīng)用標(biāo)準(zhǔn)分對學(xué)生的學(xué)習(xí)狀況進行對比、分析及文理科分班導(dǎo)向。
    下面簡單的介紹標(biāo)準(zhǔn)分的概念和特性
    一、什么是“原始分”？什么是“標(biāo)準(zhǔn)分”？
    原始分是考試后直接從卷面上得到的分?jǐn)?shù)。 標(biāo)準(zhǔn)分是指通過原始分轉(zhuǎn)化而得到的一種地位量數(shù)，它反映考生成績在全體考生成績中的位置。因此，無論試題難或易，無論整體原始分偏高或偏低，整體標(biāo)準(zhǔn)分都沒有什么變化。
    二、標(biāo)準(zhǔn)分是怎樣計算出來的？ 根據(jù)教育統(tǒng)計學(xué)的原理，標(biāo)準(zhǔn)分Z是原始分與平均分的離差以標(biāo)準(zhǔn)差為單位的分?jǐn)?shù)，用公式表示為
　 　
　    為該次考試中全體考生的平均分；x為該次考試中考生個人所得的原始分；S為該次考試分?jǐn)?shù)的標(biāo)準(zhǔn)差。
    標(biāo)準(zhǔn)分Z有如下性質(zhì)：
    ⑴平均值為0，標(biāo)準(zhǔn)差為1；
    ⑵分?jǐn)?shù)之間等距，可以作加減運算；
    ⑶原始分轉(zhuǎn)換為標(biāo)準(zhǔn)分是線性轉(zhuǎn)換，不會改變原始分的分布形狀，也不改變原來分?jǐn)?shù)的位置次序。 通過轉(zhuǎn)換后得到的標(biāo)準(zhǔn)分Z在一般情況下都帶小數(shù)，而且會出現(xiàn)負值，實際使用時不太方便，所以還可以對Z分?jǐn)?shù)進行線性變換：Z′=αZ+β 式中，Z′為其他形式的標(biāo)準(zhǔn)分，α是轉(zhuǎn)換方程的斜率，β是轉(zhuǎn)換方程的截距。 我國普通高校全國招生統(tǒng)一考試所使用的標(biāo)準(zhǔn)分，就是用剛才介紹的方法進行轉(zhuǎn)換的。 即：T=500+100Z
    這就是我們通常所說的標(biāo)準(zhǔn)分T。這種標(biāo)準(zhǔn)分的平均值為500，也就是說，如果某考生的標(biāo)準(zhǔn)分為500，則該生的成績處于此次考試的中間位置。 當(dāng)然，這是在假定原始分呈正態(tài)分布的前提下進行的。如果原始分的分布不符合正態(tài)分布的要求，則要先進行正態(tài)化處理，再轉(zhuǎn)換為標(biāo)準(zhǔn)分，轉(zhuǎn)換后的分?jǐn)?shù)稱為正態(tài)化標(biāo)準(zhǔn)分，這就是我們所稱的標(biāo)準(zhǔn)分?jǐn)?shù)。
    三、使用標(biāo)準(zhǔn)分比使用原始分有什么好處？
    根據(jù)教育統(tǒng)計學(xué)的原理，原始分轉(zhuǎn)換成標(biāo)準(zhǔn)分的意義可以從下面的比較中反映出來：
    ⑴單個標(biāo)準(zhǔn)分能夠反映考生成績在全體考生成績中的位置，而單個原始分則不能。 例如，某考生某科的原始成績?yōu)?/SPAN>85分，無法說明其這科成績究竟如何，因為這與試題的難度有關(guān)，與總體考生的分?jǐn)?shù)有關(guān)。如果某考生某科的標(biāo)準(zhǔn)分為650，即Z分?jǐn)?shù)為1.5，則通過通過使用Excel相關(guān)函數(shù)，可求得對應(yīng)的百分比為0.93319，于是我們知道，該考生的成績超過了93.319%的考生的成績，這就是分?jǐn)?shù)解釋的標(biāo)準(zhǔn)化。
    ⑵不同學(xué)科的原始分不可比，而不同學(xué)科的標(biāo)準(zhǔn)分卻是可比的。 不同的學(xué)科，由于試題的難易程度不同，各學(xué)科的分?jǐn)?shù)價值也就不同。例如某考生的語文原始成績?yōu)?/SPAN>80分，數(shù)學(xué)原始成績?yōu)?/SPAN>70分，從原始分看，其語文成績優(yōu)于數(shù)學(xué)成績。但如果這次考試全體考生的語文原始分平均為86分，而數(shù)學(xué)原始分平均為60分，則該考生的語文成績處于全體考生的平均水平之下，而數(shù)學(xué)成績處于全體考生的平均水平之上，即該生的數(shù)學(xué)成績實質(zhì)上優(yōu)于語文成績。從標(biāo)準(zhǔn)分的角度來衡量，其語文標(biāo)準(zhǔn)分小于500分，而數(shù)學(xué)標(biāo)準(zhǔn)分大于500分。由于標(biāo)準(zhǔn)分代表了原始分在整體原始分中的位置，因此是可比的。 　　⑶不同學(xué)科的原始分不可加，而不同學(xué)科的標(biāo)準(zhǔn)分之間具有可加性。 既然不同學(xué)科的原始分不可比，那么也就不可加。多學(xué)科成績，只有在各科成績的平均值相同、標(biāo)準(zhǔn)差也相同的條件下，才能相加，否則是不科學(xué)的。這是因為各科命題難度不同，導(dǎo)致各科原始分之間不能直接比較，造成分?jǐn)?shù)解釋上的困難；各科原始分相加不合理。由于各科試卷難易的不同，試卷易(難)的學(xué)科得分易(難)，可知各科中的每1分的分值是不等的，把各科分值不等的原始分累加得到總分，就好比將100元人民幣加上100元港幣再加上100元美元得到300元一樣，是不能反映三種貨幣在總額中的真實價值的。各學(xué)科原始分的平均值以及標(biāo)準(zhǔn)差一般都不相同，而各學(xué)科的標(biāo)準(zhǔn)分的平均值（）以及標(biāo)準(zhǔn)差都是相同的，因此，各科的標(biāo)準(zhǔn)分是可加的。

要實現(xiàn)本案例中的功能，學(xué)員應(yīng)該掌握以下EXCEL技術(shù)點
    ●函數(shù)應(yīng)用 STDEVP函數(shù)，STANDARDIZE函數(shù)，NORMSDIST函數(shù)
    ●綜合應(yīng)用
最終效果展示

4.6.1  創(chuàng)建原始數(shù)據(jù)表
Step 1創(chuàng)建工作簿、重名工作表
創(chuàng)建工作簿“標(biāo)準(zhǔn)分轉(zhuǎn)換.xls”，然后將工作表sheet1，sheet2和sheet3分別重命名為“原始分”、“Z分?jǐn)?shù)”和“T分?jǐn)?shù)”。

Step2輸入原始成績
①在單元格區(qū)域A1：L1分別輸入“班級”，“姓名”，“語文”，“數(shù)學(xué)”，“外語”，“物理”，“化學(xué)”，“政治”，“歷史”，“地理”，“總分”，“年級排名”。

②輸入高一年級1班、2班……8班全部465名學(xué)生的班級、姓名、和各科成績。

Step3格式化原始成績表
選中單元格區(qū)域A1:L466,為表格添加邊框，文本設(shè)置為“標(biāo)宋12號及居中對齊”。
Step4計算總分
①選中單元格K2，單擊常用工具欄按鈕“自動求和”函數(shù)，其默認的參數(shù)系列是其左側(cè)的數(shù)值區(qū)間C2:J2，按鍵確認即可得到第一個學(xué)生的總分。

②雙擊單元格K2右下角的填充柄即可完成其余學(xué)生的總分計算工作。

Step5統(tǒng)計年級排名
①選中單元格L2, 輸入以下公式:  ”= RANK(K2, $K$2:$K$466,0)” 然后按鍵確認。

②選中單元格L2，移動光標(biāo)到該單元格的右下角，當(dāng)光標(biāo)變?yōu)?/SPAN>+形狀時，雙擊填充柄即可完成公式的填充，統(tǒng)計出其余學(xué)生的年級名次。

Step6統(tǒng)計平均分和標(biāo)準(zhǔn)差
①在單元格A467和A468分別輸入“平均分”和“標(biāo)準(zhǔn)差”。

②選中單元格A467，單擊常用工具欄“自動求和”右側(cè)的下箭頭按鈕，在彈出的列表中選擇“平均值”，其默認的參數(shù)區(qū)域正確，按鍵確認即可統(tǒng)計出語文學(xué)科的平均分。

③選中單元格A468，單擊常用工具欄“插入函數(shù)”按鈕，彈出“插入函數(shù)“，在”或函數(shù)類別“選項框中選擇”統(tǒng)計“，在”選擇函數(shù)“選項框中選擇” STDEVP“。

④單擊確定按鈕彈出“函數(shù)參數(shù)“對話框，在第一個參數(shù)框中將默認的數(shù)值參數(shù)區(qū)域C2:C467，修改為C2:C466。

⑤單擊確定按鈕即可統(tǒng)計出語文學(xué)科的標(biāo)準(zhǔn)差。

⑥選中單元格區(qū)域C467:C468，光標(biāo)移至單元格C468右下角，向右拖曳填充柄至J列松開鼠標(biāo)即可完成其余學(xué)科課程的“平均分”和“標(biāo)準(zhǔn)差”的統(tǒng)計工作。

⑦選中單元格區(qū)域A467:L468，為該區(qū)域設(shè)置邊框。

4.6.2  Z分?jǐn)?shù)的轉(zhuǎn)換與應(yīng)用
Step1創(chuàng)建Z分?jǐn)?shù)轉(zhuǎn)換表
①選中單元格區(qū)域A1:L468，按組合鍵復(fù)制，單擊工作表標(biāo)簽“Z分?jǐn)?shù)”，單擊單元格A1，按組合鍵粘貼

②選中單元格區(qū)域C2:J466，按鍵清除該區(qū)域內(nèi)容。


Step2用Z分?jǐn)?shù)計算公式轉(zhuǎn)換原始分為標(biāo)準(zhǔn)分
①選中單元格C2，輸入公式
“=(原始分!C$2-原始分!C$467)/原始分!C$468”
按鍵確認皆可完成第一個學(xué)生語文原始分到標(biāo)準(zhǔn)分Z分?jǐn)?shù)的轉(zhuǎn)換。

②選中單元格C2，光標(biāo)移至單元格C2右下角，向右拖曳填充柄至J列松開鼠標(biāo)即可完成第一個學(xué)生其余學(xué)科課程原始分到Z分?jǐn)?shù)的轉(zhuǎn)換。

Step3使用Excel函數(shù)轉(zhuǎn)換原始分為標(biāo)準(zhǔn)分
①選中單元格C3，單擊單擊常用工具欄“插入函數(shù)”按鈕，彈出“插入函數(shù)“，在”或函數(shù)類別“選項框中選擇”統(tǒng)計“，在”選擇函數(shù)“選項框中選擇” STANDARDIZE “。

②單擊確定按鈕彈出“函數(shù)參數(shù)“對話框，在第一個參數(shù)框”X“中輸入”C3“，在第二個參數(shù)框 “Mean”中輸入條件“原始分!C$467”， 在第三個參數(shù)框“Stdara_dev”中輸入“原始分!C$468”。


③單擊確定按鈕即可完成第二個學(xué)生語文原始分到標(biāo)準(zhǔn)分Z分?jǐn)?shù)的轉(zhuǎn)換。

④選中單元格C3，光標(biāo)移至單元格C3右下角，向右拖曳填充柄至J列松開鼠標(biāo)即可完成第二個學(xué)生其余學(xué)科課程原始分到Z分?jǐn)?shù)的轉(zhuǎn)換。

⑤選中單元格區(qū)域C3:J3，移動光標(biāo)到該單元格J3的右下角，當(dāng)光標(biāo)變?yōu)?/SPAN>+形狀時，雙擊填充柄即可完成公式的填充，從而將其余學(xué)生的各科原始分轉(zhuǎn)換成Z分?jǐn)?shù)。同時在對應(yīng)單元格預(yù)留的“平均分”、“標(biāo)準(zhǔn)差”、“總分”和“年級名次”的計算公式也完成了相應(yīng)統(tǒng)計任務(wù)。

⑥選中單元格區(qū)域C2:J466，選中單元格區(qū)域C3:J3，單擊編輯菜單，選擇，從彈出的列表中選擇“選擇性粘貼”命令，彈出 “選擇性粘貼”對話框。在彈出的“選擇性粘貼”對話框的“粘貼”選項框中單擊“數(shù)值”按鈕。

⑦單擊確定按鈕即可完成該區(qū)域公式的隱藏，從而實現(xiàn)在該區(qū)域里只保留公式計算結(jié)果，而與引用區(qū)域脫鉤。

Step4應(yīng)用Z分?jǐn)?shù)對比學(xué)生學(xué)習(xí)情況
下面選擇1班學(xué)生陳忠，2班學(xué)生葉樂樂和6班學(xué)生陳偉的原始分與Z分?jǐn)?shù)的年級名次情況進行對比分析。
①單擊工作表標(biāo)簽“原始分”，單擊K列的任意一個單元格，如單元格K3，單擊常用工具欄“降序排序”按鈕，按總分由高到低排序。
②分別選中按原始分排名年級名次的第6、7、8名學(xué)生，即1班學(xué)生陳忠，2班學(xué)生葉樂樂和6班學(xué)生陳偉所在的單元格區(qū)域A8:L8，A9:L9，和A10:L0，單擊常用工具欄“顏色填充”按鈕，分別選擇淺黃、淺綠和玫瑰紅為其設(shè)置底紋顏色加以標(biāo)記。

③光標(biāo)切換回工作表“Z分?jǐn)?shù)”，單擊K列的任意一個單元格，如單元格K2，單擊常用工具欄“降序排序”按鈕，按總分由高到低排序。
④同樣找到1班學(xué)生陳忠，2班學(xué)生葉樂樂和6班學(xué)生陳偉所在的單元格區(qū)域A11:L11，A7:L7，和A5: L 5，單擊常用工具欄“顏色填充”按鈕，分別選擇淺黃、淺綠和玫瑰紅為其設(shè)置底紋顏色加以標(biāo)記。


Step5應(yīng)用Z分?jǐn)?shù)指導(dǎo)學(xué)生分別選擇文、理科班學(xué)習(xí)
下面從“Z分?jǐn)?shù)“工作表中選擇幾名學(xué)生成績進行分析。

上表中前2名學(xué)生理科課程物理、化學(xué)的Z分?jǐn)?shù)都是負值，文科課程政治、歷史和地理的Z分?jǐn)?shù)都是正值，而第三名學(xué)生正好相反，理科課程物理、化學(xué)的Z分?jǐn)?shù)都是正值，文科課程政治、歷史和地理的Z分?jǐn)?shù)都是負值，且文理科成績差距明顯，因此可以建議1班學(xué)生常微，4班學(xué)生劉世欣高二選擇文科班，6班學(xué)生魯秋梅選擇理科班。

4.3.6  T分?jǐn)?shù)的轉(zhuǎn)換與應(yīng)用

Step1創(chuàng)建T分?jǐn)?shù)轉(zhuǎn)換表
①單擊工作表標(biāo)簽“原始分”，選中單元格區(qū)域A1：C468，按組合鍵復(fù)制，單擊工作表標(biāo)簽“T分?jǐn)?shù)”，選中單元格A1, 按組合鍵粘貼。

②選中單元格A1，單擊常用工具欄的“格式刷”按鈕，在選擇單元格區(qū)域A7:C9，使用格式刷去掉該區(qū)域的底紋顏色。

③雙擊單元格C1將其激活準(zhǔn)備修改字段標(biāo)題，將光標(biāo)放到已有的字段標(biāo)題”語文”的后面，按組合鍵實現(xiàn)單元格內(nèi)換行，然后輸入“原始分”。

④在單元格D1、E1、F1、G1和 H1中分別輸入“語文Z分?jǐn)?shù)”，“語文T分?jǐn)?shù)”，“考分以下百分比”,“考生總數(shù)”和“考分以上人數(shù)”，單擊常用工具欄“居中”按鈕。

⑤選中單元格區(qū)域A1:H468，為該區(qū)域設(shè)置邊框。

⑥在單元格區(qū)域D2：D466中，參照4.6.2節(jié)Step3中的方法，使用STANDARDIZE函數(shù)將原始分轉(zhuǎn)換成Z分?jǐn)?shù)。

Step2轉(zhuǎn)換Z分?jǐn)?shù)為T分?jǐn)?shù)
①在單元格E2中輸入公式：“=100*D2+500”，按鍵確認。這時將第一個學(xué)生的Z分?jǐn)?shù)轉(zhuǎn)換成平均分為500，標(biāo)準(zhǔn)差為100的T分?jǐn)?shù)。

②選中單元格E2，雙擊單元格E2右下角的填充柄即可完成其余學(xué)生的T分?jǐn)?shù)的轉(zhuǎn)換。

Step3統(tǒng)計每位學(xué)生考分以下的百分比
    每位學(xué)生可以首先通過的自己的T分?jǐn)?shù)反求出對應(yīng)的Z分?jǐn)?shù)，再使用NORMSDIST函數(shù)統(tǒng)計出該考分以下的百分比，進而求出該考分以上有多少考生。采用T分?jǐn)?shù)錄取的高考考生就是這樣用個人的高考綜合分（T分?jǐn)?shù)）計算出自己在全體考生中的位置的。
①選中單元格F2，單擊單擊常用工具欄“插入函數(shù)”按鈕，彈出“插入函數(shù)“，在”或函數(shù)類別“選項框中選擇”統(tǒng)計“，在”選擇函數(shù)“選項框中選擇” NORMSDIST “。

②單擊確定按鈕彈出“函數(shù)參數(shù)“對話框，在參數(shù)框”Z“中輸入”D2“。

③單擊確定按鈕即可求出第一個學(xué)生的語文成績以下所占百分比。

④選中單元格F2，雙擊單元格F2右下角的填充柄即可完成其余學(xué)生語文成績以下所占百分比的統(tǒng)計工作。

Step4輸入考生總數(shù)
選中單元格區(qū)域G2:G466，輸入“465“，按組合鍵確認即可完成相同內(nèi)容的批量輸入。

Step4統(tǒng)計“考分“以上人數(shù)
在單元格H2輸入公式：“=ROUND(G2-F2*G2,0)“，按鍵確認即可統(tǒng)計出該考分前的考生人數(shù)。

②選中單元格H2，雙擊單元格H2右下角的填充柄即可的統(tǒng)計出其余每個學(xué)生考分前的考生人數(shù)。

關(guān)鍵知識點講解：
1.STDEVP函數(shù)
函數(shù)名稱：STDEVP
主要功能：返回以參數(shù)形式給出的整個樣本總體的標(biāo)準(zhǔn)偏差。標(biāo)準(zhǔn)偏差反映相對于平均值 (mean) 的離散程度。
使用格式：STDEVP(number1,number2,...)
參數(shù)說明：
Number1,number2,...     為對應(yīng)于樣本總體的 1 到 30 個參數(shù)。也可以不使用這種用逗號分隔參數(shù)的形式，而用單個數(shù)組或?qū)?shù)組的引用。
    ●文本和邏輯值（TRUE 或 FALSE）將被忽略。如果不能忽略邏輯值和文本，則請使用 STDEVPA 工作表函數(shù)。
函數(shù)說明
    ●函數(shù) STDEVP 假設(shè)其參數(shù)為整個樣本總體。如果數(shù)據(jù)代表樣本總體中的樣本，應(yīng)使用函數(shù) STDEV 來計算標(biāo)準(zhǔn)偏差。
    ●對于大樣本容量，函數(shù) STDEV 和 STDEVP 計算結(jié)果大致相等。
    ●此處標(biāo)準(zhǔn)偏差的計算使用“有偏差”和“n”方法。
    ●函數(shù) STDEVP 的計算公式如下：

    其中 x 為樣本平均值 AVERAGE(number1,number2,…)，n 為樣本大小。
應(yīng)用示例：

2.STANDARDIZE函數(shù)
函數(shù)名稱：STANDARDIZE
主要功能：返回以 mean 為平均值，以 standard-dev 為標(biāo)準(zhǔn)偏差的分布的正態(tài)化數(shù)值。
使用格式：STANDARDIZE(x,mean,standard_dev)
參數(shù)說明：
X    為需要進行正態(tài)化的數(shù)值。
Mean     分布的算術(shù)平均值。
Standard_dev     分布的標(biāo)準(zhǔn)偏差。
函數(shù)說明
    ●如果 standard_dev ≤ 0，函數(shù) STANDARDIZE 返回錯誤值 #NUM!。
應(yīng)用示例：

3.NORMSDIST函數(shù)
函數(shù)名稱：NORMSDIST
主要功能：返回標(biāo)準(zhǔn)正態(tài)累積分布函數(shù)，該分布的平均值為 0，標(biāo)準(zhǔn)偏差為 1。可以使用該函數(shù)代替標(biāo)準(zhǔn)正態(tài)曲線面積表。
使用格式：NORMSDIST(z)
參數(shù)說明： Z    為需要計算其分布的數(shù)值。
函數(shù)說明
    ●如果 z 為非數(shù)值型，函數(shù) NORMSDIST 返回錯誤值 #VALUE!。
    ●標(biāo)準(zhǔn)正態(tài)分布密度函數(shù)計算公式如下：

    應(yīng)用示例：