MOSExcel专家级考点.doc

MOSExcel2016专家级考点ＭOSＥｘcel2016Eｘｐeｒｔ第一章 目录MOSExcｅl20１6Ｅxpertﻩ1ＭOSＥｘｃel2016Ｅｘperｔ考点汇总ﻩ２MＯSExcel201６Ｅxｐert考点解析 3单元１ 管理工作簿选项和设置 31.1 管理工作簿 31.2ﻩ管理工作簿审阅ﻩ8单元２ﻩ应用自定义数据格式和设置ﻩ13２.1ﻩ应用自定义数据格式和数据验证ﻩ１32.2ﻩ应用高级条件格式 162．3ﻩ创建与修改自定义工作簿元素ﻩ212.４ﻩ配置工作簿的国际化应用ﻩ28单元３ 应用高级公式和数据分析工具ﻩ3０3.1ﻩ使用函数进行逻辑判断 303.2ﻩ使用函数进行条件统计 343．３ﻩ使用函数查找数据 373.４ 使用函数处理日期和时间 4０３.5 为单元格和表格创建名称 403．6 审核公式和查找错误 4２３.７ 使用数据分析工具ﻩ4３单元4ﻩ分析和展示数据 4８4.1ﻩ创建高级图表ﻩ４８4.2 使用数据透视表分析数据ﻩ51４.３ 使用数据透视图展示数据ﻩ５７MＯSExcel2016Expert考点汇总章节大纲项目任务及考点单元1 管理工作簿选项和设置1.1 管理工作簿1.2 管理工作簿审阅单元2 应用自定义数据格式和设置2.1 应用自定义数据格式和数据验证2.2 应用高级条件格式2.3 创建与修改自定义工作簿元素2.4 配置工作簿的国际化应用单元3 应用高级公式和数据分析工具3.1 使用函数进行逻辑判断3.2 使用函数进行条件统计3.3 使用函数查找数据3.4 使用函数处理日期和时间3.5 为单元格和表格创建名称3.6 审核公式和查找错误3.7 使用数据分析工具单元4 分析和展示数据4.1 创建高级图表4.2 使用数据透视表分析数据4.3 使用数据透视图展示数据MOＳExcel２016Expｅrt考点解析单元1 管理工作簿选项和设置1.1 管理工作簿任务1－1将文档保存为「Ｅｘcel模板」格式，保存位置为默认文件夹。

注释：在实际工作中有些表格我们会在未来反复使用，因此我们可以直接将常用的模板进行保存,后续使用时直接调用即可解法:a) 打开案例素材01-01b) 单击“文件”后台视图；c) 选择“另存为”,单击“浏览”；d) 将文件类型更改为“Exｃeｌ模板(*.xltx）”,(注意拓展名为“xｌtx)”;e) 保存位置已经是默认的文件夹，直接点击“保存”即可扩展：未来我们需要调用此模板时，选择“文件”选项卡,单击“新建”，选择“个人”，即可调用我们之前保存的模板任务1－2在功能区显示开发工具选项卡注释:在Ｅｘcel中有些高级的功能比如ＶＢA,宏，控件等,需要在“开发工具”选项卡中来完成,但是在默认情况下“卡发工具”选项卡是不显示的,所以需要手动选择将其显示出来解法：a) 打开案例素材01-02;b) 单击“文件”后台视图；c) 选择“选项”命令,弹出Ｅxcel选项对话框；d) 在“自定义功能区”栏目中，右侧“自定义功能区”菜单下，勾选上“开发工具”选项卡,单击“确定”按钮即可e) 在功能区中，可以看到已经显示出“开发工具”选项卡任务1-3将文档「前15%.xlsm」中的宏复制到文档「01－03.xｌｓｍ」中。

注释:考察宏在不同文档之间的复制传递解法：a) 同时打开案例素材“０1－０３”和“前１5%”两个文档;b) 首先在“前15%”的文档中，切换至“开发工具”选项卡,单击“代码”组的“VisualBａsic”选项,在“工程-VＢＡPｒojeｃt”对话框中，找到“VBAＰｒoｊｅct(前1５%.xｌsm)”选项中的“模块-模块１”选项，将其拖曳至“ＶＢAPｒoject（０1-03.ｘｌsm）”中;c) 点开“VＢAProｊeｃｔ（01－03.xｌsm)”选项前的“+”号，将其展开,查看是否已经复制成功;d) 直接关闭文件，在案例素材01－0３”中,切换至“开发工具选项卡”,打开“宏”按钮，查看杯复制过来的宏图1﻾１图1２任务1-4仅启用数字签名的宏注释:启用或者禁用宏设置解法：a) 打开案例素材０１-０4;b) 切换至“开发工具”选项卡，在“代码”组,选择“宏安全性”选项，在弹出的“信任中心”中，单击“宏设置”,勾选“禁用无数字签署的宏”,单击确定,完成题设任务要求图１﻾3任务１－５在「按月份统计」工作表中,使用「图书销售．xlｓｘ」中的数据，计算各个类别图书1-6月的合计销量注释:考察条件求和函数,即SUMIF函数的使用。

解法：a) 打开案例素材０1-05，同时打开我们需要的图书销售素材01-04；b) 选中“Ｂ3”单元格，首先输入“SUMIF”函数,先求出1月份社会科学类图书的总计销量；c) 设置函数参数,在第一个参数“Ranｇｅ”中选择我们要进行计算的单元格区域,在“视图”选项卡,“窗口”选项组，选择“切换窗口”下拉菜单，切换到“01-04”文件;选定“Ｃ列”为第一个函数的参数；d) 第二个参数是条件区域，选择B3单元格,并将其设成混合引用,行标相对引用,列标绝对引用,按Ｆ4（按3次）,完成设置；e) 第三个参数是实际求和的区域,切换到“0１-04”文件，选定“D列”，将其设置为行标绝对引用,列标相对引用f) 单击确定按钮，然后向下向右拖曳完成题设要求扩展：a) “绝对引用”指的是在拖曳单元格的过程中,单元格内容并不会着单元格位置的变化而变化b) “相对引用”指的是在拖曳单元格过程中,单元格的内容会随着元格位置的变化而变化c) “混合引用”指的是在拖曳单元格过程中，部分单元格的内容会随单元格位置的变化而变化d) 实现方法:按F4进行设置任务１-6在「图书销售」工作表中使用结构化引用，计算1-6月各种图书销量之和，并添加汇总行,计算每月所有图书销量的总和。

注释：考察求和函数,即SＵM函数的使用,和结构化引用解法:a) 打开案例素材“01-06”;b) 光标定位到「图书销售（2）」工作表中的“J3”单元格，在开始选项卡，编辑选项组中，单击　“自动求和”，在SUＭ函数中，我们能够看到参数是“表１[＠[1月]:［6月]］”,这就称之为“结构化引用”｛因为这张工作表已经被定义名称(在公式选项卡,名称管理器中我们能够看到工作表已经被定义为“表1”）},因此我们直接按“Enｔer”即可完成对求和,不用向下拖曳;c) 选择“设计”选项卡,在“表格样式选项”选项组勾选“汇总行”选项，表单的最后一行即为汇总行在1-６月汇总行的下拉菜单中,选择“求和”选项,即可完成任务要求扩展:“结构化引用”指的是在对表区域进行命名之后,表区域即被“表格化”,在随后的引用中,引用的即是表格而非区域，称之为结构化引用1.2 管理工作簿审阅任务1-7在「按月份统计」工作表上，为单元格区域B3:G9设置密码保护命名该区域为「销售数量」使用「ｍicroｍacｒｏ」作为区域保护密码注释:考察对单元格区域进行保护解法:a) 打开案例素材“０1-07”，选中需要被保护的区域B３：G9；b) 选择“审阅”选项卡,在“更改”组选择”允许用户编辑区域“选项，选择”新建“，将标题改为”销售数量“，引用单元格选择B３：G9，区域密码设置为「mｉcromａcro」,单击确定,再次输入密码确定，再次单击确定,完成题设任务。

图1﻾４图15任务1－8保护工作表「投资项目列表」,密码为「ｍicrｏmａcｒｏ」保护工作表「利润预测」，在保护后,不可以选定工作表中任意单元格，但可以通过控件修改工作表中的数据，不使用密码注释：任务分为两部分，第一部分是对工作表进行保护，第二部分是对在保护工作表的状态下设置可以更改的控件解法:a) 打开案例素材“01－０8”，定位到「投资项目列表」工作表；b) 在“审阅”选项卡,选择“保护工作表”选项,输入密码「micrｏmacro」,确定,再次输入密码确定图１﻾6图1﻾7c) 切换至「利润预测」工作表,选中Ｂ2单元格,在”开始”选项卡，”对齐方式”选项组,选择“保护”选项,取消“锁定”按钮;d) 然后在“审阅”选项卡，选择“保护工作表”选项,输入密码「mｉcｒomacｒｏ」，确定，再次输入密码确定,完成任务要求注意,本次保护工作表时，“选定锁定单元格”和“选定未锁定的单元格”的复选框是未被选中的状态图18图１9扩展:在默认状态下每个单元格都属于“锁定”状态,在锁定状态下，保护工作表之后，单元格是无法做任何更改的因此想要在保护工作表之后还能修改单元格，就需要在保护单元格之前取消对单元格的锁定。

任务１-9修改工作簿计算选项，以便包含公式的单元格计算结果发生改变的时候，只有通过手动重算或者保存工作簿才显示该变化注释：考察取消自动重算,改为手动重算解法:a) 打开案例素材“01－0９”;b) 打开“文件”后台视图，选择“选项”，在弹出的Ｅxcｅｌ选项对话框中，选择“公式”选项,将“计算选项”中的“自动重算”改为“手动重算”，并勾选“保护工作簿前重新计算”,单击确定，完成任务要求图１1０任务１-10ﻩ保护工作簿，以便用户无法添加、删除或修改工作表,除非输入密码「MｉｃｒｏMacro」注释:考察对工作簿的保护解法:a) 打开案例素材“０1-10”；b) 选择“审阅”选项卡，单击“更改”选项组的“保护工作簿”选项图１１1图１12任务1-１1ﻩ将工作簿的自动保存间隔时间设置为１５分钟注释：对Eｘcel选项的更改,设置自动保存时间间隔解法:a) 打开案例素材“01－11”;b) 打开“文件”后台视图，选择“选项”，在弹出的Excel选项对话框中,选择“保存”选项,将“保存自动恢复信息时间间隔”设置为15分钟,单击确定,即可完成任务要求单元2 应用自定义数据格式和设置2.1 应用自定义数据格式和数据验证任务２-1在「1季度销售数据」工作表上,格式化列H，使得其中的数值显示为2位小数。

将格式应用到现有的行和新行注释：考察设置单元格格式解法：a) 打开案例素材“０2-０1”;b) 选中H列,在“开始”选项卡，“数字”选项组中，单击右下角的斜向箭头,弹出“设置单元格格式”对话窗口,将数字格式设置为“数值”，且小数数位为２位，单击确定按钮，完成题设要求图２﻾１任务２-２在「10月销售记录」工作表上的单元格Ｃ3:C3３中填入「十月」不要更改单元格的格式注释：考察快速填充功能解法:a) 打开案例素材“0２-０2”;b) 将光标定位到C２单元格,将鼠标放到单元格右下角的填充柄上，当光标变为十字时,双击鼠标,完成快速填充c) 单击C列最后一行单元格右下角的“自动填充选项”下拉菜单，选择“复制单元格”,即可完成题设要求任务2－3在「1季度销售数据」工作表上,为列C设置数据验证,使得ﻩ只能输入「」、「平板电脑」、「笔记本电脑」和「台式电脑」将规则应用到现有的行和新行注释：考察数据验证解法：a) 打开案例素材“0２－０３”；b) 首先选中C列，选择“数据”选项卡,单击“数据工具”选项组“数据验证”选项，在弹出的“数据验证”对话框中,将验证条件设定为“序列”；c) 在“来源”中，分别输入“，平板电脑，笔记本电脑,台式电脑”，单击确定按钮，即可完成题设的要求。