身份证号码应用

校验身份证号码

通过第18位校验数字，校核身份证号码是否正确。前17位数字与规定的系数相乘，相加之和除以11，根据余数查找对应的校验码，如果与第18位数字一致，身份证号码正确。

=IF(E2="","",(IF(MID("10X98765432",MOD(SUMPRODUCT(MID(E2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)=MID(E2,18,18),"","错误")))

提示

将命令中的E2替换为当前行中的身份证号所在列，如该行为第五行，J列为身份证号码所在列，即将E2替换为J5。

将出生年月随意更改，将显示“错误”。

查重身份证号码

表内查询

将当前行的身份证号与所选的全部身份证号，逐一比对，如果没有重复号码，即该身份证号码在当前表中仅出现1次，COUNTIF()函数将返回1，否则将返回重复的次数，只有大于等于2才显示为“重复”。

=IF(COUNTIF($E$2:$E$14,E2&"*")>=2,"重复","")

提示

将命令中的E2替换为当前行中的身份证号所在列，如该行为第五行，J列为身份证号码所在列，即将E2替换为J5。再将$E$2:$E$14替换为当前表中的身份证号所有列，可以通过鼠标拖拽的方法选择区域，如第一行身份证号为J5、最后一行为J35，则拖拽后为显示为J5:J35，为确保绝对引用，即不因复制公式导致引用范围自动下移，应添加$符号，即$J$5:$J$35。

复制身份证号到其他人员，将显示“重复”

表间查询

将当前行的身份证号与所选的全部身份证号，逐一比对，如果没有重复号码，即该身份证号码在拟查找表中未出现，COUNTIF()函数将返回0，否则将返回重复的次数，只有大等于1才显示为“重复”。

=IF(COUNTIF(表二!E2:E11,E2&"*")>=1,"重复","")

提示

将命令中的E2替换为当前表当行中的身份证号所在列，如该行为第五行，J列为身份证号码所在列，即将E2替换为J5。再将表二!E2:E11替换为拟查找表中的身份证号所有列，可以通过鼠标拖拽的方法选择区域，如表的名称为“抽调名单”，第一行身份证号为J5、最后一行为J35，则拖拽后为显示为抽调名单!J5:J35，为确保绝对引用，即不因复制公式导致引用范围自动下移，应添加$符号，即抽调名单!$J$5:$J$35。

可利用此命令，核对不同表中姓名重复或其他项目重复的情况。

年龄查询

• TEXT()函数可提取出生日期，但不是日期格式。
• DATEDIF()为date_different的意思，用来计算两个日期相差的年、月或日数。命令中的“2021-01-31”为计算截止2021年1月底的年龄，一般不使用其他文章推荐的today()，截止当天没有意义，也不利于统一口径。

=DATEDIF(TEXT(MID(E2,7,8),"0000-00-00"),"2021-01-31","Y")

提示

将命令中的E2替换为当前行中的身份证号所在列，如该行为第五行，J列为身份证号码所在列，即将E2替换为J5。

可以用MID(E2,7,6)函数，提取出生年月，形式为199802。

性别查询

身份证第17位奇数为男性、偶数为女性，通过mod()函数，除以2后取余数，整除为女，不能整除为男。

=IF(MOD(MID(E2,17,1),2)=1,"男","女")

提示

将命令中的E2替换为当前行中的身份证号所在列，如该行为第五行，J列为身份证号码所在列，即将E2替换为J5。