视频解析
真题练习18-Excel电子表格_哔哩哔哩_bilibili
题库下载
题目要求
每年年终，太平洋公司都会给在职员工发放年终奖金，公司会计小任负责计算工资奖金的个人所得税并为每位员工制作工资条。按照下列要求完成工资奖金的计算以及工资条的制作：
1．在考生文件夹下，将“Excel素材.xlsx”文件另存为“Excel.xlsx”（“.xlsx”为扩展名），后续操作均基于此文件，否则不得分。
2．在最左侧插入一个空白工作表，重命名为“员工基础档案”，并将该工作表标签颜色设为标准红色。
3．将以分隔符分隔的文本文件“员工档案.csv”自A1单元格开始导入到工作表“员工基础档案”中。将第1列数据从左到右依次分成“工号”和“姓名”两列显示；将工资列的数字格式设为不带货币符号的会计专用、适当调整行高列宽；最后创建一个名为“档案”、包含数据区域A1:N102、包含标题的表，同时删除外部链接。
4．在工作表“员工基础档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”，出生日期“xxxx年xx月xx日”，每位员工截止2015年9月30日的年龄、工龄工资、基本月工资。其中：
①身份证号的倒数第2位用于判断性别，奇数为男性，偶数为女性；
②身份证号的第7~14位代表出生年月日；
③年龄需要按周岁计算，满1年才计1岁，每月按30天、一年按360天计算；
④工龄工资的计算方法：本公司工龄达到或超过30年的每满一年每月增加50元，不足10年的每满一年每月增加20元、工龄不满1年的没有工龄工资，其他为每满一年每月增加30元；
⑤基本月工资=签约月工资+月工龄工资。
5．参照工作表“员工基础档案”中的信息，在工作表“年终奖金”中输入与工号对应的员工姓名、部门、月基本工资；按照年基本工资总额的15%计算每个员工的年终应发奖金。
6．在工作表“年终奖金”中，根据工作表“个人所得税税率”中的对应关系计算每个员工年终奖金应交的个人所得税、实发奖金，并填入G列和H列。年终奖金目前的计税方法是：
①年终奖金的月应税所得额=全部年终奖金÷12；
②根据步骤①计算得出的月应税所得额在个人所得税税率表中找到对应的税率；
③年终奖金应交个税=全部年终奖金*月应税所得额的对应税率-对应速算扣除数；
④实发奖金=应发奖金-应交个税。
7．根据工作表“年终奖金”中的数据，在“12月工资表”中依次输入每个员工的“应发年终奖金”、“奖金个税”，并计算员工的“实发工资奖金”总额。（实发工资奖金=应发工资奖金合计-扣除社保-工资个税-奖金个税）
8．基于工作表“12月工资表”中的数据，从工作表“工资条”的A2单元格开始依次为每位员工生成样例所示的工资条，要求每张工资条占用两行、内外均加框线，第1行为工号、姓名、部门等列标题，第2行为相应工资奖金及个税金额，两张工资条之间空一行以便剪裁、该空行行高统一设为40默认单位，自动调整列宽到最合适大小，字号不得小于10磅。
9．调整工作表“工资条”的页面布局以备打印：纸张方向为横向，缩减打印输出使得所有列只占一个页面宽（但不得改变页边距），水平居中打印在纸上。
文字解析
1．【解析】打开考生文件夹下“Excel素材.xlsx”文件，在“文件”选项卡下单击“另存为”按钮，打开“另存为”对话框，在“文件名”中输入“Excel.xlsx”（注意：查看本机的文件扩展名是否被隐藏，从而决定是否加后缀名“.xlsx”），将保存的路径设置为考生文件夹，单击“保存”按钮。
2．【解析】（1）右键单击“年终奖金”工作表标签，在弹出的快捷菜单中选择“插入”命令，最左侧出现“Sheet1”工作表。
（2）右键单击“Sheet1”工作表标签，在弹出的快捷菜单中选择“重命名”，输入文字“员工基础档案”，即可将工作表名称改为“员工基础档案”。
（3）右键单击“员工基础档案”工作表标签，将鼠标置于“工作表标签颜色”命令上，在弹出的二级菜单中选择标准色下的“红色”即可。
3．【解析】（1）选中“A1”单元格，单击“数据”菜单栏下的“自文本”图标，在弹出的“导入文本文件”对话框中，找到“员工档案.csv”文件，单击“导入”按钮。
在打开的“文本导入向导-第1步，共3步”对话框中，在“文件原始格式”后的下拉框中选择“54936：简体中文（GB18030）”，单击“下一步”按钮，进入“文本导入向导-第2步，共3步”对话框，分隔符号选择“逗号”，单击“下一步”按钮，进入“文本导入向导-第3步，共3步”对话框，在数据预览下，选中“身份证号”列，在“列数据格式”下选择“文本”单选按钮，单击“完成”按钮，弹出“导入数据”对话框，选中“现有工作表”按钮，单击“确定”按钮即可。
（2）单击选中B列，然后单击右键，在弹出的菜单中，单击“插入”命令，插入一列空白列；选中A列所有单元格，单击“数据”菜单栏下的“数据工具”项中的“分列”图标，弹出“文本分列向导-第1步，共3步”对话框。
在“请选择最合适的文件类型”下选择“固定宽度”单选按钮，单击“下一步”进入“文本分列向导-第2步，共3步”对话框，在数据预览下，在“TPY001”和 “刀白凤”之间左键单击鼠标，出现一个向上的箭头，单击“下一步”进入“文本分列向导-第3步，共3步”对话框，单击“完成”按钮。在弹出的对话框中单击“确定”按钮。
双击A1单元格，选中“姓”字，按<Ctrl+X>键，双击B1单元格，在“名”字前按<Ctrl+V>键。
（3）选中L到N列，右键单击“设置单元格格式”项，在弹出的“设置单元格格式”对话框中，选择“数字”选项卡，单击“分类”中的“会计专用”项，“小数位数”设为0，“货币符号（国家/地区）”设为“无”。适当的调整行高和列宽。
（4）单击“数据”选项卡下“连接”分组中的“连接”按钮，在弹出的“工作簿连接”对话框中。
选中“员工档案”，单击“删除”按钮，然后在弹出的对话框中单击“确定”按钮，最后单击“关闭”按钮。
（5）按<Ctrl+A>组合键，选中数据区域（A1:N102），单击“开始”|“样式”分组中的“套用表格样式”下拉按钮，在下拉列表中选中一种样式（例如：表样式浅色10）；
在弹出的“套用表格式”对话框中选中“表包含标题”复选框，单击“确定”按钮；在“设计”|“属性”分组中，修改表名称为“档案”。
4．【解析】（1）在“员工基础档案”工作表中，选中F2单元格，选择“公式”菜单栏中的“逻辑”选项下的“IF”函数，弹出“函数参数”对话框，显示“IF”函数参数的设置界面。
然后点击左上角“IF”的白色框中的下拉三角，选择“其他函数”，在弹出的“插入函数”对话框中选择类别“数学与三角函数”，选择函数：“MOD”，单击“确定”按钮。
显示“MOD”函数参数的设置界面。点击左上角“MOD”白色框中的下拉三角，选择“其他函数”，在弹出的“插入函数”对话框中选择类别“全部”，选择函数：“MID”，单击“确定”按钮。
在弹出的“MID”函数参数对话框中，将参数Text设置为“E2”单元格，参数start_num设置为17，参数num_chars设置为1。
点击上方公式中的“MOD”，切换到“MOD”函数参数的设置界面，设置参数divisor为2。
点击上方公式中的“IF”，切换到“IF”函数参数的设置界面，设置参数value_if_true为“男”，参数value_if_false为“女”，单击“确定”按钮。选中“F2”单元格，按住右下角的十字光标，向下拖至F102单元格后松开鼠标即可。
（2）选中G2单元格，在编辑栏中输入“=MID(E2,7,4)&"年"&MID(E2,11,2)&"月"&MID(E2,13,2)&"日"”，按<Enter>键。拖动智能填充句柄，将G2单元格中的公式复制到G2:G102单元格区域中，从而获取出其他人员的出生年月日。
（3）首先选中H2单元格，然后输入带函数公式“=DATEDIF(TEXT(MID(E2,7,8),"#-00-00"),"2015-09-30","Y")”，按<Enter>键。拖动智能填充句柄，将H2单元格中的公式复制到H3到H102单元格区域中，从而获取出其他人员的年龄。
（4）首先选中M2单元格，然后输入带函数公式“=IF(K2>=30,K2*50,IF(K2>=10,K2*30,IF(K2>0,K2*20,0)))”，按<Enter>键。拖动智能填充句柄，将M2单元格中的公式复制到M3到M102单元格区域中，从而获取出其他人员的月工龄工资。
（5）选中N2单元格，在编辑栏中输入“=SUM(L2:M2)”，按<Enter>键。利用自动填充功能，将N2单元格中的公式复制到N3到N102单元格区域中，从而获取出其他人员的基本月工资。
5．【解析】（1）在“年终奖金”工作表中，选中B4单元格，选择“公式”选项卡下“函数库”组中的“查找与引用”下拉框中的VLOOKUP函数，在弹出的“函数参数”对话框中，将参数lookup_value设置为“A4”单元格，参数table_array设置为工作表“员工基础档案”中的A列和B列，参数col_index_num设置为2，即引用区域单元格的第2列。
函数公式栏中显示“=VLOOKUP(A4,员工基础档案!A:B,2)”。按回车键即可。利用自动填充功能，将B4单元格中的公式复制到B5到B71单元格区域中。
同理，在“年终奖金”工作表中，选中C4单元格，选择“公式”选项卡下“函数库”组中的“查找与引用”下拉框中的VLOOKUP函数，在弹出的“函数参数”对话框中，将参数lookup_value设置为“A4”单元格，参数table_array设置为工作表“员工基础档案”中的A列和C列，参数col_index_num设置为3，即引用区域单元格的第3列。
函数公式栏中显示“=VLOOKUP(A4,员工基础档案!A:C,3)”。按回车键即可。利用自动填充功能，将C4单元格中的公式复制到C5到C71单元格区域中。
在“年终奖金”工作表中，选中D4单元格，选择“公式”选项卡下“函数库”组中的“查找与引用”下拉框中的VLOOKUP函数，在弹出的“函数参数”对话框中，将参数lookup_value设置为“A4”单元格，参数table_array设置为工作表“员工基础档案”中的A列和M列，参数col_index_num设置为14，即引用区域单元格的第14列。
函数公式栏中显示“=VLOOKUP(A4,员工基础档案!A:N,14)”。按<Enter>键即可。
利用自动填充功能，将D4单元格中的公式复制到D5到D71单元格区域中。
（2）在“年终奖金”工作表中，选中E4单元格，输入“=D4*12*15%”，按<enter>键。利用自动填充功能，将E4单元格中的公式复制到E5到E71单元格区域中，从而获取出其他人员的基本月工资。
6．【解析】（1）选中F4单元格，在公式编辑框中输入“=E4/12”，按回车键即可。利用自动填充功能，将F1中的公式，复制到F5到F71单元格区域中。
（2）计算编号为“TPY001”员工月应税所得额的税率的函数公式为“IF(F4>80000,45%,IF(F4>55000, 35%,IF(F4>35000,30%,IF(F4>9000,25%,IF(F4>4500,20%,IF(F4>1500,10%,3%))))))”。
（3）首先选中G4单元格，然后在公式编辑框中输入带函数公式“=IF(F4>80000, 45%,IF(F4>55000, 35%,IF(F4>35000,30%,IF(F4>9000,25%,IF(F4>4500,20%,IF(F4>1500,10%,3%))))))*E4-VLOOKUP(IF(F4>80000,45%,IF(F4>55000,35%,IF(F4>35000,30%,IF(F4>9000,25%,IF(F4>4500,20%,IF(F4>1500,10%,IF(F4>0,3%,0))))))),个人所得税税率!$D$4:$E$11,2,0)”，按回车键即可。
利用自动填充功能，将G4中的公式，复制到G5到G71单元格区域中。
（4）选中H4单元格，在公式编辑框中输入“=E4-G4”，按回车键即可。利用自动填充功能，将H4中的公式，复制到H5到H71单元格区域中。
7．【解析】（1）根据工作表“年终奖金”中的数据，在“12月工资表”工作表中依次输入每个员工的“应发年终奖金”、“奖金个税”。
（2）选中E4单元格，在公式编辑框中输入“=VLOOKUP(A4,年终奖金!$A$3:$H$71,5,0)”，然后按<Enter>键即可。双击E4单元格下的控制柄即可填充本列剩余的应发年终奖金。
（3）选中L4单元格，在公式编辑框中输入“=VLOOKUP(A4,年终奖金!$A$3:$H$71,7,0)”，然后按<Enter>键即可。双击L4单元格下的控制柄即可填充本列剩余的应发年终奖金。
（4）选中M4单元格，在公式编辑框中输入“=H4-I4-K4-L4”，按回车键即可。
利用自动填充功能，将M4中的公式，复制到M5到M71单元格区域中。
8．【解析】本题考核制作工资条方法，方法有多种，但较为方便的还是利用函数来完成，这里我们就介绍利用MOD函数与ROW函数生成循环自然数序列来生成工资条。
（1）填充数据。
步骤1：在“工资条”表中的A2单元格中输入公式“
=IF(MOD(ROW()-1,3),OFFSET('12月工资表'!$A$3,(MOD(ROW()-2,3)>0)*ROUND(ROW()/3,0),COLUMN(A3)-1),"")”。
步骤2：向右拖动A2单元格的填充柄直至M2单元格，这时就完成了标题栏设置。
步骤3：选中A2至M2列，然后往下拖动填充柄，直至最后一条数据完成（本题中我们需要拖动到M204单元格），此时就完成了所有工资条的生成。
（2）参照工资条样例设置工资条格式
步骤1：选中所有数据，然后单击“开始”选项中“编辑”分组中的“排序和筛选”按钮，在下拉菜单中单击“筛选”命令。
步骤2：单击A2单元格中的筛选按钮，在下拉菜单中，取消全选，然后选中“空白”选项，此时筛选出了所有空白行。
步骤3：选中所有空白行，单击“开始”选项卡下“单元格”分组中“格式”按钮下的“行高”命令，在弹出的“行高”对话框中，将行高设置为“40”，单击“确定”按钮。
步骤4：单击“开始”选项卡下“字体”分组中的“边框”下拉按钮中的“无框线”命令，并将字号设置为不小于10号，这时就完成了所有空行格式设置。
步骤5：单击A2单元格中的筛选按钮，在下拉菜单中，选中“员工编号”选项，并取消“空白”选项，此时筛选出了所有标题行。
步骤6：选中所有标题行，在“开始”选项卡下“字体”分组中设置字号不小于10磅。单击“对齐方式”分组中“居中”
对齐按钮。
步骤7：单击A2单元格中的筛选按钮，在下拉菜单中，选中“全选”选项，并取消“员工编号”和“空白”选项，此时筛选出了所有工资数据。
步骤8：单击“开始”选项卡下“对齐方式”分组中“居中”
对齐按钮。
步骤9：选中D至M列中的所有金额单元格，单击“开始”选项卡下“对齐方式”分组中“文本右对齐”
对齐按钮。
步骤10：单击“开始”选项卡下“数字”对话框启动器，选中分栏中的“数值”，并设置2位小数位，选中“使用千位分隔符”复选框，然后单击“确定”按钮。
步骤11：单击A2单元格中的筛选按钮，在下拉菜单中，选中“全选”选项，并取消“空白”选项，此时筛选出了所有标题行和数据。
步骤12：全选数据，单击“开始”选项卡下“字体”分组中的“边框”下拉按钮中的“所有框线”命令；
步骤13：单击取消“开始”选项卡下“编辑”分组中“排序和筛选”按钮中的“筛选”命令；
步骤14：选中 第4行，单击格式刷按钮，然后用格式刷刷第一行。
9．【解析】（1）在“页面布局”选项卡下的“页面设置”项，打开“打印区域”按钮的下拉框，单击“设置打印区域”，在“工资条”工作表中，选择A1到M204单元格区域。
（2）打开“页边距”下的“自定义边距”，弹出“页面设置”对话框，在“页面”选项下，方向选择“横向”，缩放选择“调整为”1页宽，页高设置为空。在“页边距”选项卡下，居中方式设置为“水平”，单击“确定”按钮。
扫一扫
2303
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
