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

视频讲解

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

题库下载

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

题目要求

第42组【原13】

某停车场计划调整收费标准，拟从原来“不足15分钟按15分钟收费”调整为“不足15分钟部分不收费”的收费政策。市场部抽取了历史停车收费记录，期望通过分析掌握该政策调整后对营业额的影响。根据考生文件夹下Excel.xlsx文件中的数据信息，帮助市场分析员完成此项工作，具体要求如下：

1．打开考生文件夹下的素材文档Excel.xlsx（“.xlsx”为文件扩展名），后续操作均基于此文件，否则不得分。

2．在“停车收费记录”工作表中，涉及金额的单元格均设置为带货币符号（￥）的会计专用类型格式，并保留2位小数。

3．参考“收费标准”工作表，利用公式将收费标准金额填入到“停车收费记录”工作表的“收费标准”列。

4．利用“停车收费记录”工作表中“出场日期”、“出场时间”与“进场日期”、“进场时间”列的关系，计算“停放时间”列，该列计算结果的显示方式为“XX小时XX分钟”。

5．依据停放时间和收费标准，计算当前收费金额并填入“收费金额”列；计算拟采用新收费政策后预计收费金额并填入“拟收费金额”列；利用公式“拟收费金额-收费金额”计算拟调整后的收费与当前收费之间的差值，并填入“收费差值”列。

6．将“停车收费记录”工作表数据套用“表样式中等深浅12”表格格式，并添加汇总行，为“收费金额”、“拟收费金额”和“收费差值”列进行汇总求和。

7．在“收费金额”列中，将单次停车收费达到100元的单元格突出显示为黄底红字格式。

8．新建名为“数据透视分析”的工作表，在该工作表中创建3个数据透视表。

① 位于A3单元格的数据透视表行标签为“车型”，列标签为“进场日期”，求和项为“收费金额”，以分析当前每天的收费情况；

② 位于A11单元格的数据透视表行标签为“车型”，列标签为“进场日期”，求和项为“拟收费金额”，以分析调整收费标准后每天的收费情况；

③ 位于A19单元格的数据透视表行标签为“车型”，列标签为“进场日期”，求和项为“收费差值”，以分析调整收费标准后每天的收费变化情况。

文字解析

第42组

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

2．【解析】打开“停车收费记录”工作表，选中“收费标准”、“收费金额”、“拟收费金额”、“差值”列，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，在“数字”分组下“分类”中选择“会计专用”，小数位数为2，单击“确定”按钮。

3．【解析】在E2单元格内，输入带函数公式“=VLOOKUP(C2,收费标准!$A$3:$B$5,2,0)”，然后按回车键，双击E2单元格的智能填充柄完成其他行填充。

4．【解析】在J2单元格内，输入公式“=H2-F2+I2-G2”，然后按回车键，双击J2单元格的智能填充柄完成其他行填充。选中J列单元格，单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框，选中分类中的“自定义”，在类型中输入“[h]"小时"m"分钟"”，单击“确定”按钮。

5．【解析】（1）在“停车收费记录”工作表的K2单元格中，输入公式“=ROUNDUP(J2*24*60/15,0)*E2”，然后按回车键，双击K2单元格的智能填充柄完成其他行填充。

（2）在L2单元格内，输入带函数公式 “=ROUNDDOWN(J2*24*60/15,0)*E2”，然后按回车键，双击L2单元格的智能填充柄完成其他行填充。

（3）在M2单元格内输入公式“=L2-K2”，然后按回车键，双击M2单元格的智能填充柄完成其他行填充。

6．【解析】（1）选中“停车收费记录”工作表中任意单元格，在“开始”|“样式”分组中单击“套用表格格式”下拉按钮，在下拉列表选项中选择“表样式中等深浅12”样式即可。

（2）在A551单元格内输入文字“汇总”。在K551单元格输入公式“=SUM(K2:K550)”，然后按回车键，选中K551单元格，将光标定位在K551单元格的右下角，光标会变成细黑十字状的智能填充句柄，一直拖动到M551单元格。

7．【解析】步骤1：选中“收费金额”列除最后一行的数据区域，在“开始”|“样式”分组中单击“条件格式”的下拉按钮，在下拉列表选项中选择“突出显示单元格规则”中的“其它规则”，打开“新建规则类型”对话框。

步骤2：在“只为包含以下条件的单元格设置格式”中依次选择“单元格值”、“大于或等于”，最后输入数值100，单击“格式”按钮，打开“设置单元格格式”对话框，在“字体”选项卡下设置字体颜色为红色，在“填充”选项卡下选择“背景色”为黄色，依次单击“确定”按钮。

8．【解析】（1）在工作表下方单击“插入工作表”按钮，新建一个工作表，在工作名称上单击鼠标右键，在弹出的快捷菜单中选择“重命名”命令，输入文字“数据透视分析”，然后按<Enter>键。

（2）将光标定位在“数据透视分析”的A3单元格，在“插入”|“表格”分组中单击“数据透视表”的下拉按钮，在下拉列表选项中选择“数据透视表”，打开“创建数据透视表”对话框。在“表/区域”中输入“表1”，在“选择放置数据透视表的位置”中选择“现有工作表”，单击“确定”按钮。

在工作表的右侧会出现“数据透视表字段列”窗口，在“选择要添加到报表的字段”列表框中选中“车型”拖动到“在以下区域间拖动字段”选项组中的“行标签”下面，“进场日期”拖到“列标签”下面，“收费金额”拖到“数值”下面，且为“求和项”。

单击“行标签”筛选按钮，在下拉列表中取消“空白”项的选中。

（3）将光标定位在“数据透视分析”的A11单元格，在“插入”|“表格”分组中单击“数据透视表”的下拉按钮，在下拉列表选项中选择“数据透视表”，打开“创建数据透视表”对话框。

在“表/区域”中输入“表1”，在“选择放置数据透视表的位置”中选择“现有工作表”，单击“确定”按钮。

在工作表的右侧会出现“数据透视表字段列”窗口，在“选择要添加到报表的字段”列表框中选中“车型”拖动到“在以下区域间拖动字段”选项组中的“行标签”下面，“进场日期”拖到“列标签”下面，“拟收费金额”拖到“数值”下面，且为“求和项”。

单击“行标签”筛选按钮，在下拉列表中取消“空白”项的选中。

（4）将光标定位在“数据透视分析”的A19单元格，在“插入”选项卡下“表格”分组中单击“数据透视表”的下拉按钮，在下拉列表选项中选择“数据透视表”，打开“创建数据透视表”对话框。

在“表/区域”中输入“表1”，在“选择放置数据透视表的位置”中选择“现有工作表”，单击“确定”按钮。

在工作表的右侧会出现“数据透视表字段列”窗口，在“选择要添加到报表的字段”列表框中选中“车型”拖动到“在以下区域间拖动字段”选项组中的“行标签”下面，“进场日期”拖到“列标签”下面，“收费差值”拖到“数值”下面，且为“求和项”。

单击“行标签”筛选按钮，在下拉列表中取消“空白”项的选中。

保存电子表格并关闭。