在Excel中根據(jù)身份證號碼自動生成出生日期和性別的方法

根據(jù)身份證號碼自動生成生日的方法

方法一：

15位身份證號：410881790605552 輸出出生日期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位身份證號：410881197906055521 輸出出生日期1979/06/05

=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))

方法二：

15位身份證號：410881790605552 出生日期790605

=IF(LEN(E3)=15,MID(E3,7,6),MID(E3,9,6))

18位身份證號：410881197906055521 出生日期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位身份證號碼，那么應(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單元格顯示為“男”，否則顯示為“女”。

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”代表的是輸入身份證號碼的單元格。