EXCEL揭秘身份證號內含的秘密，實在是太全了

06-20

① 身份證號輸入 ②隱藏部分身份證號碼
③性別提取 ④出生年月提取
⑤年齡計算 ⑥周歲計算
⑦省市區縣地區判斷 ⑧星座和生肖提取

Excel

技巧-身份證號顯示不全怎麼辦

設置單元格格式中，點擊選擇「文本」

Excel

技巧-隱藏部分身份證號碼

我們有時為了演示函數的應用，需要用到大量的真實身份證號碼，當然，我們在網路上是需要保護隱私的，所以我們就將部分數字用「*」代替，下面，小編告訴你如何辦理。

下面，我們先建立一個身份證號碼的列，我們需要將出生年月有「****」代替。

在B1列輸入函數公式：=REPLACE(A1,7,4,"****")；這個函數的意思是：從A1單元格第7個數據進行替換，替換後面的四個數，替換成****；

Excel

技巧-

身份證號碼性別提取

身份證號碼隱藏著你的性別信息，如果我們建立的數據表有身份證號碼那就不必錄入性別了，直接利用公式就可以將性別輸入，這樣既方便了錄入，提高了工作的效率。還節省了數據的存儲空間。下面我們就來具體的做一下吧。

在身份證號碼中，如果你的號碼是15位，那麼你的身份證號碼的最後一位是奇數的話你就是男滴，相反就是女滴；如果你的號碼是18位，那麼第17位號碼透露你的性別信息。首先我們要將第15位號碼或者第17位號碼提取出來，先在數據表的後面建立一個新的列，列標題為【性別號碼】

在【性別號碼】下面的一個單元格中輸入公式 =IF(LEN(N3)=15,MID(N3,15,1),MID(N3,17,1)) ；公式的意思是，如果是十五位的身份證號碼，就提取第十五位數字；如果是18位的號碼就提取第十七位數字。

接著在p列輸入性別標題

在【性別】下面，輸入公式 =MOD(O3,2) ，此公式的意思是，判斷性別號碼是否能被2整除，如果能，就輸出0，如果不能就輸出1

向下拖動P3單元格的右下角，快速填充所有的行。這樣你就可以看到了，如果是奇數就輸出1，如果是偶數就輸出0。所以數據中，1就代表男，0就代表女

如果你覺得這樣看起來不方便，還可以將數字轉換成文字。Q2單元格輸入【性別】列標題，然後再Q3單元格輸入公式=IF(P3=1,"男","女") ，這個公式的意思是，如果p3單元格數字是1，就輸出男，如果是0就輸出女。

同樣的方法填充所有的行就得到的性別這個列

Excel

技巧-身份證出生年月提取

在出生年月處輸入：=text(mid(a1,7,8),"0000-00-00")

Excel

技巧-身份證年齡計算

年齡處輸入：=DATEDIF(TEXT(MID(A1,7,8),"#-00-00"),TODAY(),"Y")

然後打上「√」，就得出第一個身份證號的年齡；

Excel

技巧-身份證周歲計算

假設身份證號碼在A1中，則截止到「今天」的周歲年齡的公式為：

=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,(LEN(A1)=18)*2+6),"#-00-00"),TODAY(),"Y")

說明：上式對15或18位身份證號碼均適用。

如果全是18位身份證號，則公式可簡化為：

=DATEDIF(TEXT(MID(A1,7,8),"#-00-00"),TODAY(),"Y")

Excel

技巧-身份證判斷地區省市縣

Excel表格身份證號顯示不全怎麼辦18位身份證里前兩位是省份代碼

省是1、2位,=left(S2,2)

市是3、4位,=mid(S2,3,2)

縣是5、6位,=mid(S2,5,2)

第一步，先在Excel中，建立這樣的對應表：

全國縣及縣以上行政區劃代碼（2014年最新）

提示：可以到百度文庫中搜索，得到百度網友提供的數據。

第二步，再另一張Sheet中（例如Sheet2），輸入待查的身份證號碼。

第三步，在另一個單單元格中輸入公式

例如，在單元格B2輸入公式

=VLOOKUP(VALUE(LEFT(A2,2))*10000,Sheet1!A:D,2,FALSE)&VLOOKUP(VALUE(LEFT(A2,4))*100,Sheet1!A:D,3,FALSE)&VLOOKUP(VALUE(LEFT(A2,6)),Sheet1!A:D,4,FALSE)

即可一次性得到相應身份證號碼，所對應的省份、城市名、區名。

EXCEL

技巧-身份證提取星座和生肖

新建一張表格，在表格中輸入身份證號、出生年月、月日、生肖、星座，並輸入一些身份證號碼，我們將根據身份證號碼導出生肖和星座。

選擇B2單元格，在B2中輸入=DATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))，回車，就可以看到出生年月了，用填充手柄把剩餘的單元格都填充好。

選擇C2單元格，在C2中輸入=TEXT(B2,"M-D")，回車，就可以看到月日了，用填充手柄把剩餘的單元格都填充好。

選擇D2單元格，在D2中輸入=MID("鼠牛虎兔龍蛇馬羊猴雞狗豬",MOD(YEAR(TODAY())-DATEDIF(B2,TODAY(),"y")-4,12)+1,1)，回車，就可以看到生肖了，用填充手柄把剩餘的單元格都填充好。

選擇E2單元格，在E2中輸入

=IF(AND(C2>="3-21",C2<="4-19"),"白羊座",if(and(c2>="4-20",C2<="5-20"),"金牛座",if(and(c2>="5-21",C2<="6-21"),"雙子座",if(and(c2>="6-22",C2<="7-22"),"巨蟹座",if(and(c2>="7-23",C2<="8-22"),"獅子座",if(and(c2>="8-23",C2<="9-22"),"處女座",if(and(c2>="9-23",C2<="10-23"),"天秤座",if(and(c2>="10-24",C2<="11-21"),"天蠍座",if(and(c2>"11-22",C2<="12-21"),"射手座",if(and(c2>="12-22",C2<="1-19"),"摩羯座",if(and(c2>="1-20",C2<="2-18"),"水瓶座",if(and(c2>="2-19",C2<>

本文為頭條號作者發布，不代表今日頭條立場。