Excel软件在试卷分析中的应用
Excel软件在试卷分析中的应用
陈韬
一、前言
我们常常要对教学质量进行评估，目前最普遍的方法就是考试，可以说考试是教学评估最为普遍的手段。我们通过考试成绩来衡量学生的学习效果，来衡量教师的教学质量。
可是目前，印尼大部分三语学校刚刚起步，华语课程方面还没有一个具体的标准，还有很大进步的空间。就考试而言，很多学校华语课程的命题还存在很多问题，这些存在问题的命题不能够准确的反映出学习效果和教学质量。因此，如何使试卷能够准确、全面、科学的反映出考生的实际情况就显得尤为重要。而为了达到此目的，对试卷的综合分析是重要的手段之一。
二、概念及计算方法
（一）难度（Difficulty）
1.概念
难度指考生解答试题的难易程度，它是衡量测评试题质量的一个重要指标参数，常以试题的通过率作为难度的指标。难度一般用字母P表示，P越大表示试题越简单，P越小表示试题越难。试题要有梯度，因此各试题的难度应有不同，这是命制试题时要加以特别考虑的。
一般来说，难度在0.5左右最佳，难度过高或过低，都会降低测验的信度。当然，在实际的测试过程中，难度水平多高才合适，也还要取决于测试的目的。例如目前印尼基础教育中普遍存在的情况，就是及格分数线过高（基本上都会达到70%以上），试卷明天的难度就不能过高；但也不可以整体难度过低，这样很难测试出学生的真实水平，还是应该区分试题难度梯度，在难度较低的题目基础上增加部分中等难度和较高难度的试题，使分数更有层次，以反映出考生的真实水平。
2.计算方法
（1）客观性试题难度P（也称通过率）计算公式： P=正确人数/总人数。例如某题正确考生为40人，全部考生为50人，则此题的难度P=40÷50=0.8。
（2）主观性试题难度P计算公式： P=平均分/满分值。例如某题平均得分为4分，此题的满分值为5分，则此题的难度P=4÷5=0.8。
（二）标准差（Standard Deviation）
1.概念
标准差能反映一个数据集的离散程度。平均数相同的两组组数据，标准差未必相同。其值越大，离散程度越大。例如两个班级的平均分均为80分，但并不一定代表两个班级的程度相同，可能一个班的分数集中于80分左右；而另一个班程度相差很大，成绩多为90以上或70一下。这时标准差就能反映出这一情况。
2.计算方法
标准差的计算公式是：
但因为计算较为繁琐，一般直接用Excel函数完成。过程会在下文详细叙述。
（三）区分度（Discrimination）
1.概念
区分度是指考试或试题对考生水平鉴别程度的大小。区分度D值一般介于 -1.0～+1.0之间，D为正值叫做积极区分，D为负值叫做消极区分，D为0表示无区分。D值越大区分度越好。一般而言，试题的区分度在0.4以上表明此题的区分度很好，0.3 ～ 0.39表明此题的区分度较好，0.2 ～ 0.29表明此题的区分度不太好需修改，0.19以下表明此题的区分度不好应淘汰。但这个划分也不绝对，例如在印尼基础教育中，很多测试需要学生100%或几乎100%合格，此时如果所有试题的区分度较高，是不能达到100%合格的目标的，因此，小部分高区分度的题目用以拉开学生分数之间的等级就足够了。
2.计算方法
计算区分度的方法很多。不同的方法计算结果可能会有不同。本文介绍的是较为普遍的计算区分度D方法，具体步骤如下：
（1）
将全体考生从高到低排列顺序；
（2）
按照一定的比例（一般为27%）取出高分组（H）和低分组（L）；
（3）
计算高分组和低分组的得分率P，即PH=高分组平均分/满分分值 和 PL=低分组平均分/满分分值
（4）
区分度D=PH- PL 或 D=（高分组平均分-低分组平均分）/满分分值
例如，根据表1信息可以计算区分度：
D= PH-
PL =0.939-0.718=（93.875-71.750）/100=0.221
表1：考试成绩分组表
|
序号 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
高分组成绩 |
100 |
98 |
96 |
94 |
94 |
91 |
90 |
88 |
|
序号 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
16 |
|
中间组1成绩 |
87 |
87 |
85 |
84 |
83 |
83 |
81 |
80 |
|
序号 |
17 |
18 |
19 |
20 |
21 |
22 |
|
|
|
中间组2成绩 |
80 |
79 |
79 |
78 |
77 |
76 |
|
|
|
序号 |
23 |
24 |
25 |
26 |
27 |
28 |
29 |
30 |
|
低分组成绩 |
75 |
75 |
75 |
74 |
72 |
70 |
68 |
65 |
|
满分分值 |
100 |
|||||||
|
高分组平均分 |
93.875 |
|||||||
|
高分组得分率 |
0.939 |
|||||||
|
低分组平均分 |
71.750 |
|||||||
|
低分组得分率 |
0.718 |
|||||||
|
区分度 |
0.221 |
|||||||
三、利用Excel的计算方法
从上文的介绍中可以看出，难度、标准差、区分度三个数值中，难度和区分度两值尽管计算简单，但是都要对每个题目进行手动统计，尤其是区分度需要手动分组，在逐个计算，显然造成了时间上了浪费；对于标准差，如果手动计算则面临着很大的计算量。因此，如果又有一个更加简单有效的方法来实现对以上三值的快速计算，将会大大提高试卷分析的效率。
Excel是目前最常用的电子表格软件，具有普及率高，操作简单，易于学习的优点。此外，Excel中的数组公式功能，可以比普通公式更快速的得到所需要的结果，对于区分度的计算，有非常大的帮助。因此，笔者选用Excel软件来进行试卷分析工作。
（一）制作思路
鉴于目前三语学校部分华语教师还不能熟练的应用Excel软件的函数与数组函数等，仅能使用Excel相对较为基础的功能，因此关于本应用的设计将基于一下几点：
1. “分析结果”部分被锁定，使用者只能阅读信息，但不能修改公式，最大程度保证了统计结果的安全性。
2. 为了提高便利性和使用效率，笔者将很多使用Excel计算区分度的传统方法进行了优化整合，使使用者不必再进行任何录入测试信息意外的操作，所有的分析结果在测试信息录入完成后将自动显示。
3. 不只是计算整张试卷的难度、区分度和标准差。而是可以计算每一个题目的相关三值，已达到试卷分析的最细化。
（二）数据准备
为了使用方便，分析结果简单明了，且并尽量保证公式区不被误修改。所以笔者将Excel分为两个工作表（Sheet），分别是“统计表”，用以输入相关的考试数据；和“分析结果”，用以显示统计结果，其中“统计表”工作表的试卷分析区和“分析结果”工作表将被锁定。
1. “统计表”工作表
在“统计表”中建立如图1所示的测试情况统计表。上部信息包括“题号”、“分值”“题型”。其中“题型”包括两方面信息：具体题型和主客观题，其中“主客观题”值为之后的难度计算中的一个变量。
工作表中部为测试结果输入区，直接输入每个学生的每个题目的测试结果。
下部为试卷分析区，用以显示试卷分析的结果，此部分将被锁定禁止编辑。
图1：“统计表”工作表
2. “分析结果”工作表
实际上事件分析工作在“统计表”工作表中已经完成，“分析结果”工作表只为更好的显示分析结果。所以此部分对于试卷分析结果么有任何影响。
在“分析结果”中建立如图2所示的试卷分析结果统计表。
此工作表中大部分数据之间引用自“统计表”工作表，仅有“难度评价”和“区分度评价”为此表中新加入，目的是将数据形式的“难度”和“区分度”转化为更为直观的评价。
（三）数据计算
1. “统计表”工作表
（1）“总人数”值直接使用COUNT函数实现。
（2）“总正确数”值公式为“=COUNTIF(C5:C19,">0")”，取>0的值即为正确题目。
（3）“平均得分”值直接使用AVERAGE函数实现。
（4）“27%样本人数”值公式为“=ROUND(C20*0.27,0)”，直接使用总人数（C20）*0.27，但需要使用ROUND函数四舍五入小数点后部分。
（5）“高分组得分”和“低分组得分”值是为计算区分度做准备，需要使用到数组公式功能。公式如下：
“低分组得分”公式为：“=AVERAGE(SMALL(AH5:AH19,ROW(INDIRECT("1:"&$C$23))))”
其中“AH5:AH19”存放的是本题每个学生的测试结果；“C23”中存放的是27%样本的具体人数。
上述公式输入完毕后，要按下SHIFT+CTRL+ENTER键以生成数组公式，Excel会自动在公式两侧加上大括号，即“{=AVERAGE(SMALL(AH5:AH19,ROW(INDIRECT("1:"&$C$23))))}”。同理，计算“高分组得分”的公式为“{=AVERAGE(LARGE(AH5:AH19,ROW(INDIRECT("1:"&$C$23))))}”。
（6）“难度”值因为需要区别主观题和客观题，因此公式为“=IF(C4="客观题",C21/C20,C22/C2)”。即如果本题为客观题，“难度”值为正确人数（C21）/总人数（C20）；否则“难度”值为本题平均得分（C22）/本题分值（C2）。
（7）“区分度”公式为“=(C24-C25)/C2”，即高分组平均分（C24）-低分组平均分（C25）/本题分值（C2）。
（8）“标准差”值直接使用STDEV函数实现。
2. “分析结果”工作表
此工作表中大部分数据之间引用自“统计表”工作表，仅有“难度评价”和“区分度评价”为此表中新加入，目的是将数据形式的“难度”和“区分度”转化为更为直观的评价。
（1）“难度”值公式为“=IF(H3>=0.75,"简单",IF(H3>=0.5,"中等",IF(H3<0.5,"难")))”。因为我校华文课程及格分数线（KKM）为75分，所以将难度值大于0.75定位简单题目；0.75-0.5为中等难度；小于0.5为较难题目。此标准使用者应该根据自身情况确定。
（2）“区分度”值公式为“=IF(J3>=0.4,"很好",IF(J3>=0.3,"较好",IF(J3>=0.2,"一般",IF(J3<0.2,"差"))))”。此为根据一般标准确定，大于0.4的为区分度很好；0.2-0.4之间为区分度较好；小于0.2的为区分度较差。
（四）保护工作表
最后，为了保护数据的安全，防止使用者有意或无意的更改公式，需要对工作表进行加密保护。具体步骤为：
选择需要进行加密保护的单元格（非输入区域）；之后单击右键“设置单元格格式”→“保护”，勾选“锁定”选项；然后单击“审阅”菜单→ “保护工作表”，输入密码并再次确认密码。这样只有在正确输入密码的前提下才允许编辑锁定区域。
四、试卷分析范例
图3与图4为笔者学校7年级某班学生一次华语考试试卷分析结果。可以清楚的看出每个题目的难度、区分度、平均分与标准差等信息；此外，还可以直观的了解到难度与区分度的相应标准。
如4-3题，高分组平均得分2分（满分），低分组只有0.63分，标准差为0.72，区分度0.69，都表示本题有很好的区分度，可以区分出程度较高与程度较低的学生；但本题难度为0.82，为很简单的题目，表示绝大部分学生答对了。根据此可以得出结论，本题可以区分程度最差的学生，同时难度不高，适合我校华语测试的要求。同时应该研究此类题目的特点，以后测试中可以更有针对性的命题。
又如本试卷难度为0.81，为简单；区分度为0.38，为较好。可以判断本试卷既能区分不同程度学生，同时也能满足及格率的要求（及格分数线KKM：75）。应属于比较成功的命题。
五、结论
使用Excel软件可以实现对试卷分析的快速统计，使教师从繁重的手工统计中解放出来；同时设计编辑好的Excel试卷分析应用，有可以帮助部分还不能熟练使用Excel的教师可以简单才做，享受办公自动化带来的便利。
最重要的，简单、快速、准确的试卷分析结果，可以有效的帮助教师，尤其是三语学校华语教师掌握测试、试卷的各项指标，对于改善和提高命题和测试的水平和效果，会起到很大的辅助作用。
参考文献
[1]
王汉澜, 教育测量学[M], 河南大学出版社, 1987
[2]
吴珍珠, 浅议区分度与实体质量的关系, 生物学通讯[J], 2007, 42(2):46-47
[3]
陶英, 林佩艳, 中国教育技术装备[J], 2009(18):134-135
[4]
难度和区分度——偶得Excel公式[EB/OL], http://blog.sina.com.cn/s/blog_5d858f100100bmjq.html,
2008-12-08/2016-7-10
评论
发表评论