如何利用Excel制作身份證查詢系統(tǒng)？

梧桐智慧樹 2018-08-25

展開全文

在excel表格中如何使用身份證號碼查詢出：出生日期、年齡、性別、籍貫這些信息呢？首先做一個如下圖的表格出來

提取出生日期

當(dāng)我們在B2單元格輸入身份證號碼時，我們希望B3單元格能夠立馬提取出生年月日的信息，

所以我們要在B3單元格輸入公式：=TEXT(MID(B2,7,8),'0-00-00')

公式解析：用MID函數(shù)提取身份證號碼中出生年月日的數(shù)字，出生年月日在身份證號碼的第7位-15位，提取出來后只是一串?dāng)?shù)字，需要將它轉(zhuǎn)換為日期格式，所以用TEXT函數(shù)將數(shù)字轉(zhuǎn)換為日期。

計算年齡

在B4單元格輸入公式：=DATEDIF(TEXT(MID(B2,7,8),'0-00-00'),TODAY(),'Y')

公式解析：計算年齡先要得到出生日期，所以直接復(fù)制出生日期的公式過來，然后再前面嵌套DATEDIF函數(shù)計算出生日期到今天相差了多少年即可。

判斷性別

身份證號碼中代表性別的數(shù)字是第17位，如果數(shù)字是奇數(shù)則為男生，偶數(shù)則為女生。

所以在B5單元格輸入公式：=IF(ISEVEN(MID(B2,17,1)),'女','男')

公式解析：用MID函數(shù)提取第17位數(shù)字，然后用ISEVEN函數(shù)判斷數(shù)字是否是偶數(shù)，如果是偶數(shù)則返回邏輯值true，否則返回false，最后用if函數(shù)返回性別。

提取籍貫

身份證號碼前兩位數(shù)代表籍貫，此外，我們需要在網(wǎng)上下載一份籍貫對照表，如下圖。

然后在B6單元格輸入公式：=VLOOKUP(--MID(B2,1,2),省份信息!$A$2:$B$35,2,0)

公式解析：用MID函數(shù)提取前2位數(shù)字，MID函數(shù)提取的數(shù)字屬于文本格式的，而對照表里面的編碼是數(shù)值格式，所以需要在MID函數(shù)前面加兩個-（負(fù)號）進行負(fù)負(fù)運算，將文本格式轉(zhuǎn)為數(shù)值格式，再用VLOOKUP函數(shù)跨表查找即可。