EXCEL专家级别MOS专业认证练习图解PPT参考幻灯片

MicrosoftOfficeSpecialist,Excel2003Expert练习题解,微软办公软件国际认证,1,计,2,练习一,1.将单元格范围A4:G61转换成为一个列表项目,1.选择单元格范围A4：G61，执行“数据/列表/创建列表”,建议讲解时间：10分钟建议练习时间：16分钟,2,计算机教育培训中心,2020/4/30,2.数据范围已自动设定，勾选上“列表有标题”,3.单击“确定”按钮,3,计算机教育培训中心,2020/4/30,已完成的列表,4,计算机教育培训中心,2020/4/30,2.设定让婚姻状况字段(列G)只可接受已婚或未婚为有效的数据项。,4.选择G列，执行“数据/有效性”,5,计算机教育培训中心,2020/4/30,5.允许栏选择“序列”，来源栏中输入“已婚，未婚”,6.单击“确定”按钮,Tip:在中文输入法下输入已婚后，一定切换输入法到英文状态，输入分隔符“,”，然后再输入未婚,6,计算机教育培训中心,2020/4/30,序列输入方式，可以有效降低错误输入,7,计算机教育培训中心,2020/4/30,故意输入“单身”文字，验证有限性设置是否正确,操作练习,8,计算机教育培训中心,2020/4/30,练习二,在单元格范围B3:B47建立和套用数据格式，每个员工“员工编号”数据的前面加上U字母。,1.选择单元格范围B3：B47，执行“格式/单元格”,建议讲解时间：10分钟建议练习时间：16分钟,9,计算机教育培训中心,2020/4/30,2.点选“自定义”,3.输入“U”0,4.单击“确定”按钮,10,计算机教育培训中心,2020/4/30,2.为工作簿创建自定义属性，属性名称为机密，加上是或否数据类型，并将值设为是。此外，并将人事科输入为该工作簿的源。,5.执行“文件/属性”,11,计算机教育培训中心,2020/4/30,6.在名称框中输入“机密”，类型选择“是或否”,7.取值“是”,8.单击“添加”按钮,12,计算机教育培训中心,2020/4/30,9.名称选择“源”，类型选择“文本”,10.取值输入“人事科”,11.单击“添加”按钮,12.单击“确定”按钮,操作练习,13,计算机教育培训中心,2020/4/30,练习三,请将箭头图案和“最多！”这三个字符组合在一起，然后将组合后的对象置于图表上方以指出最多的地区。,1.选中文字框，按住Shift键，点选箭头图案,2.在选中的图案上单击鼠标右键，在弹出的选项框中，选择“组合/组合”,建议讲解时间：10分钟建议练习时间：16分钟,14,计算机教育培训中心,2020/4/30,3.在图案上再次单击鼠标右键，选择“叠放次序/置于顶层”,15,计算机教育培训中心,2020/4/30,4.拖动图案至要求位置，指示饼图中最大区块,16,计算机教育培训中心,2020/4/30,2.请将单元格范围A4:A7的类别名称新增到图表的图例，然后从图表将“北部”数据块分离出来。,5.在图表空白位置（图表区),单击鼠标右键，选择“源数据”,17,计算机教育培训中心,2020/4/30,6.利用鼠标重新选择数据区域,7.单击“确定”按钮,18,计算机教育培训中心,2020/4/30,8.在饼图中北部区块单击鼠标左键，然后再次单击鼠标左键，选中北部区块,19,计算机教育培训中心,2020/4/30,9.按住鼠标左键，拖拽北部区块，将其分离,Tip:设定完成后，在选择工作中任意单元格，箭头即可显示在图表上方,操作练习,20,计算机教育培训中心,2020/4/30,练习四,1.请修改“得分图表”工作表中数值坐标轴的格式，使其只显示到小数点后一位数。,1.在数值轴上单击鼠标右键，选择“坐标轴格式”,建议讲解时间：10分钟建议练习时间：16分钟,21,计算机教育培训中心,2020/4/30,2.选择“数字”项,3.分类选择“数值”,4.小数位数选择“1”,5.单击“确定”按钮,22,计算机教育培训中心,2020/4/30,2.修改分类坐标轴的刻度(大小)，让类别以相反的顺序显示。,6.在分类轴上单击鼠标右键，选择“坐标轴格式”,23,计算机教育培训中心,2020/4/30,7.选择“刻度”项,8.勾选“分类次序反转”,9.单击“确定”按钮,24,计算机教育培训中心,2020/4/30,答案样张,操作练习,25,计算机教育培训中心,2020/4/30,练习五,1.追踪所有提供数据给单元格E8之公式的单元格，然后编辑该公式来改正错误。,1.选中单元格E8，执行“工具/公式审核/追踪引用单元格”,建议讲解时间：10分钟建议练习时间：16分钟,26,计算机教育培训中心,2020/4/30,2.错误的来源E4，文字无法做运算，应改成D4,27,计算机教育培训中心,2020/4/30,3.改成D8*$D$4,28,计算机教育培训中心,2020/4/30,2.请根据已预设好的单元格有效性规则，把工作簿中的任何无效数据圈选出来。,3.点一下B7:D25的任一单元格，执行“数据/有效性”来查看题目的有效性范围,29,计算机教育培训中心,2020/4/30,4.题目数据有效性设置为“整数，范围0至10之间”,5.无须作任何设置，单击“确定”按钮,30,计算机教育培训中心,2020/4/30,6.执行“工具/公式审核/显示公式审核工具栏”,31,计算机教育培训中心,2020/4/30,7.单击“圈释无效数据”按钮,红色椭圆显示的数据即为不符合有效性设置的数据,操作练习,32,计算机教育培训中心,2020/4/30,练习六,请将放大及缩小两个按钮新增到常用工具栏上的恢复按钮右边并置于新的群组中。,1.执行“工具/自定义”,建议讲解时间：10分钟建议练习时间：16分钟,33,计算机教育培训中心,2020/4/30,2.选择“命令”项，“类别”选择“视图”,3.拖拽“放大”，“缩小”两个按钮至“恢复”按钮右边,4.单击“重排命令”按钮,34,计算机教育培训中心,2020/4/30,5.选择“工具栏/常用”,6.点选“放大”按钮，执行“更改所选内容/开始一组”,35,计算机教育培训中心,2020/4/30,7.单击“关闭”按钮，回到下一页面，再次单击“关闭”按钮,36,计算机教育培训中心,2020/4/30,2.请将预设的工作表数目设定为4,并将用户名称设定为财务科,8.执行“工具/选项”,37,计算机教育培训中心,2020/4/30,9.新工作簿内的工作表数设为“4”，用户名输入“财务科”,10.单击“确定”按钮,操作练习,38,计算机教育培训中心,2020/4/30,练习七,1.将“预算与实际”工作表保护起来，仅允许在单元格C5:C8中输入数据项。(注意：请保留原有的预设值。)。,1.选择单元格C5:C8，执行“格式/单元格”,建议讲解时间：10分钟建议练习时间：16分钟,39,计算机教育培训中心,2020/4/30,2.点选“保护”项，取消对“锁定”的勾选,3.单击“确定”按钮,40,计算机教育培训中心,2020/4/30,4.执行“工具/保护/保护工作表”,41,计算机教育培训中心,2020/4/30,5.勾选“选定未锁定的单元格”,6.单击“确定”按钮,42,计算机教育培训中心,2020/4/30,2.修改预设设定，让工作簿能每隔8分钟自动地储存到C:下,7.执行“工具/选项”,Tip:此时必须选择C5:C8中的任意一个单元格,43,计算机教育培训中心,2020/4/30,8.分别输入“8”，“C：/”,9.单击“确定”按钮,操作练习,44,计算机教育培训中心,2020/4/30,练习八,1.在“收入分析”的XY散点图中新增“乘幂”趋势线并于该趋势线上显示方程式和R平方值。,1.点选“收入分析”工作表,2.在图表中任一资料点上，单击鼠标右键,3.选择“添加趋势线”,建议讲解时间：10分钟建议练习时间：16分钟,45,计算机教育培训中心,2020/4/30,4.在“类型”项中，选择趋势线类型“乘幂”,5.在“选项”项中，勾选“显示公式”，“显示R平方值”,6.单击“确定”按钮,46,计算机教育培训中心,2020/4/30,添加了趋势线的XY散点图,47,计算机教育培训中心,2020/4/30,2.共享工作簿，允许一次可由多位使用者进行变更，并让该活页簿每隔10分钟更新一次变更的信息。,7.执行“工具/共享工作簿”,48,计算机教育培训中心,2020/4/30,8.勾选“允许多用户同时编辑，同时允许工作簿合并”,9.在“更新”项中，将自动更新间隔设为10分钟,10.单击“确定”按钮,49,计算机教育培训中心,2020/4/30,11.单击“确定”按钮,操作练习,50,计算机教育培训中心,2020/4/30,练习九,1.编辑名称为“页眉”的宏，让“中文系”这三个字插入页眉的左区段。,1.执行“工具/宏/宏”,建议讲解时间：10分钟建议练习时间：16分钟,51,计算机教育培训中心,2020/4/30,2.选择“页眉”宏，单击“编辑”,52,计算机教育培训中心,2020/4/30,3.将引号中的内容改为“中文系”,4.单击“运行子过程/用户窗体”按钮，单击“保存”按钮,5.单击“关闭”，回到Excel视图,53,计算机教育培训中心,2020/4/30,6.执行“打印预览”命令，查看宏运行的结果,7.单击“关闭”,54,计算机教育培训中心,2020/4/30,2.将自定义按钮新增到常用工具栏上格式刷的右边。将新按钮命名为系所页眉，并将该按钮指派给“页眉”宏。,8.执行“工具/自定义”,55,计算机教育培训中心,2020/4/30,9.在“命令”项中选择类别“宏”,10.拖拽“自定义按钮”至常用工具栏格式刷的右边,56,计算机教育培训中心,2020/4/30,11.在“自定义”按钮上，单击鼠标右键，在命名框中输入“系所页眉”，单击“指定宏”按钮,57,计算机教育培训中心,2020/4/30,12.选择“页眉”宏,13.单击“确定”按钮,58,计算机教育培训中心,2020/4/30,13.单击“关闭”按钮,操作练习,59,计算机教育培训中心,2020/4/30,练习十,1.请使用位于我的文档文件夹中，名称为“员工资料.xsd”之结构描述的dataroot(就是.xsd档案)，将“姓名”数据与单元格A1对应、”电话”数据与单元格B1对应、”部门”数据与单元格C1对应、而“职称”数据则与单元格D1对应。,1.执行“数据/XML/XML源,建议讲解时间：10分钟建议练习时间：16分钟,60,计算机教育培训中心,2020/4/30,2.单击“XML映射”,3.单击“添加”按钮,61,计算机教育培训中心,2020/4/30,4.在我的文档文件夹下，选择“员工资料.xsd”,5.单击“打开”按钮,62,计算机教育培训中心,2020/4/30,6.选择“dataroot”项，单击“确定”按钮,63,计算机教育培训中心,2020/4/30,7.单击“确定”按钮,64,计算机教育培训中心,2020/4/30,8.根据题目要求，逐一拖拽四个项目至对应单元格,65,计算机教育培训中心,2020/4/30,9.执行“数据/XML/导入”,2.请使用位于我的文档文件夹中的“员工数据.xml”档案，将对应的XML数据导入工作簿。,66,计算机教育培训中心,2020/4/30,10.选择“员工资料.xml”,单击“导入”,67,计算机教育培训中心,2020/4/30,完成资料导入,操作练习,68,计算机教育培训中心,2020/4/30,练习十一,在“业绩总计”工作表的储存格C5中，插入一个公式，以计算“第一农场”、“第二农场”、“第三农场”、和“第四农场”工作表的单元格C5总数。将该公式套用到单元格范围C6:C9。,1.选中C5单元格，输入“=第一农场!C5+第二农场!C5+第三农场!C5+第四农场!C5”,建议讲解时间：5分钟建议练习时间：8分钟,69,计算机教育培训中心,2020/4/30,按回车键便会出现计算结果,2.将光标停留在C5单元格的右下角实心小方块上，再按住鼠标左键拖曳到单元格C9后放开，即可完成公式复制,70,计算机教育培训中心,2020/4/30,答案样张,操作练习,71,计算机教育培训中心,2020/4/30,练习十二,1.组合显示B、C、D和E列,1.选中B、C、D和E列,2.执行“数据/组及分级显示/组合”,建议讲解时间：10分钟建议练习时间：16分钟,72,计算机教育培训中心,2020/4/30,通过单击“加号”及“减号”，可实现资料的合并及展开,73,计算机教育培训中心,2020/4/30,2.将“业绩”工作表中的图片还原回原来的大小，然后使用预设设定来压缩该图片,3.执行“视图/工具栏/图片”,74,计算机教育培训中心,2020/4/30,4.点选图片，单击“重设图片”按钮,75,计算机教育培训中心,2020/4/30,5.点选图片，单击“压缩图片”按钮,76,计算机教育培训中心,2020/4/30,6.单击“确定”按钮,操作练习,77,计算机教育培训中心,2020/4/30,练习十三,1.将位于我的文档文件夹中的“公共科目期末考-B”工作簿的变更合并到“共同科目期末考-A”工作簿。,1.执行“工具/比较和合并工作簿”,建议讲解时间：10分钟建议练习时间：16分钟,78,计算机教育培训中心,2020/4/30,2.选择我的文档文件夹中的“公共科目期末考-B”工作簿,3.单击“确定”按钮,79,计算机教育培训中心,2020/4/30,2.显示工作簿中所有的修订、拒绝单元格B3、C3、和D3中的修订，然后接受其它所有的修订,4.执行“工具/突出显示修订”,80,计算机教育培训中心,2020/4/30,6.单击“确定”按钮,5.时间项选择“全部”,81,计算机教育培训中心,2020/4/30,7.执行“工具/修订/接受或拒绝修订”,82,计算机教育培训中心,2020/4/30,8.单击“确定”按钮,83,计算机教育培训中心,2020/4/30,9.单击“拒绝”按钮，拒绝B3单元格的修订，以此类推，拒绝C3,D3单元格的修订，其它单元格单击“全部接受”按钮,操作练习,84,计算机教育培训中心,2020/4/30,练习十四,将单元格F2和F3当成结果单元格使用，为工作簿假设情况建立方案摘要。,1.执行“工具/方案”,建议讲解时间：10分钟建议练习时间：16分钟,85,计算机教育培训中心,2020/4/30,2.单击“摘要”按钮,3.选择结果单元格F2:F3,4.单击“确定”按钮,86,计算机教育培训中心,2020/4/30,完成结果,87,计算机教育培训中心,2020/4/30,2.在“方案摘要”工作表上，用基金这个名称将对应的XML数据导出为“XML文件”，放到“我的文档”文件夹中。,5.执行“数据/XML/XML源”,88,计算机教育培训中心,2020/4/30,6.执行“数据/XML/导出”,89,计算机教育培训中心,2020/4/30,7.输入文件名“基金”，单击“导出”按钮,操作练习,90,计算机教育培训中心,2020/4/30,练习十五,1.加载，然后使用“分析工具库”(分析Toolpak)来执行“回归”分析以决定“冰淇淋销售量”(从属变数，Y)与“气温”(独立变量，X)之间的相互关系。(注意：请接受所有的默认设定。)。,1.执行“工具/加载宏”,建议讲解时间：10分钟建议练习时间：16分钟,91,计算机教育培训中心,2020/4/30,2.勾选“分析工具库”,单击“确定”按钮,Tip：在安装分析工具库的过程中，Excel2003可能会要求放入安装光盘，请将安装光盘准备好。,92,计算机教育培训中心,2020/4/30,3.执行“工具/数据分析”,93,计算机教育培训中心,2020/4/30,4.分析工具选择“回归”,5.单击“确定”按钮,94,计算机教育培训中心,2020/4/30,6.输入Y,X值输入区域,7.单击“确定”按钮,95,计算机教育培训中心,2020/4/30,分析结果,96,计算机教育培训中心,2020/4/30,2.储存档案，规定需要密码ICECREAM才能开启和修改工作簿。(注意：接受所有的默认设定值。)。,8.执行“文件/另存为”,97,计算机教育培训中心,2020/4/30,9.单击“工具”，选择“常规选项”,98,计算机教育培训中心,2020/4/30,10.在打开权限密码和修改权限密码框中分别输入“ICECREAM”,11.单击“确定”按钮,99,计算机教育培训中心,2020/4/30,12.重新输入密码“ICECREAM”,13.单击“确定”按钮,100,计算机教育培训中心,2020/4/30,14.重新输入修改权限密码“ICECREAM”,15.单击“确定”按钮,101,计算机教育培训中心,2020/4/30,16.单击“保存”按钮,操作练习,102,计算机教育培训中心,2020/4/30,练习十六,1.在单元格F28中新增单元格监视窗口。(注意：将窗口保留在开启状态。),1.执行“工具/公式审核/显示监视窗口”,建议讲解时间：10分钟建议练习时间：16分钟,103,计算机教育培训中心,2020/4/30,2.单击“添加监视”,3.选择需要添加监视的单元格F28,4.单击“添加”按钮,104,计算机教育培训中心,2020/4/30,完成监视窗口的添加,105,计算机教育培训中心,2020/4/30,2.在单元格A5中，将名称为“员工”的逗号分隔文本文件汇入。(注意：接受其它所有的预设设定)。,5.执行“数据/导入外部数据/导入数据”,106,计算机教育培训中心,2020/4/30,6.选择“员工”文本文件，单击“打开”按钮,107,计算机教育培训中心,2020/4/30,7.点选“分隔符号”，单击“下一步”,108,计算机教育培训中心,2020/4/30,8.点选分隔符号为逗号，单击“下一步”,109,计算机教育培训中心,2020/4/30,9.单击“完成”,10.数据放置位置选择A5,11.单击“确定”按钮,110,计算机教育培训中心,2020/4/30,答案样张,操作练习,111,计算机教育培训中心,2020/4/30,练习十七,1.使用准则范围A4:H6，筛选储存格范围A8:H53来显示2002年1月1日前入社的所有“向导组”社员以及2003年1月1日后入社的所有“文书组”社员。,1.根据题目要求输入筛选条件,2.执行“数据/筛选/高级筛选”,建议讲解时间：10分钟建议练习时间：16分钟,112,计算机教育培训中心,2020/4/30,3.列表区域A8:H53,条件区域A4:H6,4.单击“确定”按钮,113,计算机教育培训中心,2020/4/30,筛选结果,114,计算机教育培训中心,2020/4/30,2.建立一个名称为”隐藏行”宏，将其储存在目前的工作簿，以隐藏第3到第7行。指派宏到的快捷方式键为CTRL+SHIFT+H。,5.执行“工具/宏/录制新宏”,115,计算机教育培训中心,2020/4/30,6.宏名框中输入“隐藏行”,7.将光标定位在快捷键框中，按Shift+H键,8.单击“确定”按钮,116,计算机教育培训中心,2020/4/30,9.选择单元格范围A3:H6,执行“格式/行/隐藏”,117,计算机教育培训中心,2020/4/30,10.单击“停止录制”按钮,操作练习,118,计算机教育培训中心,2020/4/30,练习十八,1.在单元格范围A4:A83建立和套用自定义格式，使其在每个“书籍号码”之前显示ISBN并在最后一个数字前增加一个虚线。,1.选择单元格范围A4:A83,2.执行“格式/单元格”,建议讲解时间：10分钟建议练习时间：16分钟,119,计算机教育培训中心,2020/4/30,3.选择“数字”项，点选“自定义”,4.在类型中自行输入“ISBN”00000”-”0,Tip:输入的双引号一定要在英文输入法下输入,5.单击“确定”按钮,120,计算机教育培训中心,2020/4/30,2.在储存格范围A3:D83中，根据“书籍号码”汇总数量以及根据每个“业务人员”汇总数量。仅显示汇总和总计。,6.选择单元格范围A3:D83,执行“数据/分类汇总”,121,计算机教育培训中心,2020/4/30,7.根据题目要求，分类字段选择“书籍号码”，汇总方式“求和”，汇总项“数量”,8.单击“确定”按钮,122,计算机教育培训中心,2020/4/30,9.再次执行“数据/分类汇总”,以书籍号码为分类字段的汇总结果,123,计算机教育培训中心,2020/4/30,10.根据题目要求，分类字段选择“业务人员”，汇总方式“求和”，汇总项“数量”,12.单击“确定”按钮,11.取消“替换当前分类汇总”的勾选,124,计算机教育培训中心,2020/4/30,完成两次分类汇总后的画面，根据题目要求，只显示汇总和总计，单击第三层按钮,125,计算机教育培训中心,2020/4/30,答案样张,操作练习,126,计算机教育培训中心,2020/4/30,练习十九,1.请将我的文档文件夹中的书籍系统.mdb数据库之“书籍基本数据”表格导入工作表中，从工作表的储存格A1开始导入。(注意：请接受所有的预设设定)。,1.执行“数据/导入外部数据/导入数据”,建议讲解时间：10分钟建议练习时间：16分钟,127,计算机教育培训中心,2020/4/30,2.选择我的文档文件夹下“19书籍系统.mdb”数据库文件,3.单击“打开”,128,计算机教育培训中心,2020/4/30,4.选择“书籍基本资料”表,5.单击“确定”按钮,129,计算机教育培训中心,2020/4/30,6.数据放置位置选择A1,单击“确定”按钮,130,计算机教育培训中心,2020/4/30,完成数据的导入,131,计算机教育培训中心,2020/4/30,2.保护工作表和锁定单元格的内容。使用book为窗体密码来选定锁定的单元格、选定未锁定单元格以及设置单元格格式。,7.执行“工具/保护/保护工作表”,132,计算机教育培训中心,2020/4/30,8.输入密码“book”,9.根据题目，取消对该三项的勾选,10.单击“确定”按钮,133,计算机教育培训中心,2020/4/30,11.重新输入确认密码，单击“确定”,操作练习,134,计算机教育培训中心,2020/4/30,练习二十,1.在单元格B55建立和套用自定义格式，单元格保留原始的数字格式并在任何输入的数字或公式之前插入总金额：这四个字符。,1.选择单元格B55,2.执行“格式/单元格”,建议讲解时间：10分钟建议练习时间：16分钟,135,计算机教育培训中心,2020/4/30,4.在原格式前输入“总金额：“,保持原格式不变,3.选择“数字”项，点选“自定义”,5.单击“确定”按钮,136,计算机教育培训中心,2020/4/30,6.在单元格B55中输入公式“=SUM(B4:B54)，验证设置正确性,137,计算机教育培训中心,2020/4/30,2.格式化单元格范围B4:B54，让“黄色”单元格的底纹仅在文字“本校仁爱基金”出现于C列的同时才套用在B列。,7.选择单元格范围B4:B54,8.执行“格式/条件格式”,138,计算机教育培训中心,2020/4/30,9.条件1选择“公式”，在公式框中输入=C4=“本校仁爱基金”,10.单击“格式”按钮,139,计算机教育培训中心,2020/4/30,11.选择“图案”项，单元格底纹选择“黄色”,12.单击“确定”按钮,140,计算机教育培训中心,2020/4/30,13.单击“确定”按钮,141,计算机教育培训中心,2020/4/30,答案样张,操作练习,142,计算机教育培训中心,2020/4/30,练习二十一,1.在费用总表工作簿单元格B3和C3建立公式以整合“北部分公司和南部分公司”工作簿上之“电话费”及“水电费”的对应总值。,1.同时开启三个工作簿,2.在费用总表工作簿单元格B3中输入“=北部分公司.xls北部!$B$9+南部分公司.xls南部!$B$9”，单击回车键,建议讲解时间：10分钟建议练习时间：16分钟,143,计算机教育培训中心,2020/4/30,计算结果,3.在费用总表工作簿单元格C3中输入“=北部分公司.xls北部!$C$9+南部分公司.xls南部!$C$9”，单击回车键,144,计算机教育培训中心,2020/4/30,计算结果,145,计算机教育培训中心,2020/4/30,2.为“费用总表”工作簿设定重新计算，在储存之前，以手动方式，不是用自动方式，重新计算。,4.执行“工具/选项”,146,计算机教育培训中心,2020/4/30,5.选择“重新计算”项，点选“手动重算”，勾选“保存前自动重算”,6.单击“确定”按钮,操作练习,147,计算机教育培训中心,2020/4/30,练习二十二,1.在单元格范围A3:A35，根据“区域”这个字段的值来建立新的列表。,1.执行“数据/列表/创建列表”,建议讲解时间：10分钟建议练习时间：16分钟,148,计算机教育培训中心,2020/4/30,2.数据位置选择A3：A35,