MOSExcel专家级考点.docx
- 文档编号:1585280
- 上传时间:2022-10-23
- 格式:DOCX
- 页数:52
- 大小:2.98MB
MOSExcel专家级考点.docx
《MOSExcel专家级考点.docx》由会员分享,可在线阅读,更多相关《MOSExcel专家级考点.docx(52页珍藏版)》请在冰豆网上搜索。
MOSExcel专家级考点
MOSExcel2016Expert
MOSExcel2016Expert考点汇总
章节大纲
项目任务及考点
单元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使用数据透视图展示数据
MOSExcel2016Expert考点解析
单元1管理工作簿选项和设置
1.1管理工作簿
任务1-1将文档保存为「Excel模板」格式,保存位置为默认文件夹。
注释:
在实际工作中有些表格我们会在未来反复使用,因此我们可以直接将常用的模板进行保存,后续使用时直接调用即可。
解法:
a)打开案例素材01-01
b)单击“文件”后台视图;
c)选择“另存为”,单击“浏览”;
d)将文件类型更改为“Excel模板(*.xltx)”,(注意拓展名为“xltx)”;
e)保存位置已经是默认的文件夹,直接点击“保存”即可。
扩展:
未来我们需要调用此模板时,选择“文件”选项卡,单击“新建”,选择“个人”,即可调用我们之前保存的模板。
任务1-2在功能区显示开发工具选项卡。
注释:
在Excel中有些高级的功能比如VBA,宏,控件等,需要在“开发工具”选项卡中来完成,但是在默认情况下“卡发工具”选项卡是不显示的,所以需要手动选择将其显示出来。
解法:
a)打开案例素材01-02;
b)单击“文件”后台视图;
c)选择“选项”命令,弹出Excel选项对话框;
d)在“自定义功能区”栏目中,右侧“自定义功能区”菜单下,勾选上“开发工具”选项卡,单击“确定”按钮即可。
e)在功能区中,可以看到已经显示出“开发工具”选项卡。
任务1-3将文档「前15%.xlsm」中的宏复制到文档「01-03.xlsm」中。
注释:
考察宏在不同文档之间的复制传递
解法:
a)同时打开案例素材“01-03”和“前15%”两个文档;
b)首先在“前15%”的文档中,切换至“开发工具”选项卡,单击“代码”组的“VisualBasic”选项,在“工程-VBAProject”对话框中,找到“VBAProject(前15%.xlsm)”选项中的“模块-模块1”选项,将其拖曳至“VBAProject(01-03.xlsm)”中;
c)点开“VBAProject(01-03.xlsm)”选项前的“+”号,将其展开,查看是否已经复制成功;
d)直接关闭文件,在案例素材01-03”中,切换至“开发工具选项卡”,打开“宏”按钮,查看杯复制过来的宏。
图11
图12
任务1-4仅启用数字签名的宏。
注释:
启用或者禁用宏设置
解法:
a)打开案例素材01-04;
b)切换至“开发工具”选项卡,在“代码”组,选择“宏安全性”选项,在弹出的“信任中心”中,单击“宏设置”,勾选“禁用无数字签署的宏”,单击确定,完成题设任务要求。
图13
任务1-5在「按月份统计」工作表中,使用「图书销售.xlsx」中的数
据,计算各个类别图书1-6月的合计销量。
注释:
考察条件求和函数,即SUMIF函数的使用。
解法:
a)打开案例素材01-05,同时打开我们需要的图书销售素材01-04;
b)选中“B3”单元格,首先输入“SUMIF”函数,先求出1月份社会科学类图书的总计销量;
c)设置函数参数,在第一个参数“Range”中选择我们要进行计算的单元格区域,在“视图”选项卡,“窗口”选项组,选择“切换窗口”下拉菜单,切换到“01-04”文件;选定“C列”为第一个函数的参数;
d)第二个参数是条件区域,选择B3单元格,并将其设成混合引用,行标相对引用,列标绝对引用,按F4(按3次),完成设置;
e)第三个参数是实际求和的区域,切换到“01-04”文件,选定“D列”,将其设置为行标绝对引用,列标相对引用。
f)单击确定按钮,然后向下向右拖曳完成题设要求。
扩展:
a)“绝对引用”指的是在拖曳单元格的过程中,单元格内容并不会着单元格位置的变化而变化。
b)“相对引用”指的是在拖曳单元格过程中,单元格的内容会随着元格位置的变化而变化。
c)“混合引用”指的是在拖曳单元格过程中,部分单元格的内容会随单元格位置的变化而变化。
d)实现方法:
按F4进行设置。
任务1-6在「图书销售」工作表中使用结构化引用,计算1-6月各种图书销量之和,并添加汇总行,计算每月所有图书销量的总和。
注释:
考察求和函数,即SUM函数的使用,和结构化引用。
解法:
a)打开案例素材“01-06”;
b)光标定位到「图书销售
(2)」工作表中的“J3”单元格,在开始选项卡,编辑选项组中,单击“自动求和”,在SUM函数中,我们能够看到参数是“表1[@[1月]:
[6月]]”,这就称之为“结构化引用”。
{因为这张工作表已经被定义名称(在公式选项卡,名称管理器中我们能够看到工作表已经被定义为“表1”)},因此我们直接按“Enter”即可完成对求和,不用向下拖曳;
c)选择“设计”选项卡,在“表格样式选项”选项组勾选“汇总行”选项,表单的最后一行即为汇总行。
在1-6月汇总行的下拉菜单中,选择“求和”选项,即可完成任务要求。
扩展:
“结构化引用”指的是在对表区域进行命名之后,表区域即被“表
格化”,在随后的引用中,引用的即是表格而非区域,称之为结构化
引用。
1.2管理工作簿审阅
任务1-7在「按月份统计」工作表上,为单元格区域B3:
G9设置密码保护。
命名该区域为「销售数量」。
使用「micromacro」作为区域保护密码。
注释:
考察对单元格区域进行保护
解法:
a)打开案例素材“01-07”,选中需要被保护的区域B3:
G9;
b)选择“审阅”选项卡,在“更改”组选择”允许用户编辑区域“选项,选择”新建“,将标题改为”销售数量“,引用单元格选择B3:
G9,区域密码设置为「micromacro」,单击确定,再次输入密码确定,再次单击确定,完成题设任务。
图14
图15
任务1-8保护工作表「投资项目列表」,密码为「micromacro」。
保护工作表「利润预测」,在保护后,不可以选定工作表中任意单元格,但可以通过控件修改工作表中的数据,不使用密码。
注释:
任务分为两部分,第一部分是对工作表进行保护,第二部分是对在保护工作表的状态下设置可以更改的控件。
解法:
a)打开案例素材“01-08”,定位到「投资项目列表」工作表;
b)在“审阅”选项卡,选择“保护工作表”选项,输入密码「micromacro」,确定,再次输入密码确定。
图16
图17
c)切换至「利润预测」工作表,选中B2单元格,在”开始”选项卡,”对齐方式”选项组,选择“保护”选项,取消“锁定”按钮;
d)然后在“审阅”选项卡,选择“保护工作表”选项,输入密码「micromacro」,确定,再次输入密码确定,完成任务要求。
注意,本次保护工作表时,“选定锁定单元格”和“选定未锁定的单元格”的复选框是未被选中的状态。
图18
图19
扩展:
在默认状态下每个单元格都属于“锁定”状态,在锁定状态下,保护工作表之后,单元格是无法做任何更改的。
因此想要在保护工作表之后还能修改单元格,就需要在保护单元格之前取消对单元格的锁定。
任务1-9修改工作簿计算选项,以便包含公式的单元格计算结果发生改变的时候,只有通过手动重算或者保存工作簿才显示该变化。
注释:
考察取消自动重算,改为手动重算
解法:
a)打开案例素材“01-09”;
b)打开“文件”后台视图,选择“选项”,在弹出的Excel选项对话框中,选择“公式”选项,将“计算选项”中的“自动重算”改为“手动重算”,并勾选“保护工作簿前重新计算”,单击确定,完成任务要求。
图110
任务1-10保护工作簿,以便用户无法添加、删除或修改工作表,除非输入密码「MicroMacro」。
注释:
考察对工作簿的保护
解法:
a)打开案例素材“01-10”;
b)选择“审阅”选项卡,单击“更改”选项组的“保护工作簿”选项。
图111
图112
任务1-11将工作簿的自动保存间隔时间设置为15分钟。
注释:
对Excel选项的更改,设置自动保存时间间隔。
解法:
a)打开案例素材“01-11”;
b)打开“文件”后台视图,选择“选项”,在弹出的Excel选项对话框中,选择“保存”选项,将“保存自动恢复信息时间间隔”设置为15分钟,单击确定,即可完成任务要求。
单元2应用自定义数据格式和设置
2.1应用自定义数据格式和数据验证
任务2-1在「1季度销售数据」工作表上,格式化列H,使得其中的数值显示为2位小数。
将格式应用到现有的行和新行。
注释:
考察设置单元格格式
解法:
a)打开案例素材“02-01”;
b)选中H列,在“开始”选项卡,“数字”选项组中,单击右下角的斜向箭头,弹出“设置单元格格式”对话窗口,将数字格式设置为“数值”,且小数数位为2位,单击确定按钮,完成题设要求。
图21
任务2-2在「10月销售记录」工作表上的单元格C3:
C33中填入「十月」。
不要更改单元格的格式。
注释:
考察快速填充功能
解法:
a)打开案例素材“02-02”;
b)将光标定位到C2单元格,将鼠标放到单元格右下角的填充柄上,当光标变为十字时,双击鼠标,完成快速填充。
c)单击C列最后一行单元格右下角的“自动填充选项”下拉菜单
,选择“复制单元格”,即可完成题设要求。
任务2-3在「1季度销售数据」工作表上,为列C设置数据验证,使得只能输入「手机」、「平板电脑」、「笔记本电脑」和「台式电脑」。
将规则应用到现有的行和新行。
注释:
考察数据验证
解法:
a)打开案例素材“02-03”;
b)首先选中C列,选择“数据”选项卡,单击“数据工具”选项组“数据验证”选项,在弹出的“数据验证”对话框中,将验证条件设定为“序列”;
c)在“来源”中,分别输入“手机,平板电脑,笔记本电脑,台式电脑”,单击确定按钮,即可完成题设的要求。
图22
扩展:
在输入的序列文本中,文本之间必须是英文状态下的逗号才可以。
2.2应用高级条件格式
任务2-4在「图书销售」工作表上,修改单元格区域I3:
I34的[条件格式]规则,使用内置规则将该列中所有低于平均值的数值的字体颜色更改为绿色。
注释:
考察设置单元格的条件格式
解法:
a)打开案例素材“02-04”,选中I3:
I34单元格区域;
b)在“开始”选项卡,“样式”选项组,“条件格式”下拉菜单中的“项目选取规则”选项中,选择“低于平均值”选项;
c)在弹出的低于平均值对话框中,选择“自定义格式”,在“字体”选项中,将字体颜色设置为绿色,单击确定,完成任务要求。
图23
图24
图25
任务2-5在「图书销售情况」工作表上,对列E应用条件格式规则,在值大于或等于30000时显示绿色圆圈,在值大于或等于15000但小于30000时显示黄色圆圈,在值小于15000时显示红色圆圈。
应将格式应用到列E中的新行和现有行。
注释:
考察自定义的条件格式设置
解法:
a)打开案例素材“02-05”;
b)选中E列单元格,在“开始”选项卡,“样式”选项组中选择“条件格式”选项,在下拉菜单中选择“新建规则”;
c)在弹出
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MOSExcel 专家级 考点
![提示](https://static.bdocx.com/images/bang_tan.gif)