员工工资管理模型.pptx
1员工工资管理模型员工工资管理模型员工工资管理模型员工工资管理模型2教师信息q教师：张晓黎q电子信箱：q办公室：学院楼B420q电话：677038543教学目标、学习重点与难点q能够以Excel为工具设计企业工资管理系统。教学目标：q特殊类型数据的输入q身份证号输入等q函数，单元格格式设置q日期时间函数q汇总分析函数：countif、sumif和frequency函数q员工工龄的计算及单元格格式设置q单元格设置对显示结果的影响q嵌套函数的使用qif函数计算福利q查找函数的深入理解：vlookup、hlookup、index和match函数q重点与难点q单元格格式设置q函数、嵌套函数的灵活使用4例1-员工工龄等条目的计算q算法P21q工龄=当前日期年份-入企年份q=YEAR(NOW()-YEAR(D3)注意如何利用函数面板输入函数和公式q单元格格式的设置选中工龄列数据/开始/单元格/格式/设置单元格格式右键/单元格格式/将日期型数据改为常规类型将自己的身份证号作为某个员工的身份证号进行输入q各类工资的计算：公式的使用q岗位工资=基础工资*0.5q工龄工资=工龄*100q调整后总基本工资=基础工资+岗位工资+工龄工资例2-各年龄段人数和基础工资汇总q按不同工龄段汇总人数q方法1-countif函数q对区域中符合指定条件的单元格计数。qrange必需。用于计数的单元格区域。空值和文本值将被忽略。工龄列工龄列qcriteria必需。用于确定计数的条件。q=Countif(工资记录表!$E$3:$E$182,=5)q=Countif(工资记录表!$E$3:$E$182,=10)-D25例2-各年龄段人数和基础工资汇总q按不同工龄段汇总人数q方法2-frequency函数qData_array为一数组或对一组数值的引用qBins_array为间隔的数组引用，该间隔用于对data_array中的数值进行分组。q对于返回结果为数组的公式，必须以数组公式的形式输入。q数组公式的数组公式的使用使用q选择区域选择区域(C2:C5)q输入公式输入公式“=FREQUENCY(工资记录表工资记录表!E3:E182,B2:B5)”q使用组合键使用组合键“Ctrl+Shift+Enter”6例2-各年龄段人数和基础工资汇总q按不同工龄段对基础工资进行汇总求和qSumif函数q对区域中符合指定条件的值求和。qrange必需。用于条件计算的单元格区域。空值和文本值将被忽略。工龄列工龄列qcriteria必需。用于确定求和的条件。qsum_range可选。要求和的实际单元格。如果该参数被省略，将对在range参数中指定的单元格求和。基础工资列基础工资列q=SUMIF(工资记录表!$E$3:$E$182,=5,工资记录表!$G$3:$G$182)q=SUMIF(工资记录表!$E$3:$E$182,=10,工资记录表!$G$3:$G$182)-E278例3-表格格式设置q文字格式-右键/设置单元格格式q图表标题居中加粗显示q表格边框格式-右键/设置单元格格式q外边框粗，内边框细q图表标题背景淡绿色+圆点q打印输出设置-文件/打印/页面设置(或页面布局/页面设置)q调整页边距q设置页眉页脚q每列上都有列标题：工作表/顶端标题行q打印预览：视图/页面布局9例4-员工福利项目的计算q计算“住房补贴、交通补贴、通信补贴”列q函数嵌套q查找函数的使用和参数设置q计算及操作-P23q用嵌套的IF计算住房补贴q用VLOOKUP机算交通补贴q用index和match函数计算通信补贴q比较并总结q注意vlookup函数的最后一个参数的设置q=VLOOKUP(D3,J4:M6,3,true)大致匹配查找q=VLOOKUP(D3,J4:M6,3,false)精确匹配查找福利发放标准住房补贴交通补贴通信补贴部门经理1500800600项目经理1200400300普通员工8001005010INDEX和MATCH函数计算通信补贴q用index+match函数计算通信补贴q利用match在H列确定每个员工职称在“福利发放标准”中的行位置q选定H3单元格输入以下公式q=MATCH(D3,$J$5:$J$7,0)如果match_type为-1，函数MATCH查找大于或等于lookup_value的最小数值。Lookup_array必须按降序排列。如果match_type为1，函数MATCH查找小于或等于lookup_value的最大数值。Lookup_array必须按升序排列。如果match_type为0，函数MATCH查找等于lookup_value的第一个数值。Lookup_array可以按任何顺序排列。q向下应用到所有H列单元格q利用index在G列确定相应行位置对应的补贴值q选定G3单元格输入以下公式q=INDEX($M$5:$M$7,H3,1)q向下应用到所有G列单元格11例5-工资管理表中条目计算1q定义数据区域的名称q为出勤统计表中的A3:A182数据定义名称，便于以后引用q公式/定义名称/将区域名称定义为“出勤统计”q引用出勤统计表中数据，计算“应扣缺勤费”列q事假工资扣除公式=（基本工资+各类补贴）*（事假天数）/21.75qVLOOKUP(A3,出勤统计,4)*SUM(D3:G3)/21.75q病假工资扣除公式=0.3*（基本工资+各类补贴）*（病假天数）/21.75q0.3*VLOOKUP(A3,出勤统计,5)*SUM(D3:G3)/21.75q保留两位小数q负数红色显示q不显示零值q计算“应扣四金”列q=-ROUND(SUM(D3:H3)*0.15,2)q两位小数、负数12思考题-工资管理表中条目计算2q计算“应扣所得税”列q思考P28-0305-采用Hlookup或VLookup函数计算应扣所得税列q请上网查找我国最新的个人所得税税率表（类似P27-表3.2）q根据最新的个人所得税税率表，自行构造个人所得税新税率表的查找表，然后用Hlookup或VLookup函数计算应扣所得税列q计算“实际应付工资”列q=应发工资-所得税13课堂练习、作业q课堂练习-例1-5q作业-P34，1-2（数据文件即课堂示例.xlsx）q选作操练-P34，思考题1-6
