HR工作中常用的Excel操作——与身份证有关的操作
Hi,各位亲们,大家好，从今天开始我用笔名聂美骐发布文章了，欢迎大家以后称呼我的笔名。
今天给大家分享HR工作中常用的Excel操作——与身份证号码有关的操作技巧。
1、身份证号码的录入
在Excel中输入大于11位数字，会以科学记数法显示,大于15位，后面的数字全转换为0，所以不能直接在excel中输入身份证号。
解决方法：先把要录入的那列设置为文本型格式，或输入前先输入单引号(')再输入身份证号码。
2、身份证号码长度验证
在输入身份证号码时，数字个数看起来很费劲。用数据验证可以限制身份证号码输入必须是18位。
图1
3、身份证号码的导入
从Word、网页、数据库中复制含身份证信息的表格时，如果直接粘贴到Excel中，身份证码列同样会后3位变成0。
解决方法：同1粘贴或导入前把Excel表中存放身份的列设置为文本类型。
4、身份证号码的分列
如图所示的员工信息在一列中，我们可以用分列的方式分隔成多列，但分列后身份证号码后3位会变成0。
解决方法：在分列的第3步，选取身份证列，选取文本类型即可。
图2
5、提取生日
有三种方法：快速填充神奇的快速填充功能（点击文字阅读相关技巧）、函数、分列。
分列第一步选固定宽度，第二步建立分列线，第三步结果再次分列，前2步默认，第三步常规改为日期。
图3
6、提取性别、地区代码
A1为身份证号码，提取性别公式=IF(MOD(MID(A1,17,1),2)=0,"女","男")
提取地区代码公式=LEFT(A1,6)
7、提取年龄
公式=YEAR(TODAY())-MID(A1,7,4)
8、提取属相
公式=CHOOSE(MOD(MID(A1,7,4)-1900,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪")
图4
9、提取星座
=VLOOKUP(--TEXT(MID(A1,11,4),"2015-00-00"),{0,"摩羯";42024,"水瓶";42054,"双鱼";42084,"白羊";42114,"金牛";42145,"双子";42177,"巨蟹";42208,"狮子";42239,"处女";42270,"天秤";42301,"天蝎";42331,"射手";42360,"摩羯"},2)
这里用到vlookup函数的模糊查找。
公式中的42024等数字是日期的序列，表示距离1900年1月1日的天数，公式中的数字序列对应的日期以及星座如图5,各星座起始日期见图6。
图5
图6
10、Countif函数统计身份证号码出错的解决方法
由于Excel中数字只能识别15位内的，在Countif统计时也只会统计前15位，所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。公式=Countif(A:A,A2&"*")。
今天的分享到此结束，如果想看更多历史文章，请从菜单所有文章查看。