身份证核对公式.docVIP

EXCEL中你输入的公民身份号码正确吗？ 　　　 目录：1、输入错误自动红色显示提示 　　　2、15位身份证号码升位为18位公民身份号码 　　　3、自动生成出生日期和性别 　　　4、中华人民共和国国家标准　GB 11643-1999　公民身份号码 　　 一、输入错误自动红色显示提示 　　 在企业报送的EXCEL电子表格中，经常遇到公民身份号码输入错误而给工作带来不必要的麻烦，降低了工作效率。 　　 有没有办法在公民身份号码录入错误时进行自动提示呢？ 　　 其实【中华人民共和国国家标准 GB 11643-1999】对公民身份号码第18位校验码已作了详细的规定，只是我们在电算工作中很少用到而已。 可用以下方法在EXCEL中录入公民身份号码错误时自动红字显示： 　　 选择需要录入公民身份号码的单元格（如A1），选择菜单：格式/条件格式。 　　 ①点击条件1左边的下拉箭头，选择公式，在右边的公式栏中输入以下公式： =AND(LEN()0,LEN(D4)15,LEN(D4)18) 接着单击格式，点击字体，颜色选择红色，确定。 　　 ②在条件格式中点添加，点击条件2左边的下拉箭头，选择公式，在右边的公式栏中输入以下公式： =MID(10MOD(SUMPRODUCT(MID(,ROW($1:$17),1)*MOD(2^(18-ROW($1:$17)),11)),11)+1,1)MID(D4,18,1) 接着单击条件2中的格式，点击字体，颜色选择红色，确定，确定。 　　 右键按住A1单元格右下角的小黑点，向下拖至所需要录入公民身份号码的所有单元格，松开右键，选择仅填充格式。 在接着的录入中，凡是输入不是15位或18位，或者输入18位错误的公民身份号码都会红色显示。 　　 二、15位身份证号码升位为18位公民身份号码 　　 要使单元格中A1中的15位身份证号码升位为18位公民身份号码，只需在单元格A2中输入以下公式即可： 　　 =REPLACE(A1,7,,19)MID(10MOD(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(A1,7,2),MID(A1,9,2),MID(A1,11,2)),)) 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，可对符合条件的记录进行计数。 　（更