Excel2007人力资源管理应用

Excel2007人力资源管理应用杜兵员工信息管理在EXCEL中如何规范员工信息数据根据身份证号计算出生日期和年龄用VLOOKUP函数查找员工信息利用条件格式比较员工信息合同到期日期计算、提醒数据类型•数字：表示数值的大小,可计算,右对齐如产值、利润、成绩和工资等•文本：表示意思或意义，不计算，左对齐，如姓名、职务等。•日期和时间：表示日期和时间，简单计算（加减法）右对齐，如生日等规范员工信息•利用序列控制输入内容•设置下拉菜单，选择【数据】→【数据有效性】→【设置】→【允许】→【序列】注意：序列之间必须用半角逗号隔开•【案例】设置下拉菜单选择“性别”，并检测已有性别输入是否正确规范员工信息•设置序列跨工作表数据源引用1.创建名称：【公式】→【定义的名称】2.设置跨工作表数据源引用，在来源中选择名称数据。•【案例】设置部门、学历和婚否，并隐藏保护数据来源表规范员工信息•自定义有效性规则•选择【数据】→【数据有效性】→【设置】→【允许】→【自定义】•【案例】设置员工编号值唯一性，身份证号码15或18位•附：使用COUNTIF、OR、LEN等函数提取出生日期和年龄信息•身份证号码为文本型身份代码数据，其中包含有地区编码（前6位）、出生日期、性别和编号等信息，使用DATE、MID和DATEDIF等函数进行处理。•【案例】使用身份证号码提取出生日期，动态计算员工年龄•DATE：=DATE（年份，月份，日）•MID：=MID（字符串文本，开始位置，截取位数）•DATEDIF：=DATEDIF（开始日期，终止日期，差值类型）•差值类型：“D”-日差“M”-月差“Y”-年差VLOOKUP查询员工信息•利用VLOOKUP函数建立查询系统，实现工作表关联，轻松获取员工信息。•【案例】根据员工编号查询员工各项信息附：IFERROR函数条件格式比较员工信息•选择要设置格式的区域，进行条件规则的设定。单击【开始】→【条件格式】→【新建规则】。设定的多个条件冲突时，排在上方的规则优先级更高。•【案例】数据条显示工龄长短•【案例】使用颜色显示年龄大小合同到期日计算•试用期员工合同到期日计算，可使用EDATE和TODAY等函数来完成•EDATE：=EDATE（起始日期，前（或后）月数）•TODAY：=TODAY（）返回系统日期，自动更新•【案例】计算试用期合同到期日，及条件格式颜色提示薪酬管理个人所得税的三种计算方法根据税后工资计算税前工资加班工资统计员工月度工资部门汇总表工资条的批量制作、群发邮件薪酬图表的制作：悬浮图制作个人所得税的三种计算•个人所得税是工资核算中的重要项目，使用3%-45%的七级超额累进税率，计算公式是：•个人所得税=（税前工资-起征点）*相应税率-速算扣除数1.使用IF函数嵌套2.使用数组公式3.使用VLOOKUP函数使用IF嵌套和数组公式•Excel2007可嵌套超七层，IF的使用格式：=IF（条件判断，真表达式，否表达式）•使用数组公式1.为什么使用MAX2.为什么后面带0求最大值3.使用区域数组根据税后工资计算税前工资•少数企业给员工发放的是税后工资，这就需要反算出税前工资是多少，可使用数据组公式完成：公式1：=if(应税工资0,税后工资,应税工资+3500)公式2：应税工资=税后工资-3500-速扣数/(1-税率）公式3：个税=（税前工资-3500）*税率-速扣数应税工资部分推导税=应税工资*税率-速扣数税=应税工资-税后保留部分应税工资*税率-速扣数=应税工资-税后保留部分(税后工资-起征点-速扣数)/(1-税率)=应税工资加班工资月统计•在不明确具体哪些人参与加班的情况下，使用数据透视表统计是最好选择：1.根据加班记录,计算加班时间=HOUR(结束时间-开始时间）=MINUTE（结束时间-开始时间）2.判断加班时间发生在哪一周=WEEKNUM（日期，2）=EOMONTH（日期，月数）加班工资月统计使用透视表进行统计：选择【插入】→【数据透视表】→【设置】1.使用本月周充当“列标签”2.使用加班开始时间充当“列标签”并分组步长为“7”天员工月度工资部门汇总表工资部门的汇总，是多条件的求和统计，可使用以下两种方法完成：方法1：数据透视表方法2：=SUMPRODUCT（数组1，数组2……）工资条制作工资条制作有多种方式：函数计算和宏1.函数计算：=index(数据列表，行位置，列位置）=row()=column()2.宏方式：使用相对引用，执行循环Fori=1to26next悬浮图制作平均工资悬浮图，可直观体现工资成本涨跌变化：1.数据改造形成三列,高低之间可产生涨跌柱2.创建线型图表,为图表显示趋势线类型为涨跌柱3.隐藏数据线条，显示中间点标签，更改标签引用员工绩效管理员工绩效考核表编制数据有效性条件格式宏模板-合并工作表模板绩效等级比例表设计利用Rank函数计算员工排名Vlookup函数计算绩效等级员工绩效考核表编制1.数据有效性•批量定义名称•引用其他表充当数据来源•使用INDIRECT函数=INDIRECT（引用名称）员工绩效考核表编制2.条件格式与求和计算•将分值列使用颜色条件格式，值越高颜色绿，低颜色红•使用求和函数汇总总分并自定义格式•选讲:使用密码查看数据区域员工绩效考核表编制3.录制宏转移考核表中数据•设置宏属性•录制宏•将宏指定给按钮绩效排名•使用RANK排名=RANK（数值，排名范围）•使用SUMPRODUCT排名=SUMPRODUCT（（数值范围)*1)+COUNTIF（首单元格：当前，当前）Vlookup函数计算绩效等级Vlookup查询有两种方式：精确和模糊匹配，计算绩效等级，需使用模糊匹配：=Vlookup（查询条件，数据列表，返回信息列，1）分类汇总•对数据进行分类汇总后，不但增加了数据表格的可读性，而且提供了进一步分析数据的功能。•在分类汇总前首先需要对分类字段进行排序。1.嵌套分类汇总2.定位复制汇总数据•需要复制分类汇总的结果，先选中需要复制的区域，F5，定位可见单格，分类汇总•【案例】计算各部门各学历的工资总额•【案例】定位复制汇总数据