下载文档原格式
验证公式一=IF(LEN(H20)=18,IF(RIGHT(H20,1)="X",IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18) )*7 LEFT(RIGHT(H20,17))*9 LEFT(RIGHT(H20,16))*10 LEFT(RIGHT(H20,15))*5 LEFT(RIGHT(H20,14))*8 LEFT(RIGHT(H20,13))*4 LEFT(RIGHT(H20,12))*2LEFT(RIGHT(H20,11))*1 LEFT(RIGHT(H20,10))*6 LEFT(RIGHT(H20,9))*3LEFT(RIGHT(H20,8))*7 LEFT(RIGHT(H20,7))*9 LEFT(RIGHT(H20,6))*10LEFT(RIGHT(H20,5))*5 LEFT(RIGHT(H20,4))*8 LEFT(RIGHT(H20,3))*4LEFT(RIGHT(H20,2))*2),11)1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1)),"正确！","出错啦！"),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(H20,18))*7 LEFT(RIGHT(H20,17))*9LEFT(RIGHT(H20,16))*10 LEFT(RIGHT(H20,15))*5 LEFT(RIGHT(H20,14))*8 LEFT(RIGHT(H20,13))*4 LEFT(RIGHT(H20,12))*2 LEFT(RIGHT(H20,11))*1LEFT(RIGHT(H20,10))*6 LEFT(RIGHT(H20,9))*3 LEFT(RIGHT(H20,8))*7LEFT(RIGHT(H20,7))*9 LEFT(RIGHT(H20,6))*10 LEFT(RIGHT(H20,5))*5LEFT(RIGHT(H20,4))*8 LEFT(RIGHT(H20,3))*4 LEFT(RIGHT(H20,2))*2),11)1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(H20,1))*1,"正确！","出错啦！")),IF(LEN(H20)=15,"老号，请注意！",IF(LEN(H20)=0,"缺号码","位数不对！")))验证公式二=MID("10X98765432",MOD(SUMPRODUCT(MID(H20,ROW(INDIRECT("1:17")),1)*2^(18 -ROW(INDIRECT("1:17")))),11) 1,1)=RIGHT(H20,1)身份证有15位和18位两种，身份证位数是否正确，我们可以用LEN函数判断。
Excel函数技巧：巧用Excel函数公式检验身份证号码小编有话说：小编还是第一次了解到身份证的编码规则呢，原来并不复杂，根据这个编码规则，我们自己也可以辨别身份证的真假，太牛逼了！不过说真的，要是公司员工的身份证号码写错了，而我们还没发现，真的会给公司带来很大的麻烦，所以今天我们就来和大家分享一个核查身份证号码是否有错误的公式。
一般来说，身份证号码会出现两类错误，长度错误和内容错误。
出现错误的原因大多数都输入过程中发生的，对于长度错误来说，目前都是18位的号码，如果长度出现错误，比较容易发现，但是如果其中的某一个数字出现错误，那就非常难以发现，以后就有可能会造成一些严重的后果，因此，今天重点就是核查内容的正确性。
一、身份证号组成原理首先需要了解一点关于身份证号码的编码规则：18位身份证号码组成：ddddddyyyymmddxxsp共18位，其中：1-6位是地区编码，7-14为出生日期，15-17这三位是性别代码，最后一位是根据前面的17个数字得到的校验位。
校验位的计算规则比较复杂：（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要检验一个身份证号码是否有误，原理就是按照这个规则计算出校验码，再与最后一位数字去比较，如果不一致就是有问题了。
对于这个规则理解起来还是比较费劲的，我们举个例子来看看：1、将前面的身份证号码17位数分别乘以不同的系数。
从第一位到第十七位的系数分别为：7－9－10－5－8－4－2－1－6－3－7－9－10－5－8－4－2。
一分钟提取身份证号码信息
在日常工作中，我们很常涉及到填写职员的各种信息，如果一个一个输入，效率可能有点低哦。
下面，小编介绍一系列的提取身份证号码信息的公式和方法。
一分钟搞定，等着点赞吧。
1、性别公式：
公式1：=IF(MOD(MID(C2,IF(LEN(C2)=15,15,17),1),2)=1,'男','女')
公式2：=IF(MOD(MID(C2,15,3),2),'男','女')
2、籍贯公式：
=VLOOKUP(LEFT(C2,6),籍贯对应表!A:C,2,0)
这里要注意的是，必须建立一个全国籍贯信息附表，才能查询到籍贯信息的。
3、出生日期公式：
公式1：=DATE(MID(C2,7,4),MID(C2,11,2),MID(C2,13,2))
公式2：=MID(C2,7,8)
提取出生日期除了运用公式外，还可以手工操作，速度也不慢哦。
方法：在第一个单元格输入2008-01-29，选中全列需填年龄的单元格，点击“填充”（快捷键Ctrl E),选快速填充。
如下图：
OK！
4、年龄公式：
=YEAR(NOW())-MID(C2,7,4)
说多无谓，行动最实际。
快练练手吧。
EXCEL 身份证查对公式EXCEL 中你输入的公民身份号码正确吗?目录 :1、输入错误自动红色显示提示2、15 位身份证号码升位为18 位公民身份号码3、自动生成出诞辰期和性别4、中华人民共和国国家标准GB 11643-1999公民身份号码一、输入错误自动红色显示提示在公司报送的 EXCEL 电子表格中 ,常常碰到公民身份号码输入错误而给工作带来不用要的麻烦 ,降低了工作效率。
有没有方法在公民身份号码录入错误时进行自动提示呢?其实【中华人民共和国国家标准码第 18 位校验码已作了详尽的规定而已。
GB 11643-1999 】对公民身份号,不过我们在电算工作中极少用到可用以下方法在EXCEL 中录入公民身份号码错误时自动红字显示:选择需要录入公民身份号码的单元格 (如 A1), 选择菜单 :格式 /条件格式。
①点击条件 1 左侧的下拉箭头 ,选择公式 ,在右边的公式栏中输入以下公式 :=AND(LEN(D4)<>0,LEN(D4)<>15,LEN(D4)<>18)接着单击格式 ,点击字体 ,颜色选择红色 ,确立。
②在条件格式中点增添,点击条件 2 左侧的下拉箭头 ,选择公式 ,在右边的公式栏中输入以下公式:=MID("10X98765432",MOD(SUMPRODUCT(MID(D4,ROW($1:$ 17),1)*MOD(2^(18-ROW($1:$17)),11) ),11)+1,1)<>MID(D4,18,1)接着单击条件 2 中的格式 ,点击字体 ,颜色选择红色 ,确立 ,确立。
右键按住 A1 单元格右下角的小黑点 ,向下拖至所需要录入公民身份号码的全部单元格 ,松开右键 ,选择仅填补格式。
在接着的录入中 ,凡是输入不是 15 位或 18 位 ,或许输入 18 位错误的公民身份号码都会红色显示。
身份证重复显蓝色的公式:COUNTIF($D$4:$D$65536,D4)>1二、15 位身份证号码升位为18 位公民身份号码要使单元格中 A1 中的 15 位身份证号码升位为 18 位公民身份号码 , 只需在单元格 A2 中输入以下公式即可 :=REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1)或=REPLACE(A1,7,,19)&LOOKUP(MOD(SUMPRODUCT(MID(RE PLACE(A1,7,,19),ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,7,6,5,4,3,2})三、自动生成出诞辰期和性别假如单元格 A1 为身份证号码或公民身份号码 ,单元格 A2 为性别 , 单元格 A3 为出诞辰期 ,则操作以下 :在单元格 A2 中输入以下公式 ,则会自动生成性别:=IF(LEN(A1)=18,IF(INT(MID(A1,17,1)/2)=MID(A1,17,1)/2,"女 "," 男"),IF(LEN(A1)=15,IF(INT(MID(A1,15,1)/2)=MID(A1,15,1)/2,"女","男"),""))在单元格 A3 中输入以下公式 ,则会自动生成出诞辰期:=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2) ),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),""))[顶] EXCEL 中多条件乞降、计数的 4 种方法EXCEL 中多条件乞降、计数的方法大概可概括为 4 种:⒈自动挑选法⒉归并条件法⒊数组公式法⒋调用函数法先翻开上边的工作表,分别用这 4 种方法对同时知足“A2:A15地区为A,B2:B15 地区为 10,C2:C15 地区为Ⅰ”条件的 E2:E15 地区进行乞降、计数。
