电⼦表格中关于⾝份证号的函数
中国居民⾝份证号码是⼀组特征组合码，原为１５位，现升级为１８位，其编码规则为：
１５位：６位数字常住户⼝所在县市的⾏政区划代码，６位数字出⽣⽇期代码，３位数字顺序码。
１８位：６位数字常住户⼝所在县市的⾏政区划代码，８位数字出⽣⽇期代码，３位数字顺序码和１位检验码。
其中３位数字顺序码，是为同⼀地址码的同年同⽉同⽇出⽣⼈员编制的顺序号，偶数的为⼥性，奇数的为男性。
１、提取籍贯地区的⾏政区划代码（A2为⾝份证号，下同）
１５与１８位通⽤：=LEFT(A2,6)
如果有⼀个编码和省份地区的对照表，可以⽤VLOOKUP函数来提取地区信息。
２、提取出⽣⽇期信息
１５位：=TEXT(19&MID(A2,7,6),"#-00-00")
１８位：=TEXT(MID(A2,7,8),"#-00-00")
１５与１８位通⽤：
=TEXT(IF(LEN(A2)=15,19,"")&MID(A2,7,6+IF(LEN(A2)=18,2,0))," #-00-00")
简化公式：
=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00") (请将输⼊公式的单元格格式设置为⽇期格式）
３、提取性别信息
１５位：=IF(MOD(RIGHT(A2),2)=1,"男","⼥")
１８位：=IF(MOD(MID(A2),17,1)=1,"男","⼥")
１５与１８位通⽤：
=IF(MOD(MID(A2,IF(LEN(A2)=15,15,17),1),2)=1,"男","⼥") 简化公式：=IF(MOD(RIGHT(LEFT(A1,17)),2),"男","⼥")
mid函数提取年月日４、检验⾝份证号码的正确性
１８位⾝份证号码的最后⼀位是检验码，它是根据⾝份证前17位数字依照规则计算出来的，其值０～９或X。⼀般情况只要有⼀位数字输⼊错误，依照规则计算后就会与第18位数不符。当然不排除按错误号码计算后恰好与检验码相符的情况，但这种情况出现的可能性较低。因此，对１８位号码的验证采⽤如下公式：
=MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT("1:17 ")),1)*2^(18-
ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2,1)
对于１５位⾝份证，由于没有检验码，我们只能简单地去判断出⽣⽇期代码是否是⼀个有效的⽇期，避免输⼊⼀些
像“731302”或“980230“等这样不存在的⽇期。
=ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))
综合１５位和１８位后的通⽤公式为：
=IF(LEN(A1)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A1,ROW( INDIRECT("1:17")),1)*2^(18-
ROW(INDIRECT("1:17")))),11)+1,1) =RIGHT(A1),IF(LEN(A1)=15,ISNUMBER(--TEXT(19&MID(A1,7,6),"#-00-00"))))
由于⽬前１５位⾝份证号码已经很少了，如果对１５位的号码不需要作进⼀步的判断，则公式可以简化成：
=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW( INDIRECT("1:17")),1)*2^(18-
ROW(INDIRECT("1:17")))),11)+1,1) =RIGHT(A2),LEN(A2)=15)
将上⾯的公式放到B2单元格，如果结果为TRUE，则⾝份证号是正确的，结果为FALSE则是错误的。
你也可以将上述公式放在数据有效性中，防⽌录⼊错误的⾝份证号。操作⽅法：选择需要输⼊⾝份证的全部单元格区域,⽐如
A2:A10，点菜单"数据"-"有效性"，在"允许"的下拉框中选择"⾃定义"，在"公式"输⼊上⾯的15位和18位通⽤公式，确定以后即可。注
意：公式⾥的"A2"是你刚才选定要输⼊⾝份证的单元格区域的第⼀个单元格，如果你是要在C3:C20输⼊⾝份证号，则将公式⾥的"A2"改为"C3"。另外，你也可以先设置好某单个单元格的数据有效性(这时公式的A2改为
选定的单元格)，再⽤格式刷将其格式刷到其他需要相同设置的单元格。
５、１５位升为１８位
=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMP
RODUCT(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18 -ROW(INDIRECT("1:17")))),11)+1,1),A2)
６、１８位转换为１５位
=IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)
7、⽰例
表中公式:
B2
=IF(LEN(A2)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(A2,ROW( INDIRECT("1:17")),1)*2^(18-
ROW(INDIRECT("1:17")))),11)+1,1)
=RIGHT(A2),IF(LEN(A2)=15,ISNUMBER(--TEXT(19&MID(A2,7,6),"#-00-00"))))
C2
=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2), "#-00-00")+0,)
D2 =IF(A2<>"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","⼥"),) E2
=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)
=18)*2),"#-00-00"),TODAY(),"y"),)
F2 =IF(A2<>"",VLOOKUP(LEFT(A2,2)，地区表!A:D,2,),)
H2
=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUMP
RODUCT(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18
-ROW(INDIRECT("1:17")))),11)+1,1),A2)
I2 =IF(LEN(A2)=18,LEFT(REPLACE(A2,7,2,),15),A2)
提取年龄公式：
=DATEDIF(TEXT(MID(B1,7,8),"#-00-00"),TODAY(),"Y")
从⾝份证号码中提取出性别
=IF(MOD(MID(A1,15,3),2),"男","⼥") （最简单公式）
=IF(MOD(RIGHT(LEFT(A1,17)),2),"男","⼥")
=IF(A2<>” ”,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”⼥”),)
=IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","⼥")
从⾝份证号码中进⾏年龄判断
=IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),
TODAY(),”Y”),)
=DATEDIF（A1，TODAY（），“Y”）
（以上公式会判断是否已过⽣⽇⽽⾃动增减⼀岁）
=YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900
=YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4))
=YEAR(TODAY())-VALUE(MID(B1,7,4))&"岁"
=YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4))
按⾝份证号号码计算⾄今天年龄
=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y")
以2006年10⽉31⽇为基准⽇，按⾝份证计算年龄(周岁)的公式
=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")
按⾝份证号分男⼥年龄段
按⾝份证号分男⼥年龄段，⾝份证号在K列，年龄段在J列（⾝份证号为18位）
男性16周岁以下为1
男性16周岁（含16周岁）以上⾄50周岁为 2
男性50周岁（含50周岁）以上⾄60周岁为 3
男性60周岁（含60周岁）以上为 4
⼥性16周岁以下为1
⼥性16周岁（含16周岁）以上⾄45周岁为 2
⼥性45周岁（含45周岁）以上⾄55周岁为 3
⼥性55周岁（含55周岁）以上为 4
=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),"y"),{0,16,50,60}-{0,0,5 ,5}*ISEVEN(MID(K1,17,1)))
=SUM(--(DATEDIF(MID(K1,7,4)&"/"&MID(K1,11,2)&"/"&MID(K1,13,2),TODAY(),"y")>={0,16,45,55}+{0 ,0,5,5}*MOD(MID(K1,17,1),2)))
根据出⽣年⽉计算年龄
＝DATEDIF(A1,TODAY(),"y")
＝DATEDIF(A1,TODAY(),"y")&"周岁"＝DATEDIF(A1,NOW(),"y")

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。