九色国产,午夜在线视频,新黄色网址,九九色综合,天天做夜夜做久久做狠狠,天天躁夜夜躁狠狠躁2021a,久久不卡一区二区三区

本文作者丨wayy - Excel 研究院

本文由「秋葉 Excel」原創(chuàng)發(fā)布

工作中，我們經(jīng)常需要收集員工的年齡、籍貫、出生日期等個人信息，便于公司進(jìn)行登記管理。

而當(dāng)我們在收集時，不僅需要員工仔細(xì)填寫每一項信息，收集的人也需要花時間跟每個人核對信息是否有誤。

其實有了身份證號碼，有些信息就不需要專門去收集了，而是可以直接從身份證號碼里面提取，比如性別、年齡、出生日期、籍貫（身份證所屬地區(qū)）等。

如果我們掌握了一些身份證常識，再加上一點小小的技巧，就既可以減少錄入的工作量，也保證了數(shù)據(jù)的準(zhǔn)確性。

下面小 E 就教教大家如何只靠身份證號碼就獲取到相關(guān)信息，以后再也不怕老板嫌棄我收集信息的效率低啦~

- 0 -

編碼規(guī)則

想要弄清楚如何取出這些信息，首先要了解一下身份證編碼的規(guī)則。第二代身份證號碼共 18 位，其編排規(guī)則如下——

? 前 1-6 位數(shù)字表示：所在省、市、區(qū)（縣、自治縣、縣級市）的代碼。

? 第 7-14 位數(shù)字表示：出生年、月、日。

? 第 15、16 位數(shù)字表示：所在地派出所的代碼。

? 第 17 位數(shù)字表示性別：奇數(shù)表示男性，偶數(shù)表示女性。

? 第 18 位數(shù)字是校檢碼。

了解完以上規(guī)則，接下來小 E 就用一張具體的身份證信息表格為大家演示怎樣從身份證號碼中取出這些信息~

- 1 -

所屬地區(qū)

思路：身份證號碼前 ６ 位代表的是地區(qū)代碼，要將身份證號碼對應(yīng)出所屬地區(qū)，首先要有一個全國各地區(qū)代碼的數(shù)據(jù)庫，然后使用 VLOOKUP 函數(shù)從中查詢出具體的地區(qū)名稱。

Step 1 小 E 已經(jīng)收集好了全國各地的地區(qū)代碼，存在了附件的「證件地區(qū)」工作表中，下面只是冰山一角，總共有 3218 個代碼。

Step 2 接下來需要用到 LEFT 函數(shù)取出身份證號碼的前 ６ 位。

Step 3 再然后通過 VLOOKUP 函數(shù)，在前面所提的「證件地區(qū)」工作表中查詢出地區(qū)名稱。

B2 單元格的具體的提取公式：

如下圖所示：

注：行政區(qū)劃代碼歷年來不斷有調(diào)整變化，身份證號碼中的行政區(qū)劃代碼查詢應(yīng)以當(dāng)時的區(qū)劃代碼為準(zhǔn)。

- 2 -

出生日期

身份證中第 7 到 14 位代表了出生年月日，直接提取出它們，很多小可愛都說這沒啥難度鴨~我也會！

然鵝作為日期，人家可是有規(guī)范的日期類型格式的，單純提出來的 8 位數(shù)字顯然是無法匹配人家要求的~

所以對于出生日期，不僅要提取出來，還要按規(guī)范的格式呈現(xiàn)出來。想實現(xiàn)這樣的效果，跟著下面的教程做，其實并不難~

思路：先取出身份證號碼的 7-14 位數(shù)值，然后再轉(zhuǎn)化成規(guī)范的日期類型數(shù)據(jù)。

Step 1 使用 MID 函數(shù)提取出生年月日 8 位數(shù)字：

Step 2 再使用 TEXT 函數(shù)格式化輸出日期格式：

Step 3 通過數(shù)學(xué)運算轉(zhuǎn)換成日期數(shù)據(jù)，在 C2 單元格輸入最終公式：

這里使用了兩個負(fù)號，表示減負(fù)運算，使日期文本轉(zhuǎn)換為數(shù)值，生成了規(guī)范的日期。

如下圖所示：

在第二步得到日期以后還要進(jìn)行第三步的處理，就是為了轉(zhuǎn)換成規(guī)范的日期格式。因為標(biāo)準(zhǔn)日期本質(zhì)上是數(shù)值，所以我們通常會通過數(shù)學(xué)運算（加、減、乘、除等均可以）進(jìn)行轉(zhuǎn)換，讓文本日期變成規(guī)范的日期數(shù)據(jù)。

- 3 -

年齡

思路：年齡可以使用當(dāng)前的日期與出生日期相減得到，本例中使用了 DATEDIF 函數(shù)來計算兩個日期之間的差額。

Step 1 取出生日期（前一部分已講解）公式為：

Step 2 當(dāng)前的日期可以使用 TODAY 函數(shù)獲得，公式為：

Step 3 有了起始的時間和結(jié)束的時間，使用 DATEDIF 函數(shù)中的第三個參數(shù)“Y”返回兩個日期之間的年份數(shù)，即為年齡。

D2 單元格的公式為：

如下圖所示：

- 4 -

性別

思路：性別可以從身份證號碼倒數(shù)第 2 位判斷，如果是奇數(shù)，則為男性；如果是偶數(shù)，則為女性。據(jù)此可以借用取余函數(shù) MOD 加 IF 函數(shù)來完成這個判斷。

Step 1 先用 MID 函數(shù)取身份證號碼倒數(shù)第 ２ 位。

Step 2 再借用 MOD 函數(shù)除以 ２，余數(shù)是 ０ 的即為偶數(shù)，余數(shù)是 １ 的即為奇數(shù)。

Step 3 再用 IF 函數(shù)進(jìn)行余數(shù)判斷，即可得到男或女的結(jié)果，單元格 E2 的公式為：

IF 函數(shù)的第一個參數(shù)對余數(shù)的奇偶性進(jìn)行判斷，當(dāng)余數(shù)為 1 時，奇數(shù)，即為男，否則為女。

如下圖所示：