第一篇：在Excel表格中輸入身份證號碼、出生日期和年齡的技巧

在Excel表格中輸入身份證號碼、出生日期和年齡的技巧

在使用Excel表格進行人員信息等相關(guān)操作時，常常需要用到身份證號碼、出生日期和年齡等信息。如何將這些信息方便地進行輸入呢？下面是我在相關(guān)操作中用到的技巧：

假設(shè)需要在A1單元格輸入身份證號碼。

1、輸入身份證號碼的方法：選中A1單元格，設(shè)置單元格格式為“文本”，然后再使用數(shù)字鍵盤輸入身份證號碼；不設(shè)置單元格格式也可以，但必須先輸入英文格式的“'”（引號內(nèi)的符號），再輸入身份證號碼的數(shù)字。

2、提取出生日期的方法：在需要輸入出生日期的單元格（比如B1）中，設(shè)置單元格格式為“日期”，并選擇好需要的日期格式，再輸

入“=IF(OR(LEN(A1)=18,LEN(A1)=15),TEXT(MID(A1,7,6+(LEN(A1)=18)*2),“#-00-00”)+0,“身份證錯誤”)”（引號內(nèi)的公式）后回車就可以了；

也可以使用

“=TEXT(IF(LEN(A1)=15,“19”,)&MID(A1,7,IF(LEN(A1)=18,8,l)),“####-##-##”)”（引號內(nèi)的公式）進行提取，但此時B1單元格中的日期格式是固定的“####-##-##”格式。

也可以

“=MID(A1,7,4)&-MID(A1,11,2)&-MID(A1,13,2)”（不含引號，其中藍色突出標識的為你所引用數(shù)據(jù)，即身份證號碼所在的單元格）回車即可提取到1980-02-14格式的出生年月日了

也可以

如果需要1980年2月14日格式的，可以用下面的公式“=MID(F2,7,4)&“年”&MID(F2,11,2)&“月”&MID(F2,13,2)&“日””（不含引號）（“＆”有合并的意思）

3、提取年齡的方法：提取年齡的方法有兩種，一種是根據(jù)當前年份確定年齡的方法，一種是根據(jù)特定日期確定年齡（比如學(xué)校招收一年級新生時，要根據(jù)8月31日來確定年齡是否符合要求）的方法。第一種方法的公式是“=YEAR(TODAY())-MID(A1,7,4)”；第二種方法的公式是

“=DATEDIF(TEXT(MID(A1,7,LEN(A1)/2-1),“#-00-00”),“2010-8-31”,“Y”)”。

4、提取性別的方法：

=IF(LEN(A1)=15,IF(VALUE(RIGHT(A1,3))/2=INT(VALUE(RIGHT(A1,3))/2),“

女

”,“

男”),IF(VALUE(MID(A1,15,3))/2=INT(VALUE(MID(A1,15,3))/2),“女”,“男”))

第二篇：身份證號碼提取年齡,出生日期,等

身份證號碼提取年齡

=IF(LEN(A1)>15,YEAR(NOW())-MID(A1,7,4)+1,YEAR(NOW())-(MID(A1,7,2)+1900)+1)假設(shè)A1存放的號碼，B1輸出結(jié)果，那么在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函數(shù)提取出生年份，然后用現(xiàn)在身份證號碼在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

根據(jù)身份證號碼（15位和18位通用）自動提取性別和出生年月的自編公式，供需要的網(wǎng)友參考：

說明：公式中的B2是身份證號

1、根據(jù)身份證號碼求性別：

=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、根據(jù)身份證號碼求出生年月：

=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、根據(jù)身份證號碼求年齡：

=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),”身份證錯“))

通過身份證號取得出身日期，年齡，是很多人力資源朋友遇到的問題；在這里個人的一些小經(jīng)驗分享給大家； 工具/原料

? Excel 2000及以上版本

方法/步驟

1.1 新建Excel文檔；

2.2 根據(jù)需要，在新建的表頭的上分別輸入，員工姓名，身份證號，出生日期，年齡，入職日期，服務(wù)年限

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），是將取得的年、月、日轉(zhuǎn)換成時間格式；

MID（text;start_num;num_chars）,功能是根據(jù)text的內(nèi)容，截取字符串；start_num：截取第1個字符所在的位置；num_chars：截取的字符數(shù)量。例如：Mid(b2;7;2)，表示從身份證的第7位開始，截取2個字符；

5.5 在第二行的，第三列單元格中，輸入： =DATEDIF(C2;TODAY();”y“)這個方法是計算到當前日期，員工的年齡；

6.6 在第二行的，第四列單元格中，輸入：員工到崗日期，單元格格式設(shè)為日期格式；即:YYYY-MM-DD

7.7 在第二行的，第五列單元格中，輸入： =DATEDIF(E2;TODAY();”y")即可計算到當前日期的，服務(wù)年限

8.8 選中C2，D2，復(fù)制單元格；

9.9 選中C列，D列剩余的單元格，選粘貼，即可自動計算每個員工的出生日期及年齡；

第三篇：由身份證號碼求出生日期、年齡、性別

excel中由身份證號求出生日期、性別、年齡

（1）根據(jù)身份證號碼求性別 ：=IF(VALUE(RIGHT(B2,3))/2=INT(VALUE(RIGHT(B2,3))/2),“女”,“男”)

（2）根據(jù)身份證號碼求出生日期：

=CONCATENATE(MID(B2,7,4),“年”,MID(B2,11,2),“月”,MID(B2,13,2),“日”)

（3）根據(jù)身份證號碼求出年齡：=CONCATENATE(DATEDIF(D2,TODAY(),“y”),“周歲”)

或 =DATEDIF(D3,TODAY(),“y”)

1、身份證號碼相關(guān)知識

在了解如何實現(xiàn)自動從身份證號碼中提取出生年月、性別信息之前，首先需要了解身份證號碼所代表的含義。我們知道，當今的身份證號碼有15/18位之分。早期簽發(fā)的身份證號碼是15位的，現(xiàn)在簽發(fā)的身份證由于年份的擴展（由兩位變?yōu)樗奈唬┖湍┪布恿诵灤a，就成了18位。這兩種身份證號碼將在相當長的一段時期內(nèi)共存。兩種身份證號碼的含義如下：

（1）15位的身份證號碼：1~6位為地區(qū)代碼，7~8位為出生年份(2位)，9~10位為出生月份，11~12位為出生日期，第13~15位為順序號，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。

（2）18位的身份證號碼：1~6位為地區(qū)代碼，7~10位為出生年份(4位)，11~12位為出生月份，13~14位為出生日期，第15~17位為順序號，并能夠判斷性別，奇數(shù)為男，偶數(shù)為女。18位為效驗位。

2、應(yīng)用函數(shù)

在此例中為了實現(xiàn)數(shù)據(jù)的自動提取，應(yīng)用了如下幾個Excel函數(shù)。（1）IF函數(shù)：根據(jù)邏輯表達式測試的結(jié)果，返回相應(yīng)的值。IF函數(shù)允許嵌套。

語法形式為：IF（logical_test, value_if_true,value_if_false）（2）CONCATENATE：將若干個文字項合并至一個文字項中。語法形式為：CONCATENATE(text1,text2……)

（3）MID：從文本字符串中指定的起始位置起，返回指定長度的字符。語法形式為：MID(text,start_num,num_chars)（4）TODAY：返回計算機系統(tǒng)內(nèi)部的當前日期。語法形式為：TODAY（）

（5）DATEDIF：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。語法形式為：DATEDIF(start_date,end_date,unit)（6）VALUE：將代表數(shù)字的文字串轉(zhuǎn)換成數(shù)字。語法形式為：VALUE(text)（7）RIGHT：根據(jù)所指定的字符數(shù)返回文本串中最后一個或多個字符。語法形式為：RIGHT(text,num_chars)（8）INT：返回實數(shù)舍入后的整數(shù)值。語法形式為：INT(number)

4、公式寫法及解釋（以員工Andy為例說明）

說明：為避免公式中過多的嵌套，這里的身份證號碼限定為15位的。如果您看懂了公式的話，可以進行簡單的修改即可適用于18位的身份證號碼，甚至可適用于15、18兩者并存的情況。

（1）根據(jù)身份證號碼求性別

=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)公式解釋：a.RIGHT(E4,3)用于求出身份證號碼中代表性別的數(shù)字，實際求得的為代表數(shù)字的字符串

b.VALUE(RIGHT(E4,3)用于將上一步所得的代表數(shù)字的字符串轉(zhuǎn)換為數(shù)字 c.VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用于判斷這個身份證號碼是奇數(shù)還是偶數(shù)，當然你也可以用Mod函數(shù)來做出判斷。

d.=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),“女”,“男”)及如果上述公式判斷出這個號碼是偶數(shù)時，顯示“女”，否則，這個號碼是奇數(shù)的話，則返回“男”。

（2）根據(jù)身份證號碼求出生日期

=CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))公式解釋：a.MID(E4,7,2)為在身份證號碼中獲取表示年份的數(shù)字的字符串 b.MID(E4,9,2)為在身份證號碼中獲取表示月份的數(shù)字的字符串 c.MID(E4,11,2)為在身份證號碼中獲取表示日期的數(shù)字的字符串 d.CONCATENATE(“19”,MID(E4,7,2),“/”,MID(E4,9,2),“/”,MID(E4,11,2))目的就是將多個字符串合并在一起顯示。

（3）根據(jù)參加工作時間求年資（即工齡）=CONCATENATE(DATEDIF(F4,TODAY(),“y”),“年”,DATEDIF(F4,TODAY(),“ym”),“個月”)公式解釋：

a.TODAY()用于求出系統(tǒng)當前的時間

b.DATEDIF(F4,TODAY(),“y”)用于計算當前系統(tǒng)時間與參加工作時間相差的年份

c.DATEDIF(F4,TODAY(),“ym”)用于計算當前系統(tǒng)時間與參加工作時間相差的月份，忽略日期中的日和年。

d.=CONCATENATE(D

第四篇：Excel表格中如何依據(jù)據(jù)身份證號碼自動填出生日期

一、excel里依據(jù)身份證號自動生成年齡

1、假設(shè)A1為身份號，15、18位都可，在B1顯示此人年齡（至2009年底計算），公式如下：

=2009-LEFT(IF(LEN(A1)=15,19&TEXT(MID(A1,7,6),“00-00-00”),TEXT(MID(A1,7,8),“0000”)),4)

2、或者采取下列公式：

=IF((RIGHT(20090528-IF(LEN(A1)=15,19&TEXT(MID(A1,7,6),“00-00-00”),TEXT(MID(A1,7,8),“00000000”)),4))>0,LEFT(20090523-IF(LEN(A1)=15,19&TEXT(MID(A1,7,6),“00-00-00”),TEXT(MID(A1,7,8),“00000000”)),2),LEFT(20090523-IF(LEN(A1)=15,19&TEXT(MID(A1,7,6),“00-00-00”),TEXT(MID(A1,7,8),“00000000”)),2)-1)

3、年齡（周歲）：=DATEDIF(A2,B2,“Y”)&“年”&MOD(DATEDIF(A2,B2,“M”),12)&“月”，A2為出生年月，B2為截止日期

二、提取出生年月信息

由于只需要填寫出生日期，因此這里我們只需要關(guān)心身份證號碼的相應(yīng)部位即可，在C2單元格中輸入公式“=IF(LEN(A2)=15,MID(A2,7,4),MID(A2,9,4))”，其中：

LEN(A2)=15：檢查A1單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號碼的長度是否是15位。

MID(A2,7,4)：從A1單元格中字符串的第7位開始提取四位數(shù)字，本例中表示提取15位身份證號碼的第7、8、9、10位數(shù)字。

MID(A2,9,4)：從A1單元格中字符串的第9位開始提取四位數(shù)字，本例中表示提取18位身份證號碼的第9、10、11、12位數(shù)字。

IF(LEN(A2)=15,MID(A2,7,4),MID(A2,9,4))：IF是一個邏輯判斷函數(shù)，表示如果A2單元格是15位，則提取第7位開始的四位數(shù)字，如果不是15位則提取自第9位開始的四位數(shù)字。

如果需要顯示為“70年12月”這樣的格式，請使用DATE格式，并在“單元格格式→日期”中進行設(shè)置。

三、提取性別信息

例如性別信息統(tǒng)一在B列填寫，可以在B2單元格中輸入公式“=IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,“男”,“女”)”，其中：

LEN(A2)=15：檢查身份證號碼的長度是否是15位。

MID(A2,15,1)：如果身份證號碼的長度是15位，那么提取第15位的數(shù)字。MID(A2,17,1)：如果身份證號碼的長度不是15位，即18位身份證號碼，那么應(yīng)該提取第17位的數(shù)字。

MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對提出來的數(shù)值除以2以后所得到的余數(shù)。IF(MOD(IF(LEN(A2)=15,MID(A2,15,1),MID(A2,17,1)),2)=1,“男”,“女”)：如果除以2以后的余數(shù)是1，那么B2單元格顯示為“男”，否則顯示為“女”。

回車確認后，即可在B2單元格顯示正確的性別信息，接下來就是選中填充柄直接拖曳。

四、根據(jù)身份證號碼（15位和18位通用）自動提取性別和出生年月的自編公式，供需要的網(wǎng)友參考：

說明：假設(shè)公式中的A2是身份證號

1、根據(jù)身份證號碼求性別：=IF(LEN(A2)=15,IF(MOD(VALUE(RIGHT(A2,3)),2)=0,“女”,“男”),IF(LEN(A2)=18,IF(MOD(VALUE(MID(A2,15,3)),2)=0,“女”,“男”),“身份證錯”))

2、根據(jù)身份證號碼求出生年月：=IF(LEN(A2)=15,CONCATENATE(“19”,MID(A2,7,2),“.”,MID(A2,9,2)),IF(LEN(A2)=18,CONCATENATE(MID(A2,7,4),“.”,MID(A2,11,2)),“身份證錯”))

3、根據(jù)身份證號碼求年齡：=IF(LEN(A2)=15,year(now())-1900-VALUE(MID(A2,7,2)),if(LEN(A2)=18,year(now())-VALUE(MID(A2,7,4)),“身份證錯”))

五、輸入身份證號的同時顯示戶口所在地：

假設(shè)在SHEET2中A列為六位代碼，B列為對應(yīng)區(qū)域，然后SHEET1中，B2為身份證號碼，則在空白單元格中輸入：

=IF(A1=“",”“,VLOOKUP(--MID(A1,1,6),Sheet2!A:B,2,))此公式適用于SHEET2中A列為六位代碼為數(shù)字格式。

如果SHEET2中A列為六位代碼為文本格式，則公式為： =IF(A2=”“,”“,VLOOKUP(MID(A2,1,6),Sheet2!A:B,2,))

如果引用的表二的名稱不是“Sheet2”,而是“表二”，則把以上公式的“Sheet2“改為“表二”

身份證的前六位表示戶口所在地。需要另建一個工作表存放全國各地（縣）的代碼，然后用vlookup函數(shù)。

這步做了，在SHEET2中A列為六位代碼，B列為對應(yīng)區(qū)域，然后SHEET1中，=VLOOKUP(tz($B2),表二!$A$1:$B$3239,2,FALSE)，用了這個函數(shù)，還是不行

轉(zhuǎn)Excel表格中根據(jù)身份證號碼自動填出生日期、計算年齡 18位身份證號碼轉(zhuǎn)換成出生日期的函數(shù)公式：如果E2中是身份證,在F2 中求出出生日期，F(xiàn)2=DATE(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位都可以的公式：轉(zhuǎn)換出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),”#-00-00“),”19“& TEXT(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)))

WPS表格提取身份證詳細信息

一、身份證號的位數(shù)判斷

在B2單元格輸入如下公式“=LEN($A2)”，回車后即可得到A2單元格身份證號碼的數(shù)字位數(shù)，如圖1所示。LEN($A2)公式的含義是求出A2單元格字符串中字符的個數(shù)。由于當初身份證輸入時就是以文本形式輸入的，所以用此函數(shù)正可以很方便地求到身份證號碼的位數(shù)。

二、判斷男女性別

第一步：提取性別識別數(shù)字。如果是18位身份證，那么倒數(shù)第二位就是性別判斷的識別數(shù)字，奇數(shù)為男，偶數(shù)則為女。而如果是15位身份證，那么倒數(shù)第一位則是性別識別數(shù)字，同樣奇數(shù)為男，偶數(shù)為女。所以首先應(yīng)該提取這兩種身份證的性別識別數(shù)字。在C2單元格輸入公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”，回車后，立刻可以得到相應(yīng)的識別數(shù)字了。如圖2 所示。

圖2 WPS中鍵入函數(shù)

函數(shù)MID(A2,17,1)的作用是從A2單元格字符串的第17個字開始截取1個字符，這正好是倒數(shù)第二個字符；函數(shù)RIGHT(A2,1)的作用則是從A2單元格字符串的右側(cè)開始截取1個字符，也就是倒數(shù)第一個字符。至于IF函數(shù)，則進行相應(yīng)的判斷。公式“=IF(B2=18,MID(A2,17,1),RIGHT(A2,1))”的作用是“如果B2單元格等于18，那么從A2單元格字符串的第17位開始截取1個字符，否則從A2單元格字符串的右側(cè)截取1個字符”。

第二步：根據(jù)識別數(shù)字判斷男女性別，在D2單元格輸入公式 “=IF(ISEVEN($C2)=TRUE,”女“,”男“)”，回車后就可以得到的結(jié)果了。如圖3所示。

圖3 WPS中鍵入函數(shù)判斷男、女性別

此處函數(shù)ISEVEN($C2)的作用是判斷C2單元格的數(shù)字是否為偶數(shù)，如果是，則返回結(jié)果TRUE，否則返回結(jié)果FALSE。因此，公式“=IF(ISEVEN($C2)=TRUE,”女“,”男“)”的含義就是如果C2單元格數(shù)字為偶數(shù)，那么在當前單元格顯示“女”，否則顯示

三、出生日期提取

第一步：提取數(shù)字型出生日期。在E2單元格輸入公式 “=IF($B2=18,MID($A2,7,8),”19“&MID($A2,7,6))”并回車，如圖4所示。這種形式的出生日期我們并不常用，此處提取出來只是為了我們下一步計算年齡時要用，也是一個輔助列?！?9“&MID($A2,7,6)的意思是把字符“19”和 MID($A2,7,6)截取到的6個字符合并在一起成為一個新的字符串。其它的，就不用再解釋了吧？

圖4 WPS中鍵入函數(shù)判斷出生日期

第二步：提取帶連接符的出生日期。在F2單元格輸入公式 “=IF($B2=18,MID($A2,7,4)&”-“&MID($A2,11,2)&”-“&MID($A2,13,2),”19“&MID($A2,7,2)&”-“&MID($A2,9,2)&”-“&MID($A2,11,2))” 并回車，如圖5所示，可以得到我們常用的如 “1977-08-11”的格式。此處用到的函數(shù)在前面已經(jīng)有過解釋，所以不再羅嗦了。

圖5 WPS中鍵入函數(shù)判斷特殊日期

四、公式計算年齡

在G2單元格輸入公式“=YEAR(NOW())-LEFT(E2,4)”并回車，年齡計算就這么簡單，用當前的年份減去出生的年份就行了。如圖6所示。函數(shù)YEAR(NOW())可以返回當前的年數(shù)“2007”，而LEFT(E2,4)則從E2單元格的左側(cè)截取4個字符，也就是出生年份。

圖6 WPS中用公式計算年齡

最后，選中B2:G2單元格，向下拖動填充句柄至最后一行，那么我們所需要的所有數(shù)據(jù)就有了，再選中B列、C列、E列，并點擊右鍵，在彈出的右鍵菜單中點擊“隱藏”命令，將這些輔助列隱藏起來，就可以得到一份完整的表格了。

很簡單吧？利用WPS的函數(shù)功能，只需要設(shè)置一下相應(yīng)的參數(shù)，把一個個隱藏在身份證號碼里的信息提取出來，完成上面所有操作用時也不到半小時，將所得到的表格美化一下，就可以向領(lǐng)導(dǎo)交差了，呵呵。

Excel輸入身份證號碼及提取信息

在日常辦公中Excel是大家普遍使用的辦公助手，在制作表格的時候，輸入身份證號碼也是大家經(jīng)常遇到的。有什么辦法能更快、更好的輸入身份證號碼呢？下面就教你兩招！

一、解決輸入身份證號碼的問題

默認情況下在Excel當前單元格中輸入的數(shù)字位數(shù)如果超過11位（不含11位）時，系統(tǒng)將以“科學(xué)記數(shù)”格式顯示輸入的數(shù)字；當輸入的數(shù)字位數(shù)超過15位（不含15位）時，系統(tǒng)將15位以后的數(shù)字全部顯示為“0”。這樣一來，如果我們要輸入身份證號碼（15位或18位），身份證號碼就不能正確顯示出來了。此時，我們有兩種辦法解決此問題：

1、利用數(shù)字標簽

選中需要輸入身份證號碼的單元格（區(qū)域），打開“單元格格式”對話框，進入“數(shù)字”標簽，在“分類”下面選中“文本”選項，確定退出，再輸入身份證號碼即可。

2、在輸入身份證號碼時，在號碼前面加上一個英文狀態(tài)下的單引號“'”，即可讓身份證號碼完全顯示出來（該單引號在確認后是不會顯示出來的）。

二、讓單元格內(nèi)自動添加身份證前面相同部分

大家知道，身份證號碼的前6位，是省、市、區(qū)（縣）的代號（如資中縣是“511025”等），如果持證人都是資中縣的，輸入這樣的的身份證號碼，可以讓單元格格式的“數(shù)字”標簽幫你減輕輸入的工作量：選中需要輸入身份證號碼的單元格區(qū)域，單擊Excel上面菜單欄的格式打開“單元格”對話框（或者右鍵點擊設(shè)置單元格格式），進入默認的“數(shù)字”標簽，在“分類”下面選中最下面的“自定義”選項，然后在右側(cè)“類型”下面的方框中輸入：”511025“@，確定退出。以后輸入身份證號碼第7位及以后的數(shù)字（如“220324621”）后，系統(tǒng)會自

動在前面加上相應(yīng)的代碼，形成完整的身份證號碼（如“***”）。如果是18位的身份證號碼，則這樣設(shè)置比較好：“自定義”選項右側(cè)“類型”下面的方框中輸入：”51102519“@，確定退出。以后輸入身份證號碼9位及以后的數(shù)字（如“2203246211”）后，系統(tǒng)會自動在前面加上相應(yīng)的51102519，形成完整的身份證號碼（如“***211”）。

同理，其它需要輸入數(shù)字含相同部分的也可按照這個方法輸入。

Excel身份證號碼提取個人信息

一、分析身份證號碼

其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，無論是15位還是18位的身份證號碼，其中都保存了相關(guān)的個人信息。

15位身份證號碼：第7、8位為出生年份(兩位數(shù))，第9、10位為出生月份，第11、12位代表出生日期，第15位代表性別，奇數(shù)為男，偶數(shù)為女。

18位身份證號碼：第7、8、9、10位為出生年份(四位數(shù))，第11、第12位為出生月份，第13、14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女。

例如，某員工的身份證號碼(15位)是***，那么表示1972年8月7日出生，性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來，不僅快速簡便，而且不容易出錯，核對時也只需要對身份證號碼進行檢查，肯定可以大大提高工作效率。

二、提取個人信息

這里，我們需要使用IF、LEN、MOD、MID、DATE等函數(shù)從身份證號碼中提取個人信息。如圖1所示，其中員工的身份證號碼信息已輸入完畢(C列)，出生年月信息填寫在D列，性別信息填寫在B列。

1.提取出生年月信息

由于上交報表時只需要填寫出生年月，不需要填寫出生日期，因此這里我們只需要關(guān)心身份證號碼的相應(yīng)部位即可，即顯示為“7208”這樣的信息。在D2單元格中輸入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：

LEN(C2)=15：檢查C2單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號碼的長度是否是15位。

MID(C2,7,4)：從C2單元格中字符串的第7位開始提取四位數(shù)字，本例中表示提取15位身份證號碼的第7、8、9、10位數(shù)字。

MID(C2,9,4)：從C2單元格中字符串的第9位開始提取四位數(shù)字，本例中表示提取18位身份證號碼的第9、10、11、12位數(shù)字。

IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一個邏輯判斷函數(shù)，表示如果C2單元格是15位，則提取第7位開始的四位數(shù)字，如果不是15位則提取自第9位開始的四位數(shù)字。

如果需要顯示為“70年12月”這樣的格式，請使用DATE格式，并在“單元格格式→日期”中進行設(shè)置。

2.提取性別信息

由于報表中各位員工的序號編排是按照上級核定的編制進行的，因此不可能按照男、女固定的順序進行編排，如果一個一個手工輸入的話，既麻煩又容

易出錯

例如性別信息統(tǒng)一在B列填寫，可以在B2單元格中輸入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男“,”女“)”，其中：

LEN(C2)=15：檢查身份證號碼的長度是否是15位。

MID(C2,15,1)：如果身份證號碼的長度是15位，那么提取第15位的數(shù)字。

MID(C2,17,1)：如果身份證號碼的長度不是15位，即18位身份證號碼，那么應(yīng)該提取第17位的數(shù)字。

MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對提出來的數(shù)值除以2以后所得到的余數(shù)。

IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男“,”女“)：如果除以2以后的余數(shù)是1，那么B2單元格顯示為“男”，否則顯示為“女”。

回車確認后，即可在B2單元格顯示正確的性別信息，接下來就是選中填充柄直接拖曳。如圖2所示，現(xiàn)在這份報表無論是提取信息或是核對，都方便多了！

在中驗證身份證 ．excel．．．．．．．．．．．=IF(RIGHT(B2)=MID(”10X98765432“,MOD(SUM(MID(B2,ROW($1:$17),1)*2^(18-R．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．OW($1:$17))),11)+1,1),”真“,”假“)(同時按 ．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．Ctrl+Shift+Enter)．．．．．．．．．．．．．．．．．

圖1

1．提取性別信息

由于報表中各位員工的序號編排是按照上級核定的編制進行的，因此不可能按照男、女固定的順序進行編排，如果一個一個手工輸入的話，既麻煩又容易出錯。例如性別信息統(tǒng)一在B列填寫，可以在B2單元格中輸入公式”=IF(MOD((IF(LEN(B2)=15,RIGHT(B2),MID(B2,17,1))),2)=0,“女”,“男”)“，如圖2所示。

圖2 身份證號全部改為18位:

公式”=RIGHT(B2,1)=MID(“10X98765432”,MOD(SUM(MID(B2,ROW(INDIRECT(“1:17”)),1)*2^(18-ROW(INDIRECT(“1:17”)))),11)+1,1)“。15位轉(zhuǎn)18位？？？

=IF(LEN(A1)=15,REPLACE(A1,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(--MID(REPLACE(A1,7，19),ROW(1:17),1),2^(18-ROW(1:17))),11)+1,1),A1)=IF(LEN(E4)=15,REPLACE(E4,7，19)&MID(”10X98765432“,MOD(SUM(MID(REPLACE(E4,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),E4)

假設(shè)A列自A2起是身份證號（15位或18位）。

1、身份證號全部改為18位，輸入數(shù)組公式：=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)注意：數(shù)組公式輸入方法：輸入公式后不要按回車，而是按。．．．．．．．．．．．．．．．．．．．．．．．．．．Ctrl+Shift+Enter．．．．．．．．．．．．．．．．

2、身份證號全部改為15位，輸入公式：=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))

EXCEL中如何提取身份證出生日期和性別信息以及檢驗身份證號碼的正確性

中國居民身份證號碼是一組特征組合碼，原為１５位，現(xiàn)升級為１８位，其編碼規(guī)則為：

１５位：６位數(shù)字常住戶口所在縣市的行政區(qū)劃代碼，６位數(shù)字出生日期代碼，３位數(shù)字順序碼。

１８位：６位數(shù)字常住戶口所在縣市的行政區(qū)劃代碼，８位數(shù)字出生日期代碼，３位數(shù)字順序碼和１位檢驗碼。

其中３位數(shù)字順序碼，是為同一地址碼的同年同月同日出生人員編制的順序號，偶數(shù)的為女性，奇數(shù)的為男性。

１、提取籍貫地區(qū)的行政區(qū)劃代碼（A2為身份證號，下同）

１５與１８位通用：=LEFT(A2,6)

如果有一個編碼和省份地區(qū)的對照表，可以用VLOOKUP函數(shù)來提取地區(qū)信息。

２、提取出生日期信息

１５與１８位通用：．．．．．．．．．=--TEXT(IF(LEN(A2)=15,19,”“)&MID(A2,7,6+IF(LEN(A2)=18,2,0)),”#-00-00“．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．)．簡化公式： =--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．(請將輸入公式的單元格格式設(shè)置為日期格式）．．．．．．．．．．．．．．．．．．．．．

３、提取性別信息

１５位：=IF(MOD(RIGHT(A2),2)=1,”男“,”女“)

１８位：=IF(MOD(MID(A2),17,1)=1,”男“,”女“)

１５與１８位通用：=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,”男“,”女“)

簡化公式：=IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“)

４、檢驗身份證號碼的正確性

１８位身份證號碼的最后一位是檢驗碼，它是根據(jù)身份證前17位數(shù)字依照規(guī)則計算出來的，其值０～９或X。一般情況只要有一位數(shù)字輸入錯誤，依照規(guī)則計算后就會與第18位數(shù)不符。當然不排除按錯誤號碼計算后恰好與檢驗碼相符的情況，但這種情況出現(xiàn)的可能性較低。因此，對１８位號碼的驗證采用如下公式：

=MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2,1)

對于１５位身份證，由于沒有檢驗碼，我們只能簡單地去判斷出生日期代碼是否是一個有效的日期，避免輸入一些像“731302”或“980230“等這樣不存在的日期。

=ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))

綜合１５位和１８位后的通用公式為：

=IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),IF(LEN(A2．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))))．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．

由于目前１５位身份證號碼已經(jīng)很少了，如果對１５位的號碼不需要作進一步的判斷，則公式可以簡化成：

=IF(LEN(E3)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(E3,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(E3),LEN(E3)=15)

將上面的公式放到B2單元格，如果結(jié)果為TRUE，則身份證號是正確的，結(jié)果為FALSE則是錯誤的。

你也可以將上述公式放在數(shù)據(jù)有效性中，防止錄入錯誤的身份證號。操作方法:選擇需要輸入身份證的全部單元格區(qū)域,比如A2:A10,點菜單”數(shù)據(jù)“-”有效性“,在”允許“的下拉框中選擇”自定義“,在”公式“輸入上面的15位和18位通用公式,確定以后即可。注意:公式里的”A2“是你剛才選定要輸入身份證的單元格區(qū)域的第一個單元格,如果你是要在C3:C20輸入身份證號,則將公式里的”A2“改為

”C3“。另外,你也可以先設(shè)置好某單個單元格的數(shù)據(jù)有效性(這時公式的A2改為選定的單元格),再用格式刷將其格式刷到其他需要相同設(shè)置的單元格。

５、１５位升為１８位 ．．．．．．．．．．

=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(MID．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．．)))),11)+1,1),A2)．．．．．．．．．．．．．．．．．

６、１８位轉(zhuǎn)換為１５位

=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)

7、示例

表中公式:

B2 =IF(LEN(A2)=18,MID(”10X98765432“,MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1)=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),”#-00-00“))))

C2 =IF(A2<>”“,TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“)+0,)

D2 =IF(A2<>”“,IF(MOD(RIGHT(LEFT(A2,17)),2),”男“,”女“),)

E2 =IF(A2<>”“,DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),”#-00-00“),TODAY(),”y“),)

F2 =IF(A2<>”“,VLOOKUP(LEFT(A2,2),地區(qū)表!A:D,2,),)

H2

=IF(LEN(A2)=15,REPLACE(A2,7，19)&MID(”10X98765432“,MOD(SUMPRODUCT(MID(REPLACE(A2,7，19),ROW(INDIRECT(”1:17“)),1)*2^(18-ROW(INDIRECT(”1:17“)))),11)+1,1),A2)

I2 =IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)

身份證有15位和18位兩種，身份證位數(shù)是否正確，我們可以用LEN函數(shù)判斷。但身份證上的日期是否合法：月份是否在1-12之間，日期是否在1-31之間，并且2月份只有28或29天，其他月份30或31天，都不能超過范圍。另外一般規(guī)定6歲以上才可以辦理身份證，也就是年份也有一個超范圍的可能性。綜合起來看，有三類錯誤：“身份證位數(shù)不對”、“月日錯誤”、“年份錯誤”。假定身份證號碼在B1單元格，下面的公式可以綜合判斷以上三種錯誤： =IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MONTH(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))+1900,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))),”月日錯誤“,”“))),”身份證位數(shù)不對“)&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),”年份錯誤“,”“)

Excel中輸入身份證號碼及提取信息

一、解決輸入身份證號碼的問題

默認情況下在Excel當前單元格中輸入的數(shù)字位數(shù)如果超過11位（不含11位）時，系統(tǒng)將以“科學(xué)記數(shù)”格式顯示輸入的數(shù)字；當輸入的數(shù)字位數(shù)超過15位（不含15位）時，系統(tǒng)將15位以后的數(shù)字全部顯示為“0”。這樣一來，如果我們要輸入身份證號碼（15位或18位），身份證號碼就不能正確顯示出來了。但我們有兩種辦法解決此問題：

1、利用數(shù)字標簽

選中需要輸入身份證號碼的單元格（或區(qū)域），打開“單元格格式”對話框，進入“數(shù)字”標簽，在“分類”下面選中“文本”選項，確定退出即可。

2、在輸入身份證號碼時，在號碼前面加上一個英文單引號“'”，即可讓身份證號碼完全顯示出來（該單引號不顯示）。

二、讓單元格內(nèi)自動添加身份證前面相同部分

身份證號碼的前6位，是省、市、區(qū)（縣）的代號（如資中縣：511025），如果持證人都是資中縣的，輸入這樣的的身份證號碼，可以讓單元格格式的“數(shù)字”標簽幫你減輕輸入的工作量：選中需要輸入身份證號碼的單元格區(qū)域，單擊Excel上面菜單欄的格式打開“單元格”對話框（或者右鍵點擊設(shè)置單元格格式），進入默認的“數(shù)字”標簽，在“分類”下面選中最下面的“自定義”選項，然后在右側(cè)“類型”下面的方框中輸入：”511025“@，確定。以后輸入身份證號碼9位及以后的數(shù)字（如“197303246211”）后，系統(tǒng)會自動在前面加上51102519，形成完整的身份證號碼（如“***211”）。

其它需要輸入數(shù)字含相同部分的也可按照這個方法輸入。

Excel從身份證號碼中提取個人信息。

如18位身份證號碼：前6位是公民戶口登記地，第7到14位代表出生日期，第17位代表性別，奇數(shù)為男，偶數(shù)為女。(15位身份證號碼15位數(shù)代表性別)

比如，某員工的身份證號碼(18位)是***241，那么表示1972年8月17日出生，性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來，不僅快速簡便，而且不容易出錯。

提取個人信息 需要使用IF、LEN、MOD、MID、DATE等函數(shù)。某表格，其中員工的身份證號碼信息已輸入到C列，出生年月信息填寫在D列，性別信息填寫在B列。

1.提取出生年月信息

由于上交報表時只需要填寫出生年月，不需要填寫出生日期，因此這里我們只需要關(guān)心身份證號碼的相應(yīng)部位即可，即顯示為“7208”這樣的信息。在D2單元格中輸入公式“=IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))”，其中：

LEN(C2)=15：檢查C2單元格中字符串的字符數(shù)目是否等于15，意即檢查身份證號碼的長度是否是15位。

MID(C2,7,4)：從C2單元格中字符串的第7位開始提取四位數(shù)字，即提取15位身份證號碼的第7、8、9、10位數(shù)字。

MID(C2,9,4)：從C2單元格中字符串的第9位開始提取四位數(shù)字，即提取18位身份證號碼的第9、10、11、12位數(shù)字。

IF(LEN(C2)=15,MID(C2,7,4),MID(C2,9,4))：IF是一個邏輯判斷函數(shù)，表示如果C2單元格是15位，則提取第7位開始的四位數(shù)字，否則就從第9位開始提取四位數(shù)字。

如果需要顯示為“70年12月”這樣的格式，請使用DATE格式，并在“單元格格式→日期”中進行設(shè)置。

2.提取性別信息

由于報表中各位員工的序號編排是按照上級核定的編制進行的，因此不可能

按照男、女固定的順序進行編排，如果一個一個手工輸入的話，既麻煩又容易出錯

例如性別信息統(tǒng)一在B列填寫，可以在B2單元格中輸入公式“=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男“,”女“)”，其中：

LEN(C2)=15：檢查身份證號碼的長度是否是15位。

MID(C2,15,1)：如果身份證號碼的長度是15位，那么提取第15位的數(shù)字。

MID(C2,17,1)：如果身份證號碼的長度不是15位，即18位身份證號碼，那么應(yīng)該提取第17位的數(shù)字。

MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對提出來的數(shù)值除以2以后所得到的余數(shù)。

IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,”男“,”女")：如果除以2以后的余數(shù)是1，那么B2單元格顯示為“男”，否則顯示為“女”。

回車確認后，即可在B2單元格顯示正確的性別信息，接下來就是選中填充柄直接拖曳。如圖2所示，現(xiàn)在這份報表無論是提取信息或是核對，都方便多了！

第五篇：Excel中根據(jù)身份證號碼自動填出生日期、性別、年齡

Excel中根據(jù)身份證號碼自動填出生日期、性別、計算年齡

出生日期：F2=DATE(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,“女”,“男”)計算年齡：=IF(LEN(B2)=15,109-VALUE(MID(B2,7,2)),if(LEN(B2)=18,2009-VALUE(MID(B2,7,4)),“身份證錯”))

說明：VALUE將一個文本字符串轉(zhuǎn)換成數(shù)值，LEN返回文本字符串中的字符個數(shù)，MID從文本字符串中指定的起始位置起返回指定長度的字符，MID(文本,開始字符,所取字符數(shù))。109表示當前日期為2009年，如果是2010年則改為110，2009表示當前日期為2009年。DATE代表日期的數(shù)字，MIDB自文字的指定起始位置起提取指定長度的字符串