Transcript Excel的基本应用—制作成绩表

第七章 Excel基础应用
----制
作成绩单
焦作大学信息工程学院
室
计算机基础教研
学习目标
掌握Excel中各种输入数据的方法。
熟练地进行各种公式计算及简单函数的使用。
掌握单元格格式设置、单元格数据的复制与粘
贴。
工作表的重命名、工作表的复制、移动、删除
及插入。
熟练掌握单元格的三种引用方法。
掌握数据的排序、筛选。
2015/4/13
第7章 Excel的基本应用—制作成绩表
2
目录
7.1 制作成绩表案例分析
7.2 实现方法
7.2.1 输入单科成绩表
7.2.2 由多工作表数据生成“各科成绩表”
7.2.3 成绩表的排序和筛选
7.3 案例总结
7.4 课后练习
2015/4/13
第7章 Excel的基本应用—制作成绩表
3
7.1 制作成绩表案例分析
任务的提出
班主任王老师要制作《计算机应用》课程的
成绩表及“各科成绩表” 。
解决方案
通过数据输入，新建《计算机应用》课程的成绩表。
通过对工作表的复制、移动，单元格数据的粘贴等完成
“各科成绩表” 的制作。
2015/4/13
第7章 Excel的基本应用—制作成绩表
4
7.2.1 输入单科成绩表
工作簿的建立
在工作表“Sheet1”中输入数据
单元格计算
单元格格式设置
工作表的更名
2015/4/13
第7章 Excel的基本应用—制作成绩表
5
工作簿的建立
启动Excel。
Excel的工作界面如图下所示。
2015/4/13
第7章 Excel的基本应用—制作成绩表
6
在工作表“Sheet1”中输入数据
输入“学号”列数据
诸如学号、邮编、电话号码等应视为文本数据，输入时除
了在数字前加上西文单引号“ ’ ”外，也可以将单元
格的格式设置为“文本”类型。
利用填充柄自动填充，可完成“学号”列数据的输入
输入“姓名”列数据
对于多次重复使用的数据，可以用“自定义序列” 实现。
输入“性别”列数据
如果想在多个单元格中输入相同的数据，只要选择所有需
要包含此信息的单元格，在输入数值、文本或公式后，
同时按住Ctrl+Enter键，同样的信息会输入到被选择的
所有单元格中。
2015/4/13
第7章 Excel的基本应用—制作成绩表
7
单元格计算
Excel中的“公式”是指在单元格中执行计算功能的
等式。
Excel中的所有公式都必须以“＝”号开头，若无等
号，Excel将其理解为正文，所以“＝”是公式中绝
对不可缺少的一个运算符。
“＝”后面是参与计算的运算数和运算符，每一个运
算数可以是常量、单元格或区域引用、单元格名
称或函数等。
Excel提供了四种类型的运算符：算术运算符、比
较运算符、文本运算符和引用运算符。
2015/4/13
第7章 Excel的基本应用—制作成绩表
8
单元格格式设置命令
单元格的格式设置，主要集中在“格式”→“单元格”命令中，在“单
元格格式”对话框中：
“数字”选项卡，用于设置单元格中的不同数值类型。如：货币类型、
文本类型、百分比类型等。
“对齐”选项卡，用于设置单元格中数据的对齐方式。如：水平，垂直
对齐、合并单元格、自动换行等。
“字体”选项卡，用于设置单元格中数据的“字体”、“字形”、“字
号”、“颜色”等。
“边框”选项卡，用于设置单元格或表格的边框线。
“图案”选项卡，用于设置单元格的底纹。
此外也可以通过“格式”工具栏实现单元格格式的快速设置。
调整“行高”或“列宽”的命令，则可通过执行“格式”→“行”命令或
“格式”→“列”命令实现。
2015/4/13
第7章 Excel的基本应用—制作成绩表
9
单元格格式设置操作要求
将标题字体设置为“幼圆”、“加粗”、“18”号，并
使标题在A1:G1列合并及居中。
将数据区域所有单元格的字号设置为“10”；垂直对齐
方式和水平对齐方式都设置为“居中”。
将表格的外边框设置为双细线，内边框设置为单细线。
为表格列标题区域添加浅绿色底纹，并设置垂直对齐方
式和水平对齐方式为“居中”。
将表格列标题行的行高设置为30.00（40像素），将
“平时成绩”列的列宽适当调窄。
将列标题“平时成绩、作业设计、期末考试”在单元格
内分两行显示。
将平时成绩、作业设计、期末考试三列的列宽调整为最
适合的列宽。
通过减少小数位数，将“总成绩”的结果以整数位呈现。
2015/4/13
第7章 Excel的基本应用—制作成绩表
10
工作表的更名
将当前工作表的名称“Sheet1”更名为“计算机应用” 。
双击工作表“Sheet1”的标签；或鼠标指向工作表标签，
单击鼠标右键，从快捷菜单中选择“重命名”命令。
当工作表标签出现反白（黑底白字）时，输入新的工作
表名“计算机应用” ，按Enter键确认。
2015/4/13
第7章 Excel的基本应用—制作成绩表
11
由多工作表数据生成“各科成绩表”
工作表的复制及移动
工作表的删除与插入
单元格数据的复制与粘贴
单元格数据的移动
函数及单元格引用
2015/4/13
第7章 Excel的基本应用—制作成绩表
12
工作表的复制及移动
将“大学英语.xls”工作簿中的“Sheet1”
工作表，复制到“成绩表.xls”工作簿中
的“计算机应用”工作表之前，并将复
制后的目的工作表“Sheet1”更名为“大
学英语” 。
分别打开“大学英语.xls”工作簿和
“成绩表.xls”工作簿。
选择“大学英语.xls”工作簿中的
“Sheet1”工作表作为当前工作表（源
表），在“Sheet1”工作表标签上单击
鼠标右键，在弹出的快捷菜单中选择
“移动或复制工作表”命令，在“移
动或复制工作表”对话框中进行如右
图所示的设置。
2015/4/13
第7章 Excel的基本应用—制作成绩表
13
工作表的删除与插入
删除“成绩表.xls”工作簿中的工作表“Sheet2”、
“Sheet3” 。
单击工作表“Sheet2”标签，在菜单栏中选择“编
辑”→“删除工作表”命令，将“Sheet2”工作表从当
前工作簿中删除。
在“成绩表.xls”工作簿中的“大学英语”工作表之前
插入一张新的工作表。
选定“大学英语”工作表。在菜单栏中选择“插
入”→“工作表”命令，在当前“大学英语”工作表之
前，添加一张新的工作表，同时新工作表成为当前工作
表。
2015/4/13
第7章 Excel的基本应用—制作成绩表
14
单元格数据的复制与粘贴
将“成绩表.xls”工作簿中“计算机应用”工作表的“学
号”、“姓名”、“性别”以及“总成绩”列的数据复制到
“各科成绩表”工作表中 。
在“计算机应用”工作表中，复制
“总成绩”列数据到“各科成绩表”
工作表中时，会出现什么结果？为什
么？如何解决？
2015/4/13
第7章 Excel的基本应用—制作成绩表
15
选择性粘贴的用途
对于包含公式的单元格来说，通常具有“公式”和
“值”两种属性。在复制单元格时，可使用“选择性
粘贴”的方法，任意选择是只粘贴“值”属性，还是
只粘贴“公式”属性。
此外使用“选择性粘贴”还可以任意选择只粘贴格式、
批注、列宽等内容。
2015/4/13
第7章 Excel的基本应用—制作成绩表
16
选择性粘贴的使用方法
利用选择性粘贴，将“计算机应
用”工作表中“总成绩”列的数据
复制到“各科成绩表”的目标单
元格中，并分别将“应用文写作”
列数据、“大学英语”列数据、
“高等数学”列数据，复制到“各
科成绩表”中的相应位置 。
选择要复制的工作表区域，并
执行复制命令。
在菜单栏中选择“编
辑”→“选择性粘贴”命令，
打开“选择性粘贴”对话框。
进行如右图所示的设置。
2015/4/13
第7章 Excel的基本应用—制作成绩表
17
单元格数据的移动
调整各列成绩的排列顺序。将各列成绩的排列顺
序调整为“大学英语”、“计算机应用”、“高等数学”、
“应用文写作” 。
方法一：
选择数据来源列。
在菜单栏中选择“编辑”→“剪切”命令。
选定目标单元格。
在菜单栏中选择“插入”→“剪切单元格”命令。
方法二：
选择数据来源列。
将鼠标指针移至选定区域的边框，当鼠标指针变为
形状
后，按住Shift键的同时，拖动该区域到目标位置后，先释放
鼠标，再放开Shift键。
2015/4/13
第7章 Excel的基本应用—制作成绩表
18
什么是函数
函数名称 圆括号用于括起参数
SUM（number1,number2, ...）
各参数之间用逗号分隔
所有函数都包含三部分：函数名、参数和圆括号，以求和函
数SUM来说明。
SUM是函数名称，从名称大略可知该函数的功能及用途是求和。
圆括号用来括起参数，在函数中圆括号是不可以省略的。
参数是函数在计算时所必须使用的数据。函数的参数不仅是数字类型，
它还可以是字符型、逻辑值或是单元格引用。
如：SUM(B1,C3)，SUM(D2:G2)等。
2015/4/13
第7章 Excel的基本应用—制作成绩表
19
函数的使用
在“各科成绩表”工作表中，增加“名次”列，计算每
位学生的总分排名。
选择目标单元格，单击编辑栏左边的“插入函数”按钮，
打开“插入函数”对话框，在“或选择类别”下拉列表
框中，选择“统计” ，如下图所示。
2015/4/13
第7章 Excel的基本应用—制作成绩表
20
函数的使用（续）
在“选择函数”列表框中，选择“RANK”函数，打开
“函数参数”对话框，如下图所示。
描述函数
的功能
单击此处可获得
该函数的帮助
2015/4/13
第7章 Excel的基本应用—制作成绩表
21
函数的使用（续）
在对话框中，将插入点定位在第一个参数“Number”处，
从当前工作表中选择“H2”单元格；再将插入点定位在
第二个参数“Ref”处，从当前工作表中选择单元格区域
“H2:H38”
选取参数
显示插入点
所在位置参
数的作用
这里显示函
数计算结果
2015/4/13
第7章 Excel的基本应用—制作成绩表
22
RANK函数 的功能
RANK函数是排定名次的函数，用于返回一个数
值在一组数值中的排序，排序时不改变该数值原
来的位置。
语法格式为：RANK(number,ref,order)
共包括三个参数，其中：
Number 为需要找到排位的数字。
Ref 为数字列表数组或对数字列表的引用。Ref 中的非
数值型参数将被忽略。
Order 为一数字，指明排位的方式。
如果 Order 为 0或省略，按照降序排列；如果 Order
不为零，按照升序排列。
2015/4/13
第7章 Excel的基本应用—制作成绩表
23
使用RANK函数时出现的问题
名次相同，但总分不同
2015/4/13
第7章 Excel的基本应用—制作成绩表
24
单元格引用时产生问题的原因
H5:H41
H6:H42
RANK函数的第2个参数本来应该是所有学生的总分单元格区
域“H2:H38”，但到了I5单元格却变成了“H5:H41”，而在I6
单元格又变成了“H6:H42”。
说明一旦公式所在的位置发生了变化，公式中的单元格引用
也会有相应的变化。这就是“相对引用”，是Excel默认的
引用方式。
2015/4/13
第7章 Excel的基本应用—制作成绩表
25
如何解决
在RANK函数中，第2个参数的单元格区域
“H2:H38”表示所有学生的总分，它不应该随着单
元格的复制而变化。所以这个区域不能使用“相对
引用”。要保证在复制公式时，单元格区域
“H2:H38”固定不变，就必须使用“绝对引用”！
“绝对引用”总是指向固定的单元格或单元格区域，
无论公式怎么复制也不会改变引用位置。使用“绝
对引用”的方法很简单，只要在“相对引用”的基础
上，在字母和数字之前分别加上美元符号“$”就行
了。例如：把“H2:H38”改为“$H$2:$H$38”。
2015/4/13
第7章 Excel的基本应用—制作成绩表
26
解决方法
$H$2:$H$38
$H$2:$H$38
复制公式时，当公式中使用的单元格引用需要随着所在位置
的不同而改变时，应该使用“相对引用”，如：RANK函数中的
第1个参数；复制公式后，分别为H2，……H5，H6；
当公式中使用的单元格引用不随所在位置而改变时，应该使
用“绝对引用”，如：RANK函数中的第2个参数，复制公式后，
仍然为“$H$2:$H$38”。
2015/4/13
第7章 Excel的基本应用—制作成绩表
27
单元格引用
单元格引用是指公式中指明的一个单元格或一组单元格。公
式中对单元格的引用分为相对引用、绝对引用和混合引用。
相对引用
用“H2”这样的方式来引用单元格是相对引用。相对引用是指当公式
在复制或移动时，公式中引用单元格的地址会随着移动的位置自动
改变。
绝对引用
在行号和列号前均加上“$”，如：“$H$2”这样的方式来引用单元格
是绝对引用。当公式在复制或移动时，公式中引用单元格的地址不
会随着公式的位置而改变。
混合引用
混合引用是指单元格地址中既有相对引用，也有绝对引用。“$H2”，
表示具有绝对列和相对行，当公式在复制或移动时，保持列不变，
而行变化；“H$2”表示具有相对列和绝对行，当公式在复制或移动
时，保持行不变，而列变化。
2015/4/13
第7章 Excel的基本应用—制作成绩表
28
其他函数的使用
在“各科成绩表”工作表中，计算出各门课程的“班
级平均分”。
用AVERAGE 函数完成
在“各科成绩表”工作表中，计算出各门课程的“班
级最高分”及“班级最低分”。
分别用MAX及MIN函数完成
在“各科成绩表”工作表中，将各门课程的“班级平
均分”的结果四舍五入，保留2位小数。
用ROUND与AVERAGE 的嵌套函数完成。
2015/4/13
第7章 Excel的基本应用—制作成绩表
29
嵌套函数
嵌套函数，就是指在某些情况下，将某个函数作为另一函
数的参数使用。例如：AVERAGE 函数就被当作并
ROUND函数的第一个参数使用，其意义就是：将平均值
的计算结果进行四舍五入，保留两位小数。
将函数AVERAGE(D2:D38)，作
为函数ROUND的第一个参数
2015/4/13
第7章 Excel的基本应用—制作成绩表
30
ROUND函数
ROUND函数返回某个数字按指定位数四舍五入后
的数字。
语法格式为：ROUND(number,num_digits)
共包括两个参数：
Number 需要进行四舍五入的数字；
Num_digits 指定的位数，按此位数进行四舍五入。
如果num_digits大于0，则四舍五入到指定的小数位。如：
ROUND(35.385,1)=35.4
如果num_digits等于0，则四舍五入到最接近的整数。如：
ROUND(35.385,0)=35
如果num_digits小于0，则在小数点左侧进行四舍五入。如：
ROUND(35.385,-1)=40
2015/4/13
第7章 Excel的基本应用—制作成绩表
31
成绩表的排序和筛选
数据清单
排序
自动筛选
高级筛选
2015/4/13
第7章 Excel的基本应用—制作成绩表
32
数据清单
数据清单是指包含相关数据的一系列工作表的数据行。
可以理解成工作表中的一张二维表格，例如，我们在
前面建立的成绩表等。
可使用下列数据清单元素来组织数据。
数据清单中的列是数据库中的字段。
数据清单中的列标题是数据库中的字段名称。
数据清单中的每一行对应数据库中的一条记录。
数据清单应该尽量满足下列条件：
每一列必须要有列名，且每一列中数据必须是相同类型的。
避免在一个工作表中有多个数据清单。
数据清单与其他数据间至少留出一个空白列和一个空白行。
2015/4/13
第7章 Excel的基本应用—制作成绩表
33
排序
排序并不是针对某一列进行的，而是以某一列的
大小为顺序，对所有的记录进行排序。也就是说，
无论怎么排序，每一条记录的内容都不容改变，
改变的只是它在数据清单中显示的位置。
排序方式有升序和降序两种。在按升序排序时，
Excel使用如下次序：数字从最小的负数到最大的
正数进行排序；文本按0～9、空格、各种符号、
A～Z的次序排序；空白单元格始终排在最后。在
按降序排序时，除了空白单元格总是在最后外，
其他的排序次序反转。
2015/4/13
第7章 Excel的基本应用—制作成绩表
34
排序操作
在“大学英语”工作表中，将
“大学英语”列的成绩按升序
排列。（略）
在“高等数学”工作表中，以
“性别”为主要关键字降序排
列；以“高等数学”为第二关
键字降序排列；以“姓名”为
第三关键字升序排列。
单击数据清单中的任一单
元格。
在菜单栏中选择“数
据”→“排序”命令，打
开“排序”对话框。 进行
如右图所示的设置。
2015/4/13
第7章 Excel的基本应用—制作成绩表
35
自动筛选
在“自动筛选”工作表中，筛选出同时满足以下四个条件的数
据记录：“性别”为“女”；姓“黄”或姓名中最后一个字为“静”；
“计算机应用”的成绩在80分～90分之间；“名次”在前8名。
在菜单栏中选择“数据”→“筛选”→“自动筛选”命令，
此时标题列中自动出现下拉箭头。
单击各列的下拉箭头进行相应设置。
要点提示
在数据清单中进行多次筛选，下一次筛选的对象是上一次筛
选的结果，最后的筛选结果受所有筛选条件的影响，它们之
间的逻辑关系是“与”的关系。
如果要取消对所有列的筛选，只要执行“数据”→“筛
选”→“全部显示”命令；如果要撤消数据清单中的自动筛
选箭头，并取消所有的自动筛选设置，只要重新执行“数
据”→“筛选”→“自动筛选”命令。
2015/4/13
第7章 Excel的基本应用—制作成绩表
36
高级筛选
自动筛选无法实现多个字段之间的“或”运算。在这
种情况下，只有使用高级筛选才能完成。
在高级筛选中，主要是定义以下三个单元格区域：
定义查询的列表区域；定义查询的条件区域；定义存放查
询结果的区域。
其中条件区域的定义最为复杂，条件的设置必须遵循以下
原则：
条件区域与数据清单区域之间必须有空白行或空白列隔开。
条件区域至少应该有两行，第一行用来放置字段名，下面的行放
置筛选条件。
条件区域的字段名必须与数据清单中的字段名完全一致,最好通过
复制得到。
“与”关系的条件必须出现在同一行；“或”关系的条件不能出
现在同一行
2015/4/13
第7章 Excel的基本应用—制作成绩表
37
高级筛选操作
在“高级筛选”工作表中，筛选出
总分小于200分的女生或总分大
于等于300分的男生的记录。
构造筛选条件
在进行高级筛选之前，首先必须指
定一 个条件区域。条件区域与数据
清单之 间至少应留有一 个空白行
或一 个空白列分开。如图1所示。
图1
执行高级筛选
单击数据清单中的任一 单元格 。
在菜单栏中选择“数据”→“筛
选”→“高级筛选”命令，打开
“高级筛选”对话框,设置结果参见
图2。
图2
2015/4/13
第7章 Excel的基本应用—制作成绩表
38
要点回顾
在不同工作薄中进行工作表的复制或移动操作要
注意什么问题？
使用鼠标移动及复制工作表有何区别？
什么是选择性粘贴？它有什么用途？
函数包括哪三部分？函数的参数之间用什么符号
分隔？
相对引用和绝对引用分别应用在什么场合？它们
的表达方式有何区别？
如何在相对引用、绝对引用和混合引用之间切换？
2015/4/13
第7章 Excel的基本应用—制作成绩表
39
案例总结
本章主要介绍了Excel的基本操作，包括：
Excel的基本输入方法；
工作表的格式化；
单元格复制、移动；
工作表的插入、删除、重命名、移动和复制等；
公式和函数的计算；
公式必 须 以 “=” 开始；
函数的引用形式为：函数名(参数1，参数2……)，参数之 间必须 用
逗号隔开。
要特别注意相对引用、绝对引用和混合引用这三种引用的适用场合
数据的排序、筛选。
自动筛选时，各个字段之 间的逻辑关系是“与”的关系；
高级筛选既可设置各个字段之 间的“与”关系，还可以设置“或”关
系。
2015/4/13
第7章 Excel的基本应用—制作成绩表
40
课后练习
新建一个“考勤表”工作簿，参见教
材，按要求完成对工作表数据的输入
及分析。
2015/4/13
第7章 Excel的基本应用—制作成绩表
41
填充柄的作用
鼠标指针指向A3单元格的“填充柄”（位于单元格右下角的
小黑块），此时鼠标指针变为黑十字，按住鼠标向下拖动
填充柄，拖动过程中填充柄的右下角出现填充的数据，拖
至目标单元格时释放鼠标，填充效果如下图所示。
填充柄
2015/4/13
第7章 Excel的基本应用—制作成绩表
42
“与”关系的条件
表示性别为“女”并且总分
“小于200”的条件，为“与”关
系，这两个条件应该出现
在同一行中，表示筛选的
结果必须同时满足这两个
条件。
2015/4/13
性别
女
第7章 Excel的基本应用—制作成绩表
总分
<200
43
“或”关系的条件
表示条件总分“小于200”或者总分“大于等于300”的条件为
“或”关系，这两个条件不应该出现在同一行中。表示筛选
的结果只要满足其中任意一个条件就可以了。
总分
<200
>=300
2015/4/13
第7章 Excel的基本应用—制作成绩表
44