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$4030)/分题汇总!$B5) ”
按组合键确认，统计出1班第1题的正确率。
②鼠标向下拖曳单元格C5右下角的填充柄至C12，松开鼠标完成2班至8班第1题的正确率统计工作。
Step6统计年级第一题的“正确率”
在单元格C13输入公式：“=SUM((成绩转换!C$4:C$4030)*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，单击常用工具栏的“合并及居中”按钮。
②在单元格C14中输入公式：“=MAX(C5:C12) ” ，按键确认即可完成第一题最高正确率的统计工作。
③向右拖曳单元格C14右下角的填充柄至N14即可完成第2题至第12 题的最高正确率的统计工作。
Step11隐藏数据区公式
选中单元格区域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中输入公式：“=G5/成绩转换!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班“说明文阅读““得分率”的排名位置的统计工作。
③选中单元格区域O5:O12，向右拖曳单元格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$4030)/分题汇总!$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$4030) “也是含有400个”1“和”0“的一维数组，代表全年级400名考生”题1“的得分人数。
而 (成绩转换!$A$4:$A$403=分题汇总!$A5)*(成绩转换!C$4:C$4030)
两个数组相乘结果仍然是函数400个”1“和”0“的一维数组，其中1的个数恰好是1班考生”题1“的得分人数”41“，此时公式化简为=SUM（1，1，1，1，1……1，0，0，……0，0，0）*（1，0，0，1，1，1，1……）/51
公式继续化简为=SUM（1，0，0，1，1，1，1……）/51
=41/51=80.39%
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$4030)/分题汇总!$B5) ”
按组合键确认，统计出1班第1题的正确率。
②鼠标向下拖曳单元格C5右下角的填充柄至C12，松开鼠标完成2班至8班第1题的正确率统计工作。
Step6统计年级第一题的“正确率”
在单元格C13输入公式：“=SUM((成绩转换!C$4:C$4030)*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，单击常用工具栏的“合并及居中”按钮。
②在单元格C14中输入公式：“=MAX(C5:C12) ” ，按键确认即可完成第一题最高正确率的统计工作。
③向右拖曳单元格C14右下角的填充柄至N14即可完成第2题至第12 题的最高正确率的统计工作。
Step11隐藏数据区公式
选中单元格区域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中输入公式：“=G5/成绩转换!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班“说明文阅读““得分率”的排名位置的统计工作。
③选中单元格区域O5:O12，向右拖曳单元格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$4030)/分题汇总!$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$4030) “也是含有400个”1“和”0“的一维数组，代表全年级400名考生”题1“的得分人数。
而 (成绩转换!$A$4:$A$403=分题汇总!$A5)*(成绩转换!C$4:C$4030)
两个数组相乘结果仍然是函数400个”1“和”0“的一维数组，其中1的个数恰好是1班考生”题1“的得分人数”41“，此时公式化简为=SUM（1，1，1，1，1……1，0，0，……0，0，0）*（1，0，0，1，1，1，1……）/51
公式继续化简为=SUM（1，0，0，1，1，1，1……）/51
=41/51=80.39%