巧用IF和vlookup函数及其嵌套实现员工工资管理.doc

巧用IF和vlookup函数及其嵌套实现员工工资管理摘要：Excel电子表格处理软件之所以成为数据管理分析的首选软件，是因为Excel具有丰富的公式和函数库，可以实现公式和函数的自动填充。本文以员工工资管理为例，主要介绍了Excel中的高级应用，包括IF函数、VLOOKUP函数及其嵌套的使用方法。关键词：Excel；IF函数；Vlookup函数；嵌套；工资管理Skillfully IF and vlookup function and its nested realize employee wages management 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 management1 引言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函数：返回日期的年份值，返回值是一个19009999范围内的数字。格式：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=90,优秀,IF(B3=80,良好,IF(B3=70,中等,IF(B3=60,及格,不及格)”。再利用VLOOKUP函数及嵌套查找出“工资汇总”表中的姓名、部门、基本工资、奖金、社会保险等。将光标定位到B3单元格，根据“员工信息”表和“工资汇总”表中的“员工编号”利用VLOOKUP函数查找出员工的姓名及所在部门。在B3单元格中输“=VLOOKUP(A3,员工信息!A2:H13,2,FALSE)”，在C3单元格中输入“=VLOOKUP(A3,员工信息!A2:H13,4,FALSE)”按Enter键确认，再利用填充柄完成其他员工的数据。“基本工资”和“社会保险”两项数据，在“工资明细”表中已经计算出来了，现只需通过两个表共有的“员工编号”，运用查找函数VLOOKUP即可完成这两列数据的计算。将光标定位到D3单元格，在此单元格中输入“=VLOOKUP(A3,工资明细!A2:K13,6,FALSE)”，按Enter键进行确认并通过填充柄即可完成“基本工资”的计算。接着计算“社会保险”，方法与计算“基本工资”类似，只需在F3中输入“=VLOOKUP(A3,工资明细!A2:K13,11,FALSE)”，再按Enter键进行确认并通过填充柄即可完成“社会保险”的计算。“奖金”的计算可通过利用VLOOKUP函数嵌套来实现，具体来说就是先根据“员工考核成绩”表中员工的“考核等级”，再以“考核等级”到“考核等级与奖金”表中查找对应的奖金。将光标定位到E3单元格，在此单元格中输入“=VLOOKUP(VLOOKUP(A3,员工考核成绩!$A$1:$C$13,3,FALSE),考核等级与奖金!$B$2:$C$7,2,FALSE)”，按Enter键进行确认并通过填充柄即可完成“奖金”的计算。“应发工资”=“基本工资”+“奖金”-“社会保险”。将光标定位到G3单元格，在此单元格中输入“=D3+E3-F3”，按Enter键进行确认并通过填充柄即可完成“应发工资”的计算。3.4 计算“工资汇总”表中的“个人所得税”利用IF函数及嵌套，可计算“个人所得税”。但“个人所得税”必须根据“计税工资”来求。3.4.1 计算 “计税工资”根据图1中的“个人所得税税率表”，计税工资=应发工资-2000并且个人所得税起征金额为2000元，因此计算“计税工资”的方法：将光标定位到H3单元格，并输入“=IF(G360000,H3*35%-6375,IF(H340000,H3*30%-3375,IF(H320000,H3*25%-1375,IF(H35000,H3*20%-375,IF(H32000,H3*15%-125,IF(H3500,H3*10%-25,IF(H30,H3*5%,0),2)”（ROUND函数表示对求出的数值进行四舍五入，保留2位小数）。注意：因为IF函数最多允许嵌套7层，但税率一共有9级，所以使用IF函数计算“个人所得税”时只能计算到第7级，如何解决后面剩下的2级呢？我们还可以使用VLOOKUP函数的模糊匹配可解决，但需要注意的是，使用VLOOKUP函数进行模糊匹配时，table_array参数的第一列数据必须按升序进行排序，否则VLOOKUP函数不能返回正确的数值。方法二：在“个人所得税”数据列后插入一列，将光标定位到J3单元格，插入VLOOKUP函数，输入“=H3*VLOOKUP(H3，个人所得税税率！$E$11:$G$20,2,TRUE)/100-VLOOKUP(H3，个人所得税税率!$E$11:$G$20,3,TRUE)”,按Enter键进行确认并通过填充柄即可完成“个人所得税”的计算。3.5 计算“工资汇总”表中的“实发工资”“实发工资”的计算方法为用应发工资减去个人所得税，将光标定位到J3单元格，在此单元格中输入“=G3-I3”，按Enter键进行确认并通过填充柄即可完成“实发工资”的计算。4总结员工的工资管理是一项常规工作，非常繁琐且容易出错。本文