范文EXCEL身份证核对公式.docx
《范文EXCEL身份证核对公式.docx》由会员分享，可在线阅读，更多相关《范文EXCEL身份证核对公式.docx（6页珍藏版）》请在冰豆网上搜索。
范文EXCEL身份证核对公式
EXCEL身份证核对公式
EXCEL中你输入的公民身份号码正确吗?
目录:
1、输入错误自动红色显示提示
2、15位身份证号码升位为18位公民身份号码
3、自动生成出生日期和性别
4、中华人民共和国国家标准GB11643-1999公民身份号码
一、输入错误自动红色显示提示
在企业报送的EXCEL电子表格中,经常遇到公民身份号码输入错误而给工作带来不必要的麻烦,降低了工作效率。
有没有办法在公民身份号码录入错误时进行自动提示呢?
其实【中华人民共和国国家标准GB11643-1999】对公民身份号码第18位校验码已作了详细的规定,只是我们在电算工作中很少用到而已。
可用以下方法在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（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）/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（A
1,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区域进行求和、计数。
一、自动筛选法
利用EXCEL的自动筛选功能和分类汇总函数对工作表数据进行求和、计数。
①选中数据区域A1:
E15,执行“数据→筛选→自动筛选”命令,进入“自动筛选”状态。
②选中E16单元格,输入分类汇总公式:
=SUBTOTAL（9,E2:
E15）,用于对求和列进行统计。
③点击“条件1”右侧的下拉按钮,在随后弹出的下拉列表中选择“A”;再点击“条件2”右
侧的下拉按钮,在随后弹出的下拉列表中选择“10”;再点击“条件3”右侧的下拉按钮,在随后弹出的下拉列表中选择“Ⅰ”。
④符合条件的数据被筛选出来,合计自动出现在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Ⅰ"）
三、数组公式法
利用数组公式进行多条件求和。
数组公式输入完成后,不能直接用“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）}。
对于有“或”条件的,可用+来完成。
如同时满足条件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"）*（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="Ⅰ"））
★SUMPRODUCT是“返回乘积之和”函数,为什么可用来计数呢?
我们现以=SUMPRODUCT（（A2:
A4="A"）*（B2:
B4=10）*（C2:
C4="Ⅰ"））为例来看他的计算过程:
先看每个单元格和三个条件的真假关系:
A2=A,条件为TRUE
A3=C,条件为FALSE（因为A3不等于A）
A4=B,条件为FALSE（因为A4不等于A）
B2=10,条件为TRUE
B3=30,条件为FALSE（因为B3不等于10）
B4=20,条件为FALSE（因为B4不等于10）
C2=Ⅰ,条件为TRUE
C3=Ⅲ,条件为FALSE（因为C3不等于Ⅰ）
C4=Ⅱ,条件为FALSE（因为C4不等于Ⅰ）
因此,原函数可变为:
=SUMPRODUCT（（TRUE,FALSE,FALSE）*（TRUE,FALSE,FALSE）*（TRUE,FALSE,FALSE））
在EXCEL中,TRUE和FALSE分别用1和0表示。
所以函数又变为:
=SUMPRODUCT（（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,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,调用[工具/宏/visualbasic编辑器],将下面代码放到worksheet中即可。
PrivateSubWorksheet_SelectionChange（ByValTargetAsRange）
Range（"A1"）=ActiveCell.Row
EndSub
为什么重命名工作表之后EXCEL会自动在前面加上
文件名?
【问题】
最近编制了一个EXCEL电子表格,文件名称为“川劳社办[2008]185号超龄人员退休待遇计算”,把其中一个工作表名称命名为“待遇审批表”,可是EXCEL会自动在前面加上部分文件名,变成“185号超龄人员退休待遇计算.xls]待遇审批表”,重新命名该工作表则出现错误,提示“在重命名工作表或图表时输入的名称无效”。
试重命名其他EXCEL电子表格中的工作表名称,不会出现这种现象
【解决办法】
为什么会出现这种现象呢?
试图通过搜索网络得到解决,发现也有不少网友提出同样的问题,但是没有解决办法!
后通过观察发现:
为什么重命名后的工作表只自动在前面加上部分文件名呢?
而文件名“]”前面的内容没有?
是不是“]”在作怪?
于是把文件名中的“[]”改成“[]”（全角）,重试问题解决了
原来EXCEL电子表格的文件名称中不能存在半角的“[]”,否则就会导致上述问题出现,如果文件名中确实需要“[]”,请用全角“[]”,或者使用没有这种BUG的更高级版本Office软件。
本问题在MicrosoftOfficeXP（即MicrosoftExcel2002（10.2614.2625））中存在。
升级会员 