资源简介

(共42张PPT)
第4 章
电子表格处理Excel 2016
本章对常用的电子表格处理软件Excel 2016 进行介绍，同时说明计算机数据处理的常用方法。
《信息技术基础》配套资源
4.1 制作学生成绩表
4.1.1 任务要求
某班考试4 门课程，分别为“大学计算机基础”“大学英语”“高等数学”和“哲学”，任课教师分别录入自己教授课程的成绩，班主任收集得到4 个Excel 文件，分别为“大学计算机基础—成绩表.xlsx”“大学英语—成绩表.xlsx”“高等数学—成绩表.xlsx”和“哲学—成绩表.xlsx”。
现在班主任需要把这4 个Excel 文件中的4 门考试成绩整理到一个新的工作簿文件中，得到“20182206 班第1 学期各科成绩表.xlsx”
4.1 制作学生成绩表
4.1.2 操作步骤
1．输入单科成绩
（1）新建工作簿文件：在C 盘根目录中新建一个工作文件夹并命名为“成绩”。把4个Excel 文件复制到该文件夹中，同时在该文件夹中新建一个Excel文件“20182206 班第1 学期各科成绩表”。
（2）自动填充学号：由于学号有一定规律，而且是连续的，所以可以采用自动填充的方 法。
（3）输入姓名：由于姓名没有规律，只能一个单元格一个单元格的输入。单击“姓名”列的单元格，输
入与学号对应的姓名。
（4）输入性别：性别只有“男”“女”两个值，可以采用填充实现快速输入。
4.1 制作学生成绩表
4.1.2 操作步骤
1．输入单科成绩
（5）输入成绩：输入平时成绩、期中考试成绩和期末考试成绩。由于需要输入的成绩多，在输入的过程中很容易看错行而输错数据。为了减少输入错误，下面采用选定单元格区域进行快速输入的方法，例如每次选定3 行3 列。请打开“大学计算机基础—成绩表.xlsx”文件，对照着输入。
4.1 制作学生成绩表
4.1.2 操作步骤
1．输入单科成绩
（6）计算总成绩：
总成绩的计算公式是：平时成绩*50%+ 期中考试成绩*20%+ 期末考试成绩*30%，在G3 单元格中输入公式“d3*0.5 + e3*0.2 + f3*0.3”，如左图所示，公式中不区别大小写。
双击G3 单元格的填充柄，把G3 单元格的计算公式复制到该列的其他单元格中，如右图所示。
4.1 制作学生成绩表
4.1.2 操作步骤
1．输入单科成绩
（7）将总成绩改为整数：
如果要求总成绩必须是整数，则可以通过减少小数位数来实 现。首先选中要修改的区域，在“开始”选项卡的“数字”组中，先单击“增加小数位数”按钮，把所有数增加一个小数，然后再单击两次“减少小数位数”按钮，把小数改为整数。
4.1 制作学生成绩表
4.1.2 操作步骤
1．输入单科成绩
（8）更改工作表名称：
工作表的名称在默认情况下是“Sheet1”“Sheet2”和“Sheet3”3 个。如果一个Excel 工作簿文件中含有多个工作表，应该把工作表名称改为有意义的名称。在Excel 文档窗口左下角，双击工作表标签“Sheet1” 输入新的工作表名称“大学计算机应用”，输入完成后按Enter键。
4.1 制作学生成绩表
4.1.2 操作步骤
1．输入单科成绩
（9）保存工作簿文件：
为了减少断电、死机等故障造成的数据丢失，应该经常保存文档。可以时常单击“保存”按钮 ，也可以设置为自动保存。单击“文件”菜单，在“文件”菜单中单击“选项”，显示“Excel 选项”对话 框。在左侧窗格中单击“保存”。在右侧窗格中，选中“保存自动恢复信息时间间隔”复选框后，调整“分钟”为1，单击“确定”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
2．合并工作表
（1）复制或移动工作表：
4.1 制作学生成绩表
4.1.2 操作步骤
2．合并工作表
（2）复制或粘贴工作表：如果需要并的数据比较少，采用复制和粘贴工作表会更方便。
（3）在不同工作簿之间，使用鼠标拖曳方法复制工作表：如果使用鼠标拖曳方法在不同工作簿之间复制或移动工作表，则必须同时打开两个工作簿窗口，并且在屏幕上同时可见。在源工作簿中选择工作表标签后，按住鼠标左键拖曳到目标工作簿，当鼠标指针到达目标位置后松开鼠标。
（4） 在当前工作簿中移动工作表：在当前工作簿中移动工作表就是在当前工作簿中调整工作表标签的先后顺序。
（5）在当前工作簿中复制工作表：如果要在当前工作簿中复制工作表，其操作方法与移动工作表相似，只是在拖曳工作表标签时要先按下Ctrl 键不松开，这时鼠标指针显示为带+ 号的图形。到达目标位置后，要
先松开鼠标左键，然后再松开Ctrl 键。
一般来说，在不同工作簿中复制或移动工作表，使用“移动或复制”最方便；在同一个工作簿中复制或移动工作表，使用鼠标拖曳的方法比较简单。
4.1 制作学生成绩表
4.1.2 操作步骤
3．制作各科成绩汇总表
（1）插入工作表：在要插入位置的工作表标签上右击，在显示的快捷菜单上单击“插入”，显示“插入”对话框，选中“工作表”，单击“确定”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
3．制作各科成绩汇总表
（2）单元格数据的复制与粘贴：选定要复制的单元格区域，在“开始”选项卡的“剪贴板”组中，单击“复制”按钮，或者直接按Ctrl+C键，选定的单元格区域的四周出现一个虚线框；单击“各科成绩汇总表”标签，切换到该工作表，在该工作表中单击A1 单元格，在“开始”选项卡的“剪贴板”组中，单击“粘贴”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
3．制作各科成绩汇总表
（3）公式数据的复制与粘贴：对于包含公式的单元格，至少具有“公式”和“值”两种属性。使用“选择性粘贴”可以实现选择粘贴“值”属性，或是粘贴“公式”属性。在“粘贴”下拉菜单中单击“粘贴数值”组中的“值和源格式”命令，可以确保公司数据的正确复制与粘贴。
（4）单元格或单元格区域数据的移动：对于单元格或单元格区域中的数据，有时需要改换在工作表中的位置，可以使用功能区的命令或鼠标拖曳方法来移动数据。
4.1 制作学生成绩表
4.1.2 操作步骤
4．计算总分、名次、平均分和最高分、最低分
（1）计算总分：添加“总分”列，单击H1 单元格，在H1 单元格中输入“总 分”。单击H2 单元格。在“开始”选项卡的“编辑”组中，单击“自动求和”按钮，单元格中显示求和函数SUM，并自动选定了求和范围，如图所示。如果自动选定的范围正确，则按Enter 键或单击“编辑栏”左侧的“输入”按钮确认，该单元格中即显示计算结果。如果自动选定的范围不正确，则重新选定正确的范围。
4.1 制作学生成绩表
4.1.2 操作步骤
4．计算总分、名次、平均分和最高分、最低分
（2）计算总分排名：添加“名次”列，单击I1 单元格，输入“名 次”。单击I2 单元格。单击“编辑栏”左侧的“插入函数”按钮 ，显示“插入函数”对话框，单击“或选择类别”下拉列表，选择其中的“统计”；
然后在“选择函数”列表框中单击选定“RANK.EQ”，单击“确定”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
4．计算总分、名次、平均分和最高分、最低分
（3）计算各科平均分：在“公式”选项卡的“函数库”组中，单击“自动求和”按钮后的箭头 ，从下拉列表中选择“平均值”，单元格中出现平均值函数AVERAGE，并自动选定参数范围。
4.1 制作学生成绩表
4.1.2 操作步骤
4．计算总分、名次、平均分和最高分、最低分
（4）计算最高分、最低分：单击A43 单元格，输入“班级最高分”；单击A44 单元格，输入“班级最低分”。单击D43 单元格，输入公式“=MAX（D2: D41）”，按Enter 键，计算该单元格范围内的最大数，如图4-63 所示。向右拖曳D43 单元格的填充柄到G43，计算其他科的最高分。单击D44 单击格，输入公式“=MIN（D2: D41）”，按Enter 键，计算该单元格范围内的最小数。向右拖曳D44 单元格的填充柄，计算其他科的最低分。
4.1 制作学生成绩表
4.1.2 操作步骤
4．计算总分、名次、平均分和最高分、最低分
（5）平均分数的四舍五 入：单击D42 单元格，“编辑栏”中显示“=AVERAGE（D2: D41）”，在“编辑栏”中的函数修改为“=ROUND（AVERAGE（D2: D41），1）”，然后按Enter 键确认。
4.1 制作学生成绩表
4.1.2 操作步骤
5．套用格式美化表格
在“各科成绩汇总表”工作表中，在“开始”选项卡的“样式”组中，单击“套用表格格式”按钮 ，从下拉列表中选择“浅色”组中的“表样式浅色20”。
4.1 制作学生成绩表
4.1.2 操作步骤
5．套用格式美化表格
显示“套用表格式”对话框，并在“表数据的来源”文本框中自动选定单元格范围，选中“表包含标题”复选框。如果自动选定的范围正确，则单击“确定”按钮；如果自动选定的单元格区域不正确，则重新选定单元格区域。套用表格样式后，除应用表格样式外，每列的列标题右侧显示“筛选”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
5．套用格式美化表格
单击“筛选”按钮，在下拉列表中可以选择对表格中数据的筛选方式，例如希望只显示女生记录，则单击“性别”右侧的筛选按钮，从筛选列表中取消“男”前面的复选框，单击“确定”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
5．套用格式美化表格
单击表格区域中的任意单元格，在“表格工具/ 设计”选项卡的“工具”组中，单击“转换为区域”按钮 ，显示“是否将表转换为普通区域？”对话框，单击“是”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（1）数据清单：
当工作表中的数据是由一系列数据行组成的二维表，也就是每一列中的数据是同一类型的数据，来自同一个域，每一列成为一个字段；每一行称作一条数据记录，多行组成一个数据表。这样的数据表在Excel 中被称作数据清单。例如前面建立的成绩表。
数据清单应该尽量满足以下条件。
① 每一列必须有列名，而且每一列中的数据必须是相同的类型。
② 避免在一个工作表中有多个数据清单。
③ 在一个工作表中，数据清单与其他数据之间至少留出一个空白列和一个空白行。
在执行Excel 的数据库操作时，例如排序、筛选、分类汇总等，Excel 自动把数据清单作为数据库来操作。数据清单中的列是数据库中的字段，数据清单中的列标题是数据库中的字段名。数据清单中的每一行对应数据库中的一条记录。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（2）一列的排序：
在“哲学”工作表中，把“哲学”列按成绩从大到小“降序”排列。
① 在“哲学”工作表中，单击“哲学”列中的任意一个单元格。
② 在“数据”选项卡的“排序和筛选”组中，单击“降序”按钮 ，则数据清单以记录为单位，按“哲学”列的成绩从高分到低分的降序方式排序。
③ 单击数据清单中的任意一个单元格，在“开始”选项卡的“样式”组中，单击“套用表格格式”按钮，从下拉列表中单击“浅色”组中的“表样式浅色15”。
④ 在“表格工具/ 设计”选项卡的“工具”组中，单击“转换为区域”按钮，把表格转换为普通区域。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（3）多列的排序：
在“大学计算机应用”工作表中，以“总成绩”为主要关键字降序排列，以“学号”为第2 关键字升序排序，以“性别”为第3关键字降序排列。
① 在成绩表中单击数据清单中的任意一个单元格。
② 在“数据”选项卡的“排序和筛选”组中，单击“排序”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（3）多列的排序：
③ 显示“排序”对话框，在“列”下的“主要关键字”下拉列表中选择“总成绩”；在“排序依据”下拉列表框中保留“数值”；在“次序”下拉列表框中选择“降序”。单击“添加条件”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（3）多列的排序：
④ 显示“次要关键字”条件选项，从其下拉列表框中选择“学号”；“排序依据”下拉列表框仍然保持“数值”；“次序”下拉列表框中选择“升序”。单击“添加条件”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（3）多列的排序：
⑤重复步骤④，分别选择“性别”“数值”“降序”，相同条件下先显示“女”，再显示“男”。“排序”对话框中各添加的条件。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（3）多列的排序：
⑥ 如果添加的条件多了或者不再需要，可以先选定要删除的条件，单击“删除条件”按钮即可删除该条件。所有条件选定后，单击“确定”按钮，工作表中即按要求显示排序结果。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（4）用套用表格实现排序及计算平均分：
把“大学英语”的成绩按降序排列，并利用套用表格的汇总行计算班级平均分。
① 在“大学英语”工作表中，单击数据清单区域中的任意一个单元格。在“开始”选项卡的“样式”组中，单击“套用表格样式”按钮 。从列表中单击“表样式中等深浅9”。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（4）用套用表格实现排序及计算平均分：
②显示“套用表格式”对话框，直接单击“确定”按钮。
③单击“大学英语”右侧的筛选按钮 ，在下拉列表中单击“降序”，
该列的下拉按钮变为 ，表示该列按“降序”排列。
4.1 制作学生成绩表
4.1.2 操作步骤
6．成绩表的排序
（4）用套用表格实现排序及计算平均分：
④ 单击数据清单中任意一个单元格，在“表格工具/ 设计”选项卡的“表格样式选项”组中，选中“汇总行”与“最后一列”复选框。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
筛选分为自动筛选和高级筛选。自动筛选在同一列内可以实现“与”和“或”的运行，通过多次自动筛选也可以实现多个列之间的“与”运算，但无法实现多个列之间的“或”运算。高级筛选可以实现多个列之间的“或”运算。
（1）自动筛选：
筛选出同时满足条件：“性别”为“女”、姓“李”、“大学英语”成绩在85～100 之间、
“名次”在前9 名的记 录。
① 因为自动筛选后将破环原始表的排列顺序，所以先复制一份“各科成绩汇总表”，在新复制的工作表中实现自动筛选。按下Ctrl 键不松开，拖动“各科成绩汇总表”到目标位置后松开鼠标左键和Ctrl 键。把“各科成绩汇总表（2）”工作表名重新命名为“自动筛选—成绩表”。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
② 在“数据”选项卡的“排序和筛选”组中，单击“筛选”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
③ 所有列标题右侧自动显示一个筛选按钮 ，单击“性别”右侧的筛选按钮，在下拉列表中取消选中“全选”复选框，然后选中“女”复选框，单击“确定”按钮。如果要清除筛选，除了用列标题右侧的筛选按钮外，还可以右键单击“性别”列下的任意一个单元格，从快捷菜单中单击“筛选”→“从‘性别’中清除筛选”，筛选也可以使用快捷菜单。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
④ 单击“姓名”右侧的筛选按钮，从下拉列表中单击“文本筛选”→“自定义筛选”。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
⑤ 显示“自定义自动筛选方式”对话框，第一个条件的两个下拉列表框
分别选择“开头是”和“李”；第二格条件的2 个下拉列表框分别选择“结
尾是”和“莉”；这两个条件的关系选择“或”，如图4-87 所示，单击“确
定”按钮。
⑥ 筛选结果如右图所示，设置了条件的按钮变成了 外观，被筛选出来的满足条件的行号变成了蓝色。当鼠标指针指向该筛选按钮时，即显示筛选条件。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
⑦ 单击“大学英语”右侧的筛选按钮，从下拉列表中单击“数字筛选”→“介于”。
⑧ 显示“自定义自动筛选方式”对话框，在“大于或等于”后输入85，在“小于或等于”后输入100，单击“确定”按钮。
⑨ 单击“名次”右侧的筛选按钮，从下拉列表中单击“数字筛选”→“前10项”。
⑩ 显示“自动筛选前10 个”对话框，设置条件分别为“最小”“9”“项”，单击“确定”按钮。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
满足条件的最终筛选结果，如图所示。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
（2）高级筛选：
筛选出总分大于300 分并且大学英语成绩大于90 分的学生，或者总分大于280 分并且大学英语成绩大于95 分的学生。
① 把“各科成绩汇总表”复制一份，重命名为“高级筛选—成绩表”。
② 构造筛选条件。在条件区域输入筛选条件，如图4-92 所示的D47: E49 区域。
4.1 制作学生成绩表
4.1.2 操作步骤
7．成绩表的筛选
③ 执行高级筛选。单击数据清单中的任意一个单元格。在“数据”选项卡的“排序和
筛选”组中，单击“高级”按钮。
显示“高级筛选”对话框，数据清单区域周围出现虚线选定框，筛选区域应该不包括“班级平均分”“班级最高分”和“班级最低分”，所以要重新选定列表区域，单击“列表区域”后面的折叠对话框按钮 ，选定列表区域为$A$1: $I$41，再次单击折叠对话框按钮展开对话框。
在“高级筛选”对话框中，单击“条件区域”后的 按钮，选定条件区域。
在“高级筛选”对话框中，选中“将筛选结果复制到其他位置”单选按钮。单击“复制到”后的 按钮，单击起始单元格为A51，单击“确定”按钮。列表的高级筛选结果，如图所示
4.1 制作学生成绩表
4.1.2 操作步骤
8．用主题统一表格风格
主题是一组预设的样式，包括字体（标题字体和正文字体）、颜色和效果。
（1） 打开“20182206 班第1 学期各科成绩表.xlsx”文件，单击“文件”选项卡，单击“另存为”，显示“另存为”对话框，文件名改为“20182206 班第1 学期各科成绩表—主题.xlsx”。
（2） 在“20182206 班第1 学期各科成绩表—主题.xlsx”
工作簿中，任意选择一个工作表。
（3） 在“页面布局”选项卡的“主题”组中，单击“主
题”按钮，从下拉选项组中选择的“深度” 。
（4） 分别切换到不同的工作表中，看到凡是设置过单元
格格式或套用了表格样式的工作表中的字体、底纹、边框的
颜色都被套用了指定的主题样式。但是，没有设置单元格格
式的工作表只有字体发生了变化。

展开更多......

收起↑