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

视频讲解

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

题库下载

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

题目要求

第48组

小李是东方公司的会计，为提高工作效率，同时又确保数据的准确性，她使用Excel编制了员工工资表。帮助她完成下列任务。

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

2．在“2015年8月”工作表中完成下列任务：

① 将A1单元格中的标题内容在A1:M1区域中跨列居中对齐。

② 修改“标题”样式的字体为“微软雅黑”，并将其应用于第1行的标题内容。

③ 员工工号的首字母为部门代码，根据“员工工号”列的数据，在“部门信息”工作表中查询每位员工所属部门，并填入“部门”列。

④ 在“应纳税所得额”列中填入每位员工的应纳税所得额，计算方法为：应纳税所得额=应付工资合计-扣除社保-3500（如果计算结果小于0，则应纳税所得额为0）。

⑤ 计算每位员工的应交个人所得税，计算方法为：应交个人所得税=应纳税所得额*对应税率-对应速算扣除数（对应税率和对应速算扣除数位于隐藏的工作表“工资薪金所得税率”中）。

⑥ 在“实发工资”列中，计算每位员工的实发工资，计算方法为：实发工资=应付工资合计-扣除社保-应交个人所得税。

⑦ 将“序号”列中的数值设置为“001,002…”格式，即不足3位用0占位。

⑧ 将E列到M列中的数值设置为会计专用格式，货币符号为￥，保留两位小数。

⑨ 为从第2行开始的整个数据区域中的所有单元格添加边框线。

3．复制工作表“2015年8月”置于原工作表右侧，并完成下列任务：

① 修改新复制的工作表名称为“分类汇总”。

② 使用分类汇总功能，按照部门进行分类，计算每个部门员工实发工资的平均值，部门需要按照首字读音的字母顺序升序排序，汇总结果显示在数据下方。

4．新建名称为“收入分布”的工作表，按照考生文件夹下的示例Epic01.png所示的分组标准在A1:C5单元格区域创建表格，在“人数”和“比例”列中分别按照“实发工资”计算每组的人数以及占整体的百分比（结果保留整数）。

5．在“收入分布”工作表的A6:G25单元格区域中，参照考生文件夹下的示例Epic02.png创建簇状柱形图，比较每个收入分组的人数，并进行以下设置：

① 调整柱形填充颜色为标准蓝色，边框为“白色,背景1”。

② 系列间隙宽度（分类间距）为0%。

③ 垂直轴和水平轴都不显示线条和刻度，垂直轴边界在0-140之间，刻度单位为35。

④ 将该图表另存为模板，类型为“图表模板文件”，文件名称为“分布图”，并将其保存在考生文件夹下。

⑤ 修改图表属性，以便在工作表被保护的情况下，依然可以编辑图表中的元素，但不要设置保护工作表。

6．将“收入分布”工作表的单元格区域A1:G25设置为打印区域。

文字解析

第48组

1．【解析】步骤1：在考生文件夹中双击打开“Excel.xlsx”文件。

2．【解析】①步骤1：选中A1:M1单元格区域，在选中区域中单击鼠标右键，在弹出菜单中单击“设置单元格格式”命令，打开“设置单元格格式”对话框；

步骤2：单击“对齐”选项卡，在水平对齐中选中“跨列居中”，单击“确定”按钮。

②步骤1：单击“开始”|“样式”分组中的“单元格样式”下拉按钮，在列表中的“标题”样式上单击鼠标右键，在弹出菜单中单击“修改”命令，打开“样式”对话框；

步骤2：单击“格式”按钮，打开“设置单元格格式”对话框，选中“字体”选项卡，设置字体为“微软雅黑”，单击“确定”按钮，再次单击“确定”按钮；

步骤3：选中A1:M1单元格区域，单击“开始”|“样式”分组中的“单元格样式”下拉按钮列表中“标题”。

③步骤1：打开“部门信息”工作表，选中B列，然后按<Ctrl+X>组合键剪切B列，选中A列，在选中的A列上单击鼠标右键，然后单击“插入剪切的单元格”命令，将B列粘贴到A列之前；

步骤2：选中“2015年8月”工作表中的D3单元格，单击“插入函数”按钮，打开“插入函数”对话框，在选择函数中找到并选中“VLOOKUP”函数，单击“确定”按钮，打开“函数参数”对话框；

步骤3：在第一个参数中输入截取字母函数“LEFT()”，在地址栏中单击LEFT函数，进入到LEFT函数参数对话框，在第一个参数中输入“B3”，第二个参数中输入“1”；

步骤4：在地址栏中单击VLOOKUP函数，进入到VLOOKUP函数参数对话框，此时，第一个参数已经设置好，将光标定位于第二个参数输入框中，然后选中“部门信息”工作表中的“A1:B6”单元格区域，在参数输入框中选中“A1:B6”，然后按“F4”键(给单元格区域加上绝对引用)；

步骤5：在第3个参数中输入“2”，在第4个参数中输入“0”，单击“确定”按钮；

步骤6：双击D3单元格的智能填充柄，完成其他行的部门信息填充。

④步骤1：选中K3单元格，单击“插入函数”按钮，打开“插入函数”对话框，在选择函数中找到并选中“IF”函数，单击“确定”按钮，打开“函数参数”对话框；

步骤2：在第一个参数中输入“I3-J3-3500<=0”，第二个参数中输入“0”，第三个参数中输入“I3-J3-3500”，单击“确定”按钮；

步骤3：双击K3单元格的智能填充柄，完成其他行的应纳税所得额填充。

⑤步骤1：单击“审阅”|“保护”分组中的“保护工作簿”按钮，取消选中状态；

步骤2：在工作表标签上单击鼠标右键，在弹出菜单中单击“取消隐藏”命令，打开“取消隐藏”对话框，选中取消隐藏工作表中的“工资薪金所得税率”，单击“确定”按钮；

步骤3：选中“2015年8月”工作表中的“L3”单元格，输入应交个人所得税的计算公式

“=IF(K3>80000,K3*0.45-13505,IF(K3>55000,K3*0.35-5505,IF(K3>35000,K3*0.3-2755,IF(K3>9000,K3*0.25-1005,IF(K3>4500,K3*0.2-555,IF(K3>1500,K3*0.1-105,K3*0.03))))))”，然后双击智能填充句柄完成其他行填充。

⑥选中“2015年8月”工作表中的“M3”单元格，输入“=I3-J3-L3”，然后双击智能填充句柄完成其他行填充。

⑦选中A3:A351单元格区域，单击“开始”|“数字”分组中的对话框启动器，打开“设置单元格格式”对话框，在“数字”选项卡中的“分类”中选中“自定义”，在类型中输入“000”，单击“确定”按钮。

⑧选中E3:M351单元区域，单击“开始”|“数字”分组中的“数字格式”下拉按钮，在列表中选中“会计专用”。

⑨ 选中“A2:M351”单元格区域，单击“开始”|“字体”分组中的“边框”下拉按钮，在列表中选中“所有框线”。

3．【解析】在“2015年8月”工作表标签上单击鼠标右键，然后单击“移动或复制”命令，在弹出的“移动或复制工作表”对话框中选中“建立副本”复选框，在“下列选定工作表之前”中选中“部门信息”，单击“确定”按钮。

①在复制的“2015年8月（2）”工作表上双击鼠标，将表名称改为“分类汇总”。

②步骤1：在选中“分类汇总”工作表中的任意数据区域单元格（例如A2），单击“数据”|“排序和筛选”分组中的“排序”按钮，打开“排序”对话框；

步骤2：在主要关键字中选中“部门”，在排序依据中选中“单元格值”，次序中选中“自定义序列”，在打开的“自定义序列”中的“输入序列”中输入“管理/人事/市场/行政/研发”，（注意：中间“/”都好表示换行），然后单击“添加”按钮，然后单击“确定”按钮，再次单击“确定”按钮；

步骤3：选中A2:M351单元格区域，单击“数据”|“分级显示”分组中的“分类汇总”按钮，打开“分类汇总”对话框；

步骤4：在分类字段中选中“部门”，在汇总方式中选中“平均值”，在选定汇总项中选中“实发工资”，选中“汇总结果显示在数据下方”复选框，单击“确定”按钮。

4．【解析】步骤1：单击工作表标签最后侧的“加号”按钮，新建一个工作表，在新建工作表上双击鼠标，将表名称改为“收入分布”；

步骤2：参照示“收入分组.png”例图，依次输入内容；

步骤3：选中B2单元格，单击“插入函数”按钮，打开“插入函数”对话框，在选择函数中找到并选中“COUNTIFS”函数，单击“确定”按钮，打开“函数参数”对话框，在第一个参数中输入（'2015年8月'!M3:M351），在第二个参数中输入“<3500”，单击“确定”按钮；

步骤4：选中B3单元格，单击“插入函数”按钮，打开“插入函数”对话框，在选择函数中找到并选中“COUNTIFS”函数，单击“确定”按钮，打开“函数参数”对话框,在第一个参数中输入（'2015年8月'!M3:M351），在第二个参数中输入“>=3500”，在第三个参数中输入“2015年8月”工作表中的M3:M351单元格区域（'2015年8月'!M3:M351），在第四个参数中输入"<8000"，单击“确定”按钮；

步骤5：选中B4单元格，单击“插入函数”按钮，打开“插入函数”对话框，在选择函数中找到并选中“COUNTIFS”函数，单击“确定”按钮，打开“函数参数”对话框，在第一个参数中输入（'2015年8月'!M3:M351），在第二个参数中输入“>=8000”，在第三个参数中输入“2015年8月”工作表中的M3:M351单元格区域（'2015年8月'!M3:M351），在第四个参数中输入“<13000”，单击“确定”按钮；

步骤6：选中B5单元格，单击“插入函数”按钮，打开“插入函数”对话框，在选择函数中找到并选中“COUNTIFS”函数,单击“确定”按钮，打开“函数参数”对话框，在第一个参数中输入（'2015年8月'!M3:M351），在第二个参数中输入“>=13000”，单击“确定”按钮；

步骤7：选中C2单元格，输入公式“=B2/SUM($B$2:$B$5)”，按回车键，双击C2单元格的智能填充柄，选中C2:C5单元格区域，单击“开始”|“数字”分组中的“百分比样式”按钮，如果百分比显示了小数位，那么单击“减少小数位数”按钮，直到没有小数位。

5．【解析】①步骤1：选中A1:B5单元格区域，单击“插入”|“图表”分组中个“插入柱形图或条形图”下拉按钮，在列表中选中“簇状柱形图”；

步骤2：选中图表标题，然后按删除键删除图表标题；

步骤3：拖动图表左上角到A6单元格内，缩放图表大小，使得图表右下角在G25单元格内。

②步骤1：选中柱形图数据序列，单击“图表工具”|“格式”|“形状样式”分组中的“形状填充”下拉按钮，在下拉列表中选中标准色中的“蓝色”；

步骤2：选中柱形图数据序列，单击“图表工具”|“格式”|“形状样式”分组中的“形状轮廓”下拉按钮，在下拉列表中选中“白色，背景1”；

步骤3：选中柱形图数据序列，单击“图表工具”|“格式”|“当前所选内容”分组中的“设置所选内容格式”按钮，在打开的“设置数据系列格式”窗格中，设置间隙宽度为“0%”。

③步骤1：在“图表工具”|“格式”|“当前所选内容”分组中的“图表元素”中选中“垂直（值）轴”，单击“设置所选内容格式”按钮，打开“设置坐标轴格式”窗格；

步骤2：单击“坐标轴选项”按钮，展开“刻度线”选项卡，设置主要刻度线类型为“无”，展开“坐标轴选项”，设置边界中的最小值为“0”，最大值为“140”，单位中的大为“35”，单击“填充与线条”按钮，展开“线条”选项卡，选中“无线条”按钮；

步骤3：在“图表工具”|“格式”|“当前所选内容”分组中的“图表元素”中选中“水平（类别）轴”，单击“设置所选内容格式”按钮，打开“设置坐标轴格式”窗格；

步骤4：单击“坐标轴选项”按钮，展开“刻度线”选项卡，设置主要刻度线类型为“无”，单击“填充与线条”按钮，展开“线条”选项卡，选中“无线条”按钮。

④选中图表，在图表上单击鼠标右键，然后单击“另存为模板”命令，打开“保存图表模板”对话框，在保存类型中选中“图标模板文件”，在文件名中输入“分布图”，在保存位置中找到考生文件夹，单击“保存”按钮；

⑤步骤1：选中图表，在“图表工具”|“格式”|“当前所选内容”分组中的“图表元素”中选中“图表区”，单击“设置所选内容格式”按钮，打开“设置图表区格式”窗格；

步骤2：单击“大小与属性”按钮，展开“属性”选项卡，取消“锁定”复选框的选中。

6．【解析】选中A1:G25单元格区域，单击“页面布局”|“页面设置”分组中的“打印区域”下拉按钮，在列表中单击“设置打印区域”命令。

保存并关闭Excel.xlsx文档。