Excel高级应用—企业工资管理.ppt

计算机应用基础,主编：许晞 副主编：刘艳丽 曾煌兴 郑杰 秦高德,第9章 Excel高级应用企业工资管理,2,2019/7/21,学习目标,熟练掌握YEAR函数和TODAY函数的使用方法 掌握VLOOKUP函数的嵌套使用方法 掌握SUMIF的使用方法 掌握利用IF函数计算个人所得税 能够利用VLOOKUP函数计算个人所得税,第9章 Excel高级应用企业工资管理,3,2019/7/21,目 录,9.1 企业工资管理案例分析 9.2 实现方法 9.3 案例总结 9.4 课后练习,第9章 Excel高级应用企业工资管理,4,2019/7/21,9.1 饮料店销售案例分析,任务的提出 小李是创艺公司的会计，她希望能用Excel对公司员工的工资进行管理、统计。公司员工的工资包含两部分：基本工资和奖金。按照规定公司员工每月需缴纳“社会保险”，每月还要缴纳个人所得税。 解决方案 显然，员工“基本工资”和“奖金”的计算可以用查找函数VLOOKUP来解决。至于个人所得税，可以用IF函数来计算，也可以用VLOOKUP函数计算。,返回目录,第9章 Excel高级应用企业工资管理,5,2019/7/21,9.2 实现方法,9.2.1 计算“员工信息表”中的“工龄” 9.2.2 计算“基本工资及社会保险”工作表中的内容 9.2.3 计算“工资总表”工作表中的“应发工资” 9.2.4 用IF函数计算“个人所得税” 9.2.5 用SUMIF函数进行工资统计 9.2.6 让“个人所得税”的计算更简单,返回目录,第9章 Excel高级应用企业工资管理,6,2019/7/21,9.2.1 计算“员工信息表”中的“工龄”,用YEAR函数和TODAY函数计算“员工信息”工作 表中员工的工龄 ，算法如下： 工龄=当前年份参加工作年份 = YEAR(TODAY()-YEAR(工作日期),返回,第9章 Excel高级应用企业工资管理,7,2019/7/21,9.2.2 计算“基本工资及社会保险”工作表中的各项内容,1. 用VLOOKUP函数，查找出“基本工资及社会保险”工作表中的“工龄” （1）在“员工信息”工作表中定义“员工信息”数据区 （2）在“基本工资及社会保险”工作表中根据员工编号，用VLOOKUP函数在“员工信息”数据区中查找员工编号对应的工龄,第9章 Excel高级应用企业工资管理,8,2019/7/21,9.2.2 计算“基本工资及社会保险”工作表中的各项内容,2. 用IF函数嵌套，计算“基本工资及社会保险”工作表中的“工龄工资”,第9章 Excel高级应用企业工资管理,9,2019/7/21,9.2.2 计算“基本工资及社会保险”工作表中的各项内容,3. 用VLOOKUP函数的嵌套及IF函数计算“基本工资及社会保险”工作表中的职务工资和学历工资 （1）先在“基本工资及社会保险”工作表的“职务工资”列，用VLOOKUP函数查找出相应的“职务”,第9章 Excel高级应用企业工资管理,10,2019/7/21,9.2.2 计算“基本工资及社会保险”工作表中的各项内容,（2）再根据查找到的“职务”，用VLOOKUP函数的嵌套，在“工资、奖金对照表”工作表中定义的“职务工资”数据区，查找到该职务对应的“职务工资”。,第9章 Excel高级应用企业工资管理,11,2019/7/21,9.2.2 计算“基本工资及社会保险”工作表中的各项内容,4. 计算“基本工资及社会保险”工作表中的“基本工资”和“社会保险 （1）计算员工“基本工资”。 基本工资=工龄工资+职务工资+学历工资。 （2）计算出员工需缴纳的“社会保险” 社会保险=养老保险+医疗保险+失业保险+住房公积金,返回,第9章 Excel高级应用企业工资管理,12,2019/7/21,9.2.3 计算“工资总表”工作表中的“应发工资”,1. 用VLOOKUP函数填写出“工资总表”工作表中的“姓名”、“部门”、“基本工资”和“社会保险” 2. 用嵌套的VLOOKUP函数及IF函数计算“工资总表”工作表中的“应发工资”,返回,第9章 Excel高级应用企业工资管理,13,2019/7/21,9.2.4 用IF函数计算“个人所得税”,1“个人所得税”算法 （1）“月工资薪金所得总额”=“应发工资” （2）“应纳税所得额”=“应纳税工资额” =“应发工资”-1600 （3）“个人所得税”的征税方法分为9个等级，如下表所示,第9章 Excel高级应用企业工资管理,14,2019/7/21,9.2.4 用IF函数计算“个人所得税”,第9章 Excel高级应用企业工资管理,15,2019/7/21,9.2.4 用IF函数计算“个人所得税”,（4）采用“速算扣除数法“计算“个人所得税” 为了方便“个人所得税”的计算，国家税务部门给出了用“速算扣除数法”来计算“个人所得税”的方法，计算公式如下： 应缴纳“个人所得税” “应纳税所得额”适用“税率”“速算扣除数”,第9章 Excel高级应用企业工资管理,16,2019/7/21,9.2.4 用IF函数计算“个人所得税”,2. 用IF函数计算“应纳税工资额”,第9章 Excel高级应用企业工资管理,17,2019/7/21,9.2.4 用IF函数计算“个人所得税”,3. 在“工资总表”中利用IF函数计算出“个人所得税”,第9章 Excel高级应用企业工资管理,18,2019/7/21,9.2.4 用IF函数计算“个人所得税”,4计算出“工资总表”工作表中的“实发工资” 实发工资=应发工资个人所得税。 5把“工资总表” 工作表中人民币的 货币单位设置为 “RMB”（人民币）,返回,第9章 Excel高级应用企业工资管理,19,2019/7/21,9.2.5 用SUMIF函数进行工资统计,1. SUMIF函数的用法 功能：根据指定条件对若干单元格求和。 语法：SUMIF(Range,Criteria,Sum_range) 共有3个参数，其意义如下： Range 为用于条件判断的单元格区域。 Criteria 为确定哪些单元格将被相加求和的条件。 Sum_range 是需要求和的实际单元格。,第9章 Excel高级应用企业工资管理,20,2019/7/21,9.2.5 用SUMIF函数进行工资统计,2用SUMIF函数统计“工资统计”工作表中“财务部”的“基本工资总计”,返回,第9章 Excel高级应用企业工资管理,21,2019/7/21,9.2.6 让“个人所得税”的计算更简单,1如何用VLOOKUP函数进行“模糊查找”？ 在用VLOOKUP函数进行“模糊查找”（range_lookup为TRUE）时，如果在区域table_array的第 1 列中找不到lookup_value，则返回小于等于lookup_value的最大数值。 注意： 此时table_array第 1 列的数值必须按升序排列，否则函数VLOOKUP不能返回正确的数值。,第9章 Excel高级应用企业工资管理,22,2019/7/21,9.2.6 让“个人所得税”的计算更简单,2. 用VLOOKUP函数计算“个人所得税” 按照公式：“个人所得税”“应纳税所得额”适用“税率”-“速算扣除数” 用VLOOKUP函数可以很容易地计算出“个人所得税”,返回,第9章 Excel高级应用企业工资管理,23,2019/7/21,9.3 案例总结,本章通过对企业工资的处理，介绍了用Excel计算个人所得税的两种方法及VLOOKUP函数的高级应用。 本章的重点内容是VLOOKUP函数的嵌套使用。在用VLOOKUP函数进行查找和引用时，有时需要用VLOOKUP函数的嵌套才能实现。 下面把大家在本章的学习过程中经常遇到的一些问题及处理方法列于下表中,第9章