作者：乔山办公网日期：
返回目录：excel表格制作
原始分是卷面分；按照评分标准对其作答反应直接评出来的分数，叫原始分。原始分反映了考生答对题目的个数，或作答正确的程度。但是，原始分一般不能直接反映出考生间差异状况， 不能刻画出考生相互比较后所处的地位，也不能说明考生在其他等值测试上应获得什么样的分值。 标准分的计算方法是把你的各科成绩用统计的方法计算出来的，她能看出一个人的能力的大小。 计算是：设有n个学生考试，原始分成绩分别为a1，a2，a3，……，an 当人数足够多、试题确实能反映考生的水平时，考生的成绩便会趋于“正态分布”（两头小、中间大）。 高考成绩是满足这个条件的： （1）考试人数是足够多的 （2）试题是确实反映考生的水平的 所以可用标准分方案，标准分不但可以反映考生的水平高低，还可e799bee5baa6e79fa5e98193e78988e69d83335以直接反映出该考分数在全体考生中的位置。而原始分是不可能知道分数的位置的。 标准分的计算过程中如下： 1、 求出平均分X 2、 求标准方差S 3、 标准分bi=(ai-X)/S*100+500 4、求出的结果还要根据分数在全部考生成绩中的排名进行调整，即用到所谓的百分位。 利用EXCEL可以很快算出平均分X，标准方差S，标准分。 如：Z=(X-X')/S 式中，X为原始分数，X'为原始分的平均数，S为原始分的标准差。 Z分数是以一批分数的平均数作为参照点，以标准差作为单位表示距离的。它由正负号和绝对数值两部分组成，正负号说明原始分是大于还是小于平均数，绝对数值说明原始分距离平均分数的远近程度。一批分数全部转换成Z分数后，它们的整个分布形态并没有发生改变。Z分数准确地刻划了一个分数在一批分数中的相对位置。 T=500+100Z 公式中取500为平均分，100为标准差
求采纳
如果有60名学生参加考试，在Excel表格中录入了学生的语文和物理成绩，成绩记载从第3行到第62行。使用公式“标准分=（原始分-平均分）/标准差”计算标准分。

“B”列是语文学科的原始分数，要把“B”列的原始成绩转化e799bee5baa6e79fa5e98193e59b9ee7ad94334成标准分，放在“D”列，在“D3”单元格中输入“＝（B3-AVERAGE（B$3:B$62））/ STDEV(B$3:B$62）”，回车即可计算出该学生该学科的标准分2.29


同样的道理，将C3中的物理原始分转化成标准分放在E3。

下面的部分，采用Excel的自动填充功能，同时选中D3和E3，在E3的右下角出现“+”用鼠标点中，往下拖拉，就自动把所有的同学的该学科的标准分自动计算出来了，见下图。

计算B列的平均分用公式“AVERAGE（B3: B62）”，计算B列的标准差用公式“STDEV(B3: B62），从B3到B62每一个原始分对应的标准分的计算都用到同样的平均分和标准差，为了方便计算用拖拉的方法自动填充，将B3：B62使用绝对引用，将列“B”两边加“$”表示绝对引用。函数“＝（B3-AVERAGE（$B$3: $B$62））/ STDEV($B$3:$B$62）”就是“（B3原始分数—B列平均分）÷B列标准差”，结果就是B3格数据对应的标准分。
用鼠标推拉选中D3右下角的“+”往下拖拉相当于在D列的其他单元格填充上公式并计算。例如，选中D61，将出现函数fx=（B61-AVERAGE（$B$3: $B$62））/ STDEV($B$3:$B$62），自动计算出来的便是B61中原始分对应的标准分

如此计算出来的标准分是Z分数，是在0上下波动的小数，有正数也有负数，不方便比较，将算出来的标准Z分转化成T分数，用公式T=500+100Z。变成中位数为500的T分数，操作如下：
在F单元格输入公式“=500+100*D3”，回车，即把D3单元格中的Z分数转化成T分数。同样的道理，用鼠标向下拖拉便计算出其他学生的T分。

如果要计算物理学科T分，在语文的基础上进行。将鼠标点在F3单元格，按住鼠标左键向右推拉到G3单元格，G3单元格的公式自动生成。用鼠标向下拖拉计算其他学生的标准Z分数。
标准分的定义与转换方法:
考生在接受测验后，按照评分标准对其作答反应直接评出来的分数，叫原始分。原始分反映 了考生答对题目的个数，或作答正确的程度。但是，原始分一般不能直接反映出考生间差异 状况，不能刻划出考生相互比较后所处的地位，也不能说明考生在其他等值测试上应获得什 么样的分值。
导出分是在原始分的基础上，按一定的规则推导出来的，其目的就是进一步解决原始分所没 有解决的问题，或者说，就是为了更好、更科学地解释分数的e69da5e887aae799bee5baa6e79fa5e98193366含义，进行分数的组合，实现 分数的等值化。这种把原始分数转化为导出分的过程，称作分数转换。导出分的种类有很多 ，最常用的是百分等级和标准分数。
标准分是一种由原始分推导出来的相对地位量数，它是用来说明原始分在所属的那批分数中 的相对位置的。求法如下：
Z=X-X-/S
式中，X为原始分数，X-为原始分的平均数，S为原始分的标准差。
Z分数是以一批分数的平均数作为参照点，以标准差作为单位表示距离的。它由正负号和绝 对数值两部分组成，正负号说明原始分是大于还是小于平均数，绝对数值说明原始分距离平 均分数的远近程度。一批分数全部转换成Z分数后，它们的整个分布形态并没有发生改变。Z 分数准确地刻划了一个分数在一批分数中的相对位置，但是，由于Z分数有负值，常带有小 数，不易被人理解和应用。因此人们在Z分数的基础上进一步转换，从而发展起了一系列其 他形式的标准分。转换通式为：
Z′=αZ+β
式中，Z′为其他形式的标准分，α是转换方程的斜率，β是转换方程的截距。
我国普通高校全国招生统一考试所使用的标准分，就是用刚才介绍的方法进行转换的。
即：
T=500+100Z
公式中取500为平均分，100为标准差
标准分制度的内容
建立标准分制度一般应由以下环节构成：①各省仍按以往的方法组织评分，然后合成每个考 生的各科原始分，并且统计各科的每个分数上的考生人数。②国家教委考试中心在部分省级 考试机构的配合下进行当年与往年的分数等值。
国家教委考试中心确定原始分数与标准分数的转换关系， 各省考试机构根据转换关系，得出省级常模量表分数。(各省在转换时，可以根据分数分布 具体情况有些微调)③各省考试机构公布省级常模量表分数。(原始分不公布)
高考标准分数制度由常模量表分数(包括全国常模和省常模)、等值量表分数组成。具体讲： 常模量表分数反映一次考试考生成绩在考生总体中的位置，分数值与这一位置有关。由于高 考是全国统一考试，分省进行录取，所以标准分数转换有两种情况：一种是把全国考生做为 一个总体进行分数转换，另一种是把每个省的考生做为一个总体进行分数转换，这样建立的 常模量表分数能够准确地刻划考生成绩在总体中的位置，使不同学科的成绩能够进行比较， 但还不能以此进行逐年的比较。为了弥补这种不足，就需要等值量表分数来完善。
标准分数的理解和使用
常模转换分数是根据高考的目的，按照正态分布的原理，把原始分数转换成标准分数。这种 标准分数的平均分为500，标准差为100，每一常模转换分数都与该分数以下的考生数与考生 总数的比例有确定的对应关系。
如某考生物理高考成绩为690分，我们就可以查高考标准分与百分等级对照表，得出该考生 以下的考生占考生总数的比例。查表690分对应的比例为097127998(即97127998%)，若该生为去年某省理工类考生，去年理工类考生数为9724人，则他超过9445人，比他分数高的考生约有279人(算法：9724×(1-097127998))，这样我们很容易看出考生在全体考生中的位置，较精确地刻划了考生在团体中的水平。另外，再次强调考生的各科成绩和综合分都是用常模量表分数来表示的，各科成绩相加不等于综合分。综合分是根据各科标准分进行合成，然后按常模量表分数转换方法得到的。请大家不要与原始总分混淆，也不要误认为综合分是各科标准分的平均分。
在使用原始分的省份，考生得知自己的各科分数和总分后，就要用各类学校录取分数线来衡 量自己的成绩是上何类分数线，进而估计自己大概能上哪一类学校。但是在估计中，由于不 能知道自己在全体考生中的位置，所以往往盲目性很大。
使用标准分数以后，考生很容易得知自己的总成绩和各科成绩所处的位置，然后根据各类学 校录取分数线在常模分数量表的位置，进而可以比较准确地估计和预测自己能上哪一类学校 ，把握有多大。
标准分转换以后，考试机构在高考后送到考生手上的和在档案中存放的是这样的成绩单：
考号 姓名 语文 数学 外语 物理 理综 综合分
10050516 张华 592 598 642 581 619 636
百分等级 821 837 922 791 883 913 成绩通知单的含义是：
张华的综合分为636，百分等级为913，则可知张华在全省理工类考生中的位置，即有91. 3%的考生成绩比张华成绩低。学科成绩的含义与之相同。因为各学科成绩具有同样的参照点 ，所以我们还可以对各学科之间进行比较。这样我们不难看出，张华的外语较好，物理较差 。又如，某理工类考生综合分为695分，对应的百分等级为974，当年理工类考生总数为110285人，在该生以上大约有2822人，而当年理工类本科录取分数线为633人，对应的百分等级为908，则上线人数约为10120人，重点大学录取分数线为658分，除掉多投档的人数实际能录5240人(即6228÷12=5240)。从以上情况分析，该生估计可能被重点大学录取。但是，我们也应知道录取新生既要看综合分的高低，还要考查相关学科的成绩，另外重要的一点要看考生所报志愿学校的生源情况，考生在所报学校考生中的位置以及思想表现、身体状况、高中毕业会考成绩等情况。
(6)原始分转换成标准分后的分数顺序知：各科原始分转换为标准分，每科成绩的排列顺序 不发生变化，即原始分高的标准分也高，原始分低的标准分也低，原始分相同的转换后标准 分也相同。但综合分的前后顺序与原始总分的前后顺序相比有一些变化。从总体上说原始总 分与综合分一致性程度很高，虽然变动的范围不大，但由于高校是“按总分划线录取的”， 人们自然会问：哪些考生不影响录取，哪些考生影响录取?综合分这种前后次序的变动是否合理?
①高分段和低分段的考生。由于次序变化幅度很小，所以不管按哪种办法计算总分，不影响 其是否录取。也就是这说，优秀考生不管按什么办法算总分都会录取，差生不管按什么办法 算总分，都不会被录取。
②对总分处于最低录取线边缘的考生，由于原始分数算总分与标准分数算总分的方法不同， 两种总分排序前后不同的幅度虽然不大，也会有少数考生出现录取受到影响的情况，有的考 生原始分总分未上线，但转换为标准分后上线了；有的考生原始分总分上线了，但转换为标 准分后却没有上线。但总的来说，这种录取受影响的情况所占考生的比例很小。
在原始分总分合成中，各科在总分中的权重是一种自然形成的结果。各科在总分中的权重取 决于各科分数分布的标准差的大小，标准差大(即考生分数分布比较分散，分数距离拉得比较大)，在总分中的权重就大，反之标准差小，在总分中的权重就小。也就是说在原始分中标准差大的，在总分累计中作用大，而标准差小的在总分中起作用小，这就使各科在录取中应有的权重就不能体现出来。显然，这种原始分累加计算总分是不合理的。使用标准分后，各科原始分转换为平均分为500分，标准差为100分的标准分，各科分数就有共同的参照点，也有相同的单位，统一到同一“量尺”上的分数。这样的各科标准分合成转换为综合分，保证了各科在总分中的权重，因此是合理的，上述少数学生录取与否因转化为标准综合分后受到影响是正常的、合理的、科学的，这正是标准化制度克服原始分制度的缺点而显示其优越性的结果。
有关的函数
1． RANK函数
rank函数返回一个数字在数字列表中的排位，rank函数的使用格式是“rank(number,ref,order)”。
其中：number是需要找到排位的数字；
ref是数字列表数组或对数字列表的引用；
order为一数字，指明排位的方式。
如果order为零或省略,excel对数字的排位是基于ref的降序排列。
如果order不为零,excel对数字的排位是基于ref的升序排列。
利用rank函数找出每位考生的成e799bee5baa6e79fa5e98193e58685e5aeb9333绩，进行升序排列的名次n，则在该考生成绩之下的学生便有（n-1）位。例：一位考生的成绩升序排列是第10名，那在这位考生前面有9位考生。这样，就能利用rank函数算出每个分数以下考生的人数。
2． normsinv函数
该函数返回标准正态累积分布函数的反函数。
该分布的平均值为零，标准偏差为 1。
如果probability为非数值型，函数normsinv返回错误值#value!。
如果 probability小于零或大于1，函数normsinv返回错误值#num!
3． 应用举例假设该年级学生共有500人，在第2行至501行中，在h2单元格单击，输入公式“=100*normsinv(((rank(d2,d$2:d$501,1)-1)/(counta(d$2:d$501))))+500”后回车，如果输入无误，可看到该单元格按照语文原始分转化出来的语文标准分。由于函数normsinv(0)返回的值是“#num!”（在以上公式中，就是最低分的考生），而标准分最低分是100分，因此，进行标准分转换时，在显示 “#num!”的单元格上，必须手工把它改为100。
设置h2单元格的“单元格格式”为“数值”、“小数点位数”是“0”，让显示出来的标准分都是整数。用鼠标选择h2单元格，拖动填充句柄到k2单元格，这样便完成了第一位考生各科标准分的转换工作。用鼠标选择h2:k2单元格，拖动填充句柄到最后一位考生所在的行，完成所有考生单科成绩转换的工作。
1列（标准分之和）是每位考生各科成绩转换成标准分后求和，这是为计算标准分做准备的，完成1列设置后，在m2单元格中输入公式：“=100*normsinv(((rank(l2,l$2:l$501,1)-1)/(counta(l$2:l$501))))+500”后回车，再选择这一单元格，拖动填充句柄到最后一行的学生即可。
【 必须注意的问题】 如果要把学生分开到各个班级中或进行其他操作，则必须再进行一次粘贴数值的处理才行。具体的做法是选择图中标准分所在的列（h列 ～m列），点击“复制”，在n1单元格上单击，选择“编辑→选择性粘贴”，在弹出的对话框中“粘贴”选项处，选择“数值”，单击[确定]按钮后，再把原来的g列～m列删除掉。