Fork me on GitHub

EXCEL中,将十六进制转换为十进制

一、背景

  1、在EXCEL表格中,将十六进制转换为十进制的常用方法是:使用HEX2DEC函数。

  2、在EXCEL的一个单元格中,如果输入形如"12E36"之类的可以被成功识别为“科学计数法”的文本,EXCEL就会自动将该单元格中的文本以“科学计数法”的方式进行解释转换。这种智能转换的初衷本是为了提供帮助,但如果"12E36"本身其实是一个十六进制数时,这种转换则会给用户添加不少麻烦。

二、策略

  鉴于以上原因,我在EXCEL中显示和处理十六进制数时,倾向于在原始的十六进制文本前面加上"0x"前缀。以这种方式表示十六进制数,既有助于人为直观判断,又能抑制EXCEL不适当的智能转换。

三、转换方法

  在这里,我总结了自己使用HEX2DEC函数,将含有"0x"前缀的十六进制数转换为十进制数的三种方法。

  前两种方法都采用VBA宏实现,都会用到相同的自定义函数HexToDec(其中用到了Hex2Dec函数)。代码如下:

Function HexToDec(cell)
    Dim hexStr As String
    Dim hexVal As String
    Dim decVal As String
    
    hexStr = cell.Value
    hexVal = Right(hexStr, Len(hexStr) - 2) ' 去掉"0x"前缀(即头两个字符),得到真正代表十六进制值的文本
    
    On Error Resume Next ' 开启错误处理开关(因为文本内容不一定都满足十六进制格式,所以HEX2DEC函数转换时可能会出错)
    decVal = Application.WorksheetFunction.Hex2Dec(hexVal) ' 十六进制 --> 十进制
    If Err.Number = 0 Then '转换未出错
        cell.Value = decVal
        If cell.Value = "0" Then ' 将转换为十进制后值为0的单元格的字体变灰,以便区分
            cell.Font.Color = RGB(200, 200, 200)
        End If
    End If
    On Error GoTo 0 ' 关闭错误处理开关(在函数结尾处,可以忽略)
End Function

  1、在活动工作表被选中的单元格区域中,遍历各个单元格;依次判断每个单元格的值,如果是以"0x"为前缀的,则借助于HexToDec函数进行转换。代码如下:

Sub ConvertData_1()
    Dim rowCount As Long
    Dim columnCount As Long
    Dim row As Long
    Dim column As Long
    Dim cell As Object

    rowCount = Application.Selection.Rows.Count       ' 得到被选中单元格区域中包含的行数
    columnCount = Application.Selection.Columns.Count ' 得到被选中单元格区域中包含的列数

    With Application.Selection
        For row = 1 To rowCount
            For column = 1 To columnCount
                Set cell = .Cells(row, column)
                If Left(cell.Value, 2) = "0x" Then ' 如果单元格的值以"0x"为前缀
                    HexToDec cell ' 将该单元格的值从十六进制转换为十进制
                End If
            Next column
        Next row
    End With
End Sub

   2、在活动工作表被选中的单元格区域中,使用Range对象的Find方法逐个查找以"0x"为前缀的单元格;如果查找到满足条件的单元格,则借助于HexToDec函数对其值进行转换。代码如下:

Sub ConvertData_2()
    Dim cell As Object

    With Application.Selection
        Set cell = .Find("0x", LookIn:=xlValues) ' 查找以"0x"为前缀的单元格
        If Not cell Is Nothing Then ' 如果查找成功
            firstAddress = cell.Address
            Do
                HexToDec Cells(cell.row, cell.column)
                Set cell = .FindNext(cell)

            On Error Resume Next ' 开启错误处理开关
            ' 查找并转换完毕所有满足条件的单元格后,执行下面这行语句应该条件为假从而跳出循环;
            ' 但不知为何,实际情况是:此时会出错,所以特别增加了错误处理
            Loop While Not cell Is Nothing And cell.Address <> firstAddress 
            If Err.Number <> 0 Then ' 如果出错,表明已经查找并转换完毕,退出
                Exit Sub
            End If
            On Error GoTo 0 ' 关闭错误处理开关(在函数结尾处,可以忽略)

        End If
    End With
End Sub

   3、直接在EXCEL表格中为含有十六进制数据的单元格指定Formula属性:使用转换公式(配合Hex2Dec函数、Right函数和Len函数),然后使用Copy方法拷贝相同的公式到其他含有十六进制数据的单元格中。转换公式如下:

=HEX2DEC(RIGHT(A1, LEN(A1) - 2)) ' 针对A1单元格的转换公式

 四、比较

  1、方法1、2采用VBA宏编写,更为灵活:

      (1)对于不满足十六进制格式的文本数据,可以跳过不处理,进而做到只转换满足格式的数据,不满足格式的数据不会受影响。

      (2)可以根据转换后数值的不同,设置单元格的属性(如将值为0的单元格的字体变灰)。

  2、方法1与方法2相比,方法2的速度更快,可能是得益于“Find方法”的查找效率高于“人为过滤”的效率。

  3、相比方法1、2而言,方法3的速度是最快的(而且要快得多),批量转换效率极高。但不足的地方有:

  (1)由于是直接写在EXCEL表中的,不如VBA宏编程来得灵活;

  (2)转换后得到的十进制数据一般出现在新的单元格中,所以属于拷贝转换,而不是替换;

  (3)Copy方法往往针对大块连续的单元格区域,其中若含有不满足十六进制格式的数据,也会一并被转换,不但转换后的数据是无效的,之前的原始数据也受到了影响(没有被保留)。

  当然如果能够保证所有待转换的数据都满足十六进制格式(即以"0x"为前缀,并且后续字符均代表有效的十六进制数字),并且待转换的数据量非常大,那么使用方法3将会是最佳的选择。

posted on 2011-11-09 22:45  RussellLuo  阅读(25105)  评论(0编辑  收藏  举报

导航