巧用IF和vlookup函数及其嵌套实现员工工资管理
摘要：Excel电子表格处理软件之所以成为数据管理分析的首选软件，是因为Excel具有丰富的公式和函数库，可以实现公式和函数的自动填充。本文以员工工资管理为例，主要介绍了Excel中的高级应用，包括IF函数、VLOOKUP函数及其嵌套的使用方法。
关键词：Excel；IF函数；Vlookup函数；嵌套；工资管理
Skillfully IF and vlookup function and its nested realize employee wages management
vlookup模糊匹配Abstract: Excel software have become the first choice for data management and analysis software, because Excel software is providing with rich formulas and  functions can be achieved automatically filled. In this paper, with example of Staff wage management，Introduces advanced applications in Excel, including the application of IF functions and vlookup function and its nested their use.
Key words:Excel；IF function；vlookup function；nested；wages management
 
1 引言
Excel是当前最为流行的电子表格软件，因其提供了丰富的公式和函数库，所以我们经常使用Excel对各种数据进行处理、统计、分析和辅助决策等操作。同时因Excel软件易学易用，功能较为完备，所以广泛地应用于管理、统计财经、金融等众多领域。此外，Excel还可以把表格中的数据以图表的形式进行反映，更能形象直观地得到数据的比较结果。所以Excel广泛应用于各行各业。
在日常生活和工作中，我们经常要对数据表格中的大量数据进行计算、分析管理，比如期末考试成绩表、期末考试质量分析表、教师工资表等。针对这些实际应用，如何用简单的方法来解决这类实际问题呢？现行的通用员工工资管理软件很多，而且功能也非常强大，但专门去购买这样的软件，势必会增加单位的经济负担，而且还要派专人进行学习，才能正确使用，如果考虑到经济成本，也可以自己编制一套通用工资管理软件，但这是一般用户的技术水平胜任不了的，而且还要花很多时间去不断进行测试、修改和维护；如果直接购买，从性价比上来看，成本比较高。事实上，通过excel就可轻松简地单实现员工工资的
管理。
2 员工工资管理的案例分析
根据员工考核成绩表、考核等级与奖金表、学历工资表、职务工资表、工龄工资表和个人所得税税率表中的内容，分别计算出如图2所示的“工资明细表”和“工资汇总”表中的各项内容。
其中，员工工资由两部分组成：基本工资加奖金。其中基本工资又包括职务工资、学历工资和工龄工资，奖金按照考核等级计算。其计算依据规则如图1所示。
此外，每个员工每月还有缴纳社会保险，包括：养老保险、医疗保险、失业保险和住房公积金，其缴纳的金额按此比例进行：医疗保险=基本工资×2%，养老保险=基本工资×8%，失业保险=基本工资×0.5%，住房公积金=基本工资×8%。
另外，每个员工还需要根据自己的基本计算出每个月应缴纳的个人所得税。个人所得税缴纳的比例按图1中的“个人所得税税率”表进行计算。最后计算出实发工资。
员工工资管理中所有涉及到的表如图1所示：
图1：员工工资管理中涉及的所有表项
2.1 所用函数介绍：IF函数
格式：IF(logical_test,value_if _true,value_if_false)。
功能：执行真假值判断，根据对指定的条件进行逻辑判断的真假而返回不同的结果。其中：参数“logical_test”为条件表达式，“value_if_true”是条件表达式的值为真时的返回值，“value_if_false” 是条件表达式的值为假时的返回值。
IF函数最多可以嵌套七层，使用value_if_true和value_if_false参数可以构造复杂的检测条件。
2.2 VLOOKUP函数
格式：vlookup（lookup_value，table_array，col_index_num,range_lookup）
功能：查数据区域首列满足条件的元素，并返回数据区域当前行中指定列处的值。其中，参数“lookup_value”表示需要查的内容，即查什么；“table_array”表示查的区域，
即在那个区域进行查；“col_index_num”表示查区域中的第几列，具体说就是在区域的第几列查。“range_lookup”表示是精确查还是模糊查。如果取值为“false”表示模糊查，取值为“true”时表示精确查。我们还可以根据实际需要，可以将一个VLOOKUP函数的值作为另一个VOOKUP函数的参数，即嵌套的VLOOKUP函数。
2.3 TODAY函数和YEAR函数
TODAY函数：返回日期格式的当前日期。
YEAR函数：返回日期的年份值，返回值是一个1900~9999范围内的数字。
格式：YEAR(serial_number)
serial_number是一个日期值，其中包含要查年份的日期。
3 具体实现的方法
实现的思路：①使用TODAY函数和YEAR函数计算员工的工龄；②使用嵌套的VLOOKUP函数和IF函数计算“工资明细”表中的各项数值；③使用嵌套的VLOOKUP函数和IF函数计算“
工资汇总”表中的“应发工资”；④使用嵌套的IF函数计算出个人所得税。⑤最后计算出“实发工资”。即要完成如图2所示中的各个字段。
 
图2 员工工资管理中需完成的各个表项
3.1 计算“员工信息”表中的“工龄”
工龄就是先求出当前日期的年份，再减去参加工作的日期所属的年份，将光标定位到目标单位格后，在编辑栏中输入公式“=YEAR(TODAY())-YEAR(G3)”，接着按Enter确认，即可求出第一个员工的“工龄”，利用填充柄，自动填充求出其他员工的工龄。
3.2 计算“工资明细”表中的各项内容
3.2.1 计算“工资明细”表中的“工龄”
打开“工资明细”表，使用VLOOKUP函数查并计算出“工龄”，具体过程：先将光标定位到B3，在编辑栏中输入“=VLOOKUP(A3,员工息!A2:H13,8,FALSE)”(表示在“员工信息”表的A2：
H13数据区域，查与单元格A3中的编号对应的工龄值)，按Enter键求出第一个员工的工龄，再利用填充柄，自动填充其他员工的工龄。
3.2.2 计算“工资明细”表中的“工龄工资”
根据图1中的“工龄工资”表，使用IF函数嵌套，可计算出员工的“工龄工资”。将光标定位到C3单元格，在此单元格中输入“=IF(B3<=1,100,IF(B3<=5,
200,IF(B3<=10,300,500)))”,按Enter键确定，再利用填充柄完成。
3.2.3 计算“工资明细”表中的“职务工资”和“学历工资”
    根据图1中的“职务工资”表和“学历工资”表，使用IF函数和VLOOKUP函数的嵌套，可计算出员工的“职务工资”和“学历工资”。将光标定位到D3单元格，在此单元格中输入“=VLOOKUP(VLOOKUP(A3,员工信息!A2:H13,5,FALSE),职务工资!$A$1:$B$9,2,FALSE)”,将光标定位到E3单元格，在此单元格中输入公式“=VLOOKUP(VLOOKUP(A3,员工信息!A2:H13,6,FALSE),学历工资!$A$1:$B$6，2,FALSE)”，按Enter键确定，再利用填充柄完成所有员工的“职务工资”和“学历工资”的计算。
3.2.4 计算“工资明细”表中的“基本工资”和“养老保险”、“医疗保险”、“失业保险”、“住房公积金”、“社会保险”
基本工资=工龄工资+职务工资+学历工资。将光标定位到F3单元格，在此单元格中输入个“=SUM(C3:E3)”，按Enter键确定，再利用填充柄完成所有员工的“基本工资”。
根据前面各种保险金额缴纳的比例进行计算，将光标定位到G3、H3、I3、J3单元格依次输入“=F3*0.08”、“=F3*0.02”、“=F3*0.0005”、“=F3*0.08”。
社会保险等于养老保险、医疗保险、失业保险和住房公积金之和。将光标定位到K3单元格输入“=SUM(G3:J3)”。
3.3 计算“工资汇总”表中的“应发工资”等各项数据
首先计算出图1中的“员工考核等级”表中的“考核等级”，利用IF函数的嵌套来实现。将光标定位到“员工考核等级”表中的C3单元格，在此单元格中输入“=IF(B3>=90,"优秀",IF(B3>=80,"良好",IF(B3>=70,"中等",IF(B3>=60,"及格","不及格"))))”。再利用VLOOKU
P函数及嵌套查出“工资汇总”表中的姓名、部门、基本工资、奖金、社会保险等。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。