计算机应用基础课程ecel综合实训材料.doc

《计算机应用基础课程ecel综合实训材料.doc》由会员分享，可在线阅读，更多相关《计算机应用基础课程ecel综合实训材料.doc（22页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、第1章 Office Excel 2021高级应用技术1.1 学生成绩管理一、考察知识点数据清单、格式设置、公式及函数、排序、筛选、分类汇总与图表等级知识，利用多种公式与函数对单元格中的数据进展运算；对各种数据可以进展格式设置；分析汇总各单元格中的数据信息，把相关数据用统计图表的形式表示，运用Excel 2021电子表格强大的数据管理功能，进展相应的数据处理与分析。二、知识点解析1、创立学生信息表，如图 71所示。图 11原始工作表2、操作内容：（1） 将首行合并居中，并将标题“学生信息表设置为“黑体，“20号，将其他数据清单数据设置为“宋体，“12号，“水平居中。（2） 将数据表中的行高设为

2、“20磅，列宽设为“最适宜的列宽，数据表中的单元格垂直与水平对齐方式均设定为“居中。（3） 设置各科成绩的数据输入有效性，要求成绩输入的范围为0100，当鼠标停留在成绩的单元格上时，提示信息为“成绩范围：0100。（4） 将每门课程中不及格的成绩用红色、加粗字体显示。（5） 用AVERAGE函数求出每位学生的平均分，并用IF函数根据平均分对学生做出评价，60分及以上为“合格，60分以下为“不合格。（6） 根据“身份证号码字段计算每位学生的年龄。（7） 用统计函数完成对“平均分中各分数段人数统计，把结果分别填入相应的单元格，并根据各等级的比例生成三维饼图，放在统计表下。（8） 插入一张工作表，工

3、作表标签颜色设成红色，工作表重命名为“优秀学生信息表；在“学生信息表中筛选出平均成绩大于等于85分的学生记录，将其复制到“优秀学生信息表中，“学生信息表中的记录复原。（9） 建立“按组分类汇总工作表，把“学生信息表中的成绩表复制到该表中，按“组别字段升序排序。（10） 按A、B两个组进展分类汇总，分别计算出A、B组的“数学、“英语、“物理、“化学平均分。3、操作分析及过程1输入数据表中的原始数据。2选择D3至G23区域，单击“数据选项卡“数据工具组中的“数据有效性命令，弹出如图 72所示的对话框，选择“设置选项卡进展“有效性条件设置，在“输入信息选项卡中进展相关输入信息的设置。图 12 设置“

4、数据有效性对话框3选择D3至G23区域，单击“开场选项卡“样式组中的“条件格式按钮，选择“突出显示单元格规那么“其他规那么命令，弹出“新建格式规那么对话框，设置单元格数值为“小于60，再单击“格式对话框，设置字体为“红色、“加粗，如图 73所示，单击“确定按钮。图 13条件格式设置4在单元格H3中输入公式“=AVERAGE(D3:G3)，计算该学生的平均分，利用Excel 2021的序列填充功能，求出其他学生的平均分，如图 74所示。图 14计算“平均分5在单元格I3中输入公式“=YEAR(NOW()-MID(C3,7,4)，计算该学生的年龄，利用Excel 2021的序列填充功能，求出其他学

5、生年龄，如图 75所示。图 15计算“年龄6在单元格J3中输入公式“=IF(H3=60,合格,不合格)，计算该学生的等级，利用Excel 2021的序列填充功能，求出其他学生的等级，如图 76所示。图 16计算“等级7在单元格D26中输入公式“=COUNTIF(H3:H23,=60)-COUNTIF(H3:H23,=85)，计算“60-84分的人数；在单元格F26中输入公式“=COUNTIF(H3:H23,=85)，计算“85分的人数；在单元格D27中输入公式“=D26/COUNT($H$3:$H$23)，计算出该等级所占比例，其它依次类推，如图 77所示。图 17统计各分数段人数8选中C25

6、至F27，单击“插入选项卡“图表组中的“饼图按钮，选择“三维饼图命令，根据各等级的比例生成如图 78所示的三维饼图，放在统计表下。图 18各分数段人数所占比例图表9插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优秀学生信息表略；返回原始工作表，单击“数据选项卡“排序与筛选组中的“筛选命令，各字段旁出现下拉按钮。单击“平均分字段的下拉按钮，在弹出的下拉菜单中执行“数字筛选“自定义筛选命令，弹出“自定义自动筛选方式对话框，如Error! Reference source not found.所示。筛选出平均成绩大于等于85分的学生记录，将这些记录复制到“优秀学生信息表中，单击“数据选项卡

7、“排序与筛选组中的“筛选命令，取消“学生信息表中的记录筛选。图 19设置“自动筛选对话框10建立“按组分类汇总工作表，把“学生信息表中的成绩表复制到该表中，按“组别字段升序排序，单击“数据选项卡“分级显示组中的“分类汇总命令，按“组别进展分类汇总，分别计算出A、B组的“数学、“英语、“物理、“化学平均分，如图 710所示，单击“确定按钮，得到汇总结果。图 110设置“分类汇总对话框1.2 毕业设计成绩管理一、工程介绍毕业设计成绩信息记录了毕业设计的根本情况，随着高校办学规模的扩大，各专业学生数量与毕业设计题目数量都在增加，毕业设计毕业实习指导教师在学生毕业设计及毕业完毕后，需要给学生评定成绩，

8、成绩是毕业设计毕业实习整个过程的最终表达。通过计算机采用Excel进展快速评分及计算，并得出相关的统计信息，可节省教师统计及计算的时间。通过该案例的学习可掌握Excel 2021工作表多表间的数据选择与表示、多表间的公式及函数的使用、多表间的合并计算、选择性粘贴等功能的综合应用。二、操作数据【实战训练】 图 111根本信息表数据内容【实战训练】 图 112根底分表数据内容三、操作要求（1） 以“07010101为起始学号，在“学号一列为每位同学填充学号； （2） 从身份证号码中自动提取性别填入“性别一列，提取方法：假设第17位为奇数，表示男性，假设为偶属，表示女性假设身份证统一为18位； （3

9、） 从身份证号码中提取出生日期填入“出生日期一列，格式为“年月日，提示：18为身份证号码，710位为出生年份4位，1112位为出生月份，1314位为出生日期。注意：不能用单元格格式设置；（4） 在“手机短号一列填充每位同学的手机短号码，填充方法为每一位同学手机长号后四位前加上“66；（5） 在“根底成绩一列填充每个同学的根底成绩，根底成绩由“根底成绩表中查找不同设计题目所对应的根底成绩；（6） 在“总成绩一列计算每位同学的总成绩，算法为：总成绩=根底成绩+设计成绩*30%+论文成绩*30%+实习成绩*10%，结果保存1位小数；（7） 如果毕业总成绩在85分以上，那么为优秀毕业生，在“是否为优秀

10、毕业生一列填充对应的学生为“优秀毕业生； （8） 如果某学生毕业设计成绩、毕业论文成绩及毕业实习成绩中有一项缺少，那么在其“备注一列填充“成绩缺少，不予毕业；（9） 分别统计末提交毕业设计及毕业论文、未参加毕业实习的同学个数该项成绩为空那么为未提交，分别填入E63、E64、E65；（10） 在不改变现在顺序的前提下，统计总成绩前五名的平均总成绩，结果填入E73；（11） 对“根本信息表按“仓库管理系统、人事管理系统、图书管理系统、旅游网站、公司企事业网站的顺序进展排序；（12） 用“合并计算分别统计出每个毕业设计题目所有同学的设计成绩、论文成绩、实习成绩、总成绩的平均分，结果保存1位小数，并将

11、结果保存在sheet3，并将sheet3重新命名为“不同毕业设计成绩信息；（13） 用数据库函数计算女生优秀毕业生个数，填入E67提示：自己书写条件区域； （14） 用高级筛选功能筛选出设计成绩、论文成绩、实习成绩、总成绩都及格的学生，放入“各项成绩都及格的学生工作表； （15） 根据“根本信息表建立数据透视表，显示各个毕业设计题目总成绩分段人数及占总人数的比例，要求：a、 页字段为：毕业设计题目；b、 行字段为：总成绩分数段；c、 数据区为：每个分数段的人数及占该设计题目总人数的百分比；d、 放入“各题目总成绩分段人数及比例 工作表；（16） 在“不同毕业设计成绩信息表工作表中，以各个毕业设

12、计题目所有同学的设计成绩、论文成绩、实习成绩、总成绩的平均分制作柱形圆锥图，并加以修饰，结果如下图 713所示：【实战训练】 图 113平均分柱形圆锥图（17） 对“根本信息表进展设置：a、 设置整个工作表字体为10号，行高为16，列为最适合的列宽，对齐方式为水平居中及垂直居中，外边框为双线，内边框为单实线；b、 页面设置为横向，纸张为B4，居中方式为水平居中；c、 眉中部设置为“07计算机应用技术1班毕业成绩表，右部为制表日期；页脚右部插入页码；d、 设置打印区域为A1:O60，每页都显示标题行；四、操作分析及过程操作要求1英文状态下，在A2单元格输入“07010101，然后按回车。把鼠标放

13、在A2单元格填充柄上，如图 714所示。双击填充柄填充A3-A60单元格数据或当鼠标变成十字形状时，按着鼠标左键不放，向下填充A3-A60单元格数据。【实战训练】 图 114 A2单元格填充柄操作要求2在C2单元格中输入公式“=IF(MOD(MID(D2,17,1),2)=0,女,男)，计算该学生的性别，利用Excel 2021的序列填充功能，求出其他学生年性别。如图 715所示。【实战训练】 图 115计算学生的性别操作要求3CONCATENATE 函数可将最多 255 个文本字符串联接成一个文本字符串，MID函数可返回从指定位置开场的特定数目的字符串。在E2单元格中输入公式“=CONCAT

14、ENATE(MID(D2,7,4),年,MID(D2,11,2),月,MID(D2,13,2),日)，计算该学生的出生日期，利用Excel 2021的序列填充功能，求出其他学出生日期，如图 716所示。【实战训练】 图 116计算学出生日期也可通过单击菜单“公式“插入函数，插入CONCATENATE函数，在CONCATENATE函数对话框中，输入如图 717参数信息，单击确定按钮。【实战训练】 图 117CONCATENATE函数参数对话框操作要求4在M2单元格中输入公式“=66&RIGHT(L2,4)，计算该学生的手机短号，利用Excel 2021的序列填充功能，求出其他学手机短号，如图 7

15、18所示。【实战训练】 图 118计算学生短号操作要求5选取单元格“G2，单击菜单“公式“插入函数，如图 719所示，系统弹出如图 720所示的“插入函数对话框。【实战训练】 图 119“插入函数对话框【实战训练】 图 120“选择函数对话框在“或选择类别下拉列表中选择“查找及引用，在“选择函数列表中选择“VLOOKUP，系统继续弹出如图 721所示的“函数参数对话框。在“Lookup_value搜索的条件值输入框中输入“F2，即“毕业设计题目，在“Table_array被搜索的表区域输入框中输入“根底分表!$A$1:$B$6，在“Col_index_num返回值所在的列号输入框中输入“2，即

16、“姓名列，在“Range_lookup输入框中输入“FALSE或缺省，单击“确定按钮，此时的编辑栏显示“=VLOOKUP(F2,根底分表!$A$1:$B$6,2,FALSE)，在单元格“G2出现“30，计算该学生的毕业设计根底分，利用Excel 2021的序列填充功能，求出其他学生的毕业设计根底分。【实战训练】 图 121 VLOOKUP函数参数对话框操作要求6在K2单元格中输入公式“，计算该学生的总成绩，利用Excel 2021的序列填充功能，求出其他学生的总成绩，如图 722所示。【实战训练】 图 122计算学生总成绩操作要求7在N2单元格中输入公式“=IF(K2=85,优秀毕业生,)，计

17、算该学生是否为优秀毕业生，利用Excel 2021的序列填充功能，判断其他学生是否为优秀毕业生，如图 723所示。【实战训练】 图 123计算是否为优秀毕业生操作要求8可在O2单元格中输入公式“=IF(OR(H2=,I2=,J2=),成绩缺少，不予毕业,)，如图 724所示，计算该学生是否有成绩缺少，给出相应提示信息。也可以通过插入公式方式来操作。选取单元格“O2，单击菜单“公式“插入函数，插入IF函数，在IF函数对话框中，输入如图 725参数信息。光标放在Logical_test对应区域，单击，在函数列表中单击其他函数，如图 726所示，出现插入函数对话框，选择OR函数，如图 727，在OR

18、函数对话框中，输入如图 728参数信息，按确定。利用Excel 2021的序列填充功能，判断其他学生是否有成绩缺少，给出相应提示信息。【实战训练】 图 124计算是否不予毕业【实战训练】 图 125 IF函数参数对话框图 126函数列表图 127“选择函数对话框【实战训练】 图 128 OR函数参数对话框操作要求9COUNTBLANK函数用于计算指定单元格区域中空白单元格的个数，分别在E63、E64、E65单元格中输入公式“=COUNTBLANK(H2:H60)、“=COUNTBLANK(I1:I60)、“=COUNTBLANK(J2:J60)，统计末提交毕业设计及毕业论文、未参加毕业实习的同

19、学个数，其中H2:H60区域为学生的设计成绩区域，I1:I60区域为学生的论文成绩区域，J2:J60学生的实习成绩区域，如图 729。【实战训练】 图 129计算空白单元格的个数操作要求10选取单元格“E73，单击菜单“公式“插入函数，插入AVERAGE函数，在AVERAGE函数对话框中，输入如图 730参数信息，单击确定按钮。【实战训练】 图 130 AVERAGE函数参数对话框也可以利用数组公式，在单元格“E73中，输入公式“=AVERAGE(LARGE(K2:K60,1,2,3,4,5)，求出统计总成绩前五名的平均总成绩，如图 731所示。【实战训练】 图 131计算前五名的平均总成绩操

20、作要求11把光标放在A1：O60任意单元格上，单击“开场-“排序与刷选，选择“自定义排序，如图 732，在排序对话框中主关键字选择“毕业设计题目，次序选择“自定义序列，在自定义对话框中，输入序列仓库管理系统、人事管理系统、图书管理系统、旅游网站、公司企事业网站，如图 733，点添加按钮，把该序列添加到自定义序列中，按确定，再在排序对话框中按确定按钮即可，如图 734所示。【实战训练】 图 132排序对话框【实战训练】 图 133自定义序列对话框【实战训练】 图 134排序对话框操作要求12选中“不同毕业设计成绩信息表中A1单元格，单击“数据-“合并计算，在合并计算对话框中选择函数为“平均值，单

21、击引用位置右边按钮，选择“根本信息表中F1:K60区域数据，单击添加按钮，再单击确定按钮即可，如图 735。【实战训练】 图 135“合并计算对话框选择“C2：F6区域，单击右键，选择“设置单元格格式，选择“数字-“数值，设置小数位数为1位，如图 736，完成后效果如图 737。【实战训练】 图 136“设置单元格格式对话框【实战训练】 图 137完成效果操作要求13DCOUNTA函数可返回列表或数据库中满足指定条件的记录字段列中的非空单元格的个数。先在G67:H68区域中输入女优秀毕业生条件，如图 738设置女优秀毕业生条件所示，然后选取单元格“E67，单击菜单“公式“插入函数，插入DCOU

22、NTA函数，在DCOUNTA函数对话框中，输入DCOUNTA函数参数对话框中的参数信息，如图 739，单击确定按钮。也可直接在单元格“E67中，输入公式“=DCOUNTA(B1:O60,C1,G67:H68)，求出统计总成绩前五名的平均总成绩。【实战训练】 图 138设置女优秀毕业生条件【实战训练】 图 139 DCOUNTA函数参数对话框操作要求14设置条件区域，复制“根本信息表中的如图 740所示的“H1：K1区域的标题信息，粘贴到“各项成绩都及格的学生工作表中的“A1：D1 区域，设置及格条件，如图 741设置各项成绩及格条件所示。图 140“H1：K1标题信息图 141设置各项成绩及格

23、条件【实战训练】 图 142高级筛选对话框1【实战训练】 图 143高级筛选对话框2选中“各项成绩都及格的学生工作表中的“A4 单元格，单击菜单“数据“高级，翻开高级筛选对话框，如图 142高级筛选对话框1，先对条件区域及复制位置进展设置。把光标放在列表区域对应位置，在单击右边的，选择“根本信息表中“A1：O60区域数据，列表区域设置完成效果如图 143高级筛选对话框2所示。单击“确定按钮，完成筛选。操作要求15选中“各题目总成绩分段人数及比例表中A2单元格，单击“插入-“数据透视表中“数据透视表。在“创立数据透视表对话框中选择表区域，如图 744。单击确定。【实战训练】 图 144“创立数据

24、透视表对话框把“毕业设计题目拖放到A1单元格。将总成绩拖放到左边行字段处，如图 745所示。【实战训练】 图 145“毕业设计题目拖放位置把“毕业设计题目拖放到A1单元格。将总成绩拖放到左边行字段处，如图 746所示。【实战训练】 图 146总成绩拖放位置1再将总成绩拖放到值字段处，如图 747所示。【实战训练】 图 147总成绩拖放位置2再将总成绩拖放到求与项：总成绩处。效果如图 748所示。【实战训练】 图 148总成绩求与项在右侧下方处，点击汇总的第一种方式“求各项：总成绩右侧下拉菜单，如图 749，在弹出的快捷菜单中选择“值字段设置N，如图 750，在“值字段设置对话框中“值汇总方式选

25、项卡中选择计算类型为“计数，如图 751确定，出现如图 752效果。【实战训练】 图 149求与项修改【实战训练】 图 150值字段设置【实战训练】 图 151值汇总方式修改【实战训练】 图 152修改值汇总方式后效果在右侧下方处，点击汇总的第二种方式“求各项：总成绩右侧下拉菜单，如图 152，在弹出的快捷菜单中选择“值字段设置N，如图 753，在“值字段设置对话框中“值显示方式选项卡中选择“值显示方式A为“全部汇总百分比，确定，出现如图 754效果。【实战训练】 图 153修改值显示方式【实战训练】 图 154修改值显示方式后效果选中总成绩中60分以下的成绩，单击右键，选择“创立组，如图 7

26、55，同理创立60-70分创立组，70-80分创立组,80分以上创立组。效果如图 756所示。【实战训练】 图 155创立组【实战训练】 图 156创立组后效果右击总成绩列某个单元格，选择“删除“总成绩，如图 757所示。删除后完成效果如图 758所示。【实战训练】 图 157删除总成绩字段【实战训练】 图 158删除总成绩字段后效果修改“总成绩2列中各分组名称，同时修改图中“数据列中“计数项：总成绩为“人数，“求与项：总成绩2为“占总人数比例，效果如图 759所示。【实战训练】 图 159修改字段信息操作要求16选择“不同毕业设计成绩信息表中“A1：F6数据区域，单击“插入-“柱形图，单击柱

27、形图下方三角箭头，选择“圆锥图中的簇状柱形图，如图 760所示，出现如图 761所示的圆锥图。【实战训练】 图 160“柱形图下拉列表【实战训练】 图 161圆锥图选中图 161中圆锥图的右侧“垂直值铀，双击，弹出如图 762所示“设置坐标铀格式对话框，设置最大值为100，最小值为0，主要刻度单位为20，确定。选中图中圆锥图的左侧“图例，双击，弹出如图 763所示“设置图例格式对话框，调整图例位置为底部。【实战训练】 图 162设置坐标轴格式【实战训练】 图 163设置图例格式设置坐标轴值及图例后，效果如图 764所示。【实战训练】 图 164修改坐标轴及图例格式后效果选中该图表，选择“设计-

28、“图表布局中“布局3，如图 765，输入图表标题“不同毕业设计各类成绩平均分，并设置适宜的字体大小，图表完成后最终效果如图 766。【实战训练】 图 165图表布局【实战训练】 图 166图表最终效果操作要求17选择“根本信息表中“A1：O60区域，单击鼠标右键，弹出“设置单元格格式对话框，在弹出的对话框中，单击边框，在样式中选择单线样式，点击预置中“内部，如图 767设置单元格格式对话框一所示，然后在样式中选择双线样式，点击预置中“外边框，如图 768设置单元格格式对话框二所示。设置好内部及外部边框后，单击“确定按钮。【实战训练】 图 167设置单元格格式对话框一【实战训练】 图 168设置

29、单元格格式对话框二单击“对齐，分别设置水平对齐、垂直对齐方式为“居中。如图 769所示。【实战训练】 图 169设置对齐方式选中整个数据表，单击“格式中的“行高，如图 770所示，设置单元格大小中的行高为16，如图 771所示，列宽为“自动调整列宽，如图 772所示。【实战训练】 图 170选择行高【实战训练】 图 171行高设置【实战训练】 图 172自动调整列宽在“页面布局菜单中“页面设置功能区中：单击“纸张方向按钮，选择“横向，如图 773所示；单击“纸张大小按钮，设置纸张大小为B4，如图 774所示；单击“打印区域按钮，设置打印区域，如图 775所示；单击“页面设置右侧小斜拉箭头，如图

30、 776，自定义页眉与页脚，如图 777与图 778所示，同时，也可在页面设置对话框“工作表选项卡中设置打印区域及每页都显示标题行，如图 779所示。【实战训练】 图 173纸张横向设置【实战训练】 图 174设置纸张大小【实战训练】 图 175设置打印区域【实战训练】 图 176选择页面设置【实战训练】 图 177页眉页脚设置【实战训练】 图 178自定义页眉【实战训练】 图 179打印区域及顶端标题设置【工程小结】本工程利用Excel 2021对毕业设计及实习各项成绩进展管理，节省教师统计及计算的时间。通过该案例的学习可掌握Excel 2021工作表多表间的数据选择与表示、多表间的公式及函数的使用、多表间的合并计算、选择性粘贴等功能的综合应用。【实战训练】1、假设你是一位毕业设计及实习指导教师，请你结合自己实际情况，制作学生的毕业设计及实习成绩管理的电子表。2、如果你是一位专业教研室主任，请根据自己本专业学生毕业设计及毕业实习情况，制作关于本专业学生的毕业设计及实习成绩管理的电子表。第 22 页