使用Excel校验VIN码(车架号)第九位

键入值与公式

• Excel中 A1 单元格键入需要进行校验的VIN码
• Excel中 B1 单元格键入如下公式

=SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE((SUBSTITUTE(A1,"A","1")),"B","2")),"C","3")),"D","4")),"E","5")),"F","6")),"G","7")),"H","8")),"J","1")),"K","2")),"L","3")),"M","4")),
"N","5")),"P","7")),"R","9")),"S","2")),"T","3")),"U","4")),"V","5")),"W","6")),"X","7")),"Y","8")),"Z","9")

• Excel中 C1 单元格键入如下公式

=IF(TEXT(IF(MOD(SUM((MID(B1,1,1)*8+MID(B1,2,1)*7+MID(B1,3,1)*6+MID(B1,4,1)*5+MID(B1,5,1)*4+MID(B1,6,1)*3+MID(B1,7,1)*2+MID(B1,8,1)*10+MID(B1,10,1)*9+MID(B1,11,1)*8+MID(B1,12,1)*7+MID(B1,13,1)*6+MID(B1,14,1)*5+MID(B1,15,1)*4+MID(B1,16,1)*3+MID(B1,17,1)*2)),11)=10,"X",MOD(SUM((MID(B1,1,1)*8+MID(B1,2,1)*7+MID(B1,3,1)*6+MID(B1,4,1)*5+MID(B1,5,1)*4+MID(B1,6,1)*3+MID(B1,7,1)*2+MID(B1,8,1)*10+MID(B1,10,1)*9+MID(B1,11,1)*8+MID(B1,12,1)*7+MID(B1,13,1)*6+MID(B1,14,1)*5+MID(B1,15,1)*4+MID(B1,16,1)*3+MID(B1,17,1)*2)),11)),0)=TEXT(MID(A1,9,1),0),"校验正确",IF(MOD(SUM((MID(B1,1,1)*8+MID(B1,2,1)*7+MID(B1,3,1)*6+MID(B1,4,1)*5+MID(B1,5,1)*4+MID(B1,6,1)*3+MID(B1,7,1)*2+MID(B1,8,1)*10+MID(B1,10,1)*9+MID(B1,11,1)*8+MID(B1,12,1)*7+MID(B1,13,1)*6+MID(B1,14,1)*5+MID(B1,15,1)*4+MID(B1,16,1)*3+MID(B1,17,1)*2)),11)=10,"X",MOD(SUM((MID(B1,1,1)*8+MID(B1,2,1)*7+MID(B1,3,1)*6+MID(B1,4,1)*5+MID(B1,5,1)*4+MID(B1,6,1)*3+MID(B1,7,1)*2+MID(B1,8,1)*10+MID(B1,10,1)*9+MID(B1,11,1)*8+MID(B1,12,1)*7+MID(B1,13,1)*6+MID(B1,14,1)*5+MID(B1,15,1)*4+MID(B1,16,1)*3+MID(B1,17,1)*2)),11)))

说明

• A1单元格为VIN码
• B1单元格为转译后的VIN码
• C1单元格为最终结果
  
  1.校验正确时返回“校验正确”
  2.校验错误时返回 正确的第九位

样例

VIN码	转译	Result
LFV3A28K7D3043307	36531282743043307	校验正确
LFV3A28KXD3002538	36531282743002538	8