实训任务4
一、制作课程表
1.实验目的
掌握使用Excel2016建立和编辑文档的基本方法。 2.实验要求
（1）掌握Excel2016的启动和退出。 （2）熟悉Excel2016的工作界面。
（3）掌握工作簿的创建、保存、打开和关闭等操作方法。 3.实验内容和步骤
（1）打开Word2016，制作如图1所示的“课程表”表格，并保存文件名为“课程表.docx”。
图1 在Word2016中制作的课程表
（2）在Word2016中，选择整个课程表表格，单击【开始】→〖剪贴板〗→“复制”按钮。
（3）打开Excel2016，系统自动创建“工作簿1”，选定单元格A1，单击【开始】→〖剪贴板〗→“粘贴”按钮，则课程表表格复制到工作表中，如图2所示。
图2 在Excel2016中制作的课程表
（4）单击“快速访问工具栏”的“保存”按钮，打开“另存为”对话框，在“文件名”栏中输入名称“课程表”，在“保存位置”下拉列表中选择保存工作簿文档的文件夹，选择默认保存类型“Excel工作簿（*.xlsx）”，单击“保存”按钮，完成“课程表”的制作。
（5）单击“功能区”右上角的“关闭窗口”按钮，关闭“课程表”工作簿。以后如果需要进一步编辑此工作簿，可以打开“计算机”或“资源管理器”窗口，切换到工作簿文件所在的文件夹，双击该工作簿文件即可打开。也可以通过单击【文件】→“打开”按钮打开工作簿文件。
二、制作学生情况表
1.实验目的
掌握Excel2016电子表格的数据填充方法，单元格、区域和工作表的基本编辑方法。 2.实验要求
（1）掌握数据输入的基本操作方法。 （2）掌握数据填充的基本操作方法。 （3）掌握数据编辑的基本操作方法。 （4）掌握数据查找和替换的基本操作方法。 （5）掌握工作表的基本操作方法。
（6）掌握单元格、行或列的基本操作方法。 （7）掌握单元格格式设置的基本操作方法。 （8）掌握美化工作表的基本操作方法。 3.实验内容和步骤
（1）新建工作簿，把工作簿文件命名为“学生情况表.xlsx”，并确定文件的存放位置。 （2）创建“学生情况表”工作表，输入工作表数据如图3所示。
图3 学生情况表
①输入表头：学生情况表。
②输入标题行：学号、姓名、性别、出生年月、电话、Email、班级活动费。 ③输入“学号”列数据：先在单元格A3中分别输入“’057131001”，然后选中单元格 区域A3，将鼠标指针移到该单元格的填充柄上，使鼠标指针形状成为实心十字时，按住左键拖动鼠标即可完成学号的填充。
④输入“性别”“电话”“Email”列数据：性别列中的重复值可以直接利用填充柄复制，电话和Email可部分利用填充的方式复制后进行修改。
⑤对于没有规律的数据采用直接输入的方式，如“姓名”“出生年月”等。 （3）打开“学生情况表.xlsx”工作簿文件。 （4）美化学生情况表，效果如图4所示。
图4 美化后的学生情况表
①格式化表头。
●选中第1行，设置行高为“40”。
●选中单元格区域A1:I1，设置“合并后居中”（或跨列居中），设置字体为“微软雅黑”、字号为“18”。
②格式化表体。
●选中第2行～第41行，设置行高为“20”。
●选中单元格区域A2:G41，设置字体为“宋体”、字号为“12”，设置边框为“所有 框线”。
●设置“学号”“姓名”“性别”“电话”“Email”“班级活动费”列数据“居中”。 ③格式化标题行。选中单元格区域A2:G2，设置“居中”显示，且字体“加粗”。 ④插入“照片”列。
●选中单元格H2，输入“照片”。
●选中单元格区域H2:I2，设置“合并后居中”。
●H列和I列分别从第3行开始，2行“合并后居中”为1行。 ●选中单元格区域H2:I41，设置边框为“所有框线”。 ⑤在“照片”列插入图片。
三、制作学生成绩表
1.实验目的
掌握Excel2016电子表格的公式和函数的使用方法。
2.实验要求
（1）掌握公式输入和编辑的基本方法。 （2）掌握公式中单元格引用的概念。 （3）掌握公式移动和复制的方法。 （4）掌握函数输入和编辑的基本方法。 3.实验内容和步骤
（1）新建工作簿，确定文件的存放位置，并保存工作簿文件。
（2）建立学生成绩工作表，输入学号、姓名、形成性成绩、期中成绩、期末成绩等数据，如图5所示。
图5 学生成绩表
（3）输入总评成绩的计算公式，求出每个学生的总评成绩。
①总评成绩计算方法：设总评成绩满分为100分，其中形成性成绩占40%，期中成绩占20%，期末成绩占40%。
②总评成绩计算公式：单元格F5的总评成绩公式为：=C5*0.4+D5*0.2+E5*0.4， 单元格M5的总评成绩公式为：=J5*0.4+K5*0.2+L5*0.4。
①统计90～100分的人数。利用COUNTIF函数统计总评成绩为90～100分的人数，其中统计条件可以设为“>=90”，统计范围为单元格区域F5:F31和M5:M16，公式可表示为：=COUNTIF(F5:F31,\">=90\")+COUNTIF(M5:M16,\">=90\")。
②统计80～分的人数。
=COUNTIFS(F5:F31,\">=80\
③统计70～79分的人数。
=COUNTIFS(F5:F31,\">=70\
④统计60～69分的人数。
=COUNTIFS(F5:F31,\">=60\
⑤统计0～59分的人数。
=COUNTIF(F5:F31,\"<60\")+COUNTIF(M5:M16,\"<60\") ⑥统计0～39分的人数。
=COUNTIF(F5:F31,\"<40\")+COUNTIF(M5:M16,\"<40\") ⑦统计应考和实考人数。
=COUNT(F5:F31)+COUNT(M5:M16) =COUNTA(F5:F31)+COUNTA(M5:M16)
可利用COUNT和COUNTA函数统计应考和实考人数。函数COUNT与COUNTA的区别：函数COUNT在计数时，将把数字、空值、逻辑值、日期或以文字代表的数统计进去，但是错误值或其他无法转化成数字的文字则被忽略。而COUNTA的参数值可以是任何类型，可以包括空字符(\"\")，但不包括空白单元格。
⑧统计最高分、最低分、平均分。 如成绩在F5：F31 和M5：M16则 平均分=AVERAGE(F5：F31,M5：M16) 最高分=MAX(F5：F31,M5：M16) 最低分=MIN(F5：F31,M5：M16)
四、数据的排序、筛选与分类汇总
1.实验目的
掌握Excel2016电子表格数据排序、筛选等数据分析方法。 2.实验要求
（1）掌握单关键字、多关键字及自定义排序的基本方法。 （2）掌握自动筛选及高级筛选的基本方法。 （3）掌握简单分类汇总及多级分类汇总的基本方法。 3.实验内容和步骤
（1）打开案例二(书本192页)的工作簿文件“工资明细表.xlsx”，完成如下排序：
①分别按“职工号”及“姓名”升序排序。
②先按“职称”升序排序，在职称相同的情况下，按“应发工资”降序排序。 ③先按“部门”排序，其排序次序为：计算机系、土木工程系、机械系。在部门相同的情况时，按“应发工资”降序排序。
（2）完成如下自动筛选：
①筛选出部门为“机械系”及“计算机系”的所有记录。 ②使用搜索框筛选出职称中含有“教”字的全部记录。 ③使用筛选条件筛选出职称中不含“教”字的全部记录。 ④筛选出应发工资小于1200元的所有记录。 ⑤筛选出应发工资为1500～2000元的所有记录。 ⑥筛选出应发工资高于平均工资的所有记录。 ⑦筛选出应发工资最高的前5条记录。
⑧筛选出部门为“计算机系”或“机械系”，且应发工资大于1700元的所有记录。 （3）完成如下高级筛选：
①筛选出部门为“计算机系”的所有记录。
②筛选出部门为“计算机系”或“机械系”的所有记录。 ③筛选出职称为“教授”且部门为“机械系”的所有记录。 ④筛选出应发工资小于1100元或应发工资大于2000元的所有记录。 ⑤筛选出职称为“教授”或应发工资大于1500元的所有记录。 （4）完成如下分类汇总：
①汇总出各种职称的基本工资、洗理费、奖金及应发工资合计值。 ②汇总出各种职称的人数。
③分别汇总出各部门的人数，以及各部门、不同职称的职工的基本工资、洗理费、奖金和应发工资的合计值。
（4）以上每个操作任务结束后，注意保存文件。
五、在学生成绩统计表中创建图表
1.实验目的
掌握Excel2016图表创建、编辑和修饰的方法。 2.实验要求
（1）掌握标准图表的创建和图表编辑的基本方法。 （2）掌握图表的格式设置与修饰美化的基本方法。 （3）掌握迷你图的创建和设置的基本方法。 3.实验内容和步骤 （1）创建标准图表。
●打开“学生成绩统计表.xlsx”文件“Sheet1”工作表，如图6所示，选中数据区域B2:G9。 ●创建默认的簇状柱形图，调整图表文字及图表位置，如图7所示。
图6 打开“Sheet1”工作表
图7 创建默认的簇状柱形图
（2）更改图表位置、类型、选项设置。
●复制工作表“Sheet1”为“Sheet1（2）”“Sheet1（3）”“Sheet1（4）”“Sheet1（5）”。 ●在“Sheet1”表中复制图表，更改该图表位置生成“Chart1”图表工作表。
●设置“Chart1”图表标题为“学生成绩统计分析图表”；纵轴标题为“分数”；添加“模拟运算表”；设置图表文本字号适宜，显示清晰，如图8所示。
学生成绩统计分析图表300250200150100500张罗凤计算机英语数学总成绩平均成绩947423277吴影85607321873钱铎余55455215251胡严71918324582梅谱51637619063李布卿60879524281计算机英语数学总成绩平均成绩萨伊欧娜拉98688024682分数 图8 更改为图表工作表
●选中工作表“Sheet1（2）”中的图表，切换行/列；复制该图表，将其拖拽到右下角，隐藏原图表；更改图表类型为堆积折线图；添加数据标签在“上方”并更改图表最下方的数
据系列线条颜色，如图9所示。
图9 更改为堆积折线图
●复制图表工作表“Chart1”为“Chart1（2）”；将簇状柱形图改为“饼图”；更改图表标题为“计算机成绩对比分析图”；将图例置于图表底部；添加数据标签“值”“百分比”“数据标签外”“分行符”；调整饼图的分离度以实现分离饼图的效果，如图10所示。
计算机成绩对比分析图98 , 19%94 , 18%60 , 12%85 , 16%51 , 10%55 , 11%71 , 14%张罗凤吴影钱铎余胡严梅谱李布卿萨伊欧娜拉 图10 设置图表选项
（3）创建迷你图。
●复制工作表“Sheet1”中的所有数据到“Sheet2”，调整数据区域第1行行高为27，其他各行行高为25；A:G列列宽为9；H:K列列宽为12；在单元格H1内输入“迷你图1”，向右
填充至单元格I1、J1为“迷你图2”“迷你图3”。
●选中数据区域C3:G9，创建迷你图于H3:H9区域中，如图11所示；选中列数据C3:G9， 创建迷你图于单元格I3中；依次选中D3:G9各列数据，创建各列数据迷你图于I4:I7区域中，如图12所示；选中行数据C3:G3，创建迷你图于单元格J3中，将单元格J3拖拽填充至单元格J9，生成行数据迷你图于J4:J9区域中，如图13所示。
图11 创建折线图迷你图
图12 创建列数据迷你图
图13 拖拽生成行数据迷你图
