EXCEL身份证核对公式
EXCEL中你输入的公民身份号码正确吗?
目录:1、输入错误自动红显示提示
2、15位身份证号码升位为18位公民身份号码
3、自动生成出生日期和性别
4、中华人民共和国国家标准GB 11643-1999 公民身份号码
一、输入错误自动红显示提示
在企业报送的EXCEL电子表格中,经常遇到公民身份号码输入错误而给工作带来不必要的麻烦,降低了工作效率。
有没有办法在公民身份号码录入错误时进行自动提示呢?
其实【中华人民共和国国家标准 GB 11643-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:$1 7),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})
三、自动生成出生日期和性别
excel求和的三种方法如果单元格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)}。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。