《范文：EXCEL身份证核对公式》由会员分享，可在线阅读，更多相关《范文：EXCEL身份证核对公式（10页珍藏版）》请在金锄头文库上搜索。
1、EXCEL身份证核对公式EXCEL中你输入的公民身份号码正确吗?目录:1、输入错误自动红色显示提示2、15位身份证号码升位为18位公民身份号码3、自动生成出生日期和性别4、中华人民共和国国家标准GB 11643-1999 公民身份号码一、输入错误自动红色显示提示在企业报送的EXCEL电子表格中,经常遇到公民身份号码输入错误而给工作带来不必要的麻烦,降低了工作效率。有没有办法在公民身份号码录入错误时进行自动提示呢?其实【中华人民共和国国家标准 GB 11643-1999】对公民身份号码第18位校验码已作了详细的规定,只是我们在电算工作中很少用到而已。可用以下方法在EXCEL中录入公民身份号码错误
2、时自动红字显示:选择需要录入公民身份号码的单元格(如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中的格式,点击字体,颜色选择红色,
3、确定,确定。右键按住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:$1 7),1)*M
4、OD(2(18-ROW($1:$17),11),11)+1,1)或=REPLACE(A1,7,19)&LOOKUP(MOD(SUMPRODUCT(MID(REPLACE(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)
5、/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种:自动筛选法合并条件法数组公式法调用函数法先打开上面的工作表,分
6、别用这4种方法对同时满足“A2:A15区域为A,B2:B15区域为10,C2:C15区域为”条件的E2:E15区域进行求和、计数。一、自动筛选法利用EXCEL的自动筛选功能和分类汇总函数对工作表数据进行求和、计数。 选中数据区域A1:E15,执行“数据筛选自动筛选”命令,进入“自动筛选”状态。 选中E16单元格,输入分类汇总公式:=SUBTOTAL(9,E2:E15),用于对求和列进行统计。 点击“条件1”右侧的下拉按钮,在随后弹出的下拉列表中选择“A”;再点击“条件2”右侧的下拉按钮,在随后弹出的下拉列表中选择“10”;再点击“条件3”右侧的下拉按钮,在随后弹出的下拉列表中选择“”。 符合条
7、件的数据被筛选出来,合计自动出现在E16单元格中。将SUBTOTAL(9,E2:E15)中的参数9改为2或3,可对符合条件的记录进行计数。(更正:应在第一行前面插入一行输入分类汇总公式SUBTOTAL,否则在筛选时会被筛选掉。)二、合并条件法可将多个条件合并为一个条件,再利用条件求和函数、条件计数函数分别进行单条件求和、计数。在D2单元格中输入合并公式:=A2&B2&C2,选择D2:D15,按Ctrl+D向下填充。在E16单元格中输入条件求和公式:=SUMIF(D2:D15,A10,E2:E15)在E17单元格中输入条件计数公式:=COUNTIF(D2:D15,A10)三、数组公式法利用数组公
8、式进行多条件求和。数组公式输入完成后,不能直接用“Enter”键进行确认,需要用“Ctrl+Shift+Enter”组合键进行确认。确认完成后,公式两端会出现一对数组公式标志(一对大括号)。在E16单元格中输入数组公式:=SUM(A2:A15=A)*(B2:B15=10)*(C2:C15=)*E2:E15)或:=SUM(IF(A2:A15=A)*(B2:B15=10)*(C2:C15=),E2:E15)输入完成后,按下“Ctrl+Shift+Enter”组合键确认公式即可。即确认后的公式:=SUM(A2:A15=A)*(B2:B15=10)*(C2:C15=)*E2:E15)。对于有“或”条件
9、的,可用+来完成。如同时满足条件1=C,条件2=30,条件3=或,数组公式如下:=SUM(A2:A15=C)*(B2:B15=30)*(C2:C15=)+(C2:C15=)*E2:E15)或:=SUM(IF(A2:A15=C)*(B2:B15=30)*(C2:C15=)+(C2:C15=),E2:E15)输入完成后,同样要按下“Ctrl+Shift+Enter”组合键。四、调用函数法调用SUMPRODUCT函数对数据进行求和、计数。SUMPRODUCT函数:是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和。在E16单元格中输入函数公式:=SUMPRODUCT(A2:A15=A)*(
10、B2:B15=10)*(C2:C15=)*E2:E15)对于有“或”条件的,也可用+来完成。如同时满足条件1=C,条件2=30,条件3=或,该函数使用如下:=SUMPRODUCT(A2:A15=C)*(B2:B15=30)*(C2:C15=)+(C2:C15=)*E2:E15)也可用此函数来进行多条件计数:=SUMPRODUCT(A2:A15=A)*(B2:B15=10)*(C2:C15=) SUMPROD UCT是“返回乘积之和”函数,为什么可用来计数呢?我们现以=SUMPRODUCT(A2:A4=A)*(B2:B4=10)*(C2:C4=)为例来看他的计算过程:先看每个单元格和三个条件的真
11、假关系:A2=A,条件为TRUEA3=C,条件为FALSE (因为A3不等于A)A4=B,条件为FALSE (因为A4不等于A)B2=10,条件为TRUEB3=30,条件为FALSE (因为B3不等于10)B4=20,条件为FALSE (因为B4不等于10)C2=,条件为TRUEC3=,条件为FALSE (因为C3不等于)C4=,条件为FALSE (因为C4不等于)因此,原函数可变为:=SUMPRODUCT(TRUE,FALSE,FALSE)*(TRUE,FALSE,FALSE)*(TRUE,FALSE,FALSE)在EXCEL中,TRUE和FALSE分别用1和0表示。所以函数又变为:=SUM
12、PRODUCT(1,0,0)*(1,0,0)*(1,0,0)然后接下来就是SUMPRODUCT的计算过程了:=1*1*1+0*0*0+0*0*0=1所以最后的结果等于1。通过计算过程可以看出,对应位(即工作表的同一行或列,这里是同一行)只要有一个条件为0(即假,不符合条件),其乘积后就为0。也就是说在前三条记录中,同时满足三种条件的只有1条记录。同理,用SUMPRODUCT求和的计算过程如下:=SUMPRODUCT(A2:A15=A)*(B2:B15=10)*(C2:C15=)*E2:E15)=SUNPRODUCT( 1,0,0,1,1,1,0,0,0,1,0, 0, 0, 0 )*( 1,0
13、,0,0,1,1,0,0,0,0,0, 0, 0, 0 )*( 1,0,0,1,1,1,0,0,0,0,0, 0, 1, 0 )*( 1,2,3,4,5,6,7,8,9,10,11,12,13,14)-1+0+0+0+5+6+0+0+0+0+0 +0 +0 +0 =12即最后的求和结果等于12。如何在EXCEL的A1中引用当前活动单元格的行号?【问题】如何在EXCEL的A1中引用当前活动单元格的行号?也就是说,我鼠标点到哪个单元格,那这个单元格的行号就会出现在A1中。【解决办法】可通过编写VBA来完成,方法如下:打开EXCEL,调用工具/宏/visual basic 编辑器,将下面代码放到wo
14、rksheet中即可。Private Sub Worksheet_SelectionChange(ByVal Target As Range)Range(A1) = ActiveCell.RowEnd Sub为什么重命名工作表之后 EXCEL 会自动在前面加上文件名?【问题】最近编制了一个EXCEL电子表格,文件名称为“川劳社办2008185号超龄人员退休待遇计算”,把其中一个工作表名称命名为“待遇审批表”,可是EXCEL会自动在前面加上部分文件名,变成“185号超龄人员退休待遇计算.xls待遇审批表”,重新命名该工作表则出现错误,提示“在重命名工作表或图表时输入的名称无效”。试重命名其他EX
15、CEL电子表格中的工作表名称,不会出现这种现象!【解决办法】为什么会出现这种现象呢?试图通过搜索网络得到解决,发现也有不少网友提出同样的问题,但是没有解决办法!后通过观察发现:为什么重命名后的工作表只自动在前面加上部分文件名呢?而文件名“”前面的内容没有?是不是“”在作怪?于是把文件名中的“”改成“”(全角),重试问题解决了!原来EXCEL电子表格的文件名称中不能存在半角的“”,否则就会导致上述问题出现,如果文件名中确实需要“”,请用全角“”,或者使用没有这种BUG的更高级版本Office 软件。本问题在 Microsoft Office XP(即Microsoft Excel 2002 (10.2614.
