2022年excel实训课件一 .pdf

《2022年excel实训课件一 .pdf》由会员分享，可在线阅读，更多相关《2022年excel实训课件一 .pdf（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、EXCEL 实训课件一学生成绩表的制作应用背景学期结束时， 班主任王老师遇到了一个难题：教务处将各位任课教师给出的成绩表（每张成绩表都是一个单独的Excel 工作簿，见素材）全部交给了他，要求他据此得到“各科成绩表”（图 1）及“成绩统计表” （图 2） ，便于对全班的各科成绩进行分析统计。图 1 图 2 起初， 他将所有的成绩表工作簿（见素材） 全部打开， 企图把各科成绩粘贴到图1 中，结果他发现粘贴后的单元格中却显示了出错信息，他手忙脚乱地忙活了半天，图1 的要求都没有达到，就更别提完成图2 的要求了。他只好硬着头皮一个一个去输入了，但这既容易出错，工作量又很大。不得已他向教Excel 的

2、老师请教，经过老师的指点，他快速准确地完成了图1 和图 2 两个工作表的制作， 并顺利地对全班成绩进行了其他的分析统计工作，下面是他的解决方案。名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 1 页，共 9 页 - - - - - - - - - 解决方案首先建立一个新的Excel 工作簿，并打开所有的成绩表工作簿（见素材），将相应的工作表复制到新建工作簿中。并按图1 所示的要求，将内容复制到一个空白工作表中，特别注意，在复制分数时，必须使用“选择性粘贴”或引用其他

3、工作表相应单元格的数据，否则就会显示出错信息。至于得到图2 的结果嘛，就必须使用各种函数（如：COUNT 、MAX 、MIN 、COUNTIF 以及 IF 等）来解决问题了！制作过程一、制作“计算机应用”成绩登记表1新建一个工作簿。将工作簿命名为“学号后2 位+姓名 .xls”2在工作表Sheet1中，输入图3 所示的内容。提示：学号的输入方式为输入数字前加上西文的将视为文本，或将单元格的格式设置为“文本”图 3 3将各列的宽度调整到最适合的位置（见图3） 。4将“第一次测试” 、 “第二次测试”栏目的栏目名进行自动换行。5将标题的字体设置为“黑体”， “18”号，粗体，并跨列居中。名师归纳总

4、结 精品学习资料 - - - - - - - - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 2 页，共 9 页 - - - - - - - - - 6表头单元格区域设置：行高（30） ，水平、垂直居中，淡绿色底纹。7表格数据区域单元格设置：字号（10） ；垂直居中；学号内容左对齐，其他数据内容水平居中。8表格边框设置（见图3） 。9计算：平时、第一次测试、第二次测试的平均成绩，并将结果放置在“平均成绩”单元格中。10 “计算机应用”成绩公式为：平时*0.2+ 第一次测试 *0.45+ 第二次测试 *0.35 11 通过

5、减少小数位或ROUND 函数，分别将“平均成绩”和“计算机应用”的结果以整数位呈现。12将工作表“Sheet1”更名为“计算机应用”。二、生成“各科成绩表”1参见样例“成绩表.xls” ，分别打开“程序设计”、 “大学英语” 、 “企业管理”三个工作簿，并将同名的工作表复制到“学号后2 位+姓名 .xls”的工作簿中。2将四个工作表的排列顺序调整为“计算机应用”、 “程序设计” 、 “大学英语” 、 “企业管理”。3选择工作表“Sheet2” ，并将工作表更名为“各科成绩表”。4将“程序设计”工作表中“学号”、 “姓名”、 “性别”列的数据复制到“各科成绩表”中的相应位置。5用选择性粘贴的方法

6、分别将工作表“计算机应用” 、 “程序设计”中的同名列粘贴到“各科成绩表”中；用引用单元格的方法将“大学英语”、 “企业管理”中的同名列引用到“各科成绩表”中的对应位置。（思考：你觉得这两种方法各有何特点？）6分别计算每人的总分和名次（用RANK 函数完成，注意相对引用与绝对引用的区别） ，并计算各科成绩的班级平均分（参见样例）要求四舍五入到小数点后两位。三、生成“成绩统计表”1. 在“学号后2 位+姓名 .xls”的工作簿中，选择工作表“Sheet3” ，并将工作表更名为“统计表” 。参见样例“成绩表.xls”中的“统计表” 。见图 4。名师归纳总结 精品学习资料 - - - - - - -

7、 - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 3 页，共 9 页 - - - - - - - - - 图 4 2利用 COUNT 函数计算四门课程参加考试的人数。提示：必须引用“各科成绩表” 相应区域的数据， 如： “=COUNT( 各科成绩表 !D2:D38)” ，下同。3.分别利用MAX 函数与 MIN 函数计算四门课程中的最高分数与最低分。4.将“各科成绩表”工作表中四门课程的班级平均分引用到平均分行中。提示：计算“计算机应用”课程的平均成绩：=各科成绩表 !D39。5.用 COUNTIF 函数分别统计出各门课

8、程中各分数段的人数。提示：90-100分数段： =COUNTIF( 各科成绩表 !D2:D38,=90 ；80-89分 ： =COUNTIF(各 科 成 绩 表 !D2:D38,=80)-COUNTIF(各 科 成 绩表!D2:D38,=90) 或=COUNTIF( 各科成绩表 !D2:D38,=80)-B7 ；70-79分 ： =COUNTIF(各 科 成 绩 表 !D2:D38,=70)-COUNTIF(各 科 成 绩表!D2:D38,=80) 或=COUNTIF( 各科成绩表 !D2:D38,=70)-B7-B8 。以此类推。6.分别计算四门课程中的及格率。完成以上实训的同学，还可以尝试

9、使用FREQUENCY函数的方法完成统计各门课程中各分数段的人数。提示：将“统计表”复制一份，并清除B2:E11 单元格的所有内容。FREQUENCY函数的用法：先在一个空白区域（如：G7:G11）中分别输入如下分数间隔点“100、89、 79、69、59” ，再选定目标单元格（如：B7:B11） ，然后选择FREQUENCY 函数。名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 4 页，共 9 页 - - - - - - - - - 在 data_array区域中

10、，选定“各科成绩表”工作表中的D2:D38 区域（“计算机应用”的成绩）；在 bins_array区域中，选定间隔点区域G7:G11，单击“确定”按钮；按 F2,再按Ctrl+Shift+Enter 。表示公式是以数组公式的形式输入的。如果要计算其他课程各分数段的人数，应该如何使用FREQUENCY函数？四、制作“成绩统计图”利用图表向导绘制成绩统计图，见图5，其中：图 5 1图表类型用：簇状柱形图；2数据系列产生在列；3图表标题：成绩统计图；分类（X）轴：等级；数值（Y）轴：人数；4显示数据表；5将图表作为新工作表插入，新工作表名称为：成绩统计图；6图表区域的填充效果用“麦浪滚滚”；7绘图区

11、格式的填充效果用纹理“白色大理石”；8图例格式的填充效果用“蓝色面巾纸”。名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 5 页，共 9 页 - - - - - - - - - 五、根据“各科成绩表”制作“各科等级表”1.插入一个新的工作表，并将工作表命名为“各科等级表”。2.将“各科成绩表”中“学号”、 “姓名”、 “性别”列的数据复制到“各科等级表”中的相应位置。3.利用 If 函数对“各科成绩表”工作表中“计算机应用”成绩在60 分以上的设置为“及格”，否则设

12、置为“不及格”。4.利用 If 嵌套函数对 “各科成绩表” 工作表中其他三门课程进行如下设置：成绩在 90分以上的设置为“A” ；成绩在80-89 分之间的设置为“B” ；成绩在70-79 分之间的设置为“C” ；成绩在60-69 分之间的设置为“D” ；成绩小于60 分的设置为“ E” 。5.利用条件格式将“不及格”或等级为“E”的单元格设置成“黄色底纹红色加粗字体” 。将除标题行以外的偶数行数据区域设置为“灰色底纹紫罗兰色字体”。提示：利用公式=MOD(ROW(),2)=0设置条件。六、排序1.将“计算机应用”工作表中“计算机应用”列的成绩降序排列。2.将“企业管理”工作表中的“企业管理”

13、列的成绩升序排列。3.在“大学英语”工作表中，以“性别”为主要关键字（降序）； “大学英语”为第二关键字（降序） ； “姓名”为第三关键字（升序）排列、七、筛选1. 自动筛选首先把“各科成绩表”工作表复制一份，并将工作表改名为“自动筛选”。在“自动筛选”工作表中，将同时满足以下三个条件：1） “性别”为“女”2）姓“李”或名字中最后一个字为“娜”；3） “大学英语”的成绩为90 分以上或不及格的记录筛选出来。名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 6 页，共

14、 9 页 - - - - - - - - - 2. 高级筛选把“各科成绩表”工作表再复制一份，并将工作表改名为“高级筛选”。在“高级筛选”工作表中，将名次在前5 名的女同学或名次在后5 名的男同学的记录筛选出来。六、根据“成绩统计表”制作可选择的“成绩统计图”1.插入一个新的工作表，并将工作表命名为“选择统计图”。2.在“统计表”工作表中，复制A2:E12 区域的内容，切换到新建的“选择统计图”工作表中，并选中A2 单元格，执行“选择性粘贴”命令，在对话框中，选中“转置”复选框及“值和数字格式”单选按钮。3.执行“视图”“工具栏”“窗体”命令，单击“窗体”工具栏中的“组合框”按钮，在工作区的空

15、白位置画出一个组合框，选中组合框，单击右键，执行“设置控件格式”命令，在“数据源区域”中选择四名课程名称“计算机应用”“企业管理”所在的单元格，在“单元格链接”区域中，选择D9 单元格，在“下拉显示项数”中输入“4” 。4.执行“插入”“名称”“定义”命令，在引用位置处输入：=OFFSET( 选择统计图 !$E$2,选择统计图 !$D$9,1,1,5) ，并为其命名为“Point” 。5.单击“常用” 工具栏中的 “图表向导” 按钮， 选择“饼图” 中的“分离型三维饼图” ，单击“下一步”按钮，切换到“系列”选项卡，单击“添加”按钮，在“值”处，输入引用区域名称“=成绩表 .xls!Point

16、 ” ，在“分类标志”处，选择分数段标题，如：“=选择统计图!$F$2:$J$2” 。6.适当对图表进行各种美化处理。课后作业1新建一个工作簿，并将工作簿命名为“学号后2 位+姓名 +课后作业 .xls” 。2 打开“学号后2 位+姓名 .xls”的工作簿，将工作表“各科成绩表”复制到“学号后 2 位+姓名 +课后作业 .xls”中。名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 7 页，共 9 页 - - - - - - - - - 3在“学号后2位+姓名 +课后

17、作业 .xls”中，插入一个新的工作表，并将工作表命名为“各科等级表”4根据“各科成绩表”制作“各科等级表”，其中所有四门课程的等级规定如下：成绩在90 分以上的设置为“优秀”；成绩在80-89 分之间的设置为“良好”；成绩在70-79 分之间的设置为“中等”；成绩在 60-69 分之间的设置为“及格”；成绩小于60 分的设置为“不及格” 。结果如图6 所示。图 6 5在“学号后2位+姓名 +课后作业 .xls”中，插入一个新的工作表，并将工作表命名为“统计表” ，在“统计表”相应的单元格中输入图7 所示的内容。图 7 6根据“各科等级表”中的内容，用COUNTIF函数分别统计出各门课程中各等

18、级的人数。7根据统计出的各等级人数，利用图表向导绘制“程序设计”课程各等级人数统计图。其中：图表类型为“分离型三维饼图”；图表标题为： “程序设计”各分数段人数统计图；图例位置在“底部”；数据标签包括“类别名称”及“百分比”；将图表作为新工作表插入，新工作表名称为： “程序设计”各等级人数统计图；图表区域的填充效果为“水滴”。名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 8 页，共 9 页 - - - - - - - - - 思考： 能否用 FREQUENCY 函数完成各 等级 人数的统计。为什么？名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -精心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 9 页，共 9 页 - - - - - - - - -