HR工作中最常用的excel操作大全,没做过HR的你也用得上 图文

2020年9月28日07:22:48HR工作中最常用的excel操作大全,没做过HR的你也用得上 图文已关闭评论 168 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
微信公众号 【EXCEL880】 QQ群【273774246】

我虽然未做过HR岗位,但回答过的人力资源管理相关excel问题却数不胜数。

今天就把人力资源工作中最常用的函数公式来一次大总结,希望对做HR的朋友有所帮助。

一、身份证相关

1、身份证的输入

在excel中输入大于11位数字,会以科学记数法显示。而大于15位,后面的数字全转换为0,所以不能直接在excel中输入身份证号。

解决方法:先把该列设置为文本型格式,或输入前先输入单撇(')再输入身份证号码

HR工作中最常用的excel操作大全,没做过HR的你也用得上

2、身份证的导入

从word、网页、数据库中复制含身份证信息的表格时,如果直接粘贴到excel中,身份证码列同样会后3位变成0。

解决方法:同1粘贴或导入前把excel表中存放身份的列设置为文本类型。

3、身份证的分列

如下图所示的员工信息在一列中 ,我们可以用分列的方式分隔成多列,但分列后身份证号码后3位会变成0。

解决方法:在分列的第3步,选取身份证列,选取文本类型即可。

HR工作中最常用的excel操作大全,没做过HR的你也用得上

4、身份证号长度验证

在输入身份证号码时,数字个数看起来很费劲。用数据有效性可以限制身份证号码输入必须是18位。

解决方法:选取输入单元格 - 数据 - 有效性 - 允许:文本长度,输入18

HR工作中最常用的excel操作大全,没做过HR的你也用得上

单元格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、提取出生地

参考:

http://mp.weixin.qq.com/s?__biz=MjM5NDYyNzAzNQ==&mid=212006319&idx=1&sn=f9f9d177701f709470eee5771f04d2ee#rd

9、提取属相

=CHOOSE(MOD(MID(A1,7,4)-1900,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")

10、提取星座

=VLOOKUP(--TEXT(MID(A1,11,4),"2015-00-00"),{0,"摩羯";42024,"水瓶";42054,"双鱼";42084,"白羊";42114,"金牛";42145,"双子";42177,"巨蟹";42208,"狮子";42239,"处女";42270,"天秤";42301,"天蝎";42330,"射手";42360,"摩羯"},2)

11、Countif函数统计身份证号码出错的解决方法

由于Excel中数字只能识别15位内的,在Countif统计时也只会统计前15位,所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。

=Countif(A:A,A2&"*")

HR工作中最常用的excel操作大全,没做过HR的你也用得上

二、入职计算

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," 经理")

4、生日提醒(b2为出生日期)

链接:http://mp.weixin.qq.com/s?__biz=MjM5NDYyNzAzNQ==&mid=401718861&idx=1&sn=57972f524d1ca0d93001136d9d8b6941#rd

四、工龄及工资计算

1、本月工作日天数计算

下面是2015年1月份的工资表局部。

HR工作中最常用的excel操作大全,没做过HR的你也用得上

我们可以帮这位HR妹妹这样设置公式:

C2=NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)

公式说明:

  • IF(B2<DATE(2015,1,1),DATE(2015,1,1),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、工资条制作

工资表(一定要有序号列)

HR工作中最常用的excel操作大全,没做过HR的你也用得上

制作方法:

步骤1、把标题行复制到另一个表中,然后在序号下输入数字1,然在在B2中输入下面公式并向右复制

=VLOOKUP($A2,销售部工资表!$A:$I,COLUMN(B1),0)

公式说明:Column(b1)在公式向右复制时生成2,3,4,5,6...数字,作为VLOOKUP的第3个参数。

HR工作中最常用的excel操作大全,没做过HR的你也用得上

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

HR工作中最常用的excel操作大全,没做过HR的你也用得上

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

HR工作中最常用的excel操作大全,没做过HR的你也用得上

微信公众号 【EXCEL880】 QQ群【273774246】
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页