Excel在中小学标准化测试中教育信息处理的应用

Excel在中小学标准化测试中教育信息处理的应用

Excel在中小学标准化测试中教育信息处理的应用

马如宇辽宁省教育技术装备中心辽宁沈阳110032

摘要：本文通过对Excel在与测试相关的教育信息处理方面的应用探究，重点介绍了如何使用其进行难度、信度、区分度分析、回归分析等处理方法，以期提高教师教育信息处理能力，改善教学策略，提高教学水平。

关键词：Excel 中小学教育信息处理标准化测试

目前教育信息处理方法在中小学教师中的普及和应用情况不甚乐观:大多数的教师对诸如回归分析、聚类分析等较复杂的教育信息处理方法知之甚少，没听说过如难度分析、信度分析和区分度分析等常用教育信息处理方法，而只是在每次考试后进行简单的名次排序和求出平均分，并且这项工作还多是由学科课代表手工完成，费时费力，很难对表象数据潜藏的实质性内容进行有效地挖掘和实证分析，真正做到透过现象看本质，从而形成了制约教师教学能力提高，教学手段改善，教学信息获取和教学策略制定的瓶颈。因此，引导广大中小学教师初步了解和掌握科学的教育信息处理方法并应用相关工具软件进行信息分析、处理成为当务之急，具有很强的实践指导意义。

一、与测试相关的教育信息处理方法教育信息处理是一种基于信息科学的思想、方法对教学过程中的各种信息进行分析处理的一种新学科分支。在教育信息处理的发展过程中，有许多方法、概念是基于信息科学、系统分析、控制理论和数理科学所发展起来的。由于教育过程是一种信息过程，对它的分析便是要针对其过程中产生的各种数据进行处理后，得到有价值的信息反过来评价和指导以后的教育过程。所以，教育信息处理是教育的重要环节，它对指导教学、准确评估学生学习能力进而提高教学质量具有重要的意义。

测试成绩是有关测试的教育信息中的一种。与其它教育信息比较测试得分具有量度较高、清晰等特点，易于比较分析。因此根据测试信息的特点和处理的目的，可选择合适的数学方法，实现教育信息的有效处理。主要的方法有：

1.信息理论法。教学过程是一种信息过程，以信息理论来研究教学过程是教育信息处理的重要内容。以信息理论作为指导，通过信息量的计算，可实现教学过程的有效研究。

2.系统分析法。在很多情况下，教学系统是一种定性系统，我们可利用系统科学中有关定性系统的分析方法，以图作为工具，对这种系统的结构进行分析。

3.模糊数学法。教育信息往往是一种模糊信息，利用模糊教学的方法可对这种信息进行处理。

4.数理统计法。教学实践中，更多地使用数理统计的方法对教育信息进行处理。最为常用的就是多元分析法(教育系统是一个多变量系统，为了研究和评价这种系统，使用单变量法，即一个一个变量单独地进行分析、处理存在着很多问题，需要我们对这多个变量综合地进行分析、处理)。例如，当一次测试结束后，要计算得分的平均值，偏差，要计算各门课程的相关系数，要做出得分的分布。

二、Excel在在标准化测试分析中的应用举例

1.学生成绩分析应用举例

最常见的成绩分析是求出合格率、优秀率、最高分、最低分、平均分等。下面通过举例说明如何用Excel进行学生的成绩分析。

首先选定五个单元格中输入文字“平均分”、“优秀率”“合格率”“最高分”和“最低分”。在对应单元格中，输入公式“＝AVERAGE(1:2)”（数字1，2代表单元格编号，确定指定范围，以下同此解释），按回车键得出平均分。用复制公式的方法横向拖动鼠标得出其他学科平均分。在对应单元格中应用公式＝COUNTIF(1:2,“>=85”)/COUNTA(1:2)计算出优秀率（此处

以85分以上的成绩为优秀）。用复制公式的方法计算其他学科的优秀率。单击工具栏中的按钮，将小数转换为百分数。同样，以60分以上的成绩为合格，可以计算出合格率。在对应单元格中输入公式“=MAX(1:2)”，回车后得出各学科的最高分。用复制公式的方法得出其他学科的最高分。在对应单元格中输入公式“=MIN(1:2)”，用同样的方法得出各学科的最低分。这样就完成了对学生成绩的分析。

分析学生的成绩还有一个重要的指标，那就是标准差。标准差表示一组数据的离散程度。在教育科研中，标准差大好还是小好，要根据具体情况来定。但对于学生的成绩来说，如果标准差很大，表示学生的成绩过于两极分化，这说明教师的教学是有问题的。另外，这时学生的平均分数也没什么实际意义了。下面我们通过实例来讨论如何用Excel计算标准差。

在对应单元格中输入“标准差S”。选中相应单元格区域，单击工具栏中的按钮，合并居中这三个单元格。确定“标准差S”为活动单元格。单击工具栏中的按钮，弹出“粘贴函数”对话框。在“函数分类”下选择“统计”，在“函数名”下选择“STDEVPA”，单击“确定”后，窗口弹出“引用区域”对话框。在“Value1”中输入指定单元格区域，单击“确定”后显示出成绩的标准差。用复制公式的方法，拖动鼠标得出其他学科成绩的标准差。如图1所示：

图1各学科成绩的标准差

2.学生测试试卷分析

历经每一次像中高考这样的大型考试之后，教育管理部门都会组织专家对考试的试卷进行分析。其目的在于希望从试卷中发现有价值的信息，以指导今后的教学工作和考试改革工作。试卷分析是根据考生的情况对每一道题逐一进行分析。分析的指标主要有标准差、难度、区分度和信度。下面就介绍如何用Excel来对试卷进行分析。

目前考试的题型中主要有主观题和客观题两种。从测量的角度来讲，由于题型的不同，计算这些指标的方法也不同。所以这里为方便起见，不对某一整套试卷的所有试题进行分析，而是对客观和主观两种类型的题目各举一道来说明问题。

假设在一次500人规模的考试中，从中随机抽取50人的试卷为样本（过程略），现对该试卷的其中两道题进行分析（其中主观题分值为5分，客观题的分值为2分）。在工作表中输入以上指标。如图2所示：

图2试卷样本指标难度在是试题质量的一个重要指标。

难度过大或过小对于一般的考试都是不合适的。一般来讲难度适中(P=0.5)的试题对学生的鉴别能力最强。在出试卷的过程中,出题的难度选择往往要跟考试的性质有关。如对于高考来讲,难度就要根据当年的录取率来确定。在计算难度系数时,要考虑题型的不同。对于客观题来说,考生要么得满分要么得0分,它的记分方法是“二分记分法”。它的难度系数是通过率,即作对人数与总体考生人数之比。而主观题由于存在从0分到满分的多种可能。它的难度系数是总体考生在该题的平均分与该题满分之比。先计算客观题难度系数。在对应单元格中输入公式“=COUNTIF(B2:B31,“<>0”)/COUNTA(B2:B31)”,按回车键得出该客观题的难度系数。在对应单元格中输入公式“=AVERAGE(B2:B31)/5”,按回车后得出该主观题的难度系数。

和计算难度系数一样,区分度的计算也因题型的不同而不同。在教育测量理论中,把相关系数作为区分度。对于“二分记分法”的客观题来说用点二列相关,对于主观题来说用积差相关来求其区分度。点二列相关的计算公式为：根据公式,现在我们需要分别求出答对该题的学生的平均分和比率。复制相应单元格区域,打开“Sheet 2”工作表，将数据粘贴在工作表中。在F1单元格中输入“作对客观题总分”，在F2单元格中输入公式“=IF(B2<>0,D2,0)”。意思是在F2单元格中显示答对客观题的学生的总分，如果做错则显示“0”。在显示出第一位考生的总分后，用复制公式的方法，拖动鼠标至F31处，则在F2:F31区域中显示出所有答对该客观题的考生的总分。在D34单元格中输入公式“=SUM(D2:D31)/30”，按回车键得出全体考生的平均分。在F34单元格中输入公式“＝SUM(F2:F31)/COUNTIF(F2:F31,“<>0”)”,按回车键后得出答对客观题考生的平均分。在C35单元格中输入公式“＝(F34-D34)/D33”,得出平均差与标准差之比。在C36单元格中,输入公式“＝COUNTIF(B2:B31,“<>0”)/31”,得出答对人数比例。在F36单元格中输入公式“1-C36”,得出答错人数比例。在C37单元格中输入公式“＝C35*SQRT(C36/F36)”，算出点二列相关系数。

计算主观题的区分度用的是积差相关，它可以用Excel中的“PEARSOM”(皮尔逊)积差相关函数计算，也可以直接用“工具”菜单下“数据分析”中的相关系数来计算。单击“工具”下的“数据分析”命令。选择“相关系数”选项。

单击“确定”后，在新弹出的“相关系数”对话框中“输入区域”栏目中输入要进行相

关分析的两组数据的地址“$C$1:$D$31”。在“分组方式”上选择“逐列”。在“输出选项”