返回首页 回到顶部

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

100人浏览   2024-09-10 10:17:33

在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报文中的充电机输出电流,可将其生成曲线,以查看充电过程中电流值大小及变化趋势。