实验Excel2010的高级操作

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） 用鼠标

3、拖动的方法，先选定“存款日期”列的数据，然后按住Ctrl键再分别选定“金额”和“本息”列的数据，如图2.2所示。提示：在选定数据区域时（不论是连续的，还是不连续的），都要一次性准确选择有效的数据区域，否则将会造成图表数据源引用错误，无法正确建立图表。图2.2 选定数据区域示例（3） 单击“插入”“图表”功能区中的“柱形图”命令按钮，选择“二维柱形图”中的“簇状柱形图”，即可生成对应的图表，如图2.3和图2.4所示。图2.3 选择图表类型示例图2.4 簇状柱形图表（4） 如果在插入图表之前没有选定数据或选定的数据不准确，可以单击图表，在“图表工具”“设计”功能区中单

4、击“选择数据”命令按钮，弹出“选择数据源”对话框，如图2.5和2.6所示。在“图表数据区域”文本框中可以重新选定数据区域（按照（2）步中的操作）。 图2.5 修改数据源示例图2.6 “选择数据源”对话框（5） 在“选择数据源”对话框中，单击“切换行/列”按钮，可以决定系列产生在“行”还是“列”（默认系列产生在“列”），如图2.6所示。 （6） 在“图表工具”“布局”“标签”功能组中可以创建及布局图表的各种标签，如图2.7所示。单击“图表标题”按钮，选择“图表上方”，即在图表上方创建“图表标题”标签，在标签中输入“金额、本息对照图”；单击“坐标轴标题”按钮

5、，选择“主要横坐标轴标题”“坐标轴下方标题”，即在图表下方创建“坐标轴标题”标签，在标签中输入“存款日期”；单击“坐标轴标题”按钮，选择“主要纵坐标轴标题”“竖排标题”，即在图表右方创建“坐标轴标题”标签，在标签中输入 “（元）”；单击“图例”按钮，选择“在顶部显示图例”，即在图表上方创建图例标签。（如果不需要图例，单击“图例”按钮，选择“无”即可。）（7） 在“图表工具”“设计”功能区中单击“移动图表”命令按钮，弹出“移动图表”对话框，选择“新工作表”单选按钮，并在对应的文本框中输入“金额、本息对照图”，如图2.8所示。（8） 单击“确定”按钮，完成图表工作表的创建，结果如图2.1所示。&#

6、160;（9） 单击“文件”“保存”按钮，或者单击“快速访问工具栏”中的“保存”按钮，将图表工作表保存在当前工作薄中。图2.7 创建图表标签示例图2.8 移动图表对话框 2. 设置图表工作表格式 对“金额、本息对照图”工作表进行格式设置，效果如图2.9所示。1) 操作要求（1） 图表标题：楷体、28号字、蓝色、加粗；图例标题：楷体、16号字、无边框；分类轴（X坐标轴）标题：楷体、16号字、浅蓝、加粗；数值轴（Y坐标轴）标题：楷体、16号字、浅蓝、加粗，对齐方式为中部居中。 （2） 分类轴格式：黑体、14号字、红色；数字格式为日期型“yyyy年m

7、月”，主要刻度单位“2月”；数值轴格式：黑体、14号字、红色；数字格式为数值型，无小数点，刻度最小值为5000。图2.9 格式化的簇状柱形图图表工作表示例2) 操作方法（1） 标题格式设置  单击图表标题“金额、本息对照图”，选中所有字符，在“开始”|“字体”功能组中设置字符格式为楷体、28号字、蓝色、加粗；  单击图例，在“开始”|“字体”功能组中设置字符格式为楷体、16号字；双击图例，打开“设置图例项格式”对话框，单击“边框颜色”选项卡，设置边框颜色为“无线条”；  单击分类轴标题“存款日期”，在“开始”|“字体”功能组中设置字符格式为楷体、16号字

8、、浅蓝、加粗；  单击Y坐标轴标题“（元）”，在“开始”|“字体”功能组中设置字符格式为楷体、16号字、浅蓝、加粗；双击Y坐标轴标题“（元）”，打开“设置坐标轴标题格式”对话框，选择“对齐方式”选项卡，设置文字方向为：“横排”，垂直对齐方式为：“中部居中”，并将标题“（元）”拖到Y坐标轴上方的合适位置。 （2） 分类轴格式设置  单击X轴分类轴线，在“开始”|“字体”功能组中设置字符格式为黑体、14号字、红色；  双击X轴分类轴线，打开“设置坐标轴格式”对话框； 单击“数字”选项卡，设置“类别”为“日期”，“类型”为“yyyy年m月”，如图2.10所示；

9、图2.10 “数字”选项卡 图2.11 “坐标轴选项”选项卡 单击“坐标轴选项”选项卡，设置“主要刻度单位”为“固定：2、月”，如图2.11所示。（3） 数值轴格式设置  单击Y轴数值轴线，在“开始”|“字体”功能组中设置字符格式为黑体、14号字、红色；  双击Y轴数值轴线，打开“设置坐标轴格式”对话框； 单击“数字”选项卡，设置“类别”为“数字”，“小数位数”为“0”，去掉选中的“使用千位分隔符（,）”复选框，如图2.12所示；图2.12 “数字”选项卡 图2.13 “坐标轴选项”选项卡 单击“坐标轴选项”选项卡，设置“最小值”为“固定：5000”，如图2.13所示。(二

10、)  数据的管理和分析操作1. 制作工作表副本1) 操作要求打开cunkuan.xls工作簿，插入三个工作表Sheet1、Sheet4、Sheet5，将“家庭存款帐单”工作表中的全部数据复制到工作表Sheet1、Sheet2、Sheet3、Sheet4、Sheet5中。将各工作表中的数据设为最适合列宽，并保存工作簿。 2) 操作方法 （1） 打开cunkuan.xls工作簿，鼠标右键单击Sheet2工作表标签，打开快捷菜单，如图2.14所示，单击“插入”菜单，打开“插入”对话框，双击工作表图标，在Sheet2之前插入一个名为Sheet1的工作表。 （2）

11、 用同样的方法，在Sheet3之前插入工作表：Sheet4和Sheet5。 （3） 单击“家庭存款帐单”工作表标签，使之成为当前工作表，选中整个工作表（单击工作表全选标记行与列交叉的左上角区域），在“开始”|“剪贴板”功能组中单击“复制”按钮，然后在Sheet1中的A1单元格进行粘贴。在Sheet2、Sheet3、Sheet4、Sheet5中重复粘贴操作。（4） 选中整个工作表，在“开始”|“单元格”功能组中单击“格式”按钮，选择“自动调整列宽”，设置各工作表中的数据为最合适的列宽，并保存修改后的工作簿。图2.14 插入工作表2. 数据排序操作 1) 操作要求将S

12、heet1工作表重命名为“排序”，并在该工作表中按“存期”升序排列，“存期”相同时再按“存款日期”降序排列。 2) 操作方法（1） 双击Sheet1标签，输入“排序”，然后单击工作表中的任意单元格，或按回车键，完成对工作表的重命名。 （2） 选中A2:H14单元格区域，单击“数据”|“排序”命令，打开“排序”对话框。 （3） 从“主要关键字”区域下拉列表框中选择“存期”，从“排序依据”区域下拉列表框中选择“数值”，从“次序”区域下拉列表框中选择“升序”；单击“添加条件”按钮，增加新的排序条件；从“次要关键字”区域下拉列表框中选择“存款日期”，从“排序依据”区域下拉

13、列表框中选择“数值”，从“次序”区域下拉列表框中选择“降序”；勾选“数据包含标题”复选框（如不勾选该复选框，系统会把字段名混同数据一起排序），如图2.15所示。图2.15 “排序”对话框（4） 单击“确定”按钮，进行排序，排序结果如图2.16所示 。图2.16 排序结果示例提示：当对选中数据区域中的任意单元格进行排序时，系统默认选中所有数据作为排序对象。如果只对部分数据排序，应预先选择待排序的数据区域，然后再执行排序命令。 3. 自动筛选和高级筛选操作 1) 自动筛选 （1） 操作要求将Sheet2工作表重命名为“自动筛选”，然后自动筛选出“金

14、额”大于20030且“存款银行”为“工商银行”和“中国银行”的记录。 （2） 操作方法  双击Sheet2标签，输入“自动筛选”，然后单击工作表中的任意单元格，或按回车键，完成对工作表的重命名。  选中A2:H14单元格区域， 在“数据”|“排序和筛选”功能组中单击“筛选”命令按钮，工作表中每个字段名后将出现一个下拉箭头按钮，如图2.17所示。 图2.17 自动筛选操作 单击“金额”右侧的下拉箭头按钮，在下拉列表框中选择“数字筛选”|“自定义筛选”项，打开“自定义自动筛选方式”对话框，按图2.18所示进行设置，单击“确定”按钮，此时，工作表中只显示出“金

15、额”大于20030的记录。 图2.18 设置“金额”自动筛选条件 单击“存款银行”右侧的下拉箭头按钮，在下拉列表框中选择“文本筛选”|“自定义筛选”项，打开“自定义自动筛选方式”对话框，按图2.19所示进行设置，单击“确定”按钮。此时，在工作表中显示的是“金额”大于20030且存款银行为“中国银行”或“工商银行”的记录，筛选结果如图2.20所示。图2.19 设置“存款银行”自动筛选条件图2.20 自动筛选结果示例筛选结束后，“金额”和“存款银行”字段名后的下拉箭头按钮变为漏斗形状，这两条记录的行号也变为蓝色，如图2.20所示，说明这些字段及记录是经过自动筛选的。&

16、#160;思考：在设置“存款银行”为“工商银行”和“中国银行”这两个条件时，为什么选择的是“或”关系，而不是“与”关系？ 2) 高级筛选 （1） 操作要求将Sheet4工作表重命名为“高级筛选”。在工作表中对“本息”和“存期”进行高级筛选。筛选条件为：“本息>=20030”并且“存期<3”，或者“存期=5”，条件区域存放在A19开始的单元格区域，筛选结果存放在A23开始的单元格区域。 （2） 操作方法  双击Sheet4标签，输入“高级筛选”，然后单击工作表中的任意单元格，或按回车键，完成对工作表的重命名。  填写筛选条件。将字段名

17、“本息”、“存期”复制到A19开始的单元格，在单元格A20中输入“>=20030”，单元格B20中输入“<3”，单元格B21中输入“=5（参见图2.22所示）。 注意：筛选条件在同一行表示“与”的关系，在同一列不同行表示“或”的关系。 插入点定位在数据清单任一位置，在“数据”|“排序和筛选”功能组中，单击“高级”命令，打开“高级筛选”对话框。 设置“列表区域”、“条件区域”和结果“复制到”的内容。 设置“列表区域”时，可以直接输入区域$A$2:$H$14，或直接在数据清单中选择数据区域。用同样的方法设置“条件区域”和结果“复制到”的位置，如图2.21所示。提示：

18、在填写条件区域时，条件字段名最好采用复制、粘贴的方式填写，确保与数据清单中的字段名格式保持一致。 单击“确定”按钮执行高级筛选操作，筛选结果如图2.22所示。图2.21 “高级筛选”对话框条件区域高级筛选结果图2.22 高级筛选示例4. 分类汇总操作 1) 操作要求将工作表Sheet5重命名为“分类汇总”，并按不同的“存期”对“金额”和“本息”进行汇总。 2) 操作方法 （1） 双击Sheet5标签，输入“分类汇总”，然后单击工作表中的任意单元格，或按回车键，完成对工作表的重命名。 （2） 将插入点定位在“存期”列中的任一单元格，在“

19、数据”|“排序和筛选”功能组中，单击“排序”按钮（升序，也可单击“降序”按钮）。 （3） 在“数据”|“分级显示”功能组中，单击“分类汇总”命令按钮，打开“分类汇总”对话框。 （4） 在“分类汇总”对话框中设置参数。 “分类字段”：从下拉列表框中选择“存期”（排序字段）；“汇总方式”：从下拉列表框中选择“求和”； “选定汇总项”：从下拉列表框中选中“金额”和“本息”复选框； 其他参数可取系统默认的设置，如图2.23所示。 图2.23 “分类汇总”对话框（5） 单击“确定”按钮，完成分类汇总操作，其结果如图2.24所示。 图2.24 “分类汇总”结果示例(三) 综合实例操作在桌面上新建一个文件夹，以你的班级、姓名和学号命名，在该文件夹中新建一个Excel文件（即工作簿），并将文件重命名为“期末成绩统计表.xlsx”。按要求输入表格中的数据，设计表格的版式，制作统计图表，并对数据进行管理。 将综合实例的操作步骤和操作过程的心得体会写入实验报告，将综合实例的最终效果截图粘贴到实验报告。操作要求：1. 工作表的编辑和排版(1) 输入表格的标题和字段名。字段名包括学号、姓名、性别、课程名6门(如高等数学、英语、大学物理等)、平均分、总分和成绩排名。(2) 输入数据，数据内容自由发挥，如“20150012345