【excel怎么提取单数】用EXCEL提取身份证个人行政区域和基本信息

在日常工作和生活中，我们有很多适用身份证的场景。比如出差的时候乘坐公共交通的时候需要用身份证实名买票，住宿酒店需要用身份证实名登记，办理银行卡需要用身份证实名验证。单位求职时要用身份证进行入职登记和文件等。身份证的唯一性为我们的工作和生活提供了很多便利。

众所周知，现在都在使用第二代身份证，身份证号码由18位组成，下面以53262219831191034为例介绍各个位置的定义。

53:第1 ~第2位代表该州(直辖市、自治区)的代码。

26:第三至第四名代表所在地(自治州)的代码。

22: 5 ~ 6位代表该地区(县、自治县、县级市)的代码。

19831119:第7至第14位代表出生日期

10: 15 ~ 16位代表所在地派出所的代码。

3: 17位代表性别，单数代表男性，双数代表女性。

4: 18位代表随机生产的个人信息代码，主要用于验证身份证的准确性。校准代码可以是0-9的数字，也可以用x表示。

如果知道身份证号码各个位置的定义，就可以使用excel电子表格通过身份证号码提取个人基本信息，在拥有数百人甚至数千人的公司中，可以大量提取个人信息，从而大大提高工作效率。本文档使用多个EXCEL函数。我会详细说明每个位置的提取。

一、省市地区提取

提取省市信息，首先要知道身份证前六位代码对应的省。下图是本人在网上收集的全国行政区域身份证号码代码，区号对应行政区域、长途区号、邮政编码信息。

第一步：提取身份证号码前六名。

首先使用LEFT函数提取身份证号码的前6位。函数如下图所示。

LEFT函数：LEFT(需要提取字符串的单元格，提取几个)

LEFT函数有两个参数，如上图所示。第一个参数表示需要提取字符串的单元格是B3单元格。第二个参数6表示从指定单元格中从左到右提取6位数字。如果在键盘上按Enter键，将生成单元格B3的前6位110103。

步骤2:使用VLOOKUP和LEFT函数提取所需的管理区域

VLOOKUP函数：VLOOKUP(您会找到谁、在哪里找到、会找到第几列、会准确找到还是模糊不清)

VLOOKUP函数有四个参数，如上图所示。第一个参数“LEFT(B3，6)”表示提取的ID号的前6位。第二个参数“全国行政区域身份证代码表！“B:H”是指在同一份工作簿中从名为“全国行政区域居民身份证号码表”的表B到H列进行查找。第三个参数“5”表示从列B(区域1列)开始的下5列，即行政区所在的列，即列F。第四个参数0表示正确的查询，可以留空或写为0，写为1表示模糊的查询，搜索结果显示最接近实际值的值。

上述公式如下：

=VLOOKUP(LEFT(B3，6)，全国行政区域居民身份证号表！B:H、5，0)

回车后发现单元格值中显示#N/A的问题。此时，只需在LEFT(B3，6)前面添加两条水平线“-”。每条水平线代表一个减法运算，两次减法运算将文本数字或逻辑值转换为数字！

在《全国行政区域居民身份证号码表》中，我们通过居民身份证号码前六位提取了行政区域，要分别提取市、县、区号、邮政编码等，可以将VLOOKUP函数的第三个参数修改为要提取的内容的列数。

二、提取出生日期

正如刚才所说，身份证号码中第7-14位代表出生日期，要提取一系列字符中间的字符，必须使用MID函数。

MID函数：MID(需要提取字符串的单元格，从第几个提取，提取几个)

在储存格E3中，输入公式=MID(D3，7，8)。这意味着D3单元的身份证号码从第7位到第8位后8位，即完整出线。

生年月日，如下图所示：

我们发现这样提取的出生日期不是标准的日期格式，只需要用TEXT函数进行规范即可，函数如下：

=TEXT(MID(D3,7,8),"0000-00-00")

这时候日期将显示为：1983-11-19

三、提取性别

刚才我们说到，身份证号码里第17位代表性别，单数表示男性，双数表示女性，同样需要用MID函数把第17位数提取出来，然后用MOD余数函数判断该数是单数还是双数，用IF判断这个数字后显示性别。

MOD函数：MOD(需判断的数字,2)

MOD函数共有两个参数，第一个参数表示需要判断的数字，第二个参数“2”表示用需判断的数字除以2，整个函数表示除以2后剩余几，等于0说明被除尽，表示双数，等于1说明未被除尽，表示单数。

IF函数：IF(条件,满足条件时的值,不满足条件时的值)

IF函数是用来判断对错的，可以理解为：如果这样，那么这样，否则那样，共有三个参数，第一个参数表示我们要设置的条件，如：D3=1；第二参数表示满足条件时的值，如果D3刚好等于1，说明满足条件，那么我们的第二参数就可以写你要显示的值，如：“男”（可以写你想显示的任何内容）；第三个参数显示的是不符合指定条件的值，如“女”。

下面我们把IF、MOD、MID三个函数组合起来，就能得出我们想要的性别。

在C3单元格输入公式：=IF(MOD(MID(D3,17,1),2)=1,"男","女")，表示先用从MID(D3,17,1)把身份证号码第17位提取出来，再用MOD函数得出第17位数字是单数还是双数，最后嵌套IF函数判断经过处理的这个数字是否等于1，如果等于1，则显示“男”，如果不等于1，则显示“女”。如下图所示：

四、计算虚岁和周岁

刚才，我们利用函数轻松提取了出生日期，那计算年龄就容易多了，对于虚岁，我们直接用今年的年度数减去出生的年度即可，公式为：=2021-MID(D3,7,4)，这里的4表示只提取出生的年度，对于周岁，我们需要用到DATEDIF函数。

DATEDIF函数：DATEDIF(开始日期,终止日期,比较单位)

DATEDIF函数共有三个参数，第一个参数表示开始日期，我们需要提取身份证号码的出生年月日，第二个参数表示终止日期，也就是今天的日期，用TODAY()表示，第三参数为单位，这里我们计算的是年龄，所以用“Y”表示，EXCEL中的日期，Y表示年，M表示月，D表示日。

在G3单元格输入公式：

=DATEDIF(TEXT(MID(D3,7,8),"0000-00-00"),TODAY(),"Y")，用TEXT和MID函数提取并规范出生日期，作为第一参数，用TODAY()表示今天的日期，作为第二参数，第三参数用“Y”，表示年。如下图所示：

想不到一个身份证号码竟能延伸出这么多信息，对批量处理信息、提升工作效率起到了很大作用，再也不用面对上百人信息时用计算器一个一个计算年龄了，也不用再问籍贯是哪里，通过一个身份证号码，所有基本信息统统搞定。

提示：

一、文中所用到的“全国行政区域身份证代码表”为本人在网上收集并通过长时间校对、完善、修正所得，演示用的身份证号码为随意编写。

二、文中所有函数里的符号如()、""等均为半角符号。