搜索
Excel基本应用—制作成绩表22020/3/3学习目标掌握Excel中各种输入数据的方法。熟练地进行各种公式计算及简单函数的使用。掌握单元格格式设置、单元格数据的复制与粘贴。工作表的重命名、工作表的复制、移动、删除及插入。熟练掌握单元格的三种引用方法。掌握数据的排序、筛选。32020/3/3目录6.1制作成绩表案例分析6.2实现方法6.2.1输入单科成绩表6.2.2由多工作表数据生成“各科成绩表”6.2.3成绩表的排序和筛选6.3案例总结6.4课后练习42020/3/36.1制作成绩表案例分析任务的提出班主任王老师要制作《计算机基础》课程的成绩表及“各科成绩表”。解决方案通过数据输入，新建《计算机基础》课程的成绩表。通过对工作表的复制、移动，单元格数据的粘贴等完成“各科成绩表”的制作。52020/3/36.2.1输入单科成绩表工作簿的建立在工作表“Sheet1”中输入数据单元格计算单元格格式设置工作表的更名62020/3/3工作簿的建立启动Excel。Excel的工作界面如图下所示。第6章Excel的基本应用—制作成绩72020/3/3在工作表“Sheet1”中输入数据输入“学号”列数据诸如学号、邮编、电话号码等应视为文本数据，输入时除了在数字前加上西文单引号“’”外，也可以将单元格的格式设置为“文本”类型。利用填充柄自动填充，可完成“学号”列数据的输入输入“姓名”列数据对于多次重复使用的数据，可以用“自定义序列”实现。输入“性别”列数据如果想在多个单元格中输入相同的数据，只要选择所有需要包含此信息的单元格，在输入数值、文本或公式后，同时按住Ctrl+Enter键，同样的信息会输入到被选择的所有单元格中。82020/3/3单元格计算Excel中的“公式”是指在单元格中执行计算功能的等式。Excel中的所有公式都必须以“＝”号开头，若无等号，Excel将其理解为正文，所以“＝”是公式中绝对不可缺少的一个运算符。“＝”后面是参与计算的运算数和运算符，每一个运算数可以是常量、单元格或区域引用、单元格名称或函数等。Excel提供了四种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。92020/3/3单元格格式设置命令单元格的格式设置，主要集中在“格式”→“单元格”命令中，在“单元格格式”对话框中：“数字”选项卡，用于设置单元格中的不同数值类型。如：货币类型、文本类型、百分比类型等。“对齐”选项卡，用于设置单元格中数据的对齐方式。如：水平，垂直对齐、合并单元格、自动换行等。“字体”选项卡，用于设置单元格中数据的“字体”、“字形”、“字号”、“颜色”等。“边框”选项卡，用于设置单元格或表格的边框线。“图案”选项卡，用于设置单元格的底纹。此外也可以通过“格式”工具栏实现单元格格式的快速设置。调整“行高”或“列宽”的命令，则可通过执行“格式”→“行”命令或“格式”→“列”命令实现。102020/3/3单元格格式设置操作要求将标题字体设置为“幼圆”、“加粗”、“18”号，并使标题在A1:G1列合并及居中。将数据区域所有单元格的字号设置为“10”；垂直对齐方式和水平对齐方式都设置为“居中”。将表格的外边框设置为双细线，内边框设置为单细线。为表格列标题区域添加浅绿色底纹，并设置垂直对齐方式和水平对齐方式为“居中”。将表格列标题行的行高设置为30.00（40像素），将“平时成绩”列的列宽适当调窄。将列标题“平时成绩、作业设计、期末考试”在单元格内分两行显示。将平时成绩、作业设计、期末考试三列的列宽调整为最适合的列宽。通过减少小数位数，将“总成绩”的结果以整数位呈现。112020/3/3工作表的更名将当前工作表的名称“Sheet1”更名为“计算机应用”。双击工作表“Sheet1”的标签；或鼠标指向工作表标签，单击鼠标右键，从快捷菜单中选择“重命名”命令。当工作表标签出现反白（黑底白字）时，输入新的工作表名“计算机应用”，按Enter键确认。122020/3/3由多工作表数据生成“各科成绩表”工作表的复制及移动工作表的删除与插入单元格数据的复制与粘贴单元格数据的移动函数及单元格引用132020/3/3工作表的复制及移动将“大学英语.xls”工作簿中的“Sheet1”工作表，复制到“成绩表.xls”工作簿中的“计算机应用”工作表之前，并将复制后的目的工作表“Sheet1”更名为“大学英语”。分别打开“大学英语.xls”工作簿和“成绩表.xls”工作簿。选择“大学英语.xls”工作簿中的“Sheet1”工作表作为当前工作表（源表），在“Sheet1”工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“移动或复制工作表”命令，在“移动或复制工作表”对话框中进行如右图所示的设置。142020/3/3工作表的删除与插入删除“成绩表.xls”工作簿中的工作表“Sheet2”、“Sheet3”。单击工作表“Sheet2”标签，在菜单栏中选择“编辑”→“删除工作表”命令，将“Sheet2”工作表从当前工作簿中删除。在“成绩表.xls”工作簿中的“大学英语”工作表之前插入一张新的工作表。选定“大学英语”工作表。在菜单栏中选择“插入”→“工作表”命令，在当前“大学英语”工作表之前，添加一张新的工作表，同时新工作表成为当前工作表。152020/3/3单元格数据的复制与粘贴将“成绩表.xls”工作簿中“计算机应用”工作表的“学号”、“姓名”、“性别”以及“总成绩”列的数据复制到“各科成绩表”工作表中。在“计算机应用”工作表中，复制“总成绩”列数据到“各科成绩表”工作表中时，会出现什么结果？为什么？如何解决？162020/3/3选择性粘贴的用途对于包含公式的单元格来说，通常具有“公式”和“值”两种属性。在复制单元格时，可使用“选择性粘贴”的方法，任意选择是只粘贴“值”属性，还是只粘贴“公式”属性。此外使用“选择性粘贴”还可以任意选择只粘贴格式、批注、列宽等内容。172020/3/3选择性粘贴的使用方法利用选择性粘贴，将“计算机应用”工作表中“总成绩”列的数据复制到“各科成绩表”的目标单元格中，并分别将“应用文写作”列数据、“大学英语”列数据、“高等数学”列数据，复制到“各科成绩表”中的相应位置。选择要复制的工作表区域，并执行复制命令。在菜单栏中选择“编辑”→“选择性粘贴”命令，打开“选择性粘贴”对话框。进行如右图所示的设置。182020/3/3单元格数据的移动调整各列成绩的排列顺序。将各列成绩的排列顺序调整为“大学英语”、“计算机应用”、“高等数学”、“应用文写作”。方法一：选择数据来源列。在菜单栏中选择“编辑”→“剪切”命令。选定目标单元格。在菜单栏中选择“插入”→“剪切单元格”命令。方法二：选择数据来源列。将鼠标指针移至选定区域的边框，当鼠标指针变为形状后，按住Shift键的同时，拖动该区域到目标位置后，先释放鼠标，再放开Shift键。192020/3/3什么是函数所有函数都包含三部分：函数名、参数和圆括号，以求和函数SUM来说明。SUM是函数名称，从名称大略可知该函数的功能及用途是求和。圆括号用来括起参数，在函数中圆括号是不可以省略的。参数是函数在计算时所必须使用的数据。函数的参数不仅是数字类型，它还可以是字符型、逻辑值或是单元格引用。如：SUM(B1,C3)，SUM(D2:G2)等。圆括号用于括起参数SUM（number1,number2,...）函数名称各参数之间用逗号分隔202020/3/3函数的使用在“各科成绩表”工作表中，增加“名次”列，计算每位学生的总分排名。选择目标单元格，单击编辑栏左边的“插入函数”按钮，打开“插入函数”对话框，在“或选择类别”下拉列表框中，选择“统计”，如下图所示。212020/3/3函数的使用（续）在“选择函数”列表框中，选择“RANK”函数，打开“函数参数”对话框，如下图所示。单击此处可获得该函数的帮助描述函数的功能222020/3/3函数的使用（续）在对话框中，将插入点定位在第一个参数“Number”处，从当前工作表中选择“H2”单元格；再将插入点定位在第二个参数“Ref”处，从当前工作表中选择单元格区域“H2:H38”选取参数显示插入点所在位置参数的作用这里显示函数计算结果232020/3/3RANK函数的功能RANK函数是排定名次的函数，用于返回一个数值在一组数值中的排序，排序时不改变该数值原来的位置。语法格式为：RANK(number,ref,order)共包括三个参数，其中：Number为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字，指明排位的方式。如果Order为0或省略，按照降序排列；如果Order不为零，按照升序排列。242020/3/3使用RANK函数时出现的问题名次相同，但总分不同252020/3/3单元格引用时产生问题的原因RANK函数的第2个参数本来应该是所有学生的总分单元格区域“H2:H38”，但到了I5单元格却变成了“H5:H41”，而在I6单元格又变成了“H6:H42”。说明一旦公式所在的位置发生了变化，公式中的单元格引用也会有相应的变化。这就是“相对引用”，是Excel默认的引用方式。H5:H41H6:H42262020/3/3如何解决在RANK函数中，第2个参数的单元格区域“H2:H38”表示所有学生的总分，它不应该随着单元格的复制而变化。所以这个区域不能使用“相对引用”。要保证在复制公式时，单元格区域“H2:H38”固定不变，就必须使用“绝对引用”！“绝对引用”总是指向固定的单元格或单元格区域，无论公式怎么复制也不会改变引用位置。使用“绝对引用”的方法很简单，只要在“相对引用”的基础上，在字母和数字之前分别加上美元符号“$”就行了。例如：把“H2:H38”改为“$H$2:$H$38”。272020/3/3解决方法$H$2:$H$38$H$2:$H$38复制公式时，当公式中使用的单元格引用需要随着所在位置的不同而改变时，应该使用“相对引用”，如：RANK函数中的第1个参数；复制公式后，分别为H2，……H5，H6；当公式中使用的单元格引用不随所在位置而改变时，应该使用“绝对引用”，如：RANK函数中的第2个参数，复制公式后，仍然为“$H$2:$H$38”。282020/3/3单元格引用单元格引用是指公式中指明的一个单元格或一组单元格。公式中对单元格的引用分为相对引用、绝对引用和混合引用。相对引用用“H2”这样的方式来引用单元格是相对引用。相对引用是指当公式在复制或移动时，公式中引用单元格的地址会随着移动的位置自动改变。绝对引用在行号和列号前均加上“$”，如：“$H$2”这样的方式来引用单元格是绝对引用。当公式在复制或移动时，公式中引用单元格的地址不会随着公式的位置而改变。混合引用混合引用是指单元格地址中既有相对引用，也有绝对引用。“$H2”，表示具有绝对列和相对行，当公式在复制或移动时，保持列不变，而行变化；“H$2”表示具有相对列和绝对行，当公式在复制或移动时，保持行不变，而列变化。292020/3/3其他函数的使用在“各科成绩表”工作表中，计算出各门课程的“班级平均分”。用AVERAGE函数完成在“各科成绩表”工作表中，计算出各门课程的“班级最高分”及“班级最低分”。分别用MAX及MIN函数完成在“各科成绩表”工作表中，将各门课程的“班级平均分”的结果四舍五入，保留2位小数。用ROUND与AVERAGE的嵌套函数完成。302020/3/3嵌套函数嵌套函数，就是指在某些情况下，将某个函数作为另一函数的参数使用。例如：AVERAGE函数就被当作并ROUND函数的第一个参数使用，其意义就是：将平均值的计算结果进行四舍五入，保留两位小数。将函数AVERAGE(D2:D38)，作为函数ROUND的第一个参数312020/3/3ROUND函数ROUND函数返回某个数字按指定位数四舍五入后的数字。语法格式为：ROUND(number,num_digits)共包括两个参数：Number需要进行四舍五入的数字；Num_di