excel函数身份证号提取年龄和性别（新创Excel函数ShenFenZheng判断身份证号码为真后生成精确年龄等）

excel函数身份证号提取年龄和性别（新创Excel函数ShenFenZheng判断身份证号码为真后生成精确年龄等）四、图示说明各位粉友，如果你边看边打开Excel调试时，就会发现，ShenFenZheng虽然简单实用，可是在Excel里找不到这个函数啊！是的Excel找不到这个函数，因为他是自定义的函数，需要自己创建的函数。下面就讲讲如何创建ShenFenZheng自定义函数。在创建之前建议先阅读@Excel财务VBA之前发布的有关文章介绍，了解如何在个人宏工作簿中添加程序的方法等文章。创建方法就是打开个人宏工作簿，在Visual Basic编辑窗口中插入一个模块，然后考录ShenFenZheng函数代码到模块中，最后保存个人工作簿，创建就完成了（详见图5）。是不是简单到不想学呀？二、ShenFenZheng函数的含义及应用方法ShenFenZheng函数的含义是，返回指定位置的18位身份证号码所匹配的出生日期、性别、精确年龄及真伪。其语法为，ShenFenZheng(cell_value leix

导读：一、ShenFenZheng函数的应用需求，二、ShenFenZheng函数的含义及应用方法，三、ShenFenZheng函数的创建方法，四、图示说明，五、开源代码

图1 结果预览

一、ShenFenZheng函数的应用需求

首先是一个现实问题，如果让我们在Excel表格中只填写一个出生日期，我们可以在一秒钟就随意编一个，而且无法判定他的真伪，只能当真。如果让我们通过填写能通过校验为真的身份证号码来获取出生日期，要是不写自己真实的号码，恐怕10分钟甚至更长时间都无法写出来。这对真实填写出生日期起到了技术支持的作用，因此，我们在需要获取出生日期或年龄时，就必须通过填写身份证号码来获取，而不是直接填写。那么问题来了，怎么判断身份证号码的真伪？又怎么从中提取出生日期或年龄？根据目前Excel所提供函数，判断身份证号码真伪方法的函数公式为：=IF(MID("10X98765432" MOD(SUMPRODUCT(MID(A3 ROW($1:$17) 1)*

{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}) 11) 1 1)=RIGHT(A3) "真" "错号")；提取精确年龄的函数公式为：=DATEDIF(TEXT(MID(A3 7 8) "0000-00-00") TODAY() "Y")&"岁 "&DATEDIF(TEXT(MID(A3 7 8) "0000-00-00") TODAY() "YM")&"个月"&DATEDIF(TEXT(MID(A3 7 8) "0000-00-00") TODAY() "MD")&"天"；二合一先判断为真后计算精确年龄的函数公式为：=IF(MID("10X98765432" MOD(SUMPRODUCT(MID(A3 ROW($1:$17) 1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}) 11) 1 1)=RIGHT(A3) DATEDIF(TEXT(MID(A3 7 8) "0000-00-00") TODAY() "Y")&"岁 "&DATEDIF(TEXT(MID(A3 7 8) "0000-00-00") TODAY() "YM")&"个月"&DATEDIF(TEXT(MID(A3 7 8) "0000-00-00") TODAY() "MD")&"天" "错号")。看这长长的函数公式是不是有点晕？要是再新建需要显示出生日期或年龄的文档时，是不是还要翻遍旧文档复制公式呢？更有甚者计算结果是：24岁 1个月24天，而实际应是：24岁 1个月21天，二月份只有28天少3天，得到的是不准确的结果！如何解决以上问题呢？答案就是，创建ShenFenZheng函数，优化以上函数公式计算的不准确，固化函数公式运算过程方便重复调用，以达到方便、准确、真实获取出生日期或年龄的目的。这就是ShenFenZheng函数的需求所在。

二、ShenFenZheng函数的含义及应用方法

ShenFenZheng函数的含义是，返回指定位置的18位身份证号码所匹配的出生日期、性别、精确年龄及真伪。其语法为，ShenFenZheng(cell_value leixing_type)。变量cell_value指18位身份证号码或所存放的单元格地址名称；变量leixing_type指定返回值类型，当判断身份证校验码为真时，1返回出生日期，2返回性别，3返回精确年龄，4返回值"√"，其他数字返回"属性错误"，当判断身份证校验码错误时，1，2，3类型的返回值为"错号"，4类型的返回值为"×"。ShenFenZheng函数的应用方法：第一步，选择显示真伪返回值的单元格E3，接着在编辑栏输入"=S"，在下拉列表中双击ShenFenZheng条目，最后点击编辑栏上的fx图标（详见图2）。第二步，在弹出的函数参数输入框中，在cell项中输入身份证号码存放的单元格A3，在leixing项中输入类型代码4，然后点击确定（详见图3）。第三步，分别选择显示精确年龄返回值的单元格D3、性别C3、生日B3，重复第一步第二步，并在在leixing项中分别输入类型代码3、2、1（详见图4），完成ShenFenZheng函数的应用设置。是不是简单到没朋友呢？

三、ShenFenZheng函数的创建方法

各位粉友，如果你边看边打开Excel调试时，就会发现，ShenFenZheng虽然简单实用，可是在Excel里找不到这个函数啊！是的Excel找不到这个函数，因为他是自定义的函数，需要自己创建的函数。下面就讲讲如何创建ShenFenZheng自定义函数。在创建之前建议先阅读@Excel财务VBA之前发布的有关文章介绍，了解如何在个人宏工作簿中添加程序的方法等文章。创建方法就是打开个人宏工作簿，在Visual Basic编辑窗口中插入一个模块，然后考录ShenFenZheng函数代码到模块中，最后保存个人工作簿，创建就完成了（详见图5）。是不是简单到不想学呀？

谢谢您阅读完本文！我是#Excel财务VBA#，懂会计的程序员，Excel&VBA精通。了解更多Excel实战技巧分享，请记得点"关注"@Excel财务VBA ，已关注了烦请点赞、分享哦！也可私信

四、图示说明

图2 查找ShenFenZheng函数

图3 cell变量说明

图4 leixing变量说明

图5 代码图示

五、开源代码

Function ShenFenZheng(cell As String leixing As Long) As Variant '声明函数名称、变量名称及数据类型 Application.Volatile '该方法用于设置自定义函数为易失性函数，仅用于自定义函数中。 If Len(cell) <> 18 Then ShenFenZheng = "": Exit Function '身份证号码不等于18位，清除返回值并终止函数运行 Dim arr1() arr2() t As Long s As Long '声明变量，arr1()arr2()两数组名称，t=前17位各位数值，s=前17位乘对应系数的总和 arr1 = Array(7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2) '身份证前17位乘积系数数组 arr2 = Array("1" "0" "X" "9" "8" "7" "6" "5" "4" "3" "2") '前17位乘对应系数的总和除以11的余数对应的第18位数 For i = 1 To 17 '循环身份证前17位 t = VBA.Mid(cell i 1) '取出每位数值 s = s t * arr1(i - 1) '计算前17位乘对应系数的总和 Next i s = s Mod 11 '计算前17位乘对应系数的总和除以11的余数 If arr2(s) = VBA.Right(cell 1) Then '判断余数与身份证最后一位相等 Dim shengri As Date '声明日期变量 shengri = VBA.DateSerial(VBA.Mid(cell 7 4) VBA.Mid(cell 11 2) VBA.Mid(cell 13 2)) '提取换算出生日期数值 If leixing = 1 Then '函数属性值=1 ShenFenZheng = shengri '返回出生日期 ElseIf leixing = 2 Then '函数属性值=2 返回性别 If VBA.Mid(cell 17 1) Mod 2 = 0 Then '判断身份证第17位偶数 ShenFenZheng = "女" '返回性别=女 Else '判断身份证第17位奇数数 ShenFenZheng = "男" '返回性别=男 End If ElseIf leixing = 3 Then '函数属性值=3 返回年龄 If VBA.Month(VBA.Date) = Val(VBA.Mid(cell 11 2)) And VBA.Day(VBA.Date) >= Val(VBA.Mid(cell 13 2)) Then '当前月份等于出生月份，而且日期大于等于出生日期 If VBA.Day(VBA.Date) = Val(VBA.Mid(cell 13 2)) Then '当前月份等于出生月份，而且日期等于出生日期 ShenFenZheng = VBA.DateDiff("yyyy" shengri VBA.Date) & "岁整" '计算返回整岁年龄 Else '当前月份等于出生月份，而且日期大于出生日期 ShenFenZheng = VBA.DateDiff("yyyy" shengri VBA.Date) & "岁 " & VBA.Month(VBA.Date - shengri - VBA.Day(VBA.Date _ - shengri)) Mod 12 & "个月" & VBA.Day(VBA.Date - shengri) & "天" '计算返回非整岁详细年龄 End If Else '当前月份不等于出生月份 ShenFenZheng = (VBA.DateDiff("m" shengri VBA.Date) - VBA.Month(VBA.Date - shengri)) / 12 & "岁 " & _ VBA.Month(VBA.Date - shengri - VBA.Day(VBA.Date - shengri)) Mod 12 & "个月" & _ VBA.Day(VBA.Date - shengri) & "天" '计算返回非整岁详细年龄 End If ElseIf leixing = 4 Then '函数属性值=4 ShenFenZheng = "√" '返回真并√ Else '其他属性值 ShenFenZheng = "属性错误" '其他属性值返回属性错误 End If Else '判断余数与身份证最后一位不相等 If leixing = 4 Then '函数属性值=4 ShenFenZheng = "×" '返回值假并× Else '函数其他属性值 ShenFenZheng = "错号" '返回值错号 End If End If End Function