從身份證號碼里學常用函數(shù)

L羅樂 2018-02-08

展開全文

身份證號碼里，每一位都有明確的含義，本期將從身份證號碼出發(fā)，給大家介紹其中可以玩轉(zhuǎn)的常用函數(shù)。

首先，給大家列示一下身份證號碼各數(shù)位的含義：

（1）前1、2位數(shù)字表示：所在省份的代碼；

（2）第3、4位數(shù)字表示：所在城市的代碼；

（3）第5、6位數(shù)字表示：所在區(qū)縣的代碼；

（4）第7~14位數(shù)字表示：出生年、月、日；

（5）第15、16位數(shù)字表示：戶口所在地派出所的代表號碼；

（6）第17位數(shù)字表示性別：奇數(shù)表示男性，偶數(shù)表示女性；

（7）第18位數(shù)字是校檢碼：根據(jù)前十七位數(shù)字計算所得。

接下來，我們把幾個字段合并歸類，從以下4個應用角度給大家介紹相關的函數(shù)：

01 提取出生地

我們把前6位統(tǒng)一放在一起，做為出生地信息。首先，我們學習如何從字符串的左邊提取指定長度的子串。函數(shù)為：

LEFT(text, [num_chars])

text（必需）：包含要提取的字符的文本字符串。

num_chars（可選）：指定要由 LEFT 提取的字符的數(shù)量。num_chars 必須大于或等于零。如果 num_chars 大于文本長度，則 LEFT 返回全部文本。如果省略 num_chars，則假定其值為 1。

在下圖中，C2單元格中輸入的函數(shù)為 =LEFT(B2, 6)，表示從B2單元格中從左提取6位。然后雙擊填充柄向下填充：

假定我們手中還有一套這樣的代碼與地區(qū)的對應表，上述的信息還可以進一步利用：

我們將使用以下函數(shù)，從這個對應表中自動檢索出相應的地區(qū)名稱，函數(shù)如下：

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value（必需）：要查找的值。要查找的值必須位于 table-array 中指定的單元格區(qū)域的第一列中。

Table_array （必需）：VLOOKUP 在其中搜索 lookup_value 和返回值的單元格區(qū)域。

col_index_num （必需）：其中包含返回值的單元格的編號（table-array 最左側(cè)單元格為 1 開始編號）。

range_lookup（可選）：TRUE 假定表中的第一列按數(shù)字或字母排序，然后搜索最接近的值。這是未指定值時的默認方法。

FALSE 在第一列中搜索精確值。

在下圖中，D2單元格中輸入的函數(shù)為 =VLOOKUP(C2,[地區(qū)代碼庫.xlsx]歸屬地!$B:$C,2,FALSE)，其中，要到地區(qū)代碼庫表中查找的字段是C2，被查找的區(qū)域是B:C，查到以后，返回的信息是這個區(qū)域的第2列，F(xiàn)ALSE表示精確匹配查找。

通過LEFT和VLOOKUP，我們就將一個身份證號碼的歸屬地顯示了出來。

02 提取性別

第17位表示性別，我們先把這個位提取出來。函數(shù)為：

MID(text, start_num, num_chars)

text（必需）：包含要提取的字符的文本字符串。

start_num（必需）：文本中要提取的第一個字符的位置。文本中第一個字符的 start_num 為 1，以此類推。

num_chars（必需）：指定希望 MID 從文本中返回字符的個數(shù)。

在下圖中，E2單元格的函數(shù)為 =MID(B2, 17, 1)，表示從B2的第17位起，取1位：

取出這1位后，根據(jù)身份證的編碼規(guī)則，我們要對它進行奇偶的判斷。判斷奇偶，從數(shù)學上可以用這個數(shù)字來除以2，余數(shù)為0即偶數(shù)，否則為奇數(shù)。對于如何求余數(shù)，函數(shù)為：

MOD(number, divisor)

Number（必需）：要計算余數(shù)的被除數(shù)。

Divisor（必需）：除數(shù)。

下圖中，F(xiàn)2單元格中的函數(shù)為 =MOD(E2,2)，表示求E2除以2之后的余數(shù)：

奇偶的結(jié)果有了，根據(jù)編碼規(guī)則，奇數(shù)為男性，偶數(shù)為女性，那么接下來就要做這個判斷，并顯示為相應的性別。用到的函數(shù)：

IF(logical_test, value_if_true, value_if_false)

logical_test（必需）：計算結(jié)果為 TRUE 或 FALSE 的任意值或表達式。

value_if_true（必需）：ogical_test 為 TRUE 時返回的值。

value_if_false（必需）：logical_test 為 FALSE 時返回的值。

在下圖中，G2單元格中的公式為 =IF(F2=0, '女','男')：

上述用輔助列的方式適用于初學者，或者公式較復雜的時候。熟練后，可以合并為一個公式：=IF(MOD(MID(B2, 17, 1),2)=0, '女','男')

03 提取生日

第7位起的連續(xù)8位表示出生年月日，我們先把這個段提取出來，函數(shù)仍為MID。

但是僅僅這樣提取，得到的只是個8位的字符串，不是一個真正的日期格式，需要進一步加工，所用函數(shù)：

TEXT(value, format_text)

value（必需）：要轉(zhuǎn)換為文本的數(shù)值。

format_text（必需）：一個文本字符串，定義要應用于所提供值的格式。

如下圖，H2單元格中的公式為 =--TEXT(MID(B2,7,8),'0-00-00')，表示把MID(B2,7,8)的結(jié)果，以'0-00-00'格式來顯示。兩個減號是把文本格式轉(zhuǎn)為數(shù)字（日期的本質(zhì)也是數(shù)字）：

這樣得到的日期，就可以參與日期相關的運算了。比如我們想看看今年的生日那天是星期幾。用到的函數(shù)有：

WEEKDAY(serial_number,[return_type])

Serial_number（必需）：一個序列號，代表嘗試查找的那一天的日期。

return_type（可選）：用于確定返回值類型的數(shù)字。1 或省略：數(shù)字 1（星期日）到 7（星期六）。2：數(shù)字 1（星期一）到 7（星期日）。

YEAR(serial_number) / MONTH(serial_number) / DAY(serial_number)

serial_number（必須）：日期值。

DATE(year, month, day)

year（必須）：可以包括一到四位數(shù)字。

month（必須）：一個正整數(shù)或負整數(shù)，表示一年中從 1 月至 12 月（一月到十二月）的各個月。

day（必須）：一個正整數(shù)或負整數(shù)，表示一月中從 1 日到 31 日的各天。

TODAY()

無參數(shù)。

下圖中，J2單元格的公式為 =WEEKDAY(DATE(YEAR(TODAY()), MONTH(H2), DAY(H2)), 2)。公式有點長，不過很簡單，我們從外往內(nèi)一層層看。最外面是WEEKDAY，它有兩個參數(shù)，第一個是今年的生日，第二個是2（從周一開始算一周的第一天）。那么接下來我們集中注意力在今年的生日上。今年的生日就是“今天TODAY()”的年、“出生日期H2”的月、“出生日期H2”的日，三個值拼在一起的，拼的函數(shù)是DATE。而取用年、月、日的函數(shù)分別是YEAR、MONTH、DAY。這樣一分解，就明白了吧。

給大家留了一個思考題：出生年是否閏年？想想怎么做。

04 提取校驗碼

倒數(shù)第1位是身份證的校驗碼，是前面各數(shù)位一起通過一個算法計算出來的，算法如下（留給有興趣的同學），和本文關系不大，可直接跳過。

第一步：將身份證號碼的第1位數(shù)字與7相乘；將身份證號碼的第2位數(shù)字與9相乘；將身份證號碼的第3位數(shù)字與10相乘；將身份證號碼的第4位數(shù)字與5相乘；將身份證號碼的第5位數(shù)字與8相乘；將身份證號碼的第6位數(shù)字與4相乘；將身份證號碼的第7位數(shù)字與2相乘；將身份證號碼的第8位數(shù)字與1相乘；將身份證號碼的第9位數(shù)字與6相乘；將身份證號碼的第10位數(shù)字與3相乘；將身份證號碼的第11位數(shù)字與7相乘；將身份證號碼的第12位數(shù)字與9相乘；將身份證號碼的第13位數(shù)字與10相乘；將身份證號碼的第14位數(shù)字與5相乘；將身份證號碼的第15位數(shù)字與8相乘；將身份證號碼的第16位數(shù)字與4相乘；將身份證號碼的第17位數(shù)字與2相乘。

第二步：將第一步身份證號碼1~17位相乘的結(jié)果求和，全部加起來。

第三步：用第二步計算出來的結(jié)果除以11，這樣就會出現(xiàn)余數(shù)為0，余數(shù)為1，余數(shù)為2，余數(shù)為3，余數(shù)為4，余數(shù)為5，余數(shù)為6，余數(shù)為7，余數(shù)為8，余數(shù)為9，余數(shù)為10共11種可能性。

第四步：如果余數(shù)為0，那對應的最后一位身份證的號碼為1；如果余數(shù)為1，那對應的最后一位身份證的號碼為0；如果余數(shù)為2，那對應的最后一位身份證的號碼為X；如果余數(shù)為3，那對應的最后一位身份證的號碼為9；如果余數(shù)為4，那對應的最后一位身份證的號碼為8；如果余數(shù)為5，那對應的最后一位身份證的號碼為7；如果余數(shù)為6，那對應的最后一位身份證的號碼為6；如果余數(shù)為7，那對應的最后一位身份證的號碼為5；如果余數(shù)為8，那對應的最后一位身份證的號碼為4；如果余數(shù)為9，那對應的最后一位身份證的號碼為3；如果余數(shù)為10，那對應的最后一位身份證的號碼為2。

通過這1位，可以對身份證號碼進行驗偽。首先需要提取出這1位。用到的函數(shù)：

RIGHT(text, [num_chars])

text（必需）：包含要提取的字符的文本字符串。

num_chars（可選）：指定要由 RIGHT 提取的字符的數(shù)量。num_chars 必須大于或等于零。如果 num_chars 大于文本長度，則 RIGHT 返回全部文本。如果省略 num_chars，則假定其值為 1。

下圖中，K2單元格的函數(shù)為： =RIGHT(B2,1)，即從B2右取1位。

上述是從號碼中提取的，那么號碼的前17位按標準算法應該算出什么呢？這個公式比較復雜，就不展開講了，我們拿來直接使用（有興趣的同學可以研究，或單獨找我溝通），填寫在L2單元格中： =LOOKUP(MOD(SUM(MID(MID(B2,1,17),ROW(INDIRECT('1:17')),1)*MOD(2^(18-ROW(INDIRECT('1:17'))),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,'X',9,8,7,6,5,4,3,2})

在M2中編寫判斷，比較K2和L2是否相同，=IF(K2=L2,'合法','非法')，如下圖：