实验

………………………………………………最新资料推荐………………………………………

实验二Excel2010的高级操作

一、 实验目的

通过一系列的操作实验，要求学生熟练掌握电子表格的常用操作。

1． 熟练掌握Excel2010中图表的制作、更改、修饰等操作。

2． 熟练掌握Excel2010中数据的排序、筛选、分类汇总等数据管理和分析。

3． 熟练掌握Excel2010的综合应用。

二、实验内容

1. 图表的创建和修饰操作。

2. 数据的排序操作。

3. 数据的自动筛选和高级筛选操作。

4. 数据的分类汇总操作。

5. 综合实例操作。

三、实验步骤

(一) 图表的创建和修饰操作

1. 创建簇状柱形图工作表

打开工作簿“”，利用“家庭存款帐本”工作表中的数据建立一个“簇状柱

形图”图表工作表，实验的操作结果如图2.1所示。

图2.1 簇状柱形图工作表示例

1 / 15

………………………………………………最新资料推荐………………………………………

1) 操作要求

（1） 类轴：“存款日期”；数值轴：“金额”和“本息”。

（2） 图表标题：金额、本息对照图。

（3） 分类轴标题：存款日期。

（4） 数值轴标题：（元）。

（5） 图例位置：靠上。

（6） 将图表工作表命名为“金额、本息对照图”。

（7） 将图表工作表保存到工作薄中。

2) 操作方法

（1） 打开工作簿“”，单击“家庭存款帐本”工作表标签，使之成为当前工

作表。

（2） 用鼠标拖动的方法，先选定“存款日期”列的数据，然后按住Ctrl键再分别选定

“金额”和“本息”列的数据，如图2.2所示。

提示：在选定数据区域时（不论是连续的，还是不连续的），都要一次性准确选择有

效的数据区域，否则将会造成图表数据源引用错误，无法正确建立图表。

图2.2 选定数据区域示例

（3） 单击“插入”｜“图表”功能区中的“柱形图”命令按钮，选择“二维柱形图”

中的“簇状柱形图”，即可生成对应的图表，如图2.3和图2.4所示。

图2.3 选择图表类型示例

2 / 15

………………………………………………最新资料推荐………………………………………

图2.4 簇状柱形图表

（4） 如果在插入图表之前没有选定数据或选定的数据不准确，可以单击图表，在“图

表工具”｜“设计”功能区中单击“选择数据”命令按钮，弹出“选择数据源”对话框，如

图2.5和2.6所示。在“图表数据区域”文本框中可以重新选定数据区域（按照（2）步中的

操作）。

图2.5 修改数据源示例

图2.6 “选择数据源”对话框

（5） 在“选择数据源”对话框中，单击“切换行/列”按钮，可以决定系列产生在“行”

还是“列”（默认系列产生在“列”），如图2.6所示。

（6） 在“图表工具”｜“布局”｜“标签”功能组中可以创建及布局图表的各种标签，

如图2.7所示。单击“图表标题”按钮，选择“图表上方”，即在图表上方创建“图表标题”

标签，在标签中输入“金额、本息对照图”；单击“坐标轴标题”按钮，选择“主要横坐标

轴标题”｜“坐标轴下方标题”，即在图表下方创建“坐标轴标题”标签，在标签中输入“存

3 / 15

………………………………………………最新资料推荐………………………………………

款日期”；单击“坐标轴标题”按钮，选择“主要纵坐标轴标题”｜“竖排标题”，即在图表

右方创建“坐标轴标题”标签，在标签中输入“（元）”；单击“图例”按钮，选择“在顶部

显示图例”，即在图表上方创建图例标签。（如果不需要图例，单击“图例”按钮，选择“无”

即可。）

（7） 在“图表工具”｜“设计”功能区中单击“移动图表”命令按钮，弹出“移动图

表”对话框，选择“新工作表”单选按钮，并在对应的文本框中输入“金额、本息对照图”，

如图2.8所示。

（8） 单击“确定”按钮，完成图表工作表的创建，结果如图2.1所示。

（9） 单击“文件”｜“保存”按钮，或者单击“快速访问工具栏”中的“保存”按

钮，将图表工作表保存在当前工作薄中。

图2.7 创建图表标签示例

图2.8 移动图表对话框

2. 设置图表工作表格式

对“金额、本息对照图”工作表进行格式设置，效果如图2.9所示。

1) 操作要求

（1） 图表标题：楷体、28号字、蓝色、加粗；图例标题：楷体、16号字、无边框；

分类轴（X坐标轴）标题：楷体、16号字、浅蓝、加粗；数值轴（Y坐标轴）标题：楷体、

16号字、浅蓝、加粗，对齐方式为中部居中。

（2） 分类轴格式：黑体、14号字、红色；数字格式为日期型“yyyy年m月”，主要刻

度单位“2月”；数值轴格式：黑体、14号字、红色；数字格式为数值型，无小数点，刻度

最小值为5000。

4 / 15

………………………………………………最新资料推荐………………………………………

图2.9 格式化的簇状柱形图图表工作表示例

2) 操作方法

（1） 标题格式设置

① 单击图表标题“金额、本息对照图”，选中所有字符，在“开始”|“字体”功能组

中设置字符格式为楷体、28号字、蓝色、加粗；

② 单击图例，在“开始”|“字体”功能组中设置字符格式为楷体、16号字；双击图

例，打开“设置图例项格式”对话框，单击“边框颜色”选项卡，设置边框颜色为“无线条”；

③ 单击分类轴标题“存款日期”，在“开始”|“字体”功能组中设置字符格式为楷体、

16号字、浅蓝、加粗；

④ 单击Y坐标轴标题“（元）”，在“开始”|“字体”功能组中设置字符格式为楷体、

16号字、浅蓝、加粗；双击Y坐标轴标题“（元）”，打开“设置坐标轴标题格式”对话框，

选择“对齐方式”选项卡，设置文字方向为：“横排”，垂直对齐方式为：“中部居中”，并将

标题“（元）”拖到Y坐标轴上方的合适位置。

（2） 分类轴格式设置

① 单击X轴分类轴线，在“开始”|“字体”功能组中设置字符格式为黑体、14号字、

红色；

② 双击X轴分类轴线，打开“设置坐标轴格式”对话框；

③ 单击“数字”选项卡，设置“类别”为“日期”，“类型”为“yyyy年m月”，如图

2.10所示；

5 / 15

………………………………………………最新资料推荐………………………………………

图2.10“数字”选项卡

图2.11“坐标轴选项”选项卡

④ 单击“坐标轴选项”选项卡，设置“主要刻度单位”为“固定：2、月”，如图2.11

所示。

（3） 数值轴格式设置

① 单击Y轴数值轴线，在“开始”|“字体”功能组中设置字符格式为黑体、14号字、

红色；

② 双击Y轴数值轴线，打开“设置坐标轴格式”对话框；

③ 单击“数字”选项卡，设置“类别”为“数字”，“小数位数”为“0”，去掉选中的

6 / 15

………………………………………………最新资料推荐………………………………………

“使用千位分隔符（,）”复选框，如图2.12所示；

图2.12“数字”选项卡

图2.13“坐标轴选项”选项卡

④ 单击“坐标轴选项”选项卡，设置“最小值”为“固定：5000”，如图2.13所示。

7 / 15

………………………………………………最新资料推荐………………………………………

(二) 数据的管理和分析操作

1. 制作工作表副本

1) 操作要求

打开工作簿，插入三个工作表Sheet1、Sheet4、Sheet5，将“家庭存款帐单”

工作表中的全部数据复制到工作表Sheet1、Sheet2、Sheet3、Sheet4、Sheet5中。将各工作

表中的数据设为最适合列宽，并保存工作簿。

2) 操作方法

（1） 打开工作簿，鼠标右键单击Sheet2工作表标签，打开快捷菜单，如

图2.14所示，单击“插入”菜单，打开“插入”对话框，双击工作表图标，在Sheet2之前

插入一个名为Sheet1的工作表。

（2） 用同样的方法，在Sheet3之前插入工作表：Sheet4和Sheet5。

（3） 单击“家庭存款帐单”工作表标签，使之成为当前工作表，选中整个工作表（单

击工作表全选标记――行与列交叉的左上角区域），在“开始”|“剪贴板”功能组中单

击“复制”按钮，然后在Sheet1中的A1单元格进行粘贴。在Sheet2、Sheet3、Sheet4、

Sheet5中重复粘贴操作。

（4） 选中整个工作表，在“开始”|“单元格”功能组中单击“格式”按钮，选择“自

动调整列宽”，设置各工作表中的数据为最合适的列宽，并保存修改后的工作簿。

图2.14 插入工作表

2. 数据排序操作

1) 操作要求

将Sheet1工作表重命名为“排序”，并在该工作表中按“存期”升序排列，“存期”相

同时再按“存款日期”降序排列。

2) 操作方法

（1） 双击Sheet1标签，输入“排序”，然后单击工作表中的任意单元格，或按回车键，

完成对工作表的重命名。

（2） 选中A2:H14单元格区域，单击“数据”|“排序”命令，打开“排序”对话框。

（3） 从“主要关键字”区域下拉列表框中选择“存期”，从“排序依据”区域下拉列

表框中选择“数值”，从“次序”区域下拉列表框中选择“升序”；单击“添加条件”按钮，

8 / 15

………………………………………………最新资料推荐………………………………………

增加新的排序条件；从“次要关键字”区域下拉列表框中选择“存款日期”，从“排序依据”

区域下拉列表框中选择“数值”，从“次序”区域下拉列表框中选择“降序”；勾选“数据包

含标题”复选框（如不勾选该复选框，系统会把字段名混同数据一起排序），如图2.15所示。

图2.15 “排序”对话框

（4） 单击“确定”按钮，进行排序，排序结果如图2.16所示 。

图2.16 排序结果示例

提示：当对选中数据区域中的任意单元格进行排序时，系统默认选中所有数据作为排

序对象。如果只对部分数据排序，应预先选择待排序的数据区域，然后再执行排序命令。

3. 自动筛选和高级筛选操作

1) 自动筛选

（1） 操作要求

将Sheet2工作表重命名为“自动筛选”，然后自动筛选出“金额”大于20030且“存款

银行”为“工商银行”和“中国银行”的记录。

（2） 操作方法

① 双击Sheet2标签，输入“自动筛选”，然后单击工作表中的任意单元格，或按回车

键，完成对工作表的重命名。

② 选中A2:H14单元格区域， 在“数据”|“排序和筛选”功能组中单击“筛选”命

令按钮，工作表中每个字段名后将出现一个下拉箭头按钮，如图2.17所示。

9 / 15

………………………………………………最新资料推荐………………………………………

图2.17自动筛选操作

③ 单击“金额”右侧的下拉箭头按钮，在下拉列表框中选择“数字筛选”|“自定义

筛选”项，打开“自定义自动筛选方式”对话框，按图2.18所示进行设置，单击“确定”

按钮，此时，工作表中只显示出“金额”大于20030的记录。

图2.18 设置“金额”自动筛选条件

④ 单击“存款银行”右侧的下拉箭头按钮，在下拉列表框中选择“文本筛选”|“自

定义筛选”项，打开“自定义自动筛选方式”对话框，按图2.19所示进行设置，单击“确

定”按钮。此时，在工作表中显示的是“金额”大于20030且存款银行为“中国银行”或“工

商银行”的记录，筛选结果如图2.20所示。

10 / 15

………………………………………………最新资料推荐………………………………………

图2.19 设置“存款银行”自动筛选条件

图2.20 自动筛选结果示例

筛选结束后，“金额”和“存款银行”字段名后的下拉箭头按钮变为漏斗形状，这两

条记录的行号也变为蓝色，如图2.20所示，说明这些字段及记录是经过自动筛选的。

思考：在设置“存款银行”为“工商银行”和“中国银行”这两个条件时，为什么选

择的是“或”关系，而不是“与”关系？

2) 高级筛选

（1） 操作要求

将Sheet4工作表重命名为“高级筛选”。在工作表中对“本息”和“存期”进行高级筛

选。筛选条件为：“本息>=20030”并且“存期<3”，或者“存期=5”，条件区域存放在A19

开始的单元格区域，筛选结果存放在A23开始的单元格区域。

（2） 操作方法

① 双击Sheet4标签，输入“高级筛选”，然后单击工作表中的任意单元格，或按回车

键，完成对工作表的重命名。

② 填写筛选条件。将字段名“本息”、“存期”复制到A19开始的单元格，在单元格

A20中输入“>=20030”，单元格B20中输入“<3”，单元格B21中输入“=5（参见图2.22

所示）。

注意：筛选条件在同一行表示“与”的关系，在同一列不同行表示“或”的关系。

③ 插入点定位在数据清单任一位置，在“数据”|“排序和筛选”功能组中，单击“高

级”命令，打开“高级筛选”对话框。

④ 设置“列表区域”、“条件区域”和结果“复制到”的内容。 设置“列表区域”时，

可以直接输入区域$A$2:$H$14，或直接在数据清单中选择数据区域。用同样的方法设置“条

11 / 15

………………………………………………最新资料推荐………………………………………

件区域”和结果“复制到”的位置，如图2.21所示。

提示：在填写条件区域时，条件字段名最好采用复制、粘贴的方式填写，确保与数据

清单中的字段名格式保持一致。

⑤ 单击“确定”按钮执行高级筛选操作，筛选结果如图2.22所示。

图2.21 “高级筛选”对话框

条件区域 高级筛选结果

图2.22 高级筛选示例

12 / 15

………………………………………………最新资料推荐………………………………………

4. 分类汇总操作

1) 操作要求

将工作表Sheet5重命名为“分类汇总”，并按不同的“存期”对“金额”和“本息”进

行汇总。

2) 操作方法

（1） 双击Sheet5标签，输入“分类汇总”，然后单击工作表中的任意单元格，或按回

车键，完成对工作表的重命名。

（2） 将插入点定位在“存期”列中的任一单元格，在“数据”|“排序和筛选”功能组

中，单击“排序”按钮（升序，也可单击“降序”按钮）。

（3） 在“数据”|“分级显示”功能组中，单击“分类汇总”命令按钮，打开“分类

汇总”对话框。

（4） 在“分类汇总”对话框中设置参数。 “分类字段”：从下拉列表框中选择“存期”

（排序字段）；“汇总方式”：从下拉列表框中选择“求和”；“选定汇总项”：从下拉列表框中

选中“金额”和“本息”复选框； 其他参数可取系统默认的设置，如图2.23所示。

图2.23 “分类汇总”对话框

（5） 单击“确定”按钮，完成分类汇总操作，其结果如图2.24所示。

13 / 15

………………………………………………最新资料推荐………………………………………

图2.24 “分类汇总”结果示例

(三) 综合实例操作

在桌面上新建一个文件夹，以你的班级、姓名和学号命名，在该文件夹中新建一个Excel

文件（即工作簿），并将文件重命名为“期末成绩统计表.xlsx”。按要求输入表格中的数据，

设计表格的版式，制作统计图表，并对数据进行管理。

将综合实例的操作步骤和操作过程的心得体会写入实验报告，将综合实例的最终效果截

图粘贴到实验报告。

操作要求：

1. 工作表的编辑和排版

(1) 输入表格的标题和字段名。字段名包括学号、姓名、性别、课程名6门(如高等数

学、英语、大学物理等)、平均分、总分和成绩排名。

(2) 输入数据，数据内容自由发挥，如“2，张三，男，90,78,82,98,76,88”。

注意：“平均分”、“总分”和“成绩排名”不用输入。

(3) 分别使用函数计算每个同学的平均分、总分和成绩排名。

注意：

① 按照“平均分”进行成绩排名，用到RANK函数，该函数的格式是“=Rank（number，

ref,[order]）”，表示计算某个单元格数据number在数据区域ref中的排名，其中order

省略的话为降序排名。

② 单元格数据会随着行数的变化而变化，因此需要使用相对地址；而排名范围的数

据区域始终固定不变，因此需要使用绝对地址。

(4) 合并居中标题，设置标题的字体、字号、下划线和颜色。

(5) 设置表格的边框底纹。

(6) 使用条件格式可以对表中满足条件“平均分大于80”的单元格设置字体格式，使之

突出显示出来，一目了然。

(7) 保存电子表格。

14 / 15

………………………………………………最新资料推荐………………………………………

2. 工作表的图表制作

(1) 对排名前10的同学的“姓名”和“任一课程的分数”两列数据建立柱形图，包括图

表、图表标题、坐标轴标题、系列名称。

(2) 设置柱形图的格式，包括线条颜色、背景颜色、坐标轴刻度等。

(3) 保存电子表格和图表。

3. 工作表的数据管理

(1) 复制工作表数据（除开“排名”列）到新的工作表中。

(2) 数据的自动筛选。筛选出平均分在70分~80分的数据。

(3) 数据的高级筛选。筛选出平均分大于80分或小于60分的男生数据。

(4) 数据的排序。要求表格中数据依次按“总分”、“姓名”和“学号”降序排序。

(5) 数据的分类汇总。按照“性别”对工作表中的数据进行分类汇总。

注意：分类汇总前必须排序。

(6) 保存电子表格。

15 / 15