HR工作中最的4个Excel技巧15个函数公式

时间：2024-04-08 14:46:58 晶敏 HR指南我要投稿

相关推荐

HR工作中最常用的4个Excel技巧15个函数公式

　　作一名优秀的HR，对于一些常用的办公软件一定是要求要非常熟悉的。下面是小编为大家搜集整理出来的有关于HR工作中最常用的4个Excel技巧15个函数公式，希望可以帮助到大家！

　　一、身份证相关

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

　　参考：

　　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位，所以很容易出错。不过只需要用 &"x" 转换为文本型即可正确统计。

　　=Countif(A:A，A2&"x")

　　二、入职计算

　　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

　　公式说明：

　　IF(B2

　　DATE(2015，1，31)：本月最后一天作为计算的截止日期。

　　11: 表示本公司是一周六天工作日，星期日是休息日期

　　2、工龄工资计算（每6个月40元，封顶200）

　　C2单元格中为入职日期

　　=IF(INT(DATEDIF(C2，”2015-11-30“，"M")/6)x40>200，200，INT(DATEDIF(C2，”2015-11-30“，"M")/6)x40)

　　3、个人所得税计算

　　假如A2中是应税工资，则计算个税公式为：

　　=5xMAX(A2x{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行）

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

　　一、员工考勤表公式

　　1、本月工作日天数(AG列)

　　=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

　　2、调休天数公式(AI列)

　　=COUNTIF(B9:AE9,"调")

　　3、扣钱公式(AO列)

　　婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元

　　=SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10}

　　二、员工数据分析公式

　　1、本科学历人数

　　=COUNTIF(D:D,"本科")

　　2、办公室本科学历人数

　　=COUNTIFS(A:A,"办公室",D:D,"本科")

　　3、30~40岁总人数

　　=COUNTIFS(F:F,">=30",F:F,"<40")

　　三、其他公式

　　1、提成比率计算

　　=VLOOKUP(B3,$C$12:$E$21,3)

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

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

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

【HR工作中最的4个Excel技巧15个函数公式】相关文章：

HR的说话技巧08-25