由身份证号码分析出生日期及性别 EXCEL表格自动生成

昵称fIA9z 2013-06-05

展开全文

EXCEL表格自动生成

身份证包含了大量信息，如发证地址、出生日期、性别等，可以通过设置公式自动将出生日期和性别提取出来。设置方法如下：

(1)提取出生日期

身份证号码通常是15位或18位，

1）若身份证号码为15位，如某人的身份证号码为“310112570416314”，说明他是1957年4月16日出生，如果出生的年份用M表示，月份用N表示，日期用P表示，用文本连接符将其连接起来就是“=＂19＂&M&＂年＂&N&＂月＂&P&＂日＂”。
为了从身份证号码中提取有关的信息，需要用一个文本函数"MID”，其格式为：MID(text,start_num,num_chars)，表示的意思是：MID（原来的字串，开始位置，字符的个数）。设原来字串即身份证号码为R。则：
年份M=MID（R，7，2），表示从第7个字符开始算起取2个，即取“57”。
月份N=MID（R，9，2），表示从第9个字符开始算起取2个，即取“04”。
日期P=MID（R，11，2），表示从第11个字符开始算起取2个，即取“16”。

这时上述函数就变为：
="19"&MID(B2,7,2)&"年"&MID(B2,9,2)&"月"&MID(B2,11,2)&"日"

上式表示的意思是：“19”加上从B2单元格中的第7位开始取出2位的数值加上“年”，再加上B2单元格中从第9位开始取出2位的数值加上“月”，再加上B2单元格中从第11位开始取出2位的数值加上“日”。在C2中输入公式后，打回车，然后拉动C2单元格的右下角，得出如图2-72所示结果。

图2-72

2）身份证号码为18位，因为不需要再加年了，只需将公式改为“=MID(B9,7,4)&"年"&MID(B9,11,2)&"月"&MID(B9,13,2)&"日"”即可，如图2-73所示。

图2-73

3）若15位和18位的混合在一起。

方法一：可以将其排序，将15位的和18位的分开成两类，分别用上述函数即可。
方法二：不进行新的排序，再引入判别函数IF，对字符串进行判别，若是15位，则用前述函数，否则用后面的函数。
公式为：=IF(LEN(B2)=15,”19”&MID(B2,7,2)&”年"&MID(B2,9,2)&”月"&MID(B2,11,2)&”日",MID(B2,7,4)&”年"&MID(B2,11,2)&”月"&MID(B2,13,2)&”日")。
上式的含义是：若B2单元格中是15个字符时，用含有“19”的函数运算，否则用后面的函数运算。如图2-74所示。“LEN”是确定单元格中字符串的个数的函数，既可以确定数值的个数，也可以确定文字的个数。

图2-74

(2)确定男女性别

18位身份证号码中的倒数第二位是用来确定性别的，单数为男，双数则为女。要确定性别，用函数MID，该函数表示从字符串指定位置开始取出的字符位数，如“MID(B2,17,1),2)”表示从B2单元格中的字符中，从左边数第17位开始，取出一位数值。再用函数MOD判别是奇数还是偶数。在D2中输入的函数为：“=IF(MOD(MID(B2,17,1),2)=1,"男","女")”。公式的含义是：在B2单元格中取出倒数第二位，然后除以2，当余数是1时，则为男，否则为女。然后下拉进行公式的填充。如图2?75所示。也可以在D2单元格中输入公式：“=(IF(B2="","",IF(MOD(MID(B2,17,1),2)=1,"男","女")))”，其含义是，当B2单元格为空时，显示为空，否则再进行性别的判断。如果15位和18位混合在一起，可以用公式：“=(IF(B2="","",IF(MOD(IF(LEN(B2)=15,MID(B2,15,1),MID(B2,17,1)),2)=1,"男","女")))”。当B2单元格为空时，显示为空，否则，当B2单元格为15位时，取15位的最后一位（身份证号为15位的最后一位表示性别），否则，从18位身份证号第17开始取一位，进行性别的判断。