|
首先我们先了解下中国身份证号码的编排规则，中国公民身份证共18位，1~6位为省份地区信息码；7~14位为出生日期码；15~16位数字表示所在地的派出所的代码；第17位数字表示性别（奇数表示男性，偶数表示女性）；第18位数字是校检码，也有的说是个人信息码，一般是随计算机的随机产生，用来检验身份证的正确性。校检码可以是0~9的数字，有时也用x表示。 1、提取生日（0000-00-00） 我们将用到TEXT和MID函数 （1）函数TEXT(value,farmat_text)，意思是指将数值转换为指定的格式。 （2）函数MID(text, start_num, num_chars)，意思是从文本字符串中的指定位置开始提取特定数目的字符。 我们在E3单元格输入TEXT(MID(B3,7,8),'0000-00-00')，公式是指从B3单元格（身份证号码）中第7个字符起提取8个字符，并转换为“0000-00-00”格式。  2、提取年月日 函数：MID（text，start-num，num-chars） 意思是从左往右返回文本字符串从指定位置开始的指定长度的字符。 （1）提取“年”，在F3单元格输入=MID(B3,7,4)，意思是指从B3单元格（身份证号码）中第7个字符起提取4个字符。 （2）提取“月”，在G3单元格输入=MID(B3,11,2)，意思是指从B3单元格（身份证号码）中第11个字符起提取2个字符。 （3）提取“日”，在H3单元格输入=MID(B3,13,2)，意思是指从B3单元格（身份证号码）中第13个字符起提取2个字符。  3、计算年龄 因为年龄每年都会改变，所以Excel表格中储存的人员年龄每年都会不同，要保证表格中人员年龄的准确性，需要每年对表格内容进行更新，但要是每年都手动逐个更新表格中的年龄，工作量会比较大。如果表格中存有身份证号码，我们可以根据身份证号码中的出生年月信息计算出这个人的实际年龄，而且这个年龄会根据电脑中的时间在第二年自动更新，从而可以避免手动更新的麻烦 涉及到TODAY和YEAR函数 （1）函数TODAY（），意思是提取电脑系统当天日期。 （2）函数YEAR(serial_number)，返回对应于日期的年份。 Year 作为 1900 - 9999 之间的整数返回。 我们在C3单元格输入=YEAR(TODAY())-MID(B3,7,4)，意思是指电脑系统当前年份减去B3单元格（身份证号码）中第7个字符起提取4个字符所代表的年份。  4、提取性别 涉及到ISODD和IF函数 （1）函数ISODD(number)，意思指如果参数 number 为奇数，返回 TRUE，否则返回 FALSE。 （2）函数IF（任意值或表达式，计算结果为 TRUE 时返回的值，计算结果为 FALSE 时返回的值）。 我们在D3单元格输入=IF(ISODD(MID(B3,17,1)),'男','女')，意思是指从B3单元格（身份证号码）中第17个字符起提取1个字符，如果这个数据是奇数，D3单元格返回值是“男”；如果不是奇数，则D3单元格返回值是“女”。  5、提取生肖 涉及到函数MOD来计算余数，MOD(number, divisor)，number是被除数，divisor是除数。 生肖一共12个，每12年轮回一次，这就涉及到余数的概念。把身份证号码中间包含年份的提取出来，然后把年份除以12得到12的余数，为了避免余数为零（如果是零值，无法用mid等公式取值），我们将余数+1，这样余额+1得出来的数对应不同的生肖即可见下表  由上图我们可得知，1-12分别对应的生肖是“猴鸡狗猪鼠牛虎兔龙蛇马羊”。这样我们在I3单元格输入=MID('猴鸡狗猪鼠牛虎兔龙蛇马羊',MOD(F3,12)+1,1)，这样就可提取I3单元格所对应的生肖。F3是1975（下图已隐藏），与12的余数+1是8（见上图），从'猴鸡狗猪鼠牛虎兔龙蛇马羊'字符串数第1位“猴'开始数起，第8位是兔，所以I3单元格的返回值是生肖'兔'。  6、提取籍贯 从身份证号码中是无法直接获得籍贯的，需要另外准备“地区代码名称”表，通过VLOOKUP函数从“地区代码名称”表中查找与身份证号码前6位数字对应的籍贯。  函数VLOOKUP (查阅值、包含查阅值的区域、包含返回值的区域中的列号、近似匹配 (TRUE) 或完全匹配 (FALSE))。 我们在J3单元格输入=VLOOKUP(LEFT(B3,6),地区代码名称!$A$1:$E$5000,5)，，在“地区代码名称”表中A1到E5000的区域中，查找从B3单元格左边获取6个数字，并返回“地区代码名称”表中A1到E5000的区域中的第5列所对应的籍贯。  |
|
|
