4.6 学生考试成绩的标准分转换与分析
案例背景
广东省普通高考从上个世纪90年代初开始实行标准分制度。2007年因高中实行新课程改革，考生高考时可以选择不同科目，选考X科的考生人数也不一样，就又改为使用原始分。由于实行标准分更为合理、科学，同时也有利于高中阶段学校的招生选拔。因而我国有些城市在中考中也在开始实行标准分制度。标准分制度是根据教育测量学理论建立的一套有关分数报告、分数解释和分数使用的制度。
不论我市今后是否会实行标准分制度，作为教师还是应该了解标准分，学会将“原始分”转换成标准分，从而科学、客观的评价学生的学习状况。
下面以某中学高一第二学期期末考试成绩为例，将原始分转换成标准分Z和标准分T，然后应用标准分对学生的学习状况进行对比、分析及文理科分班导向。
下面简单的介绍标准分的概念和特性
一、什么是“原始分”？什么是“标准分”？
原始分是考试后直接从卷面上得到的分数。 标准分是指通过原始分转化而得到的一种地位量数，它反映考生成绩在全体考生成绩中的位置。因此，无论试题难或易，无论整体原始分偏高或偏低，整体标准分都没有什么变化。
二、标准分是怎样计算出来的？ 根据教育统计学的原理，标准分Z是原始分与平均分的离差以标准差为单位的分数，用公式表示为
为该次考试中全体考生的平均分；x为该次考试中考生个人所得的原始分；S为该次考试分数的标准差。
标准分Z有如下性质：
⑴平均值为0，标准差为1；
⑵分数之间等距，可以作加减运算；
⑶原始分转换为标准分是线性转换，不会改变原始分的分布形状，也不改变原来分数的位置次序。 通过转换后得到的标准分Z在一般情况下都带小数，而且会出现负值，实际使用时不太方便，所以还可以对Z分数进行线性变换：Z′=αZ+β 式中，Z′为其他形式的标准分，α是转换方程的斜率，β是转换方程的截距。 我国普通高校全国招生统一考试所使用的标准分，就是用刚才介绍的方法进行转换的。 即：T=500+100Z
这就是我们通常所说的标准分T。这种标准分的平均值为500，也就是说，如果某考生的标准分为500，则该生的成绩处于此次考试的中间位置。 当然，这是在假定原始分呈正态分布的前提下进行的。如果原始分的分布不符合正态分布的要求，则要先进行正态化处理，再转换为标准分，转换后的分数称为正态化标准分，这就是我们所称的标准分数。
三、使用标准分比使用原始分有什么好处？
根据教育统计学的原理，原始分转换成标准分的意义可以从下面的比较中反映出来：
⑴单个标准分能够反映考生成绩在全体考生成绩中的位置，而单个原始分则不能。 例如，某考生某科的原始成绩为85分，无法说明其这科成绩究竟如何，因为这与试题的难度有关，与总体考生的分数有关。如果某考生某科的标准分为650，即Z分数为1.5，则通过通过使用Excel相关函数，可求得对应的百分比为0.93319，于是我们知道，该考生的成绩超过了93.319%的考生的成绩，这就是分数解释的标准化。
⑵不同学科的原始分不可比，而不同学科的标准分却是可比的。 不同的学科，由于试题的难易程度不同，各学科的分数价值也就不同。例如某考生的语文原始成绩为80分，数学原始成绩为70分，从原始分看，其语文成绩优于数学成绩。但如果这次考试全体考生的语文原始分平均为86分，而数学原始分平均为60分，则该考生的语文成绩处于全体考生的平均水平之下，而数学成绩处于全体考生的平均水平之上，即该生的数学成绩实质上优于语文成绩。从标准分的角度来衡量，其语文标准分小于500分，而数学标准分大于500分。由于标准分代表了原始分在整体原始分中的位置，因此是可比的。 ⑶不同学科的原始分不可加，而不同学科的标准分之间具有可加性。 既然不同学科的原始分不可比，那么也就不可加。多学科成绩，只有在各科成绩的平均值相同、标准差也相同的条件下，才能相加，否则是不科学的。这是因为各科命题难度不同，导致各科原始分之间不能直接比较，造成分数解释上的困难；各科原始分相加不合理。由于各科试卷难易的不同，试卷易(难)的学科得分易(难)，可知各科中的每1分的分值是不等的，把各科分值不等的原始分累加得到总分，就好比将100元人民币加上100元港币再加上100元美元得到300元一样，是不能反映三种货币在总额中的真实价值的。各学科原始分的平均值以及标准差一般都不相同，而各学科的标准分的平均值（）以及标准差都是相同的，因此，各科的标准分是可加的。
要实现本案例中的功能，学员应该掌握以下EXCEL技术点
●函数应用 STDEVP函数，STANDARDIZE函数，NORMSDIST函数
●综合应用
最终效果展示
4.6.1 创建原始数据表
Step 1创建工作簿、重名工作表
创建工作簿“标准分转换.xls”，然后将工作表sheet1，sheet2和sheet3分别重命名为“原始分”、“Z分数”和“T分数”。
Step2输入原始成绩
①在单元格区域A1：L1分别输入“班级”，“姓名”，“语文”，“数学”，“外语”，“物理”，“化学”，“政治”，“历史”，“地理”，“总分”，“年级排名”。
②输入高一年级1班、2班……8班全部465名学生的班级、姓名、和各科成绩。
Step3格式化原始成绩表
选中单元格区域A1:L466,为表格添加边框，文本设置为“标宋12号及居中对齐”。
Step4计算总分
①选中单元格K2，单击常用工具栏按钮“自动求和”函数，其默认的参数系列是其左侧的数值区间C2:J2，按键确认即可得到第一个学生的总分。
②双击单元格K2右下角的填充柄即可完成其余学生的总分计算工作。
Step5统计年级排名
①选中单元格L2, 输入以下公式: ”= RANK(K2, $K$2:$K$466,0)” 然后按键确认。
②选中单元格L2，移动光标到该单元格的右下角，当光标变为+形状时，双击填充柄即可完成公式的填充，统计出其余学生的年级名次。
Step6统计平均分和标准差
①在单元格A467和A468分别输入“平均分”和“标准差”。
②选中单元格A467，单击常用工具栏“自动求和”右侧的下箭头按钮，在弹出的列表中选择“平均值”，其默认的参数区域正确，按键确认即可统计出语文学科的平均分。
③选中单元格A468，单击常用工具栏“插入函数”按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择” STDEVP“。
④单击确定按钮弹出“函数参数“对话框，在第一个参数框中将默认的数值参数区域C2:C467，修改为C2:C466。
⑤单击确定按钮即可统计出语文学科的标准差。
⑥选中单元格区域C467:C468，光标移至单元格C468右下角，向右拖曳填充柄至J列松开鼠标即可完成其余学科课程的“平均分”和“标准差”的统计工作。
⑦选中单元格区域A467:L468，为该区域设置边框。
4.6.2 Z分数的转换与应用
Step1创建Z分数转换表
①选中单元格区域A1:L468，按组合键复制，单击工作表标签“Z分数”，单击单元格A1，按组合键粘贴
②选中单元格区域C2:J466，按键清除该区域内容。
Step2用Z分数计算公式转换原始分为标准分
①选中单元格C2，输入公式
“=(原始分!C$2-原始分!C$467)/原始分!C$468”
按键确认皆可完成第一个学生语文原始分到标准分Z分数的转换。
②选中单元格C2，光标移至单元格C2右下角，向右拖曳填充柄至J列松开鼠标即可完成第一个学生其余学科课程原始分到Z分数的转换。
Step3使用Excel函数转换原始分为标准分
①选中单元格C3，单击单击常用工具栏“插入函数”按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择” STANDARDIZE “。
②单击确定按钮弹出“函数参数“对话框，在第一个参数框”X“中输入”C3“，在第二个参数框 “Mean”中输入条件“原始分!C$467”， 在第三个参数框“Stdara_dev”中输入“原始分!C$468”。
③单击确定按钮即可完成第二个学生语文原始分到标准分Z分数的转换。
④选中单元格C3，光标移至单元格C3右下角，向右拖曳填充柄至J列松开鼠标即可完成第二个学生其余学科课程原始分到Z分数的转换。
⑤选中单元格区域C3:J3，移动光标到该单元格J3的右下角，当光标变为+形状时，双击填充柄即可完成公式的填充，从而将其余学生的各科原始分转换成Z分数。同时在对应单元格预留的“平均分”、“标准差”、“总分”和“年级名次”的计算公式也完成了相应统计任务。
⑥选中单元格区域C2:J466，选中单元格区域C3:J3，单击编辑菜单，选择，从弹出的列表中选择“选择性粘贴”命令，弹出 “选择性粘贴”对话框。在弹出的“选择性粘贴”对话框的“粘贴”选项框中单击“数值”按钮。
⑦单击确定按钮即可完成该区域公式的隐藏，从而实现在该区域里只保留公式计算结果，而与引用区域脱钩。
Step4应用Z分数对比学生学习情况
下面选择1班学生陈忠，2班学生叶乐乐和6班学生陈伟的原始分与Z分数的年级名次情况进行对比分析。
①单击工作表标签“原始分”，单击K列的任意一个单元格，如单元格K3，单击常用工具栏“降序排序”按钮，按总分由高到低排序。
②分别选中按原始分排名年级名次的第6、7、8名学生，即1班学生陈忠，2班学生叶乐乐和6班学生陈伟所在的单元格区域A8:L8，A9:L9，和A10:L0，单击常用工具栏“颜色填充”按钮，分别选择浅黄、浅绿和玫瑰红为其设置底纹颜色加以标记。
③光标切换回工作表“Z分数”，单击K列的任意一个单元格，如单元格K2，单击常用工具栏“降序排序”按钮，按总分由高到低排序。
④同样找到1班学生陈忠，2班学生叶乐乐和6班学生陈伟所在的单元格区域A11:L11，A7:L7，和A5: L 5，单击常用工具栏“颜色填充”按钮，分别选择浅黄、浅绿和玫瑰红为其设置底纹颜色加以标记。
Step5应用Z分数指导学生分别选择文、理科班学习
下面从“Z分数“工作表中选择几名学生成绩进行分析。
上表中前2名学生理科课程物理、化学的Z分数都是负值，文科课程政治、历史和地理的Z分数都是正值，而第三名学生正好相反，理科课程物理、化学的Z分数都是正值，文科课程政治、历史和地理的Z分数都是负值，且文理科成绩差距明显，因此可以建议1班学生常微，4班学生刘世欣高二选择文科班，6班学生鲁秋梅选择理科班。
4.3.6 T分数的转换与应用
Step1创建T分数转换表
①单击工作表标签“原始分”，选中单元格区域A1：C468，按组合键复制，单击工作表标签“T分数”，选中单元格A1, 按组合键粘贴。
②选中单元格A1，单击常用工具栏的“格式刷”按钮，在选择单元格区域A7:C9，使用格式刷去掉该区域的底纹颜色。
③双击单元格C1将其激活准备修改字段标题，将光标放到已有的字段标题”语文”的后面，按组合键实现单元格内换行，然后输入“原始分”。
④在单元格D1、E1、F1、G1和 H1中分别输入“语文Z分数”，“语文T分数”，“考分以下百分比”,“考生总数”和“考分以上人数”，单击常用工具栏“居中”按钮。
⑤选中单元格区域A1:H468，为该区域设置边框。
⑥在单元格区域D2：D466中，参照4.6.2节Step3中的方法，使用STANDARDIZE函数将原始分转换成Z分数。
Step2转换Z分数为T分数
①在单元格E2中输入公式：“=100*D2+500”，按键确认。这时将第一个学生的Z分数转换成平均分为500，标准差为100的T分数。
②选中单元格E2，双击单元格E2右下角的填充柄即可完成其余学生的T分数的转换。
Step3统计每位学生考分以下的百分比
每位学生可以首先通过的自己的T分数反求出对应的Z分数，再使用NORMSDIST函数统计出该考分以下的百分比，进而求出该考分以上有多少考生。采用T分数录取的高考考生就是这样用个人的高考综合分（T分数）计算出自己在全体考生中的位置的。
①选中单元格F2，单击单击常用工具栏“插入函数”按钮，弹出“插入函数“，在”或函数类别“选项框中选择”统计“，在”选择函数“选项框中选择” NORMSDIST “。
②单击确定按钮弹出“函数参数“对话框，在参数框”Z“中输入”D2“。
③单击确定按钮即可求出第一个学生的语文成绩以下所占百分比。
④选中单元格F2，双击单元格F2右下角的填充柄即可完成其余学生语文成绩以下所占百分比的统计工作。
Step4输入考生总数
选中单元格区域G2:G466，输入“465“，按组合键确认即可完成相同内容的批量输入。
Step4统计“考分“以上人数
在单元格H2输入公式：“=ROUND(G2-F2*G2,0)“，按键确认即可统计出该考分前的考生人数。
②选中单元格H2，双击单元格H2右下角的填充柄即可的统计出其余每个学生考分前的考生人数。
关键知识点讲解：
1.STDEVP函数
函数名称：STDEVP
主要功能：返回以参数形式给出的整个样本总体的标准偏差。标准偏差反映相对于平均值 (mean) 的离散程度。
使用格式：STDEVP(number1,number2,...)
参数说明：
Number1,number2,... 为对应于样本总体的 1 到 30 个参数。也可以不使用这种用逗号分隔参数的形式，而用单个数组或对数组的引用。
●文本和逻辑值（TRUE 或 FALSE）将被忽略。如果不能忽略逻辑值和文本，则请使用 STDEVPA 工作表函数。
函数说明
●函数 STDEVP 假设其参数为整个样本总体。如果数据代表样本总体中的样本，应使用函数 STDEV 来计算标准偏差。
●对于大样本容量，函数 STDEV 和 STDEVP 计算结果大致相等。
●此处标准偏差的计算使用“有偏差”和“n”方法。
●函数 STDEVP 的计算公式如下：
其中 x 为样本平均值 AVERAGE(number1,number2,…)，n 为样本大小。
应用示例：
2.STANDARDIZE函数
函数名称：STANDARDIZE
主要功能：返回以 mean 为平均值，以 standard-dev 为标准偏差的分布的正态化数值。
使用格式：STANDARDIZE(x,mean,standard_dev)
参数说明：
X 为需要进行正态化的数值。
Mean 分布的算术平均值。
Standard_dev 分布的标准偏差。
函数说明
●如果 standard_dev ≤ 0，函数 STANDARDIZE 返回错误值 #NUM!。
应用示例：
3.NORMSDIST函数
函数名称：NORMSDIST
主要功能：返回标准正态累积分布函数，该分布的平均值为 0，标准偏差为 1。可以使用该函数代替标准正态曲线面积表。
使用格式：NORMSDIST(z)
参数说明： Z 为需要计算其分布的数值。
函数说明
●如果 z 为非数值型，函数 NORMSDIST 返回错误值 #VALUE!。
●标准正态分布密度函数计算公式如下：
应用示例：
|
