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

视频讲解

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

题库下载

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

题目要求

第49组【类似20】第8组

正则明事务所的统计员小任需要对本所外汇报告的完成情况进行统计分析，并据此计算员工奖金。按照下列要求帮助小任完成相关的统计工作并对结果进行保存。

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

2．将文档中以每位员工姓名命名的5个工作表内容合并到一个名为“全部统计结果”的新工作表中，合并结果自A2单元格开始、保持A2～G2单元格中的列标题依次为报告文号、客户简称、报告收费（元）、报告修改次数、是否填报、是否审核、是否通知客户，然后将其他5个工作表隐藏。

3．在“客户简称”和“报告收费（元）”两列之间插入一个新列、列标题为“责任人”，限定该列中的内容只能是员工姓名高小丹、刘君赢、王铬争、石明砚、杨晓柯中的一个，并提供输入用下拉箭头

，然后根据原始工作表名依次输入每个报告所对应的员工责任人姓名。

4．利用条件格式“浅红色填充”标记重复的报告文号，按“报告文号”升序、“客户简称”笔划降序排列数据区域。将重复的报告文号后依次增加(1)、(2)格式的序号进行区分【要求使用西文半角括号，如13(1)】。

5．在数据区域的最右侧增加“完成情况”列，在该列中按以下规则、运用公式和函数填写统计结果：当左侧三项“是否填报”、“是否审核”、“是否通知客户”全部为“是”时显示“完成”，否则为“未完成”，将所有“未完成”的单元格以标准红色文本突出显示。

6．在“完成情况”列的右侧增加“报告奖金”列，按照下表所列条件、运用公式和函数对每个报告的员工奖金数进行计算。另外当完成情况为“完成”时，每个报告多加30元的奖金，未完成时没有额外奖金：

报告收费金额（元）	奖金（元/每个报告）
小于等于1000	80
大于1000小于等于2800	报告收费金额的8%
大于2800	报告收费金额的10%

7．适当调整数据区域的数字格式、对齐方式，加大行高和列宽，并为其套用一个内置的表格格式。

8．显示隐藏的工作表“员工个人情况统计”，在该工作表中进行下列操作：

① 对每位员工的报告完成情况以及奖金数进行统计，并将结果填入相应的单元格。

② 依据步骤①的统计结果，参照“E饼图样例.png”生成一个三维饼图用于显示不同修改次数（0、1、2、3、4次）的报告数所占比例，要求在图表中标示保留两位小数的比例值，并且将图表放置在数据源的下方。

文字解析

第49组

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

2．【解析】步骤1：单击“杨晓柯”工作表标签后面的“新工作表”按钮，新建一个工作表，在此工作表标签上右击，选择“重命名”，然后输入：全部统计结果。

步骤2：单击“高小丹”工作表，将数据复制到“全部统计结果”中（粘贴之前选中A3单元格），其他表格数据也依次复制到“全部统计结果”工作表。

步骤3：单击“高小丹”工作表，然后按住<Shift>键，再单击“杨晓柯”工作表，在五个选中的工作表中任意工作表标签上单击右键，选择“隐藏”，完成工作表的隐藏。

3．【解析】步骤1：单击“C列”，在C列区域内右击，选择“插入”命令插入一列，在C2单元格输入：责任人。

步骤2：选中C列，然后单击“数据”选项卡，选择“数据验证”中“数据验证”下拉按钮，选择“数据验证”，弹出“数据验证”对话框，在“设置”选项卡中“允许”分组中选择“序列”命令。

步骤3：在来源中输入“高小丹,刘君赢,王铬争,石明砚,杨晓柯”（逗号是英文状态下半角逗号），单击“确定”按钮，然后根据原始工作表依次输入每个报告所对应的员工责任人的姓名。

4．【解析】步骤1：选中A列，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中选择“突出显示单元格规则”菜单中的“重复值”命令。

在弹出的“重复值”对话框中的“设置为”后组合框中选择“浅红色填充”，然后单击“确定”按钮。

步骤2：选中所有数据单元格，单击“数据”|“排列和筛选”分组中的“排序”按钮，在弹出的“排序”对话框中的“主要关键字”中选择“报告文号”，次序中选择“升序”。

步骤3：单击“添加条件”按钮，在新出现的条件中“次要关键字”设置为“客户简称”，再单击“选项”按钮，在弹出的“排序选项”中设置方法为“笔划排序”，然后单击“确定”按钮，在其后的次序中选中“降序”，单击“确定”按钮。

步骤4：双击第一个浅红色填充单元格，将光标移至第一个“042”，然后输入“(1)”，同样的方法，在第二个“042”单元格中添加“(2)”（注意：此时浅红色消失为正常）。

5．【解析】步骤1：选择I2，在I2中输入“完成情况”，再单击I3,在I3中输入公式“=IF(AND(F3="是",G3="是",H3="是"),"完成","未完成")”，最后从I3完成填充至I94。

步骤2：单击“数据”|“排序和筛选”分组中的“筛选”按钮，单击“完成情况”列筛选按钮，在下拉列表中取消“完成”选项的选中，选中“未完成”选项，此时筛选出所有未完成项；

步骤3：选中所有未完成项，然后单击“开始”|“字体”分组中的“字体颜色”下拉按钮，在下拉列表中选中标准中的“红色”按钮；单击“编辑”分组中的排序和筛选下拉按钮，取消其中的“筛选”按钮。

6．【解析】步骤1：在J1单元格中输入“报告奖金”；

步骤2：在J3单元格中输入公式“=IF(D3<=1000,80,IF(D3<=2800,D3*8%,D3*10%))+IF(I3="完成",30,0)”，然后单击“J3”单元格右下角填充柄完成填充。

7．【解析】步骤1：选中D列和J列，单击“开始”|“数字”分组中的组合框，选择其中的货币，完成数字格式设置。

步骤2：选中整个有数据的区域，然后选择“对齐方式”分组中的“垂直居中”和“居中”按钮，完成对齐方式设置。

步骤3：选择所有数据的行，然后单击“开始”|“单元格”分组中的“格式”按钮，在弹出的下拉列表中单击行高和列宽，加大行高和列宽。

步骤4：单击“样式”分组中的“套用表格格式”下拉按钮，在下拉列表中选择一个合适的样式（例如：橄榄色，表样式浅色11），在弹出的“套用表格式”对话框中选中“表包含标题”复选框，然后单击“确定”按钮。

8．【解析】在“工作表标签”上单击鼠标右键，然后单击“取消隐藏”命令，打开“取消隐藏”对话框，选中“员工个人情况统计”，单击“确定”按钮。

① 步骤1：在B3单元格中输入带函数公式“=COUNTIF(表2[责任人],[@姓名])”。

步骤2：在C3单元格中输入“=COUNTIFS(表2[责任人],[@姓名],表2[报告修改次数],0)”。

步骤3：在D3单元格中输入“=COUNTIFS(表2[责任人],[@姓名],表2[报告修改次数],1)”。

步骤4：在E3单元格中输入“=COUNTIFS(表2[责任人],[@姓名],表2[报告修改次数],2)”。

步骤5：在F3单元格中输入“=COUNTIFS(表2[责任人],[@姓名],表2[报告修改次数],3)”。

步骤6：在G3单元格中输入“=COUNTIFS(表2[责任人],[@姓名],表2[报告修改次数],4)”。

步骤7：在H3单元格中输入“=SUMIF(表2[责任人],[@姓名],全部统计结果!J:J)”。

步骤8：然后选中B3单元格至H3单元格，按住H3单元格右下角的填充柄，拖至H7单元格，完成B3单元格到H7单元格所有公式的填充。

步骤9：在B8单元格中输入“=SUM(B3:B7)”然后按住B8单元格右下角的填充柄，拖至H8单元格，完成合计填充。

② 步骤1：选中C2单元格至G2单元格，然后按住<Ctrl>键，再选中“C8:G8”单元格。

步骤2：单击“插入”|“图表”分组中的“饼图”下拉按钮，在下拉列表中选择“三维饼图”菜单中的“三维饼图”命令。

步骤3：单击“图表工具”|“图表设计”选项卡中“数据”分组中的“选择数据”按钮，在弹出的“选择数据源”对话框中单击“切换行/列”按钮，单击“确定”按钮。

步骤4：在饼图上单击鼠标右键，在弹出的菜单中单击“添加数据标签”命令.这个时候会在饼图内显示数值，在该数值上单击鼠标右键，在弹出的快捷菜单中选择“设置数据标签格式”；

步骤5：在弹出的“设置数据标签格式”窗格中单击“标签选项”选项卡；

步骤6：将“标签包括”分组中的“值”前面的复选框取消勾选，选中“百分比”前复选框。

步骤7：单击“数字”选项卡，在“类别”选择“百分比”项，在“标签位置”中选中“数据标签外”按钮；

步骤8：在饼图上单击鼠标右键，在弹出菜单中单击“设置数据系列格式”命令，打开“设置数据系列格式”窗格，在“系列选项”选项卡中设置适当的饼图分离值（例如：10%）；

步骤9：选中图表，单击【图表工具】|【图表设计】|【图表布局】分组中的“添加图表元素”下拉按钮，在列表中单击“图例”级联菜单中的“右侧”命令，移动图表至数据区域下方；

保存并关闭“Excel.xlsx”文件。