实验三 电子表格Excel实验(2015)(12页).doc

-实验三 电子表格Excel实验一、实验目的与要求1.掌握Excel 2010的启动与退出方法及工作情况。2.掌握数据的输入、编辑方法和填充柄的使用方法。3.掌握工作表的格式化方法及格式化数据的方法。4.掌握字形、字体和框线、图案、颜色等多种对工作表的修饰操作。5.掌握公式和常用函数的输入与使用方法。6.掌握图表的创建、编辑和格式化方法。7.掌握对数据进行常规排序、筛选和分类汇总的操作方法。8.掌握数据透视表的应用。二、实验学时6学时三、实验内容1.创建和编辑工作表从A1单元格开始，在sheet1工作表中输入如图4­3所示的数据。图4­3期末成绩统计表单击单元格A1输入“期末成绩统计表”并按下回车键。在单元格A3、A4中分别输入“10401”和“10402”；选择单元格区域A3：A4，移动鼠标至区域右下角，待鼠标形状有空心十字变成实心十字时(通常称这种状态为“填充柄”状态)，向下拖曳鼠标至A12单元格时放开鼠标。在单元格A13、A14分别输入“最高分”和“平均分”。在A15中输入“分数段人数”； 然后输入其余部分数据。2.选取单元格区域操作(1)单个单元格的选取单击 “Sheet2” 工作表卷标，用鼠标单击B2单元格即可选取该单元格。(2)连续单元格的选取单击B3单元格，按住鼠标左键并向右下方拖动到F4单元格，则选取了B3：F4单元格区域；单击行号“4”，则第4行单元格区域全部被选取；若按住鼠标左键向下拖动至行号“6”，松开鼠标，则第4,5,6行单元格区域全部被选取；单击列表“D”，则D列单元格区域全部被选取。同样的，我们可以选取其他单元格区域。(3)非连续单元格区域的选取先选取B3：F4单元区域，然后按住【Ctrl】键不放，再选取D9、D13、E11单元格，单击行号“7”，单击列号“H”，如图4­4所示。图4­4非连续单元格的选取3.单元格数据的复制和移动(1)单元格数据的复制在Sheet1工作表中选取B2：E4单元格区域，选择“编辑”菜单中的“复制”选项或者单击工具栏中的“复制”按钮，单击Sheet2工作表标签后单击A1单元格，选择“编辑”菜单中的“粘贴”选项或者单击工具栏中的“粘贴”按钮，就可完成复制工作。(2)单元格数据的移动选取Sheet2工作表中的A1：D3单元格区域，将鼠标指针移到区域边框上，当鼠标指针变为十字方向箭头时，按住鼠标左键不放，拖动鼠标至B5单元格开始的区域，松开鼠标左键，即可完成移动操作。若拖动的同时，按住【Ctrl】键不放，则执行复制操作。4.单元格区域的插入与删除(1)插入或删除单元格在Sheet1工作表中，右键单击B2单元格，屏幕出现如图4­5所示的有四个选项的“插入”对话框，单击“活动单元格下移”单选按钮，观察姓名一栏的变化。-第 12 页-图4­5“插入”对话框图4­6“删除”对话框选取B2和B3单元格，右键单击对应单元格，屏幕出现如图4­6所示的有四个选项的“删除”对话框，单击“下方单元格上移”单选按钮，观察工作表的变化。最后单击两次“撤销”按钮，恢复原样。(2)插入或删除行选取第3行，执行“开始”| “单元格”| “插入”命令，即可在所选行的上方插入一行。选取已插入的空行，执行“开始”| “单元格”| “删除”命令，即可删除刚才插入的空行。选取第1行，插入两行空行，在A1单元格中输入“学生成绩表”，选取A1E1区域，再执行“开始”| “对齐方式”| “合并后居中”命令。(3)插入或删除列选取第B列，执行“开始”| “单元格”| “插入”命令，即可在所选列的左边插入一列。选取已插入的空列，执行“开始”| “单元格”| “删除”命令，即可删除刚才插入的空列。5.工作表的命名启动Excel 2010，在出现的Book1工作簿中双击Sheet1工作表标签，更名为“销售资料”，最后以“上半年销售统计”为名将工作簿保存在硬盘上。6.数据格式化(1)建立数据表格在“销售数据”工作表中按照如图4­7所示样式建立数据表格。其中，在A3单元格中输入“一月”后，可用填充句柄拖动到A8，自动填充“二月”“六月”。(2)调整表格的行高为18，列宽为14按住【CtrlA】组合键选中整张工作表，选择“开始”| “单元格”| “格式”菜单中的 “行高”选项，在“行高”对话框的文本框中输入18，单击“确定”按钮，用类似的方法设置列宽为14。(3)标题格式设置选取A1：H1，然后单击执行“开始”| “对齐方式”| “合并后居中”命令，使之成为居中标题。双击标题所在单元格，将光标定位在“公司”文字后面，按【AltEnter】组合键，则将标题文字放在两行。执行“格式”菜单中的“单元格”命令，将弹出如图4­8所示的“设置单元格格式”对话框，选择“字体”选项卡，将字号设为16，颜色设为红色。图4­8“单元格格式”对话框图4­7建立未格式化的表格(4)设置单元格中文字的水平方向和垂直方向为居中选中A3单元格，执行“开始”| “单元格”| “格式”命令，选择“设置单元格格式”，在“单元格格式”对话框中选择“对齐”选项卡，在“水平对齐”和“垂直对齐”下拉列表框中选择“居中”。用同样的方法将其余单元格中文字的水平方向和垂直方向设置为居中。(5)数字格式设置因为数字区域是销售额数据，所以应该将它们设置为“货币”格式。选取B3：H12区域，单击格式工具栏上的“货币样式”按钮，如图4­9所示。图4­9数字格式设置(6)边框、底纹设置全选表格区域所有单元格，执行“开始”| “单元格”| “格式”命令，选择“设置单元格格式”，在“单元格格式”对话框中选择“边框”选项卡，设置“内部”为细线，“外边框”为粗线。图4­10格式设置后的数据表格为了使表格的标题与数据以及源数据与计算数据之间区分明显，可以为它们设置不同的底纹颜色。选取需要设置颜色的区域，选择“单元格格式”对话框中选择“图案”选项卡，设置颜色。以上设置全部完成后，表格效果如图4­10所示。7.公式和函数(1)使用“自动求和”按钮。在“销售数据”工作表中，H3单元格需要计算一月份各种产品销售额的总计数值，可用“自动求和”按钮来完成。操作步骤如下：用鼠标单击H3单元格。单击常用工具栏上的 “开始”| “编辑”| “”命令，屏幕上出现求和函数SUM以及求和数据区域，如图4­11所示。图4­11单击“自动求和”按钮后出现的函数样式观察数据区域是否正确，若不正确请重新输入数据区域或者修改公式中的数据区域。单击编辑栏上的“”按钮，H3单元格显示对应结果。H3单元格结果出来之后，利用“填充句柄”拖动鼠标一直到H8可以将H3中的公式快速复制到H4：H8区域。采用同样的方法，可以计算出“合计”一列对应各个单元格的计算结果。(2)常用函数的使用。在“销售数据”工作表中，B10单元格需要计算上半年冰箱的平均销售额，可用AVERAGE函数来完成。操作步骤如下：用鼠标单击B10单元格。单击常用工具栏上的“”按钮旁的黑色三角，在出现的下拉菜单中选择“平均值”，屏幕上出现求平均值函数AVERAGE以及求平均值数据区域，如图4­12所示。图4­12单击“平均值”按钮后出现的函数样式观察数据区域是否正确。单击编辑栏上的“”按钮，B10单元格显示对应结果。B10单元格结果出来之后，利用“填充句柄”拖动鼠标一直到G10可以将B10中的公式快速复制到C10：G10区域。单击H10单元格，在编辑栏中输入公式：AVERAGE(H3：H8)，单击编辑栏上的“”按钮，可以计算“合计”中的平均值。采用同样的方法可以计算出“最高”和“最低”这两行对应的各个单元格的计算结果。8.在当前工作表中选择数据创建柱状图表(1) 打开“成绩分析.xls”文件，选择单元格B2：E6，执行“插入”菜单的“图表”命令，单击“”，打开如图4­16所示的对话框。(2)选择 “柱状图”，单击“确定”按钮，在图表中单击右键，快捷菜单中选择“选择数据”，打开如图4­17所示，单击“确定”按钮。图4­17“选择数据源”对话框图4­16“插入图表”对话框(3)在“图表工具”栏中单击“布局”，如图4­18所示，选择“图表标题”，填入“成绩对比分析”，选择“坐标轴标题”的“主要横坐标轴标题”栏填入“姓名”，在“主要纵坐标轴标题”栏填入“成绩”。图4­18“图标工具之布局”对话框 (4)单击图表空白处，即选定了图表。此时，图表边框上有8个小方块，拖曳鼠标移动图表至适当位置；或将鼠标移至方块上，拖曳鼠标改变图表大小，效果如图4­19所示。将结果存盘：单击“文件”菜单，选择“另存为”选项，输入文件名为“成绩分析new1”。图4­19完成后的效果图9.修改图表类型为折线图再次激活图表，选择“图表工具”|“设计”|“类型”菜单中的“更改图表类型”选项，显示如图4­20所示的“更改图表类型”菜单，单击“更改图表类型”，在弹出的对话框，如图4­21所示，选择“折线图”的第一个图，效果如图4­22所示。将结果存盘：单击“文件”菜单，选择“另存为”选项，输入文件名为“成绩分析new2”。图 4­20“更改图表类型”菜单图 4­21“更改图表类型”对话框图 4­22完成后的效果图10.创建饼图(1) 打开“成绩分析.xls”文件，选择单元格B15：C19，单击“插入”|“图表”|“饼图”，选择第一个，如图4-23所示。单击饼图，选择“图表工具”|“设计”|“图表布局”，选择布局6，如图4-24所示。(2)在饼图中修改图表标题为“数学成绩分析表”，最终效果如图4­25所示。图 4­24“图表布局”菜单图 4­23插入“饼图”菜单图 4­25完成后的效果图(3)用相同的方法制作出英语和计算机两门课程成绩的饼状分析图。将结果存盘：单击“文件”菜单，选择“另存为”选项，输入文件名为“成绩分析new3”。11.输入“员工薪水表”，对部门(升序)和薪水(降序)排列(1) 启动Excel，系统建立“Book1.xls”空白工作簿，在当前工作表中输入如表4­1所示数据。表4­1员工薪水表(2)选择A1：H21单元格，单击“数据”|“排序和筛选”菜单的“排序”命令，在如图4­26所示的“排序”对话框中，设置对部门升序和薪水降序，排序后的效果如图4­27所示。将结果存盘：单击“文件”菜单，选择“另存为”选项，输入文件名为“员工薪水”。图4­26“排序”对话框图4­27排序后的效果图12.筛选出在北京分公司软件部工作薪水高于5000元的员工(1)选择A1：H21单元格，单击“数据”菜单的“筛选”。(2)单击“部门”下拉组合框，勾选“软件部”， 单击“分公司”下拉组合框，勾选“北京”， 单击“薪水”下拉组合框，选择“数字筛选”中“自定义筛选”，在如图4­28所示的对话框中输入“大于或等于”和“5000”。(3)筛选出在北京分公司软件部工作薪水高于5000元的员工，筛选后的效果如图4­29所示。将结果存盘：单击“文件”菜单，选择“另存为”选项，输入文件名为“员工薪水new1”。图 4­28“自定义自动筛选方式”对话框图 4­29筛选后的效果图13.按照部门分类汇总，计算工作时数和薪水的平均值(1)打开 “员工薪水.xls”文件，选择A1：H21单元格，单击“数据”|“排序和筛选”菜单的“排序”命令，设置部门按照升序排序。(2)选择A1：H21单元格，单击“数据”|“分级显示”菜单的“分类汇总”命令，弹出如图4­30所示的“分类汇总”对话框；在该对话框中设置分类字段为“部门”，汇总方式为“平均值”，选定汇总项为“工作时数”和“薪水”；再选定“替换当前分类汇总”和“汇总结果显示在数据下方”。(3)选定后单击“确定”按钮，系统自动按照部门分类汇总，计算工作时数和薪水的平均值，分类汇总后的效果如图4­31所示。将结果存盘：单击“文件”菜单，选择“另存为”选项，输入文件名为“员工薪水new2”。图4­30“分类汇总”对话框图4­31分类汇总后的效果图14.利用数据透视表功能，计算各部门各分公司的员工薪水总额(1)打开 “员工薪水.xls”文件，选择A1：H21单元格，单击“插入”|“表格”菜单的“数据透视表”选项，选择数据透视表，出现如图4­32所示的“创建数据透视表”对话框，单击“确定”按钮，出现如图4­33所示的“数据透视表字段列表”对话框。(2)将“部门”拖到“行字段”处，将“分公司” 拖到“列字段”处，将“薪水” 拖到“数据项”处。计算各部门各分公司的员工薪水总额，计算结果如图4­34所示。图4­32 “创建数据透视表”对话框图4­33“数据透视表字段列表”对话框图4­34“数据透视表”计算结果图(3)将结果存盘：单击“文件”菜单，选择“另存为”选项，输入文件名为“员工薪水new3”。