Excel在学校中的应用42

1、Excel 在学校中的应用 42- 教师情况统计表（二）5.10 教师情况统计表（二） 案例背景学校人事干部和相关管理人员平时就要注意将本校全 体教师的各种信息完整的记录下来，并以数据库的形式加以 保存并不断更新，这样一来既可以使自己对全校教师的整体 情况了如指掌，又可以根据各级领导的要求随时汇总出所需 报表，如果给您的报表再配加一幅数据图表，则会使得您的 报表更加出彩。本例就在 5.9 节的基础上继续应用 Excel 的 “数据透视表和数据透视图”来制作各类不同的统计表和精美 的图表。关键技术点要实现本案例中的功能，学员应该掌握以下 EXCEL 技 术点。基础知识 选择性粘贴， 负数显示为正

2、数的自定义格式图表 条形图 图表的编辑综述 数据透视表和数据透视图最终效果展示 创建教师基础数据表Step 1 新建、保存工作簿，重命名工作表 新建一工作簿，并保存为“教师统计表 2.xls ”，将“ Sheet1 ”和“ Sheet2 ”重名为“数据表”和“报表”，删除多余工作表tep 2复制工作表打开 5.8 节中“人事管理 .xls ”工作簿，在“人事表”中选中单元 格区域 A1 ： N58 ，按组合键复制。Step 3 输入“教师情况统计表”原始信息单击“教师统计表 .xls ”工作表的“数据表”工作表标签，按组合 键粘贴。 创建教师情况统计表Step 1 创建基础统计报表参照 的操作

3、过成，只是在“布局“对话框中将“学科”换成“年龄”，生成 教师年龄性别统计表。 Step 2 按“年龄段”统计汇总 单击“年龄”字段的任意单元格，单击右键，在弹出的快捷菜单中“组及显示明细数据“T” “组合“命令，打开”组合”对话框。 在弹出的”组合”对话框的“步长”选项框中数“5”。然后单击“确定”按钮即可得到按年龄段分组的汇总统计表。选中 单元格区域 A4:C12 ，按组合键复制，切换到“报表”工作表，选中 A2 单元格按组合键粘贴。Step 3 创建数据透视图 单击“数据透视表“工具栏中的图标向导按钮，自动插入一幅 基本的数据透视图图表“ Chart1 ”Step 4 改变数据透视图位置

4、右键单击图表空白处，在弹出下拉列表中选择“位置”，在弹 出“图表位置”对话框中选择将图表“作为其中的对象插入”选 项，单击其右侧的下箭头，在弹出的列表框中选择“ Sheet1 ” 工作表，最后单击“确定”按钮。 Step 5 隐藏字段按钮 右键单击图表中的字段按钮， 在弹出的下拉列表中选择“隐藏 数据透视图字段按钮“， 即可将图表中的字段按钮隐藏， 从而 扩大图表区可视区域。 Step 6 添加行字段 Step 7 从“数据透视表字段列表”框中将“学科”拖到“行字段”区域即 可得到教师学科年龄 -性别统计汇总表， 当然这时的图表的效 果就差多了。 绘制教师年龄性别分布条形图 如图 8 所示的教

5、师年龄分布图将男女教师的数据绘制成柱形 图，看起来不太直观。我们可以利用数据透视表的数据制作 一个条形图，其左边是男教师的人数条形，右边是女教师的 人数条形，这样看起来全校的教师年龄结构就一目了然了。Step1 改变男教师人数的数值为负数 单击工作表标签“报表”，在任一空单元格如 F3 输入“-1”，按 组合键复制，然后选中单元格区域B3 : B9，单击菜单“编辑”宀“选择性粘贴”，在弹出的“选择性粘贴”对话框的“运算”选 项组中选择“乘”然后单击“确定”按钮即可将男教师人数数据 区的数值转换成负数。 Step2 为男教师人数数据区设置自定 义格式 选中单元格区域 B3 : B9，按快捷键弹出

6、“单元格格式”对话 框，切换到数字选项卡， 在“分类”列表框中选择“自定义”选项， 在“类型”文本框中输入” 0;0; ” , 然后单击“确定”按钮即可将单元格区域 B3：B9 的负数以正数形式显示（如果是0 则不显示），但是单元格区域内的数字仍然是负数（在编辑栏中 可见单元格B3的值为-3 “）Step3插入图表 单击菜单“插入“t”图表“弹出图表向导V步骤之1-图表类 型”对话框，在选项卡“标准类型“中的”图表类型“选项框中选 择”条形图“，在”子图表类型“选项框中选择”蔟状条形图“。 单击 “下一步“按钮弹出“图表向导4步骤之2-图表数据 源”对话框，单击“数据区域“右侧的按钮，用书选取

7、单元格区 域” $A$2 : $C$9 “。单击“下一步“按钮，弹出“图表向导 -4步骤之3-图表选项”对话框，在“标题“选项卡的”图表标题“框中输入”教师年龄一性别分布图“；在”分类X）轴“框中输 入”年龄段“在”数值（y）轴“框中输入”教师人数“单击 “下 一步“按钮，弹出“图表向导-4 步骤之 4-图表位置” 对话框， 在“将图表“选项框中选择”作为其中的对象插入“。单击”完 成“按钮即可初步得到图表 Step4 修饰美化统计表 选中单元格区域 A1:C1 单击常用工具栏“合并及居中“，输 入标题”教师年龄性别统计表“，设置字体加粗。 为表格设置边框，为字段区域设置底纹。编辑条形图图表S

8、tep1 设置图表标题格式 右键单击图表标题弹出“图表标题格式”对话框，切换到“字体” 选项卡在“字号”选项框中输入“9”，然后单击确定按Step2设置分类轴标题格式 右键单击分类轴标题弹出“坐标轴标题格式”对话框，切换到“字体”选项卡，在“字号”选项框中输入“9”。 再切换到“对齐”选项卡，在“度”选项框中输入“0”，然后单击“确定”按钮o Step3移动分类轴标题位置 左键单击选中分类轴标题框，将其直接拖动到图标左上角空 白处合适的位置。 Step4 清除数值轴标题和图例 分别用右键单击选中数值轴标题和图例，在弹出的列表框中选择清除。 Step5 设置坐标轴格式鼠标指向图表底部的数值轴，当

9、出现“数值轴”字样时右键 单击，在弹出的列表框中选择“坐标轴格式”，弹出“坐标轴格 式”对话框。切换到“刻度”选项卡，在“主要刻度单位”选项 框中输入“ 4”。切换到“字体”选项卡，在“字号”选项框中输 入“ 9”然，后单击确定按钮。 鼠标指向图表中间的分类轴 （纵 轴），当出现“分类轴”字样时右键单击，在弹出的列表框中选 择“坐标轴格式”，弹出“坐标轴格式”对话框。 在“图案”选项卡，的“刻度线标签”选项组中单击“图外”按钮。 然后切换到“字体”选项卡，在“字号”选项框中输入“9”，然后单击“确定“按钮。Step6设置数据系列格式鼠标指向图表中任意数据系列（任意年龄段中的人数条 形）,当出现

10、“系列引用”标志时，右键单击，在弹出的下拉列表中选择“数据系列格式”，弹出“数据系列格式”对话 框。单击图案选项卡中的“阴影”按钮，然后单击“填充效果”按钮， 弹出“填充效果”对话框，在“渐变”选项卡中选择“单色”，颜色 保留原来的紫色，“底纹样式”选择“水平并向右拖动“深（k） 浅”选项框中的滑动按钮至适当位置。单击确定按钮返回“数据系列格式”对话框，切换到“数据标志”选项卡，在“数据标签包括”选项组中勾选“值”选项。切换到“选项”选项卡， 在“重贴比例”选项框中输入“100” ；在“分类间隔”选项框中输入“ 0” .单击“确定“按钮成图表右侧女教师数据系列格式的设置。按照上面的操作过程完成

11、男教师的数据系列格式 的设置。 Step7 清除网格线鼠标指向图表中任意网格线，当出现“数值轴主要网格线”字 图区“大小样时右键单击，在弹出的列表框中选择“清除step! “绘鼠标单击图表中灰色区域选中“绘图区“，将鼠标移到需要调整的”绘图区“四周的任一方块上，待鼠标指针变成双向箭头时拖动，将绘图区调整到合适的大小。 step9 设置图表区格 式 鼠标右键单击图表区白色区域，在弹出的下拉列表中选择“图表区格式”，在弹出“图表区格式”对话框中的“图案”选项卡下单击“阴影”和“圆角”按钮，然后单击“填充效果”按钮”。弹出“填充效果”对话框，在“渐变”选项卡中选择“双色”在，“颜 色 1 ”选择浅黄

12、色颜色，在“颜色 2 ”中选择海绿色，在“底纹样式”选择“斜上”单击确定按钮返回“数据系列格式”对话框，然后切换到“字体”选项卡，在“字号”选项框中输入“ 9”，然后 再切换到“属性”选项卡，在“对象位置”选项组中单击“大小、 位置均固定”按钮。单击确定按钮，完成图表区格式的设置Step10 设置绘图区格式 鼠标右键单击绘图区灰色区域，在弹出的下拉列表中选择“绘图区格式”，在弹出的“绘图区格式”对话框中单击“填充效 果”按钮”， 弹出“填充效果”对话框，在“渐变”选项卡中选择“单色”，在“颜色1”选择浅青绿色，在“底纹样式”选择“水平”，然后单击 确定按钮，即可完成绘图区格式的设置。至此教师年

13、龄性别分布条形图图表的编辑工作全部完成，与基本图表图18 相比， 无论是图案、 色彩还是对比效果都已经有了很大的 改进，相信学习者通过模仿上述操作一定有了一点成就感了 吧，根据每个人对色彩的不同感觉，大家还能绘制出更加美 观的图表。单元 5 操作练习1. 以您任教的学科的全年级各班期中和期末考试成绩为例， 参照 5.1 节介绍的教师教学效果评价方法 ,从下面两种角度进 行模拟评价。1.1 按下面格式从考试名次变化评价教师的教学效果 1.2 按 下面格式从两次考试平均分的标准分差评价教师的教学效 果.2. 以本年级上学期的课程安排表为例按照下面的格式制作一 张课程安排表。3. 参照 5.3 节介

14、绍的教师“教学任务书”制作方法，以练习 2 的“课程安排表”为数据表，参照下面任务书的格式制作本年 级的教师“教学任务书”。4 按照 5.4 节案例介绍的制作监考安排表的方法，以本年级 教师为例，模拟安排监考表，具体监考安排规则为： 本年级每位教师至少监考 1 场； 每位教师监考场次不得超过 3 场； 通过设置条件格式将满足以下条件的教师所在单元格中设 置成下图样式；并设置重排检查功能。 条件 1 ：监考 4 次或重排，文字颜色“粉红”加粗加斜，背景“灰色”。条件 2 ：监考 3 次，文字颜色“蓝色”加粗。 条件 3 ：监考 2 次，文字颜色“绿色”。5. 参照 5.4 节案例介绍的制作“教师

15、监考场次和监考费统计表” 的方法，按下面统计表样式，以练习 4 的“监考安排表”为数 据表，使用 COUNTIF 函数制作“教师监考场次和监考费统计 表”。6. 参照下面成绩表的样式，以您任教的班级近期考试成绩为 例，进行高级筛选，具体高级筛选条件为：6.1 语文和外语成绩均不低于 90 或数学成绩不低于 85 ；6.2 物理成绩或化学成绩不及格；6.3 数学成绩高于全班数学平均分 *。7. 以练习 2“课程安排表”为数据表，使用高级筛选的“筛选不 重复记录”功能，筛选出一份本年级不重复的教师名单。8. 参照 5.8 节案例中的”人事管理表”的制作方法， 将本年级教 师的基本情况制作一张人事管理表,其中： “年龄”列区域的条件格式的公式改为：“ =0R(AND(C7=男,E7>=49),AND(C7=女,E7>=44) ”，满足条件的格式为：文字为宋体 14 号加粗加斜，海绿色背景。 “任现职称年限”列区域的条件格式的公式不变，满足条件