Excel与身份证的相关问题一次解决
- 游戏资讯
- 发布时间:2024-09-19 09:31:50
A2单元格是身份证号码，在B2单元格中输入公式【=MID(A2,7,8)】，即可获取身份证的出生年月日格式：20160911。
公式说明：A2单元格中的第7位数开始提取后面的8位数。
提取身份证号码后显示：yyyy-mm-dd输入公式【=TEXT(MID(A2,7,8),"0-00-00")】，即可获取身份证的出生年月日格式：2016-09-11。
公式说明：这里大家看到好像是多了一个函数对吧，那就是TEXT，这个是将数值转换为按指定数字格式。A2单元格中的第7位数开始提取后面的8位数。最后以我指定的格式0000-00-00表示出来。
提取身份证号码后显示：yyyy年mm月dd日输入公式【=TEXT(MID(A2,7,8),"0年00月00日")】，即可获取身份证的出生年月日格式：2016年09月11日。
公式说明：第7位数开始提取后面的8位数。TEXT将数值转换为按指定数字格式yyyy年mm月dd日。
提取身份证号码后显示：yyyy/mm/dd输入公式【=TEXT(TEXT(MID(A2,7,8),"0-00-00"),"e/mm/dd")】，即可获取身份证的出生年月日格式：2016/09/11。
公式说明：依次上面的几种方法你可能会觉得修改一下中间的符号就可以了。当你修改一下，就知道不行，会出错。没关系，再次嵌套一个TEXT然后指定e/mm/dd就可以了。
======================================================
判断身份证号码的男女性别首先，我们要了解身份证的号码的规则才行。身份证第17位数字表示性别：奇数表示男性，偶数表示女性。知道这一规则后，我们就知道应该从何下手了。
方法一
直接在B2单元格，输入公式：【=IF(MOD(MID(A2,17,1),2),"男","女")】即可。
MID(A2,17,1)说明：截取A2单元格中第17位数；
MOD(MID(A2,17,1),2说明：MOD求余函数，MOD(MID(A2,17,1),2表示中间截取的这个数除以2后是不是奇数；
IF：若为奇数，则为男，否则为女。
方法二
直接在C2单元格，输入公式：【=IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女")】。
LEFT说明：取A2单元格的左边17位；
RIGHT：取上一步结果的右边1位；
MOD：取上一步结果除以2的余数；IF：判断结果是奇数是男，否则是女；
======================================================
利用身份证号码来计算出年龄，当然，这个年龄是根据系统当前时间来计算，也可以你自定义指定时间来计算。这里我提供四种不同的公式方法来实现。
方法一、按当前系统日期计算在B2单元格，输入公式：【=DATEDIF(TEXT(MID(A2,7,8),"#-00-00"),TODAY(),"Y")】
DATEDIF：计算机日期比较函数；
TEXT：将数值转换为按指定数字格式表示的文本；
MID：截取出指定数量的字符；
TODAY：用于返回当前时间；
Y：指计算两个日期的年差数；
方法二、同样也是按照当前系统日期输入公式：【=DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),TODAY(),"y")】。也可以同样的计算出年龄。
方法三、计算精确年龄这种方法是计算出精确年龄，精确到年、月、日、天。公式有点长。
公式：【=DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"y")&"周岁零"&DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"ym")&"月"&DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"md")&"天"】。
方法四、按指定日期计算年龄上面几种方法都是以当前时间来计算出年龄的，这第四种方法是按指定的日期来计算年龄，比如我要计算身份证号码上的年龄一直到2010年01月01日是多少岁。应该输入什么公式呢？
输入公式：【=DATEDIF(TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1,"2010-01-01","y")】
如果想要自定义自己指定日期，可以自由修改下列公式中的【2010-01-01】来实现。
======================================================
也可以通过身份证号码提取出来的出生日期结果再来生成生肖和星座。提取日期后再算出生肖步骤一、先用之前我们讲到的提取身份证出生年月日的方法来提取出一下日期。
公式：【=TEXT(MID(A2,7,8),"0-00-00")】
步骤二、得出了出生年月日以后，我们就可以利用下面函数来生成出生肖了。
公式：【=MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(B2)-4,12)+1,1)】
直接通过身份证号码提取生肖我们除了生成日期后提取，还可以直接将身份证号码生成生肖，这样的话，也是省去了一个步骤。
公式：【=IF(LEN(A2)=15,MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD("19"&MID(A2,7,2)-4,12)+1,1),MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(A2,7,4)-4,12)+1,1))】
提取日期后再生成星座同样的先用公式提取日期，再用提取出来的结果生成生肖。
提取日期函数公式：【=TEXT(MID(A2,7,8),"0-00-00")】
用日期结果生成星座函数公式：【=LOOKUP(--TEXT(B2,"mdd"),{101,"摩羯座";120,"水瓶座";219,"双鱼座";321,"白羊座";420,"金牛座";521,"双子座";621,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1023,"天蝎座";1122,"射手座";1222,"摩羯座"})】
直接通过身份证号码提取星座同样的，下面这个公式可以直接通过身份证号码来生成出星座。
公式：【=VLOOKUP(VALUE("1900-"&TEXT(MID(A2,LEN(A2)/2+2,4),"#-##")),{1,"摩羯座";20,"水瓶座";50,"双鱼座";81,"白羊座";111,"金牛座";142,"双子座";174,"巨蟹座";205,"狮子座";236,"处女座";267,"天秤座";298,"天蝎座";328,"射手座";357,"摩羯座"},2,TRUE)】