excel中如何提取身份證出生日期、性別、檢驗身份證號碼的正確性（網路收集）
2011-09-28 18:24:12|分類： 電腦知識 |標籤：excel複製貼上 |字型大小大中小訂閱
excel中如何提取身份證出生日期、性別、檢驗身份證號碼的正確性
中國居民身份證號碼是一組特徵組合碼，原為１５位，現公升級為１８位，其編碼規則為：
１５位：６位數字常住戶口所在縣市的行政區劃**，６位數字出生日期**，３位數字順序碼。
１８位：６位數字常住戶口所在縣市的行政區劃**，８位數字出生日期**，３位數字順序碼和１位檢驗碼。
其中３位數字順序碼，是為同一位址碼的同年同月同日出生人員編制的順序號，偶數的為女性，奇數的為男性。
１、提取籍貫地區的行政區劃**（a2為身份證號，下同）
１５與１８位通用：=left(a2,6)
如果有乙個編碼和省份地區的對照表，可以用vlookup函式來提取地區資訊。
２、提取出生日期資訊
１５位：=--text(19&mid(a2,7,6),"#-00-00")
１８位：=--text(mid(a2,7,8),"#-00-00")
１５與１８位通用：=--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")
(請將輸入公式的單元格格式設定為日期格式）
３、提取性別資訊
１５位：=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),"男","女")
４、檢驗身份證號碼的正確性
１８位身份證號碼的最後一位是檢驗碼，它是根據身份證前17位數字依照規則計算出來的，其值０～９或x。一般情況只要有一位數字輸入錯誤，依照規則計算後就會與第18位數不符。當然不排除按錯誤號碼計算後恰好與檢驗碼相符的情況，但這種情況出現的可能性較低。
因此，對１８位號碼的驗證採用如下公式：
=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"))))
由於目前１５位身份證號碼已經很少了，如果對１５位的號碼不需要作進一步的判斷，則公式可以簡化成：
=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),len(a2)=15)
將上面的公式放到b2單元格，如果結果為true，則身份證號是正確的，結果為false則是錯誤的。
你也可以將上述公式放在資料有效性中，防止錄入錯誤的身份證號。操作方法:選擇需要輸入身份證的全部單元格區域,比如a2:
a10,點選單"資料"-"有效性",在"允許"的下拉框中選擇"自定義",在"公式"輸入上面的15位和18位通用公式,確定以後即可。注意:公式裡的"a2"是你剛才選定要輸入身份證的單元格區域的第乙個單元格,如果你是要在c3:
c20輸入身份證號,則將公式裡的"a2"改為"c3"。另外,你也可以先設定好某單個單元格的資料有效性(這時公式的a2改為選定的單元格),再用格式刷將其格式刷到其他需要相同設定的單元格。
５、１５位公升為１８位
=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)
６、１８位轉換為１５位
=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),地區表!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)
2010-03-02 16:05
excel中如何從身份證號碼裡提取生年月日及性別等
假如，a1是姓名，b1是身份證號碼，c1是出生年月 可以用以下公式求出 if len b2 15,19 mid b2,7,2 年 mid b2,9,2 月 mid b2,11,2 日 mid b2,7,4 年 mid b2,11,2 月 mid b2,13,2 日 在c2設定單元格格式為 日期 即可...
如何從身份證中提取出生年月及資訊
假如，a1是姓名，b1是身份證號碼，c1是出生年月 可以用以下公式求出 if len b2 15,19 mid b2,7,2 年 mid b2,9,2 月 mid b2,11,2 日 mid b2,7,4 年 mid b2,11,2 月 mid b2,13,2 日 在c2設定單元格格式為 日期 即可...
用Excel提取身份證號碼人資訊
巧用excel 利用身份證號碼提取個人資訊巧用excel 利用身份證號碼提取個人資訊 一 分析身份證號碼 其實，身份證號碼與乙個人的性別 出生年月 籍貫等資訊是緊密相連的，無論是15位還是18位的身份證號碼，其中都儲存了相關的個人資訊。15位身份證號碼 第7 8位為出生年份 兩位數 第9 10位為出...