期中考试来啦，怎样用Excel分析试卷，这篇文章说透了
考试试卷质量统计分析是考试中的一项重要任务，也是教学效果和学生水平检测的重要依据。科学、有效地开展试卷统计分析有利于提高教师的教学水平。笔者以“高三年级第一学期期中数学试卷”为例，介绍如何应用Excel软件工具做数学试卷知识点的统计与分析。
面对大量的考试数据，教师有必要用科学的手段进行试卷分析（收集数据，分析数据，评价数据反思教学，找到学生存在的问题），帮助学生更好地掌握相应的知识点。这也有利于教师改进教学方法，提高教学质量。
本次试卷延用北京高考试卷3种题型“8+6+6”的题数结构，8道选择题，每题5分；6道填空题，每题5分；6道解答题，依次为13分、14分、13分、13分、14分、13分，共80分，满分150分。试卷结构稳定，沉稳中凸显活力；同时以基本知识、基本方法为命题的出发点，基本覆盖了高中代数部分的所有内容，注重主干知识的考查。本次试卷知识点板块、题号、分值分布统计情况详见表1。
本次针对高三（7）班（普通班）56人统计数学成绩。学生的各题分项得分、各题所涉及的知识点统计以及每个学生在各知识点的得分均用表格列出。
试卷的满分、平均分、最高分、最低分、标准差及频率分布数据与直方图如图1所示。
（a）总成绩分析
（b）成绩分段
图 1 试卷总体分析数据与直方图
各分数段人数统计分析见表2。
当皮尔逊统计量小于5.5，所分析数据呈正态分布。通过心理学研究（对智力分布）分析以及大量的统计资料可知，考试成绩呈正态或近似正态分布。因此，若成绩分布呈正态或近似正态，即“中间高、两头低”，则考试成绩是合理的。通过表格汇总分析可以看出此次考试平均分为91.75。观察频率分布直方图可知成绩不呈正态分布。在平均分91.75附近人数较少，其两边人数较多。从皮尔逊统计量也可以看出，学生成绩不呈正态分布。
另外，试卷标准差为23.75大于15，说明学生成绩分化较大。所以，要想提高平均分（减小标准差，使成绩呈正态分布），需将67～89分的学生分数提高到90～104分。如何提高？对这部分学生试卷知识点做统计分析，找出其薄弱知识点，加强训练指导，从而提高考试成绩。
难度指试卷（题）的难易程度，即试题的得分率，其值在0～1。计算公式：
通过图表分析可见，难度中等的知识板块为三角函数和导数，较难的是数列，其余部分较容易。知识点的易、中、难比例为7∶2∶1，大部分难度在0.7以上。分析发现：简单题过多，应提高难度中等试题的比例。从学生答题情况看，对简单题的训练平时教学中还是比较到位的。
三角函数的得分率偏低是由于学生在第一步化简出错，导致14分的题只得了两三分；做导数题，如果求导出错，后续过程就是0分了。在今后教学中应加强这部分的练习，提高学生的计算能力，提高得分率。数列在北京高考中一直是难点，因为是普通班，所以在平时教学中没有过多渗透，今后可以对个别学生进行指导。总体上看，这是一次比较容易的考试，其难度比较适合像期中考试这类目标参照性的考试。
试题的区分度是衡量考生掌握各类知识能力水平的指标。试题区分度高，水平高的学生可得高分，能力弱的学生得低分。笔者采用27%分组法进行计算。区分度
试题区分度评价标准：区分度D>0.4区分度好，0.3<D<0.4区分度较好，0.15≤D<0.3区分度一般，0<D<0.15区分度差。分析可知，绝大部分知识的区分度大于0.4，区分度较高。区分度较差的是积分和集合，说明学生之间的差距没有拉开。区分度最大的是充要条件，说明后27%学生该题的解题水平很差，拉大了区分度。在实际教学中，该知识确实是一个难点，有部分学生不能很好地区分充分条件和必要条件，所以考试后要加强对这部分学生进行充要条件知识的练习。
在进行难度和区分度综合分析时，有人认为课程考试的试题难度在0.4～0.9，同时区分度在0.2以上的试题可视为品质优良的试题。分析可知，除积分区分度小于0.2，集合和积分难度大于0.9，其他皆属于品质优良的试题。
可见，难度小于0.3的试题没有。（1）难度在0.3～0.7的知识考点为数列、三角函数、导数，并且区分度皆大于0.3，说明这部分知识适合摸底考试：难度适中，能反映区分不同水平学生的程度，考出考生的不同水平，把好、中、差三个层次的学生真正区分开。但是对于数列知识，难度为0.38，比三角函数和导数要难，这是因为第20题为一道数列和集合的综合问题，学习不好的学生不会解答，学习好的学生也没时间作答。因为高三（7）为普通班，所以平时授课以基础为主，第20题的形式没有过多渗透，考场上完全凭借学生能力答题。所以这道题的区分度低，没有拉开学生的差距。但数列的区别度为0.37，主要是对第6、10、14、18题的区分度。
（2）难度大于0.7，区分度大于0.3的知识考点为函数、解三角形、平面向量、充要条件。通过数据分析发现，高分考生比低分考生能够更好地掌握该知识点。考查这个知识点能较好地将高分和低分考生区别开。但在教学上，因为这4个知识点相对容易，却出现了大于0.3的区分度，所以教师课下要多关注低分学生，帮助他们掌握知识。
（3）难度大于0.7，区分度在0.15～0.3的知识考点为集合。从数据上看说明不了什么问题，但从教学上看，此题是试卷的第一题，关于集合的考题是最简单的，但是仍有3人得0分。教师课下要关注这3个学生。也就是说对于这一知识点只有区分度为0，才达到了考试要求。
（4）难度大于0.7，区分度小于0.15的知识考点为积分，从数据上看好像不符合好试题的区分度要求：难度达到0.96，区分度仅为0.13，说明试题太简单，考试没意义，该试题应该剔除。但是从内容上看它是教学上要求熟悉的内容，试题很容易，区分度低，正好说明了对于重点内容绝大部分学生掌握得很好，成绩没有拉开，从另一侧面印证教学效果良好。
均方差也称标准差，是衡量学生成绩离散趋势的指标，反映学生成绩的两极分化程度。笔者在Excel中用 STDEVP 计算均方差。均方差较大，表示大部分数值和其平均值之间差异较大；均方差较小，表示这些数值较接近平均值。均方差越小，表明两极分化越小；均方差越大，表明两极分化越严重。知识点均方差比较如图2所示。
图2 知识点均方差比较
从图2中看出，知识点均方差从大到小依次为导数、数列、三角函数、解三角形、函数、平面向量、充要条件、集合和积分。
图3 导数知识分布
从图3分析可知，导数平均分为13.32，均方差最大，平均分两侧的学生偏多，成绩较分散，导数成绩两极分化较严重。其他，以此类推。
分析发现，知识相关性大于0.3的主要集中在函数、三角函数和数列上，说明知识相关性很强。因为本次考试主要考查高中代数部分，而函数贯穿于高中数学教学的始终，对函数知识的掌握情况可以在三角、数列、不等式、导数中考查。试卷的命题方向符合该知识的相关性。
总体上看，学生在此次考试中发挥了实际水平，但通过数据分析发现还存在一些问题。简单题得分不理想，集合、积分区分度为0，所有学生需拿到5分；要提高充要条件答题正确率。对于函数和平面向量知识点的教学，重视基础，强调通性通法。学生基础还不够扎实，对基本技能掌握还不够熟练，需要加强训练，减少不必要的计算失误。例如，关于解三角形和三角函数的考点，在高考试卷中一般位于解答题第一题的位置，学生能否正确解答很重要。在此次考试中，有些学生因为公式记忆错误、计算失误等原因造成该知识点得分偏低。在今后的教学中应注意，让学生多做多想，引导学生吃透本质，分析归纳，攻其一题，通其一类。
（作者系北京市大兴区兴华中学教师）
文章来源：《中小学数字化教学》2018年第6期，原标题为《电子表格在数学试题统计分析中的应用》，内容有删减
图片来源：封面图片来源于“全景网”
责任编辑：祝元志
微信编辑：李中华
监 制：朱哲
往期荐读