Excel在学校中的应用36监考安排表 监考安排表Word文档格式.docx

Excel在学校中的应用36监考安排表 监考安排表Word文档格式.docx

《Excel在学校中的应用36监考安排表 监考安排表Word文档格式.docx》由会员分享，可在线阅读，更多相关《Excel在学校中的应用36监考安排表 监考安排表Word文档格式.docx（7页珍藏版）》请在冰豆网上搜索。

●基础知识：

数字的”货币“格式，条件格式

●函数应用：

COUNTIF函数，OR函数，SUM函数，MAX函数

●综述：

逻辑判断，数组公式

最终效果展示

5.4.1创建监考安排表

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

新建一个Excel工作表，保存为“监考安排表.xls”，将工作表”Sheet1”重命名为“监考表”，”sheet2”重命名为”教师名单”，删除其余工作表。

Step2输入监考表框架

①选中下一个区域A1:

G1，设置为“合并及居中”输入标题“xx-xx学年第二学期高一期末考试监考表”。

②在单元格区域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“选项框右侧的下箭头按钮选择”公式“，然后在其右侧的公式框中输入如下公式:

$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输入如下公式，然后按键确认即可完成第一个教师监考次数的统计。

“=COUNTIF（监考表!

$B$6:

$G$14,B2）“

②选中单元格C2，双击单元格C2右下角的填充柄即可完成其他教师监考次数的统计。

③在单元格E2中输入如下公式，然后按键确认即可完成第一个教师监考费的统计。

“=C2*D2“

④选中单元格E2，双击单元格E2右下角的填充柄即可完成其他教师监考费的统计。

⑤在单元格A22输入“合计“，在单元格E22输入如下公式，然后按键确认即可完成期末考试监考费的统计。

“=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的公式为

其中（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））>

这是一个数组公式，下面以示意表形式解析公式

在原先监考安排表中“顾菲“同时被安排到”高一3班“和”高一8班“监考外语，因此“自检”公式输出结果“重复”，提示负责安排监考的人员重新安排。

内容仅供参考