Excel在学校中的应用36.docx

Excel在学校中的应用36 　　监考安排表　　案例背景　　每逢学期期末考试前，教务处要组织全校考务安排，完成全校各个班级的监考安排表的制作，老师们依据“监考安排表”按指定时间到指定班级监考通常来说，一次考试按时间次序有若干场此考试，同一场次有若干班级而一名老师可能负担若干场监考任务安排监考表的要求是：第一，一名老师不能在同一时间被同时分配到两个班级监考；第二，每名老师的监考次数要尽可能均衡、合理　　本案例以某中学期末考试为例，应用Excel技术制作监考安排表，要求本年级每位老师最少监考1场，不能超出3场，监考安排含有自检功效，出现错误或不符合监考安排规则时，依据监考表提醒重新安排　　关键技术点　　要实现本案例中的功效，学员应该掌握以下EXCEL技术点　　●基础知识：数字的”货币“格式，条件格式　　●函数应用：COUNTIF函数，OR函数，SUM函数，MAX函数　　●综述：逻辑判定，数组公式　　最终效果展示　　创立监考安排表　　Step 1创立工作簿，重名工作表　　新建一个Excel工作表，保留为“监考安排表.xls”，将工作表”Sheet1”重命名为“监考表”，”sheet2” 重命名为”老师名单”，删除其他工作表。

　　Step 2输入监考表框架信息　　①选中下一个区域A1:G1，设置为“合并及居中”输入标题“2021-2021学年第二学期高一期末考试监考表”　　②在单元格区域A2:G5输入考试的日期、早晨、下午、时间和考试科目等信息　　③在单元格区域A6:A13输入高一年级8个班的班级名称，在单元格区域A14：A15输入 “巡视”和“自检”　　④选中单元格区域A2:G15，为表格设置边框　　Step3安排监考人员　　在单元格区域B6:G14输入监考老师和巡视人员　　Step4应用条件格式区分监考场次　　①选中单元格B6，单击“格式”→“条件格式”，弹出“条件格式”对话框　　②单击“条件格式”对话框的“条件1“选项框右侧的下箭头按钮选择”公式“，然后在其右侧的公式框中输入以下公式:　　” =OR*1)>=2,SUM*1)=4)*”　　③然后单击“格式“按钮，弹出”格式“对话框在”格式“对话框的”字形“选项框选择”加粗加斜“，在”颜色“框中选择红色　　④切换到图案选项卡，在”颜色“框中选择”黄色“　　⑤单击“确定”按钮返回条件格式”对话框　　⑥再单击“条件格式”对话框的“条件2“选项框右侧的下箭头按钮选择”公式“，然后在其右侧的公式框中输入以下公式:　　“=SUM*1)=3 “　　参考上面步骤，设置格式为蓝色字体并加粗。

　　⑦再单击“条件格式”对话框的“条件3“选项框右侧的下箭头按钮选择”公式“，然后在其右侧的公式框中输入以下公式:　　“=SUM*1)=2 “　　参考上面步骤，设置格式为绿色字体　　⑧单击“确定”按钮完成单元格B6的条件格式的设置　　⑨单击常见工具栏按钮“格式刷“，光标选中单元格区域B6:G14，将单元格B6的格式传输到整个监考老师名单区域B6:G14，从而完成条件格式的设置　　Step5设置监考“重排“自检功效　　①选中单元格B15，在xx栏输入以下数组公式，按组合键确定　　“=IF)>1,"反复","ok") “　　②选中单元格B15，向右拖曳右下角的填充柄至单元格G15完成公式填充　　Step6重新设置表格边框和底纹　　①选中单元格区域A2:G15为表格设置边框　　②选中单元格区域A2:G5，为其设置底纹　　至此“监考安排表“初步制作完成，根据预定的安排监考的规则我们能够发觉，”监考安排表“提醒我们安排出现错误：7月9日下午安排老师“顾菲“同时到”高一3班“和”高一8班“监考外语，应该给予纠正另外”监考安排表“还提醒我们老师” 诸宏健“安排了4次监考，违反了预定的安排监考规则，应该合适调整。

　　创立监考统计表　　Step1输入监考统计表原始信息　　①单击工作表标签“老师名单“，在单元格A1:E1，分别输入”序号“，”老师姓名“，”监考次数“，”标准“和”监考费“　　②在单元格B2:B21,陆续输入应该参与期末考试监考的老师姓名，在单元格A2输入1，单元格A3输入2，选中单元格区域A2:A3，双击单元格A3右下角的填充柄即可完成序号的填充　　③选中单元格区域D2:D21，输入监考费标准“30“，按组合键确定完成单元格区域内的数据批量输入　　Step2统计监考次数和监考费　　①在单元格C2输入以下公式，然后按键确定即可完成第一个老师监考次数的统计　　“=COUNTIF “　　②选中单元格C2，双击单元格C2右下角的填充柄即可完成其它老师监考次数的统计　　③在单元格E2中输入以下公式，然后按键确定即可完成第一个老师监考费的统计　　“=C2*D2 “　　④选中单元格E2，双击单元格E2右下角的填充柄即可完成其它老师监考费的统计　　⑤在单元格A22输入“累计“，在单元格E22输入以下公式，然后按键确定即可完成期末考试监考费的统计　　“=SUM “　　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次之间了，至此监考那批表制作完成，能够组织老师按此“监考安排表”到时进考场监考或巡视了　　关键知识点解析　　案例案例解析　　节Step4之②中单元格A6中的条件1的公式为　　” =OR*1)>=2,SUM*1)=4)*”　　其中为一含有9个逻辑值TRUE或FALSE的一维数组，而经过将此以逻辑值为元素的一维数组乘以“1”后变为含有9个“1”或“0”的一维数组即*1变成，此时SUM*1)= SUM=1　　而为一含有9行6列的多维数组，SUM*1)的输出结果是求出整个单元格区域$B$6:$G$14中等于单元格B6的数量　　SUM*1)=4则是判定整个单元格区域$B$6:$G$14中等于单元格B6的数量是否等于4，综上单元格A6中条件格式“条件1”的公式OR函数的第一个判定是B6:B14中等于B6的值的和是否大于或等于2，OR函数的第二个判定单元格区域$B$6:$G$14中等于单元格B6的数量是否等于4，假如满足上面连个条件之一，该单元格的文字就显示为红色加粗加斜，背景为黄色。

　　节Step5中单元格E15中的自检公式为：　　“=IF)>1,"反复","ok")”　　这是一个数组公式，下面以示意表形式解析公式　　在原先监考安排表中老师“顾菲“同时被安排到”高一3班“和”高一8班“监考外语，所以“自检”公式输出结果“反复”，提醒负责安排监考的工作人员重新安排。