Excel题目和解析

Excel题目和解析

【2016年9月Excel新增操作题1】

每年年终，太平洋公司都会给在职员工发放年终奖金，公司会计小任负责计算工资奖金的个人所得税并为每位员工制作工资条。按照下列要求完成工资奖金的计算以及工资条的制作：

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

2.在最左侧插入一个空白工作表，重命名为“员工基础档案”，并将该工作表标签颜色设为标准红色。

3.将以分隔符分隔的文本文件“员工档案.csv”自A1单元格开始导入到工作表“员工基础档案”中。将第1列数据从左到右依次分成“工号”和“姓名”两列显示；将工资列的数字格式设为不带货币符号的会计专用、适当调整行高列宽；最后创建一个名为“档案”、包含数据区域A1:N102、包含标题的表，同时删除外部链接。

4.在工作表“员工基础档案”中，利用公式及函数依次输入每个学生的性别“男”或“女”，出生日期“××××年××月××日”，每位员工截止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.打开考生文件夹下的“素材.xlsx”文件-“文件”-“另存为”-“Excel.xlsx”-“保存”。

2.右击“年终奖金”工作表标签-插入-工作表-确定-把Sheet1改名为“员工基础档案”-右击“员工基础档案”标签-工作表标签颜色-红色。

3.光标定位到“员工基础档案”工作的的A1单元格-数据-获取外部数据-自文本-找到“员工档案.csv”文件-导入-文件原格式-框中选择“936简体中文（GB2312）”-下一步-分隔符号-逗号-下一步-选择“身份证号”列-列数据格式-文本-完成-确定-右击B列-插入-选定A列-数据-分列-固定列宽-下一步-在第一个学号和第一个姓名之间单击-下一步-完成-修改A列和B列的名称-选定表格后面表示工资的三个列-右击-设置单元格格式-数字-会计专用-货币符号-无-确定-双击各列标之间的分隔线调整列宽-选定所有行-右击-行高-16-确定-选定数据区域A1:N102-插入-表格-确定是-表名称-档案

4.光标定位到F2单元格-输入公式“=IF(MOD(MID([@身份证号],17,1),2)=0,"女","男")”-回车。

光标定位到G2单元格-输入公式“=MID([@身份证号],7,4)&"年"&MID([@身份证号],11,2)&"月"&MID([@身份证号],13,2)&"日"”-回车。

光标定位到H2单元格-输入公式“=ROUNDDOWN(DAYS360([@出生日期],DATE(2015,9,30))/360,0)”-回车。

光标定位到M2单元格-输入公式“=IF([@工龄]>=30,[@工龄]*50,IF([@工龄]>=10,[@工龄]*30,IF([@工龄]>=1,[@工龄]*20,[@工龄]*0)))”-回车。

光标定位到N2单元格-输入公式“=[@签约月工资]+[@月工龄工资]”-回车。

5.选定“年终奖金”工作表-光标定位到B4单元格-输入公式“=VLOOKUP($A4,员工基础档案!$A$2:$N$102,2,0)”-利用填充柄完成所有姓名-光标定位到C4单元格-输入公式“=VLOOKUP($A4,员工基础档案!$A$2:$N$102,3,0)”-利用填充柄完成所有部门-光标定位到D4单

元格-输入公式“=VLOOKUP($A4,员工基础档案!$A$2:$N$102,14,0)”-利用填充柄完成所有月基本工资-光标定位到E4单元格-输入公式“=D4*12*0.15”-利用填充柄完成所有年终应发奖金。

6.光标定位到F4单元格-输入公式“=D4/12”-利用填充柄完成所有月应税所得额。

光标定位到G4单元格-输入公式“=IF(F4<=1500,E4*0.03,IF(F4<=4500,E4*0.1-105,IF(F4<=9000,E4*0.2-555,IF(F4<=35000,E4*0.2 5-1005,IF(F4<=55000,E4*0.3-2755,IF(F4<=80000,E4*0.35-5505,E4*0.45-13505))))))”-利用填充柄完成所有月应交个税。

光标定位到G4单元格-输入公式“=E4-G4”-利用填充柄完成所有实发奖金。

7.选定“12月工资表”工作表-光标定位到E4单元格-输入公式“=VLOOKUP(A4,年终奖金!A4:H71,5,0)”-利用填充柄完成所有应发年终奖金-光标定位到L4单元格-输入公式“=VLOOKUP(A4,年终奖金!A4:H71,7,0)”-利用填充柄完成所有奖金个税-光标定位到L4单元格-输入