Excel應(yīng)用

朱曉玲

摘要：身份證作為每個人獨(dú)一無二的公民身份的證明工具，身份證號碼中包含著一個人的性別、出生年月日、籍貫、年齡等基本信息，在日常辦公當(dāng)中，我們常常需要錄入這些基本信息，本文介紹了如何在Excel表格中快速提取這些信息的方法，避免了簡單枯燥的錄入工作，從而大大提高了工作效率。

關(guān)鍵詞：Excel 身份證號碼 基本信息

【中圖分類號】TP391.14

日常辦公當(dāng)中，我們常常需要在表格中輸入人員的姓名、性別、身份證號碼、出生年月日，籍貫等基本信息，如果涉及到人員數(shù)量比較多的時候，錄入的數(shù)據(jù)量大，逐項錄入非常麻煩。如果利用功能強(qiáng)大的Excel來完成這些信息的錄入，那么將會變得非常容易。首先我們來了解一下現(xiàn)在所使用的第二代身份證號碼的編碼規(guī)則，其由18位數(shù)字組成，第1到6位為歸屬地，第7到第10位為出生年份，第11到第12位為出生月份，第13到第14位為出生日期，第17位為性別（其中奇數(shù)為男，偶數(shù)為女），第18位為校驗碼。將下來，本文介紹如何使用Excel2010快速提取“表一”中的這些基本信息。

一、 出生日期的提取方法

1. 數(shù)據(jù)分列的使用

首先，選中“表一”中要分列的數(shù)據(jù)區(qū)域C2：C3，然后點(diǎn)擊“數(shù)據(jù)→數(shù)據(jù)工具→分列”，調(diào)出分列窗口，在“請選擇最合適的文件類型”處選擇根據(jù)“固定寬度”分列，在“數(shù)據(jù)預(yù)覽”區(qū)分別在身份證號碼的第6和第7位之間及第14和第15位之間點(diǎn)擊添加分列線將身份證號碼分成三個部分，如“圖1”所示；其次，在“數(shù)據(jù)預(yù)覽”區(qū)點(diǎn)擊身份證號碼的第一部分前6位， 選擇“列數(shù)據(jù)格式”區(qū)的“不導(dǎo)入此列（跳過）”，對身份證號碼的最后一部分最后4位應(yīng)用同樣的“列數(shù)據(jù)格式”；再次，在“數(shù)據(jù)預(yù)覽”區(qū)點(diǎn)擊身份證號碼中間部分第7到第14位，選擇“列數(shù)據(jù)格式”區(qū)的“日期（D）：YMD”，將“目標(biāo)區(qū)域”定位D2單元格，如“圖2”所示；最后，點(diǎn)擊“完成”即可，實(shí)現(xiàn)了從身份證號碼中提取出生日期。

2. 函數(shù)的使用（MID函數(shù)、DATE函數(shù)、TEXT函數(shù)）

根據(jù)第二代身份證號碼的編碼規(guī)則，我們只需要將第7到14位的年月日按照需要的格式提取出來即可，方法如下：

（1） MID函數(shù)和DATE函數(shù)的組合使用

首先設(shè)置D2單元格為任一種日期格式，然后在D2單元格中輸入公式為：

=DATE（MID（C2，7，4）， MID（C2，11，2）， MID（C2，13，2））

然后回車確認(rèn)，即可提取出該人員的出生日期，再拖動填充柄把公式向下復(fù)制，其余人員的出生日期也可提取出來。

對上式函數(shù)的解釋：①DATE函數(shù)返回年月日，語法結(jié)構(gòu)為：DATE（year，month，day）。②MID函數(shù)返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定，語法結(jié)構(gòu)為：MID（text， start_num， num_chars），MID（C2，7，4）表示提取C2中的年份即1995，MID（C2，11，2）表示提取C2中的月份02，MID（C2，13，2）表示提取C2中的日期05。

（2） MID函數(shù)和TEXT函數(shù)的組合使用

首先設(shè)置D2單元格為任一種日期格式，然后輸入公式為：

=--TEXT（MID（C2，7，8），”0-00-00”）

對上式函數(shù)的解釋：①M(fèi)ID（C2，7，8）表示提取C2單元第7位開始往后的8位數(shù)字19950205。②TEXT函數(shù)表示將數(shù)值轉(zhuǎn)化為自己想要的文本格式，TEXT函數(shù)前的兩個減號，負(fù)負(fù)為正的意思，被稱為減負(fù)運(yùn)算，然后用TEXT函數(shù)結(jié)合減負(fù)運(yùn)算把數(shù)據(jù)轉(zhuǎn)換成指定的日期格式。

二、 性別的判斷方法

根據(jù)第二代身份證號碼的編碼規(guī)則，我們只需要判斷它的第17位數(shù)字，奇數(shù)為男，偶數(shù)為女，方法如下：

在E2單元格中輸入公式為：=IF（MOD（MID（C2，17，1），2）=1，"男"，"女"）

然后回車確認(rèn)，即可判斷出該人員的性別，再拖動填充柄把公式向下復(fù)制，其余人員的性別也可判斷出來。

對上式函數(shù)的解釋：①M(fèi)ID（C2，17，1）表示提取C2的第17位數(shù)字2。②MOD函數(shù)返回兩數(shù)相除后的余數(shù)，MOD（MID（C2，17，1），2）表示C2的第17位數(shù)字和2相除后的余數(shù)0。③IF函數(shù)根據(jù)指定的條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容，上述公式表示如果第17位數(shù)字是奇數(shù)，就顯示“男”，否則就顯示“女”。

三、 年齡的計算方法

根據(jù)前面提取出來的出生日期計算年齡，方法如下：

（1） 周歲計算

首先設(shè)置F2單元格為“常規(guī)”，然后在F2單元格中輸入公式為：=INT（TODAY（）-D2）/365）

最后回車確認(rèn)，即可完成計算出該人員的周歲年齡，再拖動填充柄把公式向下復(fù)制，其余人員的周歲年齡也可計算出來。

對上式函數(shù)的解釋：①TODAY函數(shù)返回當(dāng)前系統(tǒng)的日期。②INT函數(shù)是取整函數(shù)，上述公式表示將今天的日期與出生日期相減，所得數(shù)除以365化為年，再取整數(shù)，計算出周歲年齡。

（2） 虛歲計算

周歲公式基礎(chǔ)上加1即可，方法如下：

在F2單元格中輸入公式為：=YEAR（TODAY（））-YEAR（D2）+1，然后回車確認(rèn)。

參考文獻(xiàn)：

[1] 梁廣洪.巧用Excel函數(shù)提取身份證中信息.銅仁職業(yè)技術(shù)學(xué)院學(xué)報，2010年02期

[2] 周長春.Excel函數(shù)的新應(yīng)用—從身份證號碼中自動生成性別與出生日期.才智，2011年26期endprint