身份证号码函数

合集下载

身份证号验证函数

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)

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行写入的，大家可以根据需要向下拖拽，具体方法略去。