身份证性别年龄(excel最精确计算年龄的公式)

合集下载

exelce表格年龄计算公式

exelce表格年龄计算公式

exelce表格年龄计算公式
在Excel表格中计算年龄,有多种公式可以使用。

以下提供两种常用的方法:
方法一:使用DATEDIF函数
1.在需要计算的单元格输入“=DATEDIF(B2,TODAY(),"y")”,其中“B2”是出生
日期所在的单元格名称,“TODAY()”是获取当前日期的函数,“y”代表返回值是年。

2.按回车键即可得出计算结果。

方法二:通过当前日期减去出生日期
1.首先从身份证号中提取出生日期。

假设身份证号在B列,从B2到B7。

在C2单
元格输入函数公式“=TEXT(MID(B2,7,8),"00-00-00")”,然后按
【Ctrl+Enter】结束确认,即可批量提取出员工身份证中的出生日期。

2.然后只要用当前日期减去出生日期,便可以计算出身份证中的年龄。

在D2单元
格输入函数公式“=DATEDIF(TEXT(MID(B2,7,8),"00-00-
00"),TODAY(),"y")”,即可计算出身份证号中的年龄。

以上就是在Excel表格中计算年龄的公式,希望对您有所帮助。

在EXCEL中根据身份证号计算出生年月日、年龄

在EXCEL中根据身份证号计算出生年月日、年龄

在EXCEL中根据身份证号计算年龄、出生年月、性别在EXCEL中如何根据身份证号计算年龄、出生年月、性别呢?下面来介绍一下,希望对您有帮助1.根据身份证号计算年龄=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(L EN(A2)=18)*2),"#-00-00"),TODAY(),"y"),)注意:A2单元格为身份证号,且为文本格式。

2.根据身份证号计算出生年月日第一种,计算出来的格式为××年××月××日=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月"&MID(A2,11,2)&"日",MID(A2,7,4)&"年"&MID(A2,11,2)&"月"&MID(A2,13,2)&"日")第二种,计算出来的格式为××年××月=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月",MID(A2,7,4)&"年"&MID(A2,11,2)&"月")3.根据身份证号计算性别=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,"男","女")计算出生年月日的公式=TEXT(TEXT(MID(D2,7,LEN(D2)/2-1),"#-00-00"),"e-m-d") D2为身份证。

从身份证号码中提取出生日期性别年龄公式-身份证号性别公式

从身份证号码中提取出生日期性别年龄公式-身份证号性别公式

1、从身份证号码中提取出生日期性别年龄公式:=DATE(MID(A1,7,4), MID(A1,11,2), MID(A1,13,2))2、从身份证号码中提取出性别公式:=IF(MOD(MID(A1,17,1),2)=1,"男","女")②将光标定位在“出生日期”单元格(E2)中,然后在单元格中输入函数公式“=MI D(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。

关于这个函数公式的具体说明:MID函数用于从数据中间提取字符,它的格式是:MID3、确定“年龄”:“出生日期”确定后,年龄则可以利用一个简单的函数公式计算出来了:将光标定位在“年龄”单元格中,然后在单元格中输入函数公式“=INT((TODAY()-E2)/3 65)”即可计算出“年龄”。

关于这个函数公式的具体说明④计算以后可能有多位小数,可以用【减少小数位数】按钮,将年龄的数值变成“整数”,也可在公式= (TODAY()-E2)/365中再嵌套一个“INT”函数取整数,即“ =INT((TODAY()-E2)/365)”,这样就会自动将后面的小数去掉,只保留整数部分。

操作效果如下图:4、确定“性别”:每个人的性别可以利用“身份证号码”进行判断,18位身份证号码中,第15~1 7位为顺序号,奇数为男,偶数为女。

=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),"女","男")的含义是若是“偶数”就填写“女”,若是“奇数”就填写“男”。

操作效果如下图:。

在EXCEL中怎样用身份证号计算年龄,性别及出生年月、查重

在EXCEL中怎样用身份证号计算年龄,性别及出生年月、查重

在EXCEL中怎样用身份证号计算年龄假设身份证号码在A1单元格 B1单元格输入公式=datedif(text(mid(a1,7,len(a2)/2-1),"0-00-00"), now(),"y") 此公式适用于15位和18位号码假设身份证号在A1单元格都是18位的在B1单元格输入公式按月份=year(today())-year(--text(mid(A1,7,8),"0-00-00"))或按年=datedif(--text(mid(A1,7,8),"0-00-00"),today(),"Y")在Excel中自动推测出生年月日及性别的技巧大家都知道,身份证号码已经包含了每个人的出生年月日及性别等方面的信息(对于老式的15位身份证而言,7-12位即个人的出生年月日,而最后一位奇数或偶数则分别表示男性或女性。

如某人的身份证号码为420400*********,它的7-12位为700101,这就表示该人是1970年元月1日出生的,身份证的最后一位为奇数1,这就表示该人为男性;对于新式的18位身份证而言,7-14位代表个人的出身年月日,而倒数第二位的奇数或偶数则分别表示男性或女性)。

根据身份证号码的这些排列规律,结合Excel的有关函数,我们就能实现利用身份证号码自动输入出生年月日及性别等信息的目的,减轻日常输入的工作量。

Excel中提供了一个名为MID的函数,其作用就是返回文本串中从指定位置开始特定数目的字符,该数目由用户指定(另有一个名为MIDB的函数,其作用与MID完全一样,不过MID仅适用于单字节文字,而MIDB函数则可用于汉字等双字节字符),利用该功能我们就能从身份证号码中分别取出个人的出生年份、月份及日期,然后再加以适当的合并处理即可得出个人的出生年月日信息。

提示:MID函数的格式为MID(text,start_num,num_chars)或MIDB(text,start_num,num_bytes),其中Text是包含要提取字符的文本串;Start_num是文本中要提取的第一个字符的位置(文本中第一个字符的start_num为1,第二个为2……以此类推);至于Num_chars则是指定希望MID从文本中返回字符的个数。

身份证性别年龄(excel最精确计算年龄的公式)

身份证性别年龄(excel最精确计算年龄的公式)

EXCLE中最精确的计算年龄的公式中午一个同事请教我有关EXCLE自动计算年龄的方法,当时告诉她应该有一堆公式但是一时没有谁能记得清楚,答应他回来以后上网查查。

到网上一搜,大失所望。

几乎没有一种方法是精确的。

网上搜到的公式大概有这么几种:1、计算出生日期到某一指定日期(一般选用某年的最后一天入2006年12月31日)的的天数,然后除以360 ,得到一个数值,然后用 int()函数取整,得出需要的年龄。

一般使用的公式如下:=IF(C12="","",INT(DAYS360(C12,"2006-12-31")/360))聪明一点的人知道使用这个公式,=IF(C12="","",INT(DAYS360(C12,TODAY())/360))这个方法,这个公式的弊端在于,一、将每个月默认为30天去计算两个日期之间的天数,二、将每年默认为360天去计算年龄。

这种方法显然不精确。

2、年份直接相减计算周岁=YEAR(NOW())-YEAR(C12)计算虚岁=YEAR(NOW())-YEAR(C12)+1这种算法的精确程度显而易见,粗略估算还算可以。

3、使用DATEDIF函数这种方法与第一种方法采用了相同的思路,但是其的精确程度显然比第一种方法要高,这取决于DATEDIF函数本身的精确性。

=IF(C12="","",INT(DATEDIF(C12,"1983-3-20","D")/365))或者,=IF(C12="","",INT(DATEDIF(C12,now(),"D")/365))这种方法强行将一年固定为365天,我们知道通常情况每个四年就有一年是366天,所以这种算法也不是很精确。

Excel中根据身份证号求年龄性别和出生日期

Excel中根据身份证号求年龄性别和出生日期

Excel中根据身份证号求年龄性别和出生日期在工作当中有很多表格涉及到个人信息,比如年龄、性别、出生日期等。

如果我们能根据身份证号自动生成其年龄、性别、出生日期等信息,那样会提高我们的工作效率,大大缩短我们的工作时间。

本文将教你根据身份证号利用公式求出相应个人的年龄、性别、出生日期。

1、身份证号的输入如果是在默认单元格格式情况下输入身份证号会发现表格里显示为类似“1.10225E+17”的科学计数情况。

要解决这种情况,可以采取以下两种方法:一是切换输入到英文状态,输入前加个’(一定要是英文输入法下的单引号),再输入数字;二是选中表格中要输入身份证号的列,鼠标右击——“设置单元格格式”——“数字”选择“文本”。

2、求性别身份证号的第17位表示性别信息。

单数为男,双数为女。

这样只要判断第17位数的单双即可。

可以在性别的单元格(C2)下输入=IF(MOD(MID(B2,17,1),2),"男","女")。

其中“B2”为身份证所在的单元格。

可以根据自己需要设置为相应的单元格即可。

MID(B2,17,1)表示从B2单元格中字符串的第17位开始提取1位数字,本例中表示提取身份证号码的第17位数字。

MOD(MID(B2,17,1),2)用于得到给出数字除以指定数字后的余数,本例表示对提出来的数值除以2以后所得到的余数。

IF(MOD(MID(B2,17,1),2),"男","女")则表示如果除以2以后的余数是1(为真),那么性格单元格(C2)显示为“男”,否则显示为“女”。

回车确认后,即可在C2单元格显示正确的性别信息。

3、求年龄这个比较简单,网上有很多公式,但都很复杂,我们的思路就是用当前年份减去出生年份即可。

在Excel中获取当前年份的函数是=year(now())。

所以我们的公式为:=YEAR(NOW())-MID(B2,7,4)4、求出生日期如果说我们规定要把出生日期按照类似“1985-01-28”的形式输出,则用公式=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2),其中,MID(B2,7,4)表示从B2单元格中字符串的第7位开始提取4位数字,即出生年。

身份证号码换算年龄、性别、出生日期的公式

身份证号码换算年龄、性别、出生日期的公式

身份证号码换算年龄、性别、出生日期的公式15位和18位的身份证号都合适的了。

设内容如下:........A列......B列......C列......D列....1 身份证号性别出生年月年龄2(输入身份证号)算年龄的:“=IF(OR(LEN(A2)=15,LEN(A2)=18),RIGHT(IF(IF(LEN(A2)=15, MID(A2,9,2),MID(A2,11,2) )*100+IF(LEN(A2)=15,MID(A2,11,2),MI D(A2,13,2))>MONTH(TODAY())*100+DAY(TODAY()),YEAR(TODAY())-IF(LEN(A2)=15,MID(A2,7,2),MID(A2,9,2))-1,YEAR(TODAY())-IF(L EN(A2)=15,MID(A2,7,2),MID(A2,9,2))),2),"证号错误")”算性别的:“=IF(LEN(A2)=15,(IF(OR(ABS(RIGHT(A2))=1,ABS(RIGHT(A2) )=3,ABS(RIGHT(A2))=5,A BS(RIGHT(A2))=7,ABS(RIGHT(A2))=9),"男","女")),(IF(OR(ABS(MID(A2,17,1))=1,ABS(MID(A2,17,1))=3,ABS(M ID(A2,17,1))=5,ABS(MID( A2,17,1))=7,ABS(MID(A2,17,1))=9),"男","女")))”算出生年月的:“=DATE(IF(LEN(E3)=15,MID(E3,7,2),MID(E3,9,2)),IF(LEN(E3) =15,MID(E3,9,2),MID(E3,11,2)),IF(LEN(E3)=15,MID(E3,11,2),MID(E3,13,2)))”只要把输入身份证号的单元格格式设为“文本”就是最关键的,准确。

EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)

EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)

EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)EXCEL根据身份证号码自动提取性别、出生年月日和年龄(通用公式)这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式,供需要的网友参考:说明:公式中的B2是身份证号1、根据身份证号码求性别:=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错"))2、根据身份证号码求出生年月日:=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2, 9,2)),IF(LEN( B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2) ),"身份证错"))3、根据身份证号码求年龄:=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year (now())-VALUE(MID(B2,7,4)),"身份证错"))其他通用公式:(新旧身份证号通用)(1)、根据“身份证号”获得“年龄”A1为身份证号,B1中输入公式=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=1 8)*2),"#-00-00"), TODAY(),"y")(2)、根据出生“年-月-日”获得“年龄”B1为出生日期,C1中输入公式=INT((TODAY()-B1)/365)或者=DATEDIF(B1,TODAY(),"Y")判断身份证格式是否正确:B1为身份证号,C1中输入公式=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MON TH(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1* (MID(B1,11,2)),DAY(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1 * (MID(B1,13,2))))<>1*(MID(B1,13,2))),"月日错误",""),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2))+19 00,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,9,2)),DAY(DATE(1* (MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1* (MID(B1,11,2))),"月日错误",""))),"身份证位数不对")&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),"年份错误","")。

EXCEL根据身份证号码自动提取年龄

EXCEL根据身份证号码自动提取年龄

EXCEL根据身份证号码自动提取年龄、性别、出生年月的公式(15位、18位通用)EXCEL根据身份证号码自动提取年龄、性别、出生年月的公式(15位、18位通用)说明:以身份证号输入到B2单元格为例,如果身份证号输入到F4单元格,请把公式中的B2替换为F4。

1、提取年龄公式(15位、18位通用)公式1:返回虚岁,生日前后返回同样的虚岁年龄=IF(LEN(B2)=0,"",IF(LEN(B2)=15,year(now())-1900-VALUE(MI D(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错")))公式2:返回周岁,生日前后返回不同的周岁年龄=IF(LEN(B2)=0,"",IF(LEN(B2)=15,DATEDIF(DATE(CONCATE NATE("19",MID(B2,7,2)),MID(B2,9,2),MID(B2,11,2)),TODAY(),"Y") ,IF(LEN(B2)=18,DATEDIF(DATE(MID(B2,7,4),MID(B2,11,2),MID(B 2,13,2)),TODAY(),"Y"),"身份证错")))函数说明:LEN返回文本字符串中的字符个数。

DATEDIF()计算两个日期之间的天数、月数或年数。

DATE返回代表特定日期的序列号。

CONCATENATE将多个文本字符串合并成一个文本字符串。

MID从文本字符串中指定的位置开始,返回指定长度的字符串。

TODAY返回日期格式的当前日期。

2、提取性别的公式(15位、18位通用)=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),IF(LEN(B2)=0,"","身份证错")))函数说明:LEN返回文本字符串中的字符个数。

Excel中常见与身份证有关的公式

Excel中常见与身份证有关的公式

1.根据身份证号计算年龄=IF(D2<>"",DA TEDIF(TEXT((LEN(D2)=15)*19&MID(D2,7,6+(LEN(D2)=18)*2),"#-00-00"),T ODAY(),"y"),)注意:D2单元格为身份证号,且为文本格式。

2.根据身份证号计算出生年月日第一种,计算出来的格式为××年××月××日=IF(LEN(D2)=15,"19"&MID(D2,7,2)&"年"&MID(D2,9,2)&"月"&MID(D2,11,2)&"日",MID(D2,7,4)&"年"&MID(D2,11,2)&"月"&MID(D2,13,2)&"日")或者=IF(LEN(D2)=15,19,"")&TEXT(MID D2,7,8-(LEN(D2)=15)*2),"#年00月00日")第二种,计算出来的格式为××年××月=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"年"&MID(A2,9,2)&"月",MID(A2,7,4)&"年"&MID(A2,11,2)&"月")第三种计算出来的格式为2011/1/1=MID(B11,7,4)&"/"&MID(B11,11,2)&"/"&MID(B11,13,2)3.根据身份证号计算性别=IF(MOD(IF(LEN(D2)=15,MID(D2,15,1),MID(D2,17,1)),2)=1,"男","女")4.身份证号全部改为18位(输出正确18位),输入数组公式(ctrl+shift+enter结尾):=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2 ,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)5.以18位身份证为准输出15位身份证号:=IF(LEN(G2)=15,G2,LEFT(REPLACE(G2,7,2,),15))6.验证身份证号(G2为输入18位身份证号,H2为输出正确18位身份证号):=IF(G2<>0,IF(LEN(G2)=15,"一代身份证号",(IF(LEN(G2)=18,IF(G2=H2,"正确","未通过验证"),IF(LEN(G2)>18,"超过18位,请核查","身份证号不完整")))),)18位身份证号码转换成出生日期的函数公式:如果E2中是身份证,在F2中求出出生日期,F2=DA TE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))自动录入男女:=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)=0,"女","男") 根据身份证号快速录入男女性别“记住”15/18位都可以的公式:转换出生日期:=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&amp;TE XT(MID(e2,7,6),"#-00-00")) 自动录入男女:=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))计算年龄(新旧身份证号都可以):=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2 ,9,2),107-MIDB(E2,7,2)))。

excel身份证计算性别年龄出生日期姓名重复身份证重复生肖等

excel身份证计算性别年龄出生日期姓名重复身份证重复生肖等

LEN(C2)=15:检查C2单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位。
MID(C2,7,4):从C2单元格中字符串的第7位开始提取四位数字,本例中表示提取15位身份证号码的第7、8、9、10位数字。
MID(C2,9,4):从C2单元格中字符串的第9位开始提取四位数字,本例中表示提取18位身份证号码的第9、10、11、12位数字。
=DATEDIF(TEXT(RIGHT(19&MID(A1,7,LEN(A1)/2-1),8),"#-00-00"),NOW(),"Y")
已有身份证的情况下,公式应加合法性,有效性判断,所以公式改为:
出生日期:
=IF(A1="","",IF(AND(LEN(A1)<>15,LEN(A1)<>18),"错误",IF(ISERR(1*TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00")),"错误",IF(OR(1*TEXT
身份证第18位(校验码)的计算方法
1、将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为:7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2。
2、将这17位数字和系数相乘的结果相加。
3、再用求和数除以11,看余数是多少?
4、余数只可能有0-1-2-3-4-5-6-7-8-9-10这11个数字。其分别对应的最后一位身份证的号码为1-0-X-9-8-7-6-5-4-3-2。
替换哪两个数字?可以用公式:
=replace(A1,n,2,"s")

Excel如何根据身份证号码自动计算年龄

Excel如何根据身份证号码自动计算年龄

Excel如何根据身份证号码自动计算年龄
Excel如何根据身份证号码自动计算年龄方法1
1.打开要在excel中编辑的表格
2.如图所示,在身份证号后面的空格即年份一列第一格输入公式=MID(A2,7,4),输入完成后按下enter键,A2指身份证号的单元格,数字7为数字开始位置,4为字符个数
3.按下enter键后,如图所示年份一栏已显示出出生年份
4.如图所示,选中已显示年份的一格,鼠标点击绿色框右下角的小方框并下拉至身份证号的最后一栏
5.如图所示,每个身份证号对应的年份都显示出来了
6.如图所示再在年龄一列第一格输入公式2018-MID(A2,7,4),按下enter键
7.即可看到年龄已被计算出来为21岁,如图所示鼠标点击绿色框右下角的小方框并下拉至身份证号的最后一栏
8.如图所示,用这种“自动填充”功能,就能让同类型单元格有同样的公式计算结果
方法2
1.打开要在excel中编辑的表格,并选中年龄那一列的第一格
2.点击公式
3.再点击插入函数
4.在弹出来的对话框中在选择函数那一栏点击全部
5.下拉右侧的滚动条找到MID函数点击它
6.点击右下角的确定
7.在弹出来的对话框中点击第一格
8.然后点击Excel文档中的A2单元格
9.在第二格中输入数字7,表示数字开始位置
10.在第三格中输入数字4,表示字符个数为四个
11.最后单击确定
12.即可看到该身份证号的年份已经算出来了
13.然后在该公示前输入2018-即公式2018-MID(A2,7,4)按下enter键
14.即可看到年龄计算出来为21岁,下拉该单元格右下角的小黑方使下面的单元格拥有同样的计算格式
15.最后即可看到所有的年龄就被计算出来了。

身份证号码提出生年月性别年龄方法公式

身份证号码提出生年月性别年龄方法公式

一、提取出生日期如果我们要从一个人的身份证号码中批量提取其出生年月日,并表示成“yyyy-mm-dd”形式,可以这样做,假设身份证号码在C列,在D列中输入公式=IF(LEN(C6)=15,"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2),MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2))这个公式的含义就是,当其检查到C6单元格中的数据是15位的时,就显示"19"&MID(C6,7,2)&"-"&MID(C6,9,2)&"-"&MID(C6,11,2)的计算结果,否则就显示MID(C6,7,4)&"-"&MID(C6,11,2)&"-"&MID(C6,13,2)的计算结果。

如:若C6单元格中是441000*********,在D6单元格中计算出的结果是“1950-01-12”;若C6单元格中是410005************,在D6单元格中计算出的结果是“1945-11-16”。

二、提取性别在E6单元格输入公式=IF(LEN(C6)=15,IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),"女","男"),IF(MID(C6,17,1)/2=INT(MID(这个公式的含义是如果C6单元格是一个15位数,就显示IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),"女","男")的计算结果;否则,显示IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),"女","男"))的计算结果。

excel如何根据身份证号计算男女性别和年龄

excel如何根据身份证号计算男女性别和年龄

如何在Excel中根据身份证号计算年龄和性别。

操作步骤:
1. 首先看一下原始数据,本例以18位身份证号为例。

A列是18位的身份
证号,需要在B列和C列分别提计算出年龄和性别信息。

2. 18位身份证号从第7位开始的8位数字代表出生日期。

出生的年份是从
第7位开始的4位数字,因此,可以用MID函数将出生年份提取出来。

双击B2,输入公式:
=MID(A2,7,4)
3. 要求年龄,需要用当前的年份减去身份证上的出生年份。

我们可以用
YEAR函数来取得当前的年份,例如输入:=YEAR(NOW())就可以返回当前的年份2014。

4. 将上述两个部分合并,就得到了B2的最终公式:
=YEAR(NOW())-MID(A2,7,4)
5. 如何判断男女。

18位身份证号的第17位是判断性别的数字,奇数代表男
性,偶数代表女性。

首先,我们还是用MID函数将第17位数字提取出来,输入公式:=MID(A2,17,1)
6. 利用MOD函数(MOD函数是取余数的函数)取第17位数字除以2的
余数,如果余数是0,则第17位是偶数,也就是该身份证是女性;反之,如果余数是1则说明身份证是男性。

嵌套IF函数如下:
=IF(MOD(MID(A2,17,1),2),"男","女")。

  1. 1、下载文档前请自行甄别文档内容的完整性,平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益,请联系客服反馈,我们会尽快为您处理(人工客服工作时间:9:00-18:30)。

EXCLE中最精确的计算年龄的公式中午一个同事请教我有关EXCLE自动计算年龄的方法,当时告诉她应该有一堆公式但是一时没有谁能记得清楚,答应他回来以后上网查查。

到网上一搜,大失所望。

几乎没有一种方法是精确的。

网上搜到的公式大概有这么几种:1、计算出生日期到某一指定日期(一般选用某年的最后一天入2006年12月31日)的的天数,然后除以360 ,得到一个数值,然后用 int()函数取整,得出需要的年龄。

一般使用的公式如下:=IF(C12="","",INT(DAYS360(C12,"2006-12-31")/360))聪明一点的人知道使用这个公式,=IF(C12="","",INT(DAYS360(C12,TODAY())/360))这个方法,这个公式的弊端在于,一、将每个月默认为30天去计算两个日期之间的天数,二、将每年默认为360天去计算年龄。

这种方法显然不精确。

2、年份直接相减计算周岁=YEAR(NOW())-YEAR(C12)计算虚岁=YEAR(NOW())-YEAR(C12)+1这种算法的精确程度显而易见,粗略估算还算可以。

3、使用DATEDIF函数这种方法与第一种方法采用了相同的思路,但是其的精确程度显然比第一种方法要高,这取决于DATEDIF函数本身的精确性。

=IF(C12="","",INT(DATEDIF(C12,"1983-3-20","D")/365))或者,=IF(C12="","",INT(DATEDIF(C12,now(),"D")/365))这种方法强行将一年固定为365天,我们知道通常情况每个四年就有一年是366天,所以这种算法也不是很精确。

通过认真分析,我觉得只有结合我们计算年龄的实际方法,才能编制出准确无误的公式。

首先分析人们计算年龄的方法。

例如某人系1983年3月20日生人,如果要在2007年3月23日这天计算他的年龄,通常采用这样的方法。

首先,人们会用2007减去1983得出的年龄为24岁,然后再看看他“满没满”24岁,就是看看出生的月份和日期比今天早还是晚,如果出生日期晚于今天则表示没有满,那么他的年龄就应该是2007-1983-1=23岁。

如果出生日期早于今天或者就是今天,就说明他已经满了24岁或者正好满24岁,则他的年龄就是2007-1983=24岁。

分析清楚了计算年龄的过程我们再根据这个过程编写公式就很容易了。

综上所述,我编写了如下公式,在实际应用中将公式中所有的C12替换为你所使用的出生日期所在的表格行号列号组合即可。

如(A1,B2等等)=IF(MONTH(NOW())<MONTH(C12),INT(YEAR(NOW())-YEAR(C12))-1,IF(MONTH(NOW())>MONTH(C 12),YEAR(NOW())-YEAR(C12),IF(DAY(NOW())>=DAY(C12),YEAR(NOW())-YEAR(C12),YEAR(NOW ())-YEAR(C12)-1)))公式说明IF ( MONTH(NOW())<MONTH(C12) , INT(YEAR(NOW())-YEAR(C12))-1 ,//如果当前日期的月份小于所需计算日期的月份,则表示今年没有过生日,年龄数为YEAR(NOW())-YEAR(C12)-1。

如果不是这种情况,进入下面的判断IF ( MONTH (NOW())>MONTH(C12) , YEAR(NOW())-YEAR(C12) ,//如果当前日期的月份大于所需计算日期的月份,则表示今年已经过生日,年龄数为YEAR(NOW())-YEAR(C12)。

如果也不是这种情况,则表示这两个月份相等,进入下面的判断IF ( DAY(NOW())>=DAY(C12) , YEAR(NOW())-YEAR(C12) ,//如果今天的日期大于或者等于出生年月日中的日期,则表示已经过了生日,或者正在过生日,则年龄数为YEAR(NOW())-YEAR(C12),否则表示今年没有过生日,年龄数应该选择下面的公式,大小为YEAR(NOW())-YEAR(C12)-12012-10-30参考知识:IF请参阅执行真假值判断,根据逻辑计算的真假值,返回不同结果。

可以使用函数 IF 对数值和公式进行条件检测。

语法IF(logical_test,value_if_true,value_if_false)Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。

例如,A10=100 就是一个逻辑表达式,如果单元格 A10 中的值等于 100,表达式即为 TRUE,否则为 FALSE。

本参数可使用任何比较运算符。

Value_if_true logical_test 为 TRUE 时返回的值。

例如,如果本参数为文本字符串“预算内”而且 logical_test 参数值为 TRUE,则 IF 函数将显示文本“预算内”。

如果logical_test 为 TRUE 而 value_if_true 为空,则本参数返回 0(零)。

如果要显示 TRUE,则请为本参数使用逻辑值 TRUE。

Value_if_true 也可以是其他公式。

Value_if_false logical_test 为 FALSE 时返回的值。

例如,如果本参数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE,则 IF 函数将显示文本“超出预算”。

如果 logical_test 为 FALSE 且忽略了 Value_if_false(即 value_if_true 后没有逗号),则会返回逻辑值 FALSE。

如果 logical_test 为 FALSE 且 Value_if_false 为空(即value_if_true 后有逗号,并紧跟着右括号),则本参数返回 0(零)。

Value_if_false 也可以是其他公式。

说明•函数 IF 可以嵌套七层,用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。

请参阅下面最后一个示例。

•在计算参数 value_if_true 和 value_if_false 后,函数 IF 返回相应语句执行后的返回值。

•如果函数 IF 的参数包含数组,则在执行 IF 语句时,数组中的每一个元素都将计算。

•Microsoft Excel 还提供了其他一些函数,可依据条件来分析数据。

例如,如果要计算单元格区域中某个文本字符串或数字出现的次数,则可使用 COUNTIF 工作表函数。

如果要根据单元格区域中的某一文本字符串或数字求和,则可使用 SUMIF 工作表函数。

请了解关于根据条件计算值。

DATEDIF参阅: 计算两个日期之间的天数、月数或年数。

提供此函数是为了与 Lotus 1-2-3 兼容。

语法: DATEDIF(start_date,end_date,unit)Start_date 为一个日期,它代表时间段内的第一个日期或起始日期。

日期有多种输入方法:带引号的文本串(例如 "2001/1/30")、系列数(例如,如果使用 1900 日期系统则 36921 代表 2001 年 1 月 30 日)或其他公式或函数的结果(例如,DATEVALUE("2001/1/30"))。

有关日期系列数的详细信息,请参阅 NOW。

End_date 为一个日期,它代表时间段内的最后一个日期或结束日期。

Unit 为所需信息的返回类型。

Unit 返回"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"YM" start_date 与 end_date 日期中月数的差。

忽略日期中的日和年。

"MD" start_date 与 end_date 日期中天数的差。

忽略日期中的月和年。

"YD" start_date 与 end_date 日期中天数的差。

忽略日期中的年。

说明:Microsoft Excel 按顺序的系列数保存日期,这样就可以对其进行计算。

如果工作簿使用 1900 日期系统,则 Excel 会将 1900 年 1 月 1 日保存为系列数 1。

而如果工作簿使用 1904 日期系统,则 Excel 会将 1904 年 1 月 1 日保存为系列数 0,(而将 1904 年 1 月 2 日保存为系列数 1)。

例如,在 1900 日期系统中 Excel 将 1998 年 1 月 1 日保存为系列数 35796,因为该日期距离 1900 年 1 月 1 日为 35795 天。

请查阅 Microsoft Excel 如何存储日期和时间。

Excel for Windows 和 Excel for Macintosh 使用不同的默认日期系统。

有关详细信息请参阅 NOW。

示例:DATEDIF("2001/1/1","2003/1/1","Y") 等于 2,即时间段中有两个整年。

DATEDIF("2001/6/1","2002/8/15","D") 等于 440,即在 2001 年 6 月 1 日和 2002 年 8 月 15 日之间有 440 天。

DATEDIF("2001/6/1","2002/8/15","YD") 等于 75,即在 6 月 1 日与 8 月 15 日之间有 75 天,忽略日期中的年。

相关文档
最新文档