摘要 教学测试是三个主要教学环节之一．通过测试成绩的统计和分析，可以对教学效果、学生学习能力和学习水平提供客观、准确的评估数据，也为进一步调整教学活动提供理论依据．
本文试图通过介绍成绩统计分析中的基本理论和基本方法，使读者初步掌握Excel内置函数、宏以及VBA的使用技巧，掌握各类统计图表的实现方法，更好的为教学管理和教学活动服务．
一．几个基本概念
1．原始分
原始分亦称卷面分，是一种未经加工整理的分数．
2．平均分
平均分是处于一群分数中心（或重心）位置的分数．
设n个分数x1，x2，…，xn的平均分为x，则其计算公式为： ．如果用相加号∑表示，则 ．
平均分既是这群分数的代表数（它与各个分数的离差的平方和最小），也反映了该次考试的平均难度．
3．标准差
标准差是一群分数离散状态的指标．
设n个分数x1，x2，…，xn的标准差为s，则其计算公式为： ．也可以写成 ．
标准差是这群分数与其平均分的离差的平方平均值．若这群分数较为分散，则标准差较大；若这群分数较为集中，则标准差较小．
4．标准分
标准分是以平均分为中心、以标准差为量尺，对原始分进行加工处理后（称为标准化）的分数．
标准分通常分为“Z分数”和“T分数”两种．
（i）Z分数：设xi为考生i某科的原始分，x、s分别为全体考生该科的平均分和标准差．则考生该科的Z分数为： ．显然，Z分数的平均分为0，标准差为1，故Z分数成标准正态分布．因此，不同科目的原始分经标准化后就具有可加性和比较性．
（ii）T分数：由于Z分数大都分布在－4与＋4之间，分距太小，过份密集，不符合人们的常规认识，所以需要对Z分数进行转换，转换后分数称为T分数，转换公式是一个线性变换： ．即将zi扩大k倍后，再将中心位置移到c点，因此，T分数的平均分为c，标准差为k．在2007年前，广东省高考使用标准分，各科中统一使用的转换系数是 ， ．即 ，其平均分为500，标准差为100，最高分为900分，最低分为100分．
人们习惯上将900分制的T分数称为标准分．标准分在总体中所处的百分位置非常明确和稳定（见表1.1）．如考生标准分为600分，则其约处于是84.13%的位置，即比84.13%的考生要好而比15.87%的考生要差．
表1.1 标准分与百分位的对照表
标准分
400
450
500
550
600
650
700
750
800
850
900
百分位
15.87
30.85
50.0
69.15
84.13
93.23
97.75
99.38
99.87
99.98
99.997
5．难度
难度是指试题或考试的难易程度，是考生对试题或考试是否适应的指标．
设试题的平均分和满分值分别为x和w，难度为p，则其计算公式为： ．
显然0＜p＜1，p越小难度越大．试题和整卷的难度要求视考试的性质不同而有所区别，高考试题难度通常在0.3～0.7，整卷难度通常在0.5～0.6．难度也就是俗称的通过率或得分率．
6．区分度
区分度是指试题对不同知识和能力水平考生的鉴别程度．
高分考生比低分考生得多分的表示试题区分性能好，得少分的表示试题区分性能差，高分考生与低分考生得分都差不多，则表示试题无区分能力或零区分．
试题的区分度有多种计算方法，较简单的是“二列相关法”．即将n个分数x1，x2，…，xn分成高分组和低分组，设高分组和低分组的频数分别为p和q，平均分分别为xp、xq，以s表示全体的标准差，d表示区分度，则其计算公式为： ．
在高考中一般要求区分度在0.3以上，表示高分考生比低分考生能多得30%的分数．区分度在0.2以下的的试题一般应予淘汰．
7．信度
信度是反映考试结果稳定性和可靠性的指标．
信度高，表示考试所得的分数与考生的真实水平较为接近，信度低的考试无法正确评价考生的知识水平与智能素质，是一门不合格的考试．信度的最大值为1．在高考中一般要求第Ⅰ卷的信度在0.9以上，第Ⅱ卷的信度在0.7以上，全卷的信度在0.8以上．
设试题数为m，各题得分的标准差为si ，整卷得分的标准差为s，用r表示信度，则其计算公式为：
．
试题中区分度低的题目偏多，各题满分值差异太大，各题目之间异质性（即考查不同的特质）太强等，都会造成信度值低下．
二．统计数据的实现方法
某班级期中考试结束，各科考试成绩如图2.1所示（以十名同学的语、数、英分数为例）．
图2.1 期中成绩表
1．计算总分
（i）算术加法：
步骤1 在单元格F2中输入“=C2+D2+E2”，如图2．2所示，并按 ．这时，单元格F2中显示李小平的语数英三科总分255．
步骤2 选择单元格F2，并将鼠标移至F2的右下角，当鼠标显示为“╋”时，双击左键，或按住左键，并拖至F11，则完成了李小平至赵明敏的三科总分计算，如图2.3所示．
图2.2 输入算术运算
图2.3 相同运算公式的复制
（ii）调用内置函数：
步骤1 选择单元格F2，点击常用工具条中的自动求和符号“∑”，如图2.4所示．
图2.4自动求和符号
这时，在单元格F2中出现内置函数“=SUM(C2:E2)”，如图2.5所示．按 ，这时，单元格F2中显示李小平的语数英三科总分255．
也可以在单元格F2中直接输入公式“=SUM(C2:E2)”，或从工具栏中选择“插入/fx函数”，调用内置函数SUM．
步骤2 同（i）的步骤2．
图2.5 调用内置函数
2．计算平均分
步骤1 先选择单元格C12，再在工具栏选择“插入/fx函数”，如图2.6所示．
图2．6 选择“插入/fx函数”
步骤2 出现对话框“粘贴函数”，选择“统计/AVERAGE”，调用“计算参数的算术平均数”的内置函数AVERAGE，该函数中的参数可以是数字，或者是涉及数字的名称、数组或引用．如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值，但是，如果单元格包含零值则计算在内．如图2.7所示，按“确定”．
图2.7 调用内置函数AVERAGE
步骤3 出现如图2．8所示的对话框，对默认的参数范围作适当调整，然后按“确定”．即完成对班级语文学科平均分的计算．
图2.8 调整参数范围
步骤4 选择单元格C12，并将鼠标移至C12的右下角，当鼠标显示为“╋”时，按住左键，并拖至F12，则完成了班级数学、英语和总分的平均分计算，如图2.9所示．
说明：当样本数明确且较小时，也可通过在单元格C12中直接输入算术式或利用求和符号“∑”调用SUB函数来求平均分．即在单元格C12中输入：
“=（C2+C3+C4+C5+C6+C7+C8+C9+C10+C11）/10”，或“=SUB（C2:C11）/10”．
图2.9 调用相同内置函数的复制
3．排序
步骤1 先选择单元格G2，再在工具栏选择“插入/fx函数”，出现对话框“粘贴函数”，选择“统计/RANK”，调用“返回一个数字在数字列表中的排位”的内置函数RANK，如图2.10所示．
图2.10 调用内置函数RANK
步骤2 步骤1“确定”后，出现如图2.11所示的对话框．
在Number中输入F2，在Ref中输入F2:F11，Order省略，单击“确定”后，即完成指定单元格F2在一列数F2～F11中的排位．
图2.11 排序函数的参数设定
步骤3 双击单元格G2，显示内置函数RANK，如图2.12所示．将数字列表数组或对数字列表的引用F2:F11绑定，即改“=RANK（F2，F2:F11）”为“=RANK（F2，F$2:F$11）”，确定．
图2.12 排序函数的引用绑定
步骤4 选择单元格G2，并将鼠标移至G2的右下角，当鼠标显示为“╋”时，双击左键，或按住左键，并拖至G11，则完成了对总分的排序，如图2.13所示．
说明：数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）．
排序函数RANK的语法结构形成为：RANK(number,ref,order) ．其中，
Number 为需要找到排位的数字；
Ref 为数字列表数组或对数字列表的引用．Ref 中的非数值型参数将被忽略；
Order 为一数字，指明排位的方式．如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于ref为按照降序排列的列表；如果order不为零，Microsoft Excel 对数字的排位是基于ref为按照升序排列的列表．
排序函数RANK对重复数的排位相同．但重复数的存在将影响后续数值的排位．例如，在图2.13总分排名中，第7名有两个，则紧接其后的排名为第9名，而没有第8名．
如果工作表的设置简单，则可以通过点击常用工具条中的“升序排序 ”或“降序排序 ”来进行总分排名，也可以通过在工具栏选择“数据/排序”来实现．
图2.13 排序函数的复制
下面以某年级500名同学的语文、数学、英语成绩为例，分别说明标准差、单科标准分、总分标准分以及难度、区分度和信度的实现方法．如图2.14所示．
4．标准差
步骤1 先选择单元格C503，再在工具栏选择“插入/fx函数”，出现对话框“粘贴函数”，选择“统计/STDEVP”，调用“返回以参数形式给出的整个样本总体的标准偏差”的内置函数STDEVP ，如图2.15所示．
步骤2 步骤1“确定”后，出现如图2.16所示的对话框．将Number1中默认的参数范围C2:C502调整为C2:C501，单击“确定”后，即完成对全年级500名同学语文学科标准差的计算．
步骤3 选择单元格C503，并将鼠标移至C503的右下角，当鼠标显示为“╋”时，按住左键，并拖至E503，则完成了对全年级500名同学数学和英语学科标准差的计算，如图2.17所示．
说明：各科标准差反映相对于各科平均分的离散程度．
标准差函数的语法结构形成为：STDEVP(number1,number2,...) ．其中，
Number1,number2,... 为对应于样本总体的 1 到 30 个参数．也可以不使用这种用逗号分隔参数的形式，而用单个数组或对数组的引用．
文本和逻辑值（TRUE或FALSE）将被忽略．如果不能忽略逻辑值和文本，则请使用STDEVPA工作表函数．
图2.14 500名同学的语数英原始成绩数据表
图2.15 调用内置函数STDEVP
图2.16 标准差函数的参数设定
图2.17 标准差函数的复制
5．单科标准分
步骤1 选择单元格F2，并在单元格中输入“=100*(C2-C502)/C503+500”，如图2.18所示．
步骤2 步骤1“确定”后，将鼠标移至F2的右下角，当鼠标显示为“╋”时，按住左键，并拖至H2，则完成了李小平同学的语文、数学和英语的单科标准分计算．如图2.19所示．
步骤3 绑定平均分和标准差的取值．即
将单元格F2中公式“=100*(C2-C502)/C503+500)”改为“=100*(C2-C$502)/C$503+500)”；
将单元格G2中公式“=100*(D2-D502)/D503+500)”改为“=100*(D2-D$502)/D$503+500)”；
将单元格H2中公式“=100*(E2-E502)/E503+500)”改为“=100*(E2-E$502)/E$503+500)”．
图2.18 在F2中输入计算标准分的公式
图2.19 李小平同学的单科标准分
步骤4 先选择单元格F2，按住键 ，再选择单元格H2，然后点击常用工具条中复制符号，如图2.20所示．
图2.20
步骤5 先选择单元格F3，按住键 ，再选择单元格H501，然后点击常用工具条中粘贴符号，如图2.21所示．
图2．21
就完成了500名同学的语文、数学、英语单科标准分的计算，如图2.22所示．
图2.22 500名同学的语文、数学、英语单科标准分
说明1：通过计算可以验证，500名同学的语文、数学、英语单科标准分的平均分和标准差均为500和100．
说明2：通过计算还可以发现，500名同学的单科标准分中最高分和最低分分别是：语文最高分689.49，最低分－90.88；数学最高分668.45，最低分143.09；英语最高分649.73，最低分251.15．并不是理想中的900分和100分，这在样本容量较小时是正常的．
为了在样本容量较小时，也能实现最高分900，最低分100的“理想结果”，我们可以在“Z分数”向“T分数” 线性转换化时，对转换公式 中的系数作出适当调整．例如：取c＝500，k满足kM＋500＝900，其中M是Z分数中的最大值，同时将Z分数中小于等于－4分的转化为100．即转换公式为
．
这时，平均分近似的等于500，而标准差 ．又例如：取k，c的值，使之满足 ，其中M和m分别是Z分数中的最大值和最小值．这时，平均分为 ，标准差为 ．上述两例处理方法，由于其结果的平均分或标准差与M、m有关，所以不具有可加性，但仍可作为单科标准分．
说明3：将各科标准分按四舍五入近似为整数的方法有两种，一是利用内置函数ROUND(number,num_digits)（改变了单元格的数值）；二是利用设置单元格格式的方法（只改变单元格数值的表现形式，不改变单元格的数值）．
6．总分标准分
在各单科的“Z分数”向“T分数” 线性转换化时，如果转换公式中的系数、与科目无关，为同一常数，则各单科的标准分具有可加性．所以总分标准分就是各单科标准分的平均分．其实现方法参见“2．计算平均分”，结果如图2.23所示．
图2.23 500名同学的语文、数学、英语三科标准总分
通过计算可以发现，由于样本容量较小，500名同学的标准总分的平均分为500，标准差为88.8417，最高分649.239，最低分201.629，不符合“理想结果”．可以根据要求参照“5．单科标准分”中的方法和“说明2”对这组数据再“标准化”．
难度、区分度和信度是衡量试题、试卷质量的三个基本指标．
7．难度
步骤1 选择单元格C504，并在单元格中输入“= C502/100”（语文单科的满分值100）．
步骤2 步骤1“确定”后，将鼠标移至C504的右下角，当鼠标显示为“╋”时，按住左键，并拖至E504，则完成了语文、数学和英语单科试卷的难度系数计算．如图2.24所示．
图2．24 语文、数学、英语三科试卷难度系数
8．区分度
步骤1 选择单元格C505，并在单元格中输入公式
“=(SUMIF(C2:C501,">72.846")/COUNTIF(C2:C501,">72.846")-SUMIF(C2:C501,"<72.846")/COUNTIF(C2:C501,"<72.846"))/C503*SQRT(COUNTIF(C2:C501,">72.846")*COUNTIF(C2:C501,">72.846"))/COUNT(C2:C501)”．
步骤2 将单元格C505复制粘贴到D505和E505，并将其中语文的平均分72.846分别替换为数学的平均分70.028和英语的平均分68.196．得语文、数学和英语单科试卷的区分度．如图2.25所示．
9．信度
由于信度 ，与试卷的试题数m、各题得分的标准差si有关，所以它的计算需要对各份试卷进行全面的统计，这里，缺少这些数据，故略去其实现方法．
图2.25 语文、数学、英语三科试卷区分度
