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