真题练习16-Excel电子表格-全国计算机等级考试一级计算机基础及MS Office应用考试【汪老师的个人课堂】

视频讲解

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

题库下载

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

题目要求

销售部助理小王需要针对2012年和2013年的公司产品销售情况进行统计分析，以便制订新的销售计划和工作任务。现在，请按照如下需求完成工作：

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

2．在“订单明细”工作表中，删除订单编号重复的记录(保留第一次出现的那条记录），但须保持原订单明细的记录顺序。

3．在“订单明细”工作表的“单价”列中，利用VLOOKUP公式计算并填写相对应图书的单价金额。图书名称与图书单价的对应关系可参考工作表“图书定价”。

4．如果每订单的图书销量超过40本（含40本），则按照图书单价的9.3折进行销售；否则按照图书单价的原价进行销售。按照此规则，使用公式计算并填写“订单明细”工作表中每笔订单的“销售额小计”，保留2位小数。要求该工作表中的金额以显示精度参与后续的统计计算。

5．根据“订单明细”工作表的“发货地址”列信息，并参考“城市对照”工作表中省市与销售区域的对应关系，计算并填写“订单明细”工作表中每笔订单的“所属区域”。

6．根据“订单明细”工作表中的销售记录，分别创建名为“北区”、“南区”、“西区”和“东区”的工作表，这4个工作表中分别统计本销售区域各类图书的累计销售金额，统计格式请参考“Excel素材.xlsx”文件中的“统计样例”工作表。将这4个工作表中的金额设置为带千分位的、保留两位小数的数值格式。

7．在“统计报告”工作表中，分别根据“统计项目”列的描述，计算并填写所对应的“统计数据”单元格中的信息。

文字解析

1．【解析】打开“Excel_素材.xlsx”文件，单击“文件”|“另存为”，选择文件保存位置为考生文件夹、“文件名”为“Excel.xlsx”以及“保存类型”为“Excel工作簿（*xlsx）”，单击“保存”按钮。

2.【解析】步骤1：将光标定位于“订单明细”工作表中，单击“设计”|“工具”分组中的“删除重复项”按钮，打开“删除重复项”对话框；

步骤2：单击“取消全选”按钮，选中“订单编号”复选框，单击“确定”按钮。

3.【解析】在“订单明细”工作表的E3单元格内输入公式“=VLOOKUP([@图书名称],表2,2,0)”，然后按回车键。

4.【解析】（1）在I3单元格内输入公式“=PRODUCT(IF([@销量（本）]<40,[@单价],[@单价]*0.93),[@销量（本）])”，然后按回车键；

（2）单击“文件”|“选项”命令，打开“Excel选项”对话框，选中左侧的“高级”选项，选中“计算此工作薄时”分组中的“将精度设为所显示的精度”复选框，单击“确定”按钮。

（2）选中“销售额小计”数据区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”，打开“设置单元格格式”对话框，在“数字”选项卡下选择“货币”，小数位数为2，单击“确定”按钮。

5.【解析】在“订单明细”工作表中的H3单元格中输入公式“=VLOOKUP(LEFT([@发货地址],3),表3,2,0)”，然后按回车键。

6.【解析】步骤1：将光标定位于“订单明细”工作表中，单击“插入”|“表格”分组中的“数据透视表”下拉按钮，在下拉列表中单击“数据透视表”命令，打开“创建数据透视表”对话框；

步骤2：在选择一个表或区域中输入“表1”，在选中放置数据透视表的位置中选中“新建工作表”，单击“确定”按钮，此时会新建一个工作表，删除第一行和第二行；

步骤3：将“选择要添加到报表的字段”中的“所属区域”字段拖动到“报表筛选”中，将“图书名称”字段拖动到“行标签”中，将“销售额小计”字段拖动到“数值”中；

步骤4：选中B4:B21区域，单击“数字”分组中的对话框启动器，打开“设置单元格格式”对话框；

步骤5：选中分类中的“货币”，设置小数位为“2”，单击“确定”按钮；

步骤6：单击“选项”|“数据透视表”分组中的“选项”下拉按钮，在下拉列表中单击“显示报表筛选页”命令。

7.【解析】（1）在“统计报告”工作表中的B3单元格内输入带函数的公式“=SUMIFS(表1[销售额小计],表1[日期],">=2013年1月1日")”，然后按<Enter>键。

（2）在B4单元格内输入公式“=SUMIFS(表1[销售额小计],表1[图书名称],"《MS Office高级应用》",表1[日期],"<2013年1月1日")”，然后按<Enter>键。

（3）在B5单元格内输入公式“=SUMIFS(表1[销售额小计],表1[日期],">=2013-7-1",表1[日期],"<=2013-9-30",表1[书店名称],"=隆华书店")”，然后按<Enter>键。

（4）在B6单元格内输入公式“=SUMIFS(表1[销售额小计],表1[书店名称],"隆华书店",表1[日期],">=2012年1月1日",表1[日期],"<=2012年12月31日")/12”，然后按<Enter>键。

（5）在B7单元格内输入公式“=SUMIFS(表1[销售额小计],表1[日期],">=2013-1-1",表1[日期],"<=2013-12-31",表1[书店名称],"=隆华书店")/B3”然后按<Enter>键。选中B7单元格，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，在“数字”选项卡下选择“百分比”，小数位数为2，单击“确定”按钮。