你得学会的人力资源工作中常用的excel操作

拉朵

Excel在人力资源业务操作中应用广泛，熟练运用Excel，能够帮助人力资源从业者提升工作效率，节省工作时间，是每位人力资源从业者的必备技能。今天我们就把人力资源工作中最常用的函数公式来一次大总结，希望对做HR的伙伴们有所帮助。


身份证相关
1、身份证的输入
在excel中输入大于11位数字，会以科学记数法显示。而大于15位，后面的数字全转换为0，所以不能直接在excel中输入身份证号。
解决方法：先把该列设置为文本型格式，或输入前先输入单撇(')再输入身份证号码。


2、身份证的导入
从word、网页、数据库中复制含身份证信息的表格时，如果直接粘贴到excel中，身份证码列同样会后3位变成0。
解决方法：同1粘贴或导入前把excel表中存放身份的列设置为文本类型。
3、身份证的分列
如下图所示的员工信息在一列中 ，我们可以用分列的方式分隔成多列，但分列后身份证号码后3位会变成0。
解决方法：在分列的第3步，选取身份证列，选取文本类型即可。


4、身份证号长度验证
在输入身份证号码时，数字个数看起来很费劲。用数据有效性可以限制身份证号码输入必须是18位。
解决方法：选取输入单元格 - 数据 - 有效性 - 允许：文本长度，输入18


*单元格A1中是身份证号码
5、提取生日
=TEXT(MID(A1,7,8),'0-00-00')
6、提取年龄
=YEAR(TODAY())-MID(A1,7,4)
7、提取性别
=IF(MOD(MID(A2,17,1),2),'男','女')
8、提取属相
=CHOOSE(MOD(MID(A1,7,4)-1900,12)+1,'鼠','牛','虎','兔','龙','蛇','马','羊','猴','鸡','狗','猪')
9、提取星座
=VLOOKUP(--TEXT(MID(A1,11,4),'2015-00-00'),{0,'摩羯';42024,'水瓶';42054,'双鱼';42084,'白羊';42114,'金牛';42145,'双子';42177,'巨蟹';42208,'狮子';42239,'处女';42270,'天秤';42301,'天蝎';42330,'射手';42360,'摩羯'},2)
10、Countif函数统计身份证号码出错的解决方法
由于Excel中数字只能识别15位内的，在Countif统计时也只会统计前15位，所以很容易出错。不过只需要用 &'*' 转换为文本型即可正确统计。
=Countif(A:A,A2&'*')


入职计算
*A1是入职日期
1、入职年数
=Datedif(a1,today(),'Y')
2、入职月数
=Datedif(a1,today(),'M')
员工信息统计
1、A部门多少人？(B列为部门)
=Countif(b:b,'A部门')
2、北京分公司经理级别有多少人？
=Countifs(B:B,'北京' ,C:C,' 经理')
3、大于40岁的经理级别有多少人？
=Countifs(D:D,'>40',C:C,' 经理')
工龄及工资计算
1、本月工作日天数计算
下面是2015年1月份的工资表局部。


我们可以帮这位HR妹妹这样设置公式：
C2=NETWORKDAYS.INTL(IF(B2<>,DATE(2015,1,31),11)
公式说明：

• IF(B2<>;区分是不是新入职，如果不是新入职按本月1日作为开始日期，否则即为新入职，按实际入职日期算。
  
• DATE(2015,1,31)：本月最后一天作为计算的截止日期。
  
• 11: 表示本公司是一周六天工作日，星期日是休息日期
  

2、工龄工资计算（每6个月40元，封顶200）
C2单元格中为入职日期
=IF(INT(DATEDIF(C2,”2015-11-30“,'M')/6)*40>200,200,INT(DATEDIF(C2,”2015-11-30“,'M')/6)*40)
3、个人所得税计算
假如A2中是应税工资，则计算个税公式为：
=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)
4、工资条制作
工资表（一定要有序号列）


制作方法：
步骤1、把标题行复制到另一个表中，然后在序号下输入数字1，然在在B2中输入下面公式并向右复制
=VLOOKUP($A2,销售部工资表!$A:$I,COLUMN(B1),0)
公式说明：Column(b1)在公式向右复制时生成2,3,4,5,6...数字，作为VLOOKUP的第3个参数。


步骤2、选取前三行向下复制。（如果中间隔2个空行则选前4行）


复制后，工资条已自动生成。


本文为HR小报转载自网络，转载目的在于传递更多信息，并不代表HR小报赞同其观点和对其真实性负责。转载文章所包含的文字、图片等均来源于原作者。 如因作品内容、版权等存在问题，请于本文刊发30日内联系HR小报进行删除，并就版权问题联系相关内容来源。
HR小报欢迎更多媒体、企业与组织与我们展开内容合作，联系邮箱
hi@hrxiaobao.com
©2018 HR小报保留所有权利
<div class="pgc-img">