考试试卷分析诊断表

4.9 考试试卷分析诊断表

案例背景

每逢考试阅卷完毕，成绩汇总出来后，学校领导，年级组长、学科组长都会从不同角度关心考试结果，而任课教师除了要关心自己任教的班级学生的成绩外，还应该根据总体考试情况对试卷进行分析，通过统计每道题的得分率，平均分，正确率等，来分析每道题所涵盖的知识点的落实情况，命题人员应该根据统计结果与命题预设目标进行对比分析，以便使得今后命题能够使得考试结果更接近预设目标，任课教师通过试卷分析，找出自己教学中存在的问题，以便今后改进教学。

本案例以天津市2002年高中会考的某所学校的八个班的400名学生的语文试卷的客观题部分的考试成绩进行统计和分析。

关键技术点

要实现本案例中的功能，学员应该掌握以下EXCEL技术点。

●基础知识快速选择矩形区域冻结窗格添加辅助行按行排序数据的百分比形式显示

●函数应用 IF函数， COUNTIF函数， SUM函数， Rank函数

●综述数组公式

最总效果展示

4.9.1创建试卷成绩统计表

Step1创建工作簿、重名工作表

创建工作簿“试卷分析.xls”，然后将工作表重命名为“数据表”，“成绩转换”和“分题汇总”。

Step2导入答题卡原始数据

将八个班的400名考生答题卡汇总结果粘贴到“数据表”。

Step3设计成绩转换表框架

①在单元格区域A1：R1输入表格字段名称，如图3所示。

②在单元格B2输入“标准答案”，在单元格区域C2:N2分别输入各题的标准答案的字母。

④在单元格B3输入“分数”，在单元格区域C3:N3分别输入各题的分数。

Step4输入班级名称和学生考号

①单击工作表标签“数据表”，首先选中单元格区域A2:B2，然后按组合键选中单元格区域A2：B401。

②按组合键复制，切换到“成绩转换”工作表，单击单元格A4，按组合键粘贴。

Step 5实施成绩转换

①在单元格C4中输入公式：“=IF(数据表!C2=成绩转换!C$2,C$3,0)”

按键确认即可得到第一名考生“题1”的分数。

②单击单元格C4，向右拖曳填充柄至单元格N4即可完成第一名考生“题2”至“题12”的得分统计。

③选中单元格区域C4:N4，左键双击单元格N4右下角的填充柄即可完成其余学生的“题1”至”题12”的得分统计。

Step 6冻结窗格

当编辑过长或过宽的Excel工作表时，需要向下或向右滚动屏幕，这时表头也会相应的滚动而不能在屏幕上显示出来，从而造成用户不便，设施我们可以通过“冻结窗格”，让上面或左面的表头内容不随屏幕滚动。单击第4行的行标以选中第4行，然后单击菜单“窗口”→“冻结窗格”。

这时在第4行的上方就会插入一条冻结线，之后拖动上下滚动条即可查看每个学生的数据的详细情况，而表头行始终存在。

如果不需要时可以单击菜单“窗口”→“取消冻结窗格”，即可取消对窗格的冻结。

Step 7统计“说明文阅读”，“小说阅读”和“文言文阅读”的单项成绩和“客观题”总成绩。

“题1”至“题4”的内容为说明文阅读，“题5”至“题8”的内容为“小说阅读”，“题9”至“题12”的内容为“文言文阅读”。

①在单元格O4中输入公式：“=SUM(C4:F4)”，按键确认。

②在单元格P4中输入公式：“=SUM(G4:J4)”，按键确认。

③在单元格Q4中输入公式：“=SUM(K4:N4)”，按键确认。

④在单元格R4中输入公式：“=SUM(O4:Q4)”，按键确认。

⑤选中单元格区域O4:R4，左键双击单元格R4右下角的填充柄即可完成其余学生的“说明文阅读”，“小说

阅读”和“文言文阅读”的单项成绩和“客观题”总成绩统计工作。

4.9.2按题进行诊断分析

Step1设计按题诊断分析表

按图15所示设计按题诊断分析表

Step2输入班级名称

在单元格A5输入“1班”，选中单元格A5，向下拖曳其右下角的填充柄至A12即可完成8个班的班级名称

的输入。

Step 3汇总实考人数

①在单元格A13中输入“合计”，B13中预设公式“=SUM(B5:B12)”，按键确认。

②在单元格B5中输入公式“=COUNTIF(成绩转换!$A$4:$A$403,A5) ”

按键确认即可得到1班实考人数。

③选中单元格B5，左键双击单元格B5右下角的填充柄即可完成2班至8班实考人数的统计。

Step4调整列宽

按键，单击列标C，E，G，I，K，M，O，Q，S，U，W，Y，选中上述各列后，用光标将列宽调整为“宽

度：7.00（61像素）”。

Step5统计各班第一题的“正确率”

①选中单元格C5，输入数组公式：“=SUM((成绩转换!$A$4:$A$403=分题汇总!$A5)*(成绩转换!C$4:C$403<>0)/分题汇总!$B5) ”

按组合键确认，统计出1班第1题的正确率。

②鼠标向下拖曳单元格C5右下角的填充柄至C12，松开鼠标完成2班至8班第1题的正确率统计工作。

Step6统计年级第一题的“正确率”

在单元格C13输入公式：“=SUM((成绩转换!C$4:C$403<>0)*1)/分题汇总!$B13”按组合键确认，统计出年级第1题的正确率。

Step6添加辅助行

①在单元格C15中输入“1”，在单元格D15中输入“2”，然后选中单元格区域C15：D15，向右拖曳单元格

D15至Z15，完成序列填充。

②在单元格C16输入“1”，在单元格E16中输入“2”……，在单元格Y16中输入“12”。（隔列输入连续自

然数1--12）

③在单元格D16输入“13”，在单元格F16中输入“14”……，在单元格Z16中输入“24”。（隔列输入连续自

然数13--24）。

Step7按第16行升序排序

①选中单元格区域C4:Z16,单击菜单“数据”→“排序”，弹出“排序”对话框，单击“选项”按钮，弹出“排序选项”

对话框，在“方向”选项框中单击“按行排序”按钮。

②单击“确定”按钮，返回“排序”对话框，在“主要关键字”选项框中选择“第16行”，选择“升序”。

③单击“确定”按钮即可完成按第16行升序排序。

Step8统计各班第2题至第12题的“正确率”

①选中单元格区域C5:C12，向右拖曳单元格C12右下角的填充柄至N12完成公式的填充。

②按组合键弹出“单元格格式”对话框,切换到“数字”选项卡，在“分类”选项框中选择“百分比”，在“小数位数”

选项框输入“1”。

③单击“确定”按钮，将统计各题“正确率”的数据区设置为百分比形式显示。