1、实验二 Excel 2010 的高级操作一、 实验目的通过一系列的操作实验，要求学生熟练掌握电子表格的常用操作。1 熟练掌握 Excel 2010 中图表的制作、更改、修饰等操作。2 熟练掌握 Excel 2010 中数据的排序、筛选、分类汇总等数据管理和分析。3 熟练掌握 Excel 2010 的综合应用。二、实验内容 1. 图表的创建和修饰操作。2. 数据的排序操作。 3. 数据的自动筛选和高级筛选操作。4. 数据的分类汇总操作。 5. 综合实例操作。三、实验步骤(一) 图表的创建和修饰操作1. 创建簇状柱形图工作表打开工作簿“cunkuan.xls ”，利用“家庭存款帐本”工作表中的数据
2、建立一个“簇状柱形图”图表工作表，实验的操作结果如图 2.1 所示。图 2.1 簇状柱形图工作表示例1) 操作要求 （1） 类轴：“存款日期” ；数值轴：“金额”和“本息” 。 （2） 图表标题：金额、本息对照图。（3） 分类轴标题：存款日期。（4） 数值轴标题：（元） 。 （5） 图例位置：靠上。 （6） 将图表工作表命名为“金额、本息对照图” 。（7） 将图表工作表保存到工作薄中。 2) 操作方法 （1） 打开工作簿“cunkuan.xls” ，单击“家庭存款帐本”工作表标签，使之成为当前工作表。 （2） 用鼠标拖动的方法，先选定“存款日期”列的数据，然后按住 Ctrl 键再分别选定“金额
3、”和“本息”列的数据，如图 2.2 所示。提示：在选定数据区域时（不论是连续的，还是不连续的） ，都要一次性准确选择有效的数据区域，否则将会造成图表数据源引用错误，无法正确建立图表。图 2.2 选定数据区域示例（3） 单击“插入”“图表”功能区中的“柱形图”命令按钮，选择“二维柱形图”中的“簇状柱形图” ，即可生成对应的图表，如图 2.3 和图 2.4 所示。图 2.3 选择图表类型示例图 2.4 簇状柱形图表（4） 如果在插入图表之前没有选定数据或选定的数据不准确，可以单击图表，在“图表工具”“设计”功能区中单击“选择数据”命令按钮，弹出“选择数据源”对话框，如图 2.5 和 2.6 所示。
4、在“图表数据区域”文本框中可以重新选定数据区域（按照（2）步中的操作） 。 图 2.5 修改数据源示例图 2.6 “选择数据源”对话框（5） 在“选择数据源”对话框中，单击“切换行/列”按钮，可以决定系列产生在“行”还是“列” （默认系列产生在“列” ） ，如图 2.6 所示。 （6） 在“图表工具”“布局”“标签”功能组中可以创建及布局图表的各种标签，如图 2.7 所示。单击“图表标题”按钮，选择“图表上方” ，即在图表上方创建“图表标题”标签，在标签中输入“金额、本息对照图” ；单击“坐标轴标题”按钮，选择“主要横坐标轴标题”“坐标轴下方标题” ，即在图表下方创建“坐标轴标题”标签，在标签
5、中输入“存款日期” ；单击“坐标轴标题”按钮，选择“主要纵坐标轴标题”“竖排标题” ，即在图表右方创建“坐标轴标题”标签，在标签中输入 “（元） ”；单击“图例”按钮，选择“在顶部显示图例” ，即在图表上方创建图例标签。 （如果不需要图例，单击“图例”按钮，选择“无”即可。 ）（7） 在“图表工具”“设计”功能区中单击“移动图表”命令按钮，弹出“移动图表”对话框，选择“新工作表”单选按钮，并在对应的文本框中输入“金额、本息对照图” ，如图 2.8 所示。（8） 单击“确定”按钮，完成图表工作表的创建，结果如图 2.1 所示。 （9） 单击“文件”“保存”按钮，或者单击“快速访问工具栏 ”中的“
6、保存 ”按钮，将图表工作表保存在当前工作薄中。图 2.7 创建图表标签示例图 2.8 移动图表对话框2. 设置图表工作表格式 对“金额、本息对照图”工作表进行格式设置，效果如图 2.9 所示。1) 操作要求（1） 图表标题：楷体、28 号字、蓝色、加粗；图例标题：楷体、16 号字、无边框；分类轴（X 坐标轴）标题：楷体、 16 号字、浅蓝、加粗；数值轴（Y 坐标轴）标题：楷体、16 号字、浅蓝、加粗，对齐方式为中部居中。 （2） 分类轴格式：黑体、14 号字、红色；数字格式为日期型“yyyy 年 m 月” ，主要刻度单位“2 月” ；数值轴格式：黑体、14 号字、红色；数字格式为数值型，无小数
7、点，刻度最小值为 5000。图 2.9 格式化的簇状柱形图图表工作表示例2) 操作方法（1） 标题格式设置 单击图表标题“金额、本息对照图” ，选中所有字符，在“开始”|“字体”功能组中设置字符格式为楷体、28 号字、蓝色、加粗； 单击图例，在“开始”| “字体”功能组中设置字符格式为楷体、16 号字；双击图例，打开“设置图例项格式”对话框，单击“边框颜色”选项卡，设置边框颜色为“无线条” ； 单击分类轴标题“存款日期” ，在“开始”|“字体”功能组中设置字符格式为楷体、16 号字、浅蓝、加粗； 单击 Y 坐标轴标题 “（元） ”，在“开始”| “字体”功能组中设置字符格式为楷体、16 号字、
8、浅蓝、加粗；双击 Y 坐标轴标题“（元） ”，打开 “设置坐标轴标题格式”对话框，选择“对齐方式”选项卡，设置文字方向为：“横排” ，垂直对齐方式为：“中部居中” ，并将标题“（元） ”拖到 Y 坐标轴上方的合适位置。 （2） 分类轴格式设置 单击 X 轴分类轴线，在“开始”| “字体”功能组中设置字符格式为黑体、14 号字、红色； 双击 X 轴分类轴线，打开“设置坐标轴格式”对话框； 单击“数字”选项卡，设置“类别”为“日期” ， “类型”为“yyyy 年 m 月” ，如图 2.10 所示；图 2.10 “数字”选项卡图 2.11 “坐标轴选项”选项卡 单击“坐标轴选项”选项卡，设置“主要刻
9、度单位”为“固定：2、月” ，如图2.11 所示。（3） 数值轴格式设置 单击 Y 轴数值轴线，在“开始”| “字体”功能组中设置字符格式为黑体、14 号字、红色； 双击 Y 轴数值轴线，打开“设置坐标轴格式”对话框； 单击“数字”选项卡，设置“类别”为“数字” ， “小数位数”为“0” ，去掉选中的“使用千位分隔符（,） ”复选框，如图 2.12 所示；图 2.12 “数字”选项卡图 2.13 “坐标轴选项”选项卡 单击“坐标轴选项”选项卡，设置“最小值”为“固定：5000” ，如图 2.13 所示。(二) 数据的管理和分析操作1. 制作工作表副本1) 操作要求打开 cunkuan.xls
10、工作簿，插入三个工作表 Sheet1、Sheet4 、Sheet5，将“家庭存款帐单”工作表中的全部数据复制到工作表 Sheet1、Sheet2、Sheet3、Sheet4、Sheet5 中。将各工作表中的数据设为最适合列宽，并保存工作簿。 2) 操作方法 （1） 打开 cunkuan.xls 工作簿，鼠标右键单击 Sheet2 工作表标签，打开快捷菜单，如图 2.14 所示，单击“插入”菜单，打开“插入”对话框，双击工作表图标，在 Sheet2 之前插入一个名为 Sheet1 的工作表。 （2） 用同样的方法，在 Sheet3 之前插入工作表：Sheet4 和 Sheet5。 （3） 单击“
11、家庭存款帐单”工作表标签，使之成为当前工作表，选中整个工作表（单击工作表全选标记 行与列交叉的左上角区域） ，在“开始”|“剪贴板”功能组中单击“复制 ”按钮，然后在 Sheet1 中的 A1 单元格进行粘贴。在Sheet2、Sheet3、Sheet4 、Sheet5 中重复粘贴操作。（4） 选中整个工作表，在“开始”|“单元格”功能组中单击“格式”按钮，选择“自动调整列宽” ，设置各工作表中的数据为最合适的列宽，并保存修改后的工作簿。图 2.14 插入工作表2. 数据排序操作 1) 操作要求将 Sheet1 工作表重命名为“排序” ，并在该工作表中按“存期”升序排列， “存期”相同时再按“存
12、款日期”降序排列。 2) 操作方法（1） 双击 Sheet1 标签，输入“排序” ，然后单击工作表中的任意单元格，或按回车键，完成对工作表的重命名。 （2） 选中 A2:H14 单元格区域，单击 “数据”|“排序”命令，打开“排序”对话框。（3） 从“主要关键字”区域下拉列表框中选择“存期” ，从“排序依据”区域下拉列表框中选择“数值” ，从“次序”区域下拉列表框中选择“升序” ；单击“添加条件”按钮，增加新的排序条件；从“次要关键字”区域下拉列表框中选择“存款日期” ，从“排序依据”区域下拉列表框中选择“数值” ，从“次序”区域下拉列表框中选择“降序” ；勾选“数据包含标题”复选框（如不勾选
13、该复选框，系统会把字段名混同数据一起排序） ，如图 2.15所示。图 2.15 “排序”对话框（4） 单击“确定”按钮，进行排序，排序结果如图 2.16 所示 。图 2.16 排序结果示例提示：当对选中数据区域中的任意单元格进行排序时，系统默认选中所有数据作为排序对象。如果只对部分数据排序，应预先选择待排序的数据区域，然后再执行排序命令。 3. 自动筛选和高级筛选操作 1) 自动筛选 （1） 操作要求将 Sheet2 工作表重命名为“自动筛选” ，然后自动筛选出“金额”大于 20030 且“存款银行”为“工商银行”和“中国银行”的记录。 （2） 操作方法 双击 Sheet2 标签，输入“自动筛
14、选” ，然后单击工作表中的任意单元格，或按回车键，完成对工作表的重命名。 选中 A2:H14 单元格区域， 在“数据”|“排序和筛选”功能组中单击“筛选”命令按钮，工作表中每个字段名后将出现一个下拉箭头按钮，如图 2.17 所示。 图 2.17 自动筛选操作 单击“金额”右侧的下拉箭头按钮，在下拉列表框中选择“数字筛选”|“自定义筛选”项，打开“自定义自动筛选方式”对话框，按图 2.18 所示进行设置，单击“确定”按钮，此时，工作表中只显示出“金额”大于 20030 的记录。 图 2.18 设置“金额”自动筛选条件 单击“存款银行”右侧的下拉箭头按钮，在下拉列表框中选择“文本筛选”|“自定义筛
15、选”项，打开“自定义自动筛选方式”对话框，按图 2.19 所示进行设置，单击“确定”按钮。此时，在工作表中显示的是“金额”大于 20030 且存款银行为“中国银行”或“工商银行”的记录，筛选结果如图 2.20 所示。图 2.19 设置“存款银行”自动筛选条件图 2.20 自动筛选结果示例筛选结束后， “金额”和“存款银行”字段名后的下拉箭头按钮变为漏斗形状 ，这两条记录的行号也变为蓝色，如图 2.20 所示，说明这些字段及记录是经过自动筛选的。 思考：在设置“存款银行”为“工商银行”和“中国银行”这两个条件时，为什么选择的是“或”关系，而不是“与”关系？ 2) 高级筛选 （1） 操作要求将 S
16、heet4 工作表重命名为“高级筛选” 。在工作表中对“本息”和“存期”进行高级筛选。筛选条件为：“本息=20030”并且“存期=20030 ”，单元格 B20 中输入“3” ，单元格 B21 中输入“=5（参见图 2.22所示） 。 注意：筛选条件在同一行表示“与”的关系，在同一列不同行表示“或”的关系。 插入点定位在数据清单任一位置，在“数据”|“排序和筛选”功能组中，单击“高级”命令，打开“高级筛选”对话框。 设置“列表区域” 、 “条件区域”和结果“复制到”的内容。 设置“列表区域”时，可以直接输入区域$A$2:$H$14 ，或直接在数据清单中选择数据区域。用同样的方法设置“条件区域”
17、和结果“复制到”的位置，如图 2.21 所示。提示：在填写条件区域时，条件字段名最好采用复制、粘贴的方式填写，确保与数据清单中的字段名格式保持一致。 单击“确定”按钮执行高级筛选操作，筛选结果如图 2.22 所示。图 2.21 “高级筛选”对话框图 2.22 高级筛选示例条件区域 高级筛选结果4. 分类汇总操作 1) 操作要求将工作表 Sheet5 重命名为“分类汇总” ，并按不同的“存期”对“金额”和“本息”进行汇总。 2) 操作方法 （1） 双击 Sheet5 标签，输入“分类汇总” ，然后单击工作表中的任意单元格，或按回车键，完成对工作表的重命名。 （2） 将插入点定位在“存期”列中的任
18、一单元格，在“数据”|“排序和筛选”功能组中，单击“排序 ”按钮（升序，也可单击“降序 ”按钮） 。 （3） 在“数据”| “分级显示”功能组中，单击“分类汇总”命令按钮，打开“分类汇总”对话框。 （4） 在“分类汇总”对话框中设置参数。 “分类字段”：从下拉列表框中选择“存期” （排序字段） ；“汇总方式”：从下拉列表框中选择“求和” ； “选定汇总项”：从下拉列表框中选中“金额”和“本息”复选框； 其他参数可取系统默认的设置，如图 2.23 所示。 图 2.23 “分类汇总”对话框（5） 单击“确定”按钮，完成分类汇总操作，其结果如图 2.24 所示。 图 2.24 “分类汇总”结果示例(
19、三) 综合实例操作在桌面上新建一个文件夹，以你的班级、姓名和学号命名，在该文件夹中新建一个Excel 文件（即工作簿） ，并将文件重命名为 “期末成绩统计表 .xlsx”。按要求输入表格中的数据，设计表格的版式，制作统计图表，并对数据进行管理。 将综合实例的操作步骤和操作过程的心得体会写入实验报告，将综合实例的最终效果截图粘贴到实验报告。操作要求：1. 工作表的编辑和排版(1) 输入表格的标题和字段名。字段名包括学号、姓名、性别、课程名 6 门(如高等数学、英语、大学物理等) 、平均分、总分和成绩排名。(2) 输入数据，数据内容自由发挥，如“201500123456，张三，男，90,78,82
20、,98,76,88” 。注意：“平均分” 、 “总分”和“成绩排名”不用输入。(3) 分别使用函数计算每个同学的平均分、总分和成绩排名。注意： 按照“平均分”进行成绩排名，用到 RANK 函数，该函数的格式是“=Rank（number，ref,order） ”，表示计算某个单元格数据 number 在数据区域 ref中的排名，其中 order 省略的话为降序排名。 单元格数据会随着行数的变化而变化，因此需要使用相对地址；而排名范围的数据区域始终固定不变，因此需要使用绝对地址。(4) 合并居中标题，设置标题的字体、字号、下划线和颜色。(5) 设置表格的边框底纹。(6) 使用条件格式可以对表中满足
21、条件“平均分大于 80”的单元格设置字体格式，使之突出显示出来，一目了然。(7) 保存电子表格。2. 工作表的图表制作(1) 对排名前 10 的同学的“姓名”和“任一课程的分数”两列数据建立柱形图，包括图表、图表标题、坐标轴标题、系列名称。(2) 设置柱形图的格式，包括线条颜色、背景颜色、坐标轴刻度等。(3) 保存电子表格和图表。3. 工作表的数据管理(1) 复制工作表数据（除开“排名”列）到新的工作表中。(2) 数据的自动筛选。筛选出平均分在 70 分80 分的数据。(3) 数据的高级筛选。筛选出平均分大于 80 分或小于 60 分的男生数据。(4) 数据的排序。要求表格中数据依次按“总分” 、 “姓名”和“学号”降序排序。(5) 数据的分类汇总。按照“性别”对工作表中的数据进行分类汇总。注意：分类汇总前必须排序。(6) 保存电子表格。
