用excel函數(shù)利用身份證信息提取出生年月

1、用excel函數(shù)利用身份證信息提取出生年月、性別、年齡!一、建立有關(guān)身份證信息的表格:1建立一個有關(guān)身份證信息的表格：以excel2003為例，要完整的輸入 18位的身份證號，輸入身份證號的單元格的格式應(yīng)進(jìn)行如下設(shè)置：1）、如下圖，先選中要輸入身份證號的單元格，點右鍵，選擇設(shè)置單元格格式，進(jìn)入單元格 格式面板，選數(shù)字，分類選文本，我們輸入的身份證號為"文本）2二|艾件備癘強商視團犧入榕式刨工翼對驀迦商口畑料助丄川眾二此乜町離兀妙jh宋體1011121314151617182021<>7B2-A璉茗身粉證昏性別2）、點確定后，輸入身份證號。icrooft Evcel -從身

2、份證提取年證性別、出生日ffi- xls文件垃）褊輯® 視圖邊 插入 格式® 工具 數(shù)據(jù)00 窗口 幫助心£丨¥塾壬勺實曲範(fàn)第宋體二佬D15- AABCDE 1姓名身奮證號岀生年月性別年齡24*+*1970100565683#+4+19751205664844+*19800505667854+*+*!972080566286半*羋4*19701005665878910-1 n二、提取出生年月日：1點C2單元格，插入函數(shù)，提取出生年月：點“插入”一“函數(shù)”，出現(xiàn)插入函數(shù)面板， 我們選“ date ”函數(shù)，點確定，如下圖：所謂DATE!數(shù)，就是找到待找區(qū)域內(nèi)

3、代表日期的數(shù)字：半 立坤（i）編韻視ea世.插入 禍?zhǔn)睫x Tftco 總據(jù)如 匍口 會肋2.匚耒蘇A1姓名2+3*45673910111213141516171819202144022030566234*197010056658身份證號4r+T-YD701005i>5634*197512056648*19800505 譏滄出生年戈I牲別1年於B Z U2、進(jìn)入 DATE函數(shù)面板：如下圖：DATE函數(shù)包括三個內(nèi)容： YEAR（年）、MONTH月）、DAY（日），對于本例來說，我們就是要在B2單元格內(nèi)的身份證信息中找到某人的出生年月日數(shù) 字，使之顯示在C2單元格內(nèi)。=j文件刪5視開也箱式直工

4、貝也欝露四宙口町務(wù)肋翌HATS"IJf=7'831011121314151617IS19汕A姓名+*t + +*#+*4*+*+*l3701005656844*19751205664S4*+*18005O56784*19T20805662B44+4+197010056658出生年尺dATEQE年齡3、我們知道，現(xiàn)在中國的所有身份證號碼都是18位的，比如：B2單元格內(nèi)的身份證號碼：4*197010056568從左往右數(shù)，第 7-10位為某人的出生年份：1970年，第11-12位為某人的出生月份：10 月，第13-14位為某人的出生日期：05日。此時，我們要在 DATE函數(shù)中嵌套

5、 MID函數(shù)，提 取相關(guān)的信息。我們看到：我們在 YEAR欄里，輸入了 MID （B2, 7，4），MID（ B2, 7，4）就是MID函數(shù)的 格式，什么意思呢？ B2就是要查找的區(qū)域，我們要找的文本信息（身份證號）在 B2單元格 內(nèi)；“7”的意思是我們要找的年份信息在文本信息的從左往右數(shù)的第幾位開始出現(xiàn)，在第7位，所以我們填“ 7”；“ 4”的意思是，這個信息共有幾位，年份信息共有4位，所以我們填4。查找月份、日期信息是MID函數(shù)的重復(fù)應(yīng)用，是一個意思。規(guī)圖理)ISA 1)梏式TMa) 計據(jù)宦口 ip 艷飾00苗 暫 丑U - J. i H I津丄-4 ”工二亠1上丄戲心.N 7哎.*丄也：

6、-.i n.j *&*'£ I=1.12 訕上I y 1IDATE節(jié)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函數(shù)，MID (B2, 11, 2),意思是月份信息在 B2單元格內(nèi)從左往 右數(shù)的第11位開始，共2位：也就是10。：5立件® 剛a

7、9; 現(xiàn)圖 播人格式型 工具 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函數(shù)，MID ( B2, 13, 2),意思是日期信息在 B2單元格內(nèi)從左往右 數(shù)的第13位開始，共2位：也就是5。文強f 鋼輯 視酣 鼬入 移式©

8、; 工具 總據(jù) 窗口盤】稱肪如II"date116、點確定，如下: Iicraoft從口悅證智取年談“也丹.岀工日期小応?豈j交件g) am? 視團 ffiA I) 格式工臭數(shù)蟹空 窗口 W 01)« -,:JJJ J- JT密之Ml呦J宋體”謹(jǐn) | 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、復(fù)制函數(shù)，把光標(biāo)移到 C2單元格的右下角點上，出現(xiàn)十字標(biāo)記時向下拉，復(fù)制函數(shù)，如下：£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)”， 產(chǎn)mr _ _ 一2122.三、提取性別:1、點選D2單元格，插入IF函數(shù)。丨2J文斡 *勒視圖帕紅甲箱式a ZAO）戟擰如童口心嚮h電） j J*10213冷1516 1is2Gr2122 .23242627J2A身帶證號4*19701005&568 4*197512056&

11、484-+*1980050566784*+* 1972080566284*1970100566582、進(jìn)入IF函數(shù)面板:岀生年月1970-10-51975-12-51980-5-51972-3-51970-10-5r 12mi=U 3rrr(jj 褊掛 建團凹 血人 格式電)工具 數(shù)臺卸 竇口碰】 菴肋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欄嵌套函數(shù)：logical_test里填的是任何一個可判斷為對或錯的數(shù)值或表達(dá)式。我們看到IF函數(shù)里嵌套了兩個函數(shù)，分別是 MID函數(shù)和INT函數(shù)。什么是INT函數(shù)呢？是返回數(shù)值向下取整為最接近的整數(shù)，本例用來判斷身份證里數(shù)值的奇偶數(shù)。表達(dá)式是 ：=IF( MID ( B2, 17，1)/2=INT(MID(B2，17，1)/2 )什么意思呢？如果MID（B2,17,1）/2 （意思是B2單元格里的文本信息從 左往右數(shù)第17位開始，只取第 仃位這1位數(shù)的數(shù)值除以2）等于INTCD島份證號性別乙1"2)1出生年月

13、1970-10-5I975*1卜51980-5-51972-8-5姓宕*（MID （B2，17，1）/2 （意思是整數(shù)）的話，也就是偶數(shù)的話。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糸燈是舌需老，如累瀝込衛(wèi)圖一辛値*舉丁島宅期更回月一T值輕胃f 咖斷為irvb事rust的轉(zhuǎn)沮祕達(dá)克4、Value_if_true 的意思是，如

14、果表達(dá)式滿足的話，返回一個真值，對 于本例來說，我們都知道，18位身份證號的第17位是性別的識別碼， 如果是奇數(shù)，就是男的，如果是偶數(shù)，就是女的。所以此欄填“女”里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、如果是奇數(shù)，也就是 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、點確定，計算結(jié)果為女，復(fù)制函數(shù)，如下: Microsoft虹"-從身份證提取年齡、性別、出生日越亠煉文梓迥鐮輕百觀國遡議A心略式工具衛(wèi)爵據(jù)宙口迥科肋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函數(shù)：嵌套的函數(shù)有year函數(shù)、now函數(shù)、mon th函數(shù)、int函數(shù)、day函數(shù)，都是很簡單的，看一下就明白了：對了，說一下嵌套函數(shù)，就是指在某些情況下，您可能需要將某函數(shù)作為另一函數(shù)的參數(shù)使 用，這一函數(shù)就是嵌套函數(shù)，就像我們已經(jīng)和正在用到的。下面這種算法，是最準(zhǔn)確的算法，涵蓋了各種可能，只是公式較長：如下：=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 , ”的 意思是：如果當(dāng)前日期的月份小于所需計算日期的月份，則表示今年沒有過生日，年齡數(shù)為YEAR(NOW()-YEAR(D2)-1如果不是這種情況，進(jìn)入下一步判斷； mon th( now()是指當(dāng)前月份，now函數(shù)是 mo nth函數(shù)的一個參數(shù)，mo nth(c2)是 針對本例而言的，c2單元格是出生年月，對其它應(yīng)用而言，就是你設(shè)計的單元 格了。int函數(shù)就是取整數(shù)函數(shù)。IF(MONTH(NOW()>MONTH(C2),YEAR(NOWYEAR(C2),” 的意思是:如果當(dāng)前日期的月份大于所需計算日期的月份，則表示今年已經(jīng)過生日，年齡數(shù)為 YEAR(NOW()-YEAR(D2),如果也不是這種情況，則表示這兩個月份相等，進(jìn)入下一步判斷； year(now()-year(c2),就是用當(dāng)前的年份減去出生的年份(c2單元格的出生年份，對本例而言)。IF(DA Y(NOW()>=DA Y(C2