身份证号码里，每一位都有明确的含义，本期将从身份证号码出发，给大家介绍其中可以玩转的常用函数。
首先，给大家列示一下身份证号码各数位的含义：
（1）前1、2位数字表示：所在省份的代码；
（2）第3、4位数字表示：所在城市的代码；
（3）第5、6位数字表示：所在区县的代码；
（4）第7~14位数字表示：出生年、月、日；
（5）第15、16位数字表示：户口所在地派出所的代表号码；
（6）第17位数字表示性别：奇数表示男性，偶数表示女性；
（7）第18位数字是校检码：根据前十七位数字计算所得。
接下来，我们把几个字段合并归类，从以下4个应用角度给大家介绍相关的函数：
01 提取出生地
我们把前6位统一放在一起，做为出生地信息。首先，我们学习如何从字符串的左边提取指定长度的子串。函数为：
LEFT(text, [num_chars])
text（必需）：包含要提取的字符的文本字符串。
num_chars（可选）：指定要由 LEFT 提取的字符的数量。num_chars 必须大于或等于零。如果 num_chars 大于文本长度，则 LEFT 返回全部文本。如果省略 num_chars，则假定其值为 1。
在下图中，C2单元格中输入的函数为 =LEFT(B2, 6)，表示从B2单元格中从左提取6位。然后双击填充柄向下填充：
假定我们手中还有一套这样的代码与地区的对应表，上述的信息还可以进一步利用：
我们将使用以下函数，从这个对应表中自动检索出相应的地区名称，函数如下：
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value（必需）：要查找的值。要查找的值必须位于 table-array 中指定的单元格区域的第一列中。
Table_array （必需）：VLOOKUP 在其中搜索 lookup_value 和返回值的单元格区域。
col_index_num （必需）：其中包含返回值的单元格的编号（table-array 最左侧单元格为 1 开始编号）。
range_lookup（可选）：TRUE 假定表中的第一列按数字或字母排序，然后搜索最接近的值。这是未指定值时的默认方法。
FALSE 在第一列中搜索精确值。
在下图中，D2单元格中输入的函数为 =VLOOKUP(C2,[地区代码库.xlsx]归属地!$B:$C,2,FALSE)，其中，要到地区代码库表中查找的字段是C2，被查找的区域是B:C，查到以后，返回的信息是这个区域的第2列，FALSE表示精确匹配查找。
通过LEFT和VLOOKUP，我们就将一个身份证号码的归属地显示了出来。
02 提取性别
第17位表示性别，我们先把这个位提取出来。函数为：
MID(text, start_num, num_chars)
text（必需）：包含要提取的字符的文本字符串。
start_num（必需）：文本中要提取的第一个字符的位置。 文本中第一个字符的 start_num 为 1，以此类推。
num_chars（必需）：指定希望 MID 从文本中返回字符的个数。
在下图中，E2单元格的函数为 =MID(B2, 17, 1)，表示从B2的第17位起，取1位：
取出这1位后，根据身份证的编码规则，我们要对它进行奇偶的判断。判断奇偶，从数学上可以用这个数字来除以2，余数为0即偶数，否则为奇数。对于如何求余数，函数为：
MOD(number, divisor)
Number（必需）：要计算余数的被除数。
Divisor（必需）：除数。
下图中，F2单元格中的函数为 =MOD(E2,2)，表示求E2除以2之后的余数：
奇偶的结果有了，根据编码规则，奇数为男性，偶数为女性，那么接下来就要做这个判断，并显示为相应的性别。用到的函数：
IF(logical_test, value_if_true, value_if_false)
logical_test（必需）：计算结果为 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位起的连续8位表示出生年月日，我们先把这个段提取出来，函数仍为MID。
但是仅仅这样提取，得到的只是个8位的字符串，不是一个真正的日期格式，需要进一步加工，所用函数：
TEXT(value, format_text)
value（必需）：要转换为文本的数值。
format_text（必需）：一个文本字符串，定义要应用于所提供值的格式。
如下图，H2单元格中的公式为 =--TEXT(MID(B2,7,8),'0-00-00')，表示把MID(B2,7,8)的结果，以'0-00-00'格式来显示。两个减号是把文本格式转为数字（日期的本质也是数字）：
这样得到的日期，就可以参与日期相关的运算了。比如我们想看看今年的生日那天是星期几。用到的函数有：
WEEKDAY(serial_number,[return_type])
Serial_number（必需）： 一个序列号，代表尝试查找的那一天的日期。
return_type（可选）：用于确定返回值类型的数字。1 或省略：数字 1（星期日）到 7（星期六）。2：数字 1（星期一）到 7（星期日）。
YEAR(serial_number) / MONTH(serial_number) / DAY(serial_number)
serial_number（必须）：日期值。
DATE(year, month, day)
year（必须）：可以包括一到四位数字。
month（必须）：一个正整数或负整数，表示一年中从 1 月至 12 月（一月到十二月）的各个月。
day（必须）：一个正整数或负整数，表示一月中从 1 日到 31 日的各天。
TODAY()
无参数。
下图中，J2单元格的公式为 =WEEKDAY(DATE(YEAR(TODAY()), MONTH(H2), DAY(H2)), 2)。公式有点长，不过很简单，我们从外往内一层层看。最外面是WEEKDAY，它有两个参数，第一个是今年的生日，第二个是2（从周一开始算一周的第一天）。那么接下来我们集中注意力在今年的生日上。今年的生日就是“今天TODAY()”的年、“出生日期H2”的月、“出生日期H2”的日，三个值拼在一起的，拼的函数是DATE。而取用年、月、日的函数分别是YEAR、MONTH、DAY。这样一分解，就明白了吧。
给大家留了一个思考题：出生年是否闰年？想想怎么做。
04 提取校验码
倒数第1位是身份证的校验码，是前面各数位一起通过一个算法计算出来的，算法如下（留给有兴趣的同学），和本文关系不大，可直接跳过。
第一步：将身份证号码的第1位数字与7相乘；将身份证号码的第2位数字与9相乘；将身份证号码的第3位数字与10相乘；将身份证号码的第4位数字与5相乘；将身份证号码的第5位数字与8相乘；将身份证号码的第6位数字与4相乘；将身份证号码的第7位数字与2相乘；将身份证号码的第8位数字与1相乘；将身份证号码的第9位数字与6相乘；将身份证号码的第10位数字与3相乘；将身份证号码的第11位数字与7相乘；将身份证号码的第12位数字与9相乘；将身份证号码的第13位数字与10相乘；将身份证号码的第14位数字与5相乘；将身份证号码的第15位数字与8相乘；将身份证号码的第16位数字与4相乘；将身份证号码的第17位数字与2相乘。
第二步：将第一步身份证号码1~17位相乘的结果求和，全部加起来。
第三步：用第二步计算出来的结果除以11，这样就会出现余数为0，余数为1，余数为2，余数为3，余数为4，余数为5，余数为6，余数为7，余数为8，余数为9，余数为10共11种可能性。
第四步：如果余数为0，那对应的最后一位身份证的号码为1；如果余数为1，那对应的最后一位身份证的号码为0；如果余数为2，那对应的最后一位身份证的号码为X；如果余数为3，那对应的最后一位身份证的号码为9；如果余数为4，那对应的最后一位身份证的号码为8；如果余数为5，那对应的最后一位身份证的号码为7；如果余数为6，那对应的最后一位身份证的号码为6；如果余数为7，那对应的最后一位身份证的号码为5；如果余数为8，那对应的最后一位身份证的号码为4；如果余数为9，那对应的最后一位身份证的号码为3；如果余数为10，那对应的最后一位身份证的号码为2。
通过这1位，可以对身份证号码进行验伪。首先需要提取出这1位。用到的函数：
RIGHT(text, [num_chars])
text（必需）：包含要提取的字符的文本字符串。
num_chars（可选）：指定要由 RIGHT 提取的字符的数量。num_chars 必须大于或等于零。如果 num_chars 大于文本长度，则 RIGHT 返回全部文本。如果省略 num_chars，则假定其值为 1。
下图中，K2单元格的函数为： =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,'合法','非法')，如下图：
上面我们从4个方面，给大家介绍到了文本函数、数学函数、日期函数、逻辑判断函数、查找函数等多个门类的常用函数，希望能给大家今后自学函数抛个砖。
（全文完）
联系客服
