第5部分电子表格软件实验

Size: px

Start display at page:

Download "第5部分电子表格软件实验"

倏搅乐
7 years ago
Views:

1 第 5 部分电子表格软件实验实验 1 Excel 的基本操作一实验目的 1. 掌握 Excel 2003 的工作界面 2. 掌握工作簿建立保存打开的方法 3. 掌握公式和函数的应用 4. 掌握数据的编辑, 单元格的设置 5. 掌握工作表的插入新建复制移动删除和重命名二实验重点 1. 启动和退出 Excel 工作簿的操作 3. 工作表的操作 4. 单元格的操作 5. 公式和函数的使用三应用样例 1. 要求 : 启动 Excel, 在空白工作表中 A1:F15 区域内输入以下数据如表 4-1 所示, 并以 2009 第一季度.xls 为文件名保存在当前文件夹表 4-1 样例 (1) 打开 2009 第一季度.xls, 在姓名所在的行上方添加两行 (2) 将 B1:H1 合并单元格, 并添加表头, 设置表头为某校部分教师一季度工资发放统计

(3) 要求字体为隶书, 加粗,14 号字 (4) 设置单元格内的数据跨列居中垂直居中对齐 (5) 将 A2:E2 合并, 并输入制表日期 :2009 年 4 月 30 日, 设置左对齐 (6) 将 A3:F16 区域内, 文本数据单元格, 居中对齐, 数值数据单元格除工作量项外, 设置保留小数点后两位小数, 并添加货币符号 (7) 在基本工资项的左侧添加一列, 性别项, 除第

2 (3) 要求字体为隶书, 加粗,14 号字 (4) 设置单元格内的数据跨列居中垂直居中对齐 (5) 将 A2:E2 合并, 并输入制表日期 :2009 年 4 月 30 日, 设置左对齐 (6) 将 A3:F16 区域内, 文本数据单元格, 居中对齐, 数值数据单元格除工作量项外, 设置保留小数点后两位小数, 并添加货币符号 (7) 在基本工资项的左侧添加一列, 性别项, 除第 4,9,10 行为女教师, 其它各行均为男教师 (8) 学校发放洗理费, 在工作量项的左侧添加一列, 洗理费项, 学校规定男教师发放 80 元, 女教师 60 元, 使用 IF 函数, 完成输入 (9) 删除职务一列, 并使右侧单元格左移 (10) 将工作量项一列剪切, 并粘贴在 Sheet2 中的 A1 单元格开始 (11) 学校发放补助, 在 G3 中输入补助, 按照各个系的创收不同, 计算机系的教师补助 110 元, 机械系的教师补助 90 元, 使用 IF 语句完成数据输入 (12) 在单元格 J3 中输入实发金额, 实发金额的计算公式为基本工资 + 津贴 + 洗理费 + 补助 = 实发金额, 使用公式进行计算填充 (13) 将 Sheet3 工作表删除 (14) 将 Sheet1 的工作表标签改为教师工资 (15) 将工作表标签 Sheet2, 设置为绿色 2. 操作步骤 / 操作指导 : (1) 将活动单元格的位置定义在第一行, 单击鼠标右键, 在快捷菜单中选择插入, 如图 5-1 所示, 在弹出的插入对话框中选择整行, 单击确定, 同样的方法添加第二行提示也可以将活动单元格定义在第一行后, 选择插入行, 完成操作 (2) 用鼠标拖拽的方式选中 B1:H1 区域, 执行格式菜单单元格, 在弹出的单元格格式对话框的对齐选项卡中, 选择合并单元格, 如图 5-2 所示将某校部分教师一季度工资发放统计输入, 完成设置提示也可以在选中的区域上单击鼠标右键, 在快捷菜单中选择设置单元格格式图 5-1 插入图 5-2 单元格格式对齐 (3) 选中该单元格或该单元格中的数据, 单击鼠标右键, 选择设置单元格格式, 在字体选项卡下, 选择隶书 14 号加粗, 如图 5-3 所示提示也可以选择该单元格后, 通过格式工具栏, 直接选择相应的格式 (4) 选中该单元格, 执行格式菜单单元格, 在单元格格式对话框的对齐选项卡下, 选择跨列居中和垂直居中如图 5-2 所示

图 5-3 单元格内的文字格式 (5) 使用鼠标拖拽的方式选择 A2:E2 区域, 重复步骤 (2), 选中该区域在编辑区输入制表日期 :2009 年 4 月 30 日, 打开单元格格式对话框, 在对话框中的对齐选项卡下, 选择靠左 ( 缩进 ) 如图 5-4 所示图 5-4 水平对齐靠左 ( 缩进 ) (6) 使用鼠标拖拽的方式先选中 A3:C16 区域,

3 图 5-3 单元格内的文字格式 (5) 使用鼠标拖拽的方式选择 A2:E2 区域, 重复步骤 (2), 选中该区域在编辑区输入制表日期 :2009 年 4 月 30 日, 打开单元格格式对话框, 在对话框中的对齐选项卡下, 选择靠左 ( 缩进 ) 如图 5-4 所示图 5-4 水平对齐靠左 ( 缩进 ) (6) 使用鼠标拖拽的方式先选中 A3:C16 区域, 然后按下 Ctrl 键, 拖拽选择 D3:F3 区域, 则将文本数据单元格区域全部选中, 如图 5-5 所示, 通过执行格式菜单单元格格式在对齐选项卡下设为水平居中和垂直居中拖拽选中数值数据单元格区域 D4:E16, 通过执行格式菜单单元格格式在数值选项卡下, 选择货币, 并在小数位数后选择 2, 货币符号选择, 如图 5-6 所示图 5-5 工作表内多区域的选择

图 5-6 数字数据的类型修改 (7) 选中基本工资项的这一列中任意单元格, 执行插入菜单列, 则在左侧自动出现一列, 输入 D3 单元格内输入性别, 按照要求, 分别在第 D4,D9,D10 单元格输入女, 该列中的其它各行均为男 (8) 同步骤 (7), 在工作量的左侧增加一列洗理费, 选中 G4 单元格, 执行插入菜单函数, 在函数选择对话框中, 选择 IF, 单击

4 图 5-6 数字数据的类型修改 (7) 选中基本工资项的这一列中任意单元格, 执行插入菜单列, 则在左侧自动出现一列, 输入 D3 单元格内输入性别, 按照要求, 分别在第 D4,D9,D10 单元格输入女, 该列中的其它各行均为男 (8) 同步骤 (7), 在工作量的左侧增加一列洗理费, 选中 G4 单元格, 执行插入菜单函数, 在函数选择对话框中, 选择 IF, 单击确定, 在弹出的函数参数对话框中设置, 如图 5-7 所示使用填充柄, 利用函数的相对引用特点, 向下进行填充提示也可以在编辑区直接输入函数: 图 5-7 IF 函数的函数参数 (9) 鼠标拖拽选择工作量一列, 在选中区域上单击鼠标右键, 在快捷菜单中选择剪切 (10) 鼠标左键单击 Sheet2 标签, 将活动单元格定位在 A1 单元格中, 通过编辑菜单粘贴进行移动 (11) 鼠标单击 Sheet1 标签, 选中 G3 单元格, 输入补助, 设活动单元格地址为 G4, 在编辑区直接输入, 使用自动填充柄填充剩余部分, 完成操作 (12) 在 H3 中输入实发金额, 利用基本工资 + 津贴 + 洗理费 + 补助 = 实发金额, 将鼠标定位 H4 上, 直接输入 =d4+e4+f4+g4, 如图 5-8 所示, 单击回车完成公式编辑, 然后使用自动填充柄, 将剩余各项填充图 5-8 公式的编辑 (13) 在 Sheet3 工作表标签上单击右键, 选择删除命令 (14) 在 Sheet1 工作表标签上单击右键, 选择重命名命令, 标签处于可编辑

5 状态, 直接进行输入教师工资即可 (15) 在 Sheet2 工作表标签上单击右键, 在快捷菜单中选择工作表标签颜色在设置工作表标签颜色对话框中选择绿色, 单击确定完成操作如图 5-9 所示图 5-9 设置工作表标签颜色图 5-10 样例四实验作业 1. 创建一个工作表, 以宿舍编号命名 2. 统计宿舍的所有成员每个星期的支出状况 3. 设置工作表标题为某某宿舍个人支出统计表 4. 要求工作表标题跨列居中 5. 分类显示姓名, 伙食, 副食, 饰品, 电话费, 交通费, 杂费, 勤工俭学是否戴眼镜等 6. 所有费用相关的单元格, 设置货币格式, 文本数据居中排列 7. 添加共花费项, 设计公式进行计算 8. 保存该工作表

6 实验 2 Excel 的图表制作一实验目的 1. 掌握嵌入图表和独立图表的创建 2. 掌握图表的整体编辑和对图表中各对象的编辑方法 3. 了解 Excel 的各种图表功能二实验重点 1. 根据工作表中的数据创建图表 2. 图表的设置选项三应用样例 1. 要求 : (1) 打开 2009 第一季度.xls, 选中部分教师的姓名基本工资津贴洗理费四项的数据, 创建图表 (2) 删除图表中洗理费的数据系列, 然后再将基本工资和津贴的数据序列次序对调 (3) 为图表中为基本工资数据系列增加以值显示的数据标记 (4) 将图例显示靠左排列 (5) 为图表添加分类轴标题 name (6) 设置图表标题部分教师工资统计设置为新宋体,20 磅, 按样例放置 (7) 将图表区的字体大小更改为 8 磅, 选用最粗的圆角边框 (8) 将数值轴 Y 轴的主要刻度改为 600 (9) 去掉背景墙的区域图案 (10) 对建立的嵌入图表复制到 A22 单元格开始的区域, 并将簇状柱形图改为数据点折线图 (11) 对图形区的背景图例等按照样例将其格式化 (12) 将部分教师的实发工资项, 制作成三维饼图图表, 并独立插入工作簿中 2. 操作步骤 / 操作指导 : ( 1 ) 打开 2009 第一季度.xls, 使用鼠标拖拽, 并配合 Ctrl 键, 选择 A3:A9,D3:D9,E3:E9,F3:F9 区域, 单击常用工具栏中的按钮, 打开图表向导对话框, 如图 5-11 所示在图表类型中, 选择柱形图, 单击按下不放可查看实例按钮, 单击下一步在向导的步骤 2 数据区域选项卡下确定图标的数据区域, 如图 5-12 所示如果图标反应的数据区域错误, 则在数据区域后面的文本框中进行修改, 或者单击文本框后的按钮, 回到工作表中, 使用鼠标拖拽的方式, 重新选定源数据, 单击下一步下一步, 在图表向导步骤四中, 选择作为其中对象插入, 如图 5-13 所示

图 5-11 图表向导 - 图表类型图 5-12 图表向导 - 图标源数据图 5-13 图表向导 - 图标位置 (2) 在图表区, 在代表洗理费颜色的区域上单击鼠标右键, 选择清除在表示数据的柱形上单击鼠标右键, 选择数据系列格式, 打开数据序列格式对话框系列次序选项卡, 选中基本工资, 单击下移按钮, 或者选择津贴上移, 如图

7 图 5-11 图表向导 - 图表类型图 5-12 图表向导 - 图标源数据图 5-13 图表向导 - 图标位置 (2) 在图表区, 在代表洗理费颜色的区域上单击鼠标右键, 选择清除在表示数据的柱形上单击鼠标右键, 选择数据系列格式, 打开数据序列格式对话框系列次序选项卡, 选中基本工资, 单击下移按钮, 或者选择津贴上移, 如图 5-14 所示, 单击确定完成操作图 5-14 移动数据系列次序 (3) 在表示基本工资数据的柱形上单击鼠标右键, 选择数据序列格式, 打开数据序列格式对话框数据标志选项卡, 在值前面的方框中单击, 如图 5-15 所示图 5-15 添加值的数据标签 (4) 在图表区的图例上单击鼠标右键, 选择图例格式, 打开图例格式对话

框位置选项卡靠左, 如图 5-16 所示, 完成操作图 5-16 移动图例的位置 (5) 在图表区的空白处, 单击鼠标右键, 选择图表选项, 在标题选项卡中的分类 (X) 轴下方的文本框中输入 name, 如图 5-17 所示图 5-17 更改分类轴标题 (6) 重复步骤 (5), 在图表选项的标题选项卡下, 图表标题下的文本框中输入部分教师工资统计, 单击确定

8 框位置选项卡靠左, 如图 5-16 所示, 完成操作图 5-16 移动图例的位置 (5) 在图表区的空白处, 单击鼠标右键, 选择图表选项, 在标题选项卡中的分类 (X) 轴下方的文本框中输入 name, 如图 5-17 所示图 5-17 更改分类轴标题 (6) 重复步骤 (5), 在图表选项的标题选项卡下, 图表标题下的文本框中输入部分教师工资统计, 单击确定鼠标右键单击图表区的图表标题, 选择图表标题格式, 打开图表标题格式对话框字体选项卡, 设置为新宋体, 20 磅, 如图 5-18 所示图 5-18 设置图表标题字体格式 (7) 在图表区空白处单击右键, 在快捷菜单中选择图表区格式, 打开图表区格式对话框字体选项卡下字体大小更改为 8 磅, 如图 5-19 所示, 在图案选项卡中的圆角, 阴影前的方框中选中, 在粗细后面的下拉菜单中选用最粗的线条, 如图 5-20 所示

9 图 5-19 图表区字体的设置图 5-20 设置图表区边框 (8) 在图表区的数值轴 Y 轴上单击鼠标右键, 在快捷菜单中选择坐标轴格式, 打开坐标轴格式对话框刻度选项卡, 在主要刻度单位后的文本框中输入 600, 如图 5-21 所示, 单击确定完成操作图 5-21 坐标轴刻度的设置 (9) 在图表区的绘图区中单击鼠标右键, 在快捷菜单中选择绘图区格式, 在区域项目下选择无, 如图 5-22 所示图 5-22 绘图区格式的调整

(10) 在图表区的空白处单击鼠标右键, 在快捷菜单中选择复制命令, 鼠标右键单击单元格 A22, 选择粘贴, 在 A22 的图表空白处单击鼠标右键, 在快捷菜单中选择图表类型, 打开图表类型对话框, 在标准类型中选择折线图, 子图表类型中选择数据点折线图, 如图 5-23 所示图 5-23 更改图标类型 (11) 鼠标右键在网格线上单击, 选择网格线格式, 在图案选项卡下,

10 (10) 在图表区的空白处单击鼠标右键, 在快捷菜单中选择复制命令, 鼠标右键单击单元格 A22, 选择粘贴, 在 A22 的图表空白处单击鼠标右键, 在快捷菜单中选择图表类型, 打开图表类型对话框, 在标准类型中选择折线图, 子图表类型中选择数据点折线图, 如图 5-23 所示图 5-23 更改图标类型 (11) 鼠标右键在网格线上单击, 选择网格线格式, 在图案选项卡下, 更改为最粗的黑色虚线, 如图 5-24 所示图 5-24 设置网格线格式 (12) 选中部分教师的姓名和实发工资项, 执行按钮, 在图表向导中选择饼图三维饼图, 如图 5-25 所示, 单击完成在新的图表区空白处单击鼠标右键, 在快捷菜单中选择位置, 如图 5-26 所示, 作为新的工作表插入在饼区上单击鼠标右键, 打开数据系列格式对话框, 并添加值的数据标签, 见步骤 (3) 图 5-25 选择图表类型图 5-26 设置图标位置

11 部分教师工资统计基本工资津贴 8, , , , , , , , , , , , , , , , , , , 张长虹闫凯强卢波李新涛李小龙郑伟 name 图 5-27 图表样例 1 部分教师工资统计基本工资津贴 8, , , , , , , , , , , , , , , , , , , 张长虹闫凯强卢波李新涛李小龙郑伟 name 图 5-28 图表样例 2 实发金额 5, , 张长虹闫凯强 5, 卢波 8, 李新涛 6, 李小龙四实验作业图 5-29 图表样例 3 1. 打开以宿舍编号命名的工作表 2. 制作三维簇状柱形图, 图表来表示工作表中的四项数据 3. 图表标题设置为宋体 4 号字, 图表区其他字符大小设置为 8 磅 4. 绘图区背景颜色设置为黄色 5. 网格线设置为绿色虚线 6. 以花费最少的同学的支出钱数, 作为坐标轴的主要刻度 7. 复制该图表至 B32 单元格开始, 并将图表类型更改为簇状条形图 8. 将姓名项和共花费项作为源数据, 制作图表三维饼图, 并将值作为数据标签

12 实验 3 数据管理一实验目的 1. 掌握数据的排序 2. 掌握数据的自动筛选 3. 掌握数据的分类汇总 4. 了解数据透视表的操作二实验重点 1. 数据的排序 2. 数据的自动筛选功能 3. 数据分类汇总功能的使用三应用样例 1. 要求 : (1) 打开 2009 第一季度.xls, 按照实发工资项, 由低到高重新进行排序 (2) 使用自动筛选功能显示计算机系, 基本工资在 5000 元以上的教师数据 (3) 使用分类汇总, 显示男职工的平均工资和女职工的平均工资 (4) 分别统计男女教师的工资, 按实发金额进行求和汇总方式, 津贴按求平均数汇总方式, 汇总方式制作数据透视表, 将数据表的位置建于 A45 为起始位置 2. 操作步骤 / 操作指导 : (1) 选择需要排序的单元格区域,A3:H16, 执行数据菜单排序, 在排序窗口中设置主要关键字实发金额, 并选择升序排列, 如图 5-30 所示提示如果所选择的数据区域含有标题栏, 则在最下面选择有标题行 ; 若没有标题行, 选择后者图 5-30 排序窗口的设置 (2) 选择需要自动筛选的区域, 执行数据菜单筛选自动筛选, 如图 5-31 所示, 鼠标单击部门旁的下拉箭头, 选择计算机系 ; 鼠标单击基本工资

上的下拉箭头, 选择自定义, 在弹出的自定义自动筛选方式对话框中, 如图 5-32 所示左侧选择大于等于, 在右边的文本框中输入 5000, 单击确定完成操作图 5-31 对数据区域执行自动筛选功能图 5-32 自定义自动筛选方式的设置 (3) 选定需要分类汇总的区域, 执行数据菜单分类汇总, 在分类汇总对话框中设置

13 上的下拉箭头, 选择自定义, 在弹出的自定义自动筛选方式对话框中, 如图 5-32 所示左侧选择大于等于, 在右边的文本框中输入 5000, 单击确定完成操作图 5-31 对数据区域执行自动筛选功能图 5-32 自定义自动筛选方式的设置 (3) 选定需要分类汇总的区域, 执行数据菜单分类汇总, 在分类汇总对话框中设置分类字段为性别, 汇总的方式选择平均值, 汇总项为实发金额, 如图 5-33 所示, 单击确定完成操作图 5-33 分类汇总的设置图 5-34 分类汇总 - 样例 (4) 选择数据数据透视表和数据透视图, 打开数据透视表和数据透视图向导, 在数据源类型中选择 Microsoft Office Excel 数据列表或数据库, 在

所需创建的报表类型中选择数据透视表, 如图 5-35 所示, 单击下一步图 5-35 数据透视表和数据透视图向导步骤 1 在步骤 2 中选定源数据区域, 在选定区域后面的文本框中编辑选定区域地址, 如图 5-36 所示 ; 或者单击文本框右边的按钮, 在原工作表中, 使用鼠标拖拽的方式选择选择数据区域 A3:H16 图 5-36 选择源数据区域在数据透视表和数据透视图向导步骤 3

14 所需创建的报表类型中选择数据透视表, 如图 5-35 所示, 单击下一步图 5-35 数据透视表和数据透视图向导步骤 1 在步骤 2 中选定源数据区域, 在选定区域后面的文本框中编辑选定区域地址, 如图 5-36 所示 ; 或者单击文本框右边的按钮, 在原工作表中, 使用鼠标拖拽的方式选择选择数据区域 A3:H16 图 5-36 选择源数据区域在数据透视表和数据透视图向导步骤 3 中选择现有工作表, 并在下面的文本框中输入 A45, 如图 5-37 所示 ; 左键在工作表中的 A45 单元格上单击, 然后在向导对话框中单击确定完成操作图 5-37 数据透视表显示位置的设置在弹出的数据透视表字段列表中, 分别将实发金额, 津贴, 性别添加到数据透视表区, 如图 5-38 所示单击透视图表区的字段名, 打开数据透视表字段对话框, 在里面设置汇总的方式, 实发金额进行求和汇总方式, 津贴按求平均数汇总方式, 性别按计数汇总方式, 如图 5-39 所示提示如果在透视表区字段的位置错误, 可以通过鼠标拖拽字段名的方式, 更改字段位置, 也可以打开数据透视表向导, 在步骤 3 的窗口中单击布局按钮, 如图 5-37 所示, 对数据透视表中的字段重新布局或进行修改如图 5-40 所示.

15 图 5-38 添加数据透视表字段图 5-39 修改数据透视表字段的汇总方式图 5-40 布局窗口四实验作业图 5-41 数据透视表 - 样例 1. 打开以宿舍编号命名的工作表, 执行以下操作 2. 按共花费项由低到高进行排序 3. 使用自动筛选的功能, 筛选出伙食最高的同学的数据 4. 将戴眼镜同学和不戴眼镜同学的平均花费显示, 使用分类汇总的功能

60 新编计算机应用基础实训指导 (2) 单元格显示格式设置选中 (A1:I1) 单元格区域, 在格式工具栏中选择加粗按钮, 将字体设置为加粗显示 ; 选择居中显示按钮, 将字体设置为居中显示 ; 选择底纹设置按钮, 将单元格底纹设置为黄色, 显示结果如图 3 3 所示图 3

60 新编计算机应用基础实训指导 (2) 单元格显示格式设置选中 (A1:I1) 单元格区域, 在格式工具栏中选择加粗按钮, 将字体设置为加粗显示 ; 选择居中显示按钮, 将字体设置为居中显示 ; 选择底纹设置按钮, 将单元格底纹设置为黄色, 显示结果如图 3 3 所示图 3 模块三电子表格软件 Excel 训练项目 1 教师人事档案管理表的录入与编辑训练目标掌握 Excel 2003 工作簿的建立保存与打开方法掌握工作表中各种不同类型数据的输入方法掌握单元格格式的设置方法掌握表格边框和底纹的设置方法掌握特殊符号的输入方法训练内容制作教师人事档案管理表, 最终显示效果如图 3 1 所示图 3 1 教师人事档案管理表样本实训步骤 (1) 启动 Excel

第5部分 电子表格软件实验

第5部分电子表格软件实验