MOSExcelExpert讲义与答案.pdf

深圳微软技术中心 微软MCT：丁 慧 任务任务1 在 工作表“2008年销售总额计划”中，追踪单元格F9的所有直接和 间接的公式引用。（素材1） 提示：选中F9单元格公式追踪引用单元格（直接引用）-再次单 击追踪引用单元格（间接引用） 任务任务2 在工作表“学生成绩单”中，追踪不一致公式的所有公式引用。（素 材2） 提示：选中F9单元格公式错误检查错误检查下一个追踪引 用单元格 绿色小三角表示：引用不一致 任务任务3 在工作表“所得税计算”中，使用“公式求值”工具，更正C4中的 错误。 （素材3） 提示：选中C4单元格公式公式求值多次单击“求值”按钮 单击编辑栏自己动手修改错误值 任务任务4 启用迭代计算，并将最多迭代次数设置为25。 （素材4） 提示：文件选项公式启用迭代计算25确定 任务任务5 配置Excel，以使用蓝色标识检测到的公式错误。 （素材5） 提示：文件选项公式使用此颜色标识错误选中蓝色 任务任务6 在工作表“第二季度销售情况”的单元格G2中，插入SUMIFS语句， 该语句返回工号以“A”开头的销售人员所销售的“iPhone手机”的 销售数量。 （素材6） 提示：多条件求和 单击G2，输入公式 = sumifs(E2:E28,C2:C28,”A*”,D2:D28,D7) = sumifs(求和范围，条件范围，条件1，条件2范围，条件2） 任务任务7 在工作表“库存记录”的单元格H2中，使用COUNTIFS语句，计算 在部门2（Dept 2）中，有多少个库存种类（ Part Number ）的库存 价值（ Total Value ）超过了500 。 （素材7） 提示：多条件计数 =countifs(C2:C28,C2,F2:F28,”500”) =countifs(条件1范围，条件1，条件2范围，条件2） 任务任务8 在工作表“员工收入”的单元格M3中，使用AVERAGEIFS函数，计算 销售部门的员工平均奖金的数额。（剔除值为0的情况） （素材8） 提示：多条件求平均值 =averageifs(G3:G17,D3:D17,D3,G3:G17,”70”) =averageifs(求平均值的范围，条件1范围，条件1，条件2范围，条件2) 任务任务9 在工作表“员工收入”的单元格 C15中，使用HLOOKUP函数，计算 姓名为王娜的员工的实发工资的数额。 （素材9） 提示：行查找 =hlookup(C14,B4:P12,9,0) =hlookup(要找的值，包含要找的值的数据范围，返回值行数，精确 查找) 任务任务10 在工作表“员工收入”的单元格N3中，使用VLOOKUP函数，计算姓 名为王娜的员工的实发工资的数额。 （素材10） 提示： =vlookup(要找的值，包含要找值所在的范围，返回的列数，0精确查 找) 任务任务11 在工作表“员工收入”的单元格N3中，添加一个函数以对“销售” 部门中的员工进行计数。 （素材11） 提示： =countif(条件所在的范围，条件） 任务任务12 向“手机销售额”图表中添加多项式趋势线，该趋势线使用顺序3， 并且预测趋势前推两个周期。 （素材12） 提示：选中图表布局趋势线其他趋势线选项选中： 多项式顺序：前推： 任务任务13 在工作表“销售额比较 ”中，修复图表的数据源，以便柱形图包含 “王海涛”的销售数据。 （素材13） 提示：右击图表选择数据选中“销售额”数据单击“编 辑”系列值：重新选择，包含“王海涛”的销售数据确定 单击“水平分类轴标签”编辑重新选择，包含“王海涛” 确定 任务任务14 在工作表“费用统计”中，将图表样式更改为“样式34”，并应用 “白色，背景1，深色25%”的形状填充。用名称“对比分析”将图 表保存为图表模板。 （素材14） 提示：选中整个图表设计图表样式选中“样式”格式 形状填充整个图表背景，选中“白色，背景1，深色25%” 选定图表设计另存为模板输入文件名确定 任务任务15 创建并显示名为“Deutsche Bank”的方案，通过该方案将“贷款金 额：”的值更改为“2200000”；“还款期数：”的值更改为 “240”；“年利率：”的值更改为“5.2%”。 （素材15） 提示：数据模拟分析方案管理器添加方案名：Deutsche Bank可变金额：确定输入相应值确定显示 关闭 任务任务16 将工作簿中的数据合并到新工作表的单元格A1中，其值为“川渝1”， “川渝2”，“川渝3”，“川渝4”区域中值的总和。在首行和最左 列显示标签，并将新工作表命名为“川渝全年销售统计”。 （素材 16） 提示：插入一个新工作表重命名工作表：“川渝全年销售统计” 数据合并计算添加相关引用勾选：首行、最左列确定 任务任务17 在工作表“销售额汇总”中，插入切片器，以便数据透视表显示“产 品名称”和“运输方式”。 （素材17） 提示：插入切片器 任务任务18 在新工作表中创建“数据透视表”，该数据透视表的行标签为“销售 员姓名”，列标签为“产品名称”，求和项为“数量”。 （素材18） 提示：插入创建数据透视表行、列、求和字段 任务任务19 在工作表“1季度销售明细”中，创建数据透视图，以按照销售人员 显示每个月的TCL产品销售金额。将数据透视图放入新工作表中。 （素材19） 提示：创建数据透视图 轴：销售人员 求和区域：1、2、3月 报表筛选：产品名称 任务任务20 在新工作表中创建数据透视图，该数据透视图使用“苹果”，“葡 萄”，“西瓜 ”和“香蕉”中的数值显示北美地区的总计销 售金额。 （素材20） 提示：创建数据透视图 轴：地区，筛选：北美 求和区域：“苹果”，“葡萄”，“西瓜”，“香蕉” 任务任务21 在工作表 “1季度销售明细”中，创建对工作表单元格应用数字格式 “货币”和项目选取规则“高于平均值”的宏。将宏命名为“高于平 均值”，并将其保存在当前工作簿中。对“总计”列中的数值应用此 宏。（注意：接受其它的所有默认设置） （素材21） 提示：单击空白单元格先录制宏再应用宏 任务任务22 在工作表 “1季度销售明细”中，创建将行高设置为“25”像素，并 对单元格内容应用居中对齐格式的宏。将宏命名为“标题格式”，并 将其仅保存在当前工作簿中。（注意：接受其它的所有默认设置） （素材22） 提示：选中一行录制宏 任务任务23 在工作表 “1季度销售明细”单元格H1中，插入名为“TCL销售总计” 的“按钮（窗体控件）”，然后将此按钮指定给宏“TCL销售总计”。 （素材23） 提示：开发工具插入窗体控件按下ALT拖出一个按钮选中相 应的宏确定修改按钮名称为：TCL销售总计 任务任务24 在工作表“2011年PM 2.5数据” 中，更改“数值调节钮（窗体控 件）”，以便它可以将D2单元格中的数值更改为数字1-365，步长为 1。（注意：接受其它的所有默认设置） （素材24） 提示：选中控件右击设置控件格式控制设置最大值、最小值、 步长 任务任务25 创建名为“产品类别”的自定义属性，该属性是“文本类型”，取值 为“电子产品”。 （素材25） 提示：文件属性高级属性自定义名称：产品类别；类型：文 本；取值：电子产品单击：添加 任务任务26 使用现有的XML映射，对活动工作簿中的XML元素进行映射。然后在 文档文件夹中，将当前工作表导出为XML数据文件，文件名为“员工 信息.xml”。 （素材26） 提示：开发工具XML源选中右侧的所有字段，拖动到表格的第 一个字段名上单击：导出输入文件名：员工信息确定 先映射，才能导出 任务任务27 使用密码“1984”对工作簿进行加密，并将工作簿标记为最终状态。 （素材27） 提示：文件信息保护工作簿用密码进行加密输入密码标记 为最终状态 任务任务28 在工作表“学生成绩统计表”中，配置保护工作表，以便只能选择单 元格区域G3:G13，而所有其它单元格不可选。保护工作簿，但不使 用密码。 （素材28） 提示：选中G3:G13右击设置单元格格式保护取消：锁定复 选框确定审阅保护工作表仅勾选：选定未锁定的单元格保 护工作簿不输入密码确定 任务任务29 共享当前的工作簿，并将修订记录保存15天。 （素材29） 提示：审阅共享