第一篇：Excel輸入出生日期、年齡自動(dòng)計(jì)算、身份證提取日期、年度統(tǒng)計(jì)

Excel輸入出生日期、年齡自動(dòng)計(jì)算、身份證提取日期、年度統(tǒng)計(jì)

背景：統(tǒng)計(jì)在校生需要輸入出生年月日，改動(dòng)出生年月日，統(tǒng)計(jì)年齡，從身份證提取年齡等。網(wǎng)上的介紹涉及格式又?jǐn)?shù)字又文本的，有的輸入時(shí)還自動(dòng)變成1905或2441年等，很煩惱，而這次總結(jié)的辦法不會(huì)出現(xiàn)上述問(wèn)題，都是在格式為數(shù)字格式的情況計(jì)算的，只是涉及小數(shù)位數(shù)和是否自動(dòng)插入小數(shù)點(diǎn)等問(wèn)題。

常用公式：

1、=DATEDIF(E4,TODAY(),“y”)

（計(jì)算一個(gè)學(xué)生今年的歲數(shù)）

2、=2006-year(d1)(計(jì)算一個(gè)學(xué)生不同學(xué)年度的歲數(shù)）

3、擇取單元格前面的幾位數(shù)=LEFT(C15,4）取C15中前4個(gè)字符就是你要的年份了

4、提取指定位置，指定長(zhǎng)度的字符串

=MID（A2，7,8）

即 =MID（要提取的單元格，起始位數(shù)，提取長(zhǎng)度）

5、對(duì)如：79.1-197901、80.10-198010、2001.1-200101、2001.12-200112都適用。

方案： 找一空閑列輔助，比如原來(lái)的出生年月列在A列，E列為空閑列，在E1中輸入

=IF(LEN($A1)<4“錯(cuò)誤”,CHOOSE(LEN($A1)-3,“19”&LEFT($A1,2)&“0”&RIGHT($A1,1),“19”&LEFT($A1,2)&RIGHT($A1,2),LEFT($A1,4)&“0”&RIGHT($A1,1),LEFT($A1,4)&RIGHT($A1,2)))

6、假設(shè)你A1輸入的是出生年月B1輸入=DATEDIF(A1,today(),“y”)但是這是指你的A1是輸入的標(biāo)準(zhǔn)日期，如果輸入的是文本格式的那B1輸入=year(today())-left(a1,4)

綜上所述，總結(jié)了兩種極為有效的方法，其實(shí)原理是一種而已： 方法一：

1、打開(kāi)excel 2007或2003，出生年月處數(shù)據(jù)自動(dòng)插入小數(shù)點(diǎn)，格式為數(shù)字并保留兩位小數(shù)，分隔符為“-”

2、提取前四位字符，即出生年份 =LEFT(A2,4）

3、打開(kāi)2003或另一個(gè)2007程序，粘貼，格式為文本格式。

4、計(jì)算年齡： =2013-A2(年份所在單元格），拖動(dòng)年齡列即可

方法二：根據(jù)第一種方法總結(jié)的經(jīng)典法

1、打開(kāi)excel 2007或2003，出生日期處，格式為數(shù)字，保留小數(shù)兩位，高級(jí)選項(xiàng)里自動(dòng)插入小數(shù)點(diǎn)兩位，分隔符為“-”

2、停止excel自動(dòng)插入小數(shù)點(diǎn)。

3、把年齡單元格改為數(shù)值并不保留小數(shù)點(diǎn)，輸入公式 =year(today())-left(a1,4)即用今年減去年齡單元格的前四位整數(shù)，即為年齡。然后拖動(dòng)年齡列即可。

4、更換學(xué)年度時(shí)，學(xué)生的年齡是發(fā)生變化的，只需改動(dòng)年齡位置的公式，如1976年2013年度該學(xué)生是37歲，那么2012年度時(shí)該學(xué)生應(yīng)該是36歲，所以公式應(yīng)該是 =year(today())-left(a1,4)-1，注意減1即可。若是2000年度，則=year(today())-left(a1,4)-13即可，即為該學(xué)生2000年時(shí)的歲數(shù)。

第二篇：利用身份證號(hào)自動(dòng)提取出生日期和年齡性別

在excel表格中，根據(jù)身份證號(hào)碼就可以自動(dòng)提取出生年月、性別、年齡。

第一，身份證號(hào)碼的組成。

當(dāng)今的身份證號(hào)碼由18位數(shù)字組成：前6位為地址碼，第7至14位為出生日期碼，第15至17位為順序碼，第18位為校驗(yàn)碼。

15~17位為順序號(hào)，其中第17位如果是單數(shù)為男性分配碼，雙數(shù)為女性分配碼。即通過(guò)第17位數(shù)字，可以判斷性別，奇數(shù)為男，偶數(shù)為女。

下面以實(shí)例的方式講解如何根據(jù)身份證號(hào)碼自動(dòng)提取出生年月、性別、年齡。首先，在excel中輸入以下表格，實(shí)例如下面所示。

A

B

C

D

E

姓名

身份證號(hào)碼

出生日期

性別

年齡

張三

***786

1972年05月03日

女

第二，根據(jù)身份證號(hào)碼自動(dòng)提取出生年月

在目標(biāo)單元格輸入公式：

=MID(B2,7,4)&“年”&MID(B2,11,2)&“月”&MID(B2,13,2)&“日”，回車(chē)確定，單元格內(nèi)會(huì)出現(xiàn)與身份證號(hào)碼相關(guān)的出生日期。其它單元格的填充，不必一一輸入公式，只需選中該單元格復(fù)制，在其它單元格內(nèi)粘貼即可。

提示：MID函數(shù)是提供的一個(gè)“從字符串中提取部分字符”的函數(shù)命令。

第三，根據(jù)身份證號(hào)碼自動(dòng)提取性別的方法

在目標(biāo)單元格輸入公式：

=IF(MOD(RIGHT(LEFT(B2,17)),2),“男”,“女”)

提示：IF函數(shù)：根據(jù)邏輯表達(dá)式測(cè)試的結(jié)果，返回相應(yīng)的值。IF函數(shù)允許嵌套。

RIGHT函數(shù)：根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。

第四，根據(jù)身份證號(hào)碼自動(dòng)提取年齡的方法

在目標(biāo)單元格輸入公式：=DATEDIF(TEXT(MID(B2,7,6+2*(LEN(B2)=18)),“#-00-00”),NOW(),“y”)，回車(chē)確定。