UDS故障码(DTC)SAE格式和HEX相互转换公式

用excel函数实现DTC常见两种形式的转换，见下

SAE→HEX：

公式：B3=BIN2HEX(IF(LEFT(A3,1)="P"，HEX2BIN(0,2),IF(LEFT(A3,1)="C"，HEX2BIN(1,2),IF(LEFT(A3,1)="B"，HEX2BIN(2,2),IF(LEFT(A3,1)="U"，HEX2BIN(3,2),"Rrror"))))&HEX2BIN(RIGHT(LEFT(A3,2),1),2))&RIGHT(A3,5)

 

HEX→SAE：

公式：A3=IF(LEFT(HEX2BIN(LEFT(B3,1),4),2)="00","P",IF(LEFT(HEX2BIN(LEFT(B3,1),4),2)="01","C",IF(LEFT(HEX2BIN(LEFT(B3,1),4),2)="10","B",IF(LEFT(HEX2BIN(LEFT(B3,1),4),2)="11","U","Error"))))&BIN2HEX(RIGHT(HEX2BIN(LEFT(B3,1)),2))&RIGHT(B3,5)

excel中IF函数多重条件使用见http://t.csdn.cn/UUftWLL