用excel函数利用身份证信息提取出生年月

1、用excel函数利用身份证信息提取出生年月、性别、年龄!一、建立有关身份证信息的表格:1建立一个有关身份证信息的表格：以excel2003为例，要完整的输入 18位的身份证号，输入身份证号的单元格的格式应进行如下设置：1）、如下图，先选中要输入身份证号的单元格，点右键，选择设置单元格格式，进入单元格 格式面板，选数字，分类选文本，我们输入的身份证号为"文本）2二|艾件備癘强商視團牺入榕式刨工翼對驀迦商口畑料助丄川眾二此乜町离兀妙jh宋体1011121314151617182021<>7B2-A琏茗身粉证昏性别2）、点确定后，输入身份证号。icrooft Evcel -从身

2、份证提取年證性别、出生日ffi- xls文件垃）褊辑® 视图边 插入 格式® 工具 数据00 窗口 帮助心£丨¥塾壬勺实曲範第宋体二佬D15- AABCDE 1姓名身奋证号岀生年月性别年齡24*+*1970100565683#+4+19751205664844+*19800505667854+*+*!972080566286半*芈4*19701005665878910-1 n二、提取出生年月日：1点C2单元格，插入函数，提取出生年月：点“插入”一“函数”，出现插入函数面板， 我们选“ date ”函数，点确定，如下图：所谓DATE!数，就是找到待找区域内

3、代表日期的数字：半 立坤（i）编韻视ea世.插入 祸式辺 Tftco 总据如 匍口 會肋2.匚耒蘇A1姓名2+3*45673910111213141516171819202144022030566234*197010056658身份证号4r+T-YD701005i>5634*197512056648*19800505 讥沧出生年戈I牲别1年於B Z U2、进入 DATE函数面板：如下图：DATE函数包括三个内容： YEAR（年）、MONTH月）、DAY（日），对于本例来说，我们就是要在B2单元格内的身份证信息中找到某人的出生年月日数 字，使之显示在C2单元格内。=j文件删5視開也箱式直工

4、貝也欝露四宙口町務肋翌HATS"IJf=7'831011121314151617IS19汕A姓名+*t + +*#+*4*+*+*l3701005656844*19751205664S4*+*18005O56784*19T20805662B44+4+197010056658出生年尺dATEQE年龄3、我们知道，现在中国的所有身份证号码都是18位的，比如：B2单元格内的身份证号码：4*197010056568从左往右数，第 7-10位为某人的出生年份：1970年，第11-12位为某人的出生月份：10 月，第13-14位为某人的出生日期：05日。此时，我们要在 DATE函数中嵌套

5、 MID函数，提 取相关的信息。我们看到：我们在 YEAR栏里，输入了 MID （B2, 7，4），MID（ B2, 7，4）就是MID函数的 格式，什么意思呢？ B2就是要查找的区域，我们要找的文本信息（身份证号）在 B2单元格 内；“7”的意思是我们要找的年份信息在文本信息的从左往右数的第几位开始出现，在第7位，所以我们填“ 7”；“ 4”的意思是，这个信息共有几位，年份信息共有4位，所以我们填4。查找月份、日期信息是MID函数的重复应用，是一个意思。规图理)ISA 1)梏式TMa) 計据宦口 ip 艳飾00苗 暫 丑U - J. i H I津丄-4 ”工二亠1上丄戲心.N 7哎.*丄也：

6、-.i n.j *&*'£ I=1.12 讪上I y 1IDATE节X“ =DATE(1TD(B2, 7. 4)ABCDEFG1券儘证号出生年月性則年龄24+*+*19701005&56S讥 4)3*+4*+*197512056644*19800505S67S5*4*+*19720305628*4味4+1970100566537丄g101415 ,1C1713 j1112 .13214、MONT栏里，嵌套 MID函数，MID (B2, 11, 2),意思是月份信息在 B2单元格内从左往 右数的第11位开始，共2位：也就是10。：5立件® 刚a

7、9; 现图 播人格式型 工具 smg 窗口叩 m<a)BATE姓窖身份证号4*197010Cfi6S6e+4+*1975120566484*+*19：50050566784w*+i97208056628q树*札屮|: 005666859101112151617IS191314126784X110(62.11,2)D性别C 出主年月=DATE(IID(B2> 7, 4)>ITD(B2. 11,2)5、DAY栏里，嵌套 MID函数，MID ( B2, 13, 2),意思是日期信息在 B2单元格内从左往右 数的第13位开始，共2位：也就是5。文強f 鋼辑 视酣 鼬入 移式©

8、; 工具 总据 窗口盘】稱肪如II"date116、点确定，如下: Iicraoft从口悅证智取年談“也丹.岀工日期小応?岂j交件g) am? 视團 ffiA I) 格式工臭數蟹空 窗口 W 01)« -,:JJJ J- JT密之Ml呦J宋体”谨 | BC2A =DATEID(B& 7, 4j, MID (B2,11, 2)? JWID(B2,13, 2)ABCDEF1姓名身份证号岀主年月性别年龄24*197010056&68 |19T0TQ-53*+*4*+*197512056648"4*审4*+*+19S0050566785*#*4*19720

9、80566286+#*4*1970100566S8789,10 .b 1112131415161713筑C7、复制函数，把光标移到 C2单元格的右下角点上，出现十字标记时向下拉，复制函数，如下：£2 icroaft Excel从身输 U 锐取年辞.ftSl -岀xlsABCDTF01姓名券份证号出坤年月性别年龄2卓*4* *197010056 5681970-10-534*#*197Si20566481975-12-S1980-5-54*4+*+1980050566785#*#4*=* 1972080566281972-3-5lj?0<0-5F半*4*>*197QJ005

10、6658?岡891011121314151617181920 1：5J文件鳩辑视国® 捆入型格式【工具觀堀虑口楮肋團d 二； ； /- - 八 * " V 石 F- -»/«!：C2 丁 A 二DATE(HID(B2，GID(E2,ll,2)m(BZ13,2)”， 产mr _ _ 一2122.三、提取性别:1、点选D2单元格，插入IF函数。丨2J文斡 *勒視图帕紅甲箱式a ZAO）戟拧如童口心嚮h电） j J*10213冷1516 1is2Gr2122 .23242627J2A身帶证号4*19701005&568 4*197512056&

11、484-+*1980050566784*+* 1972080566284*1970100566582、进入IF函数面板:岀生年月1970-10-51975-12-51980-5-51972-3-51970-10-5r 12mi=U 3rrr(jj 褊掛 建團凹 血人 格式电)工具 數臺卸 竇口碰】 菴肋KUJaUK ”s910111213141&1E1718192021+*+4*19701005568 197512056G48 4*c*#*|SQQ5Q5557g 4*197208056623 4+4+197010056658C出生年月1970-10-&1975-12-519SO

12、-5-51972-8-51970-10-5年龄3、logical_test栏嵌套函数：logical_test里填的是任何一个可判断为对或错的数值或表达式。我们看到IF函数里嵌套了两个函数，分别是 MID函数和INT函数。什么是INT函数呢？是返回数值向下取整为最接近的整数，本例用来判断身份证里数值的奇偶数。表达式是 ：=IF( MID ( B2, 17，1)/2=INT(MID(B2，17，1)/2 )什么意思呢？如果MID（B2,17,1）/2 （意思是B2单元格里的文本信息从 左往右数第17位开始，只取第 仃位这1位数的数值除以2）等于INTCD島份证号性别乙1"2)1出生年月

13、1970-10-5I975*1卜51980-5-51972-8-5姓宕*（MID （B2，17，1）/2 （意思是整数）的话，也就是偶数的话。4*f»1970L00565684*1S7S12056648 <*1980050566781970-10 5IF*111 r i E kni*”t m 虺.n. nivraiDOf. n. i4*1972Cg05f.62g_io_.12.1_ 13 J1415!：4趾一r糸灯是舌需老，如累瀝込卫圖一辛値*舉丁岛宅期更回月一T值輕胃f 咖断为irvb事rust的轉沮祕达克4、Value_if_true 的意思是，如

14、果表达式满足的话，返回一个真值，对 于本例来说，我们都知道，18位身份证号的第17位是性别的识别码， 如果是奇数，就是男的，如果是偶数，就是女的。所以此栏填“女”里J X#O鶴宙® A 蒂式叨 X*(X) ft£Q) 侶® 蒂動一 一 - " “ 一 一 ” 1 - 8 -ifX J 刁-IF(IIDf B2.17,1)/2 IHT(IID(B2,17.1)/2),*)&-Q- Tai+lJl*护*#1祈讥g騙犯84*1975L20E664S曲 056 丹 34*I720805628 4*1970L0056658出生年月1970-10-5197S

15、-1251980-5-51372-S-51970-10-5E年齡101113 I1415Z1；l-'5、如果是奇数，也就是 Value_if_false 填男丈祥即屛鼠斑觀诵入蒂？：0工典戳）6© 屋口褪耻电F i iTFK1Zp*+*4牟*6#+*197010(566584*+"19TQlJQ5 圧&8J4*"讥的輛弭丽钉E屈.匚AJ A -* J L*B 1 y iF冥=lF（TD（B2,1仇1）/2刃町（卬（52.灯八）/2）, “女二男）出生年月4*=f*19(7208056&28E年齡91011121314IF1617131975

16、-12-519E0-5-519T2-B-5 mo-io-56、点确定，计算结果为女，复制函数，如下: Microsoft虹"-从身份证提取年龄、性别、出生日越亠炼文梓迥镰轻百观国遡議A心略式工具卫爵据宙口迥科肋QDJ2母丄 d 各=IF(iID(BZlbl)/2=IKr(kID(B2JB 1)/2),"女男")_E4TT7891011121.31415姓名身份证号岀生年月4+*+1970100565631970-10-5+*4+*197512056481975-12-54*+*198005056678 1980-5-5*4*19720805&6281 19

17、72-8-6水*4*+197C100566581970-10-5飞 曲女女兴女更四、计算年龄1、点E2单元格，插入if函数：嵌套的函数有year函数、now函数、mon th函数、int函数、day函数，都是很简单的，看一下就明白了：对了，说一下嵌套函数，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使 用，这一函数就是嵌套函数，就像我们已经和正在用到的。下面这种算法，是最准确的算法，涵盖了各种可能，只是公式较长：如下：=IF(M0NTH(N0W()<M0NTH(C2),INT( YEAR(NOW()-YEAR(C2)-1,IF(M0NTH(N0W()>M0NTH(C2)

18、, YEAR(NOW()-YEAR(C2),IF(DA Y(NOW()>=DAY(C2), YEAR(NOW()-YEAR(C2), YEAR(NOW()-YEAR(C2)-1)这么长，是什么意思呢?“=IF(MONTH(NOW()<MONTH(C2),INT(YEAR(NOWYEAR(C2)-1 , ”的 意思是：如果当前日期的月份小于所需计算日期的月份，则表示今年没有过生日，年龄数为YEAR(NOW()-YEAR(D2)-1如果不是这种情况，进入下一步判断； mon th( now()是指当前月份，now函数是 mo nth函数的一个参数，mo nth(c2)是 针对本例而言的，c2单元格是出生年月，对其它应用而言，就是你设计的单元 格了。int函数就是取整数函数。IF(MONTH(NOW()>MONTH(C2),YEAR(NOWYEAR(C2),” 的意思是:如果当前日期的月份大于所需计算日期的月份，则表示今年已经过生日，年龄数为 YEAR(NOW()-YEAR(D2),如果也不是这种情况，则表示这两个月份相等，进入下一步判断； year(now()-year(c2),就是用当前的年份减去出生的年份(c2单元格的出生年份，对本例而言)。IF(DA Y(NOW()>=DA Y(C2