第一篇：在Excel中根據(jù)身份證號碼自動生成出生生日期和性別
在Excel中根據(jù)身份證號碼自動生成出生生日期和性別
【身份證號判斷性別的方法】 18位的看倒數(shù)第二位，奇男偶女； 15位的看倒數(shù)第一位，奇男偶女。假如：身份證所在的列為C2
首先要判斷該身份證的長度，在excel中使用len函數(shù)來獲取len(C2)
如果是18位的就取第17位，即倒數(shù)第二位，Excel中使用mid函數(shù)還獲取指定位置開始，指定長度的字符串，如果是15位的就去最后一位也就是第15位 =If(len(C2)=15,mid(C2,15,1),mid(C2,17,1))
獲取到這個數(shù)后，再判斷該數(shù)是奇數(shù)還是偶數(shù)就可以得出性別了，excel中使用mod函數(shù)實現(xiàn) =IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,“男”,“女”)獲取年齡的函數(shù)如下：
=DATEDIF(TEXT((LEN(C2)=15)*19&MID(C2,7,6+(LEN(C2)=18)*2),“00-00-00”),TODAY(),“y”)
在Excel中根據(jù)身份證號碼自動生成出生生日期和性別 根據(jù)身份證號碼自動生成出生生日期 方法一：
15位身份證號：*** 輸出出生日期1979/06/05 =CONCATENATE(“19”,MID(E2,7,2),“/”,MID(E2,9,2),“/”,MID(E2,11,2))公式解釋： a.MID(E2,7,2)為在身份證號碼中獲取表示年份的數(shù)字的字符串
b.MID(E2,9,2)為在身份證號碼中獲取表示月份的數(shù)字的字符串 c.MID(E2,11,2)為在身份證號碼中獲取表示日期的數(shù)字的字符串
d.CONCATENATE(“19”,MID(E2,7,2),“/”,MID(E2,9,2),“/”,MID(E2,11,2))目的就是將多個字符串合并在一起顯示。
18位身份證號：***521 輸出出生日期1979/06/05 =CONCATENATE(MID(E2,7,4),“/”,MID(E2,11,2),“/”,MID(E2,13,2))方法二：
15位身份證號：*** 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))18位身份證號：***521 出生日期790605 =IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))公式解釋：
LEN(E2)=15：檢查E2單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號碼的長度是否是15位。
MID(E2,7,6)：從E2單元格中字符串的第7位開始提取6位數(shù)字，本例中表示提取15位身份證號碼的第7、8、9、10、11、12位數(shù)字。
MID(E2,9,6)：從C2單元格中字符串的第9位開始提取6位數(shù)字，本例中表示提取18位身份證號碼的第9、10、11、12、13、14位數(shù)字。
=IF(LEN(E2)=15,MID(E2,7,6),MID(E2,9,6))：IF是一個邏輯判斷函數(shù)，表示如果額E2單元格是15位，則提取第7位開始的6位數(shù)字，如果不是15位則提取自第9位開始的6位數(shù)字。根據(jù)身份證號碼自動生成性別： 方法一：
在C列輸入身份證號，在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位身份證號碼，那么應該提取第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單元格顯示為“男”，否則顯示為“女”。
15位身份證，看最后一位，奇男偶女；18位的，看第17位數(shù)，也是奇男偶女。方法二：
如果你是想在Excel表格中，從輸入的身份證號碼內(nèi)讓系統(tǒng)自動提取性別，可以輸入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,“男”,“女”),IF(MOD(MID(C2,17,1),2)=1,“男”,“女”))公式內(nèi)的“C2”代表的是輸入身份證號碼的單元格。
第二篇：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自文字的指定起始位置起提取指定長度的字符串
第三篇：如何在Excel中填寫身份證號碼后自動提取出生年月日、年齡、性別
如何在Excel中填寫身份證號碼后自動提取出生年月日、年齡、性別
本公式只適合于18位身份證號碼即二代居民身份證。
假如：A1輸入身份證號碼、B1顯示性別、C1顯示出生日期、D1顯示周歲。（要注意A1、B1、C1、D1分別對應A列1行B列1行C列1行D列1行，如你的A1單元格不為你的表格第一項身份證編號則需將A1替換成你選取的單元格）
需要設(shè)置：A1單元格為文本格式、B1和D1為常規(guī)格式、C1為日期格式。（選中列右鍵設(shè)置單元格格式可選取格式）
1、顯示性別：
B1輸入公式：
=IF(A1=“",”“,IF(AND(LEN(A1)<>15,LEN(A1)<>18),”錯誤
“,IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“))),”錯誤
“,IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)))
2、C1輸入公式：
=IF(A1=”“,”“,IF(AND(LEN(A1)<>15,LEN(A1)<>18),”錯誤
“,IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“))),”錯誤
“,IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)))
3、D1輸入公式：
=IF(ISERROR(1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“))),”錯誤
“,IF(OR((1*(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“)))
“,DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),”#-00-00“),TODAY(),”y")))
注意：A1、B1、C1、D1分別對應A列1行B列1行C列1行D列1行，如你的A1單元格不為你的表格第一項身份證編號則需將A1替換成你選取的單元格
如下圖
那么上述B1、C1、D1輸入函數(shù)中應將A1替換成F4即可。（替換簡便方法：ctrl+F鍵調(diào)出查找對話框選擇—替換—查找內(nèi)容填A1—替換填你填寫身份證號碼的第一項單元格名稱）
第四篇：EXCEL中如何從身份證號碼提取出生年月日及性別
EXCEL中如何從身份證號碼提取出生年月日及性別
近段時間，各校都忙壞了，學生城鎮(zhèn)居民醫(yī)療保險材料要保，今年必須提供每位學生的身份證號碼，還有性別、出生日期等信息；新生學籍要報，學籍信息包含50多項，其中也有身份證號碼、性別、出生日期等信息??。為了簡化工作，提高效率，特整理出從身份證號碼里提取出生年月日及性別等信息的方法，希望對大家有所幫助。
一、分析身份證號碼
其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，無論是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位)是***242，那么表示1989年8月7日出生，性別為女。如果能想辦法從這些身份證號碼中將上述個人信息提取出來，不僅快速簡便，而且不容易出錯，核對時也只需要對身份證號碼進行檢查，肯定可以大大提高工作效率。
二、轉(zhuǎn)換身份證號碼格式
我們先將學生的身份證號完整地輸入到EXCEL表格中，這時默認為“數(shù)字”格式（單元格內(nèi)顯示的是科學記數(shù)法的格式），需要更改一下數(shù)字格式。選中該列中的所有身份證號后，右擊鼠標，選擇“設(shè)置單元格格式”。在彈出對話框中“數(shù)字”標簽內(nèi)的“分類”設(shè)為“文本”，然后點擊確定。
三、提取個人信息
這里，我們需要使用IF、LEN、MOD、MID、DATE等函數(shù)從身份證號碼中提取個人信息。假設(shè)學生的身份證號碼信息已輸入完畢(C列)，出生年月信息填寫在D列，性別信息填寫在B列。
1.提取出生年月信息
如果上交報表時需要填寫出生年月日，我們需要關(guān)心身份證號碼的相應部位即可，即顯示為“19890807”這樣的信息。在D2單元格中輸入公式
=IF(LEN(C2)=15,“19”&MID(C2,7,6),MID(C2,7,8))，其中：
文本函數(shù)MID返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。語法：MID(text,start_num,num_chars)即MID(文本,開始字符,所取字符數(shù))，Text 是包含要提取字符的文本字符串。
Start_num 是文本中要提取的第一個字符的位置。文本中第一個字符的start_num 為 1，以此類推。
Num_chars 指定希望 MID 從文本中返回字符的個數(shù)。
字符個數(shù)計算函數(shù)LEN返回文本字符串中的字
符數(shù)。語法：LEN(text)
Text 是要查找其長度的文本?？崭駥⒆鳛樽址M行計數(shù)。
LEN(C2)=15：檢查C2單元格中字符串的字符數(shù)目，本例的含義是檢查身份證號碼的長度是否是15位。
MID(C2,7,4)：從C2單元格中字符串的第7位開始提取6位數(shù)字，本例中表示提取15位身份證號碼的第7、8、9、10、11、12位數(shù)字。
“19”&:表示如果身份證號碼是15位，在年份前加“19”。
MID(C2,7,8)：從C2單元格中字符串的第7位開始提取8位數(shù)字，本例中表示提取18位身份證號碼的第7、8、9、10、11、12、13、14位數(shù)字。
IF(LEN(C2)=15,MID(C2,7,6),MID(C2,7,8))：IF是一個邏輯判斷函數(shù)，表示如果C2單元格是15位，則提取第7位開始的6位數(shù)字并在年份前加“19”，如果不是15位則提取自第7位開始的8位數(shù)字。
如果需要顯示為“1989-08-07”這樣的格式，可以把上面的公式稍加修改即可：
=IF(LEN(C2)=15,“19”&MID(C2,7,2)&“-”&MID(C2,9,2)&“-”&MID(C2,11,2),MID(C2,7,4)&“-”&MID(C2,11,2)&“-”&MID(C2,13,2))。
解釋：如果身份證號碼是15位，則從第7位開始取兩位數(shù)字，并在這兩位數(shù)字前加“19”，后面加“-”,從第9位開始取兩位數(shù)字，并在這兩位數(shù)字后面加“-”,從第11位開始取兩位數(shù)字。否則，從第7位開始取四位數(shù)字，并在這四位數(shù)字后面加“-”,從第11位開始取兩位數(shù)字，并在這兩位數(shù)字后面加“-”,從第13位開始取兩位數(shù)字。
也可以使用DATE格式，并在“單元格格式→日期”中進行設(shè)置。
如果學生的身份證號全部都是18位的新一代身份證，可以以把上面的公式進行簡化：
=MID(C2,7,4)&“-”&MID(C2,11,2)&“-”&MID(C2,13,2)
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位身份證號碼，那么應該提取第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單元格顯示正確的性別信息，接下來就是選中填充柄直接拖曳?，F(xiàn)在這份報表無論是提取信息或是
核對，都方便多了。
提取性別也可以使用下面這個公式（無論是15位還是18位）： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,“男”,“女”),IF(MOD(MID(C2,17,1),2)=1,“男”,“女”)
如果學生的身份證號全部都是18位的新一代身份證，可以使用下面的公式： =IF(MID(C2,17,1)/2=TRUNC(MID(C2,17,1)/2),“女”,“男”)
數(shù)學函數(shù)TRUNC 將數(shù)字的小數(shù)部分截去，返回整數(shù)。
語法：TRUNC(number,num_digits)
Number 需要截尾取整的數(shù)字。
Num_digits 用于指定取整精度的數(shù)字。Num_digits 的默認值為 0。
說明：函數(shù) TRUNC 和函數(shù) INT 類似，都返回整數(shù)。函數(shù) TRUNC 直接去除數(shù)字的小數(shù)部分，而函數(shù) INT 則是依照給定數(shù)的小數(shù)部分的值，將其四舍五入到最接近的整數(shù)。函數(shù) INT 和函數(shù) TRUNC 在處理負數(shù)時有所不同。
如果身份證號的輸入已是15或18位，還可以用公式：
=IF(MOD(LEFT(RIGHT(C2,(LEN(C2)=18)+1)),2),“男”,“女”）
RIGHT 根據(jù)所指定的字符數(shù)返回文本字符串中最后一個或多個字符。
語法：RIGHT(text,num_chars)
Text 是包含要提取字符的文本字符串。
Num_chars 指定希望 RIGHT 提取的字符數(shù)。
LEFT 基于所指定的字符數(shù)返回文本字符串中的第一個或前幾個字符。
語法：LEFT(text,num_chars)
Text 是包含要提取字符的文本字符串。
Num_chars 指定要由 LEFT 所提取的字符數(shù)。
第五篇：如何在Excel里根據(jù)身份證號碼自動填寫性別、出生年月、年齡
如何在Excel里根據(jù)身份證號碼自動填寫性別、出生年月、年齡
一、分析身份證號碼
其實，身份證號碼與一個人的性別、出生年月、籍貫等信息是緊密相連的，無論是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)心身份證號碼的相應部位即可，即顯示為“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位身份證號碼，那么應該提取第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)在這份報表無論是提取信息或是核對，都方便多了
點擊咨詢 