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

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