5.10 教师情况统计表（二）
案例背景
学校人事干部和相关管理人员平时就要注意将本校全体教师的各种信息完整的记录下来，并以数据库的形式加以保存并不断更新，这样一来既可以使自己对全校教师的整体情况了如指掌，又可以根据各级领导的要求随时汇总出所需报表，如果给您的报表再配加一幅数据图表，则会使得您的报表更加出彩。本例就在5.9节的基础上继续应用Excel的“数据透视表和数据透视图”来制作各类不同的统计表和精美的图表。
关键技术点
要实现本案例中的功能，学员应该掌握以下EXCEL技术点。
●基础知识 选择性粘贴，负数显示为正数的自定义格式
●图表 条形图 图表的编辑
●综述 数据透视表和数据透视图
最终效果展示
5.10.1创建教师基础数据表
Step 1新建、保存工作簿，重命名工作表
新建一工作簿，并保存为“教师统计表2.xls”，将“Sheet1”和“Sheet2”重名为“数据表”和“报表”，删除多余工作表。
Step 2复制工作表
打开5.8节中“人事管理.xls”工作簿，在“人事表”中选中单元格区域A1：N58，按组合键复制。
Step 3输入“教师情况统计表”原始信息
单击“教师统计表.xls”工作表的“数据表”工作表标签，按组合键粘贴。
5.10.2创建教师情况统计表
Step 1创建基础统计报表
参照5.9.2的操作过成，只是在“布局“对话框中将“学科”换成“年龄”，生成
教师年龄—性别统计表。
Step 2按“年龄段”统计汇总
①单击“年龄”字段的任意单元格，单击右键，在弹出的快捷菜单中“组及显示明细数据“→”“组合“命令，打开”组合”对话框。
②在弹出的”组合”对话框的“步长”选项框中数“5”。
③然后单击“确定”按钮即可得到按年龄段分组的汇总统计表。
④选中单元格区域A4:C12，按组合键复制，切换到“报表”工作表，选中A2单元格按组合键粘贴。
Step 3创建数据透视图
单击“数据透视表“工具栏中的图标向导按钮，自动插入一幅基本的数据透视图图表“Chart1”
Step 4改变数据透视图位置
右键单击图表空白处，在弹出下拉列表中选择“位置”，在弹出“图表位置”对话框中选择将图表“作为其中的对象插入”选项，单击其右侧的下箭头，在弹出的列表框中选择“Sheet1”工作表，最后单击“确定”按钮。
Step 5隐藏字段按钮
右键单击图表中的字段按钮，在弹出的下拉列表中选择“隐藏数据透视图字段按钮“，即可将图表中的字段按钮隐藏，从而扩大图表区可视区域。
Step 6添加行字段
Step 7
从“数据透视表字段列表”框中将“学科”拖到“行字段”区域即可得到教师学科年龄-性别统计汇总表，当然这时的图表的效果就差多了。
5.10.3绘制教师年龄—性别分布条形图
如图8所示的教师年龄分布图将男女教师的数据绘制成柱形图，看起来不太直观。我们可以利用数据透视表的数据制作一个条形图，其左边是男教师的人数条形，右边是女教师的人数条形，这样看起来全校的教师年龄结构就一目了然了。
Step1改变男教师人数的数值为负数
单击工作表标签“报表”，在任一空单元格如F3输入“-1”，按组合键复制，然后选中单元格区域B3：B9，单击菜单“编辑”→“选择性粘贴”，在弹出的“选择性粘贴”对话框的“运算”选项组中选择“乘”
然后单击“确定”按钮即可将男教师人数数据区的数值转换成负数。
Step2为男教师人数数据区设置自定义格式
①选中单元格区域B3：B9，按快捷键弹出“单元格格式”对话框，切换到数字选项卡，在“分类”列表框中选择“自定义”选项，在“类型”文本框中输入” 0;0;@”,
然后单击“确定”按钮即可将单元格区域B3：B9的负数以正数形式显示（如果是0则不显示），但是单元格区域内的数字仍然是负数（在编辑栏中可见单元格B3的值为“-3“）。
Step3插入图表
①单击菜单“插入“→”图表“弹出 “图表向导-4步骤之1-图表类型”对话框，在选项卡“标准类型“中的”图表类型“选项框中选择”条形图“，在”子图表类型 “选项框中选择”蔟状条形图“。
②单击 “下一步“按钮，弹出 “图表向导-4步骤之2-图表数据源”对话框，单击“数据区域“右侧的按钮，用书选取单元格区域” $A$2：$C$9 “。
③单击 “下一步“按钮，弹出 “图表向导-4步骤之3-图表选项” 对话框，在“标题“选项卡的”图表标题“框中输入”教师年龄—性别分布图“；在”分类（x）轴“框中输入”年龄段“；在”数值（y）轴“框中输入”教师人数“。
④单击 “下一步“按钮，弹出 “图表向导-4步骤之4-图表位置” 对话框，在“将图表“选项框中选择”作为其中的对象插入“。
⑤单击”完成“按钮即可初步得到图表
Step4修饰美化统计表
①选中单元格区域A1:C1单击常用工具栏“合并及居中“，输入标题”教师年龄—性别统计表“，设置字体加粗。
②为表格设置边框，为字段区域设置底纹。
5.10.4编辑条形图图表
Step1设置图表标题格式
右键单击图表标题弹出“图表标题格式”对话框，切换到“字体”选项卡
在“字号”选项框中输入“9”，然后单击确定按钮。
Step2设置分类轴标题格式
①右键单击分类轴标题弹出“坐标轴标题格式”对话框，切换到“字体”选项卡，在“字号”选项框中输入“9”。
②再切换到“对齐”选项卡，在“度”选项框中输入“0”，
③然后单击“确定”按钮。
Step3移动分类轴标题位置
左键单击选中分类轴标题框，将其直接拖动到图标左上角空白处合适的位置。
Step4清除数值轴标题和图例
分别用右键单击选中数值轴标题和图例，在弹出的列表框中选择清除。
Step5设置坐标轴格式
①鼠标指向图表底部的数值轴，当出现“数值轴”字样时右键单击，在弹出的列表框中选择“坐标轴格式”，弹出“坐标轴格式”对话框。
②切换到“刻度”选项卡，在“主要刻度单位”选项框中输入“4”。
③切换到“字体”选项卡，在“字号”选项框中输入“9”，然后单击确定按钮。
④鼠标指向图表中间的分类轴（纵轴），当出现“分类轴”字样时右键单击，在弹出的列表框中选择“坐标轴格式”，弹出“坐标轴格式”对话框。
⑤在“图案”选项卡，的“刻度线标签”选项组中单击“图外”按钮。
⑥然后切换到“字体”选项卡，在“字号”选项框中输入“9”，然后单击“确定“按钮。
Step6设置数据系列格式
①鼠标指向图表中任意数据系列（任意年龄段中的人数条形），
当出现“系列……引用……”标志时，右键单击，在弹出的下拉列表中选择“数据系列格式”，弹出“数据系列格式”对话框。
②单击图案选项卡中的“阴影”按钮，然后单击“填充效果”按钮，弹出“填充效果”对话框，在“渐变”选项卡中选择“单色”，颜色保留原来的紫色，“底纹样式”选择“水平”，并向右拖动“深（k） 浅”选项框中的滑动按钮至适当位置。
③单击确定按钮返回“数据系列格式”对话框，切换到“数据标志”选项卡，在“数据标签包括”选项组中勾选“值”选项。
④切换到“选项”选项卡，在“重贴比例”选项框中输入“100”；在“分类间隔”选项框中输入“0”.
⑤单击“确定“按钮，完成图表右侧女教师数据系列格式的设置。
⑥按照上面的操作过程完成男教师的数据系列格式的设置。
Step7清除网格线
鼠标指向图表中任意网格线，当出现“数值轴主要网格线”字样时右键单击，在弹出的列表框中选择“清除“
Step8调整“绘图区“大小
鼠标单击图表中灰色区域选中“绘图区“，将鼠标移到需要调整的”绘图区“四周的任一方块上，待鼠标指针变成双向箭头时拖动，将绘图区调整到合适的大小。
Step9设置图表区格式
①鼠标右键单击图表区白色区域，在弹出的下拉列表中选择“图表区格式”，在弹出“图表区格式”对话框中的“图案”选项卡下单击“阴影”和“圆角”按钮，然后单击 “填充效果”按钮”。
②弹出“填充效果”对话框，在“渐变”选项卡中选择“双色”，在“颜色1”选择浅黄色颜色，在“颜色2”中选择海绿色，在“底纹样式”选择“斜上”。
③单击确定按钮返回“数据系列格式”对话框，然后切换到“字体”选项卡，在“字号”选项框中输入“9”，然后再切换到“属性”选项卡，在“对象位置”选项组中单击“大小、位置均固定”按钮。
④单击确定按钮，完成图表区格式的设置
Step10设置绘图区格式
①鼠标右键单击绘图区灰色区域，在弹出的下拉列表中选择“绘图区格式”，在弹出的“绘图区格式”对话框中单击“填充效果”按钮”，
②弹出“填充效果”对话框，在“渐变”选项卡中选择“单色”，在“颜色1”选择浅青绿色，在“底纹样式”选择“水平”，然后单击确定按钮，即可完成绘图区格式的设置。
至此教师年龄—性别分布条形图图表的编辑工作全部完成，与基本图表图18相比，无论是图案、色彩还是对比效果都已经有了很大的改进，相信学习者通过模仿上述操作一定有了一点成就感了吧，根据每个人对色彩的不同感觉，大家还能绘制出更加美观的图表。
单元5操作练习
1.以您任教的学科的全年级各班期中和期末考试成绩为例，参照5.1节介绍的教师教学效果评价方法,从下面两种角度进行模拟评价。
1.1按下面格式从考试名次变化评价教师的教学效果
1.2按下面格式从两次考试平均分的标准分差评价教师的教学效果.
2.以本年级上学期的课程安排表为例按照下面的格式制作一张课程安排表。
3.参照5.3节介绍的教师“教学任务书”制作方法，以练习2的“课程安排表”为数据表，参照下面任务书的格式制作本年级的教师“教学任务书”。
4按照5.4节案例介绍的制作监考安排表的方法，以本年级教师为例，模拟安排监考表，具体监考安排规则为：
本年级每位教师至少监考1场；
每位教师监考场次不得超过3场；
通过设置条件格式将满足以下条件的教师所在单元格中设置成下图样式；并设置重排检查功能。 ①条件1：监考4次或重排，文字颜色“粉红”加粗加斜，背景“灰色”。
②条件2：监考3次，文字颜色“蓝色”加粗。
③条件3：监考2次，文字颜色“绿色”。
5.参照5.4节案例介绍的制作“教师监考场次和监考费统计表”的方法，按下面统计表样式，以练习4的“监考安排表”为数据表，使用COUNTIF函数制作“教师监考场次和监考费统计表”。
6.参照下面成绩表的样式，以您任教的班级近期考试成绩为例，进行高级筛选，具体高级筛选条件为：
6.1语文和外语成绩均不低于90或数学成绩不低于85；
6.2物理成绩或化学成绩不及格；
6.3数学成绩高于全班数学平均分*。
7.以练习2“课程安排表”为数据表，使用高级筛选的“筛选不重复记录”功能，筛选出一份本年级不重复的教师名单。
8.参照5.8节案例中的”人事管理表”的制作方法，将本年级教师的基本情况制作一张人事管理表,其中：
① “年龄”列区域的条件格式的公式改为：
“=OR(AND(C7="男",E7>=49),AND(C7="女",E7>=44))”，满足条件的格式为：
文字为宋体14号加粗加斜，海绿色背景。
② “任现职称年限”列区域的条件格式的公式不变，满足条件的格式为：文字为宋体14号加粗，淡紫色背景。
③在“奖项”列插入“批注”并编辑“批注”。
9.以练习8的“人事管理表”为数据表，创建数据透视表统计本年级教师情况的情况。
9.1学历—性别统计表，数据透视图
9.2年龄—学科统计表，数据透视图
9.3职称—学历统计表，数据透视图
9.4学科、年龄—性别统计表
10.参照5.10节案例中，“教师年龄性别分布图”的制作方法，按照下表、下图样式，以练习8的“人事管理表”为数据表，制作一张 “本年级教师年龄性别分布图”。
全部完。
|
