2. 计算机
  3. 阿文是某食品贸易公司销售部助理，现需要对2015年的销售数据进行分析，根据以下要求，帮助她完成此项工作。 1．在考生文件夹下，将“Excel素材．xlsx”文件另存为“Excel．xlsx”(“．xlsx”为文件扩展名)，后续操作均基于此文件，否则

阿文是某食品贸易公司销售部助理，现需要对2015年的销售数据进行分析，根据以下要求，帮助她完成此项工作。 1．在考生文件夹下，将“Excel素材．xlsx”文件另存为“Excel．xlsx”(“．xlsx”为文件扩展名)，后续操作均基于此文件，否则

admin2019-09-10  78
问题 阿文是某食品贸易公司销售部助理，现需要对2015年的销售数据进行分析，根据以下要求，帮助她完成此项工作。
    1．在考生文件夹下，将“Excel素材．xlsx”文件另存为“Excel．xlsx”(“．xlsx”为文件扩展名)，后续操作均基于此文件，否则不得分。
    2．命名“产品信息”工作表的单元格区域A1：D78名称为“产品信息”；命名“客户信息”工作表的单元格区域A1：G92名称为“客户信息”。
    3．在“订单明细”工作表中，完成下列任务：
    ①根据B列中的产品代码，在C列、D列和E列填入相应的产品名称、产品类别和产品单价(对应信息可在“产品信息”工作表中查找)。
    ②设置G列单元格格式，折扣为0的单元格显示“-”，折扣大于0的单元格显示为百分比格式，并保留0位小数(如15％)。
    ③在H列中计算每个订单的销售金额，公式为“金额=单价×数量×(1-折扣)”，设置E列和H列单元格为货币格式，保留2位小数。
    4．在“订单信息”工作表中，完成下列任务：
    ①根据B列中的客户代码，在E列和F列填入相应的发货地区和发货城市(提示：需首先清除B列中的空格和不可见字符)，对应信息可在“客户信息”工作表中查找。
    ②在G列计算每个订单的订单金额，该信息可在“订单明细”工作表中查找(注意：一个订单可能包含多个产品)，计算结果设置为货币格式，保留2位小数。
    ③使用条件格式，将每个订单订货日期与发货日期间隔大于10天的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色,背景1”。
    5．在“产品类别分析”工作表中，完成下列任务：
    ①在B2：B9单元格区域计算每类产品的销售总额，设置单元格格式为货币格式，保留2位小数；并按照销售额对表格数据降序排序。
    ②在单元格区域D1：L17中创建复合饼图，并根据样例文件“图表参考效果．png”设置图表标题、绘图区、数据标签的内容及格式。
    6．在所有工作表的最右侧创建一个名为“地区和城市分析”的新工作表，并在该工作表A1：C19单元格区域创建数据透视表，以便按照地区和城市汇总订单金额。数据透视表设置需与样例文件“透视表参考效果．png”保持一致。
    7．在“客户信息”工作表中，根据每个客户的销售总额计算其所对应的客户等级(不要改变当前数据的排序)，等级评定标准可参考“客户等级”工作表；使用条件格式，将客户等级为1级～5级的记录所在单元格填充颜色设置为“红色”，字体颜色设置为“白色，背景1”。
    8．为文档添加自定义属性，属性名称为“机密”，类型为“是或否”，取值为“是”。
选项
答案1．在考生文件夹中打开“Excel素材．xlsx”文件，然后单击“文件”|“另存为”按钮，将文件名称中的“Excel素材”修改成“Excel”，然后单击“确定”按钮。注意：不能删除文件后缀“．xlsx”。 2．(1)步骤1：将光标定位在“产品信息”工作表A1单元格，然后按组合键，选中A1：G92单元格区域； 步骤2：在选中区域上单击鼠标右键，然后单击“定义名称”命令。在“新建名称”对话框中将名称修改为“客户信息”，单击“确定”按钮。 3．①步骤1：将光标定位在C2单元格，然后单击“插入函数”按钮，在“插入函数”对话框中找到“VLOOKUP”函数，单击“确定”按钮； 步骤2：在“函数参数”对话框中的第一个参数中选中要查询的表格，也就是B2单元格，在第二个参数中，设置查找范围，也就是产品信息表中的“A1：D78”区域(这里可以直接应用表名“产品信息”，注意，这是上一步定义的名称，不是工作表名)，在第三个参数中查询内容所在的列数，这里是第二列，也就是输入“2”，在第4个参数中输入0(表示精确查找)，单击“确定”按钮。 步骤3：双击C2单元格的智能填充柄，填充其他行的产品名称： 步骤4：将光标定位在D2单元格，然后单击“插入函数”按钮，在打开的“插入函数”对话框中找到“VLOOKUP”函数，单击“确定”按钮： 步骤5：在“函数参数”对话框中的第一个参数中选中要查询的表格，也就是B2单元格，在第二个参数中输入“产品信息”，在第三个参数中查询内容所在的列数，这里是第三列，也就是输入“3”，在第4个参数中输入0，单击“确定”按钮。 步骤6：双击D2单元格的智能填充柄，填充其他行的产品名称； 步骤7：将光标定位在E2单元格，然后单击“插入函数”按钮，在打开的“插入函数”对话框中找到“VLOOKUP”函数，单击“确定”按钮； 步骤8：在“函数参数”对话框中的第一个参数中选中要查询的表格，也就是B2单元格，在第二个参数中输入“产品信息”，在第三个参数中查询内容所在的列数，这里足第四列，也就是输入“4”，在第4个参数中输入0，单击“确定”按钮。 步骤9：双击E2单元格的智能填充柄，填充其他行的产品名称。 ②步骤1：选定G列单元格，单击“开始”|“数字”分组中的对话框启动器： 步骤2：选中“数字”选项卡中的“自定义”选项，在“类型”中输入“[=0]-；[＞0]0％”，单击“确定”按钮。 ③步骤1：在H2单元格中输入“=E2*F2*(1-G2)”，按回车键，双击“H2”单元格的智能填充柄； 步骤2：分别选中E列和H列，单击“数字”分组中“格式”下拉按钮，选中“货币”格式，设置小数位为2位(这里一般默认为2位小数位，这时就不用操作了)。 4．①由于客户代码中有空格和不可见字符，我们首先要清除掉，这里我们采用分列的方法来快速地清除： 步骤1：在B列后面插入一个空列。选中B列，单击“数据”|“数据工具”分组中的“分列”按钮； 步骤2：在弹出的“分列向导”对话框中选中“分隔符号”，单击“下一步”按钮，选中“空格”复选框，单击“下一步”按钮，单击“完成”按钮，单击“确定”按钮； 步骤3：删除空列C，就完成了清除B列空格和不可见字符操作； 步骤4：选中E2单元格，单击“插入函数”按钮，在“插入函数”对话框中找到“VLOOKUP”函数，单击“确定”按钮： 步骤5：第一个单元格是要查找内容，这里是B2单元格，所以输入B2；第二个参数是查找内容区域这里是客户信息表中的“客户信息”表(这里的客户信息是表区域的定义名称)：第三个参数是表示查找内容在查找区域中的列数，这里是F列，也就是第6列，因此输入6；第四个参数输入0(表示精确查找)；单击“确定”按钮； 步骤6：双击E2单元格智能填充柄，完成其他行的填充： 步骤7：采用同样方法求出发货城市，其函数公式是“=VLOOKUP(B2，客户信息，5，0)”： ②本小题需要计算订单明细中相同订单编号的订单金额总和，这里我们可以采用SUMIFS来计算。 步骤1：将光标定位在G2单元格，单击“插入函数”按钮，找到“SUMIFS”函数，单击“确定”按钮： 步骤2：第一个参数是要求和引用的区域，这里就是订单明细中的H列，因此输入“订单明细!$H$2：$H$907”(区域单元格要绝对引用)： 步骤3：第二个参数是要查找的条件区域，这里是要查找订单编号，因此输入“订单明细!$A$2：$A$907”； 步骤4：第三个参数是查找条件内容，这里输入A2，单击“确定”按钮。双击G2单元格智能填充柄完成其他行填充； 步骤5：选中G列，单击“开始”|“数字”分组中数字格式下拉列表，选择其中的“货币”，此时默认为2位小数位。 ③步骤1：选中A2：G342单元格区域，单击“样式”分组中“条件格式”下拉列表中的“新建规则”命令； 步骤2：选中“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入计算公式“=($D2-$C2)＞10”(注意：要设置整行单元格格式，必须要在两个单元格列标题前加“$”，行标题前不能加“$”)； 步骤3：单击“格式”按钮，将“字体”选项卡中的颜色设置为“白色，背景1”，将“填充”选项卡中的背景色设置为“红色”，单击“确定”按钮，单击“确定”按钮，再次单击“确定”按钮。 5．①步骤1：选中B2单元格，单击“插入函数”按钮，在打开的“插入函数”对话框中找到“SUMIFS”函数，单击“确定”按钮； 步骤2：第一个参数是求和的实际单元格区域，这里是订单明细中的金额列，单击H列即可： 步骤3：第二个参数是查找条件的单元格区域，题目是要查找产品类别，因此是D列，选中D列； 步骤4：第三个参数是查找条件内容，这里选中A2单元格，单击“确定”按钮； 步骤5：双击B2单元格智能填充柄，即可完成其他7项的销售额统计： 步骤6：选中“B2：B9”单元格区域，单击“开始”|“数字”分组中“货币”按钮； 步骤7：选中A1：B9单元格区域，单击“开始”|“编辑”分组中的“排序和筛选”下拉按钮中的“自定义排序”命令； 步骤8：在主要关键字中选中“销售额”，次序中选中“降序”，单击“确定”按钮。 ②步骤1：选中A1：B9单元格区域，单击“插入”|“图表”分组中的“饼图”下拉按钮中的“复合饼图”按钮； 步骤2：打开“图表参考效果．png”参考图片； 步骤3：参照设置饼图。选中图表标题，将图表标题内容修改为“各类产品所占比例”； 步骤4：单击“布局”|“标签”分组中“图例”下拉按钮中的“无”； 步骤5：单击“布局”|“标签”分组中“数据标签”下拉按钮中的“其他数据标签选项”； 步骤6：选中“标签选项”中的“类别名称”和“百分比”复选框，其他复选框取消选中，标签位置中选择“数据标签内”，单击“关闭”按钮； 步骤7：在数据区域单击鼠标右键，单击“设置数据系列格式”命令，在打开的“设置数据系列格式”对话框中，选中“系列选项”，设置“第二绘图区包含最后一个”为4，单击“关闭”按钮； 步骤8：将图表移动到D1：L17单元格区域，调整图表大小，使得图表在该区域中。 6．步骤1：单击“新建工作表”按钮，再将新建的工作表命名为“地区和城市分析”； 步骤2：将光标定位于A1单元格，单击“插入”|“表格”分组中“数据透视表”下拉按钮中的“数据透视表”命令； 步骤3：单击“创建数据透视表”对话框中的“表／区域”选择按钮，选中订单信息工作表中的E至G列，单击“选择”按钮返回对话框； 步骤4：选中“现有工作表”单选按钮，单击“位置”选择按钮，选中“地区和城市分析”工作表中的A1：C19单元格区域，单击“选择”按钮返回对话框，单击“确定”按钮； 步骤5：将“发货地区”和“发货城市”拖动到“行标签”，将“订单金额”拖动到“数值”； 步骤6：单击“设计”|“布局”分组中“分类汇总”下拉列表中的“不显示分类汇总”：单击“设计”|“布局”分组中“报表布局”下拉列表中的“以表格形式显示”命令； 步骤7：将“行标签”修改为“发货地区”，将“求和项”修改为“订单金额汇总”； 步骤8：单击取消选中“选项”|“显示”分组中的“加减号按钮”； 步骤9：选中C2：C19单元格区域，单击“开始”|“数字”分组中的“货币”按钮。 步骤10：单击“选项”|“数据透视表”分组中“选项”下拉列表中的“选项”命令，选中“布局和格式”选项卡中的“合并且居中排列带标签的单元格”，单击“确定”按钮。 7．(1)首先使用SUMIFS函数对各个客户的销售额进行求和。 步骤1：将光标定位于G2单元格，单击“插入函数”按钮，在打开的“插入函数”对话框中，找到SUMIFS函数，单击“确定”按钮； 步骤2：第一个参数为求和的实际单元格，这里我们选择G列“订单信息!G：G”； 步骤3：第二个参数为特定条件下的单元格区域，这里我们选择B列“订单信息!B：B”； 步骤4：第三个参数为条件内容，这里我们选“A2”，单击“确定”按钮，即求出了A2单元格客户的销售总额。 步骤5：然后采用VLOOKuP函数进行等级取值。在G2地址输入栏中，将光标定位在等号之后，然后输入“VLOOKUP(”，在公式最后输入“)”，单击“插入函数”按钮： 步骤6：这时第一个参数完成，填写第二个参数。第二个参数为查找区域，这里我们选客户等级工作表中的“A2：B11”区域(注意：也可以选A1：B11区域)，区域引用是要绝对引用，因此，引用要改成“客户等级!$A$2：$B$11”； 步骤7：第三个参数为满足条件的单元格所在数据区域列数，等级在第2列，因此填入“2”； 步骤8：第四个参输入1(0为精确匹配查找，非0为大致匹配查找，本题是大致查找，因此要输入除0以为的数字)。单击“确定”按钮； 步骤9：双击G2单元格智能填充柄，完成其他行填充。 注意：这里公式也可写成：“SUMIF(订单信息!$B$2：$B$342,A2，订单信息!$G$2：$G$342)”，这样也能得到正确结果，考试时是使用这个公式评分。 (2)步骤1：选中表格，单击“条件格式”下拉列表中“突出显示单元格规则”列表中的“介于”命令； 步骤2：在第一个单元格中输入“1级”，第二个单元格中输入“5级”； 步骤3：单击“设置为”下拉按钮，选中“自定义格式”： 步骤4：在“字体”选项卡中设置颜色为“白色，背景1”，在“填充”选项卡中设置背景为“红色”，单击“确定”按钮。单击“确定”按钮。 8．步骤1．单击“文件”|“信息”选项，单击“属性”下拉按钮中的“高级属性”命令； 步骤2：单击“自定义”选项卡，在名称中输入“机密”，在类型中选中“是或否”，在取值选中“是”，单击“添加”按钮。单击“确定”按钮。 保存并关闭Excel文档。
解析
转载请注明原文地址:https://www.kaotiyun.com/show/N66D777K
本试题收录于： 二级MS Office高级应用题库NCRE全国计算机二级分类
0

二级MS Office高级应用

NCRE全国计算机二级

相关试题推荐
随机试题
最新回复(0)