上面是C3单元格银行卡号，最后一位先设置为0，然后在D3单元格内输入公式。得到银行卡号，

就是银行卡号前15位和最后一位校验位。

=IF((10-MOD(SUMPRODUCT(LEFT((0&MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))*2^MOD(ROW(INDIRECT("1:"&LEN(C3)))+MOD(LEN(C3),2),2),1)+(0&MID((MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))*2^MOD(ROW(INDIRECT("1:"&LEN(C3)))+MOD(LEN(C3),2),2),2,1))),10))<>10,LEFT(C3,15)&(10-MOD(SUMPRODUCT(LEFT((0&MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))*2^MOD(ROW(INDIRECT("1:"&LEN(C3)))+MOD(LEN(C3),2),2),1)+(0&MID((MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))*2^MOD(ROW(INDIRECT("1:"&LEN(C3)))+MOD(LEN(C3),2),2),2,1))),10)),C3)

下面是计算校验位的公式：

10-MOD(SUMPRODUCT(LEFT((0&MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))*2^MOD(ROW(INDIRECT("1:"&LEN(C3)))+MOD(LEN(C3),2),2),1)+(0&MID((MID(C3,ROW(INDIRECT("1:"&LEN(C3))),1))*2^MOD(ROW(INDIRECT("1:"&LEN(C3)))+MOD(LEN(C3),2),2),2,1))),10)