身份证号码函数

合集下载

身份证号验证函数

WHEN 'A' THEN 10 ELSE SUBSTRING(@validFactors,@i,1) END)
,@i=@i+1
END
IF SUBSTRING(@validCodes,@iTemp%11+1,1)<>RIGHT(@idcard,1)
set @a=1
else
set @a=0
return(@a)
END
raiserror(@SaveSQL,16,1)
*/
USE [ksoa_pos]
GO
/****** Object: UserDefinedFunction [dbo].[Idcardcheck] Script Date: 04/15/2014 19:51:01 ******/
set @a=0
if len(@idcard)<>18 or left(@idcard,9)=right(@idcard,9)
begin
set @a=1
end
else if isnumeric(left(@idcard, 17))=0
begin
set @a=1
end
SELECT @validFactors='79A584216379A5842',@validCodes='10X98765432',@i=1,@iTemp=0
WHILE @i<18
BEGIN
SELECT @iTemp=@iTemp+CAST(SUBSTRING(@idcard,@i,1) AS INT)*(CASE SUBSTRING(@validFactors,@i,1)

从身份证号码里学常用函数

从身份证号码里学常用函数身份证号码里，每一位都有明确的含义，本期将从身份证号码出发，给大家介绍其中可以玩转的常用函数。

首先，给大家列示一下身份证号码各数位的含义：（1）前1、2位数字表示：所在省份的代码；（2）第3、4位数字表示：所在城市的代码；（3）第5、6位数字表示：所在区县的代码；（4）第7~14位数字表示：出生年、月、日；（5）第15、16位数字表示：户口所在地派出所的代表号码；（6）第17位数字表示性别：奇数表示男性，偶数表示女性；（7）第18位数字是校检码：根据前十七位数字计算所得。

接下来，我们把几个字段合并归类，从以下4个应用角度给大家介绍相关的函数：01 提取出生地我们把前6位统一放在一起，做为出生地信息。

首先，我们学习如何从字符串的左边提取指定长度的子串。

函数为：LEFT(text, [num_chars])text（必需）：包含要提取的字符的文本字符串。

num_chars（可选）：指定要由LEFT 提取的字符的数量。

num_chars 必须大于或等于零。

如果num_chars 大于文本长度，则LEFT 返回全部文本。

如果省略 num_chars，则假定其值为 1。

在下图中，C2单元格中输入的函数为 =LEFT(B2, 6)，表示从B2单元格中从左提取6位。

然后双击填充柄向下填充：假定我们手中还有一套这样的代码与地区的对应表，上述的信息还可以进一步利用：我们将使用以下函数，从这个对应表中自动检索出相应的地区名称，函数如下：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])lookup_value（必需）：要查找的值。

要查找的值必须位于table-array 中指定的单元格区域的第一列中。

Table_array （必需）：VLOOKUP 在其中搜索 lookup_value 和返回值的单元格区域。

col_index_num （必需）：其中包含返回值的单元格的编号（table-array 最左侧单元格为 1 开始编号）。

EXCEL表格中根据身份证号码自动生成出生日期、年龄和性别的函数

EXCEL表格中根据⾝份证号码⾃动⽣成出⽣⽇期、年龄和
性别的函数
1、正确录⼊⾝份证号
⾝份证号18位，设置成⽂本格式。

2、⾃动⽣成出⽣年⽉⽇
如F列为⾝份证号，B列为出⽣年⽉⽇，则在B2单元格中输⼊公式：
“=DATE(MID(F2,7,4),MID(F2,11,2),MID(F2,13,2))”
3、⾃动⽣成当前年龄
如B列为出⽣⽇期，E列为年龄，在E2单元格中输⼊公式：
“=DATEDIF(B2,TODAY(),"Y")”，
4、提取性别信息（F2为⾝份证号码单元格）
如性别信息在B列，对同时有15位和18位⾝份证号码的情况下，在B2单元格中输⼊公式：
“=IF(MOD(IF(LEN(F2)=15,MID(F2,15,1),MID(F2,17,1)),2)=1,"男","⼥")”
对只是18位⾝份证号码的，只要输⼊下⾯的公式就可以了：
“=IF(MOD(MID(F2,17,1),2)=1,"男","⼥")”
注意：上述公式的最外⾯引号不要输⼊。

电子表格中关于身份证号的函数

中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。

１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。

其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。

１、提取籍贯地区的行政区划代码（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),"男","女")４、检验身份证号码的正确性１８位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０～９或X。

Excel表格中根据身份证号码自动填出生日期、性别、年龄的函数公式

Excel表格中根据身份证号码自动填出生日期、性别、年龄的函数公式18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))),2)=0,"女","男")15/18位都可以的公式：转换出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TEXT(MID(e2,7,6),"#-0 0-00"))如果要想把“-”换成“年月日”，则公式可改为=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#年00月00日"),"19"&TEXT(MID(e2,7,6),"#年00月00日"))15/18位自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))计算年龄（新旧身份证号都可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",109-MIDB(E2,9,2),109-MIDB(E2,7 ,2)))说明：109表示当前日期是2009年，如果是2000年后出生的，要从身份证号码中计算年龄，则公式可改为=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",109-MIDB(E2,9,2),09-MIDB(E2,9, 2))) (此公式只适合18位新身份证)。

EXCEL表中身份证号码年月日提取公式

编辑日期：2009年7月28日EXCEL表中身份证号码信息提取公式1、从身份证号码提取“年”份如：342622************提取1994，输入公式：=IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4))，回车确定。

2、从身份证号码提取“月”份如：342622************提取11，输入公式：=IF(LEN(H2)=18,IF(MID(H2,11,1)="0",MID(H2,12,1),MID(H2,11,2)),IF(MID( H2,9,1)="0",MID(H2,10,1),MID(H2,9,2)))，回车确定。

3、从身份证号码提取“日”如：342622************提取8（不是08形式），输入公式：=IF(LEN(H2)=18,IF(MID(H2,13,1)="0",MID(H2,14,1),MID(H2,13,2)),IF(MID( H2,11,1)="0",MID(H2,12,1),MID(H2,11,2)))，回车确定。

4、从身份证号码提取“年月日”如：342622************提取形如1994-11-08，输入公式：=IF(LEN(H2)=15,1900+MID(H2,7,2)&"-"&MID(H2,9,2)&"-"&MID(H2,11,2),MID( H2,7,4)&"-"&MID(H2,11,2)&"-"&MID(H2,13,2))，回车确定。

5、从身份证号码提取“年龄”如：342622************提取年龄为15岁（针对2009年），输入公式：=YEAR(NOW())-IF(LEN(H2)=15,1900+MID(H2,7,2),MID(H2,7,4))，回车确定。

根据身份证号码(15位和18位通用)

=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))
MOD的用法：
=MOD(3, 2) 3/2 的余数 (1)
=MOD(-3, 2) -3/2 的余数。符号与除数相同 (1)
根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：
说明：公式中的B2是身份证IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错"))
VALUE：将代表数字的文字串转换成数字,这样你就可以进行数学运算了。
语法形式为：VALUE(text)
Text为带引号的文本，或对需要进行文本转换的单元格的引用。Text 可以是 Microsoft Excel 中可识别的任意常数、日期或时间格式。如果 Text 不为这些格式，则函数 VALUE 返回错误值 #VALUE!。
2、根据身份证号码求出生年月：
=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2)),"身份证错"))
3、根据身份证号码求年龄：
=MOD(3, -2) 3/-2 的余数。符号与除数相同 (-1)

最简单实用的身份证号码提取出生年月男女公式

最简单实用的身份证号码提取出生年月男女公式
18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2中求出出生日期，
在F2格中输入公式：
=IF(LEN(E2)=18, "&MID(E2,7,4)"." &MID(E2,11,2) "." &MID(E2,13,2))
回车后显示：2007.01.12
如果需要2007-01-12日则需要把"."换成短横"-"。

18位身份证号码转换成男女的函数公式：如果E2中是身份证,在F2自动录入男女：
在F2格中输入公式：
=IF(MOD(MID(E2，17，1),2=1，"1"，"2"),回车后显示男是1、女是2。

如果需要男、女汉字把"1"换成"男"，"2"换成"女"回车，一切OK。

赵官镇中心心小学
2013年6月7日。

excel身份证验证公式

excel身份证验证公式
Excel身份证验证公式
1.验证格式：数据校验公式可以验证身份证号码是否正确和合法。

Excel中可以使用“LEN()”函数来确定身份证号码字符串的长度，再与18位数对比来明确证书是否存在。

2.验证位数：只有18位数身份证号码才能使用来进行校验，15位号码已不再使用，可以通过旧省份前两位是否是“35”、“36”或“37”来判断。

3.验证省级代码：通过省份前两位码来验证省级代码，与身份证号码前两位相对应。

4.验证出生日期：可以通过对身份证号码出生日期的部分（第7到14位）与Excel 中“LEFT()”“MID()”等函数结合，来验证身份证号码的出生日期，验证准确。

5.验证顺序号：通过身份证号码的第16位和第17位数字代表性别，第15位和第18位数字代表性别，第15位数字用于最后一位校验码的算法，如果正确，身份证号码可以正确匹配！
6.验证位：计算机通过一组算法，通过身份证号码前17位数字，计算
出一个0-10之间的数字，如果运算结果与最后一位数字相等则表示验证通过！
此外，Excel中对于身份证号码的验证，还可以利用VBA编程语言进行。

其中可以使用“If（）Then（）Else（）”结构计算出一组结果，以决定身份证号码是否要验证成功。

18位身份证号码转换成出生日期的函数公式

18位身份证号码转换成出生日期的函数公式：如果E2中是身份证,在F2中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2)) 自动录入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))) ,2)= 0,"女","男") 根据身份证号快速录入男女性别“记住”15/18位都可以的公式：转换出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TE XT(MID(e2,7,6),"#-00-00"))自动录入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男"))计算年龄（新旧身份证号都可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2 ,9,2 ),107-MIDB(E2,7,2)))WPS表格提取身份证详细信息前些天领导要求统计所有员工的性别、出生日期、年龄等信息，并且要得很急。

而我们单位员工人数众多。

短时间内统计相关信息并且输入计算机几乎是不太可能的。

幸好在以前的一份金山表格中我们曾经统计有所有员工的身份证号码，而身份证中正有我们所需要的性别、出生日期、年龄等信息的。

所以。

干脆，还是直接在金山表格中从身份证号码提取相关的信息吧。

身份证号放在A2单元格以下的青岛DNA检测单位区域。

我们需要从身份证号码中提取性别、出生日期、年龄等相关信息。

由于现在使用的身份证有15位和18位两种。

Excel校验身份证号码的自定义函数

校验居民身份证号码函数在日常工作中，我们经常用电子表格输入身份证号码，往往出现输入错误，校对麻烦。

由于身份证中第十八位数字的计算方法较为繁琐，下面提供了一个自定义函数，很方便。

=jysfz(身份证号码字符型)示例：340524************←号码#NAME?=jysfz(A7)#NAME?=jysfz("340524************") 340524************#NAME? 340524************#NAME?340524************#NAME?340524************#NAME? 340524************#NAME? 340524************#NAME? 3405241952060122188#NAME?34052419710702233#NAME? 3405241964100622#NAME? 340524************#NAME? 34052419641006225想#NAME?#NAME?这样也有错！#NAME?　身份证的号码是按照国家的标准编制的，由18位组成：前六位为行政区划代码，第七至第十四位为出生日期码，第15至17位为顺序码，第15、16位为乡镇代码（一个乡镇可能有多个代码），第17位代表性别（奇数为男，偶数为女），第18位为校验码。

作为尾号的校验码，是由号码编制单位按统一的公式计算出来的，如果某人的尾号是0-9，都不会出现X，但如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且我国的计算机应用系统也不承认19位的身份证号码。

Ⅹ是罗马数字的10，用Ⅹ来代替10，可以保证公民的身份证符合国家标准。

但是我国的居民身份证在升位后，一些人的尾号变成了X，这部分人在工作生活中，例如去银行存取钱、去汽车公司租赁汽车或者报名参加考试等等过程中，往往不被检验者理解，认为是假身份证，这样的误会给很多人的生活带来不便。

电子表格中关于身份证号的函数

电⼦表格中关于⾝份证号的函数中国居民⾝份证号码是⼀组特征组合码，原为１５位，现升级为１８位，其编码规则为：１５位：６位数字常住户⼝所在县市的⾏政区划代码，６位数字出⽣⽇期代码，３位数字顺序码。

１８位：６位数字常住户⼝所在县市的⾏政区划代码，８位数字出⽣⽇期代码，３位数字顺序码和１位检验码。

其中３位数字顺序码，是为同⼀地址码的同年同⽉同⽇出⽣⼈员编制的顺序号，偶数的为⼥性，奇数的为男性。

１、提取籍贯地区的⾏政区划代码（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),"男","⼥")４、检验⾝份证号码的正确性１８位⾝份证号码的最后⼀位是检验码，它是根据⾝份证前17位数字依照规则计算出来的，其值０～９或X。

身份证号码提取公式

合并两列文字：=CONCATENATE(h2,i2)知道生日生成身份证=440981&TEXT(f2,"emmdd")&INT(10+RAND()*99)&IF(e2="F",2,1)&INT(RAN D()*9+1)中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：１５位：６位数字常住户口所在县市的行政区划代码，６位数字出生日期代码，３位数字顺序码。

１８位：６位数字常住户口所在县市的行政区划代码，８位数字出生日期代码，３位数字顺序码和１位检验码。

其中３位数字顺序码，是为同一地址码的同年同月同日出生人员编制的顺序号，偶数的为女性，奇数的为男性。

２、提取出生日期信息=TEXT(RIGHT(19&MID(A2,7,LEN(A2)/2-1),8),"#-##-##")１５位：=--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(A2)=15)*19&MID(A2,7,6+(LEN(A2)=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(A2,17)),2),"男","女")４、检验身份证号码的正确性１８位身份证号码的最后一位是检验码，它是根据身份证前17位数字依照规则计算出来的，其值０～９或X。

身份证提取出生年月日通用公式

1.打开输入有身份证号码的excel表格，其实身份证号码中，从第7位开始之后的8位数字就是出生年月日，有规则所以可以利用公式提取。

2.在身份证数据列右侧列空白单元格中输入“=mid”，然后双击MID调起函数。

3.第一个参数选择需要提前出生年月日的身份证号所在单元格，第二个参数输入7，第三个参数输入8，参数之间以逗号隔开（公式的意思就是在单元格数据中，从第7位开始提前8位数字）
4.按回车键就可以提前出对应的出生年月日
5.通过鼠标下拉填充可以得出其他列身份证号对应的出生年月日
简言之就是：
1、打开输入有身份证号码的excel表格，其实身份证号码中，从第7位开始之后的8位数字就是出生年月日，有规则所以可以利用公式提取
2、在身份证数据列右侧列空白单元格中输入“=mid”，然后双击MID调起函数
3、第一个参数选择需要提前出生年月日的身份证号所在单元格，第二个参数输入7，第三个参数输入8，参数之间以逗号隔开（公式的意思就是在单元格数据中，从第7位开始提前8位数字）
4、按回车键就可以提前出对应的出生年月日，然后通过鼠标下拉填充可以得出其他列身份证号对应的出生年月日。

两个关于身份证号码的EXCEL小函数

两个关于身份证号码的EXCEL小函数办公室工作中，经常要进行员工身份证号码的录入(比如员工参加各类考试时填写报名表、录入个人养老保险及医疗保险金信息等等)。

本人在工作中，为了提高身份证号码录入的效率和质量，设计了两个关于身份证号码的EXCEL函数“IDcard_15to18”和“IDcard_Check”，分别实现这样的功能：1、当需要录入某人员的新身份证号码，手头只有该人员工的15位老身份证号码，可用“IDcard_15to18”函数将老号码转换成新号码。

2、当在EXCEL表格中手工录入完全部人员的18位新身份证号码后，如果不能确保录入的正确性，可用“IDcard_Check”函数进行正确性校验。

“IDcard_15to18”函数的实现原理是：15位老身份证号码的第6、7位之间加上出生年份的前两位(“19”或“20”)得到17位号码，用这17位号码根据公式计算得出校验位(新身份证号码的第18位)，即得到18位的新身份证号码(详细转换规则见附件2)。

“IDcard_Check”函数的实现原理是：用新身份证号码的前17位计算得出校验位，再将计算得出的校验位和实际检验位比较，如果相等，则校验正确，否则该身份证号码有错。

该函数中还加入了出生日期的检验，如出生月份不在1到12之间，则报错。

实现方法如下：打开存有身份证号码的表格，在菜单中选择“工具(T)”－“宏（M）”－“V isual Basic 编辑器”，打开V isual Basic编辑器，在编辑器菜单中选择“插入（I）”－“模块（M）”，打开模块窗口，这时光标停留在模块窗口内。

将“附件1：函数代码”的内容全部复制到模块窗口内，点磁盘图标保存，然后关闭编辑器，函数建立完成。

选中存有身份证号码的EXCEL表格，在菜单中选“插入（I）”－“函数（F）”，出现插入函数窗口(见下图)，在函数窗口的“或选择类别”后选择“用户定义”，出现刚才定义的两个函数的名称“IDcard_15to18”和“IDcard_Check”，“IDcard_15to18”函数用来将老身份证号码转换为新身份证号码，这个函数有两个参数，第一个参数为要转换的15位老身份证号码，第二个参数为4位出生年份的前两位，例如B3单元格中存有一15位老身份证号码，并知此人的出生年份为“19XX”年，可在B4单元格中录入“=IDcard_15to18(b2,19)”，即得到此人的18位新身份证号码；“IDcard_Check”函数用来校验18位身份证号的一致性，例如B3单元格中存有一新身份证号，在可在B4单元格中录入“=IDcard_Check(B2)”对该号码进行校验，如检验正确则显示“校验正确!”，否则显示“此身份证号有误!”。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

用vlookup函数，及其他各项功能，通过身份证号得出了出生日期、性别、户籍所在地等多项信息，并加入了身份证号正确性验证机制和15位、18位号码的转换功能。

详细制作步骤
要想了解身份证的秘密，首先让我们了解一下我国现行的公民身份证标准。

1.我国现行使用公民身份证号码有两种标准。

2.15位身份证号码（六位数字地址码，六位数字出生日期码，三位数字顺序码）。

3.18位身份证号码（六位数字地址码，八位数字出生日期码，三位数字顺序码和
一位数字校验码）。

4.
5.地址码表示编码对象常住户口所在县(市、旗、区)的行政区划代码。

6.日期码表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、
日之间不用分隔符。

7.顺序码表示同一范围内对同年、月、日出生的人员编定的顺序号。

奇数分给男
性，偶数分给女性。

8.校验码是根据前面十七位数字码计算出来的检验码。

复制代码
了解了以上知识后，让我们来制作Excel表。

1.新建一个Excel文档，
2.将Sheet1重命名为“身份证信息”，在第1行各列中依次输入“身份证号码、
十五位身份证号码、十八位身份证号码、性别、出生日期、年龄、省份、市、
区县”等文字，并将“出生日期”列设置为日期（yyyy.mm.dd）格式，其余列
设置为文本格式。

3.将Sheet2重命名为“区域信息”，从国家统计局下载到最新县及县以上行政
区划代码，经整理后分别导入到“区域信息”的A、B两列中，以便查询。

复制代码
到此，Excel表框架搭建完成，我们来写入函数完成查询。

第一步判断号码是否为正确的身份证号
一个正确的身份证号码，要符合以下几个标准：
①应为15位或18位；
②要包含数字（0－9）或字符（X）；
③18位身份证号最后一位是真正的校验码。

如果上面三个条件都满足，则是正确的身份证号。

我们利用Excel单元格的“有效性”限制来完成校验。

具体方法是选择“身份证信息”表，全选“身份证号码”列，选择“数据－有效性”菜单命令，在弹出的窗口“设置”标签中，将有效性条件的“允许”设置为“自定义”、“公式”
设置为
＝
OR(LEN(A2)=15,IF(LEN(A2)=18,MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17 ")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(A2)))，如图一。

在“出错警告”标签中，设置出错提示信息，如图二。

第二步 15位和18位身份证号码的互换
具体方法是选择“身份证信息”表，在B2单元格中输入
=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))；
在C2单元格中输入
{=IF(LEN(A3)=15,REPLACE(A3,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A3,7,,1 9),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A3)}
这里要注意是数组！
第三步判断性别
如果是15位的，则取最后一位；
如果是18位的号，则取倒数第二位；
最后判断该数值的奇偶性即可。

具体方法是选择“身份证信息”表，在D2单元格中输入
=IF(A2<>"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),)
第四步判断出生日期
如果是15位的，则取第7至12位，在年份数前加上“19”；
如果是18位的，则直接取第7至14位；
具体方法是选择“身份证信息”表，在E2单元格中输入
=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,)
第五步判断年龄
这里利用一个Excel的隐藏函数 DATEDIF（）
基本语法： =DATEDIF(开始日期，结束日期，单位代码），用“Y”来表示年份。

具体方法是选择“身份证信息”表，在F2单元格中输入
=IF(A2<>"",DATEDIF(TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00"),T ODAY(),"Y"),)
第六步判断籍贯，包括省份、市、区县
这是本期比赛最难的一部分，关键在于如何多重判断省份、市、区县信息。

首先要知道地址码的构成。

代码前两位是省或直辖市代码，中间两位是市代码，最后两位是区县代码。

其次是数据存放。

“区域信息”表中已经存放了查询所需的数据，将数据区命名为“Code”以便查询。

如图三
最后利用VLOOKUP函数来查询数据。

基本语法：＝VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
具体方法是选择“身份证信息”表，
在G2单元格中输入=IF(A2<>"",VLOOKUP(LEFT(A2,2)&"0000",code,2,),)
在G2单元格中输入=IF(A2<>"",VLOOKUP(LEFT(A2,4)&"00",code,2,),)
在G2单元格中输入=IF(A2<>"",VLOOKUP(LEFT(A2,6),code,2,),)
最后一步根据需要拖拽
刚才所写的公式只是在第2行写入的，大家可以根据需要向下拖拽，具体方法略去。

身份证号码函数

最新 第二代身份证号码提取出生年月日的公式 (很简单)

身份证号验证函数

从身份证号码里学常用函数

EXCEL表格中根据身份证号码自动生成出生日期、年龄和性别的函数

电子表格中关于身份证号的函数

Excel表格中根据身份证号码自动填出生日期、性别、年龄的函数公式

EXCEL表中身份证号码年月日提取公式

根据身份证号码(15位和18位通用)

最简单实用的身份证号码提取出生年月男女公式

excel身份证验证公式

18位身份证号码转换成出生日期的函数公式

Excel校验身份证号码的自定义函数

电子表格中关于身份证号的函数

身份证号码提取公式

身份证提取出生年月日通用公式

两个关于身份证号码的EXCEL小函数

最新第二代身份证号码提取出生年月日的公式 (很简单)