6个函数公式，助你早下班

天天加班？就是因为你还不会用这几个函数公式！

小徐有话说：今天小徐给大家分享一组特别有用的函数公式，那就是关于身份证的有效输入、提取出生年月日，计算年龄，根据身份证判断男女并控制身份证的有效输入避免重复，HR必备函数公式。

1、身份证的有效输入

操作方法：选中要输入身份证号码的单元格，ctrl+1快捷键 —— 文本。

知识点：

身份证号码：一代身份证号码是15位，二代身份证是18位。在excel单元格中，常规状态下默认11位，如果要输入身份证号码（15位或者18位），必须将单元格的变成文本。

2、从身份证提取出生年月日

公式：=MID(A2,7,8)

知识点：

MID函数是一个提取文本函数

MID（文本字符串，本文要提取的第一个字符的位置，返回字符的个数）

小徐说：

提取出来之后将函数公式转换成数值，再用分列更改一下日期格式就可以啦。

3、根据出生年月日计算年龄

公式：=year(now())-year(A2)

知识点：

year/now是时间函数

now()，返回当前的系统日期和时间。

year(查找的年份日期)

小徐说：

大家都知道，计算年龄，最简单的就是把今年日期减去自己的出身年月即可。

但是如果每年打开excel工作表，想年龄会随着时间的变化变化，那么就得用公式去做，这样的话，每年就不用自己再去计算一遍。

now函数是一个易失性函数，里面不用写参数，先用now函数把今年的日期先提取出来，得到：“2018-3-15”（注意：记得转换一下日期格式：ctrl+shift+#)

在用year函数提取年，得到“2018”。再用一次year提取自己的出生年。

最后，用今年的减去自己的出身年。

4、根据身份证判断男女

公式：if(mod(left(right(a2,2),1),2）,"男","女"）

知识点：

mod函数是返回两数相除的余数。

小徐说：

告诉大家一个小知识点，根据身份证判断男女，二代身份证（18位）看倒数第二位，一代身份证（15位）看倒数一位。奇数为：男，偶数为：女

先用right/left函数提取，再用mod函数计算字符与2相除的余数，得到的余数为1和0，在用if去判断。

5、根据身份证判断男女（遇到15位和18位）

公式：=if(mod(mid(A2,15,3),2),"男","女"）

知识点：

mid函数是提取文本长度函数

MID(文本字符串，为文本要提取的第一字符的位置，返回文本的个数 )

小徐说：

18位身份证号码与15位身份证号码同时存在一个工作表中去判断男女，看看你看懂了没有？

6、查找重复身份证号码

公式：=IF(COUNTIF($A$2:$A$6,A2&"*")>1,"重复","")

知识点：

countif函数是一个条件计数函数。

countif(计数一个或多个单元格包含数字、数字名称数组或引用，计数条件)

小徐说：

countif第二参数加了通配符“*”用来解决超过15位失效，也就是说，如果不加通配符15位之后的数字不相同也会认为相同，加了通配符就能识别是否相同。

7、控制身份证的有效输入，避免重复

公式：=AND(OR(LEN(C13)=15,LEN(C13)=18),COUNTIF($C$13:$C$15,C13)=1)

知识点：

len函数返回文本字符串的字符个数。

小徐说：

小徐一般做控制身份证号码的有效输入，都是在外面先判断，再把公式粘贴到数据有效性里面。