地址:长春市高新区学海街781号吉大科技园高科技产业孵化大厦903室
电话:+86 0431 88526466
邮箱:mdgs@cchrbp.com 
工作总结
您当前的位置:首页 > 企业动态 > 工作总结
HR工作中最常用的excel操作大全
作者: 发布于:2017/8/24 13:26:37 点击量:
放大  缩小  默认

 01

身份证有关


1. 身份证的输入

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

处理办法:先把该列设置为文本型格局,或输入前先输入单撇(')再输入身份证号码。

HR工作中最常用的excel操作大全(图二)

(图二)

 

2. 身份证的导入

从word、页面、数据库中仿制含身份证信息的表格时,假设直接张贴到excel中,身份证码列相同会后3位成为0。

处理办法:同1张贴或导入前把excel表中寄存身份的列设置为文本类型。

3. 身份证的排列

如下图所示的职工信息在一列中 ,咱们能够用排列的办法分隔成多列,但排列后身份证号码后3位会成为0。

处理办法:在排列的第3步,选择身份证列,选择文本类型即可。

HR工作中最常用的excel操作大全(图三)

(图三)
 

4. 身份证号长度验证

在输入身份证号码时,数字个数看起来很费力。用数据有用功用够约束身份证号码输入有必要是18位。

处理办法:选择输入单元格 - 数据 - 有用性 - 答应:文本长度,输入18

HR工作中最常用的excel操作大全(图四)

(图四)


单元格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. 获取出生地

身份证的前2位代表省份,前4位代表详细的市,前6位代表详细的区县。为了获取详细信息,咱们还需求一个身份证归属表(文后有下载地址),如下图所示。

 

HR工作中最常用的excel操作大全(图五)

 

有了身份证数据库,在客户明细表中就能够用vlookup函数别离获取省、市和区,用&衔接在一同构成归属地。

公式:C2

=VLOOKUP(LEFT(B2,2),身份证数据库!A:B,2,0)&VLOOKUP(LEFT(B2,4),身份证数据库!A:B,2,0)&VLOOKUP(LEFT(B2,6),身份证数据库!A:B,2,0)

 

HR工作中最常用的excel操作大全(图六)

 

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操作大全(图七)

 

02

入职核算

A1是入职日期。

1. 入职年数

=Datedif(a1,today(),"Y")

2. 入职月数

=Datedif(a1,today(),"M")

03

职工信息核算

1. A部分多少人?(B列为部分)

=Countif(b:b,"A部分")

2. 北京分公司司理级别有多少人?

=Countifs(B:B,"北京" ,C:C," 司理")

3. 大于40岁的司理级别有多少人?

=Countifs(D:D,">40",C:C," 司理")

04

工龄及薪酬核算

1. 本月作业日天数核算

下面是2015年1月份的薪酬表部分。

HR工作中最常用的excel操作大全(图八)

 

咱们能够帮这位HR小妹这么设置公式:

C2=NETWORKDAYS.INTL(IF(B2


公式阐明:

  • IF(B2

  • 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操作大全(图九)

(图九)

 

制造办法:


过程1

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

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

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

HR工作中最常用的excel操作大全(图一十)

(图一十)

 

过程2

选择前三行向下仿制。(假设中心隔2个空行则选前4行)

HR工作中最常用的excel操作大全(图一十一)

(图一十一)

 

仿制后,薪酬条已主动生成。

 

HR工作中最常用的excel操作大全(图一十二)

(图一十二)

 


版权所有:吉林省明德国仕企业管理顾问有限公司  技术支持:南北科技   吉ICP备:16000470号-1