Excel身份证提取性别、籍贯、生日的方法

2025-07-05 06:54

Excel以其强大的数据分析、引用、计算、统计等功能在单位人事工资管理中得到了广泛应用，特别是在工作人员众多的企事业可以说是人事工资管理工作不二的选择。由于长期从事人事工资管理工作，在实践中边学习边应用，许多公式、函数用过后就很容易忘记了，为便于保存并有利于指导今后工作，现将一些基本的操作记录如下。如对同仁有所助益，也不妄此举！

一、不可不重视的身份证

身份证是一个人的唯一标识（不排除重复问题），在人事管理具有不可替代的作用，特别是在人员众多、姓名可能出现重复的情况下尤其重要（这是多年的经验），所以在建立人事工资管理基础表格的时候要作为重要信息反复核对清楚。同时，一个正确的身份证号在人事过程中可提取很多重要信息，避免手工输入引起的错误。

（一）身份证号提取性别信息

1.在18位身份证号码中，倒数第二位是性别代码，偶数为女性，奇数为男性，根据这个特性，我们可以在“性别”列设置如下函数：

=IF(MOD(MID(C2,17,1),2)=1,"男","女")

函数释义：如果（if）身份证号（C2）的第十七位是奇数（MOD），性别为“男”，否则为“女”。

如果你需要套用这个函数，只需要将A2更改为你数据中对应的身份证列与行即可。

图片1

2.如果在众多身份证号中有15位的，也有18位的则要考虑15位的身份证号是最后一位代表性别信息的，应输入以下函数进行判断：

=IF(LEN(C2)=15,IF(MOD(RIGHT(C2,1),2)=1,"男","女"),IF(LEN(C2)=18,IF(MOD(MID(C2,17,1),2)=1,"男","女"),"号码有误"))

函数释义：如果身份证为15位（LEN(C2)=15），则取身份证倒数第一位（RIGHT(C2,1)）并判断是否为奇数（MOD(RIGHT(C2,1),2)=1），结果为真，则输出结果为“男”，否则为“女”；如果身份证号为18位，解释同第1条。这里同时加了对身份证号位数是否正确的判断，如果身份证号既不为15，也不为18位，则认为“号码有误”。

图片2

（二）身份证号提取籍贯信息

如果你收藏了全国籍贯查询表，你还可以通过在“籍贯”列输入以下公式，实现个人籍贯信息的自动查找填充：

=VLOOKUP(LEFT(C2,6),全国籍贯查询表!A2:E3506,5,TRUE)

函数释义：提取身份证的前6位（left(C2,6)）,从“全国籍贯查询表”的A2列到E3506行这一数据域中查找（VLOOKUP）与身份证前6位相对应的第五列（5）籍贯信息。

注：1.查询数据域必须将查询关键词（如：身份证前6位）设为第一列，但不一定是查数据表的第一列（如在身份证对照信息在全国籍贯查询表的第B列，则选择数据域时就变成B2：E3506），而后才能确定查找引用的是第几列的数据（如：第5列，第几列只能从数据域的第1列起算）。

2.参数：“TRUE”是一个参数设置，分为大致匹配（FALSE）精确匹配（TRUE）,如果忽略不填则默认为精确匹配。

图片3

（三）身份证号提取出生日期

出生日期是人事管理中的重要信息也是身份证号内含的重要信息，这一信息从身份证号第7位起到第14位按照“YYYYMMDD”排列，这为我们提取出生日期提供了帮助，只要在出生日期中输入以下公式即可从身份证号中获得：

1.简单公式

=MID(C2,7,8)

函数释义：从身份证号（C2）的第7位起提取后8位数据，出现的数据就是“YYYYMMDD”的形式，且不能通过“数字”格式设置改变其显示结果。

图片4

2.显示“年”“月”“日”

=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))

函数释义：从身份证号（C2）第7位起取4位为出生年（MID(C2,7,4)），从第11位起取2位为出生月（MID(C2,11,2)），从第13位起取2位为出生日（MID(C2,13,2)），然后把这三组数字组成日期格式（DATE）。

注：信息数据提取后如果显示的不是你想要的数值，主要是出生日期的格式设置不正确，还需要打开出生日期“设置单元格格式”里的“数字”将其设为你想要的格式，一般有“YYYY年MM月DD日”“YYYY-MM-DD”或者只显示“年”和“月”等格式，如果你还不满意还可以能过“自定义”将其定义为你想要的格式。（如我们把这个显示格式选择为“YYYY-MM-DD”格式显示。）

图片5

3.考虑身份证号位数

如果在从多工作人员中有个别人员还是旧的15位编号的身份证则需要输入以下函数进行判断：

=IF(LEN(C2)=15,DATE(MID(C2,7,2),MID(C2,9,2),MID(C2,11,2)),IF(LEN(C2)=18,DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2)),"号码有错"))

函数释义：如果身份证号为15位（LEN(C2)=15），则从身份证号（C2）的第7位起取2位为出生年（MID(C2,7,2)），从第9位起取2位为出生月（MID(C2,9,2)），从第11位起取2位为出生日（MID(C2,11,2))，然后组合为出生日期；如果身份证号为18位，则从第7位起取4位为出生年，其余相同；如果不符合15位、18位的规定，则显示“号码有错”。（如我们把这一个出生日期组合方式自定义为“YYYY.MM”

图片6

在实践中如果想运用农历出生日期（生辰八字），还可以依仗EXCEL表格的万年历去查找，或者运用一定的公式、函数进行计算，但在人事工资管理中实际意义不大，在这里不再一一赘述。