MOS_Excel2003_培训教材_Expert_CPC_20071012.ppt

microsoft office specialist,微软国际认证办公专家,m o s excel2003 expert 学习教程,练习一,1. 将单元格范围 a4:g61转换成为一个列表项目,回首页,下一页,上一页,1.选择单元格范围a4：g61，执行“数据/列表/创建列表”,第 200页,建议讲解时间：10分钟 建议练习时间：16分钟,2.数据范围已自动设定，勾选上“列表有标题”,3.单击“确定”按钮,回首页,下一页,上一页,已完成的列表,回首页,下一页,上一页,2.设定让婚姻状况字段 (列 g) 只可接受 已婚 或 未婚为有效的数据项。,4.选择g列，执行“数据/有效性”,回首页,下一页,上一页,5.允许栏选择“序列”，来源栏中输入“已婚，未婚”,6.单击“确定”按钮,tip:在中文输入法下输入已婚后，一定切换输入法到英文 状态，输入分隔符“,”，然后再输入未婚,回首页,下一页,上一页,序列输入方式，可以有效降低错误输入,回首页,下一页,上一页,故意输入“单身”文字，验证有限性设置是否正确,回首页,下一页,上一页,练习二,在单元格范围 b3:b47 建立和套用数据格式， 每个员工 “员工编号” 数据的前面加上u字母。,1.选择单元格范围b3：b47，执行“格式/单元格”,回首页,下一页,上一页,第 205页,建议讲解时间：10分钟 建议练习时间：16分钟,2.点选“自定义”,3.输入“u”0,4.单击“确定”按钮,回首页,下一页,上一页,2. 为工作簿创建自定义属性，属性名称为机密，加上 是或否数据类型，并将值设为是。此外，并将 人事科输入为该工作簿的源。,5.执行“文件/属性”,回首页,下一页,上一页,6.在名称框中输入“机密”，类型选择“是或否”,7.取值“是”,8.单击“添加”按钮,回首页,下一页,上一页,9.名称选择“源”，类型选择“文本”,10.取值输入“人事科”,11.单击“添加”按钮,12.单击“确定”按钮,回首页,下一页,上一页,练习三,请将箭头图案和 “最多！” 这三个字符组合在一起，然后将组合后的对象置于图表上方以指出最多的地区。,1.选中文字框，按住shift键，点选箭头图案,2.在选中的图案上 单击鼠标右键， 在弹出的选项框中， 选择“组合/组合”,回首页,下一页,上一页,第 211页,建议讲解时间：10分钟 建议练习时间：16分钟,3.在图案上再次单击鼠标右键， 选择“叠放次序/置于顶层”,回首页,下一页,上一页,4.拖动图案至要求位置， 指示饼图中最大区块,回首页,下一页,上一页,2.请将单元格范围 a4:a7的类别名称新增到图表的图例中，然后从图表将 “北部” 数据块分离出来。,5.在图表空白位置（图表区), 单击鼠标右键，选择“源数据”,回首页,下一页,上一页,6.利用鼠标重新选择数据区域,7.单击“确定”按钮,回首页,下一页,上一页,8.在饼图中北部区块单击鼠标左键， 然后再次单击鼠标左键，选中北部区块,回首页,下一页,上一页,9.按住鼠标左键，拖拽北部区块，将其分离,tip:设定完成后，在选择工作中任意单元格， 箭头即可显示在图表上方,回首页,下一页,上一页,练习四,1.请修改“得分图表” 工作表中数值坐标轴的格 式，使其只显示到小数点后一位数。,1.在数值轴上单击鼠标右键，选择“坐标轴格式”,回首页,下一页,上一页,第 216页,建议讲解时间：10分钟 建议练习时间：16分钟,2.选择“数字”项,3.分类选择“数值”,4.小数位数选择“1”,5.单击“确定”按钮,回首页,下一页,上一页,2.修改分类坐标轴的刻度(大小)， 让类别以相反的顺序显示。,6.在分类轴上单击鼠标右键，选择“坐标轴格式”,回首页,下一页,上一页,7.选择“刻度”项,8.勾选“分类次序反转”,9.单击“确定”按钮,回首页,下一页,上一页,答案样张,回首页,下一页,上一页,练习五,1.追踪所有提供数据给单元格 e8 之公式的单元格， 然后编辑该公式来改正错误。,1.选中单元格e8，执行“工具/ 公式审核/追踪引用单元格”,回首页,下一页,上一页,第 219页,建议讲解时间：10分钟 建议练习时间：16分钟,2. 错误的来源e4，文字无法做运算，应改成d4,回首页,下一页,上一页,3.改成d8*$d$4,回首页,下一页,上一页,2.请根据已预设好的单元格有效性规则， 把工作簿 中的任何无效数据圈选出来。,3.点一下b7:d25的任一单元格， 执行“数据/有效性”来查看题目的有效性范围,回首页,下一页,上一页,4.题目数据有效性设置为“整数，范围0至10之间”,5.无须作任何设置， 单击“确定”按钮,回首页,下一页,上一页,6.执行“工具/公式审核/显示公式审核工具栏”,回首页,下一页,上一页,7.单击“圈释无效数据”按钮,红色椭圆显示的数据即为不符合有效性设置的数据,回首页,下一页,上一页,练习六,请将放大及缩小两个按钮新增到常用 工具栏上的恢复按钮右边并置于新的 群组中。,1.执行“工具/自定义”,回首页,下一页,上一页,第 223页,建议讲解时间：10分钟 建议练习时间：16分钟,2.选择“命令”项，“类别”选择“视图”,3.拖拽“放大”，“缩小”两个按钮至“恢复”按钮右边,4.单击“重排命令”按钮,回首页,下一页,上一页,5.选择“工具栏/常用”,6.点选“放大”按钮， 执行“更改所选内容/ 开始一组”,回首页,下一页,上一页,7.单击“关闭”按钮，回到下一页面，再次单击“关闭”按钮,回首页,下一页,上一页,2. 请将预设的工作表数目设定为 4 并将用户名称设定为 财务科,8.执行“工具/选项”,回首页,下一页,上一页,9.新工作簿内的工作表数设为“4”，用户名输入“财务科”,10.单击“确定”按钮,回首页,下一页,上一页,练习七,1.将 “预算与实际” 工作表保护起来，仅允许在单元格 c5:c8 中输入数据项。 (注意：请保留原有的预设值。)。,1.选择单元格c5:c8，执行“格式/单元格”,回首页,下一页,上一页,第 227页,建议讲解时间：10分钟 建议练习时间：16分钟,2.点选“保护”项，取消对“锁定”的勾选,3.单击“确定”按钮,回首页,下一页,上一页,4.执行“工具/保护/保护工作表”,回首页,下一页,上一页,5.勾选“选定未锁定的单元格”,6.单击“确定”按钮,回首页,下一页,上一页,2. 修改预设设定，让工作簿能每隔 8 分钟自动地储存到 c:下,7.执行“工具/选项”,tip:此时必须选择c5:c8中的任意一个单元格,回首页,下一页,上一页,8.分别输入“8”，“c：/”,9.单击“确定”按钮,回首页,下一页,上一页,练习八,1.在 “收入分析” 的 xy 散点图中新增 “乘幂” 趋势线并于该趋势线上显示方程式和 r 平方值。,1.点选“收入分析”工作表,2.在图表中任一资料点上，单击鼠标右键,3.选择“添加趋势线”,回首页,下一页,上一页,第 230页,建议讲解时间：10分钟 建议练习时间：16分钟,4.在“类型”项中，选择 趋势线类型“乘幂”,5.在“选项”项中，勾选 “显示公式”，“显示r 平方值”,6.单击“确定”按钮,回首页,下一页,上一页,添加了趋势线的xy散点图,回首页,下一页,上一页,2. 共享工作簿，允许一次可由多位使用者进行变更，并让该活页簿每隔 10 分钟更新一次变更的信息。,7.执行“工具/共享工作簿”,回首页,下一页,上一页,8.勾选“允许多用户同时编辑， 同时允许工作簿合并”,9.在“更新”项中，将自动 更新间隔设为10分钟,10.单击“确定”按钮,回首页,下一页,上一页,11.单击“确定”按钮,回首页,下一页,上一页,练习九,1.编辑名称为 “页眉” 的宏，让 “中文系” 这三个字插入页眉的左区段。,1.执行“工具/宏/宏”,回首页,下一页,上一页,第 235页,建议讲解时间：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源,回首页,下一页,上一页,第 240页,建议讲解时间：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”,回首页,下一页,上一页,第 245页,建议讲解时间：5分钟 建议练习时间：8分钟,按回车键便会出现计算结果,2.将光标停留在c5单元格的右下角实心小方块上， 再按住鼠标左键拖曳到单元格c9后放开，即可完成公式复制,回首页,下一页,上一页,答案样张,回首页,下一页,上一页,练习十 二,1.组合显示 b、c、d和 e列,1.选中b、c、d和 e列,2.执行“数据/组及分级显示/组合”,回首页,下一页,上一页,第 248页,建议讲解时间：10分钟 建议练习时间：16分钟,通过单击“加号”及“减号”， 可实现资料的合并及展开,回首页,下一页,上一页,2.将 “业绩” 工作表中的图片还原回原来的大小，然后使用预设设定来压缩该图片,3.执行“视图/工具栏/图片”,回首页,下一页,上一页,4.点选图片，单击“重设图片”按钮,回首页,下一页,上一页,5.点选图片，单击“压缩图片”按钮,回首页,下一页,上一页,6.单击“确定”按钮,回首页,下一页,上一页,练习十三,1.将位于 我的文档文件夹中的 “公共科目期末考-b” 工作簿的变更合并到 “共同科目期末考-a” 工作簿。,1.执行“工具/比较 和合并工作簿”,回首页,下一页,上一页,第 251页,建议讲解时间：10分钟 建议练习时间：16分钟,2.选择我的文档文件夹中的 “公共科目期末考-b” 工作簿,3.单击“确定”按钮,回首页,下一页,上一页,2.显示工作簿中所有的修订、拒绝单元格 b3、c3、和 d3 中的修订，然后接受其它所有的修订,4.执行“工具/突出显示修订”,回首页,下一页,上一页,6.单击“确定”按钮,5.时间项选择“全部”,回首页,下一页,上一页,7.执行“工具/修订/接受或拒绝修订”,回首页,下一页,上一页,8.单击“确定”按钮,回首页,下一页,上一页,9.单击“拒绝”按钮，拒绝b3单元格的修订， 以此类推，拒绝c3,d3单元格的修订， 其它单元格单击“全部接受”按钮,回首页,下一页,上一页,练习十四,将单元格 f2 和 f3 当成结果单元格使用， 为工作簿假设情况建立方案摘要。,1.执行“工具/方案”,回首页,下一页,上一页,第 255页,建议讲解时间：10分钟 建议练习时间：16分钟,2.单击“摘要”按钮,3.选择结果单元格f2:f3,4.单击“确定”按钮,回首页,下一页,上一页,完成结果,回首页,下一页,上一页,2. 在 “方案摘要” 工作表上，用基金这个名称将对应的 xml 数据导出为 “xml 文件”，放到 “我的文档” 文件夹中。,5. 执行“数据/xml/xml源”,回首页,下一页,上一页,6. 执行“数据/xml/导出”,回首页,下一页,上一页,7.输入文件名“基金”，单击“导出”按钮,回首页,下一页,上一页,练习十五,1.加载，然后使用 “分析工具库”(分析toolpak) 来执行 “回归” 分析以决定 “冰淇淋销售量” (从属变数，y) 与 “气温” (独立变量，x) 之间的相互关系。(注意：请接受所有的默认设定。)。,1.执行“工具/加载宏”,回首页,下一页,上一页,第 259页,建议讲解时间：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.执行“工具/公式审核/显示监视窗口”,回首页,下一页,上一页,第 265页,建议讲解时间：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.执行“数据/筛选/高级筛选”,回首页,下一页,上一页,第 271页,建议讲解时间：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.执行“格式/单元格”,回首页,下一页,上一页,第 277页,建议讲解时间：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.执行“数据/导入外部数据/导入数据”,回首页,下一页,上一页,第 282页,建议讲解时间：10分钟 建议练习时间：16分钟,2.选择我的文档文件夹下“19书籍系统.mdb”数据库文件,3.单击“打开”,回首页,下一页,上一页,4.选择“书籍基本资料”表,5.单击“确定”按钮,回首页,下一页,上一页,6.数据放置位置选择a1,单击“确定”按钮,回首页,下一页,上一页,完成数据的导入,回首页,下一页,上一页,2.保护工作表和锁定单元格的内容。 使用book为窗体密码来选定锁定的单元格、选定未锁定单元格以及设置单元格格式。,7.执行“工具/保护/保护工作表”,回首页,下一页,上一页,8.输入密码“book”,9.根据题目，取消对该三项的勾选,10.单击“确定”按钮,回首页,下一页,上一页,11.重新输入确认密码，单击“确定”,回首页,下一页,上一页,练习二十,1.在单元格 b55 建立和套用自定义格式，单元格保留原始的数字格式并在任何输入的数字或公式之前插入总金额：这四个字符。,1.选择单元格b55,2.执行“格式/单元格”,回首页,下一页,上一页,第 286页,建议讲解时间：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”， 单击回车键,回首页,下一页,上一页,第 291页,建议讲解时间：10分钟 建议练习时间：16分钟,计算结果,3.在费用总表工作簿单元格c3中输入 “=北部分公司.xls北部!$c$9+南部分公司.xls南部!$c$9”， 单击回车键,回首页,下一页,上一页,计算结果,回首页,下一页,上一页,2.为 “费用总表” 工作簿设定重新计算，在储存之前，以手动方式，不是用自动方式，重新计算。,4.执行“工具/选项”,回首页,下一页,上一页,5.选择“重新计算”项，点选“手动重算”， 勾选“保存前自动重算”,6.单击“确定”按钮,回首页,下一页,上一页,练习二十二,1.在单元格范围 a3:a35，根据 “区域” 这个 字段的值来建立新的列表。,1.执行“数据/列表/创建列表”,回首页,下一页,上一页,第 294页,建议讲解时间：10分钟 建议练习时间：16分钟