1、EXCEL 第六章在人力资源与行政管理中的应用CONTENTS01制作考勤表02出勤情况分析03不同部门工资增幅变化目录05计算销售提成04制作、汇总工资表6.1 整理考勤机数据考勤机数据虽然市面上的考勤机种类非常多，功能也越来越强大，但是实际应用中，仍然需要人力资源管理人员对请假、迟到、早退、未打卡等一些异常情况进行单独处理，最终以考勤机的打卡时间为依据，辅以请休假等特殊情况的记录生成考勤表。6.1.1统计出勤状态和早退迟到时间出勤状态公式=IF(D2-INT(D2)=-18:00),正常,迟到/早退)日期和时间数据日期和时间混合型的数据，实际上是由整数组成的日期序列值和由小数组成的时间序列
2、值两部分组成。统计迟到早退次数筛选不重复记录6.1.2制作加班统计表公式的复制与填充方法1 拖曳填充柄。单击输入公式的单元格，指向该单元格右下角，当鼠标指针变为黑色“ ”字型填充柄时，按住鼠标左键向下拖曳至目标单元格。方法2 双击填充柄。单击输入公式的单元格，双击该单元格右下角的填充柄，公式将向下填充到当前单元格所位于的不间断区域的最后一行。方法3 填充公式。在一个单元格中输入公式后，然后选中需要输入相同公式的单元格区域，按组合键或单击【开始】选项卡的【填充】下拉按钮，在扩展菜单中单击【向下】按钮。按指定条件求和SUMIF函数第一参数用于判断条件的单元格区域。第二参数用于确定求和的条件。第三参
3、数是要求和的实际单元格区域。其语法可以理解为：SUMIF(条件判断区域,求和条件,求和区域)求和条件使用通配符按多个条件求和SUMIFS函数用法：SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,)多求和条件使用通配符除了使用考勤机打卡记录员工的考勤之外，还有部分企业会使用“考勤表”的形式记录员工出勤。设计合理的考勤表不仅能够直观显示员工的出勤状况，还可以减少统计人员的工作量。6.2 用Excel制作考勤表考勤表常用日期类函数 DAY函数返回某个日期的在一个月中的天数，结果是介于 1 到 31 之间的整数。YEAR函数返回对应于某个日期的年份，结果为19009999之间的整数。M
4、ONTH函数返回日期中的月份，结果是介于1（一月）到12（十二月）之间的整数。DATE函数可以根据指定的年份数、月份数和一月中的天数返回日期序列。EOMONTH函数返回指定日期之前或之后指定月份数的月末日期。WEEKDAY函数返回对应于某个日期的一周中的第几天。一个月结束后，通常需要制作一份月度出勤汇总表，对员工的出勤情况进行汇总后张贴公示或提请领导签字后再以此制作工资表。6.3 制作月度出勤汇总表汇总表效果6.4 部门出勤情况分析统计部门出勤情况时，通常会统计部门的出勤率或是缺勤率，部门出勤率的计算方法为部门实际出勤天数之和除以应出勤天数之和*100%。部门出勤率分析表平均出勤率分析表6.5
5、 制作职工带薪年休假申请审批单凡连续工作1年以上的均可以享受带薪年休假，职工在休假期间享受与正常工作期间相同的工资收入。累计工作满1年不满10年的，年休假5天；满10年不满20年的，年休假10天；满20年的年休假15天。职工年休假记录台帐带薪年休假申请审批单6.6 用图表展示不同部门工资增幅变化年度工资结构分析通常包括薪资增长状况、不同薪资结构对比、不同职位和级别的薪资数据、奖金福利状况、长期激励措施以及未来薪资走势分析等。本节以工资增幅数据为例，学习使用图表展示不同部门工资增幅变化的情况。不同部门工资增幅变化6.7 使用条件格式展示工资增幅变化使用图表展示数据的优点是效果美观，但是其操作步骤
6、较多，刚刚学习Excel的用户往往不容易掌握，可以使用条件格式实现类似的效果。不同部门工资增幅变化6.8 制作工资表企业有每月为职工代扣、代缴工资薪金所得部分个人所得税的义务。根据有关法规：工资薪金所得以每月收入额减除费用3500元后的余额为应纳税所得额，即： 月应纳税所得额=月工资收入额-3500（基数） 月应纳税额=月应纳税所得额适用税率-速算扣除数个人所得税税率表级数级数 含税级距含税级距税率税率(%)(%)计算公式计算公式速算扣除数速算扣除数1 1不超过1500元的 3T=(A-3500)*3%-002 2超过1500元至4500元的部分 10T=(A-3500)*10%-105105
7、3 3超过4500元至9000元的部分 20T=(A-3500)*20%-5555554 4超过9000元至35000元的部分 25T=(A-3500)*25%-100510055 5超过35000元至55000元的部分 30T=(A-3500)*30%-277527756 6超过55000元至80000元的部分 35T=(A-3500)*35%-550555057 7超过80000元的部分 45T=(A-3500)*45%-1350513505本表含税级距指以每月收入额减除费用本表含税级距指以每月收入额减除费用35003500元后的余额或者减除附加减除费用后的余额。元后的余额或者减除附加减除
8、费用后的余额。6.8.2 计算个人所得税个税计算公式=ROUND(MAX(应税工资-3500)*3;10;20;25;30;35;45%-0;105;555;1005;2755;5505;13505,0),2)6.9 分类汇总工资表在月度工资表中往往需要按照部门统计当月工资的小计，使用分类汇总功能可以简化统计工作量，提高工作效率。不同部门工资增幅变化 部门分类汇总统计 打印不同汇总结果 高亮显示部门小计6.10 每页带小计的工资表如果工资表的数据量比较多，当需要打印时，在每一页的最后加上本页小计，在最后一页加上总计，能够增加表格的可读性。类似的打印效果在一些财务软件中已经实现了自动化，用Exc
9、el自带的功能也可以实现这样的效果。设置后的打印预览效果6.11 按部门拆分工作表如果将工资表中的内容按不同的部门分别拆分到不同工作表内，能够便于查询和打印。本节学习按部门拆分工资表的技巧。显示报表筛选页6.12 批量制作员工工资条工资条是单位定期给员工反映工资的凭据，记录着员工的月收入分项和收入总额，通常包括工号、职工姓名、基本工资、奖金福利、代缴保险、应发工资、个人所得税和实发工资等项目。工资条的制作可通过多种方法来实现，主要有排序法、函数法以及邮件合并法等。工资条效果6.13 计算销售提成销售提成计算主要包括以下四种：1、纯提成制，销售人员的工资全部由提成构成，不发放固定的基本工资。2、底薪+提成制，销售人员的工资由基本工资和业绩提成两部分构成。3、底薪+提成+奖金制，销售人员除了底薪和业绩提成外，还会获得部分奖金收入。4、底薪+奖金制，销售人员的收入主要以底薪和与企业整体利益挂钩的奖金组成。阶梯式销售提成计算6.14 用图表展示历年平均薪酬变化以企业历年平均薪酬数据为例，学习使用图表展示历年平均薪酬变化的技巧完成后的图表效果
