1、第七章 Excel2003高级应用 -班级成绩分析表,计算机办公应用实务,案例描述,成绩分析是评价教学效果的重要手段。通过成绩分析可以对每位学生在校的相对发展情况进行实时跟踪，对各门课程的教学绩效实施诊断性评价，为教学行政管理及教学改革提供可靠依据。期末考试结束后，各门课程的总评成绩都已出来了，班主任老师要求学习委员张文萍同学对本班成绩进行分析。,张文萍从班主任老师那里拿到各门课程成绩，她先打草稿，初步计划一下成绩分析表应该有哪些原始数据。通过分析，获得成绩分析表表头，顺序确定为下列内容： 学号，姓名，毛、邓、三个代表，英语，数学，web应用开发，软件项目的开发与管理，操作系统，数据库开发，总
2、分，班级排名。 同时，成绩分析表中要体现出每位同学的成绩总分、班级排名和各门课程的平均分，不及格人数，能将不及格的成绩进行区分，并对各门课程不及格人数进行图表分析。成绩分析表完成后对数据要进行保护。 借助于Excel在公式、函数、筛选、汇总、保护和图表等方面强大的数据处理功能，张文萍同学才能圆满完成班主任老师交代的工作。,解决方案,第七章 Excel2003高级应用-班级成绩分析表,计算机办公应用实务,任务分解,成绩分析表中总分的实现,2,成绩分析表中不及格数据的区分,成绩分析表中班级排名的实现,成绩分析表原始数据的建立,各门课程平均分和不及格人数的实现,各门课程不及格人数图表的实现,成绩分析
3、表的数据保护的实现,4,6,计算机办公应用实务,7.1成绩分析表初步设计,能力目标,知识目标,进一步掌握建立电子原始数据表的一般步骤。,具有分析原始数据、完成电子数据表的制作能力。,任务一 成绩分析表原始数据的建立,任务要求,应用第五章Excel 2003基础知识所学内容实现成绩分析表的原始数据的建立。,计算机办公应用实务,7.1成绩分析表初步设计,操作步骤,1新建工作簿并进行保存。文件名为：成绩分析表.xls。将“Sheet1”工作表的名称更改为“成绩分析表效果图”工作表。 2工作表页面设置。,计算机办公应用实务,7.1成绩分析表初步设计,操作步骤,3制作标题和表头。 （1）将标题和表头的数
4、据输入到相应的单元格中。 （2）鼠标右键单击工作表中行号“1”，在弹出的下拉菜单中选择“行高”命令。弹出“行高”对话框。设置“行高”文本框为“40”。如图所示。单击“确定”按钮返回表格编辑区。,计算机办公应用实务,7.1成绩分析表初步设计,操作步骤,4输入表格数据 将表格中的数据信息依次输入到相应单元格。 5设置表格格式 为了使成绩分析表效果图更加美观、实用，接下来将对表格数据格式进行调整。 6最后可以根据需要设置表格的页眉和页脚。 7单击“保存”按钮。,计算机办公应用实务,7.2成绩分析表中总分的实现,能力目标,知识目标,掌握Excel工作表中公式的使用。,能够熟练应用公式有效解决Excel
5、工作表的数据处理问题。,任务二 成绩分析表中总分的实现,任务要求,应用Excel中公式计算出成绩分析表效果图中总分栏数据。,计算机办公应用实务,7.2成绩分析表中总分的实现,操作步骤,1鼠标左键单击J3单元格，即第一位同学的总分数据单元格。 2在J3单元格中输入“=C3+D3+E3+F3+G3+H3+I3” 。,3按“Enter”键，结果如图所示。,计算机办公应用实务,7.2成绩分析表中总分的实现,操作步骤,4将鼠标移到J3单元格右下角，利用Excel自动填充的方法，计算其他同学的总分成绩。如左图所示。填充结果如右图所示。,5单击“保存”按钮。,计算机办公应用实务,7.3成绩分析表中班级排名的
6、实现,能力目标,知识目标,掌握Excel中排序功能的一般使用方法。,能够熟练利用排序解决一些特殊的数据处理要求。,任务三 成绩分析表中班级排名的实现,任务要求,能按照总分按由高到低的顺序进行班级排名，并注明每一位学生在班级中成绩名次。,计算机办公应用实务,7.3成绩分析表中班级排名的实现,操作步骤,1由于有25位同学参加班级排名，选择A3：J27单元格区域，单击“数据”菜单下的“排序”命令。 2希望总分按由高到低的顺序，那么在弹出的“排序”对话框中，“主要关键字”选择“总分”选项，并选择“降序”按钮。 3单击“确定”按钮。,计算机办公应用实务,7.3成绩分析表中班级排名的实现,操作步骤,4接着
7、给每位同学排名，在K3单元格中输入“1”（表示排名为第一的同学），同时在K4单元格中输入“2”，选择K3：K4区域，如图所示。将鼠标移到K4单元格右下角，利用Excel自动填充的方法，输出每位同学的班级排名。如图所示。,计算机办公应用实务,7.3成绩分析表中班级排名的实现,操作步骤,5班级排名基本完成，若希望表格中的数据仍按“学号”从小到大排列。那么通过上述方法，在弹出的“排序”对话框中进行设置。 6排序后最终效果如图所示。 7.单击“保存”按钮。,计算机办公应用实务,7.4成绩分析表中不及格数据显著标记的实现,能力目标,知识目标,掌握Excel中数据筛选的一般方法。,能够熟练利用筛选功能解决
8、Excel工作表的数据处理问题。,任务四 成绩分析表中不及格数据显著标记的实现,任务要求,在成绩分析表效果图中要求将低于60分的成绩用红色字体进行区分。,计算机办公应用实务,7.4成绩分析表中不及格数据显著标记的实现,操作步骤,1选择A2单元格，一般只要选择表头部分的任一单元格。 2接着选择“数据”菜单中“筛选”子菜单下的“自动筛选”命令。 3自动筛选后，在表头的每个单元格右下角出现筛选标记。,计算机办公应用实务,7.4成绩分析表中不及格数据显著标记的实现,操作步骤,4选择C2单元格的筛选标记，弹出下拉菜单。 5选择“自定义”命令，弹出“自定义自动筛选方式”对话框。选择“小于”命令，并输入“6
9、0”。,计算机办公应用实务,7.4成绩分析表中不及格数据显著标记的实现,操作步骤,6单击“确定”按钮。即显示出“毛、邓、三个代表”这一门课程中不及格的数据。 7将低于“60”分的数据通过“格式”工具栏的“字体颜色来命令，设置成“红色”，并进行“加粗“显示。效果如图所示。,计算机办公应用实务,7.4成绩分析表中不及格数据显著标记的实现,操作步骤,8继续选择C2单元格的筛选标记，弹出下拉菜单。选择“全部”命令，接着成绩表又被全部展开。 9同理，根据上述步骤依次对其他六门课程中不及格的数据进行筛选。并分别用红色并加粗显示出来。 10通过筛选的方法，最后的效果如图所示。 11单击“保存”按钮。,计算机
10、办公应用实务,7.5各门课程平均分和不及格人数的实现,能力目标,知识目标,熟练掌握Excel工作表中常用函数的使用方法。,能够熟练利用Excel函数快速解决数据计算和统计问题。,任务五 各门课程平均分和不及格人数的实现,任务要求,使用Excel中函数进行各门课程平均分计算和不及格人数的统计。,计算机办公应用实务,7.5各门课程平均分和不及格人数的实现,操作步骤,1利用AVERAGE函数计算各门课程的平均分 （1）选择A28单元格，输入信息“平均分计算”。 （2）选择C28单元格，单击常用工具栏的自动求和的向下箭头按钮。选择“平均值”命令。 （3）在编辑栏中输入“=AVERAGE(C3:C27)
11、”，按“Enter”键。即计算出“毛、邓、三个代表”课程的平均成绩。 （4）将鼠标移到C28单元格右下角，利用Excel自动填充的方法，向右拖动，输出其他课程的平均分。 （5）单击“保存”按钮。,计算机办公应用实务,7.5各门课程平均分和不及格人数的实现,操作步骤,2利用COUNTIF函数统计各门课程不及格人数 （1）选择A29单元格，输入信息“不及格人数”。 （2）选择C29单元格，单击常用工具栏的自动求和的向下箭头按钮。选择“其他函数”命令，打开“插入函数”对话框。 （3）在“插入函数”对话框中，设置“选择类别”下拉列表框为“统计”，在“选择函数”列表框中选择“COUNTIF”命令，插入相
12、应函数。 （4）单击“确定”按钮，弹出“函数参数”对话框，设置“COUNTIF”中“Range”引用区域为C3：C27，也可以单击右侧的选择范围按钮来选择C3至C27所有单元格；设置“Criteria”文本框为“”60”。,计算机办公应用实务,7.5各门课程平均分和不及格人数的实现,操作步骤,（5）单击“确定”按钮，在C29单元格中自动统计出“毛、邓、三个代表”列所有成绩小于60分的记录数。 （6）选中C29单元格，将鼠标移到C29单元格右下角，利用快速序列填充的方法，拖动鼠标至I29单元格。 （7）各门课程不及格人数效果如图所示。 （8）单击“保存”按钮。,计算机办公应用实务,7.6各门课程
13、不及格人数图表的实现,能力目标,知识目标,熟练掌握Excel工作表中图表创建的一般步骤。,能够熟练利用图表向导工具解决图表创建的一般问题，并能够解决图表中格式设置问题。,任务六 各门课程不及格人数图表的实现,任务要求,利用Excel图表功能，直观地显示各门课程不及格人数的分布。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,1创建图表 （1）打开“成绩分析表.xls”工作簿，将光标定位在成绩分析表效果图的数据区内。 （2）选择“插入”菜单下的“图表”命令。打开“图表向导”对话框。 （3）“图表向导”对话框“步骤之1”中，在“标准类型”选项卡中，图表类型下的列表框里选择“柱形
14、图”，子图表类型选择“簇状柱形图”，如图所示。单击“下一步”按钮。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,（4）“图表向导”对话框进入“步骤之2”，可以看到图表中的数据很混乱，如图所示。在这里要重新做一下修改。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,（5）“图表向导”对话框进入“步骤之3”，在这个对话框里一共包含了六个选项卡，依次进行以下设置： “标题”选项卡：在“图表标题”下的文本框中输入“08程序331班各科不及格人数分析表”；在“分类（X）轴”下的文本框中输入“课程名称”；在“分类（Y）轴”下的文本框中输入“人数”，如图所示。,计算
15、机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,“坐标轴”选项卡：默认设置，如图所示。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,“网格线”选项卡：默认设置，如图所示。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,“图例”选项卡：将“显示图例”复选框左边的勾取消。如图所示。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,“数据标志”选项卡：选择“数据标签包括”中“值”复选框。如图所示。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,“数据表”选项卡：默认设置，如图所示。,计算机办公应用实务,7.
16、6各门课程不及格人数图表的实现,操作步骤,（6）单击“下一步”，“图表向导”对话框进入“步骤之4”，选择“作为其中的对象插入”选项。 （7）单击“完成”按钮。在“成绩分析表效果图”工作表内插入了一张图表。 （8）单击“保存”按钮。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,2图表修饰 根据“图表向导”完成的图表，比较简单，容易操作。但有些时候还有能达到实际操作的需要。 （1）格式设置 选择图表中“人数”，鼠标右键单击，在弹出的菜单中选择“坐标轴标题格式”命令。 弹出“坐标轴标题格式”对话框，选择“字体”选项卡，将字号设置为：10。 选择“对齐”选项卡，将“方向”设置为：
17、0度。 单击“确定”。效果如图所示。,计算机办公应用实务,7.6各门课程不及格人数图表的实现,操作步骤,（2）图表类型 鼠标右键单击图表中的空白区域，在弹出的菜单中选择“图表类型”命令。 在弹出的“图表类型”对话框中选择“标准类型”选项卡，在“图表类型”下的列表框里选择“折线图”，子图表类型选择“数据点折线图”。 单击“确定”。效果如图所示。,计算机办公应用实务,7.7成绩分析表的数据保护的实现,能力目标,知识目标,掌握保护工作簿和保护工作表的一般步骤。,能够根据工作需要，分析数据的重要性，对工作簿和工作表进行保护，当然也能取消保护。,任务七 成绩分析表的数据保护的实现,任务要求,对已完成的学
18、生成绩分析表，为防止他人对数据进行创建和删除等操作，进行工作部和工作表的二级保护。,计算机办公应用实务,7.7成绩分析表的数据保护的实现,操作步骤,1保护工作簿 （1）打开“成绩分析表.xls”工作簿，选择“工具”菜单下“保护”子菜单中“保存工作表”命令。如图所示。 （2）弹出“保护工作簿”对话框，选中“结构”复选框，选中“窗口”复选框，在“密码”文本框中输入密码，单击“确定”按钮。（工作簿保护密码设为：123456） （3）弹出“确认密码”对话框，在“重新输入密码”文本框中重新输入密码，单击“确定”按钮。工作簿的保护设置完成。,计算机办公应用实务,7.7成绩分析表的数据保护的实现,操作步骤,
19、2保护工作表 “成绩分析表效果图”工作表制作完成后，为了防止他人修改工作表中的数据内容，可以为工作表设置密码，从而保护工作表。 （1）打开“成绩分析表.xls”中的“成绩分析表效果图”工作表，选择“工具”菜单下“保护”子菜单中“保存工作表”命令。 （2）弹出“保护工作表”对话框，选中“保护工作表及锁定的单元格内容”复选框，在“取消工作表保护时使用的密码”文本框中输入密码，单击“确定”按钮，（工作表保护密码设为：123456）。 （3）弹出“确认密码”对话框，在“重新输入密码”文本框中再次输入密码，单击“确定”按钮，完成保护工作表的操作。,计算机办公应用实务,能力训练,1工资表的编排与制作 工资
20、表的编排与制作需要根据各行业、各公司的特点，由公司财务部门按照自己公司员工收入的构成来设计与制作。在工资表设计的时候通常都会将所有发放的条目放在前面，所有需要扣除的条目放在后面。下面请根据提示完成工资表的制作。 题目要求： （1）“全勤奖金” 50元月。 （2）迟到或早退除扣除“全勤奖金”外，另扣50元次。 （3）各部门岗位工资分别为：财务部：600元；销售部：1000元；供应部：700元；生产部：800元；设计部：700元。 （4）“住房补助”、“车费补助”分别为“基本工资”的20%、5%。 （5）“养老保险”、“医疗保险”、“失业保险”分别为“基本工资”的7%、2%、1%。,计算机办公应用
21、实务,能力训练,工资表最终效果如图所示。,计算机办公应用实务,能力训练,2产品销售表中高级筛选功能的实现 在Excel中，数据的筛选包括自动筛选和高级筛选两种，它们有各自不同有应用范围。高级筛选功能可以更灵活地筛选信息，可能任意组合查询条件，弥补了自动筛选的不足。 题目要求：在下面的“产品销售表”数据表中，要求筛选出“跑鞋”销售量大于25，并且“休闲鞋”销售量小于18的所有记录。,计算机办公应用实务,能力训练,3产品销售表中分类汇总功能的实现 在数据的分析处理中，分类汇总也是一个经常使用的功能，它可以对表格中不同逻辑的数据进行归类，并提供进一步的总结和统计等操作。Excel的这一功能在工作表的
22、分析中有着十分重要的作用，分类汇总的操作不但增加了表格的可读性，并且能更方便快捷地获得需要的数据。 题目要求：在下面的“产品销售表”数据表中，利用分类汇总功能计算出每种商品的销售总数量和每种商品销售的总金额。,计算机办公应用实务,能力训练,4销售量分析表中数据透视表的实现 数据透视表是交互式报表，可快速合并和比较大量数据。你可以旋转其行和列，看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。数据透视表是一种动态工作表，它提供了一种以不同要求查看数据清单的灵活方法。 题目要求：根据下面的“第一季度销售量表”，制作数据透视表。请按照产品的型号和产地查询季度销售总量。销售量分析表数据透视表效果如图所示。,谢 谢!,
