1、歸屬地

歸屬地查詢需要提前準(zhǔn)備好地區(qū)編碼對(duì)照表，不過網(wǎng)上下載的地區(qū)編碼表似乎都不全，我做好的示例文檔中的對(duì)照表基本是全的。

建議新建一個(gè)工作表保存地區(qū)對(duì)照表。編碼放在A列，使用文本格式；地區(qū)放在B列。

身份證信息查詢表使用結(jié)構(gòu)化表格，俗稱超級(jí)表或動(dòng)態(tài)表。這種表格以后添加數(shù)據(jù)時(shí)會(huì)自動(dòng)更新表中的公式及單元格格式。

序號(hào)使用這個(gè)公式：

=IF([@身份證號(hào)碼]="","",ROW()-2)

使用這個(gè)公式，添加或減少數(shù)據(jù)，可以自動(dòng)更新序號(hào)。

歸屬地的查詢公式如下：

=IF([@身份證號(hào)碼]="","",VLOOKUP(--MID([@身份證號(hào)碼],1,6),地區(qū)編碼對(duì)照表!A:B,2,0))

2、出生日期

公式如下：

=IF([@身份證號(hào)碼]="","",TEXT(MID([@身份證號(hào)碼],7,8),"0年00月00日"))

3、年齡

公式如下：

=IF([@身份證號(hào)碼]="","",DATEDIF(TEXT(MID([@身份證號(hào)碼],7,8),"0000-00-00"),TODAY(),"Y"))

4、性別

公式如下：

=IF([@身份證號(hào)碼]="","",TEXT(-1^MID([@身份證號(hào)碼],9,9),"女;男"))

5、生日

公式如下：

=IF([@身份證號(hào)碼]="","",TEXT(MID([@身份證號(hào)碼],11,4),"0月00日"))

6、屬相

公式如下：

=IF([@身份證號(hào)碼]="","",MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(MID([@身份證號(hào)碼],7,4)-4,12)+1,1))

7、星座

公式如下：

=IF([@身份證號(hào)碼]="","",LOOKUP(--TEXT([@出生日期],"mdd"),{101,"摩羯座";120,"水瓶座";219,"雙魚座";321,"白羊座";420,"金牛座";521,"雙子座";621,"巨蟹座";723,"獅子座";823,"處女座";923,"天秤座";1023,"天蝎座";1122,"射手座";1222,"摩羯座"}))

8、退休日期（按男60歲，女55歲計(jì)算）

如果需要按其他年齡計(jì)算，僅修改一下公式中的數(shù)字60或55即可。

公式如下：

=IF([@身份證號(hào)碼]="","",EDATE([@出生日期],IF([@性別]="男",60,55)*12))

9、身份證號(hào)碼真?zhèn)悟?yàn)證

公式如下：

=IF([@身份證號(hào)碼]="","",IF(RIGHT([@身份證號(hào)碼])=MID("10X98765432",MOD(SUM(MID([@身份證號(hào)碼],ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),"真","假"))

附：示例文檔下載地址（收費(fèi)）

https://mianbaoduo.com/o/chensir18/work