真题练习45-Excel电子表格-全国计算机等级考试二级MS Office高级应用与设计考试【汪老师的个人课堂】

题库下载

全国计算机等级考试题库下载（用电脑下载安装）：请点击

视频讲解

真题练习45-Excel电子表格_哔哩哔哩_bilibili

题目要求

第45组

某公司销售部门主管大华拟对本公司产品前两季度的销售情况进行统计，按下述要求帮助他完成整理分析工作。

1．打开考生文件夹下的素材文档Excel.xlsx（“.xlsx”为文件扩展名），后续操作均基于此文件，否则不得分。操作过程中，不得随意改变各工作表中数据的顺序。

2．按照“产品基本信息表”所列信息，运用公式或函数分别在工作表“一季度销售情况表”、“二季度销售情况表”中填入各型号产品对应的单价（产品型号代码是唯一的），并计算各月销售额填入F列中，填列相关数据时应保留原有单元格的边框及填充格式。将单价和销售额的数字格式均设为数值、保留两位小数、使用千位分隔符。

3．在“产品销售汇总表”中，分别计算各型号产品的一、二季度销量、销售额及合计数，填入相应列中。所有销售额的数字格式均设为数值型、小数位数0，使用千位分隔符。

4．在“产品销售汇总表”中，在不改变原有数据顺序的情况下，按一二季度销售总额从高到低给出销售额排名（其中并列者占用名次），填入I列相应单元格中，并显示为“第1名、第2名……”。通过设置条件格式将排名前3位和后3位的产品所在记录行分别用标准红色和标准绿色字体标出。

5．为“产品销售汇总表”的数据区域A1:I21套用一个表格格式，包含表标题，并取消列标题行的筛选标记。

6．参照考生文件夹下的文件“E透视表样例.png”，以“产品销售汇总表”为数据源、自新工作表“透视分析”的A3单元格开始创建数据透视表，统计每个产品类别的一、二季度销售额及总销售额，要求如下：

① 透视表结构、以及各行数据的列标题应与示例完全相同，不得多列或少列。

② 按两个季度销售总额从高到低进行排序。

③ 适当改变透视表的文本字体和数字格式。

7．将“透视分析”工作表标签颜色设为标准紫色，并移动到“产品销售汇总表”的右侧。

文字解析

第45组

1．【解析】双击打开考生文件夹下的“Excel.xlsx”文件。

2．【解析】步骤1：在“一季度销售情况表”工作表中，在E2单元格中输入公式“=VLOOKUP(B2,产品基本信息表!$B$1:$C$21,2,0)”按<Enter>键，在E3单元格中输入公式“=VLOOKUP(B3,产品基本信息表!$B$1:$C$21,2,0)”按<Enter>键，然后同时选中E2和E3单元格，双击“智能填充柄”进行填充其余单元格；

步骤2：在F2单元格中输入公式“=D2*E2”按<Enter>键，在F3单元格中输入公式“=D3*E3”按<Enter>键，然后同时选中F2和F3单元格，双击“智能填充柄”进行填充其余单元格；

步骤3：选中E列和F列，单击鼠标右键，选择“设置单元格格式”，打开“设置单元格格式”对话框，在“数字”选项卡中选择“数值”、2位小数，并勾选“使用千位分隔符”，单击“确定”按钮；

步骤4：使用（1）~（3）相同的方法，在“二季度销售情况表”E2和F2单元格中输入相应的公式，并设置单元格格式。

3．【解析】步骤1：选中“产品销售汇总表”的D、F、H列，单击鼠标右键，选择“设置单元格格式”，打开“设置单元格格式”对话框，在“数字”分组中，选择“数值”、0位小数，并勾选“使用千位分隔符”，对齐方式为右对齐，单击“确定”按钮；

步骤2：在C2单元格中输入公式“=SUMIF(一季度销售情况表!$B$2:$B$44,B2,一季度销售情况表!$D$2:$D$44)”，按<Enter>键，双击“智能填充柄”进行填充其余单元格；

步骤3：在D2单元格中输入公式“=SUMIF(一季度销售情况表!$B$2:$B$44,B2,一季度销售情况表!$F$2:$F$44)”，按<Enter>键，双击“智能填充柄”进行填充其余单元格；

步骤4：在E2单元格中输入公式“=SUMIF('二季度销售情况表 '!B2:B43,B2,'二季度销售情况表 '!$D$2:$D$43)”，按<Enter>键，双击“智能填充柄”进行填充其余单元格；

步骤5：在F2单元格中输入公式“=SUMIF('二季度销售情况表 '!$B$2:$B$43,B2,'二季度销售情况表 '!$F$2:$F$43)”，按<Enter>键，双击“智能填充柄”进行填充其余单元格；

步骤6：在G2单元格中输入公式“=C2+E2”，双击“智能填充柄”进行填充其余单元格；

步骤7：在H2单元格中输入公式“=D2+F2”，双击“智能填充柄”进行填充其余单元格。

4．【解析】步骤1：在I2单元格中输入带函数公式“="第" & RANK(H2,$H$2:$H$21,0) & "名"”，按<Enter>键；

步骤2：双击“智能填充柄”进行填充其余单元格。

知识点拨：RANK函数是排位函数，主要功能是返回一个数值在指定数值列表中的排位。本题中“=RANK(H2,$H$2:$H$21,0)”是指求取H2单元格中的数值在单元格区域H2:H21中的降序排位。

步骤3：选定“产品销售汇总表”工作表中的数据区域，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中单击“新建规则”命令，打开“新建格式规则”对话框；

步骤4：在“选中规则类型”中选“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入“=$H2>1548143”；

步骤5：单击“格式”按钮，打开“设置单元格格式”对话框，在“字体”分组中设置字体颜色为标准色中的“红色”，单击“确定”按钮；

步骤4：单击“新建规则”按钮，打开“新建格式规则”对话框，在“选中规则类型”中选“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入“=$H2<318473”；

步骤6：单击“格式”按钮，打开“设置单元格格式”对话框，在“字体”分组中设置字体颜色为标准色中的“绿色”，单击“确定”按钮，再次单击“确定”按钮。

5．【解析】步骤1：在“产品销售汇总表”中选“A1:I21”单元格，单击“开始”选项卡下“样式”分组中的“套用表格格式”下拉按钮，选择一种表格格式；

步骤2：在打开的“套用表格式”对话框中勾选“表包含标题（M）”复选框，单击“确定”按钮；

步骤3：单击“开始”选项卡下“编辑”分组中的“排序或筛选”按钮，在下方单击“筛选”按钮，取消列标题行的筛选标记。

6．【解析】步骤1：单击工作表名称右侧的“插入工作”按钮新建一个工作表，重命名为“透视分析”；

步骤2：在“产品销售汇总表”工作表中选择“A1:I21”单元格区域，单击“插入”|“表格”分组中的“数据透视表”下拉按钮，在列表中单击“表格和区域”，打开“来自表格或区域的数据透视表”对话框；

步骤3：在对话框中选择“现有工作表”，位置为“透视分析!A3”，单击“确定”按钮。

①步骤1：在“选择要添加到报表的字段”列表框中选择“产品类别代码”，拖拽至“行标签”列表框中，然后分别将“一季度销售额”、“二季度销售额”、“一二季度销售总额”添加至“数值”列表框中；

步骤2：在工作表中选择“行标签”，命名为“产品类别”。双击“求和项:一季度销售额”，会弹出“字段值设置”对话框，将自定义名称改为“第一季度销售额”，然后单击“确定”按钮。采用同样的方法，将后两个修改成“第二季度销售额”、“两个季度销售总额”。

②单击“产品类别”右侧的“自动排序”按钮，在打开的下拉列表中选择“其他排序选项”，在打开的对话框中选中“降序排序（Z到A）依据”单选按钮，并在其下方的下拉列表中选择“两个季度销售总额”选项，单击“确定”按钮。

③步骤1：选中B4:D7单元格区域，单击鼠标右键，在弹出的菜单中单击“设置单元格格式”命令，在“数字”选项卡下设置分类为“数值”、“0位小数”，并勾选“使用千位分隔符”复选框；

步骤2：选中A3:D7单元格区域，设置为一种字体（非默认的宋体，例如：黑体）。

7．【解析】步骤1：在“透视分析”工作表标签上单击鼠标右键，在打开的快捷菜单中选择“工作表标签颜色”选项，在颜色列表中选择“紫色”；

步骤2：向右拖动“透视分析”工作表标签，将其放置在“产品销售汇总表”的右侧。

保存并关闭Excel文件。