什么值得买App下载 App ›
连续签到专享现金活动

Excel如何根据身份证号一键提取出生年月、年龄、性别、籍贯

在企业人力资源管理中,员工档案信息的数字化整理是一项基础性工作。面对大量员工身份证信息,手动录入出生年月、年龄、性别及籍贯不仅效率低下,且容易出现人为误差。基于国内通用的18位二代身份证号码编码规则,利用Excel或WPS表格的内置函数,可实现上述关键信息的自动化提取与计算。掌握这一方法能够显著提升档案整理的准确性与工作效率。
01提取出生年月
身份证号码的第7位至第14位代表了持证人的出生年月日(YYYYMMDD格式)。通过Excel的文本截取与格式转换功能,可将这串数字转化为标准的日期格式。
功能目标核心公式函数原理解析
提取出生日期=TEXT(MID(A2,7,8),"0-00-00")MID:从第7位开始截取8个字符;TEXT:将数字串转换为年-月-日标准格式

注:假设A2单元格为身份证号码,输入公式后下拉即可批量生成。

02计算实际周岁年龄
档案管理中通常要求记录员工的周岁年龄,而非虚岁。利用日期计算函数,结合系统当前日期,可实现对员工年龄的精准动态计算,无需每年手动更新。
功能目标核心公式函数原理解析
计算周岁年龄=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"Y")TODAY:获取当前系统日期;DATEDIF:计算两个日期之间的整年数差值

优势:系统日期更新后,年龄数据会自动同步变化。

03判定性别信息
身份证号码的第17位用于标识性别,奇数代表男性,偶数代表女性。通过逻辑判断函数提取该位数字并进行奇偶校验,即可自动输出性别结果。
功能目标核心公式函数原理解析
判定性别=IF(MOD(MID(A2,17,1),2)=0,"女","男")MID:截取第17位数字;MOD:计算除以2的余数;IF:余数为0则显示女,否则显示男

该公式逻辑严密,能准确对应身份证编码规则。

04解析籍贯信息
身份证号码的前6位代表户籍所在地的行政区划代码。虽然Excel内置公式无法直接将代码翻译为文字,但可以通过截取代码或引用外部数据表的方式实现信息的关联。
  1. 提取行政区划代码
    公式:=MID(A2,1,6)
    说明:直接截取前6位数字,用户需自行对照《行政区划代码表》查找对应地区。
  2. 匹配文字籍贯(推荐)
    公式:=VLOOKUP(MID(A2,1,6),代码对照表区域,2,0)
    说明:需预先准备一份“代码-地区”对照表,公式将自动提取前6位代码并在对照表中检索对应的省市区文字。
05操作注意事项
  • 身份证位数限制
    上述公式仅适用于18位二代身份证号。若档案中存在15位老身份证号,需先将其升级转换为18位后方可使用,否则会导致提取位置错位。
  • 数据清洗要求
    输入身份证号码时,必须确保单元格内无多余空格。建议在录入前使用分列功能或查找替换功能清洗数据,避免因空格导致字符截取错误。
  • 软件兼容性
    所有列举的公式在WPS表格中均可直接通用,逻辑完全一致,无需进行任何语法修改。

评论

评论区空态插画

当前文章无评论,是时候发表评论了