大家好,我是雅客。 已经从事工作的朋友们,想必会经常和身份证号码打交道,身份证号码就是一个信息宝库,包含了很多私密信息,籍贯、出生日期、性别统统都在里面了! 今天我想和大家聊聊excel中身份证号码的那些事情,教大家如何利用身份证号码,自动提取各种各样的信息。 从身份证号码提取生日日期 身份证中第 7 到 14 位代表了出生年月日,我们要如何按照规范的格式将它提取出来呢?其实跟着下面的教程做,想实现这样的效果并不难~ 我们先取出身份证号码的 7-14 位数值,然后再转化成规范的日期类型数据。 Step 1 : 使用 MID 函数提取出生年月日 8 位数字: =MID(A2,7,8) Step 2:再使用 TEXT 函数格式化输出日期格式: =TEXT(MID(A2,7,8),'#00-00-00') 从身份证号码中提取年龄 大家一定很好奇如何才能提取身份证号码里的年龄吧!今天就教大家如何实现这个操作。其实我们只需要两个函数就能轻松解决。 在看如何提取身份证号码中的实际年龄前,我们先来了解一下excel中一个隐藏的函数——DATEDIF 函数,返回两个日期之间的年\月\日间隔数,常常用来计算两日期之差。 DATEDIF函数的基本语法是: (start_date,end_date,unit) Start_date 为一个日期,它代表时间段内的第一个日期或起始日期。 End_date 为一个日期,它代表时间段内的最后一个日期或结束日期。 Unit 为所需信息的返回类型。 Unit 返回 但需要注意的是,结束日期必须大于起始日期。 年龄可以使用当前的日期与出生日期相减得到,因此本例中使用了 DATEDIF 函数来计算两个日期之间的差额。 Step 1: 取出生日期(前一部分已讲解)公式为: =TEXT(MID(A2,7,8),'0000-00-00') Step 2 :当前的日期可以使用 TODAY 函数获得,公式为: =TODAY() Step 3: 有了起始的时间和结束的时间,使用 DATEDIF 函数中的第三个参数「 Y 」 返回两个日期之间的年份数,即为年龄。 从身份证号码中提取性别 身份证第17位数代表性别,奇数为男,偶数为女。所以我们需要先提取出来第17位数。 Step 1 :需要点公式 — 文本 — MID,依次输入A2、17、1(意思是引用A2单元格数据,从第17位开始引用,引用1位)。或者可以直接在B2单元格输入=MID(A2,17,1) Step 2:要处理B列表格中的奇偶数,这里用MOD函数来取余数。点公式 — 数学和三角函数 — MOD,依次输入B2、2。熟练的朋友直接输入=MOD(B2,2) Step 3:最后用IF函数,点公式 — 逻辑 — IF,依次输入C2=1、男、女(意思是识别C2表格中数据,是1显示男不是则显示女)。熟练的朋友依旧直接输=IF(C2=1,'男','女') 全部下拉之后就会显示出性别了,逐个公式解释完之后,现在就需要将所有公式加在一起了。 在单元格中直接输入=IF(MOD(MID(A2,17,1),2)=1,'男','女'),回车就完成了。 |
|