视频讲解
真题练习20-Excel电子表格_哔哩哔哩_bilibili
题库下载
题目要求
第20套
正则明事务所的统计员小任需要对本所外汇报告的完成情况进行统计分析，并据此计算员工奖金。按照下列要求帮助小任完成相关的统计工作并对结果进行保存：
1．在考生文件夹下，将“Excel素材1.xlsx”文件另存为“Excel.xlsx”（“.xlsx”为文件扩展名），除特殊指定外后续操作均基于此文件，否则不得分。
2．将文档中以每位员工姓名命名的5个工作表内容合并到一个名为“全部统计结果”的新工作表中，合并结果自A2单元格开始、保持A2～G2单元格中的列标题依次为报告文号、客户简称、报告收费（元）、报告修改次数、是否填报、是否审核、是否通知客户，然后将其他5个工作表隐藏。
3．在“客户简称”和“报告收费（元）”两列之间插入一个新列、列标题为“责任人”，限定该列中的内容只能是员工姓名高小丹、刘君赢、王铬争、石明砚、杨晓柯中的一个，并提供输入用下拉箭头，然后根据原始工作表名依次输入每个报告所对应的员工责任人姓名。
4．利用条件格式“浅红色填充”标记重复的报告文号，按“报告文号”升序、“客户简称”笔划降序排列数据区域。将重复的报告文号后依次增加(1)、(2)格式的序号进行区分（使用西文括号，如13(1)）。
5．在数据区域的最右侧增加“完成情况”列，在该列中按以下规则、运用公式和函数填写统计结果：当左侧三项“是否填报”、“是否审核”、“是否通知客户”全部为“是”时显示“完成”，否则为“未完成”，将所有“未完成”的单元格以标准红色文本突出显示。
6．在“完成情况”列的右侧增加“报告奖金”列，按照下列要求对每个报告的员工奖金数进行统计计算（以元为单位）。另外当完成情况为“完成”时，每个报告多加30元的奖金，未完成时没有额外奖金：
7．适当调整数据区域的数字格式、对齐方式以及行高和列宽等格式，并为其套用一个恰当的表格样式。最后设置表格中仅“完成情况”和“报告奖金”两列数据不能被修改，密码为空。
8．打开工作簿“Excel素材2.xlsx”，将其中的工作表Sheet1移动或复制到工作簿“Excel.xlsx”的最右侧。将“Excel.xlsx”中的Sheet1重命名为“员工个人情况统计”，并将其工作表标签颜色设为标准紫色。
9．在工作表“员工个人情况统计”中，对每位员工的报告完成情况及奖金数进行计算统计并依次填入相应的单元格。
10．在工作表“员工个人情况统计”中，生成一个三维饼图统计全部报告的修改情况，显示不同修改次数（0、1、2、3、4次）的报告数所占的比例，并在图表中标示保留两位小数的比例值。图表放置在数据源的下方。
文字解析
第20组
1．【解析】打开考生文件夹下的“Excel素材1.xlsx”文件，单击“文件”选项卡下的“另存为”按钮，打开“另存为”对话框，在“文件名”中输入文字“Excel.xlsx”（注：“.xlsx”是扩展名，请查看本机是否隐藏了扩展名），将保存路径设置为考生文件夹，单击“确定”按钮。
2．【解析】步骤1：单击“杨晓柯”工作表标签后面的“新工作表”按钮，新建一个工作表，在此工作表标签上右击，选择“重命名”，然后输入：全部统计结果。
步骤2：单击“高小丹”工作表，将数据复制到“全部统计结果”中（粘贴之前选中A2单元格），其他表格数据也依次复制到“全部统计结果”工作表。
步骤3：单击“高小丹”工作表，然后按住<Shift>键，再单击“杨晓柯”工作表，在五个选中的工作表中任意工作表标签上单击右键，选择“隐藏”，完成工作表的隐藏。
3．【解析】步骤1：单击“C列”，在C列区域内右击，选择“插入”命令插入一列，在C2单元格输入：责任人。
步骤2：选中C列，然后单击“数据”选项卡，选择“数据验证”中“数据验证”下拉按钮，选择“数据验证”，弹出“数据验证”对话框，在“设置”选项卡中“允许”分组中选择“序列”命令。
步骤3：在来源中输入“高小丹,刘君赢,王铬争,石明砚,杨晓柯”（逗号是英文状态下半角逗号），单击“确定”按钮，然后根据原始工作表依次输入每个报告所对应的员工责任人的姓名。
4．【解析】步骤1：选中A列，单击“开始”|“样式”分组中的“条件格式”下拉按钮，在下拉列表中选择“突出显示单元格规则”菜单中的“重复值”命令。
在弹出的“重复值”对话框中的“设置为”后组合框中选择“浅红色填充”，然后单击“确定”按钮。
步骤2：选中除了标题行的所有数据单元格，单击“数据”|“排列和筛选”分组中的“排序”按钮，在弹出的“排序”对话框中的“主要关键字”中选择“列A”，次序中选择“升序”。
步骤3：单击“添加条件”按钮，在新出现的条件中“次要关键字”设置为“列B”，再单击“选项”按钮，在弹出的“排序选项”中设置方法为“笔划排序”，然后单击“确定”按钮，在其后的次序中选中“降序”，单击“确定”按钮。
步骤4：选择有浅红色标记的两行有数据区域，单击“数据”|“排序和筛选”分组中的“排序”按钮，弹出“排序”对话框。
步骤5：取消右上角“数据包含标题”复选框，在“次要关键字”组合框中选择B列，排序依据为数值，次序为降序。
步骤6：单击“选项”按钮，选择方法组中“笔划排序”。连续两次单击“确定”按钮。
步骤7：双击第一个浅红色填充单元格，将光标移至第一个“042”，然后输入“(1)”，同样的方法，在第二个“042”单元格中添加“(2)”（注意：此时浅红色消失为正常）。
5．【解析】步骤1：选择I2，在I2中输入“完成情况”，再次单击I3,在I3中输入公式“=IF(AND(F3="是",G3="是",H3="是")，最后从I3完成填充至I94.
步骤2：单击“完成情况”列筛选按钮，在下拉列表中取消“完成”选项的选中，选中“未完成”选项，此时筛选出所有未完成项；
步骤3：选中所有未完成项，然后单击“开始”|“字体”分组中的“字体颜色”下拉按钮，在下拉列表中选中标准中的“红色”按钮；单击“编辑”分组中的排序和筛选下拉按钮，取消其中的“筛选”按钮。
6．【解析】在J2输入“报告奖金”。在J3输入“=IF(D3<=1000,100,IF(D3<=2800,D3*8%,D3*10%))+IF(I3="完成",30,0)”，然后单击“J3”单元格右下角填充柄完成填充。
7．【解析】步骤1：选中D列和J列，单击“开始”|“数字”分组中的组合框，选择其中的货币，完成数字格式设置。
步骤2：选中整个有数据的区域，然后选择“对齐方式”分组中的“垂直居中”和“居中”按钮，完成对齐方式设置。
步骤3：选择所有数据的行，然后单击“开始”|“单元格”分组中的“格式”按钮，在弹出的下拉列表中单击“自动调整行高”和“自动调整列宽”命令。
步骤4：单击“样式”分组中的“套用表格格式”下拉按钮，在下拉列表中选择“表样式浅色11”，在弹出的“套用表格式”对话框中选中“表包含标题”复选框，然后单击“确定”按钮。
步骤5：单击左上角的行和列交界处，选中整个工作表，在选中区域单击鼠标右键，在弹出的菜单中单击“设置单元格格式”命令.
步骤6：在弹出的“设置单元格格式”对话框中选择“保护”选项卡，取消“锁定”复选框，单击“确定”按钮。
步骤7：选择“完成情况”和“报告奖金”两列数据，在选中区域单击右键，单击“设置单元格格式”命令，将“保护”|“锁定”复选框勾选上。
步骤8：单击“审阅”选项卡，选择“保护”分组中的“保护工作表”，在弹出的“保护工作表”对话框中不修改默认选项，直接单击“确定”按钮，完成工作表保护。
8．【解析】步骤1：打开工作簿“Excel素材2.xlsx”，然后在工作表“Sheet1”上单击右键，在弹出的菜单中选择“移动或复制”命令.
步骤2：在弹出的“移动和复制工作表”对话框中选中“工作簿”为“Excel.xlsx”，选择“下列选定工作表之前”为“移至最后”，选中“建立副本”复选框，单击“确定”按钮。
步骤3：在“Excel.xlsx”工作表中“Sheet1”工作表名上单击鼠标右键，在弹出的菜单中单击“重命名”命令，然后输入“员工个人情况统计”并按回车键。
步骤4：在“员工个人情况统计”工作表上单击鼠标右键，在弹出的“工作表标签颜色”菜单中选择中标准色“紫色”。
9．【解析】步骤1：在B3单元格中输入带函数公式“=COUNTIF(表1[责任人],员工个人情况统计!$A3)”。
步骤2：在C3单元格中输入“=COUNTIFS(表1[责任人],员工个人情况统计!$A3,表1[报告修改次数],0)”。
步骤3：在D3单元格中输入“=COUNTIFS(表1[责任人],员工个人情况统计!$A3,表1[报告修改次数],1)”。
步骤4：在E3单元格中输入“=COUNTIFS(表1[责任人],员工个人情况统计!$A3,表1[报告修改次数],2)”。
步骤5：在F3单元格中输入“=COUNTIFS(表1[责任人],员工个人情况统计!$A3,表1[报告修改次数],3)”。
步骤6：在G3单元格中输入“=COUNTIFS(表1[责任人],员工个人情况统计!$A3,表1[报告修改次数],4)”。
步骤7：在H3单元格中输入“=SUMIF(表1[责任人],员工个人情况统计!$A3,表1[报告奖金])”。
步骤8：然后选中B3单元格至H3单元格，按住H3单元格右下角的填充柄，拖至H7单元格，完成B3单元格到H7单元格所有公式的填充。
步骤9：在B8单元格中输入“=SUM(B3:B7)”然后按住B8单元格右下角的填充柄，拖至H8单元格，完成合计填充。
10．【解析】步骤1：选中C2单元格至G2单元格，然后按住<Ctrl>键，再选中“C8:G8”单元格。
步骤2：单击“插入”|“图表”分组中的“饼图”下拉按钮，在下拉列表中选择“三维饼图”菜单中的“三维饼图”命令。
步骤3：单击“图表工具”|“设计”选项卡中“数据”分组中的“选择数据”按钮，在弹出的“选择数据源”对话框中单击“切换行/列”按钮，单击“确定”按钮。
步骤4：在饼图上单击鼠标右键，在弹出的菜单中单击“添加数据标签”命令.这个时候会在饼图内显示数值，在该数值上单击鼠标右键，在弹出的快捷菜单中选择“设置数据标签格式”。
步骤5：在弹出的“设置数据标签格式”窗口中单击“标签选项”选项卡.
步骤6：将“标签包括”分组中的“值”前面的复选框取消勾选，选中“百分比”前复选框。
步骤7：单击“数字”选项卡，在“类别”选择“百分比”项，单击“关闭”按钮。
步骤8：移动图表至数据区域下方，保存并关闭“Excel.xlsx”文件。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
