用Excel2003转换标准分的方法

 

用标准分来衡量学生的成绩，是现行考试中一种常见的方法。现在把原始分转化为标准分，多数是用专用的软件完成计算过程的。用Excel2002把原始分转化为标准分，具体操作说明如下。

1. 标准分的计算原理

标准分的计算原理是：对于每一个分数Xi，先算出这个分数以下的考生数占考生总数的百分比Pi，再查这个Pi对应于正态分布表中最接近的数值Zi，最后通过公式：标准分=Zi*100+500算得的。

标准分总分的计算方法是：把每位考生各科的标准分相加求和，再算出这个和以下学生成绩的学生数，除以总人数得到Pi值，再按照以上求各科标准分的方法，完成标准总分的转换过程。

2. 有关的函数

（1）Rank函数

RANK函数返回一个数字在数字列表中的排位。

RANK函数的使用格式是：RANK(number,ref,order)

其中：Number 是需要找到排位的数字。Ref是数字列表数组或对数字列表的引用。

Order为一数字，指明排位的方式。

①如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 的降序排列。

②如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 的升序排列。

在这里，我们利用Rank函数找出每位考生的成绩进行升序排列的名次n，则在该考生成绩之下的学生便有(n-1)位，例如一位考生的成绩升序排列是第10名，显然，在这位考前面有9位考生，即在这位考生成绩之下的学生共有9名。这样，我们便能利用Rank函数算出每个分数以下考生的人数。

（2）Counta函数

Counta函数返回参数列表中非空值的单元格个数。利用函数 COUNTA 可以计算单元格区域或数组中包含数据的单元格个数。

在此，我们利用Counta函数，算出在指定的范围内，参加考试的学生人数。结合以上我们用Rank函数算出每个成绩以下的学生数，我们便能算出Pi的值。

（3）NORMSINV 函数

该函数返回标准正态累积分布函数的反函数。该分布的平均值为 0，标准偏差为 1。

①语法NORMSINV (probability)，其中：Probability是正态分布的概率值。

②如果 probability 为非数值型，函数 NORMSINV 返回错误值 #VALUE!。

③如果 probability < 0 或 probability > 1，函数 NORMINV 返回错误值 #NUM!。

我们利用NORMSINV函数，为我们找出正态分布表中与Pi相对应的数值Zi，其中的关系是：Zi=NORMSINV（Pi）。

3.应用举例

（图1）

如图1，假设该年级学生共有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。

 以上标准分转换公式的含义是：RANK(D2,D$2:D$501,1)表示D2单元格学生在该列中按升序排列的名次，RANK(D2,D$2:D$501,1)-1就是比D2单元格上学生语文成绩少的学生人数；COUNTA(D$2:D$501)计算从D2单元格到D501单元格有成绩的学生人数，因此，Pi可用如下公式计算：

Pi=(RANK(D2,D$2:D$501,1)-1)/(COUNTA(D$2:D$501)。

再结合公式Zi=NORMSINV（Pi）和标准分计算公式，综合起来，就是以上我们输入的标准分转换的公式。

设置H2单元格的“单元格格式”为“数值”、“小数点位数”是0，让显示出来的标准分都是整数。用鼠标选择H2单元格，拖动填充句柄到K2单元格，这样便完成了第一位考生各科标准分的转换工作。用鼠标选择H2～K2单元格，拖动填充句柄到最后一位考生所在的行，我们便完成了所有考生单科成绩转换的工作。

在图1中，L列（“标准分之和”）是每位考生各科成绩转换成标准分后求和，这是为计算标准分做准备的，完成L列设置后，在M1单元格中输入公式：

=100*NORMSINV(((RANK(L2,L$2:L$501,1)-1)/(COUNTA(L$2:L$501))))+500后回车，再选择这一单元格，拖动填充句柄到最后一行的学生即可。