C20-【计算机二级】Excel操作题(20)统计正则明事务所外汇报告完成情况

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

1.在考生文件夹下，将“Excel素材1.xlsx”文件另存为“Excel.xlsx”(“.xlsx”为文件扩展名），除特殊指定外后续操作均基于此文件，否则不得分。

• 考点1：另存为

按F12，将文件另存在考生文件夹下，文件名为“Excel.xlsx”

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

①创建一个新工作表，命名为“全部统计结果”

②将每个工作表中的数据复制到“全部统计结果”中

3.在“客户简称”和“报告收费（元)”两列之间插入一个新列、列标题为“责任人”，限定该列中的内容只能是员工姓名高小丹、刘君赢、王铬争、石明砚、杨晓柯中的一个，并提供输入用下拉箭头，然后根据原始工作表名依次输入每个报告所对应的员工责任人姓名。

①在“客户简称”和“报告收费（元)”两列之间插入一个新列、列标题为“责任人”

②提供输入用下拉箭头，然后根据原始工作表名依次输入每个报告所对应的员工责任人姓名

• 考点2：数据验证

将每个人对应填入“责任人”列

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

①利用条件格式“浅红色填充”标记重复的报告文号

• 考点3：标记重复值

标记为浅红色填充深红色文本

②按“报告文号”升序、“客户简称”笔划降序排列数据区域

• 考点4：自定义排序

③将重复的报告文号后依次增加(1)、(2)格式的序号进行区分

因为重复的报告文号比较少，因此直接手动输入即可

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

①在数据区域的最右侧增加“完成情况”列

②填写统计结果

• 考点5：IF函数和AND函数嵌套

Ⅰ、先用AND函数判断是否三个单元格都为“是“

Ⅱ、用IF函数判断完成情况

②、将所有“未完成”的单元格以标准红色文本突出显示

注意“红色文本“并不是标准红色文本，要选择自定义格式

设置完成

6.在“完成情况”列的右侧增加“报告奖金”列，按照下列要求对每个报告的员工奖金数进行统计计算（以元为单位）。另外当完成情况为“完成”时，每个报告多加30元的奖金，未完成时没有额外奖金:

​

① 在“完成情况”列的右侧增加“报告奖金”列

• 考点6：IF函数嵌套

7.适当调整数据区域的数字格式、对齐方式以及行高和列宽等格式，并为其套用一个恰当的表格样式。

①调整数字格式、对齐方式、行高列宽等

②套用表格样式

8.显示隐藏的工作表“员工个人情况统计”。

• 考点7：显示隐藏的工作表

9.在工作表“员工个人情况统计”中，对每位员工的报告完成情况及奖金数进行计算统计并依次填入相应的单元格。

①撰写报告数

• 考点8：COUNTIF函数

②修改过0次、1次、2次、3次、4次的报告数

• 考点9：COUNTIFS函数

修改过0次的报告数

计算修改过1次、2次、3次、4次的报告数，只需要修改第四个参数即可

③报告奖金总计

• 考点10：SUMIFS函数（求和区域，条件区域，条件）

10.在工作表“员工个人情况统计”中，生成一个三维饼图统计全部报告的修改情况，显示不同修改次数(0、1、2、3、4次）的报告数所占的比例，并在图表中标示保留两位小数的比例值。图表放置在数据源的下方。

• 考点11：插入图表

在图表中标示保留两位小数的比例值

点击数据标签→更多选项

勾选“百分比“

勾选标签位置为“数据标签外“，数字类别为”百分比“，保留2位小数

删除图片标题，将图例放置在右侧

双击饼图，进入数据系列格式对话框，设置饼图的分离程度

别忘了第一问要求隐藏五个姓名工作表

Ctrl+S快速保存