Excel的基本应用制作成绩表

1、Excel的基本应用制作成绩表学习目标掌握Excel中各种输入数据的方法。熟练地进展各种公式计算及简单函数的使用。掌握单元格格式设置、单元格数据的复制与粘贴。工作表的重命名、工作表的复制、移动、删除及插入。熟练掌握单元格的三种引用方法。掌握数据的排序、筛选。2022/8/222第6章 Excel的根本应用制作成绩表目录6.1 制作成绩表案例分析 6.2 实现方法6.2.1 输入单科成绩表 6.2.2 由多工作表数据生成“各科成绩表 6.2.3 成绩表的排序和筛选 6.3 案例总结6.4 课后练习2022/8/223第6章 Excel的根本应用制作成绩表6.1 制作成绩表案例分析任务的提出 班主

2、任王教师要制作?计算机应用?课程的成绩表及“各科成绩表 。解决方案通过数据输入，新建?计算机应用?课程的成绩表。通过对工作表的复制、移动，单元格数据的粘贴等完成“各科成绩表 的制作。2022/8/224第6章 Excel的根本应用制作成绩表6.2.1 输入单科成绩表工作簿的建立在工作表“Sheet1中输入数据 单元格计算 单元格格式设置 工作表的更名 2022/8/225第6章 Excel的根本应用制作成绩表工作簿的建立启动Excel。Excel的工作界面如图下所示。 2022/8/226第6章 Excel的根本应用制作成绩表在工作表“Sheet1中输入数据输入“学号列数据 诸如学号、 、 号

3、码等应视为文本数据，输入时除了在数字前加上西文单引号“ 外，也可以将单元格的格式设置为“文本类型。利用填充柄自动填充，可完成“学号列数据的输入输入“姓名列数据对于屡次重复使用的数据，可以用“自定义序列 实现。输入“性别列数据 如果想在多个单元格中输入一样的数据，只要选择所有需要包含此信息的单元格，在输入数值、文本或公式后，同时按住Ctrl+Enter键，同样的信息会输入到被选择的所有单元格中。2022/8/227第6章 Excel的根本应用制作成绩表单元格计算 Excel中的“公式是指在单元格中执行计算功能的等式。Excel中的所有公式都必须以“号开头，假设无等号，Excel将其理解为正文，所

4、以“是公式中绝对不可缺少的一个运算符。“后面是参与计算的运算数和运算符，每一个运算数可以是常量、单元格或区域引用、单元格名称或函数等。Excel提供了四种类型的运算符：算术运算符、比较运算符、文本运算符和引用运算符。2022/8/228第6章 Excel的根本应用制作成绩表单元格格式设置命令单元格的格式设置，主要集中在“格式“单元格命令中，在“单元格格式对话框中：“数字选项卡，用于设置单元格中的不同数值类型。如：货币类型、文本类型、百分比类型等。“对齐选项卡，用于设置单元格中数据的对齐方式。如：水平，垂直对齐、合并单元格、自动换行等。“字体选项卡，用于设置单元格中数据的“字体、“字形、“字号、

5、“颜色等。“边框选项卡，用于设置单元格或表格的边框线。“图案选项卡，用于设置单元格的底纹。此外也可以通过“格式工具栏实现单元格格式的快速设置。调整“行高或“列宽的命令，那么可通过执行“格式“行命令或“格式“列命令实现。2022/8/229第6章 Excel的根本应用制作成绩表单元格格式设置操作要求将标题字体设置为“幼圆、“加粗、“18号，并使标题在A1:G1列合并及居中。将数据区域所有单元格的字号设置为“10；垂直对齐方式和水平对齐方式都设置为“居中。将表格的外边框设置为双细线，内边框设置为单细线。为表格列标题区域添加浅绿色底纹，并设置垂直对齐方式和水平对齐方式为“居中。将表格列标题行的行高设

6、置为40像素，将“平时成绩列的列宽适当调窄。将列标题“平时成绩、作业设计、期末考试在单元格内分两行显示。将平时成绩、作业设计、期末考试三列的列宽调整为最适合的列宽。通过减少小数位数，将“总成绩的结果以整数位呈现。2022/8/2210第6章 Excel的根本应用制作成绩表工作表的更名 将当前工作表的名称“Sheet1更名为“计算机应用 。双击工作表“Sheet1的标签；或鼠标指向工作表标签，单击鼠标右键，从快捷菜单中选择“重命名命令。当工作表标签出现反白黑底白字时，输入新的工作表名“计算机应用 ，按Enter键确认。2022/8/2211第6章 Excel的根本应用制作成绩表由多工作表数据生成

7、“各科成绩表工作表的复制及移动 工作表的删除与插入 单元格数据的复制与粘贴 单元格数据的移动 函数及单元格引用 2022/8/2212第6章 Excel的根本应用制作成绩表工作表的复制及移动将“大学英语.xls工作簿中的“Sheet1工作表，复制到“成绩表.xls工作簿中的“计算机应用工作表之前，并将复制后的目的工作表“Sheet1更名为“大学英语 。分别翻开“大学英语.xls工作簿和“成绩表.xls工作簿。选择“大学英语.xls工作簿中的“Sheet1工作表作为当前工作表源表，在“Sheet1工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“移动或复制工作表命令，在“移动或复制工作表对话框中

8、进展如右图所示的设置。2022/8/2213第6章 Excel的根本应用制作成绩表工作表的删除与插入 删除“成绩表.xls工作簿中的工作表“Sheet2、“Sheet3 。单击工作表“Sheet2标签，在菜单栏中选择“编辑“删除工作表命令，将“Sheet2工作表从当前工作簿中删除。在“成绩表.xls工作簿中的“大学英语工作表之前插入一张新的工作表。选定“大学英语工作表。在菜单栏中选择“插入“工作表命令，在当前“大学英语工作表之前，添加一张新的工作表，同时新工作表成为当前工作表。2022/8/2214第6章 Excel的根本应用制作成绩表单元格数据的复制与粘贴将“成绩表.xls工作簿中“计算机应

9、用工作表的“学号、“姓名、“性别以及“总成绩列的数据复制到“各科成绩表工作表中 。在“计算机应用工作表中，复制“总成绩列数据到“各科成绩表工作表中时，会出现什么结果？为什么？如何解决？2022/8/2215第6章 Excel的根本应用制作成绩表选择性粘贴的用途对于包含公式的单元格来说，通常具有“公式和“值两种属性。在复制单元格时，可使用“选择性粘贴的方法，任意选择是只粘贴“值属性，还是只粘贴“公式属性。此外使用“选择性粘贴还可以任意选择只粘贴格式、批注、列宽等内容。2022/8/2216第6章 Excel的根本应用制作成绩表选择性粘贴的使用方法利用选择性粘贴，将“计算机应用工作表中“总成绩列的

10、数据复制到“各科成绩表的目标单元格中，并分别将“应用文写作列数据、“大学英语列数据、“高等数学列数据，复制到“各科成绩表中的相应位置 。选择要复制的工作表区域，并执行复制命令。在菜单栏中选择“编辑“选择性粘贴命令，翻开“选择性粘贴对话框。进展如右图所示的设置。2022/8/2217第6章 Excel的根本应用制作成绩表单元格数据的移动调整各列成绩的排列顺序。将各列成绩的排列顺序调整为“大学英语、“计算机应用、“高等数学、“应用文写作 。方法一：选择数据来源列。在菜单栏中选择“编辑“剪切命令。选定目标单元格。在菜单栏中选择“插入“剪切单元格命令。方法二：选择数据来源列。将鼠标指针移至选定区域的边

11、框，当鼠标指针变为 形状后，按住Shift键的同时，拖动该区域到目标位置后，先释放鼠标，再放开Shift键。 2022/8/2218第6章 Excel的根本应用制作成绩表什么是函数所有函数都包含三局部：函数名、参数和圆括号，以求和函数SUM来说明。SUM是函数名称，从名称大概可知该函数的功能及用途是求和。圆括号用来括起参数，在函数中圆括号是不可以省略的。参数是函数在计算时所必须使用的数据。函数的参数不仅是数字类型，它还可以是字符型、逻辑值或是单元格引用。如：SUM(B1,C3)，SUM(D2:G2)等。 圆括号用于括起参数SUMnumber1,number2, .函数名称各参数之间用逗号分隔2

12、022/8/2219第6章 Excel的根本应用制作成绩表函数的使用在“各科成绩表工作表中，增加“名次列，计算每位学生的总分排名。 选择目标单元格，单击编辑栏左边的“插入函数按钮，翻开“插入函数对话框，在“或选择类别下拉列表框中，选择“统计 ，如以下图所示。2022/8/2220第6章 Excel的根本应用制作成绩表函数的使用续在“选择函数列表框中，选择“RANK函数，翻开“函数参数对话框，如以下图所示。 单击此处可获得该函数的帮助描述函数的功能2022/8/2221第6章 Excel的根本应用制作成绩表函数的使用续在对话框中，将插入点定位在第一个参数“Number处，从当前工作表中选择“H2

13、单元格；再将插入点定位在第二个参数“Ref处，从当前工作表中选择单元格区域“H2:H38 选取参数显示插入点所在位置参数的作用这里显示函数计算结果2022/8/2222第6章 Excel的根本应用制作成绩表RANK函数 的功能RANK函数是排定名次的函数，用于返回一个数值在一组数值中的排序，排序时不改变该数值原来的位置。语法格式为：RANK(number,ref,order)共包括三个参数，其中：Number 为需要找到排位的数字。Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。Order 为一数字，指明排位的方式。如果 Order 为 0或省略，按照降序排列；如果

14、 Order 不为零，按照升序排列。 2022/8/2223第6章 Excel的根本应用制作成绩表使用RANK函数时出现的问题名次相同，但总分不同2022/8/2224第6章 Excel的根本应用制作成绩表单元格引用时产生问题的原因RANK函数的第2个参数本来应该是所有学生的总分单元格区域“H2:H38，但到了I5单元格却变成了“H5:H41，而在I6单元格又变成了“H6:H42。说明一旦公式所在的位置发生了变化，公式中的单元格引用也会有相应的变化。这就是“相对引用，是Excel默认的引用方式。 H5:H41 H6:H42 2022/8/2225第6章 Excel的根本应用制作成绩表如何解决在

15、RANK函数中，第2个参数的单元格区域“H2:H38表示所有学生的总分，它不应该随着单元格的复制而变化。所以这个区域不能使用“相对引用。要保证在复制公式时，单元格区域“H2:H38固定不变，就必须使用“绝对引用！“绝对引用总是指向固定的单元格或单元格区域，无论公式怎么复制也不会改变引用位置。使用“绝对引用的方法很简单，只要在“相对引用的根底上，在字母和数字之前分别加上美元符号“$就行了。例如：把“H2:H38改为“$H$2:$H$38。 2022/8/2226第6章 Excel的根本应用制作成绩表解决方法$H$2:$H$38 $H$2:$H$38 复制公式时，当公式中使用的单元格引用需要随着所

16、在位置的不同而改变时，应该使用“相对引用，如：RANK函数中的第1个参数；复制公式后，分别为H2，H5，H6；当公式中使用的单元格引用不随所在位置而改变时，应该使用“绝对引用，如：RANK函数中的第2个参数，复制公式后，仍然为“$H$2:$H$38。 2022/8/2227第6章 Excel的根本应用制作成绩表单元格引用单元格引用是指公式中指明的一个单元格或一组单元格。公式中对单元格的引用分为相对引用、绝对引用和混合引用。相对引用用“H2这样的方式来引用单元格是相对引用。相对引用是指当公式在复制或移动时，公式中引用单元格的地址会随着移动的位置自动改变。绝对引用在行号和列号前均加上“$，如：“$

17、H$2这样的方式来引用单元格是绝对引用。当公式在复制或移动时，公式中引用单元格的地址不会随着公式的位置而改变。混合引用混合引用是指单元格地址中既有相对引用，也有绝对引用。“$H2，表示具有绝对列和相对行，当公式在复制或移动时，保持列不变，而行变化；“H$2表示具有相对列和绝对行，当公式在复制或移动时，保持行不变，而列变化。2022/8/2228第6章 Excel的根本应用制作成绩表其他函数的使用在“各科成绩表工作表中，计算出各门课程的“班级平均分。用AVERAGE 函数完成在“各科成绩表工作表中，计算出各门课程的“班级最高分及“班级最低分。分别用MAX及MIN函数完成在“各科成绩表工作表中，将

18、各门课程的“班级平均分的结果四舍五入，保存2位小数。用ROUND与AVERAGE 的嵌套函数完成。2022/8/2229第6章 Excel的根本应用制作成绩表嵌套函数嵌套函数，就是指在某些情况下，将某个函数作为另一函数的参数使用。例如：AVERAGE 函数就被当作并ROUND函数的第一个参数使用，其意义就是：将平均值的计算结果进展四舍五入，保存两位小数。将函数AVERAGE(D2:D38)，作为函数ROUND的第一个参数2022/8/2230第6章 Excel的根本应用制作成绩表ROUND函数 ROUND函数返回某个数字按指定位数四舍五入后的数字。语法格式为：ROUND(number,num_

19、digits)共包括两个参数：Number 需要进展四舍五入的数字；Num_digits 指定的位数，按此位数进展四舍五入。如果num_digits大于0，那么四舍五入到指定的小数位。如：如果num_digits等于0，那么四舍五入到最接近的整数。如：ROUND(35.385,0)=35如果num_digits小于0，那么在小数点左侧进展四舍五入。如：ROUND(35.385,-1)=402022/8/2231第6章 Excel的根本应用制作成绩表成绩表的排序和筛选 数据清单 排序 自动筛选 高级筛选 2022/8/2232第6章 Excel的根本应用制作成绩表数据清单数据清单是指包含相关数据

20、的一系列工作表的数据行。可以理解成工作表中的一张二维表格，例如，我们在前面建立的成绩表等。可使用以下数据清单元素来组织数据。数据清单中的列是数据库中的字段。数据清单中的列标题是数据库中的字段名称。数据清单中的每一行对应数据库中的一条记录。数据清单应该尽量满足以下条件：每一列必须要有列名，且每一列中数据必须是一样类型的。防止在一个工作表中有多个数据清单。数据清单与其他数据间至少留出一个空白列和一个空白行。2022/8/2233第6章 Excel的根本应用制作成绩表排序排序并不是针对某一列进展的，而是以某一列的大小为顺序，对所有的记录进展排序。也就是说，无论怎么排序，每一条记录的内容都不容改变，改

21、变的只是它在数据清单中显示的位置。 排序方式有升序和降序两种。在按升序排序时，Excel使用如下次序：数字从最小的负数到最大的正数进展排序；文本按09、空格、各种符号、AZ的次序排序；空白单元格始终排在最后。在按降序排序时，除了空白单元格总是在最后外，其他的排序次序反转。 2022/8/2234第6章 Excel的根本应用制作成绩表排序操作在“大学英语工作表中，将“大学英语列的成绩按升序排列。略在“高等数学工作表中，以“性别为主要关键字降序排列；以“高等数学为第二关键字降序排列；以“姓名为第三关键字升序排列。 单击数据清单中的任一单元格。在菜单栏中选择“数据“排序命令，翻开“排序对话框。 进展

22、如右图所示的设置。2022/8/2235第6章 Excel的根本应用制作成绩表自动筛选在“自动筛选工作表中，筛选出同时满足以下四个条件的数据记录：“性别为“女；姓“黄或姓名中最后一个字为“静；“计算机应用的成绩在80分90分之间；“名次在前8名。 在菜单栏中选择“数据“筛选“自动筛选命令，此时标题列中自动出现下拉箭头。 单击各列的下拉箭头进展相应设置。要点提示在数据清单中进展屡次筛选，下一次筛选的对象是上一次筛选的结果，最后的筛选结果受所有筛选条件的影响，它们之间的逻辑关系是“与的关系。如果要取消对所有列的筛选，只要执行“数据“筛选“全部显示命令；如果要撤消数据清单中的自动筛选箭头，并取消所有

23、的自动筛选设置，只要重新执行“数据“筛选“自动筛选命令。2022/8/2236第6章 Excel的根本应用制作成绩表高级筛选自动筛选无法实现多个字段之间的“或运算。在这种情况下，只有使用高级筛选才能完成。在高级筛选中，主要是定义以下三个单元格区域：定义查询的列表区域；定义查询的条件区域；定义存放查询结果的区域。其中条件区域的定义最为复杂，条件的设置必须遵循以下原那么：条件区域与数据清单区域之间必须有空白行或空白列隔开。条件区域至少应该有两行，第一行用来放置字段名，下面的行放置筛选条件。条件区域的字段名必须与数据清单中的字段名完全一致,最好通过复制得到。“与关系的条件必须出现在同一行；“或关系的

24、条件不能出现在同一行 2022/8/2237第6章 Excel的根本应用制作成绩表高级筛选操作在“高级筛选工作表中，筛选出总分小于200分的女生或总分大于等于300分的男生的记录。 构造筛选条件在进展高级筛选之前，首先必须指定一 个条件区域。条件区域与数据清单之 间至少应留有一 个空白行或一 个空白列分开。如图1所示。执行高级筛选 单击数据清单中的任一 单元格 。在菜单栏中选择“数据“筛选“高级筛选命令，翻开“高级筛选对话框,设置结果参见图2。图1图22022/8/2238第6章 Excel的根本应用制作成绩表要点回忆 在不同工作薄中进展工作表的复制或移动操作要注意什么问题？使用鼠标移动及复制工作表有何区别？什么是选择性粘贴？它有什么用途？函数包括哪三局部？函数的参数之间用什么符号分隔？相对引用和绝对引用分别应用在什么场合？它们的表达方式有何区别？如何在相对引用、绝对引用和混合引用之间切换？2022/8/2239第6章 Excel的根本应用制作成绩表