《员工工资管理模型》PPT课件.pptx

1、1,员工工资管理模型,2,教师信息,教师：张晓黎 电子信箱： 办公室：学院楼B420 电话： 67703854,3,教学目标、学习重点与难点,能够以Excel为工具设计企业工资管理系统。教学目标： 特殊类型数据的输入 身份证号输入等 函数，单元格格式设置 日期时间函数 汇总分析函数：countif、sumif和frequency函数 员工工龄的计算及单元格格式设置 单元格设置对显示结果的影响 嵌套函数的使用 if函数计算福利 查找函数的深入理解：vlookup、hlookup、index和match函数 重点与难点 单元格格式设置 函数、嵌套函数的灵活使用,4,例1-员工工龄等条目的计算,算法

2、P21 工龄=当前日期年份-入企年份 =YEAR(NOW()-YEAR(D3) 注意如何利用函数面板输入函数和公式 单元格格式的设置 选中工龄列数据/开始/单元格/格式/设置单元格格式 右键/单元格格式/将日期型数据改为常规类型 将自己的身份证号作为某个员工的身份证号进行输入 各类工资的计算：公式的使用 岗位工资=基础工资*0.5 工龄工资=工龄*100 调整后总基本工资=基础工资+岗位工资+工龄工资,例2-各年龄段人数和基础工资汇总,按不同工龄段汇总人数 方法1-countif函数 对区域中符合指定条件的单元格计数。 range 必需。用于计数的单元格区域。空值和文本值将被忽略。工龄列 cr

3、iteria 必需。用于确定计数的条件。 =Countif(工资记录表! $E$3:$E$182,=5) = Countif(工资记录表! $E$3:$E$182,=10)-D2,5,例2-各年龄段人数和基础工资汇总,按不同工龄段汇总人数 方法2-frequency函数 Data_array 为一数组或对一组数值的引用 Bins_array 为间隔的数组引用，该间隔用于对 data_array 中的数值进行分组。 对于返回结果为数组的公式，必须以数组公式的形式输入。 数组公式的使用 选择区域(C2:C5) 输入公式“=FREQUENCY(工资记录表!E3:E182,B2:B5)” 使用组合键“

4、Ctrl+Shift+Enter”,6,例2-各年龄段人数和基础工资汇总,按不同工龄段对基础工资进行汇总求和 Sumif函数 对区域中符合指定条件的值求和。 range 必需。用于条件计算的单元格区域。空值和文本值将被忽略。工龄列 criteria 必需。用于确定求和的条件。 sum_range 可选。要求和的实际单元格。如果该参数被省略，将对在 range 参数中指定的单元格求和。基础工资列 =SUMIF(工资记录表!$E$3:$E$182,=5,工资记录表!$G$3:$G$182) =SUMIF(工资记录表!$E$3:$E$182,=10,工资记录表!$G$3:$G$182)-E2,7,8

5、,例3-表格格式设置,文字格式-右键/设置单元格格式 图表标题居中加粗显示 表格边框格式-右键/设置单元格格式 外边框粗，内边框细 图表标题背景淡绿色+圆点 打印输出设置-文件/打印/页面设置(或 页面布局/页面设置) 调整页边距 设置页眉页脚 每列上都有列标题：工作表/顶端标题行 打印预览：视图/页面布局,9,例4-员工福利项目的计算,计算“住房补贴、交通补贴、通信补贴”列 函数嵌套 查找函数的使用和参数设置 计算及操作-P23 用嵌套的IF计算住房补贴 用VLOOKUP机算交通补贴 用index和match函数计算通信补贴 比较并总结 注意vlookup函数的最后一个参数的设置 =VLOO

6、KUP(D3,J4:M6,3,true) 大致匹配查找 =VLOOKUP(D3,J4:M6,3,false) 精确匹配查找,10,INDEX和MATCH函数计算通信补贴,用index+match函数计算通信补贴 利用match在H列确定每个员工职称在“福利发放标准”中的行位置 选定H3单元格输入以下公式 =MATCH(D3,$J$5:$J$7,0) 如果 match_type 为 -1，函数 MATCH 查找大于或等于 lookup_value 的最小数值。Lookup_array 必须按降序排列。 如果 match_type 为 1，函数 MATCH 查找小于或等于 lookup_value

7、 的最大数值。Lookup_array 必须按升序排列。 如果 match_type 为 0，函数 MATCH 查找等于 lookup_value 的第一个数值。Lookup_array 可以按任何顺序排列。 向下应用到所有H列单元格 利用index在G列确定相应行位置对应的补贴值 选定G3单元格输入以下公式 =INDEX($M$5:$M$7,H3,1) 向下应用到所有G列单元格,11,例5-工资管理表中条目计算1,定义数据区域的名称 为出勤统计表中的A3:A182数据定义名称，便于以后引用 公式/定义名称/将区域名称定义为“出勤统计” 引用出勤统计表中数据，计算“应扣缺勤费”列 事假工资扣除

8、公式=（基本工资+各类补贴） *（事假天数） /21.75 VLOOKUP(A3,出勤统计,4)*SUM(D3:G3)/21.75 病假工资扣除公式=0.3*（基本工资+各类补贴） *（病假天数） /21.75 0.3*VLOOKUP(A3,出勤统计,5)*SUM(D3:G3)/21.75 保留两位小数 负数红色显示 不显示零值 计算“应扣四金”列 =-ROUND(SUM(D3:H3)*0.15,2) 两位小数、负数,12,思考题-工资管理表中条目计算2,计算“应扣所得税”列 思考P28-0305-采用Hlookup或VLookup函数计算应扣所得税列 请上网查找我国最新的个人所得税税率表（类似P27-表3.2） 根据最新的个人所得税税率表，自行构造个人