第一篇:身份证号码提取年龄,出生日期,等
身份证号码提取年龄
=IF(LEN(A1)>15,YEAR(NOW())-MID(A1,7,4)+1,YEAR(NOW())-(MID(A1,7,2)+1900)+1)假设A1存放的号码,B1输出结果,那么在B1中输入:
=IF(LEN(TRIM(A1))=18,YEAR(TODAY())-MID(A1,7,4),RIGHT(YEAR(TODAY()),2)+100-MID(A1,7,2))
公式考虑了身份证号码是15位还是18位的问题
=YEAR(NOW())-MID(A1,7,4)1 用mid函数提取出生年份,然后用现在身份证号码在A列(在A1单元格里)B1输入 =MID(A1,7,8)回车后 公式
例如:
身份证号***000(填写在A1)在B1除填写公式:
=TEXT(TODAY(),“YYYY”)-(IF(LEN(A1)=18,“",19)&LEFT(REPLACE(A1,1,6,”“),2+(LEN(A1)=18)*2))回车后,B1将显示:26
根据身份证号码(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)),”身份证错“))
通过身份证号取得出身日期,年龄,是很多人力资源朋友遇到的问题;在这里个人的一些小经验分享给大家; 工具/原料
Excel 2000及以上版本
方法/步骤
1.1 新建Excel文档;
2.2 根据需要,在新建的表头的上分别输入,员工姓名,身份证号,出生日期,年龄,入职日期,服务年限
3.3 将员工的姓名及身份证号从准备好的档案中copy过来;
4.4 在第二行,第二列出生日期单元格中,输入:
=IF(LEN(B2)<18;(DATE((”19“&MID(B2;7;2));MID(B2;9;2);MID(B2;11;2)));DATE(MID(B2;7;4);MID(B2;11;2);MID(B2;13;2)))
注:LEN(B2)<18,是判断身份证号是15位还是18位的;
DATE(YYYY;MM;DD),是将取得的年、月、日转换成时间格式;
MID(text;start_num;num_chars),功能是根据text的内容,截取字符串;start_num:截取第1个字符所在的位置;num_chars:截取的字符数量。例如:Mid(b2;7;2),表示从身份证的第7位开始,截取2个字符;
5.5 在第二行的,第三列单元格中,输入: =DATEDIF(C2;TODAY();”y“)这个方法是计算到当前日期,员工的年龄;
6.6 在第二行的,第四列单元格中,输入:员工到岗日期,单元格格式设为日期格式;即:YYYY-MM-DD
7.7 在第二行的,第五列单元格中,输入: =DATEDIF(E2;TODAY();”y")即可计算到当前日期的,服务年限
8.8 选中C2,D2,复制单元格;
9.9 选中C列,D列剩余的单元格,选粘贴,即可自动计算每个员工的出生日期及年龄;
第二篇:由身份证号码求出生日期、年龄、性别
excel中由身份证号求出生日期、性别、年龄
(1)根据身份证号码求性别 :=IF(VALUE(RIGHT(B2,3))/2=INT(VALUE(RIGHT(B2,3))/2),“女”,“男”)
(2)根据身份证号码求出生日期:
=CONCATENATE(MID(B2,7,4),“年”,MID(B2,11,2),“月”,MID(B2,13,2),“日”)
(3)根据身份证号码求出年龄:=CONCATENATE(DATEDIF(D2,TODAY(),“y”),“周岁”)
或 =DATEDIF(D3,TODAY(),“y”)
1、身份证号码相关知识
在了解如何实现自动从身份证号码中提取出生年月、性别信息之前,首先需要了解身份证号码所代表的含义。我们知道,当今的身份证号码有15/18位之分。早期签发的身份证号码是15位的,现在签发的身份证由于年份的扩展(由两位变为四位)和末尾加了效验码,就成了18位。这两种身份证号码将在相当长的一段时期内共存。两种身份证号码的含义如下:
(1)15位的身份证号码:1~6位为地区代码,7~8位为出生年份(2位),9~10位为出生月份,11~12位为出生日期,第13~15位为顺序号,并能够判断性别,奇数为男,偶数为女。
(2)18位的身份证号码:1~6位为地区代码,7~10位为出生年份(4位),11~12位为出生月份,13~14位为出生日期,第15~17位为顺序号,并能够判断性别,奇数为男,偶数为女。18位为效验位。
2、应用函数
在此例中为了实现数据的自动提取,应用了如下几个Excel函数。(1)IF函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。
语法形式为:IF(logical_test, value_if_true,value_if_false)(2)CONCATENATE:将若干个文字项合并至一个文字项中。语法形式为:CONCATENATE(text1,text2……)
(3)MID:从文本字符串中指定的起始位置起,返回指定长度的字符。语法形式为:MID(text,start_num,num_chars)(4)TODAY:返回计算机系统内部的当前日期。语法形式为:TODAY()
(5)DATEDIF:计算两个日期之间的天数、月数或年数。语法形式为:DATEDIF(start_date,end_date,unit)(6)VALUE:将代表数字的文字串转换成数字。语法形式为:VALUE(text)(7)RIGHT:根据所指定的字符数返回文本串中最后一个或多个字符。语法形式为:RIGHT(text,num_chars)(8)INT:返回实数舍入后的整数值。语法形式为:INT(number)
4、公式写法及解释(以员工Andy为例说明)
说明:为避免公式中过多的嵌套,这里的身份证号码限定为15位的。如果您看懂了公式的话,可以进行简单的修改即可适用于18位的身份证号码,甚至可适用于15、18两者并存的情况。
(1)根据身份证号码求性别
=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)公式解释:a.RIGHT(E4,3)用于求出身份证号码中代表性别的数字,实际求得的为代表数字的字符串
b.VALUE(RIGHT(E4,3)用于将上一步所得的代表数字的字符串转换为数字 c.VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判断这个身份证号码是奇数还是偶数,当然你也可以用Mod函数来做出判断。
d.=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)及如果上述公式判断出这个号码是偶数时,显示“女”,否则,这个号码是奇数的话,则返回“男”。
(2)根据身份证号码求出生日期
=CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))公式解释:a.MID(E4,7,2)为在身份证号码中获取表示年份的数字的字符串 b.MID(E4,9,2)为在身份证号码中获取表示月份的数字的字符串 c.MID(E4,11,2)为在身份证号码中获取表示日期的数字的字符串 d.CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))目的就是将多个字符串合并在一起显示。
(3)根据参加工作时间求年资(即工龄)=CONCATENATE(DATEDIF(F4,TODAY(),“y”),“年”,DATEDIF(F4,TODAY(),“ym”),“个月”)公式解释:
a.TODAY()用于求出系统当前的时间
b.DATEDIF(F4,TODAY(),“y”)用于计算当前系统时间与参加工作时间相差的年份
c.DATEDIF(F4,TODAY(),“ym”)用于计算当前系统时间与参加工作时间相差的月份,忽略日期中的日和年。
d.=CONCATENATE(D
第三篇:Excel中利用身份证号码提取出生日期和性别
Excel中利用身份证号码(15或18位)提取出生日期和性别
如何使用Excel从身份证号码中提取出生日期2009-02-27 22:52例如:从身份证***616中提取出生日期来,如何快速得出?
呵呵,只需使用语句:=DATE(mid(A1,7,4),mid(A1,11,2),mid(A1,13,2))【A1是身份证号码所在单元格】
date()函数,地球人都知道,日期函数;如输入今天的日期=today()
那么,mid函数是什么东东呢?
MID(text,start_num,num_chars)
Text 为包含要提取字符的文本字符串;Start_num 为文本
中要提取的第一个字符的位置。文本中第一个字符的start_num 为1,以此类推;Num_chars指定希望MID 从文本中返回字符的个数。
对身份证号码分析下就知道:***616,出生日期是1992年2月6日;也就是 从字符串(***616)的第7位开始的4位数字表示年,从字符串的第11位开始的2位数字表示月,字符串的第13位开始的2位数字表示日。呵呵,强悍吧!Excel中利用身份证号码(15或18位)提取出生日期和性别
需要的函数:
LEN(C6)=15:检查C6单元格中字符串的字符数目,本例的含义是检查身份证号码的长度是否是15位;
INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数。RIGHT:返回文本字符串最后一个字符开始指定个数的字符;
MID:返回文本字符串指定起始位置起指定长度的字符,MID(C6,7,2)表示:在C3中从左边第七位起提取2位数;
“19”&MID(C6,7,2)表示:在C3中从左边第七位起提取2位数的前面添加19;
„„
&“"&表示:其左右两边所提取出来的数字不用任何符号连接;
&”-“&表示:其左右两边所提取出来的数字间用“-”符号连接。若需要的日期格式是yyyy年mm月dd日,则可以把公式中的“-”分别用“年月日”进行替换就行了。
一、提取出生日期
如果我们要从一个人的身份证号码中批量提取其出生年月日,并表示成“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单元格中是***,在D6单元格中计算出的结果是“1950-01-12”;若C6单元格中是***794,在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,17,1)/2),”女“,”男“))这个公式的含义是如果C6单元格是一个15位数,就显示IF(RIGHT(C6)/2=INT(RIGHT(C6)/2),”女“,”男“)的计算结果;否则,显示IF(MID(C6,17,1)/2=INT(MID(C6,17,1)/2),”女“,”男“))的计算结果。
最后把D6和E6单元格的公式向下拉下来,在C6列输入身份证号码后,出生日期和性别可自动输入了,这样就减少了用户输入数据工作量,提高了办事效率!
根据身份证号码让Excel自动输入出生日期和性别
办公室人事文员有时要输入很多员工的人事资料,每输完臃长的身份证号后又要输入员工出生年月日和性别,这样无疑增加了工作量,有没有办法让出生日期和性别自动输入呢?其实用Excel公式即可轻松解决问题!
众所周知,我国身份证号码里有每个人的出生日期和性别等信息:老式的身份证号是15位数,第7位到12位是出生年月日,最后一位如果是偶数就代表女生,奇数就是代表男性;新式身份证号是18位数,第7位到14位是出生日期,倒数第二位的偶数或奇数代表女性或男性。既然存在这样的规律,用Excel函数就可轻松实现根据身份证号自动输入出生日期和性别。
本例中所需要函数:
LEN:返回文本字符串的个数,本例中用来计算身份证的位数
MID:返回文本字符串指定起始位置起指定长度的字符,本例中用来计算身份证号中出生日期的字符;
INT:返回数值向下取整为最接近的整数,本例中用来判断身份证里数值的奇偶数.RIGHT:返回文本字符串最后一个字符开始指定个数的字符,本例中来计算身份证号最后一位数.如果A3单元格是身份证号码,B3单元格为出生日期,C3单元格为性别,那么在B3中输入公式=IF(LEN(A3)=18,MID(A3,7,4)&”年“&MID(A3,11,2)&”月“&MID(A3,13,2)&”日“,”19“&MID(A3,7,2)&”年“&MID(A3,9,2)&”月“&MID(A3,11,2)&”日“),这个公式的含义是,如果A3单元格是一个18位数IF(LEN(A3)=18)那么从第7位数开始得到的4位数就是年份MID(A3,7,4)后面再加上文字&”年”,月日依此类推,否则,A3单元格为15位数,就执行”19“&MID(A3,7,2)&”年“&MID(A3,9,2)&”月“&MID(A3,11,2)&”日“)含义与18位相似.只是在年MID(A3,7,2)前面要加上"19"
在C3单元格输入公式=IF(LEN(A3)=18,IF(MID(A3,17,1)/2=INT(MID(A3,17,1)/2),”女“,”男“),IF(RIGHT(A3)/2=INT(RIGHT(A3)/2),”女“,”男“))这个公式的含义是如果A3单元格是一个18位数IF(LEN(A3)=18)就执行IF(MID(A3,17,1)/2=INT(MID(A3,17,1)/2),”女“,”男“),其中如果A3单元格第17位数(身份证号倒数第二位)除以二MID(A3,17,1)/2等于一个整数INT(MID(A3,17,1)/2),那么第17位为偶数,即是”女”,否则是奇数,即为”男”;否则A3单元格是15位数,就执行IF(RIGHT(A3)/2=INT(RIGHT(A3)/2),”女“,”男“),其中RIGHT(A3)含义是返回A3单元格从右往左的第一位数,即身份证最后一位数.其余含意跟上面18位数一样.最后把B3和C3单元格的公式向下拉下来,在A3列输入身份证号码后,出生日期和性别可自动输入了,这样就减少了用户输入数据工作量,提高了办事效率!
年龄查找:
A1输入份证号.B1输入下面公式.可以计算此人现在的年纪.=TEXT(DATEDIF(TEXT(IF(LEN(A1)=18,MID(A1,7,8),”19“&MID(A1,7,6)),”0000-00-00“),TODAY(),”Y“),”@")
第四篇:利用身份证号自动提取出生日期和年龄性别
在excel表格中,根据身份证号码就可以自动提取出生年月、性别、年龄。
第一,身份证号码的组成。
当今的身份证号码由18位数字组成:前6位为地址码,第7至14位为出生日期码,第15至17位为顺序码,第18位为校验码。
15~17位为顺序号,其中第17位如果是单数为男性分配码,双数为女性分配码。即通过第17位数字,可以判断性别,奇数为男,偶数为女。
下面以实例的方式讲解如何根据身份证号码自动提取出生年月、性别、年龄。首先,在excel中输入以下表格,实例如下面所示。
A
B
C
D
E
姓名
身份证号码
出生日期
性别
年龄
张三
***786
1972年05月03日
女
第二,根据身份证号码自动提取出生年月
在目标单元格输入公式:
=MID(B2,7,4)&“年”&MID(B2,11,2)&“月”&MID(B2,13,2)&“日”,回车确定,单元格内会出现与身份证号码相关的出生日期。其它单元格的填充,不必一一输入公式,只需选中该单元格复制,在其它单元格内粘贴即可。
提示:MID函数是提供的一个“从字符串中提取部分字符”的函数命令。
第三,根据身份证号码自动提取性别的方法
在目标单元格输入公式:
=IF(MOD(RIGHT(LEFT(B2,17)),2),“男”,“女”)
提示:IF函数:根据逻辑表达式测试的结果,返回相应的值。IF函数允许嵌套。
RIGHT函数:根据所指定的字符数返回文本串中最后一个或多个字符。
第四,根据身份证号码自动提取年龄的方法
在目标单元格输入公式:=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),“#-00-00”),NOW(),“y”),回车确定。
第五篇:Excel表格身份证号码提取出生日期的公式
Excel表格身份证号码提取出生日期的公式
(B2表示身份证号码所在的列位置)
=MID(B2,7,4)&“-”&MID(B2,11,2)&“-”&MID(B2,13,2)回车→向下填充
1.Excel表中用身份证号码中取其中的号码用:MID(文本,开始字符,所取字符数);
2.15位身份证号从第7位到第12位是出生年月日,年份用的是2位数。
3.18位身份证号从第7位到第14位是出生的年月日,年份用的是4位数。
一、提取出生年月: A、15位身份证号码:
=MID(B2,7,2)&“-”&MID(B2,9,2)&“-”&MID(B2,11,2)回车确认即可。
B、18位身份证号码:
=MID(B2,7,4)&“年”&MID(B2,11,2)&“月”&MID(B2,13,2)&”日”回车确认即可。
二、提取性别: 18位身份证号码:
=IF(MOD(MID(B2,17,1),2)=1,“男”,“女”)回车确认即可。
*excel公式中 =IF(MOD(MID(E4,17,1),2)=0,“女”,“男”)是什么意思? IF是选择函数,当MOD(MID(E4,17,1),2)=0成立时,单元格显示“女”,否则显示“男”。
MOD是取模函数,即是一个求余函数,求MID(E4,17,1)除以2的余数。实质是判断MID(E4,17,1)的奇偶性。
MID从一个文本字符串的指定位置开始,截取指定数目的字符。MID(E4,17,1)是从E4单元格的文本中的第17个字符开始,取一个字符。
三、提取年龄:
=year(today())-value(right(left(B2,10),4))回车确认即可。学生的年龄是这样计算的:2000.08算8岁,而2000.09就算成7岁,也就是以本年的8月31日与9月1日之间为界。假定身份证号码在B2,计算学生年龄公式如下: =DATEDIF(TEXT(MID(B2,7,LEN(B2)/2-1),“#-00-00”),“2008-8-31”,“Y”)(2008可变更)
4-7岁自动在另外一侧表格幼儿园下打钩,7-14岁在小学栏上打钩,14-17岁在初中栏上打钩,17-18岁在高中栏上打钩,这种公式怎么弄的,可以弄么!
=IF(2012-MID(B2,7,4)<8,“√”,“")这个函数写在幼儿园下的单元格内,其它相同,只是值改一下即可。
当然,这个要求的是18位的身份证号码。
根据身份证号码用EXCEL计算出生年月年龄及性别
在学校的人事管理中经常会遇到需要统计教职工的年龄的问题,但案头的原始资料只有身份证号码,其实这足够了。在EXCEL中,引用其内置函数利用身份证号码达到此目的比较简单。
1、身份证号码简介(18位):1~6位为地区代码;7~10位为出生年份;11~12位为出生月份;13~14位为出生日期;15~17位为顺序号,并能够判断性别,奇数为男,偶数为男;第18位为校验码。
2、确定“出生日期”:18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。①正确输入了身份证号码。(假设在D2单元格中)②将光标定位在“出生日期”单元格(E2)中,然后在单元格中输入函数公式=MID(D2,7,4)&”-“&MID(D2,11,2)&”-“&MID(D2,13,2)即可计算出“出生日期”。
关于这个函数公式的具体说明:MID函数用于从数据中间提取字符,它的格式是:MID(text,starl_num,num_chars)。
Text是指要提取字符的文本或单元格地址(上列公式中的D2单元格)。
starl_num是指要提取的第一个字符的位置(上列公式中依次为7、11、13)。
num_chars指定要由MID所提取的字符个数(上述公式中,提取年份为4,月份和日期为2)。
多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起,“/”或“-”
分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记,这样的数据以后就可以作为日期类型进行年龄计算。
3、确定“年龄”:
“出生日期”确定后,年龄则可以利用一个简单的函数公式计算出来了:将光标定位在“年龄”单元格中,然后在单元格中输入函数公式“=INT((TODAY()-E2)/365)”即可计算出“年龄”。关于这个函数公式的具体说明:
①TODAY函数用于计算当前系统日期。只要计算机的系统日期准确,就能立即计算出当前的日期,它无需参数。操作格式是TODAY()。②用TODAY()-E2,也就是用当前日期减去出生日期,就可以计算出这个人的出生天数。
③再除以 “365”减得到这个人的年龄。
④计算以后可能有多位小数,可以用【减少小数位数】按钮,将年龄的数值变成“整数”,也可在公式=(TODAY()-E2)/365中再嵌套一个
“INT”函数取整数,即“ =INT((TODAY()-E2)/365)”,这样就会自动将后面的小数去掉,只保留整数部分。
4、确定“性别”:
每个人的性别可以利用“身份证号码”进行判断,18位身份证号码中,第15~17位为顺序号,奇数为男,偶数为女。
将光标定位在“性别”单元格中,然后在单元格中输入函数公式“=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),”女“,”男“)”即可计算出“性别”。
关于这个函数公式的具体说明:
①函数公式中,MID(D2,15,3)的含义是将身份证中的第15~17位提取出来。
②VALUE(MID(D2,15,3))的含义是将提取出来的文本数字转换成能够计算的数值。
③VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2)的含义是判断奇偶。(“INT”在上面说过是取整函数,如果是偶数,则前后相等;如果是奇数,则前后不相等。)④=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),”女“,”男“)的含义是若是“偶数”就填写“女”,若是“奇数”就填写“男”。
确定性别=IF(VALUE(MID(E7,15,3))/2=INT(VALUE(MID(E7,15,3))/2),”女“,”男“)确定出生日期=MID(E7,7,4)&”-“&MID(E7,11,2)&”-“&MID(E7,13,2)确定年龄=2010-IF(LEN(C3)=18,MID(C3,7,4),”19“&MID(C3,7,2))
在EXCEL中如何利用身份证号码计算出生年月年龄及性别
在学校的人事管理中,经常会遇到需要统计教职工的年龄的问题,但案头的原始资料只有身份证号码,其实这足够了。在EXCEL中,引用其内置函数利用身份证号码达到此目的比较简单。
1、身份证号码简介(18位):
1~6位为地区代码;7~10位为出生年份;11~12位为出生月份;13~14位为出生日期;15~17位为顺序号,并能够判断性别,奇数为男,偶数为男;第18位为校验码。
2、确定“出生日期”:
18位身份证号码中的生日是从第7位开始至第14位结束。提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。①正确输入了身份证号码。(假设在D2单元格中)②将光标定位在“出生日期”单元格(E2)中,然后在单元格中输入函数公式“=MID(D2,7,4)&”-“&MID(D2,11,2)&”-“&MID(D2,13,2)”即可计算出“出生日期”。
关于这个函数公式的具体说明:MID函数用于从数据中间提取字符,它的格式是:MID(text,starl_num,num_chars)。
Text是指要提取字符的文本或单元格地址(上列公式中的D2单元格)。starl_num是指要提取的第一个字符的位置(上列公式中依次为7、11、13)。num_chars指定要由MID所提取的字符个数(上述公式中,提取年份为4,月份和日期为2)。
多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起,“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记,这样的数据以后就可以作为日期类型进行年龄计算。操作效果如下图:
3、确定“年龄”:
“出生日期”确定后,年龄则可以利用一个简单的函数公式计算出来了:将光标定位在“年龄”单元格中,然后在单元格中输入函数公式“=INT((TODAY()-E2)/365)”即可计算出“年龄”。关于这个函数公式的具体说明:
①TODAY函数用于计算当前系统日期。只要计算机的系统日期准确,就能立即计算出当前的日期,它无需参数。操作格式是TODAY()。
②用TODAY()-E2,也就是用当前日期减去出生日期,就可以计算出这个人的出生天数。
③再除以 “365”减得到这个人的年龄。
④计算以后可能有多位小数,可以用【减少小数位数】按钮,将年龄的数值变成“整数”,也可在公式=(TODAY()-E2)/365中再嵌套一个 “INT”函数取整数,即“ =INT((TODAY()-E2)/365)”,这样就会自动将后面的小数去掉,只保留整数部分。操作效果如下图:
还有一种函数(datedif)可以解决这个问题:这个函数用于计算两个日期之间的天数、月数或年数。
语法:DATEDIF(start_date,end_date,unit)start_date为一个日期,它代表时间段内的第一个日期或起始日期。end_date为一个日期,它代表时间段内的最后一个日期或结束日期。unit为所需信息的返回类型:其中,“y”为时间段中的整年数,“m”为时间段中的整月数,“d”为时间段中的天数。操作效果如下图:(注:出生日期在B列B1中。)
4、分段统计年龄: 利用countif函数。如图:
H3单元格中输入“=COUNTIF(F2:F8,”<=45“)-COUNTIF(F2:F8,”<36“)”(外引号不要输入),可计算36至45岁的人数。
H2单元格中输入“=COUNTIF(F2:F8,”<=35“)”,可计算35岁及以下的人数。H4单元格中输入“=COUNTIF(F2:F8,”<=60“)-COUNTIF(F2:F8,”<46“)”,可计算46至60岁的人数。
H5单元格中输入“=COUNTIF(F2:F8,”>60“)”,可计算60岁以上的人数。
5、确定“性别”:
每个人的性别可以利用“身份证号码”进行判断,18位身份证号码中,第15~17位为顺序号,奇数为男,偶数为女。
将光标定位在“性别”单元格中,然后在单元格中输入函数公式“=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),”女“,”男“)”即可计算出“性别”。
关于这个函数公式的具体说明:
①函数公式中,MID(D2,15,3)的含义是将身份证中的第15~17位提取出来。②VALUE(MID(D2,15,3))的含义是将提取出来的文本数字转换成能够计算的数值。
③VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2)的含义是判断奇偶。(“INT”在上面说过是取整函数,如果是偶数,则前后相等;如果是奇数,则前后不相等。)④=IF(VALUE(MID(D2,15,3))/2=INT(VALUE(MID(D2,15,3))/2),”女“,”男")的含义是若是“偶数”就填写“女”,若是“奇数”就填写“男”。操作效果如下图: