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

视频讲解

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

题库下载

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

题目要求

销售部助理小王需要针对公司上半年产品销售情况进行统计分析，并根据全年销售计划执行进行评估。按照如下要求完成该项工作：

1．在考生文件夹下，打开“Excel素材.xlsx”文件，将其另存为“Excel.xlsx”（“.xlsx”为扩展名）之后所有的操作均基于此文件，否则不得分。

2．在“销售业绩表”工作表的“个人销售总计”列中，通过公式计算每名销售人员1月~6月的销售总和。

3．依据“个人销售总计”列的统计数据，在“销售业绩表”工作表的“销售排名”列中通过公式计算销售排行榜，个人销售总计排名第一的，显示“第1名”；个人销售总计排名第二的，显示“第2名”；以此类推。

4．在“按月统计”工作表中，利用公式计算1月~6月的销售达标率，即销售额大于60000元的人数所占比例，并填写在“销售达标率”行中。要求以百分比格式显示计算数据，并保留2位小数。

5．在“按月统计”工作表中，分别通过公式计算各月排名第1、第2和第3的销售业绩，并填写在“销售第一名业绩”、“销售第二名业绩”和“销售第三名业绩”所对应的单元格中。要求使用人民币会计专用数据格式，并保留2位小数。

6．依据“销售业绩表”中的数据明细，在“按部门统计”工作表中创建一个数据透视表，并将其放置于A1单元格。要求可以统计出各部门的人员数量，以及各部门的销售额占销售总额的比例。数据透视表效果可参考“按部门统计”工作表中的样例。

7．在“销售评估”工作表中创建一标题为“销售评估”的图表，借助此图表可以清晰反映每月“A类产品销售额”和“B类产品销售额”之和，与“计划销售额”的对比情况。图表效果可参考“销售评估”工作表中的样例。

文字解析

1．【解析】打开考生文件夹下“Excel素材.xlsx”文件，在“文件”选项卡下单击“另存为”按钮，打开“另存为”对话框，在“文件名”中输入“Excel.xlsx”（注意：查看本机的文件扩展名是否被隐藏，从而决定是否加后缀名“.xlsx”），将保存的路径设置为考生文件夹，单击“保存”按钮。

2．【解析】在“销售业绩表”工作表中，选择J3单元格，输入公式“=SUM(表1[@[一月份]:[六月份]])”，按<Enter>键即可。

3．【解析】在“销售业绩表”工作表中，选择K3单元格，单击“公式”选项卡下“其他函数”图标，单击其下拉菜单中“统计”菜单中的“RANK.EQ”函数。

在弹出的“函数参数”对话框中，有三个参数number，ref，order即数值、引用、排序：

第一个参数number在工作表上选中J3单元格，参数框内显示为“[@个人销售总计]”；

第二个参数ref在工作表上选中J3到J46单元格，参数框内显示为“[个人销售总计]”；

第三个参数order设置为0，即按销售业绩降序排名。此时“销售排名”列内显示名次的数字。

选中K3到K46单元格，单击右键，在弹出的下拉菜单中选择“设置单元格格式”项，在弹出的“设置单元格格式”对话框中，单击“数字”选项卡，选择“分类”中的“自定义”项，在“类型”下输入“第#名”，单击“确定”按钮。

4．【解析】在“按月统计”工作表中，选择B3单元格，单击“公式”选项卡下的“其他函数”图标，单击其下拉菜单中“统计”菜单中的“COUNTIF”函数。在弹出的“函数参数”对话框中，有两个参数range、criteria，将光标置于第一个参数range框中，单击“销售业绩表”工作表，在“销售业绩表”工作表中选中D3到D46单元格。

将光标置于第二个参数criteria框，输入“>60000”。双击B3单元格，在“=COUNTIF(表1[一月份],">60000")”后输入“/44”，按<Enter>键。其中“44”为销售部总人数。

选中“B3”单元格，单击右键，在弹出的下拉菜单中选择“设置单元格格式”项，在弹出的“设置单元格格式”对话框中，单击“数字”选项卡，选择“分类”中的“百分比”项，小数位数设置为“2”，单击“确定”按钮。选中B3单元格，将鼠标置于其右下角，显示十字光标后点中向右拖至G3单元格松开鼠标即可。

5．【解析】（1）在“按月统计”工作表中，选择B4单元格，单击“公式”选项卡下的“其他函数”图标，单击其下拉菜单中“统计”菜单中的“LARGE”函数。

在弹出的“函数参数”对话框中，有两个参数array、k，将光标置于第一个参数array框中，单击“销售业绩表”工作表，在“销售业绩表”工作表中选中D3到D46单元格，参数array框中显示“表1[一月份]”。将光标置于第二个参数k框，输入“1”，单击“确定”按钮。

（2）在“按月统计”工作表中，选择B5单元格，单击“公式”选项卡下的“其他函数”图标，单击其下拉菜单中“统计”菜单中的“LARGE”函数。在弹出的“函数参数”对话框中，有两个参数array、k。

将光标置于第一个参数array框中，单击“销售业绩表”工作表，在“销售业绩表”工作表中选中D3到D46单元格，参数array框中显示“表1[一月份]”。将光标置于第二个参数k框，输入“2”。单击“确定”按钮。

（3）在“按月统计”工作表中，选择B6单元格，单击“公式”选项卡下的“其他函数”图标，单击其下拉菜单中“统计”菜单中的“LARGE”函数。在弹出的“函数参数”对话框中，有两个参数array、k。

将光标置于第一个参数array框中，单击“销售业绩表”工作表，在“销售业绩表”工作表中选中D3到D46单元格，参数array框中显示“表1[一月份]”。将光标置于第二个参数k框，输入“3”。单击“确定”按钮。

选中B4:B6单元格，将鼠标置于其右下角，显示十字光标后点中向右拖至G6单元格松开鼠标。

选中B4:G6单元格，单击鼠标右键，在快捷菜单中选择“设置单元格格式”，打开“设置单元格格式”对话框，单击“数字”选项卡，选择“会计专用”“货币符号”为人民币符号，并保留2位小数，单击“确定”按钮。

6．【解析】（1）在工作表“按部门统计”中将光标置入A1数据单元格中，在“插入”选项卡“表格”分组中，单击“数据透视表”按钮，在展开的列表中选择“数据透视表”选项。

（2）启动创建数据透视表选项卡。将光标置于“选择一个表或区域”-“表/区域”文本框中，单击“销售业绩表”工作表，选中A2到J46单元格区域，“表/区域”文本框中显示“表1[[员工编号]：[个人销售总计]]”。

（3）在“选择放置数据透视表的位置”中选择“现有工作表”选项。此时会在“按部门统计”工作表下存放了一个数据透视表。

（4）在工作表“按部门统计”右侧出现一个“数据透视表字段列表”任务窗格。在“选择要添加到报表的字段”列表框中选中“销售团队”，显示在行标签中。在“选择要添加到报表的字段”列表框中选中“员工编号”，拖动到“在以下区域间拖动字段”选项组的“数值”下面。在“选择要添加到报表的字段”列表框中选中“个人销售总计”，显示在“在以下区域间拖动字段”选项组的“数值”下面。

（5）在工作表“按部门统计”中，选中C2:C5单元格区域，单击鼠标右键，选择快捷菜单中的“值显示方式”下的“列汇总的百分比”。

（6）在A1单元格，将“行标签”改为“部门”；双击B1单元格，在打开的“值字段设置”框中，将自定义名称下的“计数项：员工编号”改为“销售团队人数”；双击C1单元格，在打开的“值字段设置”框中，将自定义名称下的“求和项:个人销售总计”改为“各部门所占销售比例”。

7．【解析】（1）打开“销售评估”工作表，选择单元格区域A2:G5。在“插入”|“图表”分组中单击“插入柱形图或条形图”下拉按钮，在下拉列表中选择“堆积柱形图”。

（2）鼠标右键单击“计划销售额”柱形图显示部分，点击“设置数据系列格式”项，弹出“设置数据系列格式”对话框。在“系列选项”下，“间隙宽度”设置为“50%”。

在“系列绘制在”单选按钮设置在“次坐标轴”。在“填充”项下选择“无填充”单选按钮。在“填充与线条”下的“边框”项中设置在“实线”单选按钮，并将颜色设置为深红色。在“宽度”项中将宽度设置为1.5磅，然后单击“关闭”按钮。

（3）选中图例，单击鼠标右键，选择“设置图例格式”，在打开的“设置图例格式”窗口中，将图例位置设置在“靠下”。

（4）选中图标区，点击顶部菜单栏上的“布局”菜单，然后点击下方工具栏上的“图表标题”，设置标题的位置为“图表上方”，“图表标题”即显示在图标上方，将“图表标题”修改为“销售评估”。单击选中右坐标轴数据，然后删除。

（5）选中右侧坐标轴删除，然后右键单击左侧坐标轴，在弹出的菜单中单击“设置坐标轴格式”命令，在“坐标轴选项”选项卡中，设置单位“小”“500000”，“大”为“5000000”，最后单击“关闭”按钮。