Excel判断身份证号校验码
验证身份证号是否正确的公式、通过身份证号提取出生年月、性别、年龄等的公式汇总
身份证号验证：=IF(LEN(身份证号所在单元格)=0,"空",IF(LEN(身份证号所在单元格)=15,"老号",IF(LEN(身份证号所在单元格)<>18,"位数不对",IF(CHOOSE(MOD(SUM(MID(身份证号所在单元格,1,1)*7+MID(身份证号所在单元格,2,1)*9+MID(身份证号所在单元格,3,1)*10+MID(身份证号所在单元格,4,1)*5+MID(身份证号所在单元格,5,1)*8+MID(身份证号所在单元格,6,1)*4+MID(身份证号所在单元格,7,1)*2+MID(身份证号所在单元格,8,1)*1+MID(身份证号所在单元格,9,1)*6+MID(身份证号所在单元格,10,1)*3+MID(身份证号所在单元格,11,1)*7+MID(身份证号所在单元格,12,1)*9+MID(身份证号所在单元格,13,1)*10+MID(身份证号所在单元格,14,1)*5+MID(身份证号所在单元格,15,1)*8+MID(身份证号所在单元格,16,1)*4+MID(身份证号所在单元格,17,1)*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=IF(ISNUMBER(RIGHT(身份证号所在单元格,1)*1),RIGHT(身份证号所在单元格,1)*1,"X"),"正确","错误"))))性别：=IFERROR(IF(MOD(VALUE(MID(身份证号所在单元格,17,1)),2)=1,"男","女"),"")年龄：=IFERROR(YEAR(NOW())-VALUE(MID(身份证号所在单元格,7,4)),"")出生年月日：=CONCATENATE(MID(身份证号所在单元格,7,4),"-",MID(身份证号所在单元格,11,2),"-",MID(身份证号所在单元格,13,2))是否农村青壮年（女16至45、男16至50）：=IF(户口性质<>"农业户口","否",IF(AND(性别="女",G4>=16,G4<=45),"是",IF(AND(性别="男",G4>=16,G4<=50),"是","否")))照片插入：第一步：新建一寸照文件夹为“目标文件夹”，照片命名例：99人以内（01+姓名），999人以内（001+姓名）第二步：在基础信息表内输入公式：=”照片源地址”&\”&序号栏&姓名栏&”.jpg”输入公式后，把公式内所有的“\”都换为“\\”；序号要求：99人以内从01开始，999人以内从001开始；第三步：到WORD文档，建立表格，并使光标在照片格“插入”→“文档部件”→“域”→“includepicture”→域属性输入“123”→FN+ALT+F9→把先前输入的“123”删除，在邮件合并内找输入合并域，选择“照片栏”→FN+F9。
EXCEL中如何提取身份证出生日期和性别信息以及检验身份证号码的正确性
1、检验身份证号码的正确性（1）录入前可以通过下面操作避免录入错误通用公式为：=IF(LEN(D6)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(D6,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT ("1:17")))),11)+1,1)=RIGHT(D6),IF(LEN(D6)=15,ISNUMBER(--TEXT(19&MID(D6,7,6),"#-00-00"))))你也可以将上述公式放在数据有效性中，防止录入错误的身份证号。
操作方法:选择需要输入身份证的全部单元格区域,比如D6:A10,点菜单"数据"-"有效性",在"允许"的下拉框中选择"自定义",在"公式"输入上面的15位和18位通用公式,确定以后即可。
注意:公式里的"D6"是你刚才选定要输入身份证的单元格区域的第一个单元格,如果你是要在C3:C20输入身份证号,则将公式里的"D6"改为"C3"。
另外,你也可以先设置好某单个单元格的数据有效性(这时公式的D6改为选定的单元格),再用格式刷将其格式刷到其他需要相同设置的单元格。
（2）录入后可以通过下面操作检测录入错误通用公式为：=IF(LEN(D6)=18,MID("10X98765432",MOD(SUMPRODUCT(MID(D6,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=RIGHT(D6),IF(LEN(D6)=15,ISNUMBER(--TEXT(19&MID(D6,7,6),"#-00-00"))))将上面的公式放到B2单元格，如果结果为TRUE，则身份证号是正确的，结果为FALSE则是错误的。
excel用好身份证中的信息
我们知道，身份证号码包含着很多重要的个人信息，比如，性别、年龄、生日、所在地。
在Excel2013中，我们可以借助函数方便地从身份证中提取相关信息。
小知识中国人民共和国公民第二代身份证号码，按照顺序从左至右依次为：6位数字地址码、8位数字出生年月码、3位数字顺序码和1位数字检验码。
Tips出生年月所在列的格式要设置为“日期”，选中所在列，右击选择“设置单元格格式”，在“数字”下选择“日期”，再选择一种日期的类型。
提取出生年月身份证号码中，从第7位开始就是出生年月日，15位身份证号包含6位生日码，18位身份证号包含8位生日码，只要确定身份证号的位数，就可以使用Excel中的函数获取具体的性别。
将身份证号码输入Excel电子表格中，在生日一列中输入如下函数（如图1）：=TEXT（RIGHT（19&MID（B2，7，6+（LEN（B2）=18）*2），8），"0-00-00"）+0这里的B2表示身份证号码所在列，MID（B2，7，6+（LEN（B2）=18）*2）是判断身份证号码的位数，18位取8位，不然则提取6位，如果是6位则自动在前面添加19，最后通过通过text函数将8位日期文本转换成日期格式的数据。
如果身份证号是18位，那提取生日的公式就是：=TEXT（MID（B2，7，8），"0-00-00"）+0判定个人性别身份证号码中出生年月的后3位是顺序码，其中奇数表示男性，偶数表示女性，因此可以根据顺序码自动判定性别。
加入身份证号码从B2列开始，则公式应该这么写（如图2）：=IF（MOD（MID（B2，15+（LEN（B2）=18）*2，1），2），"男"，"女"）这里主要是通过MOD对顺序码进行判断，奇数自动判定为男性，偶数自动判定为女性。
获取真实年龄我们还可以根据身份证号码获取真实年龄，这里可通过DATEDIF函数从身份证号码中获取真实年龄。
excel表格在同一张表格里如何检查身份证号码和名字对照是否一致
竭诚为您提供优质文档/双击可除excel表格在同一张表格里如何检查身份证号码和名字对照是否一致篇一：如何用excel来判断身份证号码是否正确如何用excel来判断身份证号码是否正确？没有参照系，只要格式、位数没错，就难以辨其是非了；而给出参照系，即可判别：★按补充题意，用性别判别对与错：1、如果以c列输入性别为准，在b列输入公式“=iF(iF(mod(mid(a1,17,1),2)=0,"女","男")=c1,"第17位正确","第17位错")”，向下复制到需要处如b3，即可判别a列号码第17位数码之对错，如黄色区域所示。
2、如果以a列输入号码为准，在d列输入公式“=iF(iF(mod(mid(a1,17,1),2)=0,"女","男")=c1,"c列正确","c列填错")”，向下复制到需要处如d3，即可判别c列填写之对与错，如绿色区域所示。
3、若以c列为准，还可以用红色标示a列数码以示其17位错误，以与b列相呼应。
请选中a1:a3在“格式/条件格式”中查看公式和格式具体设置。
附件：辨别.xls篇二：excel中判断身份证号的几个方法excel中判断身份证号是否正确的方法探讨身份证有15位和18位两种，身份证位数是否正确，我们可以用len函数判断。
但身份证上的日期是否合法：月份是否在1-12之间，日期是否在1-31之间，并且2月份只有28或29天，其他月份30或31天，都不能超过范围。
另外一般规定6岁以上才可以办理身份证，也就是年份也有一个超范围的可能性。
综合起来看，有三类错误：“身份证位数不对”、“月日错误”、“年份错误”。
假定身份证号码在b1单元格，下面的公式可以综合判断以上三种错误：=iF(oR(len(b1)=18,len(b1)=15),iF(len(b1)=18,iF(oR(m onth(date(1*(mid(b1,7,4)),1*(mid(b1,11,2)),1*(mid(b 1,13,2))))1*(mid(b1,11,2)),day(date(1*(mid(b1,7,4)) ,1*(mid(b1,11,2)),1*(mid(b1,13,2))))1*(mid(b1,13,2) )),"月日错误",""),iF(len(b1)=15,iF(oR(month(date(1*(mid(b1,7,2))+1900,1*(mid(b1,9,2)),1*(mid(b1,11,2))))1*(mid(b1, 9,2)),day(date(1*(mid(b1,7,2))+1900,1*(mid(b1,9,2)) ,1*(mid(b1,11,2))))1*(mid(b1,11,2))),"月日错误",""))),"身份证位数不对")&iF(and(len(b1)=18,1*mid(b1,7,4)>yeaR(today())-6) ,"年份错误","")公式首先对身份证长度进行判断，是否等于15位或18位，然后对18位和15位身份证分别判断月日是否合法，最后判断18位身份证的年份是否合法。
EXCEL身份证号码计算出生年月年龄及性别和重名筛选
EXCEL身份证号码计算出生年月年龄及性别和重名筛选在学校的人事管理中经常会遇到需要统计教职工的年龄的问题，但案头的原始资料只有身份证号码，其实这足够了。
在EXCEL中，引用其内置函数利用身份证号码达到此目的比较简单。
1、身份证号码简介（18位）：1～6位为地区代码；7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15～17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。
2、确定“出生日期”：18位身份证号码中的生日是从第7位开始至第14位结束。
提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。
①正确输入了身份证号码。
(假设在D2单元格中)②将光标定位在“出生日期”单元格(E2)中，然后在单元格中输入函数公式“=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13,2)”即可计算出“出生日期”。
关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID（text,starl_num,num_chars）。
Text是指要提取字符的文本或单元格地址（上列公式中的D2单元格）。
starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。
num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。
多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-”分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。
操作效果如下图：3、确定“年龄”：“出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式“=INT((TODAY()-E2)/365)”即可计算出“年龄”。
身份证号校验--避免输入错,有错即刻提示
22
-
说明23：21..本身表故中受职益业人类如别为依“据法《定中”国指人被保保寿险险人有的限法公定司第》一职顺业序分继类承表人确。定。 第1页，共5页
-
制表日期： 年 月 日
24
-
25
-
26
-
27
-
28
-
29
-
30
-
31
-
32
-
33
-
34
-
35
-
36
-
37
-
38
-
39
-
40
-
41
-
42
-
43
-
44
-
67
-
68
-
69
-
70
-
71
-
72
-
73
-
74
-
75
-
76
-
77
-
说明： 1.身故受益人如为“法定”指被保险人的法定第一顺序继承人。
2.本表中职业类别依据《中国人保寿险有限公司》职业分类表确定。 第3页，共5页
制表日期： 年 月 日
78
-
79
-
80
-
81
-
82
-
83
-
84
-
85
-
86
-
87
-
88
108
-
109
-
110
-
111
-
112
-
113
-
114
-
115
-
116
-
117
和身份证有关的5个Excel函数公式
和身份证有关的5个Excel函数公式2017-10-05 作者:ExcelHome 阅读:114973次以下是一组和身份证有关的函数公式，如果你是财务人员或是一名HR，那就一起来看一下啦：1、查找重复身份证号码：=IF(COUNTIF(A:A,A2&"*")>1,"重复","")要点：在COUNTIF函数的第二参数后加上一个星号&"*"。
COUNTIF函数在计算文本型数字时，会默认按数值型进行处理，但是Excel中的数字精度只有15位，并且身份证号码是18位，因此会把前15位相同的身份证全部识别为相同的内容。
在第二参数后加上一个星号&"*"，就是告诉Excel，要查找包含A2单元格内容的文本，通过这样变通的手段，COUTNIF函数就听话啦。
2、用出生年月来计算年龄：=DATEDIF(A4,TODAY(),"y")要点：DATEDIF函数用于计算两个日期之间的间隔。
用法是：=DATEDIF(开始日期,结束日期,指定要返回的类型)第三参数是Y，表示年，是M，就表示月。
TODAY函数返回系统当前的日期。
DATEDIF函数是隐藏函数，输入时没有屏幕提示，需要手工录入哦。
3、根据身份证号计算出生年月：=--TEXT(MID(A2,7,8),"0!/00!/00")要点：身份证号码中的第8位到15位是出生年月信息。
首先使用MID函数从A2单元格的第7位开始，提取出8个字符，得到类似19840727的结果。
再使用TEXT函数将这个字符串变成日期样式的文本，得到类似1984/07/27的结果。
由于TEXT函数的结果是文本型的内容，所以加上两个小减号-- 就是计算负数的负数，通过四则运算，变成真正的日期序列。
公式也可以使用=--TEXT(MID(A2,7,8),"0-00-00")，原理是一样的。
如何在EXCEL中提取身份证号码里的出生年月日、性别等,相关信息
如何在EXCEL中提取身份证号码里的出生年月日、性别等，相关信息1、身份证号码简介（18位）：1～6位为地区代码；7～10位为出生年份；11～12位为出生月份；13～14位为出生日期；15～17位为顺序号，并能够判断性别，奇数为男，偶数为男；第18位为校验码。
2、确定“出生日期”：18位身份证号码中的生日是从第7位开始至第14位结束。
提取出来后为了计算“年龄”应该将“年”“月”“日”数据中添加一个“/”或“-”分隔符。
①正确输入了身份证号码。
(假设在D2单元格中)②将光标定位在“出生日期”单元格(E2)中，然后在单元格中输入函数公式“=MID(D2,7,4)&"-"&MID(D2,11,2)&"-"&MID(D2,13, 2)”即可计算出“出生日期”。
关于这个函数公式的具体说明：MID函数用于从数据中间提取字符，它的格式是:MID（text,starl_num,num_chars）。
Text是指要提取字符的文本或单元格地址（上列公式中的D2单元格）。
starl_num是指要提取的第一个字符的位置（上列公式中依次为7、11、13）。
num_chars指定要由MID所提取的字符个数(上述公式中，提取年份为4，月份和日期为2）。
多个函数中的“&”起到的作用是将提取出的“年”“月”“日”信息合并到一起，“/”或“-” 分隔符则是在提取出的“年”“月”“日”数据之间添加的一个标记，这样的数据以后就可以作为日期类型进行年龄计算。
操作效果如下图：3、确定“年龄”：“出生日期”确定后，年龄则可以利用一个简单的函数公式计算出来了：将光标定位在“年龄”单元格中，然后在单元格中输入函数公式“= INT((TODAY()-E2)/365)”即可计算出“年龄”。
关于这个函数公式的具体说明：①TODAY函数用于计算当前系统日期。
只要计算机的系统日期准确，就能立即计算出当前的日期，它无需参数。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Excel判断身份证号校验码
假设在一个工作表中：A列是姓名，B列是身份证号码，C列是备注。
在B列中输入18位身份证号码后，在C2单元格中输入计算数组公式：
=RIGHT(B2,1)=MID("10X98765432",MOD(SUM(MID(B2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1) 然后再将该公式复制给C列其他单元格。
公式的计算结果是一个逻辑值，True表示校验正确，即计算出来的校验码与输入的相同，而False则表示校验错误。
值得注意的是，使用校验码进行校验判断为正确的身份证号码未必是正确的，但判断为错误的则肯定是错误的，即通过校验的只是身份证号码正确的必要条件而非充分条件。
当然，在实际输入中，身份证号码输入错误而校验正确的情况很少见。
在大多数情况下，使用校验码进行校验可以大大提高身份证号码输入的正确性。
RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。
MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。
MOD 返回两数相除的余数。
SUM 返回某一单元格区域中所有数字之和。
ROW 返回引用的行号。
INDIRECT 返回由文本字符串指定的引用。
或者输入数组公式：
=IF(LEN(B2)<>18,"错误
",IF(MID("10X98765432",MOD(SUM(MID(B2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(B2,18,1), "正确","错误"))。