EXCEL实训课件一制作成绩表课课前前准准备备1.在D盘新建个人文件夹学号最后2位+姓名,如“10张三”;2.进入计算机基础教学网作业提交、资料下载【登录】;将操作素材“第6章素材”下载到D盘的个人文件夹中,并解压缩;实实训训目目标标按照样文“成绩表(样例).xls”,制作一份“计算机应用”课程学生成绩登记表(图1)。并据此和三个素材文件的数据生成“各科成绩表”(图2)、并利用“自动筛选”查找出满足条件的记录(图3)。图1请采用“文档结构图”的方式浏览课件(【视图】→【文档结构图】)图2图3实实现现过过程程任务一:制作“计算机应用”成绩登记表图4实现目标:在任务一中,我们要制作如图4所示的工作表,主要完成以下任务:1.掌握各种快速高效地在Excel中输入数据的方法。2.进行公式计算。3.进行单元格格式设置。4.工作表的重命名。通过公式计算得到1.准备工作(1)启动Excel。(2)单击“常用”工具栏中的“保存”按钮,在“另存为”对话框中将文件名由“Book1.xls”,另存为“成绩表(学号后2位+姓名).xls”,并保存在个人文件夹中。相关概念:(a)工作簿、工作表、工作表标签(b)行、列、单元格、当前单元格格式2.在工作表Sheet1中输入标题及表头数据请参见“成绩表(样例).xls”中的“计算机应用”工作表,输入以下内容:(1)建立表格标题:在工作表Sheet1中,选中单元格A1,输入标题“《计算机应用》课程学生成绩登记表”。(2)输入表格的表头内容:分别在单元格A2、B2、C2、D2、E2、F2、G2中,输入“学号姓名性别平时成绩作业设计期末考试总成绩”。3.输入“学号”列数据请根据以下提示,参见P125~126,完成所有学号(04302101~04302137)的输入。在单元格A3中输入“学号”数据“04302101”数据前面的数字0出现了吗??为什么呢?怎么让它出现呢?4.利用自定义填充序列,输入“姓名”列数据(选做,若无时间可用复制粘贴的方法实现)请参见P126~127,完成姓名列的输入。5.输入“性别”列数据(选做,若无时间可用复制粘贴的方法实现)请参见“成绩表(样例).xls”中的“计算机应用”工作表,并参见P127,完成“性别”列数据的输入。(1)利用“填充柄”,将“性别”列的内容全部填充为“女”。(2)选择第一个应该修改为“男”的单元格,在按住Ctrl键的同时,分别单击其他单元格。(3)在被选中的最后一个单元格中输入“男”。(4)同时按住Ctrl+Enter键。如图6所示。图6相关概念:(a)填充柄:位于选定区域右下角的小黑方块,将用鼠标指向填充柄时,鼠标的指针更改为黑十字。(b)复制粘贴(c)自定义填充序列6.输入“平时成绩”、“作业设计”、“期末考试”列的数据切换到Word文档“计算机应用(素材).doc”文件,选择3列分数,将分数复制到相应单元格处。7.单元格计算(请参见P129)请参见P129,完成“总成绩”列数据的计算。要求如下:计算所有学生的“总成绩”(其中:总成绩=平时成绩×20%+作业设计×30%+期末考试×50%)。相关概念:公式的复制:可以通过使用填充柄将公式复制到相邻的单元格中。选择包含公式的单元格,再拖动填充柄,使之覆盖要填充的区域。8.单元格格式设置请参见P130~P134,完成“单元格格式设置”,要求如下:注意1.Excel中的“公式”是指在单元格中执行计算功能的等式。公式可以在单元格中直接输入,也可以在编辑栏中输入。Excel中的所有公式都必须以“=”号开头,若无等号,Excel将其理解为正文,所以“=”是公式中最不可缺少的一个运算符。“=”后面是参与计算的运算数和运算符,每一个运算数可以是常量、单元格或区域引用、单元格名称或函数等。2.算术运算符中的乘号和除号与数学中使用的符号不同,乘号要用“*”,除号要用“/”,乘方用“^”,这是初学者经常容易忽视的问题。3.在Excel的公式中,最好包含单元格引用,如:“=D3*0.2+E3*0.3+F3*0.5”,而不要直接使用数值,如:“=90*0.2+88*0.3+95*0.5”。(1)将标题字体设置为“幼圆”、“加粗”、“18”号,并使标题在A1:G1列合并及居中。(2)将数据区域(不包括列标题)所有单元格的字号设置为“10”;垂直对齐方式和水平对齐方式都设置为“居中”。(3)将表格的外边框设置为双细线,内边框设置为单细线。(4)为表格列标题区域添加浅绿色底纹,并设置垂直对齐方式和水平对齐方式为“居中”。(5)将表格列标题行的行高设置为30.00(40像素),将“平时成绩”列的列宽适当调窄。(6)将列标题“平时成绩、作业设计、期末考试”在单元格内分两行显示。(7)将平时成绩、作业设计、期末考试三列的列宽调整为最适合的列宽。(8)通过减少小数位数,将“总成绩”的结果以整数位呈现。(减少小数位数按钮和增加小数位数按钮)相关概念:单元格格式的设置9.工作表的更名请参见P134,完成对当前工作表的更名,要求如下:将当前工作表的名称“Sheet1”更名为“计算机应用”。提示:方法一:右键单击工作表的标签sheet1方法二:双击工作表标签sheet1再输入“计算机应用”要点回顾1.如何在单元格中输入你自己的学号?2.如何快速填充全班的学号?如何快速填充数字序列1、2、3~10?3.向单元格输入公式应注意什么?4.如何将标题在一行的多个单元格之间“合并及居中”,它与“跨列居中”有何不同?5.调整列宽和行高有哪些方法?哪种方法最简便?6.如何使单元格的宽度根据内容自动调整?7.如何精确调整单元格的行高或列宽?注意请单击“常用”工具栏中的“保存”按钮,保存当前工作簿文件。任务二、由多张工作表数据生成“各科成绩表”图71.工作表的复制及移动请参见P134~P135,完成对工作表的复制及移动操作,要求如下:(1)分别打开“大学英语(素材).xls”工作簿、“应用文写作(素材).xls”工作簿、“高等数学(素材).xls”工作簿。(2)将“大学英语(素材).xls”工作簿中的“Sheet1”工作表,复制到你自己的工作簿,如:“18张三(成绩表).xls”中的“计算机应用”工作表之前,并将复制后的目的工作表“Sheet1”更名为“大学英语”。(2)分别将“应用文写作(素材).xls”工作簿中的“应用文写作”工作表、“高等数学(素材).xls”工作簿中的“高等数学”工作表复制到你自己工作簿中的“Sheet2”工作表之前。(3)在你自己的工作簿中,将前四个工作表的排列顺序调整为“大学英语”、“计算机应用”、“高等数学”、“应用文写作”。2.工作表的删除与插入请参见P136,完成对工作表的删除与插入操作,要求如下:实现目标:在任务二中,我们要制作如图7所示的工作表,主要完成以下任务:1.工作表的复制、移动、删除及插入。2.单元格数据的复制与粘贴。3.单元格的引用方法。说明:(1)在同一工作簿中复制或移动工作表,使用鼠标拖曳的方法比较方便;而在不同工作簿中复制或移动工作表,则使用“移动或复制工作表”命令更可靠。(2)在不同工作簿中移动或复制工作表时,应该首先打开相关的所有工作簿。通过公式计算得到(1)删除你自己工作簿中的工作表“Sheet2”、“Sheet3”。(2)在你自己工作簿中的工作表“大学英语”之前插入一张新的工作表,并将新工作表更名为“各科成绩表”。3.单元格数据的复制与粘贴请参见“成绩表(样例).xls”中的“各科成绩表”工作表,并参考教材P136,完成对单元格数据的复制与粘贴操作,要求如下:(1)将你自己工作簿中“计算机应用”工作表的“学号”、“姓名”、“性别”的数据复制到“各科成绩表”工作表中的A1~C38单元格中。(2)利用选择性粘贴,将“计算机应用”工作表中的“总成绩”列的数据复制到“各科成绩表”的D1单元格中,并将“总成绩”更改为“计算机应用”。再分别将“应用文写作”列数据、“大学英语”列数据、“高等数学”列数据,复制到“各科成绩表”中的E1、F1以及G1的位置。结果如图8所示。图84.单元格数据的移动请参见图8,并参考教材P139,完成对单元格数据的移动操作,要求如下:在“各科成绩表”工作表中,将各列成绩的排列顺序调整为“大学英语”、“计算机应用”、“高等数学”、“应用文写作”,调整结果如图7所示。5.函数及单元格引用请参见“成绩表(样例).xls”中的“各科成绩表”工作表,并参考教材P139~P142,完成各种函数计算操作,要求如下:(1)在“各科成绩表”工作表中,增加“总分”列,计算每位学生的总分(用SUM函数完成)。试一试:在“计算机应用”工作表中,复制“总成绩”列数据到“各科成绩表”工作表中时,会出现什么结果?(2)在“各科成绩表”工作表中,增加“名次”列,计算每位学生的总分排名。(用RANK函数完成,注意相对引用与绝对引用的区别)注意:双击填充柄后,计算机出的名次对吗?有没有问题,为什么呢???(3)在“各科成绩表”工作表中,修改每位学生的总分排名。思考:你知道什么是“相对引用”、“绝对引用”和“混合引用”吗??(4)在“各科成绩表”工作表中,计算出各门课程的“班级平均分”。提示:使用AVERAGE函数(5)在“各科成绩表”工作表中,计算出各门课程的“班级最高分”及“班级最低分”。提示:使用MAX、MIN函数(6)在“各科成绩表”工作表中,将各门课程的“班级平均分”的结果四舍五入,保留2位小数。提示:使用ROUND和AVERAGE函数各参数之间用逗号分隔圆括号用于括起参数用括起来SUM(number1,number2,...))函数名称说明:所有函数都包含三部分:函数名、参数和圆括号,以求和函数SUM来说明。SUM是函数名称,从名称大略可知该函数的功能及用途是求和。圆括号用来括起参数,在函数中圆括号是不可以省略的。参数是函数在计算时所必须使用的数据。函数的参数不仅是数字类型,它还可以是字符型、逻辑值或是单元格引用,如:SUM(B1,C3),SUM(D2:G2)等。说明:相对引用用“H2”这样的方式来引用单元格是相对引用。相对引用是指当公式在复制或移动时,公式中引用单元格的地址会随着移动的位置自动改变。绝对引用在行号和列号前均加上“$”,如:“$H$2”这样的方式来引用单元格是绝对引用。当公式在复制或移动时,公式中引用单元格的地址不会随着公式的位置而改变。混合引用混合引用是指单元格地址中既有相对引用,也有绝对引用。“$H2”,表示具有绝对列和相对行,当公式在复制或移动时,保持列不变,而行变化;“H$2”表示具有相对列和绝对行,当公式在复制或移动时,保持行不变,而列变化。相关概念:(a)函数的使用(最大值(MAX函数)、最小值(MIN函数)、四舍五入(ROUND函数、)求和(Sum函数)和平均值(Average函数))(b)相对引用、绝对引用混合引用要点回顾1.在不同工作薄中进行工作表的复制或移动操作要注意什么问题?2.使用鼠标移动及复制工作表有何区别?3.什么是选择性粘贴?它有什么用途?4.函数包括哪三部分?函数的参数之间用什么符号分隔?5.相对引用和绝对引用分别应用在什么场合?它们的表达方式有何区别?6.如何在相对引用、绝对引用和混合引用之间切换?问题辨析在下图所示的单元格中,有以下数据,请分析以下四个函数表达式中各返回什么值?为什么?其中哪两个函数表达式返回的值一定相同?哪个函数表达式返回的的值没有意义?请说明原因。(1)=MAX(A1,A2,B1,B2)(2)=MAX(A1+A2+B1+B2)(3)=MAX(A1:B2)(4)=MAX(A1,B2)任务三、成绩表的排序和筛选图91.排序请参见P148~P149,完成对“大学英语”、“高等数学”工作表的操作,要求如下:(1)在“大学英语”工作表中,将“大学英语”列的成绩按升序排列。(2)在“高等数学”工作表中,以“性别”为主要关键字降序排列;以“高等数学”为第二关键