excel身份证号提取出生日期 如何从身份证号自动提取年龄、性别、出生日期等

我国公民身份证由18位数字组成，前6位代表行政区域，出生日期7-14，派出所顺序码15-16，第17位即倒数第二位，最后一位校验码。当我们使用Excel时，18位身份证是我们经常需要输入和编辑的一种数据。今天我们请一位老师用Excel分享一下身份证的知识，这个你肯定不知道！

excel如何完整显示身份证号码

第一次用Excel输入身份证时，下图C、D栏通常有错误，要么数字显示为科学计数法，要么身份证最后三位数字全部变成0。

这就是为什么，其实和Excel的数据类型有关。默认情况下，可以计算Excel数字。所以Excel会自动将10位以上的数字更新为科学的计算方法，一旦超过15位，16位以后的数字全部变成0。

此时，我们只需要将Excel列的数据类型改为文本即可。文字是指无论单元格中输入什么信息，都会完整显示出来。需要注意的是，如果在输入数据之前设置了文本，数据会正确显示。如果输入数据后设置文本，需要重新输入数据。

根据身份证自动显示出生日期。

=MID(F7，7，4)& amp；“年份”& MID(F7，11，2)& amp；“月”& MID(F7，13，2)& amp；“日”

功能说明:F7是身份证号，功能输入G7单元格。mid(F7，7，4)表示从F7单元的第七个数字中取四个数字。&:这个符号是一个连接器，将数字和文本链接在一起。汉字通常在函数中加双引号。结果出来后可以直接填。

3根据身份证自动显示年龄

=year(today())-year(G7)

功能说明:找到年龄的方法有很多。这里我们用最简单的方法来计算:当年减去出生年份。Year()表示查找括号中日期的年代，today()显示今天的日期，year(today())查找当天，然后查找当年。因为出生日期已经在G7中找到，所以年份(G7)是显示出生日期的年份。

4根据身份证自动判断性别

=if(mod(mid(F7，17，1)，2)=0，“女性”，“男性”)

函数说明:这是一个三个基本函数嵌套的复合函数。首先我们知道倒数第二张身份证判断性别，奇数男，偶数女，最外层if函数，if(逻辑条件，“女”，“男”)。如果逻辑条件成立，则显示女性，否则显示男性。逻辑条件是什么？身份证倒数第二位是偶数，即身份证倒数第二位除以2的余数为0，mid(F7，17，1)取身份证倒数第二位，mod(A，2)要求A除以2的余数，A为偶数则显示0，A为奇数则显示1。

5根据身份证自动确定属

= choose (mod (mid (F7，7，4)-1900，12)+1，“鼠”“牛”“虎”“兔”“龙”“蛇”“马”“羊”“猴”“鸡”

功能说明:选择功能根据参数索引显示数值。因为有12个属，出生年份减1900，除以12后的余数加1。第一个值对应鼠标，第二个值对应牛，以此类推。

6根据身份证自动确定星座

= vlookup (-text (mid (F7，11，4)，" 2015-00-00 ")，{0，"摩羯座"；42024，“水瓶”；42054，“双鱼”；42084，《白羊座》；42114，“金牛座”；42145，“双子”；42177，《癌症》；42208，《狮子》；42239，“处女”；42270，“天秤”；42301，“天蝎”；42330，“射手”；42360，“摩羯座”}，2)

功能解释:太复杂，解释不了。如果要用，直接涂就可以了。

可见Excel的功能非常强大，这里用到的功能可以直接复制引用，但是一定要注意单元格的差异。比如F列是这里的身份证，F7是第一个出现的身份证号，所以如果引用的时候身份证在其他单元格，这里的F7就要改了。另外需要注意的是，这里所有的标点符号都应该是英文的。

原干货，好玩好学。我在找老师。这是本周的技能知识。明天我们会和文物分享。