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

第一篇：电子表格中关于身份证号的函数

中国居民身份证号码是一组特征组合码，原为１５位，现升级为１８位，其编码规则为：

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

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

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

１、提取籍贯地区的行政区划代码（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。一般情况只要有一位数字输入错误，依照规则计算后就会与第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(SUMPRODUCT(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(SUMPRODUCT(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周岁以下为男性16周岁（含16周岁）以上至50周岁为 2 男性50周岁（含50周岁）以上至60周岁为 3 男性60周岁（含60周岁）以上为

女性16周岁以下为女性16周岁（含16周岁）以上至45周岁为 2 女性45周岁（含45周岁）以上至55周岁为 3 女性55周岁（含55周岁）以上为

=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")

第二篇：身份证号错误函数

Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo ErrorHandle '设置错误处理

Application.Volatile(False)'将函数标记为非易失性函数

If Target.Column = 15 And Target.Row > 4 Then '此处设置身份证件号所在列序号

Dim wi As Variant, ji As Variant, sum%, i%, datBirthday As Date

Dim s As String

s = Target.Text

wi = Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2)

ji = Array(“1”, “0”, “X”, “9”, “8”, “7”, “6”, “5”, “4”, “3”, “2”)If Len(s)= 18 Then If Not(IsNumeric(Mid(s, 1, 17))And(IsNumeric(Right(s, 1))Or Right(s, 1)= “X”))Then

Err.Raise vbObjectError + 1001, , “号码中有非法字符”

GoTo ErrorHandle End If ElseIf Len(s)= 0 Then

Cells(Target.Row, Target.Column).Interior.ColorIndex = 0 Exit Sub Else

Err.Raise vbObjectError + 1002, , “号码不是18位”

GoTo ErrorHandle End If datBirthday = DateValue(Mid(s, 7, 4)& “-” & Mid(s, 11, 2)& “-” & Mid(s, 13, 2))

sum = 0 For i = 0 To UBound(wi)sum = sum + Mid(s, i + 1, 1)* wi(i)Next i

If ji(sum Mod 11)<> Right(s, 1)Then

MsgBox “18位身份证号码中的校验码错误！” & vbCrLf & “您要输入的是：” & Left(s, 17)& ji(sum Mod 11)& “吗？”

Cells(Target.Row, Target.Column).Interior.ColorIndex = 33

Else

Cells(Target.Row, Target.Column).Interior.ColorIndex = 0 End If Exit Sub

ErrorHandle: If Err.Number = 13 Then

MsgBox “号码中出生日期非法” Else

MsgBox Err.Description End If

Cells(Target.Row, Target.Column).Interior.ColorIndex = 33 Exit Sub End If End Sub

第三篇：Excel电子表格中的函数怎么计算乘法公式

Excel电子表格中的函数怎么计算乘法公式

(1)在需要显示结果的单元格点一下，输入＝

输入要计算的单元格

例如：在c3中输入“＝”

再输入“a3*b3”，敲回车（不包括引号“”，=，*为英文字符，半角的）；

顺c3右下角往下拖就是了。

(2)乘法——计算所有参数的乘积函数PRODUCT()，括号内的每个数字用逗号分隔，数字个数不得超过30个。

如前述可以在C1单元格中编辑公式

=PRODUCT（A1，B1）

或者

=PRODUCT（A1：B1）

说明：

1、当输入函数进行运算的时候要注意的是，EXCEL函数中所有的分隔符号均要求是在英文状态下的，换句话说，如果你输入一个中文输入法下的逗号就是无效的。

2、快捷复制公式的方法(公式填充)：先点一下C3选中C3单元格，鼠标指向C3单元格的右下角（有一个小黑点），当光标变成一个黑色的实心小十字时按下左键不松手，向下拖动，这个操作叫公式填充，那活动单元格上右下角的小黑点叫填充柄，只有操作在填充柄上才可以进行公式填充。

第四篇：身份证号中的信息

身份证包含信息

2011-05-11 10:07:49| 分类： 网络转载 | 标签：校验 身份证 代码 数字 号码 |字号 订阅

一、身份证号码执行标准：18位身份证标准在国家质量技术监督局于 1999年7月1日实施的GB11643-1999《公民身份号码》。

GB11643-1999《公民身份号码》为GB11643-1989《社会保障号码》的修订版，其中指出将原标准名称“社会保障号码”更名为“公民身份号码”，另外GB11643-1999《公民身份号码》从实施之日起代替GB11643-1989。

二、编码规则：公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位校验码，可以用字母表示如为ABCDEFYYYYMMDDXXXR。其含义如下：

1.地址码（ABCDEF）：表示编码对象常住户口所在县(市、旗、区)的行政区划代码，按GB/T2260的规定执行。

2.出生日期码（YYYYMMDD）：表示编码对象出生的年、月、日，按GB/T7408的规定执行，年、月、日分别用4位、2位（不足两位加0）、2（同上）位数字表示，之间不用分隔符。

3.顺序码（XXX）：表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。

4．校验码（R），一位数字，通过前17位数字根据一定计算得出。

三、关于地址码含义的详细解释： 中华人民共和国行政区划代码（GB/T2260—999）

代码 省、市名称 代码 省、市名称 北京市 42 湖北省 12 天津市 43 湖南省 13 河北省 44 广东省 山西省 45 广西壮族自治区 15 内蒙古自治区 46 海南省 21 辽宁省 50 重庆市 22 吉林省 51 四川省 23 黑龙江省 52 贵州省 31 上海市 53 云南省 32 江苏省 54 西藏自治区 33 浙江省 61 陕西省 34 安徽省 62 甘肃省 35 福建省 63 青海省

江西省 64 宁夏回族自治区 37 山东省 65 新疆维吾尔自治区 41 河南省

身份证前六位是地区代码，我们用ABCDEF表示。代码的解释规则如下： A:国内区域

华北三省二市 2 东北三省 3 华东六省一市 4 华南六省 5 西南四省一市 6 西北五省 7 台湾 8 港澳

B（或者说是AB，就是前2位）:省（直辖市，自治区，特别行政区）代码

按照A划定的分区定义省代码，有直辖市的，直辖市列前，其余按离直辖市的距离排序，没有直辖市的，按离北京的远近排序。

具体省（直辖市，自治区，特别行政区）代码如下： 11-15 京 津冀 晋 蒙 21-23 辽 吉黑

31-37 沪 苏浙 皖 闽 赣 鲁 41-46 豫 鄂湘 粤 桂 琼 50-54 渝 川贵 云 藏 61-65 陕 甘青 宁 新 81-82 港 澳

CD:城市代码

从01开始排，对于直辖市，CD=01表示市辖区，CD=02表示辖县；省的城市代码从省会开始排，比如2101=沈阳2102=大连…… 只有地级城市有独立的城市代码，县级市没有。

EF:市辖区、郊区、郊县、县级市代码

如果EF=00，指代这个城市，不特定区县；对于非直辖市，如EF=01，指代市辖区（任意一个区），02开始指代特定的区。其中：

E=0代表市辖区，E=1代表郊区，E=2代表郊县，E=8代表县级市。

对于直辖市，从01开始就依次排区，没有市区和郊区的代码区分。：

四、生日期码（YYYYMMDD）表示编码对象出生的年、月、日，其中年份用四位数字表示，年、月、日之间不用分隔符。例如：1981年05月11日就用19810511表示。

五、顺序码（XXX）（身份证第十五位到十七位）是县、区级政府所辖派出所的分配码，每个派出所分配码为10个连续号码，例如“000-009”或“060-069”，其中单数为男性分配码，双数为女性分配码，如遇同年同月同日有两人以上时顺延第二、第三、第四、第五个分配码。如：007的就是个男生，而且和他同年月日生的男生至少有两个，他们的后四位是001*和003*。分配顺序码中“999、998、997、996”四个顺序号分别为男女性百岁以上老人专用的特定编号。

六、关于校验码

校验码是通过一系列数学计算得出来的，具体校验的计算方式如下：

1.对前17位数字本体码加权求和

公式为：S = Sum(Ai * Wi), i = 0,..., 16

其中Ai表示第i位置上的身份证号码数字值，Wi表示第i位置上的加权因子，其各位对应的值依次为： 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2

通俗解释：身份证第一位数字X7+第二位X9+第三位X10+第四位X5+第五位X5+第六位X8+第七位X4+第八位X1+第九位X6+第十位X3+十一位X7+十二位X9+十三位X10+十四位X5+十五位X8+十六位X4+十七位X2；计算出总和（用S）表示。

2.以11对计算结果取模

Y = mod(S, 11)通俗解释：用S除以11，看最后的余数。如果除尽，为0；余数为1，则计为1；最大余数为10；全部数字为0-10共11个数字。（用Y表示）。

3.根据模的值得到对应的校验码

对应关系为：

Y值： 0 1 2 3 4 5 6 7 8 9 10

校验码： 1 0 X 9 8 7 6 5 4 3 2

通俗解释：余数为0，则校验码为1；依次类推：余数为1，则校验码对应0；以下：2--X；3--9；4--8；5--7；6--6；7--5；8--4；9-3；10-2。

如果校验码不符合这个规则，则肯定是假号码。

关于18位身份证号码尾数是“X”的一种解释：因为按照上面的规则，校验码有11个，而不是10个，所以不能用0-9表示。所以如果尾号是10，那么就得用X来代替，因为如果用10做尾号，那么此人的身份证就变成了19位，而19位的号码违反了国家标准，并且我国的计算机应用系统也不承认19位的身份证号码。Ⅹ是罗马数字的10，用X来代替10，可以保证公民的身份证符合国家标准。

根据〖中华人民共和国国家标准 GB 11643-1999〗中有关公民身份号码的规定，公民身份号码是特征组合码，由十七位数字本体码和一位数字校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。身份证磁条的作用：

除了楼上说姓名，性别，民族，籍贯，身份证号码，发证机关名称，发证日期及有效期的上述内容，还有被公安机关处罚过的档案，比如：有吸毒历史、受过治安拘留、刑满释放等等，还有你的单位、学历、家人的基本信息，而且是终身携带，谁也消除不了，只不过在公安的系统才能看到，酒店只能看到基本信息！就是楼上说的信息。

第五篇：身份证号

身份证号: 功能: 1)查询身份证号所属地区 2)分析出生年月日、性别

是查看身份证所有人的所在地区 和 出生年月日、性别

18位身份号一般是由

所在地+所在区（县）+出生年月日+个人代码组成的！

个人代码尾数（也就是身份证号的最后一位）双数为女性，单数为男性！

举个例子：我是天津，天津地区号是120，区号（以河西区为例）103 生日：1958年2月9号 个人的代码是5918

这样组成的身份证号就是 ***918

中华人民共和国行政区划代码（GB/T2260—999）

代码 省、市名称 代码 省、市名称 11 北京市 42 湖北省 12 天津市 43 湖南省 13 河北省 44 广东省 14 山西省 45 广西壮族自治区 15 内蒙古自治区 46 海南省 21 辽宁省 50 重庆市 22 吉林省 51 四川省 23 黑龙江省 52 贵州省 31 上海市 53 云南省 32 江苏省 54 西藏自治区 33 浙江省 61 陕西省 34 安徽省 62 甘肃省 35 福建省 63 青海省 36 江西省 64 宁夏回族自治区 37 山东省 65 新疆维吾尔自治区 41 河南省

我们要验证身份证号是否正确，就得先了解身份证号的含意。

身份证号都代表什么意思？

1、号码的结构

公民身份号码是特征组合码，由十七位数字本体码和一位校验码组成。排列顺序从左至右依次为：六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码。

2、地址码(前六位数）

表示编码对象常住户口所在县(市、旗、区)的行政区划代码，按GB/T2260的规定执行。

3、出生日期码（第七位至十四位）

表示编码对象出生的年、月、日，按GB/T7408的规定执行，年、月、日代码之间不用分隔符。

4、顺序码（第十五位至十七位）

表示在同一地址码所标识的区域范围内，对同年、同月、同日出生的人编定的顺序号，顺序码的奇数分配给男性，偶数分配给女性。

5、校验码（第十八位数）

（1）十七位数字本体码加权求和公式

S = Sum(Ai * Wi), i = 0,..., 16，先对前17位数字的权求和 Ai:表示第i位置上的身份证号码数字值 Wi:表示第i位置上的加权因子

Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2（2）计算模 Y = mod(S, 11)（3）通过模得到对应的校验码 Y: 0 1 2 3 4 5 6 7 8 9 10 校验码: 1 0 X 9 8 7 6 5 4 3 2 尾数“X”啥意思？升位后有一部分人身份证号码的尾数却是“X”，常常被怀疑是假证。其实，第18位校验码是由公安部门按统一的公式计算出来的，从“1”至“10”，由10个阿拉伯数字组成。假如身份证尾号是10，那么号码就成了19位数，有关部门采用了罗马数字“X”来代替10，这样，有一部分居民的身份证号码尾数就是“X”。辽宁省 210000

名称 代码 名称 代码

沈阳市 210100 西岗区 210203 市辖区 210101 沙河口区 210204 和平区 210102 甘井子区 210211 沈河区 210103 旅顺口区 210212 大东区 210104 金州区 210213 皇姑区 210105 长海县 210224 铁西区 210106 瓦房店市 210281 苏家屯区 210111 普兰店市 210282 东陵区 210112 庄河市 210283 新城子区 210113 鞍山市 210300 于洪区 210114 市辖区 210301 辽中县 210122 铁东区 210302 康平县 210123 铁西区 210303 法库县 210124 立山区 210304 新民市 210181 旧堡区 210311 大连市 210200 台安县 210321

市辖区 210201 岫岩满族自治县 210323 中山区 210202 海城市 210381 抚顺市 210400 市辖区 210801 市辖区 210401 站前区 210802 新抚区 210402 西市区 210803 露天区 210403 鲅鱼圈区 210804 望花区 210404 老边区 210811 顺城区 210411 盖州市 210881 抚顺县 210421 大石桥市 210882

新宾满族自治县 210422 阜新市 210900 清原满族自治县 210423 市辖区 210901 本溪市 210500 海州区 210902 市辖区 210501 新邱区 210903 平山区 210502 太平区 210904 溪湖区 210503 清河门区 210905 明山区 210504 细河区 210911

南芬区 210511 阜新蒙古族自治县 210921 本溪满族自治县 210521 彰武县 210922 桓仁满族自治县 210522 辽阳市 211000 丹东市 210600 市辖区 211001 市辖区 210601 白塔区 211002 元宝区 210602 文圣区 211003 振兴区 210603 宏伟区 211004 振安区 210604 弓长岭区 211005

凤城满族自治县 210621 太子河区 211011 宽甸满族自治县 210624 辽阳县 211021 东港市 210681 灯塔县 211022 锦州市 210700 盘锦市 211100 市辖区 210701 市辖区 211101 古塔区 210702 双台子区 211102 凌河区 210703 兴隆台区 211103 太和区 210711 大洼县 211121

北镇满族自治县 210725 盘山县 211122 黑山县 210726 铁岭市 211200 义县 210727 市辖区 211201 凌海市 210781 银州区 211202 营口市 210800 清河区 211204

铁岭县 211221 喀喇沁左翼蒙古族自治县 211324 西丰县 211223 北票市 211381 昌图县 211224 凌源市 211382 铁法市 211281 锦西市 211400 开原市 211282 市辖区 211401 朝阳市 211300 连山区 211402 市辖区 211301 葫芦岛区 211403 双塔区 211302 南票区 211404 龙城区 211303 绥中县 211421 朝阳县 211321 建昌县 211422 建平县 211322 兴城市 211481