1、实验任务书712实验7 Excel的基本操作【实验目的】1. 掌握启动和退出Excel的操作方法。2. 熟悉Excel的工作界面，理解工作簿、工作表和单元格的概念。3. 熟练掌握Excel工作簿的建立和保存方法。4. 熟练掌握Excel工作表的编辑操作。5. 熟练掌握格式化工作表的操作。【实验环境】Windows XP+Office2003/2007【实验内容】任务一：制作学生成绩统计表。任务二：制作产品月销售统计表。【实验步骤】任务一：制作学生成绩统计表。建立学生成绩统计表，并对表格进行格式化。任务一学生成绩统计表制作效果如图2所示。 图1 “学生成绩统计表”原始数据 图2 任务一效果图1.
2、 新建一个工作簿文件，将当前的临时工作簿文件保存在D:EXCEL文件夹中，命名为“学生成绩统计表.xls。2. 在Sheetl工作表中输入图1所示内容。3. 将Sheetl中的表格内容复制到Sheet2相同的区域中，将工作表Sheet2重新命名为“学生成绩表”，并在该工作表中完成下列操作。4. 设置单元格的对齐方式及合并单元格将标题行合并居中（水平居中、垂直居中），将2-16行数据居中。5. 设置字体格式将标题字体设置为隶书、加粗、深蓝色、20号且加下划线，其他的文字字体设置为宋体、常规、12号。6. 设置数字格式将“数学”、“英语”和“计算机”列数据保留1位小数。7. 设置边框、背景图案为学
3、生成绩统计表添加边框：深红色，外框粗线、内框细线。为标题行加背景：浅青色；列标头加背景：象牙色。8. 设置单元格的行高和列宽将标题栏的行高设置为30，数据部分的行高设置为18，将“学号”列宽设置为10，其余列的列宽设置为8。9. 设置条件格式对学生成绩统计表中数学、英语、计算机成绩大于或等于90分的数据用蓝色、加双下划线来标注，低于60元的数据用红色、加粗倾斜来标注。10. 插入批注1 对学生成绩统计表中李凯的姓名列加一个批注“班长”。2 石慧娟的姓名列加一个批注“学习委员”。11. 保存工作簿。任务二：制作产品月销售统计表。建立产品月销售统计表，并对表格进行格式化。任务二产品月销售统计表制作
4、效果如图4所示。 图3 产品月销售统计表原始数据 图4 任务二效果图1. 新建一个工作簿文件，将当前的临时工作簿文件保存在D:EXCEL文件夹中，命名为“产品月销售统计表.xls。2. 在Sheetl工作表中输入图3所示内容。3. 将Sheetl中的表格内容复制到Sheet2相同的区域中，将工作表Sheet2重新命名为“产品月销售统计表”，并在该工作表中完成下列操作。4. 在第二行前插入一行，输入当前日期（当前日期输入方法为），并将当前日期数据设置为“*年*月*日”的格式。5. 设置单元格的对齐方式及合并单元格将标题行跨列垂直和水平居中，第2行（当前日期）右对齐，其余行数据居中。6. 设置字体
5、格式1 将标题字体设置为黑体、加粗、深红色、18号。2 其他的文字字体设置为宋体、常规、12号。7. 设置单元格的行高和列宽1 将标题栏的行高设置为35，数据部分的行高设置为20。2 将“名称”、“单价”列宽设置为8，其余列设置为最合适的列宽。8. 保存工作簿。【实验总结】实验8 工作表的编辑和美化【实验目的】1. 熟练掌握Excel工作表的编辑操作。2. 熟练掌握格式化工作表的操作。【实验环境】Windows XP+Office2003/2007【实验内容】任务一：制作员工年度绩效考核表。任务二：制作人事变动申请单。任务三：制作员工值班安排表。【实验步骤】任务一 ：制作员工年度绩效考核表。建
6、立员工年度绩效考核表，并对表格进行格式化。任务一员工年度绩效考核表最终效果如图2所示。图1 员工年度绩效考核表原始数据图2 任务一效果图1. 新建一个工作簿文件，将当前的临时工作簿文件保存在D:EXCEL文件夹中，命名为“员工年度绩效考核表.xls。2. 在Sheetl工作表中输入图5所示内容。其中： C3单元格内容为：工作计划考评(50分)D3单元格内容为：规章制度执行考评(30分)E3单元格内容为：出勤情况考评(10分)F3单元格内容为：团队与协作考评(10分)G3单元格内容为：年度绩效总分3. 将Sheetl中的表格内容复制到Sheet2相同的区域中，将工作表Sheet2重新命名为“员工
7、年度绩效考核表”，并在该工作表中完成下列操作。4. 设置单元格的对齐方式及合并单元格参照图2所示设置单元格的对齐方式及合并单元格。5. 设置字体格式将标题字体设置为宋体、加粗、黑色、18号，其他的文字字体设置为宋体、常规、12号。6. 设置边框、背景图案1 为员工年度绩效考核表添加边框：外框双细线、内框单细线。2 第1行标题：茶色点状底纹，第2-3行文字（班级、姓名）：黄色底纹，其他各行文字：浅绿色底纹。7. 设置单元格的行高和列宽1 将数据列宽设置为8。2 将标题栏的行高设置为30，数据部分的行高设置最合适的行高。8. 保存工作簿。任务二 ：制作人事变动申请单。根据图3所示效果制作人事变动申
8、请单。（制作过程参考课本P102页实例5.1。）任务三 ：制作员工值班安排表。根据图4所示效果制作人事变动申请单。（制作过程参考课本P120页实例6.1。）图3 人事变动申请单 效果图图4 员工值班安排表 效果图【实验总结】实验9 Excel的数据处理（一）【实验目的】1. 熟练掌握Excel工作表的编辑操作。2. 学习公式与函数的使用。3. 掌握图表的制作方法和图表的格式化操作。【实验环境】Windows XP+Office2003/2007【实验内容】任务一 ：学生成绩统计表的计算。任务二：产品月销售统计表的计算。任务三 ：员工年度绩效考核表的计算。【实验步骤】任务一 ：学生成绩统计表的计
9、算。建立“学生成绩统计表.xls”，并对表格按要求进行计算，结果如图1所示。图1 学生成绩统计表的计算结果1. 添加“总分”和“总评”列，增加“平均分”、“最高分”、“最低分”、“总人数”以及“不及格人数”行，如图1所示。2. 计算“总分”、“平均分”列数据。1 总分= 数学 + 英语 + 计算机2 平均分=( 数学 + 英语 + 计算机)33. 分别计算“数学”、“英语”、“计算机”三门课的“平均分”、“最高分”、“最低分”、“总人数”以及“不及格人数”。4. 计算“总评”列数据。1 总评：平均分=90，优秀；平均分=80，良好；平均分=60，及格；否则，不及格。5. 在“学生成绩统计表”中
10、，对前5个学生制作如图2所示的“成绩图表”，并设置图表标题格式为黑体、加粗、18号，设置绘图区图案格式为双色（橙色和浅黄色）底纹格式，移动图表位置和调整图表大小到合适的位置。 图2 学生成绩统计表的图表样式6. 保存工作簿。任务二：产品月销售统计表的计算。建立“产品月销售统计表.xls”，并对表格按要求进行计算，结果如图3所示。图3 产品月销售统计表的计算结果要求：1. 计算“产品月销售统计表”中“销售额”、“利润”列数据，将计算出的单元格数据设置为“￥”货币格式。1 销售额= 单价销售数量2 利润= 销售额利润率（存放在E11单元格）2. 计算“合计”行数据。3. 计算“占总销售额比例”列数
11、据，将计算出的单元格数据设置为百分比格式。v 占总销售额比例= 销售额合计4. 设置单元格格式，对该表格使用自动套用格式中的“彩色2”。5. 对“产品月销售统计表”中数据制作如图4所示的“产品销售额占总销售额比例”图表，并设置图表标题格式为宋体、加粗、14号，设置图表区图案格式为“填充效果”中“纹理”中的“新闻纸”，移动图表位置和调整图表大小到合适的位置。图4 “产品销售额占总销售额比例”图表样式6. 保存工作簿。任务三 ：员工年度绩效考核表的计算。建立“员工年度绩效考核表.xls”，并对表格按要求进行计算，结果如图5所示。图5 员工年度绩效考核表的计算结果1. 在“员工年度绩效考核表”中，利
12、用SUM函数计算“”列数据。2. 利用RANK函数，根据“年度绩效总分”成绩计算“本人排名”。3. 保存工作簿。【实验总结】【操作提示】1. 学生成绩统计表的计算1 插入“总分”和“总评”列：执行【插入】【行】命令。2 计算“总分”、“平均分”列数据：单击“常用”工具栏中的“自动求和”按钮完成或采用输入公式的方式。请尝试这两种方法。公式复制可采用向下拖曳填充柄的方式进行。3 “数学”、“英语”、“计算机”三门课的“平均分”、“最高分”、“最低分”、“总人数”以及“不及格人数”的计算可采用：单击“常用”工具栏中的“自动求和”按钮完成或采用插入函数的方式。函数如下：v 数学“平均分”，D19单元格
13、中输入“=AVERAGE(D3:D17)”v 数学“最高分”，D20单元格中输入“=MAX(D3:D17)”v 数学“最低分”，D21单元格中输入“=MIN(D3:D17)”v 数学“总人数”，D22单元格中输入“=COUNT(D3:D17)”v 数学“不及格人数”，D23单元格中输入“=COUNTIF(D3:D17,=90,优秀,IF(H3=80,良好,IF(H3=60,及格,不及格)”v 然后采用公式复制的方法得到其他学生的“总评”列数据。5 制作图表，选择前5个学生的“姓名”、“数学”、“英语”、“计算机”列数据，执行【插入】【图表】命令或单击工具栏中的图表向导按钮，按照向导步骤指引制作
14、族状柱形图表，对图表标题以及绘图区单击鼠标右键设置格式。2. 产品月销售统计表的计算1 计算“产品月销售统计表”中“销售额”，使用相对地址引用，D4单元格中输入“=B4*C4”，然后采用公式复制的方法得到下方数据。v “利润”列数据计算应使用绝对地址引用，E4单元格中输入“=D4/$D$8”，然后采用公式复制的方法得到下方数据。v 将计算出的单元格数据设置为“￥”货币格式的方法：选择数据区域，执行【格式】【单元格】命令，选择“数字”标签，进行相应设置。2 计算“合计”行数据：单击“常用”工具栏中的“自动求和”按钮完成。3 计算“占总销售额比例”列数据，使用绝对地址引用，F4单元格中输入“=D4
15、/$D$8”，然后采用公式复制的方法得到下方数据。计算出的单元格数据设置为百分比格式：选择数据区域，执行【格式】【单元格】命令，选择“数字”标签，进行相应设置。4 设置单元格格式，对该表格使用自动套用格式中的“彩色2”：选择数据区域，执行【格式】【自动套用格式】命令，选择“彩色2”。5 制作图表，选择“名称”和“占总销售额比例”列数据，执行【插入】【图表】命令执行【插入】【图表】命令或单击工具栏中的图表向导按钮，按照向导步骤指引制作分离型三维饼图，对图表区单击鼠标右键，在快捷菜单中单击“图表区格式”，在“图案”选项卡中选择“填充效果”，在“纹理”中选择“新闻纸”。3.员工年度绩效考核表的计算1
16、 在“员工年度绩效考核表”中，在G4单元格中利用SUM函数计算“年度绩效总分”列数据，然后采用公式复制的方法得到下方数据。2 利用RANK函数计算“本人排名”列数据的方法：使用绝对地址引用，H4单元格中输入“=RANK(G4,$G$4:$G$12)”，然后采用公式复制的方法得到下方数据。实验10 Excel的数据处理（二）【实验目的】1. 了解Excel的数据库管理功能。2. 掌握数据记录的排序操作。3. 掌握数据记录的筛选（自动筛选和高级筛选）操作。4. 掌握数据表分类汇总的方法。5. 掌握数据透视表的建立方法。【实验环境】Windows XP+Office2003/2007【实验内容】在实
17、验9完成的“学生成绩统计表”的基础上，完成排序、筛选、分类汇总、数据透视表等操作。任务一：学生成绩统计表的排序和筛选。任务二：学生成绩统计表的分类汇总和制作数据透视表。【实验步骤】任务一：学生成绩统计表的排序和筛选。打开“学生成绩统计表.xls”，并对表格按要求进行排序和筛选操作。要求：1 将实验9完成的“学生成绩统计表”复制到4个新表中，并将复制所得的新表重命名为“排序表”、“自动筛选表”、“自定义筛选表”以及“高级筛选表”。2 在“排序表”中，以“平均分”为关键字按递减方式排序，若平均分相同，则按姓名的姓氏笔画升序排序，结果如图1所示。图1 “排序表”结果图3 在“自动筛选表”中，筛选出“
18、英语”排在前5名的学生，结果如图2所示。图2 “自动筛选表”结果图4 在“自定义筛选表”中，筛选出“计算机”分数在80 90分之间的学生，结果如图3所示。图3 “自定义筛选表”结果图5 在“高级筛选表”中，筛选出至少有一门课程不及格的学生，如图4所示。图4 “高级筛选表”结果图任务二：学生成绩统计表的分类汇总和制作数据透视表。打开“学生成绩统计表.xls”，并对表格按要求进行分类汇总和制作数据透视表。要求：1 将“学生成绩统计表”再次复制到2个新表中，并将复制所得的新表重命名为 “分类汇总表”以及“数据透视表”。2 在“分类汇总表”中，按照性别分类汇总各列数据的平均值，结果如图5所示。图5 “
19、分类汇总表”结果图3 在“数据透视表”中，为学生成绩统计表建立数据透视表，如图6所示。图6 数据透视表4 保存工作簿。【实验总结】【操作提示】1. 学生成绩统计表的排序和筛选1 数据排序执行【数据】【排序】命令，在弹出的对话框中根据要求进行设置，如图7所示。2 数据筛选执行【数据】【筛选】【自动筛选（高级筛选）】命令，在弹出的对话框中根据要求进行设置，如图8、9、10所示。 图7 “排序”对话框 图8“自动筛选前10个”对话框图9 “自定义自动筛选方式”对话框 图10 “高级筛选”对话框 图11 “分类汇总”对话框2. 学生成绩统计表的分类汇总和制作数据透视表1 数据分类汇总先按照“性别”字段
20、进行排序，然后执行【数据】【分类汇总】命令，在弹出的对话框中根据要求进行设置，如图11所示。2 建立数据透视表执行【数据】【数据透视表】命令，根据数据透视表向导的指引完成数据透视表建立。实验11 Excel的图表与图形【实验目的】1. 熟练掌握Excel的图表制作。2. 熟练掌握套用表格格式、单元格样式、条件规则的操作。3. 熟练掌握【实验环境】Windows XP+Office2003/2007【实验内容】任务一：制作公司员工月出勤统计表。任务二：学生成绩统计表的分类汇总和制作数据透视表。【实验步骤】任务一：制作员工月出勤统计表。根据图1所示效果制作员工月出勤统计表。（制作过程参考课本P14
21、6页实例7.1。） 9.3-9.7考勤记录表 9.10-9.14考勤记录表 9.17-9.21考勤记录表 9.24-9.29考勤记录表 图1 9月考勤统计表任务二：分析员工学历和年龄状况。根据图2所示效果制作分析图表。（制作过程参考课本P234页实例11.1。） 性别结构比例图 年龄结构比例图 学历结构比例图 年龄与学历结构比例图 图2 分析图【实验总结】实验12 Excel综合应用【实验目的】1. 熟练掌握Excel的基本制表方式方法。2. 熟练掌握Excel中公示和函数计算。3. 熟练掌握Excel的图表制作。4. 提高综合应用Excel处理数据的能力。【实验环境】Windows XP+O
22、ffice2003/2007【实验内容】建立“部门人员工资表.xls”，完成表格输入、计算、排序、筛选、分类汇总、数据透视表等操作。任务一：部门人员工资表的建立以及格式化。【实验步骤】任务一：部门人员工资表的建立以及格式化。建立“部门人员工资表”，如图1所示。完成表格输入、计算以及格式化操作。任务一完成之后的表格如图2所示。图1 “部门人员工资表”原始数据图2 完成计算及格式化后的“部门人员工资表”要求：1 新建一个工作簿文件，将当前的临时工作簿文件保存在D:EXCEL文件夹中，命名为“部门人员工资表.xls”。2 在sheet1中输入数据，如图1所示。3 在表中完成计算：1 “工龄补贴”：按
23、工作一年补贴8元计算。2 “扣公积金”：按“职务工资”的一定比例扣除公积金，该比例数存放在J16单元格中，要求使用绝对地址的概念。3 应发工资 = 职务工资 + 津贴 + 奖金 + 工龄补贴 - 扣水电费 - 扣公积金4 并求出每一列数据的平均值、最大值和最小值。4 参考图2，在第二行前插入一行，输入制表日期和制表人，在“制表日期”后填写练习当日的系统日期，在“制表人”后填写制表人的姓名。5 参考图2，对表格格式化：5 第一行标题：华文彩云、22号、加粗、水平方向和垂直方向居中、红色、填充颜色为冰蓝；6 第二行：宋体、10号、水平居中、黑色，跨列合并；7 第三行列标题文字：宋体、12号、加粗、
24、水平方向和垂直方向居中、黑色、填充颜色为浅青绿；8 其余行文字：宋体、12号、水平垂直居中、黑色，数值保留一位小数，其中“应发工资”列保留两位小数，并带货币符号；9 参考图2设置表格边框。6 将Sheetl工作表名更改为“工资表”。7 工作过程中，随时保存文件。任务二 ：部门人员工资表的数据库管理功能。对“部门人员工资表”进行排序、筛选、分类汇总等操作。要求：1. 将“工资表”复制到4个新表中，并将复制所得的新表重命名为“排序”、“自动筛选”、“高级筛选”以及“分类汇总”。2. 在“排序”工作表中，首先按“工作单位”递增排序；“工作单位”相同的人，再按“奖金”作递减排序，结果如图3所示。图3
25、“排序”工作表3. 在“自动筛选”工作表中，筛选出“应发工资”大于2000元的“女”职工，并保持筛选后的状态，结果如图4所示。图4 “自动筛选”工作表4. 在“高级筛选”工作表中，筛选出“职务工资”在1400元以上，并且“工作单位”属于外联处的职工，并保持筛选后的状态，结果如图5所示。图5 “高级筛选”工作表5. 在“分类汇总”工作表中，按“工作单位”对“奖金”、“扣水电费”、“扣公积金”、“应发工资”分类汇总（求和），结果如图6所示。图6 “分类汇总”工作表6. 为“部门人员工资表”建立数据透视表，并根据透视表建立数据透视图，如图7、8所示。 图7 数据透视表图8 数据透视图7. 制作工资条
26、，如图9所示。图9 工资条8. 保存工作簿。9. 【实验总结】【操作提示】1. 计算“工龄补贴”列数据1 I4单元格中输入公式：“ =(YEAR(TODAY( )-YEAR(D4)*8 ”；v TODAY函数的功能为返回系统当前日期。v YEAR函数的功能为返回某日期的年份。其结果为1900到9999之间的一个整数。2 然后采用公式复制的方法得到其他人的“工龄补贴”列数据。2. 计算“扣公积金”列数据1 K4单元格中输入公式：“=F4*$K$17 ”；2 然后采用公式复制的方法得到其他人的“扣公积金”列数据。3. 制作工资条在单元格A3中输入第一个员工的编号“001”。在单元格B3、C3、D3
27、、E3、F3、G3、H3、I3、J3中分别输入如下公式：B3单元格中输入“=VLOOKUP(A3, 工资表! $A$3: $J$16, 2, FALSE)”，C3单元格中输入：“=VLOOKUP(A3, 工资表! $A$3: $J$16, 3, FALSE)”。D3单元格中输入：“=VLOOKUP(A3, 工资表! $A$3: $J$16, 4, FALSE)”。E3单元格中输入：“=VLOOKUP(A3, 工资表! $A$3: $J$16, 5, FALSE)”。F3单元格中输入：“=VLOOKUP(A3, 工资表! $A$3: $J$16, 6 FALSE)”。G3单元格中输入：“=VLOOKUP(A3, 工资表! $A$3: $J$16, 7, FALSE)”。H3单元格中输入：“=VLOOKUP(A3, 工资表! $A$3: $J$16, 8, FALSE)”。I3单元格中输入：“=VLOOKUP(A3, 工资表! $A$3: $J$16, 9, FALSE)”。J3单元格中输入：“=VLOOKUP(A3, 工资表! $A$3: $J$16, 10, FALSE)”。最后得到如图所示的引用结果。
