首页 > 分享 > EXCEL校验身份证号码和银行卡号

EXCEL校验身份证号码和银行卡号

花匠小妙招
2024-12-03 16:16

目录身份证相关提取出生年月提取性别信息身份证号码校验银行卡号码相关

最近有一项任务，要输入N多人员信息，身份证号码，性别，出生年月，银行卡号码等等。身份证号码上已经包含了性别和出生年月还要输入一遍，最让人抓狂的就是身份证号码和银行卡号了，输入完还要为了防止极低的错误率再核对一遍，费事费力。
既然身份证号码中包含了性别和出生年月信息和校验位，银行卡号码也包含校验位，为什么不让excel自动填充，并显示输入的号码是否正确呢！

身份证相关

身份证号码的构成，例如身份证号码110101190001011236

位数数值含义1-211省份代码3-60101区县代码7-1419000101出生日期15-1612顺序编号前两位173顺序编号第三位，偶数为女，奇数为男186校验位

提取出生年月

=TEXT(MID(E4,7,8),"0-00-00") 1

E4为身份证号码所在单元格；
MID(cell, n, l)的作为，从cell的第n位开始提取，共提取l位。

提取性别信息

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

E4为身份证号码所在单元格；截取E4单元格的第17位，除2取余，如是1填充“男”，如果是0填充“女”。

身份证号码校验

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

E4为身份证号码所在单元格；

位数数值权重乘积位数数值权重乘积112^171310721012^80212^16655361112^70302^1501212^664412^14163841312^50502^1301412^416612^1240961512^38712^1120481612^28812^1092161712^16912^90

将乘积列相加的228454，对11取余的6，取10X98765432中的第6+1位，即6。如果第18位是6则正确，否则错误。

银行卡号码相关

=IF(MOD(SUMPRODUCT(VALUE(MID(TEXT(MID(D4,ROW(INDIRECT("1:"&LEN(D4))),1)*(MOD(LEN(D4)-ROW(INDIRECT("1:"&LEN(D4))),2)+1),"00"),{1,2},1))),10),"错误","正确") 1

D4为银行卡号码所在单元格；
具体校验原理请自行百度Luhn。
将银行卡号码从右向左数，奇数位乘1，偶数位乘2，将乘积的个位数和十位数相加，如果和可被10整除则正确，否则错误。
目前看来不适用于中国工商银行以0开口的号码，如果判定为错误，与原始数据仔细核对。