excel查找包含某字段并提取出来_Excel|操作题第19套

 Excel | 操 作 题 第 19 套 题 目  

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

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、复制Excel素材，粘贴一份，重命名为Excel。不用输入扩展名，打开Excel做题即可。

2、新建一个工作表，重命名为员工基础档案，颜色改为标准色红色。并移动到最左边。

3、光标定位员工档案的第一个单元格→数据→自文本→选择员工档案→打开

语言选择936：简体中文→下一步

分隔符选择逗号→下一步

身份证需要改成文本格式→完成→确定

在A列后插入一个新列

选择A列一整列→数据→分列→分隔符为固定宽度→下一步

在工号和姓名中间单击一下会出来一条竖线，下一步直到完成

A列标题改为工号，B列改为姓名

选择后三列基本工资的数据→右击设置单元格格式→会计专用，无货币符号

适当加大行高和列宽

选择所有数据→随便套用一个表格格式→表包含标题，在弹出是否删除外部链接的提示时，点击是

光标定位表中任意一个单元格→名称框内输入→档案

4、根据题目要求，填写性别、出生日期、年龄。

①性别：身份证号的倒数第2位如果是奇数为男性，偶数为女性。

首先我们先把倒数第2位提取出来，即用mid函数提取。不会的请看链接：Excel|提取函数

mid函数参数(提取的数据，从第几位提取，提取几位)

提取倒数第2位，即提取身份证号码的第17位，即

倒数第2位=mid(身份证号码,17,1)

现在判断奇偶，用mod取余函数即可，即返回两数相除的余数

mod函数参数(被除数，除数)

例：=mod(3,2)=1  即3除以2，余1

这时可以用if函数判断，如果余数除以2=0，那么肯定是偶数，否则就是奇数。

即=if(mod(mid(身份证号码,17,1)，2)=0，“女”，“男”) 

②出生日期：

我们可以用date函数，date(年，月，日)

我们把代表年月日的数字填写到date函数中即可，代表年月日的数字可以用mid函数提取。

算完出生日期后，把日期改成长日期的数字格式。

③年龄：年龄需要按周岁计算，满1年才计1岁，到2015年9月30日为止的工龄

我们要用datedif函数，含义是：计算两个日期之间的整年数。

datedif函数参数(开始日期，结束日期，计算的类型)

计算年龄即：(出生的日期，2015-9-30，年份单位)

具体解释请看链接，datedif 函数

即：

(这道题也可以用days360函数，不过用datedif函数也是可以得到满分的)

④月工龄工资的计算：

根据题目要求：工龄达到或超过30年的每满一年每月增加50元、即1年工龄就加50元，那么有几年工龄就加多少个50元。即工龄年数*50，所以用if函数判断：

工龄>30,工龄年数*50元

工龄>10,工龄年数*30元

工龄>1，工龄年数*20元，

否则就是0元

即：

⑤基本月工资=签约月工资+月工龄工资

5、如图，填写此表的姓名，部门，等信息。直接用vlookup函数在员工基础档案表中查找即可。

vlookup函数(查找的数据，查找的范围，返回的值在范围的第几列，0)

查找的数据为员工编号：

查找的范围为表2全部

姓名在范围的第2列，即

部门在范围的第3列，即

月基本工资在范围的第14列，即

根据题意：应发奖金=年基本工资*0.15 即=月基本工资*12*0.15

6、①月应税所得额=全部年终奖金÷12

②年终奖金应交个税=全部年终奖金×月应税所得额的对应税率-对应速算扣除数

这道题可以说是很熟悉了，我们自第7套题有仔细的讲解，在这里不在多说

Excel|操作题第7套

答案为：

③实发奖金=应发奖金-应交个税。即：

7、如图，填写对应人的应付年终奖金和奖金个税。我们刚刚已经在年终奖金的表中求出来了，所以直接用vlookup函数查找对应的即可。

即根据编号，查找年终奖金表中对应的值，应发年终奖金在范围的第5列

同理：奖金个税在第7列，即：

实发工资奖金=应发工资奖金合计-扣除社保-工资个税-奖金个税，即：

8、复制12月工资表的A3:M71区域，

光标定位工资条的表，粘贴选择123值的格式。

选中D2:DM69，设置单元格格式为货币，无货币符号，2位小数

在第一个人的后面添加一列辅助列，输入1,2,3...的序号

复制这些1-68的序号，在序号下面粘贴

再粘贴一次1-68的序号

将工资表的标题行粘贴到第二次粘贴序号1-68的地方

光标定位辅助列的任意一个单元格→升序

这时候工资条就制作好了，把辅助列删除即可

然后选中表中的任意数据→查找和选择→常量

然后单击所有框线

在工资条的第一行插入一个空行

选择空白单元格→查找和选择→定位条件→空值

格式→行高→输入40

9、调整纸张方向为横向

打开页面设置对话框启动器→调整为1页高1页宽

点击页边距→勾选水平居中

最后确定，保存即可

往期链接

Excel|操作题第1套

Excel|操作题第2套

Excel|操作题第3套

Excel|操作题第4套

Excel|操作题第5套

Excel|操作题第6套

Excel|操作题第7套

Excel|操作题第8套

Excel|操作题第9套

Excel|操作题第10套

Excel|操作题第11套

Excel|操作题第12套

Excel|操作题第13套

Excel|操作题第14套

Excel|操作题第15套(上)

Excel|操作题第15套(下)

Excel|操作题第16套

Excel|操作题第17套

Excel|操作题第18套

喜欢记得来一个