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

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

身份证相关

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

位数	数值	含义
1-2	11	省份代码
3-6	0101	区县代码
7-14	19000101	出生日期
15-16	12	顺序编号前两位
17	3	顺序编号第三位，偶数为女，奇数为男
18	6	校验位

提取出生年月

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

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

提取性别信息

=IF(MOD(MID(E4,17,1),2)=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),"正确","错误")))

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

位数	数值	权重	乘积	位数	数值	权重	乘积
1	1	2^17	131072	10	1	2^8	0
2	1	2^16	65536	11	1	2^7	0
3	0	2^15	0	12	1	2^6	64
4	1	2^14	16384	13	1	2^5	0
5	0	2^13	0	14	1	2^4	16
6	1	2^12	4096	15	1	2^3	8
7	1	2^11	2048	16	1	2^2	8
8	1	2^10	9216	17	1	2^1	6
9	1	2^9	0

将乘积列相加的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),"错误","正确")

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