考试试卷分析诊断表

4.9考试试卷分析诊断表案例背景每逢考试阅卷完毕，成绩汇总出来后，学校领导，年级组长、学科组长都会从不同角度关心考试结果，而任课教师除了要关心自己任教的班级学生的成绩外，还应该根据总体考试情况对试卷进行分析，通过统计每道题的得分率，平均分，正确率等，来分析每道题所涵盖的知识点的落实情况，命题人员应该根据统计结果与命题预设目标进行对比分析，以便使得今后命题能够使得考试结果更接近预设目标，任课教师通过试卷分析，找出自己教学中存在的问题，以便今后改进教学。本案例以天津市2002年高中会考的某所学校的八个班的400名学生的语文试卷的客观题部分的考试成绩进行统计和分析。关键技术点要实现本案例中的功能，学员应该掌握以下EXCEL技术点。•基础知识快速选择矩形区域冻结窗格添加辅助行按行排序数据的百分比形式显示•函数应用IF函数，COUNTIF函数，SUM函数，Rank函数•综述数组公式最总效果展示4.9・1创建试卷成绩统计表Stepl创建工作簿、重名工作表创建工作簿“试卷分析.xls”，然后将工作表重命名为“数据表”，“成绩转换”和“分题汇总”。Step2导入答题卡原始数据将八个班的400名考生答题卡汇总结果粘贴到“数据表”。Step3设计成绩转换表框架在单元格区域A1：日1输入表格字段名称，如图3所示。在单元格B2输入“标准答案”，在单元格区域C2:N2分别输入各题的标准答案的字母。④在单元格B3输入“分数”，在单元格区域C3:N3分别输入各题的分数。Step4输入班级名称和学生考号单击工作表标签“数据表”，首先选中单元格区域A2:B2，然后按组合键选中单元格区域A2：B401。按组合键复制，切换到“成绩转换”工作表，单击单元格A4,按组合键粘贴。Step5实施成绩转换在单元格C4中输入公式："=IF(数据表!C2=成绩转换!C$2,C$3,0)”按键确认即可得到第一名考生“题1”的分数。单击单元格C4,向右拖曳填充柄至单元格N4即可完成第一名考生“题2”至“题12”的得分统计。选中单元格区域C4:N4，左键双击单元格N4右下角的填充柄即可完成其余学生的“题1”至”题12”的得分统计。Step6冻结窗格当编辑过长或过宽的Excel工作表时，需要向下或向右滚动屏幕，这时表头也会相应的滚动而不能在屏幕上显示出来，从而造成用户不便，设施我们可以通过，冻结窗格”，让上面或左面的表头内容不随屏幕滚动。单击第4行的行标以选中第4行，然后单击菜单“窗口”一“冻结窗格”。这时在第4行的上方就会插入一条冻结线，之后拖动上下滚动条即可查看每个学生的数据的详细情况，而表头行始终存在。如果不需要时可以单击菜单“窗口”一“取消冻结窗格”，即可取消对窗格的冻结。Step7统计“说明文阅读”，“小说阅读”和“文言文阅读”的单项成绩和“客观题”总成绩。“题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按题进行诊断分析Stepl设计按题诊断分析表按图15所示设计按题诊断分析表Step2输入班级名称在单元格A5输入“1班”，选中单元格A5，向下拖曳其右下角的填充柄至A12即可完成8个班的班级名称的输入。Step3汇总实考人数在单元格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统计各班第一题的“正确率”选中单元格。5，输入数组公式："=SUM（（成绩转换!$A$4:$A$403=分题汇总!$A5）*（成绩转换!。$4:。$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”。单击“确定”按钮，将统计各题“正确率”的数据区设置为百分比形式显示。Step9统计各班第1题至第12题的“正确率”的排名位置在单元格O5输入公式："=RANK（C5，C$5:C$12）”，按键确认，完成1班第一题正确率在8个班中名次位置的统计。向下拖曳单元格O5右下角的填充柄至单元格O12，完成2班至8班第一题正确率在8个班中各自名次位置的统计。向右拖曳单元格O12右下角的填充柄至单元格Z12,完成8个班第2题至第12题的正确率在8个班中各自名次位置的统计。Step10统计每题“正确率”的最高分在单元格A14中输入“该项最高分”，选中单元格区域A14:B14，单击常用工具栏的“合并及居中”按钮。在单元格64中输入公式：q/XC&C12）”，按键确认即可完成第一题最高正确率的统计工作。向右拖曳单元格C14右下角的填充柄至N14即可完成第2题至第12题的最高正确率的统计工作。Stepll隐藏数据区公式选中单元格区域C5:Z13，按组合键复制，单击菜单“编辑”一“选择性粘贴”，弹出“选择性粘贴”对话框，在“选择性粘贴”对话框的“粘贴”选项框中选择“数值”选项，单击“确定”按钮，完成公式的隐藏。Step12按第15行升序重新排序参照Step7按第16行排序的方法，按第15行升序重新排序。选中单元格区域C15:Z16，按键清除辅助行区域内容。4.9.3按题类别进行诊断分析Step1创建“分类汇总”工作表单击菜单“插入”一”工作表”，插入一张新的工作表，将其重命名为”分类汇总“。Step2设计按题类别诊断分析表按图39样式设计按题类别诊断分析表Step3统计各班“说明文阅读“的”平均分“，“得分率”和排名“位置“在单元格D5中输入公式SUM（（成绩转换!$A$4:$A$403=分类汇总!$A5）*（成绩转换!O$4:O$403））/分类汇总!$B5"，按组合键确认，统计出1班“说明文阅读“的”平均分“。向下拖曳单元格D5右下角的填充柄至单元格D12，完成2班至8班“说明文阅读“的”平均分“的统计。在单元格D13输入公式：“=SUM（成绩转换!O$4:O$403）/分类汇总!$B$13"按键确认，统计出全年级400名学生的“说明文阅读“的平均”平均分“。Step4添加辅助行在单元格C15中输入“1”，在单元格D15中输入“2”，然后选中单元格区域C15：D15，向右拖曳单元格D15至R15，完成序列填充。在单元格C16中输入“1”，在单元格G16中输入“2”，在单元格K16中输入“3”，在单元格O16中输入“4”，（每隔3列输入连续自然数）。在单元格D16中输入“5”，在单元格H16中输入“6”，在单元格L16中输入“7”，在单元格P16中输入“8”，（每隔3列输入连续自然数）。在单元格E16中输入“9”，在单元格I16中输入“10”，在单元格M16中输入“11”，在单元格R16中输入“12”，（每隔3列输入连续自然数）。在单元格F16中输入“13”，在单元格J16中输入“14”，在单元格N16中输入“15”，在单元格R16中输入“16”，（每隔3列输入连续自然数）。Step4按第16行升序重新排序按键光标连续选中单元格C5,G5,K5,O5，单击常用工具栏“合并及居中“按钮，取消“合并及居中“。参照4.8.2节Step7按行排序的方法，按第16行升序重新排序。Step5统计各班”小说阅读”，“文言文阅读“和”客观题总分“的”得分率“选中单元格区域G5:G13，向右拖曳单元格G13右下角的填充柄至单元格J13，即可完成各班”小说阅读”，“文言文阅读“和”客观题总分“的”平均分“的统计。Step6统计各班“说明文阅读"”小说阅读”，“文言文阅读“和”客观题总分“的”得分率“在单元格K5中输入公式：“=65/成绩转换!O$3"，按键确认即可完成1班“说明文阅读“的”得分率“的统计工作。向下拖曳单元格K5右下角的填充柄至单元格K13,完成2班至8班以及全年级“说明文阅读“的平均”得分率“的统计。选中单元格区域K5:K13，向右拖曳单元格K13右下角的填充柄至N列，即可完成各班”小说阅读”，“文言文阅读“和”客观题总分“的”得分率“的统计工作。按组合键弹出“单元格格式”对话框,切换到“数字”选项卡，在“分类”选项框中选择“百分比”，在“小数位数”选项框输入“2”，单击“确定”按钮，将统计“得分率”的数据区设置为百分比形式显示。Step7统计各班的位置在单元格O5中输入公式：“=RANK（K5,K$5:K$12）“，按键确认，即可得到1班“说明文阅读““得分率”的排名位置。向下拖曳单元格O5右下角的填充柄至单元格O12即可完成2班至8班“说明文阅读““得分率”的排名位置的统计工作。选中单元格区域05:012，向右拖曳单元格O12右下角的填充柄至R列即可统计出各班”小说阅读”，“文言文阅读“和”客观题总分“的”得分率“的排名位置。Step8按第15行升序重新排序选中单元格区域C4:R16，按组合键复制，单击菜单“编辑”一“选择性粘贴”，弹出“选择性粘贴”对话框，在“选择性粘贴”对话框的“粘贴”选项框中选择“数值”选项，单击“确定”按钮，完成公式的隐藏。参照4.8.2节Step7按行排序的方法，按第15行升序重新排序。鼠标选中第15行和16行，按键清除辅助行内容。Step9修饰美化诊断分析表按图55和图56样式为诊断分析表设置边框和文字效果至此客观题诊断分析表的统计工作全部完成。案例公式解析：本案例中只使用了常用函数SUM函数和RANK函数，但多数公式都是数组公式，使用数组公式可以有效减少函数的嵌套，提高公式的运算效率，下面针对案例中的数组公式进行解析。工作表“分题汇总“中单元格C5的数组公式为："=SUM（（成绩转换!$A$4:$A$403=分题汇总!$A5）*（成绩转换!C$4:C$403<>0）/分题汇总!$B5）”其中“（成绩转换!$A$4:$A$403=分题汇总!$A5）“为含有400个”1“和”0“的一维数组，其中前51个数是”1“，后面是349各”0“，”1“的个数就是1班的实考人数51，其形式如（1，1，1......，1，0，0......0）。而“（成绩转换!C$4:C$403<>