使用数据透视图展示数据 MOSExcel2016Expert MOSExcel2016Expert第一章 目录MOSExcel2016Expert1MOSExcel2016Expert考点汇总2MOSExcel2016Expert考点解析3单元1管理工作簿选项和设置31.1管理工作簿31.2管理工作簿审阅8单元2应用自定义数据格式和设置132.1应用自定义数据格式和数据验证132.2应用高级条件格式162.3创建与修改自定义工作簿元素212.4配置工作簿的国际化应用28单元3应用高级公式和数据分析工具303.1使用函数进行逻辑判断303.2使用函数进行条件统计343.3使用函数查找数据373.4使用函数处理日期和时间403.5为单元格和表格创建名称403.6审核公式和查找错误423.7使用数据分析工具43单元4分析和展示数据484.1创建高级图表484.2使用数据透视表分析数据514.3使用数据透视图展示数据57MOSExcel2016Expert考点汇总章节大纲项目任务及考点单元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-01b) 单击“文件”后台视图；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函数中，我们能够看到参数是“表11月: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图17c) 切换至利润预测工作表，选中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) 在“来源”中，分别输入“手机,平板电脑,笔记本电脑,台式