5.4 监考安排表
案例背景
每逢学期期末考试前，教务处要组织全校考务安排，完成全校各个班级的监考安排表的制作，教师们根据“监考安排表”按指定时间到指定班级监考。一般来说，一次考试按时间顺序有若干场此考试，同一场次有若干班级。而一名教师可能承担若干场监考任务。安排监考表的要求是：第一，一名教师不能在同一时间被同时分配到两个班级监考；第二，每名教师的监考次数要尽量均衡、合理。
本案例以某中学期末考试为例，应用Excel技术制作监考安排表，要求本年级每位教师至少监考1场，不能超过3场，监考安排具有自检功能，出现错误或不符合监考安排规则时，根据监考表提示重新安排。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识：数字的”货币“格式，条件格式
●函数应用：COUNTIF函数，OR函数，SUM函数，MAX函数
●综述：逻辑判断，数组公式
最终效果展示
5.4.1创建监考安排表
Step 1创建工作簿，重名工作表
新建一个Excel工作表，保存为“监考安排表.xls”，将工作表”Sheet1”重命名为“监考表”，”sheet2” 重命名为”教师名单”，删除其余工作表。
Step 2输入监考表框架信息
①选中下一个区域A1:G1，设置为“合并及居中”输入标题“2008-2009学年第二学期高一期末考试监考表”。
②在单元格区域A2:G5输入考试的日期、上午、下午、时间和考试科目等信息
③在单元格区域A6:A13输入高一年级8个班的班级名称，在单元格区域A14：A15输入 “巡视”和“自检”。
④选中单元格区域A2:G15，为表格设置边框
Step3安排监考人员
在单元格区域B6:G14输入监考教师和巡视人员。
Step4应用条件格式区分监考场次
①选中单元格B6，单击“格式”→“条件格式”，弹出“条件格式”对话框。
②单击“条件格式”对话框的“条件1“选项框右侧的下箭头按钮选择”公式“，然后在其右侧的公式框中输入如下公式:
” =OR(SUM((B$6:B$14=B6)*1)>=2,SUM(($B$6:$G$14=B6)*1)=4)*(B6<>"")”
③然后单击“格式“按钮，弹出”格式“对话框。在”格式“对话框的”字形“选项框选择”加粗加斜“，在”颜色“框中选择红色。
④切换到图案选项卡，在”颜色“框中选择”黄色“。
⑤单击“确定”按钮返回条件格式”对话框。
⑥再单击“条件格式”对话框的“条件2“选项框右侧的下箭头按钮选择”公式“，然后在其右侧的公式框中输入如下公式:
“=SUM(($B$6:$G$14=B6)*1)=3 “
参照上面步骤，设置格式为蓝色字体并加粗。
⑦再单击“条件格式”对话框的“条件3“选项框右侧的下箭头按钮选择”公式“，然后在其右侧的公式框中输入如下公式:
“=SUM(($B$6:$G$14=B6)*1)=2 “
参照上面步骤，设置格式为绿色字体。
⑧单击“确定”按钮完成单元格B6的条件格式的设置。
⑨单击常用工具栏按钮“格式刷“，光标选中单元格区域B6:G14，将单元格B6的格式传递到整个监考教师名单区域B6:G14，从而完成条件格式的设置。
Step5设置监考“重排“自检功能
①选中单元格B15，在编辑栏输入如下数组公式，按组合键确认。
“=IF(MAX(COUNTIF(B6:B14,B6:B14))>1,"重复","ok") “
②选中单元格B15，向右拖曳右下角的填充柄至单元格G15完成公式填充。
Step6重新设置表格边框和底纹
①选中单元格区域A2:G15为表格设置边框。
②选中单元格区域A2:G5，为其设置底纹
至此“监考安排表“初步制作完成，按照预定的安排监考的规则我们可以发现，”监考安排表“提示我们安排出现错误：7月9日下午安排教师“顾菲“同时到”高一3班“和”高一8班“监考外语，应该予以纠正。另外”监考安排表“还提示我们教师” 诸宏健“安排了4次监考，违反了预定的安排监考规则，应该适当调整。
5.4.2创建监考统计表
Step1输入监考统计表原始信息
①单击工作表标签“教师名单“，在单元格A1:E1，分别输入”序号“，”教师姓名“，”监考次数“，”标准“和”监考费“。
②在单元格B2:B21,陆续输入应该参加期末考试监考的教师姓名，在单元格A2输入1，单元格A3输入2，选中单元格区域A2:A3，双击单元格A3右下角的填充柄即可完成序号的填充。
③选中单元格区域D2:D21，输入监考费标准“30“，按组合键确认完成单元格区域内的数据批量输入。
Step2统计监考次数和监考费
①在单元格C2输入如下公式，然后按< Inter>键确认即可完成第一个教师监考次数的统计。
“=COUNTIF(监考表!$B$6:$G$14,B2) “
②选中单元格C2，双击单元格C2右下角的填充柄即可完成其他教师监考次数的统计。
③在单元格E2中输入如下公式，然后按< Inter>键确认即可完成第一个教师监考费的统计。
“=C2*D2 “
④选中单元格E2，双击单元格E2右下角的填充柄即可完成其他教师监考费的统计。
⑤在单元格A22输入“合计“，在单元格E22输入如下公式，然后按< Inter>键确认即可完成期末考试监考费的统计。
“=SUM(E2:E21) “
Step3设置“监考费“为”货币“格式
①选中单元格区域E2:E22，按组合键弹出“单元格格式“对话框，切换到”数字“选项卡，在”分类“选项框中选择”货币“，在”货币符号“选项框中选择”￥“。
②单击“确定”按钮，即可完成将监考费“设置为”货币“格式。
至此监考次数和监考费的统计工作全部完成，因为是动态的统计，可以根据统计结果调整原先“监考安排表”中违反预先设定监考安排规则之处，统计结果显示原先监考安排有如下不合理之处：下面根据统计结果重新对监考安排表做出如下微调。
第一，教师“潘艳波”未安排监考，贮备安排3次监考，首先安排潘艳波替换已经安排5此监考的刘帅老师的7月8日上午高一6班语文课监考，其次替换7月9日下午高一8班排重的顾菲老师，最后再替换已经安排4次的诸宏健老师7月10日下午高一4班的政治课监考。
第二，安排刘超老师替换已经安排5次监考的范春玲老师的7月8日上午高一7班语文课监考和7月9日下午高一6班外语课监考。
第三，安排吕晓辰老师替换已经安排5次监考的杨新玉老师7月8日上午语文课和7月10上午高一8班化学课的监考。
第四，安排王文静老师替换刘帅老师7月9日下午高一5班外语课的监考。
Step4微调监考安排
①光标切换到“教师名单”工作表，选中单元格B21，按组合键复制，单击工作表标签“监考表”按键同时选中单元格B11，E13和G9，按组合键粘贴。
②光标切换到“教师名单”工作表，选中单元格B20，按组合键复制，单击工作表标签“监考表”按键同时选中单元格B12和E11，按组合键粘贴。
③光标切换到“教师名单”工作表，选中单元格B10，按组合键复制，单击工作表标签“监考表”按键同时选中单元格C14和F13，按组合键粘贴。
④光标切换到“教师名单”工作表，选中单元格B2，按组合键复制，单击工作表标签“监考表”按键同时选中单元格E10，按组合键粘贴。
⑤光标切换到“教师名单”工作表，可以看出经过调整后，如图31所示，所有教师监考次数均在2—3次之间了，至此监考那批表制作完成，可以组织老师按此“监考安排表”到时进考场监考或巡视了。
关键知识点解析
案例案例解析
5.4.1节Step4之②中单元格A6中的条件1的公式为
” =OR(SUM((B$6:B$14=B6)*1)>=2,SUM(($B$6:$G$14=B6)*1)=4)*(B6<>"")”
其中(B$6:B$14=B6)为一含有9个逻辑值TRUE或FALSE的一维数组，而通过将此以逻辑值为元素的一维数组乘以“1”后变为含有9个“1”或“0”的一维数组。即(B$6:B$14=B6)*1变成（1，0，0，0，0，0，0，0，0），此时SUM((B$6:B$14=B6)*1)= SUM（1，0，0，0，0，0，0，0，0）=1，
而($B$6:$G$14=B6)为一含有9行6列的多维数组，SUM(($B$6:$G$14=B6)*1)的输出结果是求出整个单元格区域$B$6:$G$14中等于单元格B6的数量，
SUM(($B$6:$G$14=B6)*1)=4则是判断整个单元格区域$B$6:$G$14中等于单元格B6的数量是否等于4，综上单元格A6中条件格式“条件1”的公式OR函数的第一个判断是B6:B14中等于B6的值的和是否大于或等于2，OR函数的第二个判断单元格区域$B$6:$G$14中等于单元格B6的数量是否等于4，如果满足上面连个条件之一，该单元格的文字就显示为红色加粗加斜，背景为黄色。
5.4.1节Step5中单元格E15中的自检公式为：
“=IF(MAX(COUNTIF(E6:E14,E6:E14))>1,"重复","ok")”
这是一个数组公式，下面以示意表形式解析公式
在原先监考安排表中教师“顾菲“同时被安排到”高一3班“和”高一8班“监考外语，因此“自检”公式输出结果“重复”，提示负责安排监考的工作人员重新安排。
|
