EXCEL专家级别MOS专业认证练习图解培训讲学.ppt

MicrosoftOfficeSpecialist Excel2003Expert练习题解 微软办公软件国际认证 练习一 1 将单元格范围A4 G61转换成为一个列表项目 1 选择单元格范围A4 G61 执行 数据 列表 创建列表 建议讲解时间 10分钟建议练习时间 16分钟 2 数据范围已自动设定 勾选上 列表有标题 3 单击 确定 按钮 已完成的列表 2 设定让 婚姻状况 字段 列G 只可接受已婚或未婚为有效的数据项 4 选择G列 执行 数据 有效性 5 允许栏选择 序列 来源栏中输入 已婚 未婚 6 单击 确定 按钮 Tip 在中文输入法下输入已婚后 一定切换输入法到英文状态 输入分隔符 然后再输入未婚 序列输入方式 可以有效降低错误输入 故意输入 单身 文字 验证有限性设置是否正确 操作练习 练习二 在单元格范围B3 B47建立和套用数据格式 每个员工 员工编号 数据的前面加上U字母 1 选择单元格范围B3 B47 执行 格式 单元格 建议讲解时间 10分钟建议练习时间 16分钟 2 点选 自定义 3 输入 U 0 4 单击 确定 按钮 2 为工作簿创建自定义属性 属性名称为机密 加上 是 或 否 数据类型 并将值设为 是 此外 并将人事科输入为该工作簿的 源 5 执行 文件 属性 6 在名称框中输入 机密 类型选择 是或否 7 取值 是 8 单击 添加 按钮 9 名称选择 源 类型选择 文本 10 取值输入 人事科 11 单击 添加 按钮 12 单击 确定 按钮 操作练习 练习三 请将箭头图案和 最多 这三个字符组合在一起 然后将组合后的对象置于图表上方以指出最多的地区 1 选中文字框 按住Shift键 点选箭头图案 2 在选中的图案上单击鼠标右键 在弹出的选项框中 选择 组合 组合 建议讲解时间 10分钟建议练习时间 16分钟 3 在图案上再次单击鼠标右键 选择 叠放次序 置于顶层 4 拖动图案至要求位置 指示饼图中最大区块 2 请将单元格范围A4 A7的类别名称新增到图表的图例 然后从图表将 北部 数据块分离出来 5 在图表空白位置 图表区 单击鼠标右键 选择 源数据 6 利用鼠标重新选择数据区域 7 单击 确定 按钮 8 在饼图中北部区块单击鼠标左键 然后再次单击鼠标左键 选中北部区块 9 按住鼠标左键 拖拽北部区块 将其分离 Tip 设定完成后 在选择工作中任意单元格 箭头即可显示在图表上方 操作练习 练习四 1 请修改 得分图表 工作表中 数值 坐标轴的格式 使其只显示到小数点后一位数 1 在数值轴上单击鼠标右键 选择 坐标轴格式 建议讲解时间 10分钟建议练习时间 16分钟 2 选择 数字 项 3 分类选择 数值 4 小数位数选择 1 5 单击 确定 按钮 2 修改 分类 坐标轴的刻度 大小 让类别以相反的顺序显示 6 在分类轴上单击鼠标右键 选择 坐标轴格式 7 选择 刻度 项 8 勾选 分类次序反转 9 单击 确定 按钮 答案样张 操作练习 练习五 1 追踪所有提供数据给单元格E8之公式的单元格 然后编辑该公式来改正错误 1 选中单元格E8 执行 工具 公式审核 追踪引用单元格 建议讲解时间 10分钟建议练习时间 16分钟 2 错误的来源E4 文字无法做运算 应改成D4 3 改成D8 D 4 2 请根据已预设好的单元格有效性规则 把工作簿中的任何无效数据圈选出来 3 点一下B7 D25的任一单元格 执行 数据 有效性 来查看题目的有效性范围 4 题目数据有效性设置为 整数 范围0至10之间 5 无须作任何设置 单击 确定 按钮 6 执行 工具 公式审核 显示公式审核工具栏 7 单击 圈释无效数据 按钮 红色椭圆显示的数据即为不符合有效性设置的数据 操作练习 练习六 请将 放大 及 缩小 两个按钮新增到 常用 工具栏上的 恢复 按钮右边并置于新的群组中 1 执行 工具 自定义 建议讲解时间 10分钟建议练习时间 16分钟 2 选择 命令 项 类别 选择 视图 3 拖拽 放大 缩小 两个按钮至 恢复 按钮右边 4 单击 重排命令 按钮 5 选择 工具栏 常用 6 点选 放大 按钮 执行 更改所选内容 开始一组 7 单击 关闭 按钮 回到下一页面 再次单击 关闭 按钮 2 请将预设的工作表数目设定为4 并将用户名称设定为财务科 8 执行 工具 选项 9 新工作簿内的工作表数设为 4 用户名输入 财务科 10 单击 确定 按钮 操作练习 练习七 1 将 预算与实际 工作表保护起来 仅允许在单元格C5 C8中输入数据项 注意 请保留原有的预设值 1 选择单元格C5 C8 执行 格式 单元格 建议讲解时间 10分钟建议练习时间 16分钟 2 点选 保护 项 取消对 锁定 的勾选 3 单击 确定 按钮 4 执行 工具 保护 保护工作表 5 勾选 选定未锁定的单元格 6 单击 确定 按钮 2 修改预设设定 让工作簿能每隔8分钟自动地储存到C 下 7 执行 工具 选项 Tip 此时必须选择C5 C8中的任意一个单元格 8 分别输入 8 C 9 单击 确定 按钮 操作练习 练习八 1 在 收入分析 的XY散点图中新增 乘幂 趋势线并于该趋势线上显示方程式和R平方值 1 点选 收入分析 工作表 2 在图表中任一资料点上 单击鼠标右键 3 选择 添加趋势线 建议讲解时间 10分钟建议练习时间 16分钟 4 在 类型 项中 选择趋势线类型 乘幂 5 在 选项 项中 勾选 显示公式 显示R平方值 6 单击 确定 按钮 添加了趋势线的XY散点图 2 共享工作簿 允许一次可由多位使用者进行变更 并让该活页簿每隔10分钟更新一次变更的信息 7 执行 工具 共享工作簿 8 勾选 允许多用户同时编辑 同时允许工作簿合并 9 在 更新 项中 将自动更新间隔设为10分钟 10 单击 确定 按钮 11 单击 确定 按钮 操作练习 练习九 1 编辑名称为 页眉 的宏 让 中文系 这三个字插入页眉的左区段 1 执行 工具 宏 宏 建议讲解时间 10分钟建议练习时间 16分钟 2 选择 页眉 宏 单击 编辑 3 将引号中的内容改为 中文系 4 单击 运行子过程 用户窗体 按钮 单击 保存 按钮 5 单击 关闭 回到Excel视图 6 执行 打印预览 命令 查看宏运行的结果 7 单击 关闭 2 将自定义按钮新增到 常用 工具栏上 格式刷 的右边 将新按钮命名为系所页眉 并将该按钮指派给 页眉 宏 8 执行 工具 自定义 9 在 命令 项中选择类别 宏 10 拖拽 自定义按钮 至常用工具栏格式刷的右边 11 在 自定义 按钮上 单击鼠标右键 在命名框中输入 系所页眉 单击 指定宏 按钮 12 选择 页眉 宏 13 单击 确定 按钮 13 单击 关闭 按钮 操作练习 练习十 1 请使用位于我的文档文件夹中 名称为 员工资料 xsd 之结构描述的dataroot 就是 xsd档案 将 姓名 数据与单元格A1对应 电话 数据与单元格B1对应 部门 数据与单元格C1对应 而 职称 数据则与单元格D1对应 1 执行 数据 XML XML源 建议讲解时间 10分钟建议练习时间 16分钟 2 单击 XML映射 3 单击 添加 按钮 4 在我的文档文件夹下 选择 员工资料 xsd 5 单击 打开 按钮 6 选择 dataroot 项 单击 确定 按钮 7 单击 确定 按钮 8 根据题目要求 逐一拖拽四个项目至对应单元格 9 执行 数据 XML 导入 2 请使用位于我的文档文件夹中的 员工数据 xml 档案 将对应的XML数据导入工作簿 10 选择 员工资料 xml 单击 导入 完成资料导入 操作练习 练习十一 在 业绩总计 工作表的储存格C5中 插入一个公式 以计算 第一农场 第二农场 第三农场 和 第四农场 工作表的单元格C5总数 将该公式套用到单元格范围C6 C9 1 选中C5单元格 输入 第一农场 C5 第二农场 C5 第三农场 C5 第四农场 C5 建议讲解时间 5分钟建议练习时间 8分钟 按回车键便会出现计算结果 2 将光标停留在C5单元格的右下角实心小方块上 再按住鼠标左键拖曳到单元格C9后放开 即可完成公式复制 答案样张 操作练习 练习十二 1 组合显示B C D和E列 1 选中B C D和E列 2 执行 数据 组及分级显示 组合 建议讲解时间 10分钟建议练习时间 16分钟 通过单击 加号 及 减号 可实现资料的合并及展开 2 将 业绩 工作表中的图片还原回原来的大小 然后使用预设设定来压缩该图片 3 执行 视图 工具栏 图片 4 点选图片 单击 重设图片 按钮 5 点选图片 单击 压缩图片 按钮 6 单击 确定 按钮 操作练习 练习十三 1 将位于我的文档文件夹中的 公共科目期末考 B 工作簿的变更合并到 共同科目期末考 A 工作簿 1 执行 工具 比较和合并工作簿 建议讲解时间 10分钟建议练习时间 16分钟 2 选择我的文档文件夹中的 公共科目期末考 B 工作簿 3 单击 确定 按钮 2 显示工作簿中所有的修订 拒绝单元格B3 C3 和D3中的修订 然后接受其它所有的修订 4 执行 工具 突出显示修订 6 单击 确定 按钮 5 时间项选择 全部 7 执行 工具 修订 接受或拒绝修订 8 单击 确定 按钮 9 单击 拒绝 按钮 拒绝B3单元格的修订 以此类推 拒绝C3 D3单元格的修订 其它单元格单击 全部接受 按钮 操作练习 练习十四 将单元格F2和F3当成结果单元格使用 为工作簿假设情况建立方案摘要 1 执行 工具 方案 建议讲解时间 10分钟建议练习时间 16分钟 2 单击 摘要 按钮 3 选择结果单元格F2 F3 4 单击 确定 按钮 完成结果 2 在 方案摘要 工作表上 用基金这个名称将对应的XML数据导出为 XML文件 放到 我的文档 文件夹中 5 执行 数据 XML XML源 6 执行 数据 XML 导出 7 输入文件名 基金 单击 导出 按钮 操作练习 练习十五 1 加载 然后使用 分析工具库 分析Toolpak 来执行 回归 分析以决定 冰淇淋销售量 从属变数 Y 与 气温 独立变量 X 之间的相互关系 注意 请接受所有的默认设定 1 执行 工具 加载宏 建议讲解时间 10分钟建议练习时间 16分钟 2 勾选 分析工具库 单击 确定 按钮 Tip 在安装 分析工具库 的过程中 Excel2003可能会要求放入安装光盘 请将安装光盘准备好 3 执行 工具 数据分析 4 分析工具选择 回归 5 单击 确定 按钮 6 输入Y X值输入区域 7 单击 确定 按钮 分析结果 2 储存档案 规定需要密码ICECREAM才能开启和修改工作簿 注意 接受所有的默认设定值 8 执行 文件 另存为 9 单击 工具 选择 常规选项 10 在打开权限密码和修改权限密码框中分别输入 ICECREAM 11 单击 确定 按钮 12 重新输入密码 ICECREAM 13 单击 确定 按钮 14 重新输入修改权限密码 ICECREAM 15 单击 确定 按钮 16 单击 保存 按钮 操作练习 练习十六 1 在单元格F28中新增单元格监视窗口 注意 将窗口保留在开启状态 1 执行 工具 公式审核 显示监视窗口 建议讲解时间 10分钟建议练习时间 16分钟 2 单击 添加监视 3 选择需要添加监视的单元格F28 4 单击 添加 按钮 完成监视窗口的添加 2 在单元格A5中 将名称为 员工 的逗号分隔文本文件汇入 注意 接受其它所有的预设设定 5 执行 数据 导入外部数据 导入数据 6 选择 员工 文本文件 单击 打开 按钮 7 点选 分隔符号 单击 下一步 8 点选分隔符号为逗号 单击 下一步 9 单击 完成 10 数据放置位置选择A5 11 单击 确定 按钮 答案样张 操作练习 练习十七 1 使用准则范围A4 H6 筛选储存格范围A8 H53来显示2002年1月1日前入社的所有 向导组 社员以及2003年1月1日后入社的所有 文书组 社员 1 根据题目要求输入筛选条件 2 执行 数据 筛选 高级筛选 建议讲解时间 10分钟建议练习时间 16分钟 3 列表区域A8 H53 条件区域A4 H6 4 单击 确定 按钮 筛选结果 2 建立一个名称为 隐藏行 宏 将其储存在目前的工作簿 以隐藏第3到第7行 指派宏到的快捷方式键为CTRL SHIFT H 5 执行 工具 宏 录制新宏 6 宏名框中输入 隐藏行 7 将光标定位在快捷键框中 按Shift H键 8 单击 确定 按钮 9 选择单元格范围A3 H6 执行 格式 行 隐藏 10 单击 停止录制 按钮 操作练习 练习十八 1 在单元格范围A4 A83建立和套用自定义格式 使其在每个 书籍号码 之前显示ISBN并在最后一个数字前增加一个虚线 1 选择单元格范围A4 A83 2 执行 格式 单元格 建议讲解时间 10分钟建议练习时间 16分钟 3 选择 数字 项 点选 自定义 4 在类型中自行输入 ISBN 00000 0 Tip 输入的双引号一定要在英文输入法下输入 5 单击 确定 按钮 2 在储存格范围A3 D83中 根据 书籍号码 汇总数量以及根据每个 业务人员 汇总数量 仅显示汇总和总计 6 选择单元格范围A3 D83 执行 数据 分类汇总 7 根据题目要求 分类字段选择 书籍号码 汇总方式 求和 汇总项 数量 8 单击 确定 按钮 9 再次执行 数据 分类汇总 以书籍号码为分类字段的汇总结果 10 根据题目要求 分类字段选择 业务人员 汇总方式 求和 汇总项 数量 12 单击 确定 按钮 11 取消 替换当前分类汇总 的勾选 完成两次分类汇总后的画面 根据题目要求 只显示汇总和总计 单击第三层按钮 答案样张 操作练习 练习十九 1 请将我的文档文件夹中的书籍系统 mdb数据库之 书籍基本数据 表格导入工作表中 从工作表的储存格A1开始导入 注意 请接受所有的预设设定 1 执行 数据 导入外部数据 导入数据 建议讲解时间 10分钟建议练习时间 16分钟 2 选择我的文档文件夹下 19书籍系统 mdb 数据库文件 3 单击 打开 4 选择 书籍基本资料 表 5 单击 确定 按钮 6 数据放置位置选择A1 单击 确定 按钮 完成数据的导入 2 保护工作表和锁定单元格的内容 使用book为窗体密码来选定锁定的单元格 选定未锁定单元格以及设置单元格格式 7 执行 工具 保护 保护工作表 8 输入密码 book 9 根据题目 取消对该三项的勾选 10 单击 确定 按钮 11 重新输入确认密码 单击 确定 操作练习 练习二十 1 在单元格B55建立和套用自定义格式 单元格保留原始的数字格式并在任何输入的数字或公式之前插入总金额 这四个字符 1 选择单元格B55 2 执行 格式 单元格 建议讲解时间 10分钟建议练习时间 16分钟 4 在原格式前输入 总金额 保持原格式不变 3 选择 数字 项 点选 自定义 5 单击 确定 按钮 6 在单元格B55中输入公式 SUM B4 B54 验证设置正确性 2 格式化单元格范围B4 B54 让 黄色 单元格的底纹仅在文字 本校仁爱基金 出现于C列的同时才套用在B列 7 选择单元格范围B4 B54 8 执行 格式 条件格式 9 条件1选择 公式 在公式框中输入 C4 本校仁爱基金 10 单击 格式 按钮 11 选择 图案 项 单元格底纹选择 黄色 12 单击 确定 按钮 13 单击 确定 按钮 答案样张 操作练习 练习二十一 1 在费用总表工作簿单元格B3和C3建立公式以整合 北部分公司和南部分公司 工作簿上之 电话费 及 水电费 的对应总值 1 同时开启三个工作簿 2 在费用总表工作簿单元格B3中输入 北部分公司 xls 北部 B 9 南部分公司 xls 南部 B 9 单击回车键 建议讲解时间 10分钟建议练习时间 16分钟 计算结果 3 在费用总表工作簿单元格C3中输入 北部分公司 xls 北部 C 9 南部分公司 xls 南部 C 9 单击回车键 计算结果 2 为 费用总表 工作簿设定重新计算 在储存之前 以手动方式 不是用自动方式 重新计算 4 执行 工具 选项 5 选择 重新计算 项 点选 手动重算 勾选 保存前自动重算 6 单击 确定 按钮 操作练习 练习二十二 1 在单元格范围A3 A35 根据 区域 这个字段的值来建立新的列表 1 执行 数据 列表 创建列表 建议讲解时间 10分钟建议练习时间 16分钟 2 数据位置选择A3 A35 3 勾选 列表有标题