16进制数的65536怎么来的（Excel公式自动解析CAN报文的方法）

在CAN网络上截取的报文一般被保存为为.txt或.asc等格式，在分析报文时经常需要对着协议来逐条报文、逐个字节甚至逐节来解析。为了应对这种情况，行业内也是提出了各种解决方案，比如Vector的CAN报文软件，或者自己写上位机来自动解析，再或者将对应报文导入进Excel中用公式自动解析……

本文详细介绍了在Excel中用公式解析CAN报文的方法，读者也可基于这个Excel来编写解析用到的其他报文协议。

报文定义类型

以GB\T 27930协议中的CRM报文为例，CAN报文中的字节定义主要有以下几种用途。

1. 状态定义，比如“BMS不能识别”和“BMS能识别”这两种状态
2. 数据定义，比如充电机编号
3. 编码定义，比如将报文内容转换为ASCII码

常用公式

根据这几种用法，可在Excel中用对应的公式将其表示结果自动解析出来。以下是常用到的公式（以下示例如未特别注明，则数字均为十进制格式）：

• 进制转换

HEX2DEC(number) —— 将十六进制数字number转换成十进制格式。如填入十六进制数字10，则返回十进制数字16，即HEX2DEC(10) = 16；

• 除法取余

MOD(number,divisor) —— 将数字number除以divisor，然后取结果的余数。如number = 10,divisor = 3，则返回结果为1，即MOD(10,3) = 1；

• 除法取整

INT(number)——对number进行取整，例如number = 10/3，则返回3，即INT(10,3) = 3；

• 依据序号取值

CHOOSE(index_num,value1,value2,…) —— 根据index_num的值来显示后方的value。如index_num = 2，value1 = 5，value2 = 6，则CHOOSE的返回结果为6，CHOOSE(2,5,6) = 6；

• 条件公式

IF(test,true,false) —— test是一个条件公式，根据条件逻辑结果返回后方对应的内容。如test内容为“1 = 2”，true = 33，false = 44，则IF返回结果为44，即IF(1=2,33,44) = 44；

• ASCII码转换

CHAR(number) —— 返回数字number对应的ASCII中的字符。如number = 68，则CAHR返回的结果为“D”，即CHAR(68) = D.

公式应用

下面将以CHM、CRM、BST报文为例来讲解公式用法。

• CHM报文

CHM报文定义很简单，就三个字节来表示版本。实际分析时一眼就能看明白，用不着解析，不过为了说明还是有必要来看看解析方式啦~

解析公式为“="CHM协议版本：V"&J4&I4&"."&H4”。

1. 其中J4、I4、H4分别为定义中的byte3,byte2和byte1
2. 符号&将将这三个字节合并到一起成为011
3. 但是定义中是有符号V和小数点的，因此在定义位置加入内置对应文字的英文双引号字符，再用符号&将它们并起来就可以显示成定义模样了
4. 公式中双引号内的字符可根据实际需求随意修改

• CRM报文

CRM报文有三个定义，刚好对应三种类型：

• 将数字转为对应的文字
• 将多字节数字转为一个数字
• 将数字转换为ASCII编码。

• 数字转文字。公式为“=IF(H207="aa","BMS能辨识",IF(H207=0,"BMS不能辨识","无效"))”，用了两个IF.

1. 第一个IF判断第一字节内容是否为aa，是的话显示文字“BMS能辨识”，否则进入下一个IF
2. 第二个IF判断第一字节是否为0，若为0则显示“BMS不能辨识”，否则就是报文有误，提示无效

• 多字节数字转为一个数字。公式为“="充电机编号:"&HEX2DEC(L4)*65536+HEX2DEC(K4)*4096+HEX2DEC(J4)*256+HEX2DEC(I4)”

1. 与十进制计算类似，公式里面的256、4096、65536依次是16的平方、立方、四次幂
2. 因为是16进制，所以计算时都乘以16进制的倍数

• 将数字转为ASCII编码。公式为“="区域:"&CHAR(HEX2DEC(M207))&CHAR(HEX2DEC(N207))&CHAR(HEX2DEC(O207))”公式虽稍长，将其分为三个部分

1. “区域：”&.这个用法如CRM中所述
2. CHAR()可将括号内的数字转为ASCII字符，但是要求是十进制的，而报文中数字为十六进制，因此括号内再加一个HEX2DEC()公式将十六进制转为十进制

• 按位取值方式

BST报文按位来显示不同的文字，而位与进制直接相关。在解析之前说明一下取值方式。

对于一个十进制数（如12306），如果说要取其个位、十位、百位或千位的数字，我们一眼就能看出其分别为6、0、3、2。但是Excel中公式计算用到的都是十进制，如果要取八进制、四进制、二进制的某个位中的数值，则没这么容易。这涉及一个算法。

以十进制12306例。若取12306的千分位（即从右往左第四个数字），则算法分两步

1. 12306/1000 = 12，将12306对1000（即10的(4-1)次幂=1000）整除取整
2. 12%10 = 2，将取整结果对10（即每个位置可能出现10种数字）取余，所得余数即为千分位数字
3. 0x84/(4^3) = 2，第7~8位在第四个定义位置，因此分母为4的(4-1)次幂
4. 2%4 = 2，结果十进制2的二进制为，对应定义中的“不可行状态”
5. 注：这数字0x84是我随意想的，

• BST报文

BST报文第一字节的第7~8位解析公式为

“=CHOOSE(MOD(HEX2DEC(H559)/64,4)+1,"正常","充电机中止","不可信状态","预留")”，计算分五步

1. HEX2DEC(H559) —— 将第一字节转换为十进制（本文涉及到的公式均以十进制数字来计算）
2. 将十进制结果除以4^(4-1)，即除以64
3. 用MOD()，将上述结果对4取余
4. 将余数+1，（因为CHOOSE的第一个参数是从1开始的，而报文定义是从0开始的）
5. 用CHOOSE()根据计算结果从后方取对应的文字描述显示

直流充电网CAN报文

虽然公式稍显繁复，但公式写好后实际应用时，只需在对应的报文ID后方复制、粘贴、拉伸即可批量解析。以下是解析示例

下图是Excel中GB T/27930协议的全部解析公式截图示意

解析后进一步分析

将报文解析后可根据实际定义进一步分析。

例如CCS报文中的充电机输出电流，可将其生成曲线，以查看充电过程中电流值大小及变化趋势。